1 column 2 data type issue + field not defined issues

nextdoordoc 0
Viewed 36

I have struggled with this problem for 1 day And finally asking here.

My startup where I am working is logging user data to BigQuery by google cloud logging.

At some point we add 1 column (field) and then we change the data type of this column(field). And It's impossible to query this field through the wholetime of this Table.

The images below is showing the field that I mentioned above. And shows the timeline of the field ("jsonPayload.page.result_count.total") created in 2022/09/09 and the data type changed from STRING to FLOAT.

enter image description here

enter image description here

enter image description here

And If I try to query like below. the error comes out.

enter image description here

How can I query this field "jsonPayload.page.result_count.total" without any problems?

Thanks in advance! :)

google-bigquery
1 Answers

This is a BigQuery limitation using wildcard wherein

If a single scanned table has a schema mismatch (that is, a column with the same name is of a different type), the query fails with the error Cannot read field of type X as Y Field: column_name.

Possible workaround for this is to identify your tables that have the same Data Type for jsonPayload.page.result_count.total and then alter your wildcard query so that only ALL tables with STRING data type for jsonPayload.page.result_count.total will be queried.

You can query using INFORMATION_SCHEMA.COLUMNS to help you identify what tables have STRING and FLOAT64 just like my example below.

select table_name, column_name, data_type
from `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS`
where table_name LIKE '%table%'
order by table_name

My result:

enter image description here

On my example, jsonPayload.page.result_count.total in table1 and table11 are STRING and can be in a single query and table2 and table21 will be in a separate query.

Once identified, I can now create my wildcard query based on my checking of data types above, and then use UNION ALL to join the results (results of STRING and FLOAT64) in a single query and then cast the FLOAT64 into STRING as shown below.

select jsonPayload.page.result_count.total from `your-table.your-dataset.yourwildcard*`
union all select cast(jsonPayload.page.result_count.total as string) from `your-table.your-dataset.yourwildcard2*`

Output: enter image description here

userMTlc10
Related