CODEWITHSUNDEEP
google-bigquerygoogle-cloud-platform
KeepLearn
KeepLearn

Reputation: 308

BigQuery load - NULL is treating as string instead of empty

My requirement is to pull the data from Different sources(Facebook,youtube, double click search etc) and load into BigQuery. When I try to pull the data, in some of the sources I was getting "NULL" when the column is empty.

I tried to load the same data to BigQuery and BigQuery is treating as a string instead of NULL(empty).

Right now replacing ""(empty string) where NULL is there before loading into BigQuery. Instead of doing this is there any way to load the file directly without any manipulations(replacing).

Thanks,

Upvotes: 1

Views: 13057

Answers (2)

George
George

Reputation: 1516

You may consider running a command similar to: bq load --field_delimiter="\t" --null_marker="\N" --quote="" \ PROJECT:DATASET.tableName gs://bucket/data.csv.gz table_schema.json

More details can be gathered from the replies to the "Best Practice to migrate data from MySQL to BigQuery" question.

Upvotes: 3

Pulkit Barad
Pulkit Barad

Reputation: 136

What is the file format of source file e.g. CSV, New Line Delimited JSON, Avro etc?

The reason is CSV treats an empty string as a null and the NULL is a string value. So, if you don't want to manipulate the data before loading you should save the files in NLD Json format.

As you mentioned that you are pulling data from Social Media platforms, I assume you are using their REST API and as a result it will be possible for you to save that data in NLD Json instead of CSV.

Answer to your question is there a way we can load this from web console?: Yes, Go to your bigquery project console https://bigquery.cloud.google.com/ and create table in a dataset where you can specify the source file and table schema details.

From Comment section (for the convenience of other viewers):

Is there any option in bq commands for this?

Try this: bq load --format=csv --skip_leading_rows=1 --null_marker="NULL" yourProject:yourDataset.yourTable ~/path/to/file/x.csv Col1:string,Col2:string,Col2:integer,Col3:string

Upvotes: 4

Related Questions