CODEWITHSUNDEEP
python-3.xgoogle-bigquery
E88
E88

Reputation: 107

insert_rows_json error using python to load bigquery

Need help with the json object load into bigquery, I'm able to establish the connection but now able to load as it keep giving below error, any suggestion? tried both json as a string and object. both giving error

JSON object
d = {}
d['date'] = date_time()
d['status' ] = status
#a = json.dumps(d, indent = 2) # as a json string

qc=bigquery.Client(project=project_name)
dataset = qc.dataset(dataset)
table = dataset.table(table)
table_nm = qc.get_table(table)

qc.insert_rows_json(table_nm, d)

input dict : {"date": "2021-02-01-11.19.55", "status": "Pass "}

error: raise TypeError("json_rows argument should be a sequence of dicts") TypeError: json_rows argument should be a sequence of dicts

Upvotes: 3

Views: 7909

Answers (1)

Tim Swena
Tim Swena

Reputation: 14786

The insert_rows_json method expects multiple rows to be written at a time. Specify your structure as a list of JSON objects rather than a single JSON object.

d = {}
d['date'] = date_time()
d['status' ] = status
#a = json.dumps(d, indent = 2) # as a json string

qc=bigquery.Client(project=project_name)
dataset = qc.dataset(dataset)
table = dataset.table(table)
table_nm = qc.get_table(table)

errors = qc.insert_rows_json(
    table_nm,
    [d],  # Must be a list of objects, even if only 1 row.
)
for error in errors:
    print(f"encountered error: {error}")

Note: in the case of errors / retries, according to the BigQuery documentation on the streaming API "De-duplication offered by BigQuery is best effort, and it should not be relied upon as a mechanism to guarantee the absence of duplicates in your data" Therefore, the BigQuery documentation recommends periodically removing duplicates from the destination table when using the streaming API possibly by a scheduled query.

#standardSQL
SELECT
  * EXCEPT(row_number)
FROM (
  SELECT
    *,
    ROW_NUMBER()
          OVER (PARTITION BY ID_COLUMN) row_number
  FROM
    `TABLE_NAME`)
WHERE
  row_number = 1

For details on de-duplication, see the BigQuery streaming API guide

Upvotes: 4

Related Questions