Where to embed SQL Query for Amazon Athena in Power Query

[KremenaK]

Hello,
I have successfully connected Power BI with AWS/Amazon Athena (via ODBC connector), but can't figure out where to write a sql statement (or what M function to use) to query the underlying data in order to limit the results as current dataset has millions of rows.

Doing simple transformations in Power Query doesn't help...data still too lage to load to model.

Thank you!

[Syndicate_Admin]

Can you please help how did you write direct query for AWS Athena table ? 

I am using ODBC connector to connect  Athena , using import , and full tables are coming to my data set .

Please help .

[KremenaK]

When you connect to Athena via ODBC driver you can either write a SQL query (see my other response above) or in Power Query select the columns you need, filter data, do transformations, etc. and load into your model.

[Syndicate_Admin]

Custom query is not available when using Amazon Athena Connector. Instead, you can try ODBC connector to connect to Amazon Athena and then you will have an option to write your custom query in advance options. I hope this answers your question.

Thanks.

[KremenaK]

Hi, @edhans! Yes, I had tried Power Query beforehand and the filtering didn't reduce the dataset by much, the pivoting didn't work either (it was taking forever without result). Finally I left it spinning to import the dataset (with some filters and reduced number of columns) overnight and the task was completed (to my surprise!) - there were more than 105M rows! After inspecting the data it turned out there were about 85% cost items in the range -0.01 to 0.01. So after filtering those out in Power Query the data load time was siginificantly reduced - to 1.5 hr. Which is still way slower than DirectQuery (which loads in about 30s and updates visuals in 10s), but maybe the performance of Import Data will get better after publishing to the Power BI Service.

[KremenaK]

Thank you, @edhans. Now I am connecting as Direct Query picking only the table columns and year(s) that I need and am able to compile some reports and visualizations. It's a bit slow, but workable. Thanks again

[sanjoy_PBI]

Hi,

Can you please provide me the steps where exactly you have writted the QRY (direct qry) for Athena  tables ? I feel to fire the SQL , some pre set up connection string needs to be written , which I am not able to do .

Thanks

Sanjoy

[KremenaK]

When you connect to Athena via ODBC driver you get the option to write a SQL query to limit what you pull into Power BI:

Another option would be to skip this step and in Power Query select the columns you need, filter data, do transformations, etc.
Hope this helps!

[edhans]

Yeah, DirectQuery is a special use case and can be very slow.

Did you try using Power Query? I am not sure if Athena is supported with folding, but if your first few steps are removing columns, renaming columns, most filtering, Power Query will create the SQL query to send back and let you get a nice clean import.

This works fantastic with SQL Server and many other databases. Just not sure about Athena.

[edhans · Accepted solution]

There is no place to do it per Using the Amazon Athena Power BI connector - Amazon Athena
You need to have a view created on Athena that limits the data as desired, then connect to that.

[dsharp_crmf]

I do not see any views in Athena (using the Simba Athena connector), only the tables. How do I get a list of the queries I have created>

Thanks.

[smishra99acres]

Hav you got the solution for custom query in power bi using amazon athena

[KremenaK]

I am removing columns and filtering in Power Query and then loading the data via DirectQuery.