Any success in getting data from redshift spectrum? I'm getting the error: DataSource.Error: The table has no visible columns and cannot be queried.
From aws documentation, https://docs.aws.amazon.com/redshift/latest/dg/c-using-spectrum.html.
It says ... "If your business intelligence or analytics tool doesn't recognize Redshift Spectrum external tables, configure your application to query SVV_EXTERNAL_TABLES and SVV_EXTERNAL_COLUMNS". Anyone could help how can this be configured in power bi desktop?
I still have the same error. I also tried to create a view around the external table but it still fails. Below are the error details.
Feedback Type: Frown (Error) Timestamp: 2019-11-20T14:16:11.2215250Z Local Time: 2019-11-20T16:16:11.2215250+02:00 Session ID: 81f7d9ba-90ec-44fd-85bd-8c01f4b90a3c Release: November 2019 Product Version: 2.75.5649.582 (19.11) (x64) OS Version: Microsoft Windows NT 10.0.18362.0 (x64 en-US) CLR Version: 4.7 or later [Release Number = 528040] Peak Virtual Memory: 38.3 GB Private Memory: 544 MB Peak Working Set: 702 MB IE Version: 11.388.18362.0 User ID: b22a3ce1-8cc5-4140-a640-90212e8cd6c1 Workbook Package Info: 1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True. Telemetry Enabled: True Snapshot Trace Logs: C:\Users\youssef.youssef\Microsoft\Power BI Desktop Store App\FrownSnapShot1286604497.zip Model Default Mode: DirectQuery Enabled Preview Features: PBI_shapeMapVisualEnabled PBI_NewWebTableInference PBI_showIncrementalRefreshPolicy PBI_userFavoriteResourcePackagesEnabled PBI_personalVisualizationPaneEnabled PBI_showMinervaRibbon PBI_showMinervaViewNavigator PBI_decompositionTree Disabled Preview Features: PBI_SpanishLinguisticsEnabled PBI_qnaLiveConnect PQ_AIInsightsFunctionBrowser PBI_realTimeRefresh PBI_QueryDiagnostics Disabled DirectQuery Options: TreatHanaAsRelationalSource Cloud: GlobalCloud DPI Scale: 100% Supported Services: Power BI
Turned out that the data types used in Athena must be one of the supported data types in Redshift
Changing strings to varchar and integers to decimal data types got the direct queries working in Power BI against Redshift spectrum.
I was able to import redshift spectrum tables in Power BI Desktop but when I use direct query mode I receive query folding errors. Any hint on how to resolve this issue?
Thanks for you reply. I was able to connect and get the data from redshift to power bi (as mentioned in the blog). It works when my data source in redshift is a normal database table wherein data is loaded(physically).
It will not work when my datasource is an external table. External table in redshift does not contain data physically. The data is coming from an S3 file location.
we got the same issue. We cannot connect Power BI to redshift spectrum.
1) The connection to redshift itself works.
2) All "normal" redshift views and tables are working.
3) All spectrum tables (external tables) and views based upon those are not working.
I tried the POWER BI redshift connection as well as the redshift ODBC driver:
By the way - it works with Microsoft Excel. There you can access the spectrum tables.