Forum Discussion

amirthaP's avatar
amirthaP
Frequent Visitor
2 years ago

Partitioning in direct Query mode

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!

 

Need Help

2 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.