cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

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 dataflow. When connecting to the actual table (orders) I am able to add a filter on orderDate and connect correctly. However, when attempting to connect to the view (orders_test) it returns an ODBC error. 

 

Advanced editor: 

 

let
  Source = GoogleBigQuery.Database([BillingProject = null, UseStorageApi = null, ConnectionTimeout = null, CommandTimeout = null]),
  Navigation = Source{[Name = "bi-stg"]}[Data],
  #"Navigation 1" = Navigation{[Name = "bi", Kind = "Schema"]}[Data],
  #"Navigation 2" = #"Navigation 1"{[Name = "orders_test", Kind = "View"]}[Data],
  #"Filtered rows" = Table.SelectRows(#"Navigation 2", each [i_orderDate] = #date(2022, 1, 4))
in
  #"Filtered rows"

 

 

Error:

 

DataSource.Error: ODBC: ERROR [42000] [Microsoft][BigQuery] (70) Invalid query: Cannot query over table 'bi-stg.bi.orders' without a filter over column(s) 'orderDate' that can be used for partition eliminationDetailsDataSourceKind = GoogleBigQuery
DataSourcePath = GoogleBigQuery
OdbcErrors = #table({"SQLState", "NativeError", "Message"}, {})

 

 

Note that the error message is referring to the original table (orders) and it's orderDate column, not to the view (orders_test) and it's orderDate column (i_orderDate) which I'm trying to query. I tried changing the Filtered Rows step and specifying orderDate instead of i_orderDate but receive the same error. It's assuming I need to add a filter to the original table even though I'm trying to query the view. Can anyone help? Thanks

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

Hi @lg_analyst 

 

See if these links help:

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

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

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

 

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?

vjingzhang_0-1642131089603.png

 

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

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

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

Syndicate_Admin
Administrator
Administrator

Hi Jing, thank you for your answer. I can't disable the require partition filter because the table is also used by other users (other than Power BI) and the filter is necessary to try and contain costs as much as possible. 

Using Power Bi Desktop I have the same issue 

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,410)