Operations (sample payloads)
Main operations
Add or update columns in table
Add or update one or more columns in a table in BigQuery via the API, so you don't have to create them manually. Useful when you're looking to ensure that a table matches a schema before running an "Insert Rows" operation. Sample Input
{
"project_id": "my-bigquery-project",
"dataset_id": "sales_data",
"table_id": "customer_transactions",
"fields": [
\{
"name": "customer_id",
"mode": "REQUIRED",
"type": "STRING",
"description": "Unique identifier for the customer"
\},
\{
"name": "transaction_date",
"mode": "REQUIRED",
"type": "DATE",
"description": "Date of the transaction"
\},
\{
"name": "amount",
"mode": "REQUIRED",
"type": "FLOAT",
"description": "Transaction amount in USD"
\},
\{
"name": "product_category",
"mode": "NULLABLE",
"type": "STRING",
"description": "Category of the product purchased"
\},
{
"name": "customer_info",
"mode": "NULLABLE",
"type": "RECORD",
"description": "Nested record containing customer information",
"fields": [
\{
"name": "name",
"mode": "REQUIRED",
"type": "STRING",
"description": "Customer's full name"
\},
\{
"name": "email",
"mode": "NULLABLE",
"type": "STRING",
"description": "Customer's email address"
\}
]
}
]
}
Sample Output
{
"kind": "bigquery#table",
"etag": "\"mCOGFE+ego/Jm3g3HdKrcmcuy/M/rXbMbKfEG+4mYcI=\"",
"id": "my-bigquery-project:sales_data.customer_transactions",
"selfLink": "https://bigquery.googleapis.com/bigquery/v2/projects/my-bigquery-project/datasets/sales_data/tables/customer_transactions",
"tableReference": \{
"projectId": "my-bigquery-project",
"datasetId": "sales_data",
"tableId": "customer_transactions"
\},
"schema": {
"fields": [
\{
"name": "customer_id",
"type": "STRING",
"mode": "REQUIRED"
\},
\{
"name": "transaction_date",
"type": "DATE",
"mode": "REQUIRED"
\},
\{
"name": "amount",
"type": "FLOAT",
"mode": "REQUIRED"
\},
\{
"name": "product_category",
"type": "STRING",
"mode": "NULLABLE"
\},
{
"name": "customer_info",
"type": "RECORD",
"mode": "NULLABLE",
"fields": [
\{
"name": "name",
"type": "STRING",
"mode": "REQUIRED"
\},
\{
"name": "email",
"type": "STRING",
"mode": "NULLABLE"
\}
]
}
]
},
"numBytes": "0",
"numLongTermBytes": "0",
"numRows": "0",
"creationTime": "1625097600000",
"lastModifiedTime": "1625097600000",
"type": "TABLE"
}
Create table
Create a new, empty table in the dataset. Sample Input Sample Output
{
"kind": "bigquery#table",
"etag": "\"mTRxODTXWKzTJ0cCZLWzIw==\"",
"id": "my-bigquery-project:sales_data.transactions_2023",
"selfLink": "https://bigquery.googleapis.com/bigquery/v2/projects/my-bigquery-project/datasets/sales_data/tables/transactions_2023",
"tableReference": \{
"projectId": "my-bigquery-project",
"datasetId": "sales_data",
"tableId": "transactions_2023"
\},
"schema": {
"fields": [
\{
"name": "transaction_id",
"type": "STRING",
"mode": "REQUIRED",
"description": "Unique identifier for each transaction"
\},
\{
"name": "date",
"type": "DATE",
"mode": "REQUIRED",
"description": "Date of the transaction"
\},
\{
"name": "customer_id",
"type": "STRING",
"mode": "REQUIRED",
"description": "Unique identifier for the customer"
\},
\{
"name": "amount",
"type": "FLOAT",
"mode": "REQUIRED",
"description": "Total amount of the transaction"
\},
{
"name": "items",
"type": "RECORD",
"mode": "REPEATED",
"description": "List of items in the transaction",
"fields": [
\{
"name": "product_id",
"type": "STRING",
"mode": "REQUIRED",
"description": "Unique identifier for the product"
\},
\{
"name": "quantity",
"type": "INTEGER",
"mode": "REQUIRED",
"description": "Quantity of the product purchased"
\},
\{
"name": "unit_price",
"type": "FLOAT",
"mode": "REQUIRED",
"description": "Price per unit of the product"
\}
]
}
]
},
"numBytes": "0",
"numLongTermBytes": "0",
"numRows": "0",
"creationTime": "1625097600000",
"expirationTime": "1735689599000",
"lastModifiedTime": "1625097600000",
"type": "TABLE",
"location": "US"
}
Delete table
Delete the table specified by table_id from the dataset. If the table contains data, all the data will be deleted. Sample Input
\{
"project_id": "my-bigquery-project",
"dataset_id": "sales_data",
"table_id": "customer_transactions"
\}
Sample Output
Get job
Find a job by it's ID. Sample Input Sample Output
{
"kind": "bigquery#job",
"etag": "\"cX5UmbB_R-S07ii743IKGH9YCYM/MT\"",
"id": "my-bigquery-project:US.job_ABC123XYZ",
"selfLink": "https://bigquery.googleapis.com/bigquery/v2/projects/my-bigquery-project/jobs/job_ABC123XYZ",
"jobReference": \{
"projectId": "my-bigquery-project",
"jobId": "job_ABC123XYZ"
\},
"configuration": {
"load": {
"schema": {
"fields": [
\{
"name": "id",
"type": "INTEGER",
"mode": "REQUIRED"
\},
\{
"name": "name",
"type": "STRING",
"mode": "NULLABLE"
\},
\{
"name": "age",
"type": "INTEGER",
"mode": "NULLABLE"
\}
]
},
"destinationTable": \{
"projectId": "my-bigquery-project",
"datasetId": "my_dataset",
"tableId": "my_table"
\},
"fieldDelimiter": ",",
"skipLeadingRows": 1,
"maxBadRecords": 0,
"allowQuotedNewlines": false,
"sourceFormat": "CSV",
"allowJaggedRows": false,
"ignoreUnknownValues": false
}
},
"status": {
"state": "DONE"
},
"statistics": {
"creationTime": "1625097600000",
"startTime": "1625097605000",
"endTime": "1625097610000",
"load": \{
"inputFiles": "1",
"inputFileBytes": "1024",
"outputRows": "100",
"outputBytes": "2048"
\}
},
"user_email": "user@example.com"
}
Get table
Gets full details about a Google BigQuery table. Sample Input Sample Output
Insert job
Start a new asynchronous job. Sample Input
{
"project_id": "my-project-123",
"configuration": {
"job_type": "QUERY",
"query": {
"query": "SELECT * FROM my-project-123.my_dataset.my_table LIMIT 1000",
"destination_table": \{
"project_id": "my-project-123",
"dataset_id": "my_dataset",
"table_id": "query_results"
\},
"create_disposition": "CREATE_IF_NEEDED",
"write_disposition": "WRITE_TRUNCATE",
"use_legacy_sql": false,
"allow_large_results": true,
"priority": "INTERACTIVE",
"use_query_cache": true,
"maximum_bytes_billed": "1000000000"
}
},
"job_reference": \{
"project_id": "my-project-123",
"job_reference": "my_query_job_001",
"location": "US"
\}
}
Sample Output
{
"kind": "bigquery#job",
"etag": "\"cX5UmbB_R-S07ii743IKGH9YCYM/MT\"",
"id": "my-project-123:US.my_query_job_001",
"selfLink": "https://www.googleapis.com/bigquery/v2/projects/my-project-123/jobs/my_query_job_001",
"user_email": "user@example.com",
"configuration": {
"query": {
"query": "SELECT * FROM my-project-123.my_dataset.my_table LIMIT 1000",
"destinationTable": \{
"projectId": "my-project-123",
"datasetId": "my_dataset",
"tableId": "query_results"
\},
"createDisposition": "CREATE_IF_NEEDED",
"writeDisposition": "WRITE_TRUNCATE",
"priority": "INTERACTIVE",
"allowLargeResults": true,
"useQueryCache": true,
"useLegacySql": false,
"maximumBytesBilled": "1000000000"
},
"jobType": "QUERY"
},
"jobReference": \{
"projectId": "my-project-123",
"jobId": "my_query_job_001",
"location": "US"
\},
"statistics": \{
"creationTime": "1625097600000",
"startTime": "1625097601000",
"endTime": "1625097605000"
\},
"status": {
"state": "DONE"
}
}
Insert rows
Inserts up to 500 rows into a Google BigQuery table. Sample Input
{
"project_id": "my-bigquery-project",
"dataset_id": "sales_data",
"table_id": "transactions",
"rows": [
{
"data": \{
"transaction_id": "TX123456",
"customer_name": "John Doe",
"product": "Widget A",
"quantity": 5,
"price": 19.99,
"date": "2023-05-15"
\},
"insert_id": "INS001"
},
{
"data": \{
"transaction_id": "TX123457",
"customer_name": "Jane Smith",
"product": "Widget B",
"quantity": 2,
"price": 29.99,
"date": "2023-05-15"
\},
"insert_id": "INS002"
}
]
}
Sample Output
\{
"kind": "bigquery#tableDataInsertAllResponse",
"success": true
\}
Insert rows from CSV file
Uploads a CSV file to BigQuery, saves the rows into a table. Sample Input
{
"project_id": "my-bigquery-project",
"dataset_id": "sales_data",
"table_id": "monthly_sales",
"file": \{
"name": "sales_june_2023.csv",
"content": "date,product,quantity,revenue\n2023-06-01,Widget A,100,5000\n2023-06-02,Widget B,75,4500\n2023-06-03,Widget C,50,3000"
\},
"import_options": \{
"allow_jagged_rows": true,
"skip_leading_rows": 1,
"allow_quoted_new_lines": true,
"null_marker": "NULL",
"ignore_unknown_values": true
\}
}
Sample Output
{
"kind": "bigquery#job",
"etag": "\"cX5UmbB_R-S07ii743IKGH9YCYM/MT\"",
"id": "my-bigquery-project:US.job_ABC123XYZ",
"selfLink": "https://bigquery.googleapis.com/bigquery/v2/projects/my-bigquery-project/jobs/job_ABC123XYZ",
"user_email": "user@example.com",
"configuration": {
"load": {
"schema": {
"fields": [
\{
"name": "date",
"type": "DATE",
"mode": "NULLABLE"
\},
\{
"name": "product",
"type": "STRING",
"mode": "NULLABLE"
\},
\{
"name": "quantity",
"type": "INTEGER",
"mode": "NULLABLE"
\},
\{
"name": "revenue",
"type": "FLOAT",
"mode": "NULLABLE"
\}
]
},
"destinationTable": \{
"projectId": "my-bigquery-project",
"datasetId": "sales_data",
"tableId": "monthly_sales"
\},
"nullMarker": "NULL",
"skipLeadingRows": 1,
"allowQuotedNewlines": true,
"allowJaggedRows": true,
"ignoreUnknownValues": true
},
"jobType": "LOAD"
},
"jobReference": \{
"projectId": "my-bigquery-project",
"jobId": "job_ABC123XYZ",
"location": "US"
\},
"statistics": \{
"creationTime": "1625097600000",
"startTime": "1625097601000"
\},
"status": {
"state": "RUNNING"
}
}
List datasets
List all the datasets for a project. Sample Input Sample Output
{
"datasets": [
\{
"datasetId": "sales_data",
"projectId": "my-bigquery-project",
"location": "US",
"creationTime": "1625097600000",
"lastModifiedTime": "1640995200000",
"description": "Dataset containing sales information"
\},
\{
"datasetId": "customer_data",
"projectId": "my-bigquery-project",
"location": "EU",
"creationTime": "1627776000000",
"lastModifiedTime": "1641081600000",
"description": "Dataset containing customer information"
\},
\{
"datasetId": "analytics",
"projectId": "my-bigquery-project",
"location": "US",
"creationTime": "1630454400000",
"lastModifiedTime": "1641168000000",
"description": "Dataset for analytics and reporting"
\}
],
"nextPageToken": "eyJwYWdlX3NpemUiOjEwMCwicGFnZV90b2tlbiI6IlpZWFdWVVRTUlFQT05NTEtKSUhHRkVEQ0JBIiwidG90YWxfcm93cyI6NTAwfQ=="
}
List job query results
List the results of a query job. Sample Input
\{
"project_id": "my-bigquery-project",
"job_id": "job_abcdefghijklmnop",
"location": "US",
"use_int64_timestamp": false,
"timeout_ms": 15000,
"max_results": 100,
"page_token": "next_page_token_123",
"start_index": "0",
"raw_response": false
\}
Sample Output
{
"kind": "bigquery#getQueryResultsResponse",
"etag": "\"abcdefghijklmnop\"",
"schema": {
"fields": [
\{
"name": "name",
"type": "STRING",
"mode": "NULLABLE"
\},
\{
"name": "age",
"type": "INTEGER",
"mode": "NULLABLE"
\},
\{
"name": "city",
"type": "STRING",
"mode": "NULLABLE"
\}
]
},
"jobReference": \{
"projectId": "my-bigquery-project",
"jobId": "job_abcdefghijklmnop",
"location": "US"
\},
"totalRows": "1000",
"pageToken": "next_page_token_456",
"rows": [
{
"f": [
{
"v": "John Doe"
},
{
"v": "30"
},
{
"v": "New York"
}
]
},
{
"f": [
{
"v": "Jane Smith"
},
{
"v": "25"
},
{
"v": "Los Angeles"
}
]
}
],
"totalBytesProcessed": "1024",
"jobComplete": true,
"cacheHit": false
}
List jobs
List all the jobs for a project. Sample Input
\{
"project_id": "my-bigquery-project",
"all_users": true,
"projection": "full",
"state_filter": "running",
"max_results": 10,
"page_token": "abc123xyz"
\}
Sample Output
{
"kind": "bigquery#jobList",
"etag": "\"cX5UmbB_R-S07ii743USF__fAYM/XYZ\"",
"jobs": [
{
"id": "my-bigquery-project:job_ABC123",
"kind": "bigquery#job",
"jobReference": \{
"projectId": "my-bigquery-project",
"jobId": "job_ABC123"
\},
"state": "RUNNING",
"statistics": \{
"creationTime": "1625097600000",
"startTime": "1625097605000"
\},
"status": {
"state": "RUNNING"
}
},
{
"id": "my-bigquery-project:job_DEF456",
"kind": "bigquery#job",
"jobReference": \{
"projectId": "my-bigquery-project",
"jobId": "job_DEF456"
\},
"state": "RUNNING",
"statistics": \{
"creationTime": "1625097700000",
"startTime": "1625097705000"
\},
"status": {
"state": "RUNNING"
}
}
]
}
List projects
List all the projects you have in your Google BigQuery account. Sample Input
\{
"max_results": 3,
"page_token": "abc123xyz"
\}
Sample Output
{
"kind": "bigquery#projectList",
"etag": "\"cX5UmbB_R-S07ii743AICX__EYc/XYZ\"",
"projects": [
{
"kind": "bigquery#project",
"id": "project-1",
"numericId": "1234567890",
"projectReference": {
"projectId": "project-1"
},
"friendlyName": "My First Project"
},
{
"kind": "bigquery#project",
"id": "project-2",
"numericId": "2345678901",
"projectReference": {
"projectId": "project-2"
},
"friendlyName": "Analytics Project"
},
{
"kind": "bigquery#project",
"id": "project-3",
"numericId": "3456789012",
"projectReference": {
"projectId": "project-3"
},
"friendlyName": "Data Warehouse"
}
],
"totalItems": 3
}
List tables
List all the tables for a dataset. Sample Input
\{
"project_id": "my-bigquery-project",
"dataset_id": "sales_data",
"max_results": 3,
"page_token": "abc123xyz"
\}
Sample Output
{
"kind": "bigquery#tableList",
"etag": "\"cX5UmbB_R-S07ii743USAQ==\"",
"tables": [
{
"kind": "bigquery#table",
"id": "my-bigquery-project:sales_data.customers",
"tableReference": \{
"projectId": "my-bigquery-project",
"datasetId": "sales_data",
"tableId": "customers"
\},
"type": "TABLE",
"creationTime": "1625097600000"
},
{
"kind": "bigquery#table",
"id": "my-bigquery-project:sales_data.orders",
"tableReference": \{
"projectId": "my-bigquery-project",
"datasetId": "sales_data",
"tableId": "orders"
\},
"type": "TABLE",
"creationTime": "1625184000000"
},
{
"kind": "bigquery#table",
"id": "my-bigquery-project:sales_data.products",
"tableReference": \{
"projectId": "my-bigquery-project",
"datasetId": "sales_data",
"tableId": "products"
\},
"type": "TABLE",
"creationTime": "1625270400000"
}
],
"totalItems": 3
}
Run query
Create a query job. Sample Input
\{
"project_id": "my-bigquery-project",
"query": "SELECT id, email, first_name, last_name FROM my-dataset.users LIMIT 5",
"max_results": 5,
"timeout_ms": 15000,
"dry_run": false,
"use_query_cache": true,
"raw_response": false
\}
Sample Output
{
"kind": "bigquery#queryResponse",
"schema": {
"fields": [
\{
"name": "id",
"type": "INTEGER",
"mode": "NULLABLE"
\},
\{
"name": "email",
"type": "STRING",
"mode": "NULLABLE"
\},
\{
"name": "first_name",
"type": "STRING",
"mode": "NULLABLE"
\},
\{
"name": "last_name",
"type": "STRING",
"mode": "NULLABLE"
\}
]
},
"jobReference": \{
"projectId": "my-bigquery-project",
"jobId": "job_abcdefghijklmnop"
\},
"totalRows": "5",
"pageToken": "ABCDEFGHIJKLMNOP",
"rows": [
\{
"id": "1",
"email": "john.doe@example.com",
"first_name": "John",
"last_name": "Doe"
\},
\{
"id": "2",
"email": "jane.smith@example.com",
"first_name": "Jane",
"last_name": "Smith"
\},
\{
"id": "3",
"email": "bob.johnson@example.com",
"first_name": "Bob",
"last_name": "Johnson"
\},
\{
"id": "4",
"email": "alice.williams@example.com",
"first_name": "Alice",
"last_name": "Williams"
\},
\{
"id": "5",
"email": "charlie.brown@example.com",
"first_name": "Charlie",
"last_name": "Brown"
\}
],
"totalBytesProcessed": "1024",
"jobComplete": true,
"cacheHit": false
}
DDL operations
List datasets (DDL)
Note that DDL operations can only be called directly by Connectors API, or when using CustomJS in the Embedded solution editor for e.g. DDL-dependent data mapping
Sample Input
{}
Sample Output
List jobs (DDL)
Note that DDL operations can only be called directly by Connectors API, or when using CustomJS in the Embedded solution editor for e.g. DDL-dependent data mapping
Sample Input
{}
Sample Output
[
{
"id": "project-id:us-central1.job_ABC123XYZ",
"jobType": "QUERY",
"state": "DONE",
"creationTime": "1625097600000",
"startTime": "1625097601000",
"endTime": "1625097605000",
"user": "user@example.com",
"query": {
"query": "SELECT * FROM dataset.table LIMIT 1000",
"destinationTable": \{
"projectId": "project-id",
"datasetId": "temp_dataset",
"tableId": "job_result_ABC123XYZ"
\},
"createDisposition": "CREATE_IF_NEEDED",
"writeDisposition": "WRITE_TRUNCATE"
}
},
{
"id": "project-id:us-central1.job_DEF456UVW",
"jobType": "LOAD",
"state": "RUNNING",
"creationTime": "1625097700000",
"startTime": "1625097701000",
"user": "another-user@example.com",
"configuration": {
"load": {
"sourceUris": [
"gs://my-bucket/data.csv"
],
"destinationTable": \{
"projectId": "project-id",
"datasetId": "my_dataset",
"tableId": "new_table"
\},
"schema": {
"fields": [
\{
"name": "column1",
"type": "STRING"
\},
\{
"name": "column2",
"type": "INTEGER"
\}
]
},
"createDisposition": "CREATE_IF_NEEDED",
"writeDisposition": "WRITE_APPEND"
}
}
}
]
List projects (DDL)
Note that DDL operations can only be called directly by Connectors API, or when using CustomJS in the Embedded solution editor for e.g. DDL-dependent data mapping
Sample Input
{}
Sample Output
[
\{
"projectId": "my-project-123",
"projectNumber": "123456789012",
"name": "My Project",
"createTime": "2023-05-15T10:30:45.123Z",
"state": "ACTIVE"
\},
\{
"projectId": "another-project-456",
"projectNumber": "456789012345",
"name": "Another Project",
"createTime": "2023-04-01T08:15:30.456Z",
"state": "ACTIVE"
\},
\{
"projectId": "test-project-789",
"projectNumber": "789012345678",
"name": "Test Project",
"createTime": "2023-03-10T14:20:00.789Z",
"state": "ACTIVE"
\}
]
List tables (DDL)
Note that DDL operations can only be called directly by Connectors API, or when using CustomJS in the Embedded solution editor for e.g. DDL-dependent data mapping
Sample Input
{}
Sample Output
[
\{
"table_name": "users",
"ddl": "CREATE TABLE project_id.dataset_name.users (\n user_id INT64 NOT NULL,\n username STRING,\n email STRING,\n created_at TIMESTAMP,\n last_login TIMESTAMP\n) PARTITION BY DATE(created_at)\nCLUSTER BY user_id"
\},
\{
"table_name": "orders",
"ddl": "CREATE TABLE project_id.dataset_name.orders (\n order_id INT64 NOT NULL,\n user_id INT64,\n order_date DATE,\n total_amount FLOAT64,\n status STRING\n) PARTITION BY order_date\nCLUSTER BY user_id"
\},
\{
"table_name": "products",
"ddl": "CREATE TABLE project_id.dataset_name.products (\n product_id INT64 NOT NULL,\n name STRING,\n description STRING,\n price FLOAT64,\n category STRING\n)"
\}
]