JSON with different schemas for a single external table in BigQuery

Question

Is there a way to set up an external table in BigQuery on a few JSON files (stored in Google Cloud Storage) with different schemas? I would have files with the schemas:

field_name	type	mode
laps	record	repeated
waypoints	record	repeated
col1	string	nullable
col2	string	nullable
col3	integer	nullable

field_name	type	mode
laps	record	repeated
waypoints	record	repeated
col1	string	nullable
col2	string	nullable
col3	integer	nullable
col4	float	nullable
col5	timestemp	nullable

so, the new attributes will be only added to the end.

May I request an expected output of the table ? Placing loading json file query documentation here for reference: cloud.google.com/bigquery/docs/… — Nestor
– Nestor, Commented Mar 14, 2023 at 13:51
@NestorCenizaJr it should contain 5 columns (from col1 to col5) and for the json that don't have attributes col4 and col5, there will be null — KnowNothing
– KnowNothing, Commented Mar 14, 2023 at 16:05

Vitória Calil · Accepted Answer · 2024-02-12 20:27:06Z

1

I have found a solution for this. When creating the External Table in BQ you have to set ignore_unknown_values = true.

Unfortunately, I was using the BQ create table interface which does not include this option.

After carefully reviewing the External Table documentation I found out about this option.

CREATE EXTERNAL TABLE `project-id.dataset.table_name`

WITH PARTITION COLUMNS

OPTIONS (

format = 'NEWLINE_DELIMITED_JSON',

uris = ['gs://bucket/folder/dt=*.json'],

hive_partition_uri_prefix = 'gs://bucket/folder',

require_hive_partition_filter = false,

ignore_unknown_values = true);

answered Feb 12, 2024 at 20:27

Vitória Calil

1701 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JSON with different schemas for a single external table in BigQuery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related