Error with Amazon Redshift

[ludovic_lm]

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..

[v-haibl-msft]

@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

[ludovic_lm]

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]

@ludovic_lm

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

Best Regards,
Herbert

[Anonymous]

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