cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
edsonfajilagot
New Member

Error in accessing redshift spectrum (external table)

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!

 

7 REPLIES 7
youssefyoussef
Frequent Visitor

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

 

 

 

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
New Member

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

v-huizhn-msft
Microsoft
Microsoft

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

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?

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

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors