PowerBI & Athena ODBC connection problem

[pejay]

Hi everyone,

we try to connect to AWS Athena from PowerBI via their ODBC driver. We encountered a problem with queries that have a with statement, i.e.

with a1 as

(
     select * from table1
)
select *
from a1;

We get the error message: We encountered an error while trying to connect. Details: "This native database query isn't currently supported."

Queries without with statement work fine, also with queries work with a JDBC driver. I couldn't find any problems while checking logs of the ODBC Athena driver, it looks to me like a problem of PowerBI.

Any idea what could causing this and how to solve it? Nesting the queries gets quite ugly.

Thanks for any help.

Best,

Philipp

[v-yuezhe-msft]

@pejay,

As you test, it doesn't support CTE query in this case, you would need to remove with statement in the query to connect to your data source.

Regards,
Lydia

[pejay]

Well, I wanted to know why it doesn't support CTE queries, it seems to be a problem of PowerBI, not Athena ODBC. JFYI: Another workaround is to use Redshift Spectrum.

[v-yuezhe-msft]

@pejay,

This is by design, you can submit an idea here.

Regards,
Lydia