Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

ludovic_lm

Error with Amazon Redshift

Submitted by on ‎04-12-2017 04:15 AM

 

Hi Guys,

 

When i export data from a postgreSQL DB hosted in Amazon Redshift with PowerBi Desktop i got the following error. This error happen for somes tables but not all tables.

 

1. I coonect to Amazon redshift Beta using the Server, DB name, USer and Password. OK

2. i select the list of tables that i want to import.

3. when the import process start i got the below error for some tables

 

Note i have the error when i connect through edit query window (the equivalent of the power Query window).

When i connect through the equivalent of the PowerPivot windows i can import the data, but when i want to edit the query then i got the error:

 

DataSource.Error : ODBC: ERROR [42601] [Microsoft][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 42601] ERROR: syntax error at or near "1000"
LINE 1: select top 1000
^

Détails :
DataSourceKind=AmazonRedshift
DataSourcePath=db-pg-crm.cpzow3g7iahe.eu-west-1.rds.amazonaws.com:5432;EKKO
OdbcErrors=Table

 

 

So i can import data but i cannot edit the query and make the necessary transformation..

Status: Needs Info
See more ideas labeled with:
5 Comments (5 New)
Comments
v-haibl-msft
Employee
Employee
‎04-16-2017 10:38 PM

@ludovic_lm

 

Did you get data through the Amazon Redshift (Beta) connector in Apr 2017 version of PBI Desktop? For those tables which returned error message, does your account have the corresponding permission to access them?


Best Regards,
Herbert

 

Vicky_Song
Impactful Individual
Impactful Individual
‎04-16-2017 10:38 PM
Status changed to: Needs Info
 
ludovic_lm
Regular Visitor
‎04-18-2017 02:23 AM
Hi Herbert, Yes I was using the Amazon Redshift (Beta) connector in Apr 2017 version of PBI Desktop. The account has acces to the underliing tables. Method 1, when i use this connector from the "PowerPivot" window it works and *i can import* data from all the tables. Then when i want to edit the query i got an error when the "PowerQuery" window open (see above Error). This error happen for some tables but not all. Some tables are ok and i can edit them. Method 2, when i use this connector from the "PowerQuery" window i got an error (see above Error). This error happen for some tables but not all. Some tables are ok and i can edit then =>So there is a difference between the powerPivot and the PowerQuery window. Finally i found another solution. I can connect to the PostgreSQL database hosted in Amazon by using the postgreSQL connector directly. (even if it is hosted in Amazon). I only need to install the Amazon certificat on my pc first. So wondering what is the best approach to connect to a PostgreSQL database hosted in Amazon. Is it by using the PostgreSQL connector or the Amazon Redshift connector? Hope this help.
v-haibl-msft
Employee
Employee
‎04-18-2017 07:11 PM

@ludovic_lm

 

I suggest to use the PostgreSQL connector since the Amazon Redshift connector is still a beta version.

 

Best Regards,
Herbert

Anonymous
Not applicable
‎09-27-2017 11:50 AM

@ludovic_lmCan you share some details of certificate that you installed to connect to PostgreSQL in AWS ?

Idea Statuses