Google BigQuery - opening partitioned tables

[#1]

Via File --> Database --> Open Table... I am trying to open a table from Google BigQuery using ODBC. This results in the error message below (which also reproduces in the log if attempting to open using Open Database() in JSL).

Google's help on querying partitioned tables is not of much use for my skill level, but I guess I need a qualifying filter over the partition column. Anyone who can advise on how to proceed? Below, a screenshot of the BigQuery Table info. The table is around 2e9 rows.

[#2]

I ran across this:
https://stackoverflow.com/questions/55688114/is-it-possible-to-remove-the-partition-filter-required-...

I'm not sure if you have already tried it, and I haven't tried it, but it's supposed to change the Partition filter to false.

The other alternative I saw was this:
https://stackoverflow.com/questions/51194105/cannot-query-over-table-without-a-filter-that-can-be-us...

[#3]

Thank you! I will try both as soon as I find the time. Ufortunately, my designs are frustrated by other assignments and the flu, of course.

[#4 · Accepted solution]

@bryan_boone fyi, I haven't found a solution using JMP/JSL directly to download partitioned tables from BigQuery. There are two approaches I considered, one of which I am following up practically:

1. It is possible to write queries in BigQuery to aggregate the data and then download as *.csv or *.json, for example. Given the number of tables though as well as the number of rows to be handled in each table, I'd expect to dedicated a (very) large chunk of time to just iteratively look for the right aggregation. If there is a better solution, I don't know it.
2. Using Python. I am in the fortunate position to have a newly hired data engineer as desk neighbor who I briefly discussed my challenge with. He offered to provide the necessary training so I can use Python to loop through these tables, or at least copies of them on our Congite Data Fusion implementation, to get what I think I want. Probably, the Cognite Python SDK will play a big role in this.

I hope you'll forgive me for accepting my own response as solution somewhat prematurely.

[#5]

I was looking at:
https://cloud.google.com/retail/docs/export-data-into-bq

(If there is another download api you are looking at, let me know)

However, if it is that endpoint, or something similar, you can use HTTP Request or Multi HTTP Request to download results from Big Query.

With HTTP Request, you can do one at a time, with Multi HTTP Request (as the name implies) you can do multiple requests, at the same time. That is, with multi, the time it takes is the time it takes for the longest request and with the single http request, it's the total time for all requests.

[#6]

This is very interesting, but at the same time also confusing. The link you shared ...

• ... describes exporting of the data into BigQuery, whereas my data is already in BigQuery and I want to download it.
• ... refers to retail product and user event data. The data I want to access are time series of various sensors/tags (pressure, temperature, flow rates, etc.). Maybe the difference is not relevant, though?
• ... is very technical and I am not sure I understand the parts I should understand. (Hence this response.)

I am also not sure whether HTTP request is something I should be able to extract form the reference you shared or whether that comes in addition. (I am guessing, the latter.)

Thank you for your patience. I am an avid reader and JSL is about the only experience I have with coding so far, but I think I need to start at a much more basic level with regards to accessing/unlocking BigQuery.