Create bq schema using a batch of data

[tc5613213]

Hi,

I wonder if it is possible for me to use a batch of data, deduce schema base on the batch of data instead of a single file? Thanks!

[bxparks]

If you are using newline delimited json, just concat all the files and pipe into generate-schema:

$ cat file1.json file2.json file3.json ... | generate-schema

If you are using CSV files, you have to strip off the 1st line of each file, because that contains the header.

If the files are too big to be concatenated together, use the --existing_schema_path flag, and write a shell script to generate the updated schema, then feed the new schema into the generate-schema command using this flag.

If that is not suitable, write a Python program and use bigquery-schema-generator as a library, and process each file within the Python program.

Lots of options.

[bxparks]

Oh, with CSV files, you can do the catting and stripping if you geek out on some obscure bash features, like this:

$ cat file1.csv <(tail -n +2 file2.csv) <(tail -n +2 file3.csv) ... | generate-schema

(Edit: Fixed incorrect flags on tail command).

[tc5613213]

Thank you for your quick response. I just figure it out and it works.

However, I came into a situation where the there might be a type in-consistance with in the data. Let say I have got a field, 1 json files have the type int, and the other one with the type str. How would you handle this kind of situation?
I am using the deduce_schema as a package and it return a error log:
[{'line_number': 2, 'msg': 'Ignoring field with mismatched type: old=(hard,type,NULLABLE,INTEGER); new=(hard,type,NULLABLE,STRING)'}]
and the schema for the field become None.
How do I deal with these kind of situation?

[bxparks]

bigquery-schema-generator can't help you with that. You can:

• remove one of the lines
• rename one of the fields so that it doesn't clash
• convert both of them into integers
• convert both of them into strings