Automate BigQuery schema/table creation from csv file using Python

Question

I want to automatically create BQ table/s from a desktop folder containing csv files( i.e Automatically create schema and load to a new table)

If the same file is loaded next time just update the existing table, if a new file is loaded then create a new table. Is it possible to automate using Python?.

Current Code:

 import pandas as pd
 from google.cloud import bigquery 
 def bqDataLoad(event, context): 
  bucketName = event['test_vs'] 
  blobName = event['gf-dev-models'] 
  fileName = "gs://" + bucketName + "/" + blobName 
  bigqueryClient = bigquery.Client() 
  tableRef = bigqueryClient.dataset("gf-dev-models-204097").table("test_vs") 
  dataFrame = pd.read_csv(fileName) bigqueryJob = bigqueryClient.load_table_from_dataframe(dataFrame, tableRef) bigqueryJob.result()
#Project id = gf-dev-models
#dataset = gf-dev-models-204097 
#table name = want a new table created

@Soumendra Mishra - How do I pass credentials within this code and how do I create a schema automatically if I dont have an existing table in BigQuery? — Sin
– Sin, Commented Aug 13, 2020 at 1:53

Soumendra Mishra · Accepted Answer · 2020-08-14 08:33:47Z

Here is my answer with reference to your question in the comment section:

Credential in Code: You can create a service account with desired BigQuery roles and download JSON key file (example: data-lab.json).

import os
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = "data-lab.json"

Create Schema Automatically & Loading data to BigQuery:

from google.cloud import bigquery

bigqueryClient = bigquery.Client()
jobConfig = bigquery.LoadJobConfig()
jobConfig.skip_leading_rows = 1
jobConfig.source_format = bigquery.SourceFormat.CSV
jobConfig.write_disposition = bigquery.WriteDisposition.WRITE_APPEND   
jobConfig.autodetect=True

datasetName = "dataset-name"
targetTable = "table-name"
uri = "gs://bucket-name/file-name.csv"
tableRef = bigqueryClient.dataset(datasetName).table(targetTable)
bigqueryJob = bigqueryClient.load_table_from_uri(uri, tableRef, job_config=jobConfig)
bigqueryJob.result()

Collectives™ on Stack Overflow

Automate BigQuery schema/table creation from csv file using Python

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