0

I'm trying to figure out whether an AWS Athena query is successfully folding on the Native Query in PowerBI, for the purpose of setting up an incremental refresh. I created the parameters, filtered my datetime column on those parameters and tried the Diagnose tool (see https://www.youtube.com/watch?v=QEFze-LdLqo from 4:50 on), but it keeps running and doesn't show any results. So, I'm trying the approach of querying in the Advanced Editor (https://www.youtube.com/watch?v=KEh2Udm6ibA&feature=youtu.be 20:00 onwards). But since this example is in SQL and I'm working with AWS Athena, I keep getting errors. Here are the Advanced Editor queries I have tried so far:

Example 1:

> let
>     Source = Odbc.Query("dsn=Simba Athena", "SELECT * FROM ""databasename"".""tablename"" where StartTimeCET>= ' " &
> DateTime.From(RangeStart) & "' and StartTimeCET< '" &
> DateTime.From(RangeEnd) & "' ") in Source

Error: We cannot apply operator & to types Text and DateTime.

Example 2:

> let
>     Source= Odbc.Query("dsn=Simba Athena", "SELECT * FROM ""database"".""tablename""
> where StartTimeCET>= ' "  DateTime.From(RangeStart)  "' and
> StartTimeCET< '"  DateTime.From(RangeEnd)  "' ")

Error: Token Comma expected.

Example 3:

      let
     Source = Odbc.Query("dsn=Simba Athena", "SELECT * FROM ""database"".""tablename"" where StartTimeCET>= ' "" &
   Text.From(RangeStart) & ""' and StartTimeCET < '"" & Text.From(RangeEnd) & "" ' ") in Source

Error: Exception parsing query SELECT * FROM ""database"".""tablename"" where StartTimeCET>= ' " & Text.From(RangeStart) & "' and StartTimeCET < '" & Text.From(RangeEnd) & " ' with parser version athena_v1 and context QueryExecutionContext(queryId=null, database=default, catalog=null) [Execution ID: ]

Any ideas on how to write such an Advanced Editor query for AWS Athena? To simplify, I want to filter the Advanced Editor query in PowerBI based on the RangeStart and RangeEnd paramaters. Both parameters and StartTimeCET column are type date/time.

Salman Tahir
  • 37
  • 1
  • 8

2 Answers2

1

I think I solved the error but still failed to "View Native Query".

You can pass the manual Athena direct query step by writing with Presto syntax and using DateTime.ToText() function with appropriate date format i.e.

Odbc.Query("dsn=Simba Athena", 
"SELECT * FROM tablename 
WHERE StartTimeCET >= TIMESTAMP '" & DateTime.ToText(RangeStart, "yyyy-MM-dd") & "' 
AND StartTimeCET < TIMESTAMP '" & DateTime.ToText(RangeEnd, "yyyy-MM-dd") & "'
")

EDIT: I think I have managed to achieve the "Incremental Load" in Power BI using Athena. This (still) does not allow you to view Native query but you can still make Power BI manipulate the direct query to implement it.

To avoid full scan of S3 data in Athena - you have to enable Partitions in your dataset. Without going off topic, once you partition the S3 data via Athena you can then pin point the datasets with days/months/years without scanning your whole dataset.

Once you do that, you can achieve the Incremental Load by running Direct Queries as mentioned in the last video link you shared and achieve resource-efficient query execution.

The final query will look something like -

Odbc.Query("dsn=Simba Athena", 
    "SELECT * FROM tablename 
    WHERE year >= " & DateTime.ToText(RangeStart, "yyyy") & "
AND month >= " & DateTime.ToText(RangeStart, "MM") & "
AND day >= " & DateTime.ToText(RangeStart, "dd") & "
AND year <= " & DateTime.ToText(RangeEnd, "yyyy") & "
AND month <= " & DateTime.ToText(RangeEnd, "MM") & "
AND day <= " & DateTime.ToText(RangeEnd, "dd") & "
")

EDIT #2: OR simply

    Odbc.Query("dsn=Simba Athena", 
        "SELECT * FROM tablename 
        WHERE dt >= '" & DateTime.ToText(RangeStart, "yyyy/MM/dd") & "'
        AND dt <= '" & DateTime.ToText(RangeEnd, "yyyy/MM/dd") & "'
    ")
BillaD
  • 86
  • 1
  • 5
0

always go >= RangeStart and < RangeEnd to avoid duplicates

Bernat Agulló
  • 43
  • 7