Autocreate tables in Bigquery for multiple CSV files

Admin 1
Viewed 286

I want to generate tables automatically in Bigquery whenever a file is uploaded in storage bucket using cloud function in python.

For example- if sample1.csv file is uploaded to bucket then a sample1 table will be created in Bigquery. How to automate it using cloud function using Python i tried with below code but was able to generate 1 table and all data got appended to that table, how to proceed

def hello_gcs(event, context):
    from google.cloud import bigquery
    # Construct a BigQuery client object.
    client = bigquery.Client()

    # TODO(developer): Set table_id to the ID of the table to create.
    table_id = "test_project.test_dataset.test_Table"

    job_config = bigquery.LoadJobConfig(
    autodetect=True,
    skip_leading_rows=1,
    # The source format defaults to CSV, so the line below is optional.
    source_format=bigquery.SourceFormat.CSV,
    )
    uri = "gs://test_bucket/*.csv"

    load_job = client.load_table_from_uri(
    uri, table_id, job_config=job_config
    )  # Make an API request.

    load_job.result()  # Waits for the job to complete.

    destination_table = client.get_table(table_id)  # Make an API request.
    print("Processing file: {file['name']}.")
google-cloud-functions google-bigquery python-3.x
1 Answers

Sounds like you need to do three things:

  1. Extract the name of the CSV file/object from the notification event you're receiving to fire your function.

  2. Update the table_id in your example code to set the table name based on the filename you extracted in the first step.

  3. Update the uri in your example code to only use the single file as the input. As written, your example attempts to load data from all matching CSV objects in GCS to the table.

user_2124350
Related