CODEWITHSUNDEEP
pythonpython-3.xgoogle-cloud-platformgoogle-bigquerybigquery-udf
ditil
ditil

Reputation: 89

Insert or upsert or delete row in Big Query based on condition with efficient way

Here is my input json format

{
  "ID": "ABC34567",
  "NUM": "45678",
  "ORG_ID": "161",
  "CONTACT_NUMBER": null,
  "FLAG": "N"
}

I need to check whether ID is already present or not. If present, need to update the Data else insert a row. If FLAG:"Del", Need to Delete if ID is already present in BigQuery table else leave as is.

I am struggling how to achieve above functionality in python.

To insert a json, I know below code in python using BigQuery API,

rows_to_insert = [{"ID":"sdf1234","NUM":"4567890","ORG_ID":"567","CONTACT_NUMBER":"456789", "FLAG":"N"}]
    
errors = client.insert_rows_json(table_id,rows_to_insert)

I found below code on internet,

from google.cloud import bigquery

client = bigquery.Client()

query_job = client.query(
    """
    MERGE my-dataset.json_table T
USING my-dataset.json_table_source S
ON T.ID = S.ID
WHEN MATCHED THEN
  UPDATE SET string_field_1 = s.string_field_1
WHEN NOT MATCHED THEN
  INSERT (int64_field_0, string_field_1) VALUES(int64_field_0, string_field_1)"""
)

results = query_job.result()  # Waits for job to complete.

I am totally stuck how can i able to pass json in above query or any other best way to update the existing row

Please guide me how can i achieve this in BigQuery API using python

Upvotes: 1

Views: 547

Answers (0)

Related Questions