Timestream ODBC & DirectQuery

[DavinGonzales]

The issue I am facing is when trying to apply a *time* filter or slice from Power BI on a Timestream table via Direct Query on the custom Timestream Connector from Amazon. When selecting time as the attribute to filter on, I am greeted with the following image:

Looking into the logs for the Timestream ODBC driver, I found this error:

```
ERROR MSG: diagnosable_adapter.cpp:36 timestream::odbc::diagnostic::DiagnosableAdapter::AddStatusRecord: Adding new record: AWS API Failure: Failed to execute query

...
where "C1" < {ts '2025-04-01 12:00:00'} and "C1" >= {ts '2025-04-01 00:01:01'}
...
```

Running the exact same query in the Timestream console with the "{ts ...}" escape sequence removed returns a valid result.

Could this be a misconfiguration of the ODBC driver from my end? An issue with Direct Query? A potential problem with the custom connector or the ODBC driver itself? Has anyone else seen this issue?

I am able to successfully connect the Timestream table to Power BI via the custom connector for Power BI. The preview of the data and all of the columns from the table show up in Power BI. Additionally, I can successfully retrieve summarized data via filtering (as long as I don't filter by *time*), so I know there are no issues with the actual connection between Timestream and Power BI.

Any help or advice would be greatly appreciated!
Thank you!

[v-dineshya]

Hi @DavinGonzales ,

Thanks for reaching out to the Microsoft fabric community forum. 

The ODBC logs show this query is being transformed into:

sql code:

where "C1" < {ts '2025-04-01 12:00:00'} and "C1" >= {ts '2025-04-01 00:01:01'}
This syntax is standard ODBC escape syntax for timestamps ({ts '...'}), but Amazon Timestream does not support this syntax natively, which explains why removing it in the Timestream query editor works.

Note:The problem likely stems from how the custom Timestream ODBC driver handles ODBC escape syntax when used with DirectQuery. Since Power BI uses {ts '...'} escape sequences internally for timestamps in DirectQuery mode, this becomes incompatible with Timestream.

Please follow below steps:

1. Use Import Mode: If real-time data is not strictly required, use Import Mode instead of DirectQuery: Change your data source to Import instead of DirectQuery. Apply your time filter Power BI will convert the data before querying, bypassing the {ts} issue.

2. If You Need DirectQuery:

1.Transform the time column into a string or integer in Power BI:

Create a calculated column that casts the timestamp to a format that avoids {ts} syntax:

DAX:

TimeText = FORMAT([time], "yyyy-MM-dd HH:mm:ss")

Filter on TimeText, which may avoid triggering {ts} syntax.

2. Custom SQL (if supported by the connector): Try defining a native SQL query in the connector setup that parses time ranges using from_iso8601_timestamp() or another Timestream-friendly format. Unfortunately, custom SQL is often restricted in DirectQuery for connectors.

3. Raise the issue with AWS: Report to AWS Support . This is a potential bug or lack of support in the ODBC driver for Power BI DirectQuery mode.

Please refer communtiy threads .

Solved: Power BI DirectQuery Error with Simba ODBC – Anyon... - Microsoft Fabric Community

Solved: Direct Query using ODBC - Microsoft Fabric Community

Solved: DirectQuery with ODBC (Postgre SQL) - Microsoft Fabric Community

Enabling DirectQuery for an ODBC-based Power Query connector - Power Query | Microsoft Learn

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[v-dineshya]

Hi @DavinGonzales ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.

Regards,

Dinesh

[v-dineshya]

Hi @DavinGonzales ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.

Regards,

Dinesh

[v-dineshya]

Hi @DavinGonzales ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.

Regards,

Dinesh