Generate Schema from CSV string and Write Generated Schema to String

[oengelmann-pr]

Hi there,

I'm trying to generate a schema from a CSV string object within a google cloud function and then write this generated schema to a new string so that I can use this in the bigquery table load job. I was assuming I'd be able to do something like;

from bigquery_schema_generator.generate_schema import SchemaGenerator
from google.cloud import storage
from google.cloud import bigquery

storage_client = storage.Client()
bigquery_client = bigquery.Client()

bucket_name = '<BUCKET_NAME>'
file_name = '<FILE_NAME>'
table_name = '<TABLE_NAME>'
dataset_name = '<DATASET_NAME>'

bucket = storage_client.bucket(bucket_name)
file_blob =  bucket.get_blob(file_name)

bigquery_csv_file_name = "".join(["gs://",bucket_name,'/',file_name])

with file_blob.open("r") as f:
     csv_file_contents = f.read()

generator = SchemaGenerator(input_format='csv',sanitize_names=True,quoted_values_are_strings=True)
schema_map = generator.deduce_schema(csv_file_contents)
generated_schema = generator.run(schema_map)

bigquery_job_config = bigquery.LoadJobConfig()
bigquery_job_config.autodetect=False
bigquery_job_config.schema = generated_schema

bigquery_table_reference = bigquery_client.dataset(dataset_name).table(table_name)

bigquery_job = bigquery_client.load_table_from_uri(bigquery_csv_file_name, bigquery_table_reference , job_config=bigquery_job_config)
bigquery_job.result()

However I can't seem to get this working. My interpretation of the README makes me think that this is not possible and I need to write the schema to an output file before I can use it in a load job.

Is there a way to generate a schema based off a CSV string (or file like object) and write the schema back to a string?

[bxparks]

The SchemaGenerator.deduce_schema() method is meant to accept a file object directly. So instead of reading in the CSV into memory, open the file and pass it into deduce_schema(), something like this (this is untested):

generator = SchemaGenerator(input_format='csv',sanitize_names=True,quoted_values_are_strings=True)
with file_blob.open("r") as csv_file:
    schema_map = generator.deduce_schema(csv_fie)
generated_schema = generator.flatten_schema(schema_map)

I recommend getting this working using a small local file before pulling in the complexity of Google Cloud Storage and BigQuery.

Looking at the explanation in the README.md (https://github.com/bxparks/bigquery-schema-generator#SchemaGeneratorDeduceSchema), I definitely need to make that example more clear.

[bxparks]

I uploaded a bunch of examples here:
https://github.com/bxparks/bigquery-schema-generator/tree/develop/examples

The examples/csvreader.py file is probably the most relevant to you.

[oengelmann-pr]

Thanks Brian - have got it working now using;

from bigquery_schema_generator.generate_schema import SchemaGenerator
from google.cloud import storage
from google.cloud import bigquery

storage_client = storage.Client()
bigquery_client = bigquery.Client()

bucket_name = '<BUCKET_NAME>'
file_name = '<FILE_NAME>'
table_name = '<TABLE_NAME>'
dataset_name = '<DATASET_NAME>'

bucket = storage_client.bucket(bucket_name)
file_blob =  bucket.get_blob(file_name)

bigquery_csv_file_name = "".join(["gs://",bucket_name,'/',file_name])

generator = SchemaGenerator(input_format='csv',
sanitize_names=True,
quoted_values_are_strings=True,
infer_mode=False)

with file_blob.open("r") as csv_file:
      schema_map, errors = generator.deduce_schema(csv_file)

schema = generator.flatten_schema(schema_map)

bigquery_job_config = bigquery.LoadJobConfig()
bigquery_job_config.autodetect=False
bigquery_job_config.schema = schema

bigquery_table_reference = bigquery_client.dataset(dataset_name).table(table_name)

bigquery_job = bigquery_client.load_table_from_uri(bigquery_csv_file_name, bigquery_table_reference, job_config=bigquery_job_config)
bigquery_job.result()

Thanks for the package, and your prompt/detailed response!

[bxparks]

Glad that you got it working.
Just out of curiosity, does the bigquery Python binding provide access to the bq load --autodetect functionality? If so, then you may not need to use SchemaGenerator to create the schema first.

[oengelmann-pr]

Yeah it does if you set bigquery_job_config.autodetect=True, however unfortunately it only uses the first 500 rows to infer the schema as per https://cloud.google.com/bigquery/docs/schema-detect. I've created a feature request with Google to enhance this at https://issuetracker.google.com/issues/260487034, but for now your package does the job :)