Partitioning in direct Query mode

[amirthaP]

Hi all,
I am trying to connect a partitioned table with partition projection in athena to powerbi using direct Query mode
But I am getting this ERROR: 
DataSource.Error: ODBC: ERROR [HY000] [Simba][Athena] (1040) An error has been thrown from the AWS Athena client. Error Message: CONSTRAINT_VIOLATION: Injected projected partition column id must have only (and at least one) equality conditions in the WHERE clause!
Basically there should be a where clause with partition key's value

How to include that from powerbi??
or is there any other way to include partitioning in direct query mode

please suggest!

[Anonymous]

Hi @amirthaP 

The error message "CONSTRAINT_VIOLATION: The injected projected partition column ID must have only one (and at least) equal condition in the WHERE clause" indicates that AWS Athena requires a specific condition in the WHERE clause to be related

To address this issue in Power BI with DirectQuery mode, you can try the following approaches:

1. Modify the Query in Power BI:

• When setting up your DirectQuery connection in Power BI, you usually have the option to specify a custom SQL query.
• In this custom query, make sure to include a WHERE clause that specifies a condition for the partitioned column.
• This can be a static value, or if you need more flexibility, you might consider creating a parameterized query in Power BI, though this may have some limitations in DirectQuery mode.

2. Use Power BI Parameters:

• Power BI allows you to create parameters that can be used in your queries.
• You can create a parameter corresponding to the partition key and use this parameter in your WHERE clause.
• Note that using parameters in DirectQuery mode has some restrictions, and it might not be as dynamic as in Import mode.

3. View or Table in Athena:

• Another approach could be to create a view or intermediate table in Athena that already includes the necessary WHERE clause.
• This way, you can connect Power BI to this view or table directly without needing to modify the query in Power BI.
• This approach can be more manageable if you have a common set of partitions you query against frequently.

^{Best Regards,}

^Jayleny

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[vanessafvg]

from what your error is saying it sounds like you have overlapping partitions?