PowerBI Folding on Native Query 基于 AWS Athena 查询-增量刷新 [英] PowerBI Folding on Native Query based on AWS Athena query-incremental refresh

问题描述

我正在尝试确定 AWS Athena 查询是否成功折叠在 PowerBI 中的本机查询上，以便设置增量刷新.我创建了参数，根据这些参数过滤了我的日期时间列并尝试了诊断工具(请参阅 https://www.youtube.com/watch?v=QEFze-LdLqo 从 4:50 开始)，但它会继续运行并且不显示任何结果.所以，我正在尝试在高级编辑器中查询的方法 (https://www.youtube.com/watch?v=KEh2Udm6ibA&feature=youtu.be 20:00 起).但是由于这个示例是在 SQL 中，并且我正在使用 AWS Athena，所以我不断收到错误消息.以下是我迄今为止尝试过的高级编辑器查询:

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:

示例 1:

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

错误:我们无法应用运算符 &输入 Text 和 DateTime.

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

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

示例 3:

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

错误:异常解析查询 SELECT * FROM ""database""."""tablename"""其中 StartTimeCET>= ' "&Text.From(RangeStart) &"和 StartTimeCET <''&Text.From(RangeEnd) &"' 使用解析器版本 athena_v1 和上下文 QueryExecutionContext(queryId=null, database=default, catalog=null) [执行 ID:]

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: ]

关于如何为 AWS Athena 编写这样的高级编辑器查询有什么想法吗?为了简化，我想根据 RangeStart 和 RangeEnd 参数过滤 PowerBI 中的高级编辑器查询.参数和 StartTimeCET 列都是日期/时间类型.

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.

推荐答案

我想我解决了错误，但仍然无法查看原生查询".

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

您可以通过使用 Presto 语法编写并使用 DateTime.ToText() 具有适当日期格式的函数，即

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") & "'
")

编辑:我想我已经设法实现了增量负载".在 Power BI 中使用 Athena.这(仍然)不允许您查看本机查询，但您仍然可以让 Power BI 操纵直接查询来实现它.

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.

为避免在 Athena 中全面扫描 S3 数据 - 您必须启用 数据集中的分区.无需偏离主题，一旦通过 Athena 对 S3 数据进行分区，您就可以用天/月/年精确定位数据集，而无需扫描整个数据集.

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") & "
")

希望这会有所帮助:-)

Hope this helps :-)

这篇关于PowerBI Folding on Native Query 基于 AWS Athena 查询-增量刷新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！