Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Power BI Athena Incremental Refresh

Hi All
 
I have been successfully using Power BI’s incremental refresh daily with a MySQL data source. However, I can't get this configured with AWS Athena, because seemingly the latter interprets the values in the required parameters RangeStart and RangeEnd as strings. Since the data source is around 50 million rows I’d rather avoid querying this from scratch every day.
 

In this video from Guy in a Cube, you can clearly see that the query sent by Power BI to Azure has a convert to datetime2 function - something like this is presumably missing for Athena/Presto, which needs the type constructor TIMESTAMP in order to do datetime comparisons (https://stackoverflow.com/a/38041684/3675679), and of course incremental refresh must be based on datetime fields. I am using the datetime field adv_date for the incremental load.

 

Here is what the M query looks like in Power Query Editor:

= Table.SelectRows(#"Removed Columns1", each [adv_date] >= RangeStart and [adv_date] < RangeEnd)

And here is the resultant error message in Athena:

Your query has the following errors:SYNTAX_ERROR: line 1:1: Incorrect number of parameters: expected 2 but found 0

Whilst this is how Athena interprets the query:

    select "col1", "col2", "adv_date" 
    from "AwsDataCatalog"."test"."test_table" 
    where "adv_date" >= ? and "adv_date" < ?

I have contacted Power BI support without success.

 

Does anyone have a workaround for this by any chance? Happy to provide more info if needed.

Need Help

7 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Anonymous ,

     

    Based on my test,I cannot reproduce your issue here. How about deleting other steps to check again?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-frfei-msft , thanks for the response.

       

      Here's the entire M query, which I have reduced slightly to the most relevant elements.

       

      let
      Source = Odbc.DataSource("dsn=athena", [HierarchicalNavigation=true]),
      AwsDataCatalog_Database = Source{[Name="AwsDataCatalog",Kind="Database"]}[Data],
      test_Schema = AwsDataCatalog_Database{[Name="test",Kind="Schema"]}[Data],
      cpbd_test_Table = test_Schema{[Name="cpbd_test",Kind="Table"]}[Data],
      #"Filtered Rows" = Table.SelectRows(#"cpbd_test_Table", each [adv_date] >= RangeStart and [adv_date] < RangeEnd)
      in
      #"Filtered Rows"

      As you can see, all I am doing is selecting the entire table and attempting to filter on the datetime column.  I still have the same error as above.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Any update on this? I have the same problem. Date parameters on Power BI show up as question marks in Athena giving Syntax errors

  • bertcasier's avatar
    bertcasier
    New Member
    5 years ago

    I'm having the same problem. Did anyone find a solution for this?

    • bertcasier's avatar
      bertcasier
      New Member
      5 years ago

      From what I can find on the internet:

      AWS athena does not support prepared statements, they advice to construct the full select before sending it to athena.

       

      Is there a way to do that in powerQuery, in attempt to fix the incremental refresh?
      We should be able to construct a query where RangeStart and RangeEnd are replaced before sending it to Athena

      • rafaelruivo's avatar
        rafaelruivo
        Regular Visitor
        5 years ago

        After much, much suffering, I was able to login back to this community. Not even that works well in PBI!

        Well, to the subject at hand, I think I got it working. I added another step before the date filter, and it seems to have worked. I think it moves the filter for after the query load on PBI, and this is very, very annoying, but seems to be working.

        Sometimes I don't understand why I still insist in punishing myself with Power BI.