Google Big Query

Related to SD-5810

To enable an external application to submit data to your BigQuery dataset using the tabledata.insertAll method, you need to follow these steps for authentication, authorization, and API implementation.

Please note: While the tabledata.insertAll method is marked as deprecated it still is the only option to submit data via HTTP POST requests as a RESTFUL API. Other options are recommended by Google BigQuery but require a different streaming approach and framework.

1. Set Up Authentication

Since your application is external to Google Cloud, you should use a Service Account to authenticate.

1. Create a Service Account: Go to the IAM & Admin console in Google Cloud and create a service account specifically for this application.

2. Generate a Key: Create and download a JSON key file for this service account. Your external application will use this file to authenticate.

3. Environment Variable: Set the environment variable GOOGLE_APPLICATION_CREDENTIALS to the path of your JSON key file on the server where your application runs.

2. Grant Permissions (IAM Roles)

The service account needs specific permissions to write data to your dataset. You should grant one of the following roles to the service account at the dataset or project level:

• roles/bigquery.dataEditor: Allows streaming inserts and modifying data.

• roles/bigquery.dataOwner: Full control over the dataset.

Specifically, the tabledata.insertAll method requires:

• bigquery.tables.updateData

• bigquery.tables.get

• bigquery.datasets.get

3. API Implementation (JSON Payload)

The tabledata.insertAll method (Legacy Streaming API) expects a POST request to the following endpoint: POST https://bigquery.googleapis.com/bigquery/v2/projects/{projectId}/datasets/MSDAC_JENTIS_CONNECTOR/tables/{tableId}/insertAll

Example JSON Payload: The rows must be wrapped in a json object within a rows array.

{
  "kind": "bigquery#tableDataInsertAllRequest",
  "rows": [
    {
      "insertId": "unique_id_1", 
      "json": {
        "column_name_1": "value1",
        "column_name_2": 123
      }
    }
  ]
}

• insertId: (Optional but recommended) Used for best-effort deduplication if the same request is sent twice.

4. Implementation Example (Python)

If you are using the BigQuery Client Library, the implementation is simplified:

from google.cloud import bigquery

client = bigquery.Client()
table_id = "msdac-generic-projects.MSDAC_JENTIS_CONNECTOR.your_table"

rows_to_insert = [
    {"column_name_1": "value1", "column_name_2": 123},
]

errors = client.insert_rows_json(table_id, rows_to_insert)

if errors == []:
    print("New rows have been added.")
else:
    print(f"Encountered errors: {errors}")

Recommendation

For new integrations, Google recommends using the BigQuery Storage Write API instead of tabledata.insertAll. It offers lower costs, better performance, and stronger reliability (exactly-once delivery). However, tabledata.insertAll remains fully supported.