Forum Discussion

lg_analyst's avatar
lg_analyst
Helper I
4 years ago

Connecting Big Query view with partitioning

Hi, I'm trying to connect to a BigQuery view which is linked to a table that is partitioned on orderDate and requires a filter on orderDate for the query to run. I'm connecting via the Power BI dataf...
Need Help
v-jingzhang's avatar
v-jingzhang
Community Support
4 years ago

Hi lg_analyst 

 

See if these links help:

https://cloud.google.com/bigquery/docs/querying-partitioned-tables#require_a_partition_filter_in_queries 

https://www.yuichiotsuka.com/bigquery-table-partition/ 

https://tipsfordev.com/how-can-i-import-a-partitioned-table-from-bigquery-that-requires-a-partition-filter-in-power-bi 

 

I'm not sure how the SQL query is generated when using a view. It seems the actual table has been set to require Partition Filters in received queries. However, the query currently sent to the actual table doesn't include the valid date filter clause correctly so it indicates this error message. See if you can uncheck the option Require partition filter for the actual table in BigQuery?

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

  • lg_analyst's avatar
    lg_analyst
    Helper I
    4 years ago

    Hi v-jingzhang thank you but I'm looking to query a view, not a table (querying a table works) and removing the partition filter kind of defeats the point

    • v-jingzhang's avatar
      v-jingzhang
      Community Support
      4 years ago

      Hi lg_analyst 

       

      A view is a virtual table defined by a SQL query. When you create a view, you query it in the same way you query a table. When a user queries the view, the query results contain data only from the tables and fields specified in the query that defines the view. So I think disable the option Require partition filter for the actual table might be helpful.

       

      In addition, have you performed the same operations in Power Query Desktop? If it works in Power Query Desktop, you can try copying the query from Desktop to Dataflow and check the result. 

       

      Jing