Error in accessing redshift spectrum (external table)

[edsonfajilagot]

Hi guys,

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?

Many thanks!

[youssefyoussef]

Hello,

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

[youssefyoussef]

Turned out that the data types used in Athena must be one of the supported data types in Redshift

https://docs.aws.amazon.com/redshift/latest/dg/c_Supported_data_types.html

Changing strings to varchar and integers to decimal data types got the direct queries working in Power BI against Redshift spectrum.

[KjellR]

I'm facing the same issue. Any news on this? 

[v-huizhn-msft]

Hi @edsonfajilagot,

In Power Bi desktop, please follow the steps in this official blog, and check if it works fine.  And it also support for Amazon Redshift connectivity in the Power BI service.

Best Regards,
Angelia

[youssefyoussef]

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?

[edsonfajilagot]

Hi @v-huizhn-msft,

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.

Best Regards,

Edson

[JF15]

Hi,

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:

https://docs.aws.amazon.com/redshift/latest/mgmt/install-odbc-driver-windows.html

By the way - it works with Microsoft Excel. There you can access the spectrum tables.

Please support.

Kind regards,

Joern