> ## Documentation Index
> Fetch the complete documentation index at: https://docs.pinkfish.ai/llms.txt
> Use this file to discover all available pages before exploring further.
# google-bigquery
> SQL queries, datasets, and tables
**Server path:** `/google-bigquery` | **Type:** Application | **PCID required:** Yes
## Tools
| Tool | Description |
| --------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| [`google-bigquery_datasets_delete`](#google-bigquery_datasets_delete) | Deletes the dataset specified by the datasetId value. Before you can delete a dataset, you must delete all its tables, either manually or by specifying deleteContents. Immediately after deletion, you can create another dataset with the same name. |
| [`google-bigquery_datasets_get`](#google-bigquery_datasets_get) | Returns the dataset specified by datasetID. |
| [`google-bigquery_datasets_insert`](#google-bigquery_datasets_insert) | Creates a new empty dataset. |
| [`google-bigquery_datasets_list`](#google-bigquery_datasets_list) | Lists all datasets in the specified project to which the user has been granted the READER dataset role. |
| [`google-bigquery_datasets_patch`](#google-bigquery_datasets_patch) | Updates information in an existing dataset. The update method replaces the entire dataset resource, whereas the patch method only replaces fields that are provided in the submitted dataset resource. This method supports RFC5789 patch semantics. |
| [`google-bigquery_execute_sql_query`](#google-bigquery_execute_sql_query) | Runs a BigQuery SQL query synchronously and returns query results if the query completes within a specified timeout. |
| [`google-bigquery_insert_rows`](#google-bigquery_insert_rows) | Streams data into BigQuery one record at a time without needing to run a load job. |
| [`google-bigquery_jobs_cancel`](#google-bigquery_jobs_cancel) | Requests that a job be cancelled. This call will return immediately, and the client will need to poll for the job status to see if the cancel completed successfully. Cancelled jobs may still incur costs. |
| [`google-bigquery_jobs_delete`](#google-bigquery_jobs_delete) | Requests the deletion of the metadata of a job. This call returns when the job's metadata is deleted. |
| [`google-bigquery_jobs_get`](#google-bigquery_jobs_get) | Returns information about a specific job. Job information is available for a six month period after creation. Requires that you're the person who ran the job, or have the Is Owner project role. |
| [`google-bigquery_jobs_get_query_results`](#google-bigquery_jobs_get_query_results) | RPC to get the results of a query job. |
| [`google-bigquery_jobs_list`](#google-bigquery_jobs_list) | Lists all jobs that you started in the specified project. Job information is available for a six month period after creation. The job list is sorted in reverse chronological order, by job creation time. Requires the Can View project role, or the Is Owner project role if you set the allUsers property. |
| [`google-bigquery_list_row_access_policies`](#google-bigquery_list_row_access_policies) | Lists all row access policies on the specified table. |
| [`google-bigquery_list_table_data`](#google-bigquery_list_table_data) | List the content of a table in rows. |
| [`google-bigquery_models_delete`](#google-bigquery_models_delete) | Deletes the model specified by modelId from the dataset. |
| [`google-bigquery_models_get`](#google-bigquery_models_get) | Gets the specified model resource by model ID. |
| [`google-bigquery_models_list`](#google-bigquery_models_list) | Lists all models in the specified dataset. Requires the READER dataset role. After retrieving the list of models, you can get information about a particular model by calling the models.get method. |
| [`google-bigquery_models_patch`](#google-bigquery_models_patch) | Patch specific fields in the specified model. |
| [`google-bigquery_projects_list`](#google-bigquery_projects_list) | RPC to list projects to which the user has been granted any project role. Users of this method are encouraged to consider the [Resource Manager](https://cloud.google.com/resource-manager/docs/) API, which provides the underlying data for this method and has more capabilities. |
| [`google-bigquery_routines_delete`](#google-bigquery_routines_delete) | Deletes the routine specified by routineId from the dataset. |
| [`google-bigquery_routines_get`](#google-bigquery_routines_get) | Gets the specified routine resource by routine ID. |
| [`google-bigquery_routines_insert`](#google-bigquery_routines_insert) | Creates a new routine in the dataset. |
| [`google-bigquery_routines_list`](#google-bigquery_routines_list) | Lists all routines in the specified dataset. Requires the READER dataset role. |
| [`google-bigquery_routines_update`](#google-bigquery_routines_update) | Updates information in an existing routine. The update method replaces the entire Routine resource. |
| [`google-bigquery_tables_delete`](#google-bigquery_tables_delete) | Deletes the table specified by tableId from the dataset. If the table contains data, all the data will be deleted. |
| [`google-bigquery_tables_get`](#google-bigquery_tables_get) | Gets the specified table resource by table ID. This method does not return the data in the table, it only returns the table resource, which describes the structure of this table. |
| [`google-bigquery_tables_insert`](#google-bigquery_tables_insert) | Creates a new, empty table in the dataset. |
| [`google-bigquery_tables_list`](#google-bigquery_tables_list) | Lists all tables in the specified dataset. Requires the READER dataset role. |
| [`google-bigquery_tables_patch`](#google-bigquery_tables_patch) | Updates information in an existing table. The update method replaces the entire table resource, whereas the patch method only replaces fields that are provided in the submitted table resource. This method supports RFC5789 patch semantics. |
| [`google-bigquery_undelete_dataset`](#google-bigquery_undelete_dataset) | Undeletes a dataset which is within time travel window based on datasetId. If a time is specified, the dataset version deleted at that time is undeleted, else the last live version is undeleted. |
***
## google-bigquery\_datasets\_delete
Deletes the dataset specified by the datasetId value. Before you can delete a dataset, you must delete all its tables, either manually or by specifying deleteContents. Immediately after deletion, you can create another dataset with the same name.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ---------------- | ------- | -------- | ------- | -------------------------------------------------------------------------------------------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the dataset being deleted |
| `datasetId` | string | Yes | — | Required. Dataset ID of dataset being deleted |
| `deleteContents` | boolean | No | — | If True, delete all the tables in the dataset. If False and the dataset contains tables, the request will fail. Default is False |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the dataset being deleted"
},
"datasetId": {
"type": "string",
"description": "Required. Dataset ID of dataset being deleted"
},
"deleteContents": {
"type": "boolean",
"description": "If True, delete all the tables in the dataset. If False and the dataset contains tables, the request will fail. Default is False"
}
},
"required": [
"PCID",
"projectId",
"datasetId"
]
}
```
***
## google-bigquery\_datasets\_get
Returns the dataset specified by datasetID.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ------------- | ------ | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------ |
| `projectId` | string | Yes | — | Required. Project ID of the requested dataset |
| `datasetId` | string | Yes | — | Required. Dataset ID of the requested dataset |
| `datasetView` | string | No | — | Optional. Specifies the view that determines which dataset information is returned. By default, metadata and ACL information are returned. |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the requested dataset"
},
"datasetId": {
"type": "string",
"description": "Required. Dataset ID of the requested dataset"
},
"datasetView": {
"type": "string",
"description": "Optional. Specifies the view that determines which dataset information is returned. By default, metadata and ACL information are returned.",
"enum": [
"DATASET_VIEW_UNSPECIFIED",
"METADATA",
"ACL",
"FULL"
]
}
},
"required": [
"PCID",
"projectId",
"datasetId"
]
}
```
***
## google-bigquery\_datasets\_insert
Creates a new empty dataset.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| -------------------------------- | --------- | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the new dataset |
| `access` | object\[] | No | — | Optional. An array of objects that define dataset access for one or more entities. You can set this property when inserting or updating a dataset in order to control who is allowed to access the data. If unspecified at dataset creation time, BigQuery adds default dataset access for the following entities: access.specialGroup: projectReaders; access.role: READER; access.specialGroup: projectWriters; access.role: WRITER; access.specialGroup: projectOwners; access.role: OWNER; access.userByEmail: \[dataset creator email]; access.role: OWNER; |
| `creationTime` | string | No | — | Output only. The time when this dataset was created, in milliseconds since the epoch. |
| `datasetReference` | object | No | — | Dataset Reference |
| `defaultCollation` | string | No | — | Optional. Defines the default collation specification of future tables created in the dataset. If a table is created in this dataset without table-level default collation, then the table inherits the dataset default collation, which is applied to the string fields that do not have explicit collation specified. A change to this field affects only tables created afterwards, and does not alter the existing tables. The following values are supported: \* 'und:ci': undetermined locale, case insensitive. \* '': empty string. Default to case-sensitive behavior. |
| `defaultEncryptionConfiguration` | object | No | — | Default Encryption Configuration |
| `defaultPartitionExpirationMs` | string | No | — | This default partition expiration, expressed in milliseconds. When new time-partitioned tables are created in a dataset where this property is set, the table will inherit this value, propagated as the `TimePartitioning.expirationMs` property on the new table. If you set `TimePartitioning.expirationMs` explicitly when creating a table, the `defaultPartitionExpirationMs` of the containing dataset is ignored. When creating a partitioned table, if `defaultPartitionExpirationMs` is set, the `defaultTableExpirationMs` value is ignored and the table will not be inherit a table expiration deadline. |
| `defaultRoundingMode` | string | No | — | Optional. Defines the default rounding mode specification of new tables created within this dataset. During table creation, if this field is specified, the table within this dataset will inherit the default rounding mode of the dataset. Setting the default rounding mode on a table overrides this option. Existing tables in the dataset are unaffected. If columns are defined during that table creation, they will immediately inherit the table's default rounding mode, unless otherwise specified. |
| `defaultTableExpirationMs` | string | No | — | Optional. The default lifetime of all tables in the dataset, in milliseconds. The minimum lifetime value is 3600000 milliseconds (one hour). To clear an existing default expiration with a PATCH request, set to 0. Once this property is set, all newly-created tables in the dataset will have an expirationTime property set to the creation time plus the value in this property, and changing the value will only affect new tables, not existing ones. When the expirationTime for a given table is reached, that table will be deleted automatically. If a table's expirationTime is modified or removed before the table expires, or if you provide an explicit expirationTime when creating a table, that value takes precedence over the default expiration time indicated by this property. |
| `description` | string | No | — | Optional. A user-friendly description of the dataset. |
| `etag` | string | No | — | Output only. A hash of the resource. |
| `externalDatasetReference` | object | No | — | Configures the access a dataset defined in an external metadata storage. |
| `friendlyName` | string | No | — | Optional. A descriptive name for the dataset. |
| `id` | string | No | — | Output only. The fully-qualified unique name of the dataset in the format projectId:datasetId. The dataset name without the project name is given in the datasetId field. When creating a new dataset, leave this field blank, and instead specify the datasetId field. |
| `isCaseInsensitive` | boolean | No | — | Optional. TRUE if the dataset and its table names are case-insensitive, otherwise FALSE. By default, this is FALSE, which means the dataset and its table names are case-sensitive. This field does not affect routine references. |
| `kind` | string | No | — | Output only. The resource type. |
| `labels` | object | No | — | The labels associated with this dataset. You can use these to organize and group your datasets. You can set this property when inserting or updating a dataset. See Creating and Updating Dataset Labels for more information. |
| `lastModifiedTime` | string | No | — | Output only. The date when this dataset was last modified, in milliseconds since the epoch. |
| `linkedDatasetSource` | object | No | — | A dataset source type which refers to another BigQuery dataset. |
| `location` | string | No | — | The geographic location where the dataset should reside. See [https://cloud.google.com/bigquery/docs/locations](https://cloud.google.com/bigquery/docs/locations) for supported locations. |
| `maxTimeTravelHours` | string | No | — | Optional. Defines the time travel window in hours. The value can be from 48 to 168 hours (2 to 7 days). The default value is 168 hours if this is not set. |
| `satisfiesPzi` | boolean | No | — | Output only. Reserved for future use. |
| `satisfiesPzs` | boolean | No | — | Output only. Reserved for future use. |
| `selfLink` | string | No | — | Output only. A URL that can be used to access the resource again. You can use this URL in Get or Update requests to the resource. |
| `storageBillingModel` | string | No | — | Optional. Updates storage\_billing\_model for the dataset. |
| `tags` | object\[] | No | — | Output only. Tags for the Dataset. |
| `type` | string | No | — | Output only. Same as `type` in `ListFormatDataset`. The type of the dataset, one of: \* DEFAULT - only accessible by owner and authorized accounts, \* PUBLIC - accessible by everyone, \* LINKED - linked dataset, \* EXTERNAL - dataset with definition in external metadata catalog. -- \*BIGLAKE\_METASTORE - dataset that references a database created in BigLakeMetastore service. -- |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the new dataset"
},
"access": {
"type": "array",
"items": {
"type": "object",
"properties": {
"dataset": {
"type": "object",
"description": "Grants all resources of particular types in a particular dataset read access to the current dataset. Similar to how individually authorized views work, updates to any resource granted through its dataset (including creation of new resources) requires read permission to referenced resources, plus write permission to the authorizing dataset."
},
"domain": {
"type": "string",
"description": "[Pick one] A domain to grant access to. Any users signed in with the domain specified will be granted the specified access. Example: \"example.com\". Maps to IAM policy member \"domain:DOMAIN\"."
},
"groupByEmail": {
"type": "string",
"description": "[Pick one] An email address of a Google Group to grant access to. Maps to IAM policy member \"group:GROUP\"."
},
"iamMember": {
"type": "string",
"description": "[Pick one] Some other type of member that appears in the IAM Policy but isn't a user, group, domain, or special group."
},
"role": {
"type": "string",
"description": "An IAM role ID that should be granted to the user, group, or domain specified in this access entry. The following legacy mappings will be applied: OWNER <=> roles/bigquery.dataOwner WRITER <=> roles/bigquery.dataEditor READER <=> roles/bigquery.dataViewer This field will accept any of the above formats, but will return only the legacy format. For example, if you set this field to \"roles/bigquery.dataOwner\", it will be returned back as \"OWNER\"."
},
"routine": {
"type": "object",
"description": "Id path of a routine."
},
"specialGroup": {
"type": "string",
"description": "[Pick one] A special group to grant access to. Possible values include: projectOwners: Owners of the enclosing project. projectReaders: Readers of the enclosing project. projectWriters: Writers of the enclosing project. allAuthenticatedUsers: All authenticated BigQuery users. Maps to similarly-named IAM members."
},
"userByEmail": {
"type": "string",
"description": "[Pick one] An email address of a user to grant access to. For example: fred@example.com. Maps to IAM policy member \"user:EMAIL\" or \"serviceAccount:EMAIL\"."
},
"view": {
"type": "object",
"description": "The view value"
}
}
},
"description": "Optional. An array of objects that define dataset access for one or more entities. You can set this property when inserting or updating a dataset in order to control who is allowed to access the data. If unspecified at dataset creation time, BigQuery adds default dataset access for the following entities: access.specialGroup: projectReaders; access.role: READER; access.specialGroup: projectWriters; access.role: WRITER; access.specialGroup: projectOwners; access.role: OWNER; access.userByEmail: [dataset creator email]; access.role: OWNER;"
},
"creationTime": {
"type": "string",
"description": "Output only. The time when this dataset was created, in milliseconds since the epoch."
},
"datasetReference": {
"type": "object",
"description": "Dataset Reference",
"properties": {
"datasetId": {
"type": "string",
"description": "Required. A unique ID for this dataset, without the project name. The ID must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_). The maximum length is 1,024 characters."
},
"projectId": {
"type": "string",
"description": "Optional. The ID of the project containing this dataset."
}
}
},
"defaultCollation": {
"type": "string",
"description": "Optional. Defines the default collation specification of future tables created in the dataset. If a table is created in this dataset without table-level default collation, then the table inherits the dataset default collation, which is applied to the string fields that do not have explicit collation specified. A change to this field affects only tables created afterwards, and does not alter the existing tables. The following values are supported: * 'und:ci': undetermined locale, case insensitive. * '': empty string. Default to case-sensitive behavior."
},
"defaultEncryptionConfiguration": {
"type": "object",
"description": "Default Encryption Configuration",
"properties": {
"kmsKeyName": {
"type": "string",
"description": "Optional. Describes the Cloud KMS encryption key that will be used to protect destination BigQuery table. The BigQuery Service Account associated with your project requires access to this encryption key."
}
}
},
"defaultPartitionExpirationMs": {
"type": "string",
"description": "This default partition expiration, expressed in milliseconds. When new time-partitioned tables are created in a dataset where this property is set, the table will inherit this value, propagated as the `TimePartitioning.expirationMs` property on the new table. If you set `TimePartitioning.expirationMs` explicitly when creating a table, the `defaultPartitionExpirationMs` of the containing dataset is ignored. When creating a partitioned table, if `defaultPartitionExpirationMs` is set, the `defaultTableExpirationMs` value is ignored and the table will not be inherit a table expiration deadline."
},
"defaultRoundingMode": {
"type": "string",
"description": "Optional. Defines the default rounding mode specification of new tables created within this dataset. During table creation, if this field is specified, the table within this dataset will inherit the default rounding mode of the dataset. Setting the default rounding mode on a table overrides this option. Existing tables in the dataset are unaffected. If columns are defined during that table creation, they will immediately inherit the table's default rounding mode, unless otherwise specified.",
"enum": [
"ROUNDING_MODE_UNSPECIFIED",
"ROUND_HALF_AWAY_FROM_ZERO",
"ROUND_HALF_EVEN"
]
},
"defaultTableExpirationMs": {
"type": "string",
"description": "Optional. The default lifetime of all tables in the dataset, in milliseconds. The minimum lifetime value is 3600000 milliseconds (one hour). To clear an existing default expiration with a PATCH request, set to 0. Once this property is set, all newly-created tables in the dataset will have an expirationTime property set to the creation time plus the value in this property, and changing the value will only affect new tables, not existing ones. When the expirationTime for a given table is reached, that table will be deleted automatically. If a table's expirationTime is modified or removed before the table expires, or if you provide an explicit expirationTime when creating a table, that value takes precedence over the default expiration time indicated by this property."
},
"description": {
"type": "string",
"description": "Optional. A user-friendly description of the dataset."
},
"etag": {
"type": "string",
"description": "Output only. A hash of the resource."
},
"externalDatasetReference": {
"type": "object",
"description": "Configures the access a dataset defined in an external metadata storage.",
"properties": {
"connection": {
"type": "string",
"description": "Required. The connection id that is used to access the external_source. Format: projects/{project_id}/locations/{location_id}/connections/{connection_id}"
},
"externalSource": {
"type": "string",
"description": "Required. External source that backs this dataset."
}
}
},
"friendlyName": {
"type": "string",
"description": "Optional. A descriptive name for the dataset."
},
"id": {
"type": "string",
"description": "Output only. The fully-qualified unique name of the dataset in the format projectId:datasetId. The dataset name without the project name is given in the datasetId field. When creating a new dataset, leave this field blank, and instead specify the datasetId field."
},
"isCaseInsensitive": {
"type": "boolean",
"description": "Optional. TRUE if the dataset and its table names are case-insensitive, otherwise FALSE. By default, this is FALSE, which means the dataset and its table names are case-sensitive. This field does not affect routine references."
},
"kind": {
"type": "string",
"description": "Output only. The resource type."
},
"labels": {
"type": "object",
"description": "The labels associated with this dataset. You can use these to organize and group your datasets. You can set this property when inserting or updating a dataset. See Creating and Updating Dataset Labels for more information."
},
"lastModifiedTime": {
"type": "string",
"description": "Output only. The date when this dataset was last modified, in milliseconds since the epoch."
},
"linkedDatasetSource": {
"type": "object",
"description": "A dataset source type which refers to another BigQuery dataset.",
"properties": {
"sourceDataset": {
"type": "object",
"description": "Source Dataset"
}
}
},
"location": {
"type": "string",
"description": "The geographic location where the dataset should reside. See https://cloud.google.com/bigquery/docs/locations for supported locations."
},
"maxTimeTravelHours": {
"type": "string",
"description": "Optional. Defines the time travel window in hours. The value can be from 48 to 168 hours (2 to 7 days). The default value is 168 hours if this is not set."
},
"satisfiesPzi": {
"type": "boolean",
"description": "Output only. Reserved for future use."
},
"satisfiesPzs": {
"type": "boolean",
"description": "Output only. Reserved for future use."
},
"selfLink": {
"type": "string",
"description": "Output only. A URL that can be used to access the resource again. You can use this URL in Get or Update requests to the resource."
},
"storageBillingModel": {
"type": "string",
"description": "Optional. Updates storage_billing_model for the dataset.",
"enum": [
"STORAGE_BILLING_MODEL_UNSPECIFIED",
"LOGICAL",
"PHYSICAL"
]
},
"tags": {
"type": "array",
"items": {
"type": "object",
"properties": {
"tagKey": {
"type": "string",
"description": "Required. The namespaced friendly name of the tag key, e.g. \"12345/environment\" where 12345 is org id."
},
"tagValue": {
"type": "string",
"description": "Required. The friendly short name of the tag value, e.g. \"production\"."
}
}
},
"description": "Output only. Tags for the Dataset."
},
"type": {
"type": "string",
"description": "Output only. Same as `type` in `ListFormatDataset`. The type of the dataset, one of: * DEFAULT - only accessible by owner and authorized accounts, * PUBLIC - accessible by everyone, * LINKED - linked dataset, * EXTERNAL - dataset with definition in external metadata catalog. -- *BIGLAKE_METASTORE - dataset that references a database created in BigLakeMetastore service. --"
}
},
"required": [
"PCID",
"projectId"
]
}
```
***
## google-bigquery\_datasets\_list
Lists all datasets in the specified project to which the user has been granted the READER dataset role.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ------------ | ------- | -------- | ------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the datasets to be listed |
| `all` | boolean | No | — | Whether to list all datasets, including hidden ones |
| `filter` | string | No | — | An expression for filtering the results of the request by label. The syntax is "labels.\\[:\]". Multiple filters can be ANDed together by connecting with a space. Example: "labels.department:receiving labels.active". See [Filtering datasets using labels](/bigquery/docs/labeling-datasets#filtering_datasets_using_labels) for details. |
| `maxResults` | integer | No | — | The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection. |
| `pageToken` | string | No | — | Page token, returned by a previous call, to request the next page of results |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the datasets to be listed"
},
"all": {
"type": "boolean",
"description": "Whether to list all datasets, including hidden ones"
},
"filter": {
"type": "string",
"description": "An expression for filtering the results of the request by label. The syntax is \\\"labels.[:]\\\". Multiple filters can be ANDed together by connecting with a space. Example: \\\"labels.department:receiving labels.active\\\". See [Filtering datasets using labels](/bigquery/docs/labeling-datasets#filtering_datasets_using_labels) for details."
},
"maxResults": {
"type": "integer",
"description": "The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection."
},
"pageToken": {
"type": "string",
"description": "Page token, returned by a previous call, to request the next page of results"
}
},
"required": [
"PCID",
"projectId"
]
}
```
***
## google-bigquery\_datasets\_patch
Updates information in an existing dataset. The update method replaces the entire dataset resource, whereas the patch method only replaces fields that are provided in the submitted dataset resource. This method supports RFC5789 patch semantics.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| -------------------------------- | --------- | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the dataset being updated |
| `datasetId` | string | Yes | — | Required. Dataset ID of the dataset being updated |
| `access` | object\[] | No | — | Optional. An array of objects that define dataset access for one or more entities. You can set this property when inserting or updating a dataset in order to control who is allowed to access the data. If unspecified at dataset creation time, BigQuery adds default dataset access for the following entities: access.specialGroup: projectReaders; access.role: READER; access.specialGroup: projectWriters; access.role: WRITER; access.specialGroup: projectOwners; access.role: OWNER; access.userByEmail: \[dataset creator email]; access.role: OWNER; |
| `creationTime` | string | No | — | Output only. The time when this dataset was created, in milliseconds since the epoch. |
| `datasetReference` | object | No | — | Dataset Reference |
| `defaultCollation` | string | No | — | Optional. Defines the default collation specification of future tables created in the dataset. If a table is created in this dataset without table-level default collation, then the table inherits the dataset default collation, which is applied to the string fields that do not have explicit collation specified. A change to this field affects only tables created afterwards, and does not alter the existing tables. The following values are supported: \* 'und:ci': undetermined locale, case insensitive. \* '': empty string. Default to case-sensitive behavior. |
| `defaultEncryptionConfiguration` | object | No | — | Default Encryption Configuration |
| `defaultPartitionExpirationMs` | string | No | — | This default partition expiration, expressed in milliseconds. When new time-partitioned tables are created in a dataset where this property is set, the table will inherit this value, propagated as the `TimePartitioning.expirationMs` property on the new table. If you set `TimePartitioning.expirationMs` explicitly when creating a table, the `defaultPartitionExpirationMs` of the containing dataset is ignored. When creating a partitioned table, if `defaultPartitionExpirationMs` is set, the `defaultTableExpirationMs` value is ignored and the table will not be inherit a table expiration deadline. |
| `defaultRoundingMode` | string | No | — | Optional. Defines the default rounding mode specification of new tables created within this dataset. During table creation, if this field is specified, the table within this dataset will inherit the default rounding mode of the dataset. Setting the default rounding mode on a table overrides this option. Existing tables in the dataset are unaffected. If columns are defined during that table creation, they will immediately inherit the table's default rounding mode, unless otherwise specified. |
| `defaultTableExpirationMs` | string | No | — | Optional. The default lifetime of all tables in the dataset, in milliseconds. The minimum lifetime value is 3600000 milliseconds (one hour). To clear an existing default expiration with a PATCH request, set to 0. Once this property is set, all newly-created tables in the dataset will have an expirationTime property set to the creation time plus the value in this property, and changing the value will only affect new tables, not existing ones. When the expirationTime for a given table is reached, that table will be deleted automatically. If a table's expirationTime is modified or removed before the table expires, or if you provide an explicit expirationTime when creating a table, that value takes precedence over the default expiration time indicated by this property. |
| `description` | string | No | — | Optional. A user-friendly description of the dataset. |
| `etag` | string | No | — | Output only. A hash of the resource. |
| `externalDatasetReference` | object | No | — | Configures the access a dataset defined in an external metadata storage. |
| `friendlyName` | string | No | — | Optional. A descriptive name for the dataset. |
| `id` | string | No | — | Output only. The fully-qualified unique name of the dataset in the format projectId:datasetId. The dataset name without the project name is given in the datasetId field. When creating a new dataset, leave this field blank, and instead specify the datasetId field. |
| `isCaseInsensitive` | boolean | No | — | Optional. TRUE if the dataset and its table names are case-insensitive, otherwise FALSE. By default, this is FALSE, which means the dataset and its table names are case-sensitive. This field does not affect routine references. |
| `kind` | string | No | — | Output only. The resource type. |
| `labels` | object | No | — | The labels associated with this dataset. You can use these to organize and group your datasets. You can set this property when inserting or updating a dataset. See Creating and Updating Dataset Labels for more information. |
| `lastModifiedTime` | string | No | — | Output only. The date when this dataset was last modified, in milliseconds since the epoch. |
| `linkedDatasetSource` | object | No | — | A dataset source type which refers to another BigQuery dataset. |
| `location` | string | No | — | The geographic location where the dataset should reside. See [https://cloud.google.com/bigquery/docs/locations](https://cloud.google.com/bigquery/docs/locations) for supported locations. |
| `maxTimeTravelHours` | string | No | — | Optional. Defines the time travel window in hours. The value can be from 48 to 168 hours (2 to 7 days). The default value is 168 hours if this is not set. |
| `satisfiesPzi` | boolean | No | — | Output only. Reserved for future use. |
| `satisfiesPzs` | boolean | No | — | Output only. Reserved for future use. |
| `selfLink` | string | No | — | Output only. A URL that can be used to access the resource again. You can use this URL in Get or Update requests to the resource. |
| `storageBillingModel` | string | No | — | Optional. Updates storage\_billing\_model for the dataset. |
| `tags` | object\[] | No | — | Output only. Tags for the Dataset. |
| `type` | string | No | — | Output only. Same as `type` in `ListFormatDataset`. The type of the dataset, one of: \* DEFAULT - only accessible by owner and authorized accounts, \* PUBLIC - accessible by everyone, \* LINKED - linked dataset, \* EXTERNAL - dataset with definition in external metadata catalog. -- \*BIGLAKE\_METASTORE - dataset that references a database created in BigLakeMetastore service. -- |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the dataset being updated"
},
"datasetId": {
"type": "string",
"description": "Required. Dataset ID of the dataset being updated"
},
"access": {
"type": "array",
"items": {
"type": "object",
"properties": {
"dataset": {
"type": "object",
"description": "Grants all resources of particular types in a particular dataset read access to the current dataset. Similar to how individually authorized views work, updates to any resource granted through its dataset (including creation of new resources) requires read permission to referenced resources, plus write permission to the authorizing dataset."
},
"domain": {
"type": "string",
"description": "[Pick one] A domain to grant access to. Any users signed in with the domain specified will be granted the specified access. Example: \"example.com\". Maps to IAM policy member \"domain:DOMAIN\"."
},
"groupByEmail": {
"type": "string",
"description": "[Pick one] An email address of a Google Group to grant access to. Maps to IAM policy member \"group:GROUP\"."
},
"iamMember": {
"type": "string",
"description": "[Pick one] Some other type of member that appears in the IAM Policy but isn't a user, group, domain, or special group."
},
"role": {
"type": "string",
"description": "An IAM role ID that should be granted to the user, group, or domain specified in this access entry. The following legacy mappings will be applied: OWNER <=> roles/bigquery.dataOwner WRITER <=> roles/bigquery.dataEditor READER <=> roles/bigquery.dataViewer This field will accept any of the above formats, but will return only the legacy format. For example, if you set this field to \"roles/bigquery.dataOwner\", it will be returned back as \"OWNER\"."
},
"routine": {
"type": "object",
"description": "Id path of a routine."
},
"specialGroup": {
"type": "string",
"description": "[Pick one] A special group to grant access to. Possible values include: projectOwners: Owners of the enclosing project. projectReaders: Readers of the enclosing project. projectWriters: Writers of the enclosing project. allAuthenticatedUsers: All authenticated BigQuery users. Maps to similarly-named IAM members."
},
"userByEmail": {
"type": "string",
"description": "[Pick one] An email address of a user to grant access to. For example: fred@example.com. Maps to IAM policy member \"user:EMAIL\" or \"serviceAccount:EMAIL\"."
},
"view": {
"type": "object",
"description": "The view value"
}
}
},
"description": "Optional. An array of objects that define dataset access for one or more entities. You can set this property when inserting or updating a dataset in order to control who is allowed to access the data. If unspecified at dataset creation time, BigQuery adds default dataset access for the following entities: access.specialGroup: projectReaders; access.role: READER; access.specialGroup: projectWriters; access.role: WRITER; access.specialGroup: projectOwners; access.role: OWNER; access.userByEmail: [dataset creator email]; access.role: OWNER;"
},
"creationTime": {
"type": "string",
"description": "Output only. The time when this dataset was created, in milliseconds since the epoch."
},
"datasetReference": {
"type": "object",
"description": "Dataset Reference",
"properties": {
"datasetId": {
"type": "string",
"description": "Required. A unique ID for this dataset, without the project name. The ID must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_). The maximum length is 1,024 characters."
},
"projectId": {
"type": "string",
"description": "Optional. The ID of the project containing this dataset."
}
}
},
"defaultCollation": {
"type": "string",
"description": "Optional. Defines the default collation specification of future tables created in the dataset. If a table is created in this dataset without table-level default collation, then the table inherits the dataset default collation, which is applied to the string fields that do not have explicit collation specified. A change to this field affects only tables created afterwards, and does not alter the existing tables. The following values are supported: * 'und:ci': undetermined locale, case insensitive. * '': empty string. Default to case-sensitive behavior."
},
"defaultEncryptionConfiguration": {
"type": "object",
"description": "Default Encryption Configuration",
"properties": {
"kmsKeyName": {
"type": "string",
"description": "Optional. Describes the Cloud KMS encryption key that will be used to protect destination BigQuery table. The BigQuery Service Account associated with your project requires access to this encryption key."
}
}
},
"defaultPartitionExpirationMs": {
"type": "string",
"description": "This default partition expiration, expressed in milliseconds. When new time-partitioned tables are created in a dataset where this property is set, the table will inherit this value, propagated as the `TimePartitioning.expirationMs` property on the new table. If you set `TimePartitioning.expirationMs` explicitly when creating a table, the `defaultPartitionExpirationMs` of the containing dataset is ignored. When creating a partitioned table, if `defaultPartitionExpirationMs` is set, the `defaultTableExpirationMs` value is ignored and the table will not be inherit a table expiration deadline."
},
"defaultRoundingMode": {
"type": "string",
"description": "Optional. Defines the default rounding mode specification of new tables created within this dataset. During table creation, if this field is specified, the table within this dataset will inherit the default rounding mode of the dataset. Setting the default rounding mode on a table overrides this option. Existing tables in the dataset are unaffected. If columns are defined during that table creation, they will immediately inherit the table's default rounding mode, unless otherwise specified.",
"enum": [
"ROUNDING_MODE_UNSPECIFIED",
"ROUND_HALF_AWAY_FROM_ZERO",
"ROUND_HALF_EVEN"
]
},
"defaultTableExpirationMs": {
"type": "string",
"description": "Optional. The default lifetime of all tables in the dataset, in milliseconds. The minimum lifetime value is 3600000 milliseconds (one hour). To clear an existing default expiration with a PATCH request, set to 0. Once this property is set, all newly-created tables in the dataset will have an expirationTime property set to the creation time plus the value in this property, and changing the value will only affect new tables, not existing ones. When the expirationTime for a given table is reached, that table will be deleted automatically. If a table's expirationTime is modified or removed before the table expires, or if you provide an explicit expirationTime when creating a table, that value takes precedence over the default expiration time indicated by this property."
},
"description": {
"type": "string",
"description": "Optional. A user-friendly description of the dataset."
},
"etag": {
"type": "string",
"description": "Output only. A hash of the resource."
},
"externalDatasetReference": {
"type": "object",
"description": "Configures the access a dataset defined in an external metadata storage.",
"properties": {
"connection": {
"type": "string",
"description": "Required. The connection id that is used to access the external_source. Format: projects/{project_id}/locations/{location_id}/connections/{connection_id}"
},
"externalSource": {
"type": "string",
"description": "Required. External source that backs this dataset."
}
}
},
"friendlyName": {
"type": "string",
"description": "Optional. A descriptive name for the dataset."
},
"id": {
"type": "string",
"description": "Output only. The fully-qualified unique name of the dataset in the format projectId:datasetId. The dataset name without the project name is given in the datasetId field. When creating a new dataset, leave this field blank, and instead specify the datasetId field."
},
"isCaseInsensitive": {
"type": "boolean",
"description": "Optional. TRUE if the dataset and its table names are case-insensitive, otherwise FALSE. By default, this is FALSE, which means the dataset and its table names are case-sensitive. This field does not affect routine references."
},
"kind": {
"type": "string",
"description": "Output only. The resource type."
},
"labels": {
"type": "object",
"description": "The labels associated with this dataset. You can use these to organize and group your datasets. You can set this property when inserting or updating a dataset. See Creating and Updating Dataset Labels for more information."
},
"lastModifiedTime": {
"type": "string",
"description": "Output only. The date when this dataset was last modified, in milliseconds since the epoch."
},
"linkedDatasetSource": {
"type": "object",
"description": "A dataset source type which refers to another BigQuery dataset.",
"properties": {
"sourceDataset": {
"type": "object",
"description": "Source Dataset"
}
}
},
"location": {
"type": "string",
"description": "The geographic location where the dataset should reside. See https://cloud.google.com/bigquery/docs/locations for supported locations."
},
"maxTimeTravelHours": {
"type": "string",
"description": "Optional. Defines the time travel window in hours. The value can be from 48 to 168 hours (2 to 7 days). The default value is 168 hours if this is not set."
},
"satisfiesPzi": {
"type": "boolean",
"description": "Output only. Reserved for future use."
},
"satisfiesPzs": {
"type": "boolean",
"description": "Output only. Reserved for future use."
},
"selfLink": {
"type": "string",
"description": "Output only. A URL that can be used to access the resource again. You can use this URL in Get or Update requests to the resource."
},
"storageBillingModel": {
"type": "string",
"description": "Optional. Updates storage_billing_model for the dataset.",
"enum": [
"STORAGE_BILLING_MODEL_UNSPECIFIED",
"LOGICAL",
"PHYSICAL"
]
},
"tags": {
"type": "array",
"items": {
"type": "object",
"properties": {
"tagKey": {
"type": "string",
"description": "Required. The namespaced friendly name of the tag key, e.g. \"12345/environment\" where 12345 is org id."
},
"tagValue": {
"type": "string",
"description": "Required. The friendly short name of the tag value, e.g. \"production\"."
}
}
},
"description": "Output only. Tags for the Dataset."
},
"type": {
"type": "string",
"description": "Output only. Same as `type` in `ListFormatDataset`. The type of the dataset, one of: * DEFAULT - only accessible by owner and authorized accounts, * PUBLIC - accessible by everyone, * LINKED - linked dataset, * EXTERNAL - dataset with definition in external metadata catalog. -- *BIGLAKE_METASTORE - dataset that references a database created in BigLakeMetastore service. --"
}
},
"required": [
"PCID",
"projectId",
"datasetId"
]
}
```
***
## google-bigquery\_execute\_sql\_query
Runs a BigQuery SQL query synchronously and returns query results if the query completes within a specified timeout.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ---------------------- | --------- | -------- | ------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the query request. |
| `connectionProperties` | object\[] | No | — | Optional. Connection properties which can modify the query behavior. |
| `continuous` | boolean | No | — | \[Optional] Specifies whether the query should be executed as a continuous query. The default value is false. |
| `createSession` | boolean | No | — | Optional. If true, creates a new session using a randomly generated session\_id. If false, runs query with an existing session\_id passed in ConnectionProperty, otherwise runs query in non-session mode. The session location will be set to QueryRequest.location if it is present, otherwise it's set to the default location based on existing routing logic. |
| `defaultDataset` | object | No | — | Default Dataset |
| `dryRun` | boolean | No | — | Optional. If set to true, BigQuery doesn't run the job. Instead, if the query is valid, BigQuery returns statistics about the job such as how many bytes would be processed. If the query is invalid, an error returns. The default value is false. |
| `formatOptions` | object | No | — | Options for data format adjustments. |
| `jobCreationMode` | string | No | — | Optional. If not set, jobs are always required. If set, the query request will follow the behavior described JobCreationMode. This feature is not yet available. Jobs will always be created. |
| `kind` | string | No | — | The resource type of the request. |
| `labels` | object | No | — | Optional. The labels associated with this query. Labels can be used to organize and group query jobs. Label keys and values can be no longer than 63 characters, can only contain lowercase letters, numeric characters, underscores and dashes. International characters are allowed. Label keys must start with a letter and each label in the list must have a different key. |
| `location` | string | No | — | The geographic location where the job should run. See details at [https://cloud.google.com/bigquery/docs/locations#specifying\_your\_location](https://cloud.google.com/bigquery/docs/locations#specifying_your_location). |
| `maximumBytesBilled` | string | No | — | Optional. Limits the bytes billed for this query. Queries with bytes billed above this limit will fail (without incurring a charge). If unspecified, the project default is used. |
| `maxResults` | integer | No | — | Optional. The maximum number of rows of data to return per page of results. Setting this flag to a small value such as 1000 and then paging through results might improve reliability when the query result set is large. In addition to this limit, responses are also limited to 10 MB. By default, there is no maximum row count, and only the byte limit applies. |
| `parameterMode` | string | No | — | GoogleSQL only. Set to POSITIONAL to use positional (?) query parameters or to NAMED to use named (@myparam) query parameters in this query. |
| `preserveNulls` | boolean | No | — | This property is deprecated. |
| `query` | string | Yes | — | Required. A query string to execute, using Google Standard SQL or legacy SQL syntax. Example: "SELECT COUNT(f1) FROM myProjectId.myDatasetId.myTableId". |
| `queryParameters` | object\[] | No | — | Query parameters for GoogleSQL queries. |
| `requestId` | string | No | — | Optional. A unique user provided identifier to ensure idempotent behavior for queries. Note that this is different from the job\_id. It has the following properties: 1. It is case-sensitive, limited to up to 36 ASCII characters. A UUID is recommended. 2. Read only queries can ignore this token since they are nullipotent by definition. 3. For the purposes of idempotency ensured by the request\_id, a request is considered duplicate of another only if they have the same request\_id and are actually duplicates. When determining whether a request is a duplicate of another request, all parameters in the request that may affect the result are considered. For example, query, connection\_properties, query\_parameters, use\_legacy\_sql are parameters that affect the result and are considered when determining whether a request is a duplicate, but properties like timeout\_ms don't affect the result and are thus not considered. Dry run query requests are never considered duplicate of another request. 4. When a duplicate mutating query request is detected, it returns: a. the results of the mutation if it completes successfully within the timeout. b. the running operation if it is still in progress at the end of the timeout. 5. Its lifetime is limited to 15 minutes. In other words, if two requests are sent with the same request\_id, but more than 15 minutes apart, idempotency is not guaranteed. |
| `timeoutMs` | integer | No | — | Optional. Optional: Specifies the maximum amount of time, in milliseconds, that the client is willing to wait for the query to complete. By default, this limit is 10 seconds (10,000 milliseconds). If the query is complete, the jobComplete field in the response is true. If the query has not yet completed, jobComplete is false. You can request a longer timeout period in the timeoutMs field. However, the call is not guaranteed to wait for the specified timeout; it typically returns after around 200 seconds (200,000 milliseconds), even if the query is not complete. If jobComplete is false, you can continue to wait for the query to complete by calling the getQueryResults method until the jobComplete field in the getQueryResults response is true. |
| `useLegacySql` | boolean | No | — | Specifies whether to use BigQuery's legacy SQL dialect for this query. The default value is true. If set to false, the query will use BigQuery's GoogleSQL: [https://cloud.google.com/bigquery/sql-reference/](https://cloud.google.com/bigquery/sql-reference/) When useLegacySql is set to false, the value of flattenResults is ignored; query will be run as if flattenResults is false. |
| `useQueryCache` | boolean | No | — | Optional. Whether to look for the result in the query cache. The query cache is a best-effort cache that will be flushed whenever tables in the query are modified. The default value is true. |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the query request."
},
"connectionProperties": {
"type": "array",
"items": {
"type": "object",
"properties": {
"key": {
"type": "string",
"description": "The key of the property to set."
},
"value": {
"type": "string",
"description": "The value of the property to set."
}
}
},
"description": "Optional. Connection properties which can modify the query behavior."
},
"continuous": {
"type": "boolean",
"description": "[Optional] Specifies whether the query should be executed as a continuous query. The default value is false."
},
"createSession": {
"type": "boolean",
"description": "Optional. If true, creates a new session using a randomly generated session_id. If false, runs query with an existing session_id passed in ConnectionProperty, otherwise runs query in non-session mode. The session location will be set to QueryRequest.location if it is present, otherwise it's set to the default location based on existing routing logic."
},
"defaultDataset": {
"type": "object",
"description": "Default Dataset",
"properties": {
"datasetId": {
"type": "string",
"description": "Required. A unique ID for this dataset, without the project name. The ID must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_). The maximum length is 1,024 characters."
},
"projectId": {
"type": "string",
"description": "Optional. The ID of the project containing this dataset."
}
}
},
"dryRun": {
"type": "boolean",
"description": "Optional. If set to true, BigQuery doesn't run the job. Instead, if the query is valid, BigQuery returns statistics about the job such as how many bytes would be processed. If the query is invalid, an error returns. The default value is false."
},
"formatOptions": {
"type": "object",
"description": "Options for data format adjustments.",
"properties": {
"useInt64Timestamp": {
"type": "boolean",
"description": "Optional. Output timestamp as usec int64. Default is false."
}
}
},
"jobCreationMode": {
"type": "string",
"description": "Optional. If not set, jobs are always required. If set, the query request will follow the behavior described JobCreationMode. This feature is not yet available. Jobs will always be created.",
"enum": [
"JOB_CREATION_MODE_UNSPECIFIED",
"JOB_CREATION_REQUIRED",
"JOB_CREATION_OPTIONAL"
]
},
"kind": {
"type": "string",
"description": "The resource type of the request."
},
"labels": {
"type": "object",
"description": "Optional. The labels associated with this query. Labels can be used to organize and group query jobs. Label keys and values can be no longer than 63 characters, can only contain lowercase letters, numeric characters, underscores and dashes. International characters are allowed. Label keys must start with a letter and each label in the list must have a different key."
},
"location": {
"type": "string",
"description": "The geographic location where the job should run. See details at https://cloud.google.com/bigquery/docs/locations#specifying_your_location."
},
"maximumBytesBilled": {
"type": "string",
"description": "Optional. Limits the bytes billed for this query. Queries with bytes billed above this limit will fail (without incurring a charge). If unspecified, the project default is used."
},
"maxResults": {
"type": "integer",
"description": "Optional. The maximum number of rows of data to return per page of results. Setting this flag to a small value such as 1000 and then paging through results might improve reliability when the query result set is large. In addition to this limit, responses are also limited to 10 MB. By default, there is no maximum row count, and only the byte limit applies."
},
"parameterMode": {
"type": "string",
"description": "GoogleSQL only. Set to POSITIONAL to use positional (?) query parameters or to NAMED to use named (@myparam) query parameters in this query."
},
"preserveNulls": {
"type": "boolean",
"description": "This property is deprecated."
},
"query": {
"type": "string",
"description": "Required. A query string to execute, using Google Standard SQL or legacy SQL syntax. Example: \"SELECT COUNT(f1) FROM myProjectId.myDatasetId.myTableId\"."
},
"queryParameters": {
"type": "array",
"items": {
"type": "object",
"properties": {
"name": {
"type": "string",
"description": "Optional. If unset, this is a positional parameter. Otherwise, should be unique within a query."
},
"parameterType": {
"type": "object",
"description": "The type of a query parameter."
},
"parameterValue": {
"type": "object",
"description": "The value of a query parameter."
}
}
},
"description": "Query parameters for GoogleSQL queries."
},
"requestId": {
"type": "string",
"description": "Optional. A unique user provided identifier to ensure idempotent behavior for queries. Note that this is different from the job_id. It has the following properties: 1. It is case-sensitive, limited to up to 36 ASCII characters. A UUID is recommended. 2. Read only queries can ignore this token since they are nullipotent by definition. 3. For the purposes of idempotency ensured by the request_id, a request is considered duplicate of another only if they have the same request_id and are actually duplicates. When determining whether a request is a duplicate of another request, all parameters in the request that may affect the result are considered. For example, query, connection_properties, query_parameters, use_legacy_sql are parameters that affect the result and are considered when determining whether a request is a duplicate, but properties like timeout_ms don't affect the result and are thus not considered. Dry run query requests are never considered duplicate of another request. 4. When a duplicate mutating query request is detected, it returns: a. the results of the mutation if it completes successfully within the timeout. b. the running operation if it is still in progress at the end of the timeout. 5. Its lifetime is limited to 15 minutes. In other words, if two requests are sent with the same request_id, but more than 15 minutes apart, idempotency is not guaranteed."
},
"timeoutMs": {
"type": "integer",
"description": "Optional. Optional: Specifies the maximum amount of time, in milliseconds, that the client is willing to wait for the query to complete. By default, this limit is 10 seconds (10,000 milliseconds). If the query is complete, the jobComplete field in the response is true. If the query has not yet completed, jobComplete is false. You can request a longer timeout period in the timeoutMs field. However, the call is not guaranteed to wait for the specified timeout; it typically returns after around 200 seconds (200,000 milliseconds), even if the query is not complete. If jobComplete is false, you can continue to wait for the query to complete by calling the getQueryResults method until the jobComplete field in the getQueryResults response is true."
},
"useLegacySql": {
"type": "boolean",
"description": "Specifies whether to use BigQuery's legacy SQL dialect for this query. The default value is true. If set to false, the query will use BigQuery's GoogleSQL: https://cloud.google.com/bigquery/sql-reference/ When useLegacySql is set to false, the value of flattenResults is ignored; query will be run as if flattenResults is false."
},
"useQueryCache": {
"type": "boolean",
"description": "Optional. Whether to look for the result in the query cache. The query cache is a best-effort cache that will be flushed whenever tables in the query are modified. The default value is true."
}
},
"required": [
"PCID",
"projectId",
"query"
]
}
```
***
## google-bigquery\_insert\_rows
Streams data into BigQuery one record at a time without needing to run a load job.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| --------------------- | --------- | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the destination. |
| `datasetId` | string | Yes | — | Required. Dataset ID of the destination. |
| `tableId` | string | Yes | — | Required. Table ID of the destination. |
| `ignoreUnknownValues` | boolean | No | — | Optional. Accept rows that contain values that do not match the schema. The unknown values are ignored. Default is false, which treats unknown values as errors. |
| `kind` | string | No | — | Optional. The resource type of the response. The value is not checked at the backend. Historically, it has been set to "bigquery#tableDataInsertAllRequest" but you are not required to set it. |
| `rows` | object\[] | Yes | — | The rows value |
| `skipInvalidRows` | boolean | No | — | Optional. Insert all valid rows of a request, even if invalid rows exist. The default value is false, which causes the entire request to fail if any invalid rows exist. |
| `templateSuffix` | string | No | — | Optional. If specified, treats the destination table as a base template, and inserts the rows into an instance table named "\{destination}\{templateSuffix}". BigQuery will manage creation of the instance table, using the schema of the base template table. See [https://cloud.google.com/bigquery/streaming-data-into-bigquery#template-tables](https://cloud.google.com/bigquery/streaming-data-into-bigquery#template-tables) for considerations when working with templates tables. |
| `traceId` | string | No | — | Optional. Unique request trace id. Used for debugging purposes only. It is case-sensitive, limited to up to 36 ASCII characters. A UUID is recommended. |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the destination."
},
"datasetId": {
"type": "string",
"description": "Required. Dataset ID of the destination."
},
"tableId": {
"type": "string",
"description": "Required. Table ID of the destination."
},
"ignoreUnknownValues": {
"type": "boolean",
"description": "Optional. Accept rows that contain values that do not match the schema. The unknown values are ignored. Default is false, which treats unknown values as errors."
},
"kind": {
"type": "string",
"description": "Optional. The resource type of the response. The value is not checked at the backend. Historically, it has been set to \"bigquery#tableDataInsertAllRequest\" but you are not required to set it."
},
"rows": {
"type": "array",
"items": {
"type": "object",
"properties": {
"insertId": {
"type": "string",
"description": "Insertion ID for best-effort deduplication. This feature is not recommended, and users seeking stronger insertion semantics are encouraged to use other mechanisms such as the BigQuery Write API."
},
"json": {
"type": "object",
"description": "Represents a single JSON object."
}
}
},
"description": "The rows value"
},
"skipInvalidRows": {
"type": "boolean",
"description": "Optional. Insert all valid rows of a request, even if invalid rows exist. The default value is false, which causes the entire request to fail if any invalid rows exist."
},
"templateSuffix": {
"type": "string",
"description": "Optional. If specified, treats the destination table as a base template, and inserts the rows into an instance table named \"{destination}{templateSuffix}\". BigQuery will manage creation of the instance table, using the schema of the base template table. See https://cloud.google.com/bigquery/streaming-data-into-bigquery#template-tables for considerations when working with templates tables."
},
"traceId": {
"type": "string",
"description": "Optional. Unique request trace id. Used for debugging purposes only. It is case-sensitive, limited to up to 36 ASCII characters. A UUID is recommended."
}
},
"required": [
"PCID",
"projectId",
"datasetId",
"tableId",
"rows"
]
}
```
***
## google-bigquery\_jobs\_cancel
Requests that a job be cancelled. This call will return immediately, and the client will need to poll for the job status to see if the cancel completed successfully. Cancelled jobs may still incur costs.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ----------- | ------ | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `projectId` | string | Yes | — | Required. Project ID of the job to cancel |
| `jobId` | string | Yes | — | Required. Job ID of the job to cancel |
| `location` | string | No | — | The geographic location of the job. You must specify the location to run the job for the following scenarios: - If the location to run a job is not in the `us` or the `eu` multi-regional location - If the job's location is in a single region (for example, `us-central1`) For more information, see [https://cloud.google.com/bigquery/docs/locations#specifying\_your\_location](https://cloud.google.com/bigquery/docs/locations#specifying_your_location). |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the job to cancel"
},
"jobId": {
"type": "string",
"description": "Required. Job ID of the job to cancel"
},
"location": {
"type": "string",
"description": "The geographic location of the job. You must specify the location to run the job for the following scenarios: - If the location to run a job is not in the `us` or the `eu` multi-regional location - If the job's location is in a single region (for example, `us-central1`) For more information, see https://cloud.google.com/bigquery/docs/locations#specifying_your_location."
}
},
"required": [
"PCID",
"projectId",
"jobId"
]
}
```
***
## google-bigquery\_jobs\_delete
Requests the deletion of the metadata of a job. This call returns when the job's metadata is deleted.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ----------- | ------ | -------- | ------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the job for which metadata is to be deleted. |
| `jobId` | string | Yes | — | Required. Job ID of the job for which metadata is to be deleted. If this is a parent job which has child jobs, the metadata from all child jobs will be deleted as well. Direct deletion of the metadata of child jobs is not allowed. |
| `location` | string | No | — | The geographic location of the job. Required. See details at: [https://cloud.google.com/bigquery/docs/locations#specifying\_your\_location](https://cloud.google.com/bigquery/docs/locations#specifying_your_location). |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the job for which metadata is to be deleted."
},
"jobId": {
"type": "string",
"description": "Required. Job ID of the job for which metadata is to be deleted. If this is a parent job which has child jobs, the metadata from all child jobs will be deleted as well. Direct deletion of the metadata of child jobs is not allowed."
},
"location": {
"type": "string",
"description": "The geographic location of the job. Required. See details at: https://cloud.google.com/bigquery/docs/locations#specifying_your_location."
}
},
"required": [
"PCID",
"projectId",
"jobId"
]
}
```
***
## google-bigquery\_jobs\_get
Returns information about a specific job. Job information is available for a six month period after creation. Requires that you're the person who ran the job, or have the Is Owner project role.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ----------- | ------ | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `projectId` | string | Yes | — | Required. Project ID of the requested job. |
| `jobId` | string | Yes | — | Required. Job ID of the requested job. |
| `location` | string | No | — | The geographic location of the job. You must specify the location to run the job for the following scenarios: - If the location to run a job is not in the `us` or the `eu` multi-regional location - If the job's location is in a single region (for example, `us-central1`) For more information, see [https://cloud.google.com/bigquery/docs/locations#specifying\_your\_location](https://cloud.google.com/bigquery/docs/locations#specifying_your_location). |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the requested job."
},
"jobId": {
"type": "string",
"description": "Required. Job ID of the requested job."
},
"location": {
"type": "string",
"description": "The geographic location of the job. You must specify the location to run the job for the following scenarios: - If the location to run a job is not in the `us` or the `eu` multi-regional location - If the job's location is in a single region (for example, `us-central1`) For more information, see https://cloud.google.com/bigquery/docs/locations#specifying_your_location."
}
},
"required": [
"PCID",
"projectId",
"jobId"
]
}
```
***
## google-bigquery\_jobs\_get\_query\_results
RPC to get the results of a query job.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| --------------------------------- | ------- | -------- | ------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the query job. |
| `jobId` | string | Yes | — | Required. Job ID of the query job. |
| `formatOptions.useInt64Timestamp` | boolean | No | — | Optional. Output timestamp as usec int64. Default is false. |
| `location` | string | No | — | The geographic location of the job. You must specify the location to run the job for the following scenarios: - If the location to run a job is not in the `us` or the `eu` multi-regional location - If the job's location is in a single region (for example, `us-central1`) For more information, see [https://cloud.google.com/bigquery/docs/locations#specifying\_your\_location](https://cloud.google.com/bigquery/docs/locations#specifying_your_location). |
| `maxResults` | integer | No | — | Maximum number of results to read. |
| `pageToken` | string | No | — | Page token, returned by a previous call, to request the next page of results. |
| `startIndex` | string | No | — | Zero-based index of the starting row. |
| `timeoutMs` | integer | No | — | Optional: Specifies the maximum amount of time, in milliseconds, that the client is willing to wait for the query to complete. By default, this limit is 10 seconds (10,000 milliseconds). If the query is complete, the jobComplete field in the response is true. If the query has not yet completed, jobComplete is false. You can request a longer timeout period in the timeoutMs field. However, the call is not guaranteed to wait for the specified timeout; it typically returns after around 200 seconds (200,000 milliseconds), even if the query is not complete. If jobComplete is false, you can continue to wait for the query to complete by calling the getQueryResults method until the jobComplete field in the getQueryResults response is true. |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the query job."
},
"jobId": {
"type": "string",
"description": "Required. Job ID of the query job."
},
"formatOptions.useInt64Timestamp": {
"type": "boolean",
"description": "Optional. Output timestamp as usec int64. Default is false."
},
"location": {
"type": "string",
"description": "The geographic location of the job. You must specify the location to run the job for the following scenarios: - If the location to run a job is not in the `us` or the `eu` multi-regional location - If the job's location is in a single region (for example, `us-central1`) For more information, see https://cloud.google.com/bigquery/docs/locations#specifying_your_location."
},
"maxResults": {
"type": "integer",
"description": "Maximum number of results to read."
},
"pageToken": {
"type": "string",
"description": "Page token, returned by a previous call, to request the next page of results."
},
"startIndex": {
"type": "string",
"description": "Zero-based index of the starting row."
},
"timeoutMs": {
"type": "integer",
"description": "Optional: Specifies the maximum amount of time, in milliseconds, that the client is willing to wait for the query to complete. By default, this limit is 10 seconds (10,000 milliseconds). If the query is complete, the jobComplete field in the response is true. If the query has not yet completed, jobComplete is false. You can request a longer timeout period in the timeoutMs field. However, the call is not guaranteed to wait for the specified timeout; it typically returns after around 200 seconds (200,000 milliseconds), even if the query is not complete. If jobComplete is false, you can continue to wait for the query to complete by calling the getQueryResults method until the jobComplete field in the getQueryResults response is true."
}
},
"required": [
"PCID",
"projectId",
"jobId"
]
}
```
***
## google-bigquery\_jobs\_list
Lists all jobs that you started in the specified project. Job information is available for a six month period after creation. The job list is sorted in reverse chronological order, by job creation time. Requires the Can View project role, or the Is Owner project role if you set the allUsers property.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ----------------- | --------- | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------- |
| `projectId` | string | Yes | — | Project ID of the jobs to list. |
| `allUsers` | boolean | No | — | Whether to display jobs owned by all users in the project. Default False. |
| `maxCreationTime` | string | No | — | Max value for job creation time, in milliseconds since the POSIX epoch. If set, only jobs created before or at this timestamp are returned. |
| `maxResults` | integer | No | — | The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection. |
| `minCreationTime` | string | No | — | Min value for job creation time, in milliseconds since the POSIX epoch. If set, only jobs created after or at this timestamp are returned. |
| `pageToken` | string | No | — | Page token, returned by a previous call, to request the next page of results. |
| `parentJobId` | string | No | — | If set, show only child jobs of the specified parent. Otherwise, show all top-level jobs. |
| `projection` | string | No | — | Restrict information returned to a set of selected fields |
| `stateFilter` | string\[] | No | — | Filter for job state |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Project ID of the jobs to list."
},
"allUsers": {
"type": "boolean",
"description": "Whether to display jobs owned by all users in the project. Default False."
},
"maxCreationTime": {
"type": "string",
"description": "Max value for job creation time, in milliseconds since the POSIX epoch. If set, only jobs created before or at this timestamp are returned."
},
"maxResults": {
"type": "integer",
"description": "The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection."
},
"minCreationTime": {
"type": "string",
"description": "Min value for job creation time, in milliseconds since the POSIX epoch. If set, only jobs created after or at this timestamp are returned."
},
"pageToken": {
"type": "string",
"description": "Page token, returned by a previous call, to request the next page of results."
},
"parentJobId": {
"type": "string",
"description": "If set, show only child jobs of the specified parent. Otherwise, show all top-level jobs."
},
"projection": {
"type": "string",
"description": "Restrict information returned to a set of selected fields",
"enum": [
"full",
"minimal"
]
},
"stateFilter": {
"type": "array",
"items": {
"type": "string",
"enum": [
"done",
"pending",
"running"
]
},
"description": "Filter for job state"
}
},
"required": [
"PCID",
"projectId"
]
}
```
***
## google-bigquery\_list\_row\_access\_policies
Lists all row access policies on the specified table.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ----------- | ------- | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the row access policies to list. |
| `datasetId` | string | Yes | — | Required. Dataset ID of row access policies to list. |
| `tableId` | string | Yes | — | Required. Table ID of the table to list row access policies. |
| `pageSize` | integer | No | — | The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection. |
| `pageToken` | string | No | — | Page token, returned by a previous call, to request the next page of results. |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the row access policies to list."
},
"datasetId": {
"type": "string",
"description": "Required. Dataset ID of row access policies to list."
},
"tableId": {
"type": "string",
"description": "Required. Table ID of the table to list row access policies."
},
"pageSize": {
"type": "integer",
"description": "The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection."
},
"pageToken": {
"type": "string",
"description": "Page token, returned by a previous call, to request the next page of results."
}
},
"required": [
"PCID",
"projectId",
"datasetId",
"tableId"
]
}
```
***
## google-bigquery\_list\_table\_data
List the content of a table in rows.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| --------------------------------- | ------- | -------- | ------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project id of the table to list. |
| `datasetId` | string | Yes | — | Required. Dataset id of the table to list. |
| `tableId` | string | Yes | — | Required. Table id of the table to list. |
| `formatOptions.useInt64Timestamp` | boolean | No | — | Optional. Output timestamp as usec int64. Default is false. |
| `maxResults` | integer | No | — | Row limit of the table. |
| `pageToken` | string | No | — | To retrieve the next page of table data, set this field to the string provided in the pageToken field of the response body from your previous call to tabledata.list. |
| `selectedFields` | string | No | — | Subset of fields to return, supports select into sub fields. Example: selected\_fields = "a,e.d.f"; |
| `startIndex` | string | No | — | Start row index of the table. |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project id of the table to list."
},
"datasetId": {
"type": "string",
"description": "Required. Dataset id of the table to list."
},
"tableId": {
"type": "string",
"description": "Required. Table id of the table to list."
},
"formatOptions.useInt64Timestamp": {
"type": "boolean",
"description": "Optional. Output timestamp as usec int64. Default is false."
},
"maxResults": {
"type": "integer",
"description": "Row limit of the table."
},
"pageToken": {
"type": "string",
"description": "To retrieve the next page of table data, set this field to the string provided in the pageToken field of the response body from your previous call to tabledata.list."
},
"selectedFields": {
"type": "string",
"description": "Subset of fields to return, supports select into sub fields. Example: selected_fields = \"a,e.d.f\";"
},
"startIndex": {
"type": "string",
"description": "Start row index of the table."
}
},
"required": [
"PCID",
"projectId",
"datasetId",
"tableId"
]
}
```
***
## google-bigquery\_models\_delete
Deletes the model specified by modelId from the dataset.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ----------- | ------ | -------- | ------- | -------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the model to delete. |
| `datasetId` | string | Yes | — | Required. Dataset ID of the model to delete. |
| `modelId` | string | Yes | — | Required. Model ID of the model to delete. |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the model to delete."
},
"datasetId": {
"type": "string",
"description": "Required. Dataset ID of the model to delete."
},
"modelId": {
"type": "string",
"description": "Required. Model ID of the model to delete."
}
},
"required": [
"PCID",
"projectId",
"datasetId",
"modelId"
]
}
```
***
## google-bigquery\_models\_get
Gets the specified model resource by model ID.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ----------- | ------ | -------- | ------- | -------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the requested model. |
| `datasetId` | string | Yes | — | Required. Dataset ID of the requested model. |
| `modelId` | string | Yes | — | Required. Model ID of the requested model. |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the requested model."
},
"datasetId": {
"type": "string",
"description": "Required. Dataset ID of the requested model."
},
"modelId": {
"type": "string",
"description": "Required. Model ID of the requested model."
}
},
"required": [
"PCID",
"projectId",
"datasetId",
"modelId"
]
}
```
***
## google-bigquery\_models\_list
Lists all models in the specified dataset. Requires the READER dataset role. After retrieving the list of models, you can get information about a particular model by calling the models.get method.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ------------ | ------- | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the models to list. |
| `datasetId` | string | Yes | — | Required. Dataset ID of the models to list. |
| `maxResults` | integer | No | — | The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection. |
| `pageToken` | string | No | — | Page token, returned by a previous call to request the next page of results |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the models to list."
},
"datasetId": {
"type": "string",
"description": "Required. Dataset ID of the models to list."
},
"maxResults": {
"type": "integer",
"description": "The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection."
},
"pageToken": {
"type": "string",
"description": "Page token, returned by a previous call to request the next page of results"
}
},
"required": [
"PCID",
"projectId",
"datasetId"
]
}
```
***
## google-bigquery\_models\_patch
Patch specific fields in the specified model.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ------------------------- | --------- | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the model to patch. |
| `datasetId` | string | Yes | — | Required. Dataset ID of the model to patch. |
| `modelId` | string | Yes | — | Required. Model ID of the model to patch. |
| `bestTrialId` | string | No | — | The best trial\_id across all training runs. |
| `creationTime` | string | No | — | Output only. The time when this model was created, in millisecs since the epoch. |
| `defaultTrialId` | string | No | — | Output only. The default trial\_id to use in TVFs when the trial\_id is not passed in. For single-objective [hyperparameter tuning](/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-hp-tuning-overview) models, this is the best trial ID. For multi-objective [hyperparameter tuning](/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-hp-tuning-overview) models, this is the smallest trial ID among all Pareto optimal trials. |
| `description` | string | No | — | Optional. A user-friendly description of this model. |
| `encryptionConfiguration` | object | No | — | Encryption Configuration |
| `etag` | string | No | — | Output only. A hash of this resource. |
| `expirationTime` | string | No | — | Optional. The time when this model expires, in milliseconds since the epoch. If not present, the model will persist indefinitely. Expired models will be deleted and their storage reclaimed. The defaultTableExpirationMs property of the encapsulating dataset can be used to set a default expirationTime on newly created models. |
| `featureColumns` | object\[] | No | — | Output only. Input feature columns for the model inference. If the model is trained with TRANSFORM clause, these are the input of the TRANSFORM clause. |
| `friendlyName` | string | No | — | Optional. A descriptive name for this model. |
| `hparamSearchSpaces` | object | No | — | Hyperparameter search spaces. These should be a subset of training\_options. |
| `hparamTrials` | object\[] | No | — | Output only. Trials of a [hyperparameter tuning](/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-hp-tuning-overview) model sorted by trial\_id. |
| `labelColumns` | object\[] | No | — | Output only. Label columns that were used to train this model. The output of the model will have a "predicted\_" prefix to these columns. |
| `labels` | object | No | — | The labels associated with this model. You can use these to organize and group your models. Label keys and values can be no longer than 63 characters, can only contain lowercase letters, numeric characters, underscores and dashes. International characters are allowed. Label values are optional. Label keys must start with a letter and each label in the list must have a different key. |
| `lastModifiedTime` | string | No | — | Output only. The time when this model was last modified, in millisecs since the epoch. |
| `location` | string | No | — | Output only. The geographic location where the model resides. This value is inherited from the dataset. |
| `modelReference` | object | No | — | Id path of a model. |
| `modelType` | string | No | — | Output only. Type of the model resource. |
| `optimalTrialIds` | string\[] | No | — | Output only. For single-objective [hyperparameter tuning](/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-hp-tuning-overview) models, it only contains the best trial. For multi-objective [hyperparameter tuning](/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-hp-tuning-overview) models, it contains all Pareto optimal trials sorted by trial\_id. |
| `remoteModelInfo` | object | No | — | Remote Model Info |
| `trainingRuns` | object\[] | No | — | Information for all training runs in increasing order of start\_time. |
| `transformColumns` | object\[] | No | — | Output only. This field will be populated if a TRANSFORM clause was used to train a model. TRANSFORM clause (if used) takes feature\_columns as input and outputs transform\_columns. transform\_columns then are used to train the model. |
````json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the model to patch."
},
"datasetId": {
"type": "string",
"description": "Required. Dataset ID of the model to patch."
},
"modelId": {
"type": "string",
"description": "Required. Model ID of the model to patch."
},
"bestTrialId": {
"type": "string",
"description": "The best trial_id across all training runs."
},
"creationTime": {
"type": "string",
"description": "Output only. The time when this model was created, in millisecs since the epoch."
},
"defaultTrialId": {
"type": "string",
"description": "Output only. The default trial_id to use in TVFs when the trial_id is not passed in. For single-objective [hyperparameter tuning](/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-hp-tuning-overview) models, this is the best trial ID. For multi-objective [hyperparameter tuning](/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-hp-tuning-overview) models, this is the smallest trial ID among all Pareto optimal trials."
},
"description": {
"type": "string",
"description": "Optional. A user-friendly description of this model."
},
"encryptionConfiguration": {
"type": "object",
"description": "Encryption Configuration",
"properties": {
"kmsKeyName": {
"type": "string",
"description": "Optional. Describes the Cloud KMS encryption key that will be used to protect destination BigQuery table. The BigQuery Service Account associated with your project requires access to this encryption key."
}
}
},
"etag": {
"type": "string",
"description": "Output only. A hash of this resource."
},
"expirationTime": {
"type": "string",
"description": "Optional. The time when this model expires, in milliseconds since the epoch. If not present, the model will persist indefinitely. Expired models will be deleted and their storage reclaimed. The defaultTableExpirationMs property of the encapsulating dataset can be used to set a default expirationTime on newly created models."
},
"featureColumns": {
"type": "array",
"items": {
"type": "object",
"properties": {
"name": {
"type": "string",
"description": "Optional. The name of this field. Can be absent for struct fields."
},
"type": {
"type": "object",
"description": "The data type of a variable such as a function argument. Examples include: * INT64: `{\"typeKind\": \"INT64\"}` * ARRAY: { \"typeKind\": \"ARRAY\", \"arrayElementType\": {\"typeKind\": \"STRING\"} } * STRUCT>: { \"typeKind\": \"STRUCT\", \"structType\": { \"fields\": [ { \"name\": \"x\", \"type\": {\"typeKind\": \"STRING\"} }, { \"name\": \"y\", \"type\": { \"typeKind\": \"ARRAY\", \"arrayElementType\": {\"typeKind\": \"DATE\"} } } ] } }"
}
}
},
"description": "Output only. Input feature columns for the model inference. If the model is trained with TRANSFORM clause, these are the input of the TRANSFORM clause."
},
"friendlyName": {
"type": "string",
"description": "Optional. A descriptive name for this model."
},
"hparamSearchSpaces": {
"type": "object",
"description": "Hyperparameter search spaces. These should be a subset of training_options.",
"properties": {
"activationFn": {
"type": "object",
"description": "Search space for string and enum."
},
"batchSize": {
"type": "object",
"description": "Search space for an int hyperparameter."
},
"boosterType": {
"type": "object",
"description": "Search space for string and enum."
},
"colsampleBylevel": {
"type": "object",
"description": "Search space for a double hyperparameter."
},
"colsampleBynode": {
"type": "object",
"description": "Search space for a double hyperparameter."
},
"colsampleBytree": {
"type": "object",
"description": "Search space for a double hyperparameter."
},
"dartNormalizeType": {
"type": "object",
"description": "Search space for string and enum."
},
"dropout": {
"type": "object",
"description": "Search space for a double hyperparameter."
},
"hiddenUnits": {
"type": "object",
"description": "Search space for int array."
},
"l1Reg": {
"type": "object",
"description": "Search space for a double hyperparameter."
},
"l2Reg": {
"type": "object",
"description": "Search space for a double hyperparameter."
},
"learnRate": {
"type": "object",
"description": "Search space for a double hyperparameter."
},
"maxTreeDepth": {
"type": "object",
"description": "Search space for an int hyperparameter."
},
"minSplitLoss": {
"type": "object",
"description": "Search space for a double hyperparameter."
},
"minTreeChildWeight": {
"type": "object",
"description": "Search space for an int hyperparameter."
},
"numClusters": {
"type": "object",
"description": "Search space for an int hyperparameter."
},
"numFactors": {
"type": "object",
"description": "Search space for an int hyperparameter."
},
"numParallelTree": {
"type": "object",
"description": "Search space for an int hyperparameter."
},
"optimizer": {
"type": "object",
"description": "Search space for string and enum."
},
"subsample": {
"type": "object",
"description": "Search space for a double hyperparameter."
},
"treeMethod": {
"type": "object",
"description": "Search space for string and enum."
},
"walsAlpha": {
"type": "object",
"description": "Search space for a double hyperparameter."
}
}
},
"hparamTrials": {
"type": "array",
"items": {
"type": "object",
"properties": {
"endTimeMs": {
"type": "string",
"format": "int64",
"description": "Ending time of the trial."
},
"errorMessage": {
"type": "string",
"description": "Error message for FAILED and INFEASIBLE trial."
},
"evalLoss": {
"type": "number",
"format": "double",
"description": "Loss computed on the eval data at the end of trial."
},
"evaluationMetrics": {
"type": "object",
"description": "Evaluation metrics of a model. These are either computed on all training data or just the eval data based on whether eval data was used during training. These are not present for imported models."
},
"hparamTuningEvaluationMetrics": {
"type": "object",
"description": "Evaluation metrics of a model. These are either computed on all training data or just the eval data based on whether eval data was used during training. These are not present for imported models."
},
"hparams": {
"type": "object",
"description": "Options used in model training."
},
"startTimeMs": {
"type": "string",
"format": "int64",
"description": "Starting time of the trial."
},
"status": {
"type": "string",
"enum": [
"TRIAL_STATUS_UNSPECIFIED",
"NOT_STARTED",
"RUNNING",
"SUCCEEDED",
"FAILED",
"INFEASIBLE",
"STOPPED_EARLY"
],
"description": "The status of the trial."
},
"trainingLoss": {
"type": "number",
"format": "double",
"description": "Loss computed on the training data at the end of trial."
},
"trialId": {
"type": "string",
"format": "int64",
"description": "1-based index of the trial."
}
}
},
"description": "Output only. Trials of a [hyperparameter tuning](/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-hp-tuning-overview) model sorted by trial_id."
},
"labelColumns": {
"type": "array",
"items": {
"type": "object",
"properties": {
"name": {
"type": "string",
"description": "Optional. The name of this field. Can be absent for struct fields."
},
"type": {
"type": "object",
"description": "The data type of a variable such as a function argument. Examples include: * INT64: `{\"typeKind\": \"INT64\"}` * ARRAY: { \"typeKind\": \"ARRAY\", \"arrayElementType\": {\"typeKind\": \"STRING\"} } * STRUCT>: { \"typeKind\": \"STRUCT\", \"structType\": { \"fields\": [ { \"name\": \"x\", \"type\": {\"typeKind\": \"STRING\"} }, { \"name\": \"y\", \"type\": { \"typeKind\": \"ARRAY\", \"arrayElementType\": {\"typeKind\": \"DATE\"} } } ] } }"
}
}
},
"description": "Output only. Label columns that were used to train this model. The output of the model will have a \"predicted_\" prefix to these columns."
},
"labels": {
"type": "object",
"description": "The labels associated with this model. You can use these to organize and group your models. Label keys and values can be no longer than 63 characters, can only contain lowercase letters, numeric characters, underscores and dashes. International characters are allowed. Label values are optional. Label keys must start with a letter and each label in the list must have a different key."
},
"lastModifiedTime": {
"type": "string",
"description": "Output only. The time when this model was last modified, in millisecs since the epoch."
},
"location": {
"type": "string",
"description": "Output only. The geographic location where the model resides. This value is inherited from the dataset."
},
"modelReference": {
"type": "object",
"description": "Id path of a model.",
"properties": {
"datasetId": {
"type": "string",
"description": "Required. The ID of the dataset containing this model."
},
"modelId": {
"type": "string",
"description": "Required. The ID of the model. The ID must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_). The maximum length is 1,024 characters."
},
"projectId": {
"type": "string",
"description": "Required. The ID of the project containing this model."
}
}
},
"modelType": {
"type": "string",
"description": "Output only. Type of the model resource.",
"enum": [
"MODEL_TYPE_UNSPECIFIED",
"LINEAR_REGRESSION",
"LOGISTIC_REGRESSION",
"KMEANS",
"MATRIX_FACTORIZATION",
"DNN_CLASSIFIER",
"TENSORFLOW",
"DNN_REGRESSOR",
"XGBOOST",
"BOOSTED_TREE_REGRESSOR",
"BOOSTED_TREE_CLASSIFIER",
"ARIMA",
"AUTOML_REGRESSOR",
"AUTOML_CLASSIFIER",
"PCA",
"DNN_LINEAR_COMBINED_CLASSIFIER",
"DNN_LINEAR_COMBINED_REGRESSOR",
"AUTOENCODER",
"ARIMA_PLUS",
"ARIMA_PLUS_XREG",
"RANDOM_FOREST_REGRESSOR",
"RANDOM_FOREST_CLASSIFIER",
"TENSORFLOW_LITE",
"ONNX"
]
},
"optimalTrialIds": {
"type": "array",
"items": {
"type": "string",
"format": "int64"
},
"description": "Output only. For single-objective [hyperparameter tuning](/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-hp-tuning-overview) models, it only contains the best trial. For multi-objective [hyperparameter tuning](/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-hp-tuning-overview) models, it contains all Pareto optimal trials sorted by trial_id."
},
"remoteModelInfo": {
"type": "object",
"description": "Remote Model Info",
"properties": {
"connection": {
"type": "string",
"description": "Output only. Fully qualified name of the user-provided connection object of the remote model. Format: ```\"projects/{project_id}/locations/{location_id}/connections/{connection_id}\"```"
},
"endpoint": {
"type": "string",
"description": "Output only. The endpoint for remote model."
},
"maxBatchingRows": {
"type": "string",
"description": "Output only. Max number of rows in each batch sent to the remote service. If unset, the number of rows in each batch is set dynamically."
},
"remoteModelVersion": {
"type": "string",
"description": "Output only. The model version for LLM."
},
"remoteServiceType": {
"type": "string",
"description": "Output only. The remote service type for remote model.",
"enum": [
"REMOTE_SERVICE_TYPE_UNSPECIFIED",
"CLOUD_AI_TRANSLATE_V3",
"CLOUD_AI_VISION_V1",
"CLOUD_AI_NATURAL_LANGUAGE_V1",
"CLOUD_AI_SPEECH_TO_TEXT_V2"
]
},
"speechRecognizer": {
"type": "string",
"description": "Output only. The name of the speech recognizer to use for speech recognition. The expected format is `projects/{project}/locations/{location}/recognizers/{recognizer}`. Customers can specify this field at model creation. If not specified, a default recognizer `projects/{model project}/locations/global/recognizers/_` will be used. See more details at [recognizers](https://cloud.google.com/speech-to-text/v2/docs/reference/rest/v2/projects.locations.recognizers)"
}
}
},
"trainingRuns": {
"type": "array",
"items": {
"type": "object",
"properties": {
"classLevelGlobalExplanations": {
"type": "array",
"items": {
"type": "object",
"properties": {
"classLabel": {
"type": "string",
"description": "Class label for this set of global explanations. Will be empty/null for binary logistic and linear regression models. Sorted alphabetically in descending order."
},
"explanations": {
"type": "array",
"description": "A list of the top global explanations. Sorted by absolute value of attribution in descending order."
}
}
},
"description": "Output only. Global explanation contains the explanation of top features on the class level. Applies to classification models only."
},
"dataSplitResult": {
"type": "object",
"description": "Data split result. This contains references to the training and evaluation data tables that were used to train the model."
},
"evaluationMetrics": {
"type": "object",
"description": "Evaluation metrics of a model. These are either computed on all training data or just the eval data based on whether eval data was used during training. These are not present for imported models."
},
"modelLevelGlobalExplanation": {
"type": "object",
"description": "Global explanations containing the top most important features after training."
},
"results": {
"type": "array",
"items": {
"type": "object",
"properties": {
"arimaResult": {
"type": "object",
"description": "(Auto-)arima fitting result. Wrap everything in ArimaResult for easier refactoring if we want to use model-specific iteration results."
},
"clusterInfos": {
"type": "array",
"description": "Information about top clusters for clustering models."
},
"durationMs": {
"type": "string",
"description": "Time taken to run the iteration in milliseconds."
},
"evalLoss": {
"type": "number",
"description": "Loss computed on the eval data at the end of iteration."
},
"index": {
"type": "integer",
"description": "Index of the iteration, 0 based."
},
"learnRate": {
"type": "number",
"description": "Learn rate used for this iteration."
},
"principalComponentInfos": {
"type": "array",
"description": "The information of the principal components."
},
"trainingLoss": {
"type": "number",
"description": "Loss computed on the training data at the end of iteration."
}
}
},
"description": "Output only. Output of each iteration run, results.size() <= max_iterations."
},
"startTime": {
"type": "string",
"format": "google-datetime",
"description": "Output only. The start time of this training run."
},
"trainingOptions": {
"type": "object",
"description": "Options used in model training."
},
"trainingStartTime": {
"type": "string",
"format": "int64",
"description": "Output only. The start time of this training run, in milliseconds since epoch."
},
"vertexAiModelId": {
"type": "string",
"description": "The model id in the [Vertex AI Model Registry](https://cloud.google.com/vertex-ai/docs/model-registry/introduction) for this training run."
},
"vertexAiModelVersion": {
"type": "string",
"description": "Output only. The model version in the [Vertex AI Model Registry](https://cloud.google.com/vertex-ai/docs/model-registry/introduction) for this training run."
}
}
},
"description": "Information for all training runs in increasing order of start_time."
},
"transformColumns": {
"type": "array",
"items": {
"type": "object",
"properties": {
"name": {
"type": "string",
"description": "Output only. Name of the column."
},
"transformSql": {
"type": "string",
"description": "Output only. The SQL expression used in the column transform."
},
"type": {
"type": "object",
"description": "The data type of a variable such as a function argument. Examples include: * INT64: `{\"typeKind\": \"INT64\"}` * ARRAY: { \"typeKind\": \"ARRAY\", \"arrayElementType\": {\"typeKind\": \"STRING\"} } * STRUCT>: { \"typeKind\": \"STRUCT\", \"structType\": { \"fields\": [ { \"name\": \"x\", \"type\": {\"typeKind\": \"STRING\"} }, { \"name\": \"y\", \"type\": { \"typeKind\": \"ARRAY\", \"arrayElementType\": {\"typeKind\": \"DATE\"} } } ] } }"
}
}
},
"description": "Output only. This field will be populated if a TRANSFORM clause was used to train a model. TRANSFORM clause (if used) takes feature_columns as input and outputs transform_columns. transform_columns then are used to train the model."
}
},
"required": [
"PCID",
"projectId",
"datasetId",
"modelId"
]
}
````
***
## google-bigquery\_projects\_list
RPC to list projects to which the user has been granted any project role. Users of this method are encouraged to consider the [Resource Manager](https://cloud.google.com/resource-manager/docs/) API, which provides the underlying data for this method and has more capabilities.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ------------ | ------- | -------- | ------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `maxResults` | integer | No | — | `maxResults` unset returns all results, up to 50 per page. Additionally, the number of projects in a page may be fewer than `maxResults` because projects are retrieved and then filtered to only projects with the BigQuery API enabled. |
| `pageToken` | string | No | — | Page token, returned by a previous call, to request the next page of results. If not present, no further pages are present. |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"maxResults": {
"type": "integer",
"description": "`maxResults` unset returns all results, up to 50 per page. Additionally, the number of projects in a page may be fewer than `maxResults` because projects are retrieved and then filtered to only projects with the BigQuery API enabled."
},
"pageToken": {
"type": "string",
"description": "Page token, returned by a previous call, to request the next page of results. If not present, no further pages are present."
}
},
"required": [
"PCID"
]
}
```
***
## google-bigquery\_routines\_delete
Deletes the routine specified by routineId from the dataset.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ----------- | ------ | -------- | ------- | --------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the routine to delete |
| `datasetId` | string | Yes | — | Required. Dataset ID of the routine to delete |
| `routineId` | string | Yes | — | Required. Routine ID of the routine to delete |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the routine to delete"
},
"datasetId": {
"type": "string",
"description": "Required. Dataset ID of the routine to delete"
},
"routineId": {
"type": "string",
"description": "Required. Routine ID of the routine to delete"
}
},
"required": [
"PCID",
"projectId",
"datasetId",
"routineId"
]
}
```
***
## google-bigquery\_routines\_get
Gets the specified routine resource by routine ID.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ----------- | ------ | -------- | ------- | -------------------------------------------------------------------------------------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the requested routine |
| `datasetId` | string | Yes | — | Required. Dataset ID of the requested routine |
| `routineId` | string | Yes | — | Required. Routine ID of the requested routine |
| `readMask` | string | No | — | If set, only the Routine fields in the field mask are returned in the response. If unset, all Routine fields are returned. |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the requested routine"
},
"datasetId": {
"type": "string",
"description": "Required. Dataset ID of the requested routine"
},
"routineId": {
"type": "string",
"description": "Required. Routine ID of the requested routine"
},
"readMask": {
"type": "string",
"description": "If set, only the Routine fields in the field mask are returned in the response. If unset, all Routine fields are returned."
}
},
"required": [
"PCID",
"projectId",
"datasetId",
"routineId"
]
}
```
***
## google-bigquery\_routines\_insert
Creates a new routine in the dataset.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ----------------------- | --------- | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `projectId` | string | Yes | — | Required. Project ID of the new routine |
| `datasetId` | string | Yes | — | Required. Dataset ID of the new routine |
| `arguments` | object\[] | No | — | Optional. |
| `creationTime` | string | No | — | Output only. The time when this routine was created, in milliseconds since the epoch. |
| `dataGovernanceType` | string | No | — | Optional. If set to `DATA_MASKING`, the function is validated and made available as a masking function. For more information, see [Create custom masking routines](https://cloud.google.com/bigquery/docs/user-defined-functions#custom-mask). |
| `definitionBody` | string | Yes | — | Required. The body of the routine. For functions, this is the expression in the AS clause. If language=SQL, it is the substring inside (but excluding) the parentheses. For example, for the function created with the following statement: `CREATE FUNCTION JoinLines(x string, y string) as (concat(x, "\n", y))` The definition\_body is `concat(x, "\n", y)` (\n is not replaced with linebreak). If language=JAVASCRIPT, it is the evaluated string in the AS clause. For example, for the function created with the following statement: `CREATE FUNCTION f() RETURNS STRING LANGUAGE js AS 'return "\n";\n'` The definition\_body is `return "\n";\n` Note that both \n are replaced with linebreaks. |
| `description` | string | No | — | Optional. The description of the routine, if defined. |
| `determinismLevel` | string | No | — | Optional. The determinism level of the JavaScript UDF, if defined. |
| `etag` | string | No | — | Output only. A hash of this resource. |
| `importedLibraries` | string\[] | No | — | Optional. If language = "JAVASCRIPT", this field stores the path of the imported JAVASCRIPT libraries. |
| `language` | string | No | — | Optional. Defaults to "SQL" if remote\_function\_options field is absent, not set otherwise. |
| `lastModifiedTime` | string | No | — | Output only. The time when this routine was last modified, in milliseconds since the epoch. |
| `remoteFunctionOptions` | object | No | — | Options for a remote user-defined function. |
| `returnTableType` | object | No | — | A table type |
| `returnType` | object | No | — | The data type of a variable such as a function argument. Examples include: \* INT64: `{"typeKind": "INT64"}` \* ARRAY: \{ "typeKind": "ARRAY", "arrayElementType": \{"typeKind": "STRING"} } \* STRUCT>: \{ "typeKind": "STRUCT", "structType": \{ "fields": \[ \{ "name": "x", "type": \{"typeKind": "STRING"} }, \{ "name": "y", "type": \{ "typeKind": "ARRAY", "arrayElementType": \{"typeKind": "DATE"} } } ] } } |
| `routineReference` | object | No | — | Id path of a routine. |
| `routineType` | string | Yes | — | Required. The type of routine. |
| `securityMode` | string | No | — | Optional. The security mode of the routine, if defined. If not defined, the security mode is automatically determined from the routine's configuration. |
| `sparkOptions` | object | No | — | Options for a user-defined Spark routine. |
| `strictMode` | boolean | No | — | Optional. Use this option to catch many common errors. Error checking is not exhaustive, and successfully creating a procedure doesn't guarantee that the procedure will successfully execute at runtime. If `strictMode` is set to `TRUE`, the procedure body is further checked for errors such as non-existent tables or columns. The `CREATE PROCEDURE` statement fails if the body fails any of these checks. If `strictMode` is set to `FALSE`, the procedure body is checked only for syntax. For procedures that invoke themselves recursively, specify `strictMode=FALSE` to avoid non-existent procedure errors during validation. Default value is `TRUE`. |
````json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the new routine"
},
"datasetId": {
"type": "string",
"description": "Required. Dataset ID of the new routine"
},
"arguments": {
"type": "array",
"items": {
"type": "object",
"properties": {
"argumentKind": {
"type": "string",
"enum": [
"ARGUMENT_KIND_UNSPECIFIED",
"FIXED_TYPE",
"ANY_TYPE"
],
"description": "Optional. Defaults to FIXED_TYPE."
},
"dataType": {
"type": "object",
"description": "The data type of a variable such as a function argument. Examples include: * INT64: `{\"typeKind\": \"INT64\"}` * ARRAY: { \"typeKind\": \"ARRAY\", \"arrayElementType\": {\"typeKind\": \"STRING\"} } * STRUCT>: { \"typeKind\": \"STRUCT\", \"structType\": { \"fields\": [ { \"name\": \"x\", \"type\": {\"typeKind\": \"STRING\"} }, { \"name\": \"y\", \"type\": { \"typeKind\": \"ARRAY\", \"arrayElementType\": {\"typeKind\": \"DATE\"} } } ] } }"
},
"isAggregate": {
"type": "boolean",
"description": "Optional. Whether the argument is an aggregate function parameter. Must be Unset for routine types other than AGGREGATE_FUNCTION. For AGGREGATE_FUNCTION, if set to false, it is equivalent to adding \"NOT AGGREGATE\" clause in DDL; Otherwise, it is equivalent to omitting \"NOT AGGREGATE\" clause in DDL."
},
"mode": {
"type": "string",
"enum": [
"MODE_UNSPECIFIED",
"IN",
"OUT",
"INOUT"
],
"description": "Optional. Specifies whether the argument is input or output. Can be set for procedures only."
},
"name": {
"type": "string",
"description": "Optional. The name of this argument. Can be absent for function return argument."
}
}
},
"description": "Optional."
},
"creationTime": {
"type": "string",
"description": "Output only. The time when this routine was created, in milliseconds since the epoch."
},
"dataGovernanceType": {
"type": "string",
"description": "Optional. If set to `DATA_MASKING`, the function is validated and made available as a masking function. For more information, see [Create custom masking routines](https://cloud.google.com/bigquery/docs/user-defined-functions#custom-mask).",
"enum": [
"DATA_GOVERNANCE_TYPE_UNSPECIFIED",
"DATA_MASKING"
]
},
"definitionBody": {
"type": "string",
"description": "Required. The body of the routine. For functions, this is the expression in the AS clause. If language=SQL, it is the substring inside (but excluding) the parentheses. For example, for the function created with the following statement: `CREATE FUNCTION JoinLines(x string, y string) as (concat(x, \"\\n\", y))` The definition_body is `concat(x, \"\\n\", y)` (\\n is not replaced with linebreak). If language=JAVASCRIPT, it is the evaluated string in the AS clause. For example, for the function created with the following statement: `CREATE FUNCTION f() RETURNS STRING LANGUAGE js AS 'return \"\\n\";\\n'` The definition_body is `return \"\\n\";\\n` Note that both \\n are replaced with linebreaks."
},
"description": {
"type": "string",
"description": "Optional. The description of the routine, if defined."
},
"determinismLevel": {
"type": "string",
"description": "Optional. The determinism level of the JavaScript UDF, if defined.",
"enum": [
"DETERMINISM_LEVEL_UNSPECIFIED",
"DETERMINISTIC",
"NOT_DETERMINISTIC"
]
},
"etag": {
"type": "string",
"description": "Output only. A hash of this resource."
},
"importedLibraries": {
"type": "array",
"items": {
"type": "string"
},
"description": "Optional. If language = \"JAVASCRIPT\", this field stores the path of the imported JAVASCRIPT libraries."
},
"language": {
"type": "string",
"description": "Optional. Defaults to \"SQL\" if remote_function_options field is absent, not set otherwise.",
"enum": [
"LANGUAGE_UNSPECIFIED",
"SQL",
"JAVASCRIPT",
"PYTHON",
"JAVA",
"SCALA"
]
},
"lastModifiedTime": {
"type": "string",
"description": "Output only. The time when this routine was last modified, in milliseconds since the epoch."
},
"remoteFunctionOptions": {
"type": "object",
"description": "Options for a remote user-defined function.",
"properties": {
"connection": {
"type": "string",
"description": "Fully qualified name of the user-provided connection object which holds the authentication information to send requests to the remote service. Format: ```\"projects/{projectId}/locations/{locationId}/connections/{connectionId}\"```"
},
"endpoint": {
"type": "string",
"description": "Endpoint of the user-provided remote service, e.g. ```https://us-east1-my_gcf_project.cloudfunctions.net/remote_add```"
},
"maxBatchingRows": {
"type": "string",
"description": "Max number of rows in each batch sent to the remote service. If absent or if 0, BigQuery dynamically decides the number of rows in a batch."
},
"userDefinedContext": {
"type": "object",
"description": "User-defined context as a set of key/value pairs, which will be sent as function invocation context together with batched arguments in the requests to the remote service. The total number of bytes of keys and values must be less than 8KB."
}
}
},
"returnTableType": {
"type": "object",
"description": "A table type",
"properties": {
"columns": {
"type": "array",
"items": {
"type": "object"
},
"description": "The columns in this table type"
}
}
},
"returnType": {
"type": "object",
"description": "The data type of a variable such as a function argument. Examples include: * INT64: `{\"typeKind\": \"INT64\"}` * ARRAY: { \"typeKind\": \"ARRAY\", \"arrayElementType\": {\"typeKind\": \"STRING\"} } * STRUCT>: { \"typeKind\": \"STRUCT\", \"structType\": { \"fields\": [ { \"name\": \"x\", \"type\": {\"typeKind\": \"STRING\"} }, { \"name\": \"y\", \"type\": { \"typeKind\": \"ARRAY\", \"arrayElementType\": {\"typeKind\": \"DATE\"} } } ] } }",
"properties": {
"arrayElementType": {
"type": "object",
"description": "Circular schema reference: #/components/schemas/StandardSqlDataType"
},
"rangeElementType": {
"type": "object",
"description": "Circular schema reference: #/components/schemas/StandardSqlDataType"
},
"structType": {
"type": "object",
"description": "The representation of a SQL STRUCT type."
},
"typeKind": {
"type": "string",
"description": "Required. The top level type of this field. Can be any GoogleSQL data type (e.g., \"INT64\", \"DATE\", \"ARRAY\").",
"enum": [
"TYPE_KIND_UNSPECIFIED",
"INT64",
"BOOL",
"FLOAT64",
"STRING",
"BYTES",
"TIMESTAMP",
"DATE",
"TIME",
"DATETIME",
"INTERVAL",
"GEOGRAPHY",
"NUMERIC",
"BIGNUMERIC",
"JSON",
"ARRAY",
"STRUCT",
"RANGE"
]
}
}
},
"routineReference": {
"type": "object",
"description": "Id path of a routine.",
"properties": {
"datasetId": {
"type": "string",
"description": "Required. The ID of the dataset containing this routine."
},
"projectId": {
"type": "string",
"description": "Required. The ID of the project containing this routine."
},
"routineId": {
"type": "string",
"description": "Required. The ID of the routine. The ID must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_). The maximum length is 256 characters."
}
}
},
"routineType": {
"type": "string",
"description": "Required. The type of routine.",
"enum": [
"ROUTINE_TYPE_UNSPECIFIED",
"SCALAR_FUNCTION",
"PROCEDURE",
"TABLE_VALUED_FUNCTION",
"AGGREGATE_FUNCTION"
]
},
"securityMode": {
"type": "string",
"description": "Optional. The security mode of the routine, if defined. If not defined, the security mode is automatically determined from the routine's configuration.",
"enum": [
"SECURITY_MODE_UNSPECIFIED",
"DEFINER",
"INVOKER"
]
},
"sparkOptions": {
"type": "object",
"description": "Options for a user-defined Spark routine.",
"properties": {
"archiveUris": {
"type": "array",
"items": {
"type": "string"
},
"description": "Archive files to be extracted into the working directory of each executor. For more information about Apache Spark, see [Apache Spark](https://spark.apache.org/docs/latest/index.html)."
},
"connection": {
"type": "string",
"description": "Fully qualified name of the user-provided Spark connection object. Format: ```\"projects/{project_id}/locations/{location_id}/connections/{connection_id}\"```"
},
"containerImage": {
"type": "string",
"description": "Custom container image for the runtime environment."
},
"fileUris": {
"type": "array",
"items": {
"type": "string"
},
"description": "Files to be placed in the working directory of each executor. For more information about Apache Spark, see [Apache Spark](https://spark.apache.org/docs/latest/index.html)."
},
"jarUris": {
"type": "array",
"items": {
"type": "string"
},
"description": "JARs to include on the driver and executor CLASSPATH. For more information about Apache Spark, see [Apache Spark](https://spark.apache.org/docs/latest/index.html)."
},
"mainClass": {
"type": "string",
"description": "The fully qualified name of a class in jar_uris, for example, com.example.wordcount. Exactly one of main_class and main_jar_uri field should be set for Java/Scala language type."
},
"mainFileUri": {
"type": "string",
"description": "The main file/jar URI of the Spark application. Exactly one of the definition_body field and the main_file_uri field must be set for Python. Exactly one of main_class and main_file_uri field should be set for Java/Scala language type."
},
"properties": {
"type": "object",
"description": "Configuration properties as a set of key/value pairs, which will be passed on to the Spark application. For more information, see [Apache Spark](https://spark.apache.org/docs/latest/index.html) and the [procedure option list](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#procedure_option_list)."
},
"pyFileUris": {
"type": "array",
"items": {
"type": "string"
},
"description": "Python files to be placed on the PYTHONPATH for PySpark application. Supported file types: `.py`, `.egg`, and `.zip`. For more information about Apache Spark, see [Apache Spark](https://spark.apache.org/docs/latest/index.html)."
},
"runtimeVersion": {
"type": "string",
"description": "Runtime version. If not specified, the default runtime version is used."
}
}
},
"strictMode": {
"type": "boolean",
"description": "Optional. Use this option to catch many common errors. Error checking is not exhaustive, and successfully creating a procedure doesn't guarantee that the procedure will successfully execute at runtime. If `strictMode` is set to `TRUE`, the procedure body is further checked for errors such as non-existent tables or columns. The `CREATE PROCEDURE` statement fails if the body fails any of these checks. If `strictMode` is set to `FALSE`, the procedure body is checked only for syntax. For procedures that invoke themselves recursively, specify `strictMode=FALSE` to avoid non-existent procedure errors during validation. Default value is `TRUE`."
}
},
"required": [
"PCID",
"projectId",
"datasetId",
"definitionBody",
"routineType"
]
}
````
***
## google-bigquery\_routines\_list
Lists all routines in the specified dataset. Requires the READER dataset role.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ------------ | ------- | -------- | ------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the routines to list |
| `datasetId` | string | Yes | — | Required. Dataset ID of the routines to list |
| `filter` | string | No | — | If set, then only the Routines matching this filter are returned. The supported format is `routineType:{RoutineType}`, where `{RoutineType}` is a RoutineType enum. For example: `routineType:SCALAR_FUNCTION`. |
| `maxResults` | integer | No | — | The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection. |
| `pageToken` | string | No | — | Page token, returned by a previous call, to request the next page of results |
| `readMask` | string | No | — | If set, then only the Routine fields in the field mask, as well as project\_id, dataset\_id and routine\_id, are returned in the response. If unset, then the following Routine fields are returned: etag, project\_id, dataset\_id, routine\_id, routine\_type, creation\_time, last\_modified\_time, and language. |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the routines to list"
},
"datasetId": {
"type": "string",
"description": "Required. Dataset ID of the routines to list"
},
"filter": {
"type": "string",
"description": "If set, then only the Routines matching this filter are returned. The supported format is `routineType:{RoutineType}`, where `{RoutineType}` is a RoutineType enum. For example: `routineType:SCALAR_FUNCTION`."
},
"maxResults": {
"type": "integer",
"description": "The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection."
},
"pageToken": {
"type": "string",
"description": "Page token, returned by a previous call, to request the next page of results"
},
"readMask": {
"type": "string",
"description": "If set, then only the Routine fields in the field mask, as well as project_id, dataset_id and routine_id, are returned in the response. If unset, then the following Routine fields are returned: etag, project_id, dataset_id, routine_id, routine_type, creation_time, last_modified_time, and language."
}
},
"required": [
"PCID",
"projectId",
"datasetId"
]
}
```
***
## google-bigquery\_routines\_update
Updates information in an existing routine. The update method replaces the entire Routine resource.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ----------------------- | --------- | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `projectId` | string | Yes | — | Required. Project ID of the routine to update |
| `datasetId` | string | Yes | — | Required. Dataset ID of the routine to update |
| `routineId` | string | Yes | — | Required. Routine ID of the routine to update |
| `arguments` | object\[] | No | — | Optional. |
| `creationTime` | string | No | — | Output only. The time when this routine was created, in milliseconds since the epoch. |
| `dataGovernanceType` | string | No | — | Optional. If set to `DATA_MASKING`, the function is validated and made available as a masking function. For more information, see [Create custom masking routines](https://cloud.google.com/bigquery/docs/user-defined-functions#custom-mask). |
| `definitionBody` | string | No | — | Required. The body of the routine. For functions, this is the expression in the AS clause. If language=SQL, it is the substring inside (but excluding) the parentheses. For example, for the function created with the following statement: `CREATE FUNCTION JoinLines(x string, y string) as (concat(x, "\n", y))` The definition\_body is `concat(x, "\n", y)` (\n is not replaced with linebreak). If language=JAVASCRIPT, it is the evaluated string in the AS clause. For example, for the function created with the following statement: `CREATE FUNCTION f() RETURNS STRING LANGUAGE js AS 'return "\n";\n'` The definition\_body is `return "\n";\n` Note that both \n are replaced with linebreaks. |
| `description` | string | No | — | Optional. The description of the routine, if defined. |
| `determinismLevel` | string | No | — | Optional. The determinism level of the JavaScript UDF, if defined. |
| `etag` | string | No | — | Output only. A hash of this resource. |
| `importedLibraries` | string\[] | No | — | Optional. If language = "JAVASCRIPT", this field stores the path of the imported JAVASCRIPT libraries. |
| `language` | string | No | — | Optional. Defaults to "SQL" if remote\_function\_options field is absent, not set otherwise. |
| `lastModifiedTime` | string | No | — | Output only. The time when this routine was last modified, in milliseconds since the epoch. |
| `remoteFunctionOptions` | object | No | — | Options for a remote user-defined function. |
| `returnTableType` | object | No | — | A table type |
| `returnType` | object | No | — | The data type of a variable such as a function argument. Examples include: \* INT64: `{"typeKind": "INT64"}` \* ARRAY: \{ "typeKind": "ARRAY", "arrayElementType": \{"typeKind": "STRING"} } \* STRUCT>: \{ "typeKind": "STRUCT", "structType": \{ "fields": \[ \{ "name": "x", "type": \{"typeKind": "STRING"} }, \{ "name": "y", "type": \{ "typeKind": "ARRAY", "arrayElementType": \{"typeKind": "DATE"} } } ] } } |
| `routineReference` | object | No | — | Id path of a routine. |
| `routineType` | string | No | — | Required. The type of routine. |
| `securityMode` | string | No | — | Optional. The security mode of the routine, if defined. If not defined, the security mode is automatically determined from the routine's configuration. |
| `sparkOptions` | object | No | — | Options for a user-defined Spark routine. |
| `strictMode` | boolean | No | — | Optional. Use this option to catch many common errors. Error checking is not exhaustive, and successfully creating a procedure doesn't guarantee that the procedure will successfully execute at runtime. If `strictMode` is set to `TRUE`, the procedure body is further checked for errors such as non-existent tables or columns. The `CREATE PROCEDURE` statement fails if the body fails any of these checks. If `strictMode` is set to `FALSE`, the procedure body is checked only for syntax. For procedures that invoke themselves recursively, specify `strictMode=FALSE` to avoid non-existent procedure errors during validation. Default value is `TRUE`. |
````json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the routine to update"
},
"datasetId": {
"type": "string",
"description": "Required. Dataset ID of the routine to update"
},
"routineId": {
"type": "string",
"description": "Required. Routine ID of the routine to update"
},
"arguments": {
"type": "array",
"items": {
"type": "object",
"properties": {
"argumentKind": {
"type": "string",
"enum": [
"ARGUMENT_KIND_UNSPECIFIED",
"FIXED_TYPE",
"ANY_TYPE"
],
"description": "Optional. Defaults to FIXED_TYPE."
},
"dataType": {
"type": "object",
"description": "The data type of a variable such as a function argument. Examples include: * INT64: `{\"typeKind\": \"INT64\"}` * ARRAY: { \"typeKind\": \"ARRAY\", \"arrayElementType\": {\"typeKind\": \"STRING\"} } * STRUCT>: { \"typeKind\": \"STRUCT\", \"structType\": { \"fields\": [ { \"name\": \"x\", \"type\": {\"typeKind\": \"STRING\"} }, { \"name\": \"y\", \"type\": { \"typeKind\": \"ARRAY\", \"arrayElementType\": {\"typeKind\": \"DATE\"} } } ] } }"
},
"isAggregate": {
"type": "boolean",
"description": "Optional. Whether the argument is an aggregate function parameter. Must be Unset for routine types other than AGGREGATE_FUNCTION. For AGGREGATE_FUNCTION, if set to false, it is equivalent to adding \"NOT AGGREGATE\" clause in DDL; Otherwise, it is equivalent to omitting \"NOT AGGREGATE\" clause in DDL."
},
"mode": {
"type": "string",
"enum": [
"MODE_UNSPECIFIED",
"IN",
"OUT",
"INOUT"
],
"description": "Optional. Specifies whether the argument is input or output. Can be set for procedures only."
},
"name": {
"type": "string",
"description": "Optional. The name of this argument. Can be absent for function return argument."
}
}
},
"description": "Optional."
},
"creationTime": {
"type": "string",
"description": "Output only. The time when this routine was created, in milliseconds since the epoch."
},
"dataGovernanceType": {
"type": "string",
"description": "Optional. If set to `DATA_MASKING`, the function is validated and made available as a masking function. For more information, see [Create custom masking routines](https://cloud.google.com/bigquery/docs/user-defined-functions#custom-mask).",
"enum": [
"DATA_GOVERNANCE_TYPE_UNSPECIFIED",
"DATA_MASKING"
]
},
"definitionBody": {
"type": "string",
"description": "Required. The body of the routine. For functions, this is the expression in the AS clause. If language=SQL, it is the substring inside (but excluding) the parentheses. For example, for the function created with the following statement: `CREATE FUNCTION JoinLines(x string, y string) as (concat(x, \"\\n\", y))` The definition_body is `concat(x, \"\\n\", y)` (\\n is not replaced with linebreak). If language=JAVASCRIPT, it is the evaluated string in the AS clause. For example, for the function created with the following statement: `CREATE FUNCTION f() RETURNS STRING LANGUAGE js AS 'return \"\\n\";\\n'` The definition_body is `return \"\\n\";\\n` Note that both \\n are replaced with linebreaks."
},
"description": {
"type": "string",
"description": "Optional. The description of the routine, if defined."
},
"determinismLevel": {
"type": "string",
"description": "Optional. The determinism level of the JavaScript UDF, if defined.",
"enum": [
"DETERMINISM_LEVEL_UNSPECIFIED",
"DETERMINISTIC",
"NOT_DETERMINISTIC"
]
},
"etag": {
"type": "string",
"description": "Output only. A hash of this resource."
},
"importedLibraries": {
"type": "array",
"items": {
"type": "string"
},
"description": "Optional. If language = \"JAVASCRIPT\", this field stores the path of the imported JAVASCRIPT libraries."
},
"language": {
"type": "string",
"description": "Optional. Defaults to \"SQL\" if remote_function_options field is absent, not set otherwise.",
"enum": [
"LANGUAGE_UNSPECIFIED",
"SQL",
"JAVASCRIPT",
"PYTHON",
"JAVA",
"SCALA"
]
},
"lastModifiedTime": {
"type": "string",
"description": "Output only. The time when this routine was last modified, in milliseconds since the epoch."
},
"remoteFunctionOptions": {
"type": "object",
"description": "Options for a remote user-defined function.",
"properties": {
"connection": {
"type": "string",
"description": "Fully qualified name of the user-provided connection object which holds the authentication information to send requests to the remote service. Format: ```\"projects/{projectId}/locations/{locationId}/connections/{connectionId}\"```"
},
"endpoint": {
"type": "string",
"description": "Endpoint of the user-provided remote service, e.g. ```https://us-east1-my_gcf_project.cloudfunctions.net/remote_add```"
},
"maxBatchingRows": {
"type": "string",
"description": "Max number of rows in each batch sent to the remote service. If absent or if 0, BigQuery dynamically decides the number of rows in a batch."
},
"userDefinedContext": {
"type": "object",
"description": "User-defined context as a set of key/value pairs, which will be sent as function invocation context together with batched arguments in the requests to the remote service. The total number of bytes of keys and values must be less than 8KB."
}
}
},
"returnTableType": {
"type": "object",
"description": "A table type",
"properties": {
"columns": {
"type": "array",
"items": {
"type": "object"
},
"description": "The columns in this table type"
}
}
},
"returnType": {
"type": "object",
"description": "The data type of a variable such as a function argument. Examples include: * INT64: `{\"typeKind\": \"INT64\"}` * ARRAY: { \"typeKind\": \"ARRAY\", \"arrayElementType\": {\"typeKind\": \"STRING\"} } * STRUCT>: { \"typeKind\": \"STRUCT\", \"structType\": { \"fields\": [ { \"name\": \"x\", \"type\": {\"typeKind\": \"STRING\"} }, { \"name\": \"y\", \"type\": { \"typeKind\": \"ARRAY\", \"arrayElementType\": {\"typeKind\": \"DATE\"} } } ] } }",
"properties": {
"arrayElementType": {
"type": "object",
"description": "Circular schema reference: #/components/schemas/StandardSqlDataType"
},
"rangeElementType": {
"type": "object",
"description": "Circular schema reference: #/components/schemas/StandardSqlDataType"
},
"structType": {
"type": "object",
"description": "The representation of a SQL STRUCT type."
},
"typeKind": {
"type": "string",
"description": "Required. The top level type of this field. Can be any GoogleSQL data type (e.g., \"INT64\", \"DATE\", \"ARRAY\").",
"enum": [
"TYPE_KIND_UNSPECIFIED",
"INT64",
"BOOL",
"FLOAT64",
"STRING",
"BYTES",
"TIMESTAMP",
"DATE",
"TIME",
"DATETIME",
"INTERVAL",
"GEOGRAPHY",
"NUMERIC",
"BIGNUMERIC",
"JSON",
"ARRAY",
"STRUCT",
"RANGE"
]
}
}
},
"routineReference": {
"type": "object",
"description": "Id path of a routine.",
"properties": {
"datasetId": {
"type": "string",
"description": "Required. The ID of the dataset containing this routine."
},
"projectId": {
"type": "string",
"description": "Required. The ID of the project containing this routine."
},
"routineId": {
"type": "string",
"description": "Required. The ID of the routine. The ID must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_). The maximum length is 256 characters."
}
}
},
"routineType": {
"type": "string",
"description": "Required. The type of routine.",
"enum": [
"ROUTINE_TYPE_UNSPECIFIED",
"SCALAR_FUNCTION",
"PROCEDURE",
"TABLE_VALUED_FUNCTION",
"AGGREGATE_FUNCTION"
]
},
"securityMode": {
"type": "string",
"description": "Optional. The security mode of the routine, if defined. If not defined, the security mode is automatically determined from the routine's configuration.",
"enum": [
"SECURITY_MODE_UNSPECIFIED",
"DEFINER",
"INVOKER"
]
},
"sparkOptions": {
"type": "object",
"description": "Options for a user-defined Spark routine.",
"properties": {
"archiveUris": {
"type": "array",
"items": {
"type": "string"
},
"description": "Archive files to be extracted into the working directory of each executor. For more information about Apache Spark, see [Apache Spark](https://spark.apache.org/docs/latest/index.html)."
},
"connection": {
"type": "string",
"description": "Fully qualified name of the user-provided Spark connection object. Format: ```\"projects/{project_id}/locations/{location_id}/connections/{connection_id}\"```"
},
"containerImage": {
"type": "string",
"description": "Custom container image for the runtime environment."
},
"fileUris": {
"type": "array",
"items": {
"type": "string"
},
"description": "Files to be placed in the working directory of each executor. For more information about Apache Spark, see [Apache Spark](https://spark.apache.org/docs/latest/index.html)."
},
"jarUris": {
"type": "array",
"items": {
"type": "string"
},
"description": "JARs to include on the driver and executor CLASSPATH. For more information about Apache Spark, see [Apache Spark](https://spark.apache.org/docs/latest/index.html)."
},
"mainClass": {
"type": "string",
"description": "The fully qualified name of a class in jar_uris, for example, com.example.wordcount. Exactly one of main_class and main_jar_uri field should be set for Java/Scala language type."
},
"mainFileUri": {
"type": "string",
"description": "The main file/jar URI of the Spark application. Exactly one of the definition_body field and the main_file_uri field must be set for Python. Exactly one of main_class and main_file_uri field should be set for Java/Scala language type."
},
"properties": {
"type": "object",
"description": "Configuration properties as a set of key/value pairs, which will be passed on to the Spark application. For more information, see [Apache Spark](https://spark.apache.org/docs/latest/index.html) and the [procedure option list](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#procedure_option_list)."
},
"pyFileUris": {
"type": "array",
"items": {
"type": "string"
},
"description": "Python files to be placed on the PYTHONPATH for PySpark application. Supported file types: `.py`, `.egg`, and `.zip`. For more information about Apache Spark, see [Apache Spark](https://spark.apache.org/docs/latest/index.html)."
},
"runtimeVersion": {
"type": "string",
"description": "Runtime version. If not specified, the default runtime version is used."
}
}
},
"strictMode": {
"type": "boolean",
"description": "Optional. Use this option to catch many common errors. Error checking is not exhaustive, and successfully creating a procedure doesn't guarantee that the procedure will successfully execute at runtime. If `strictMode` is set to `TRUE`, the procedure body is further checked for errors such as non-existent tables or columns. The `CREATE PROCEDURE` statement fails if the body fails any of these checks. If `strictMode` is set to `FALSE`, the procedure body is checked only for syntax. For procedures that invoke themselves recursively, specify `strictMode=FALSE` to avoid non-existent procedure errors during validation. Default value is `TRUE`."
}
},
"required": [
"PCID",
"projectId",
"datasetId",
"routineId"
]
}
````
***
## google-bigquery\_tables\_delete
Deletes the table specified by tableId from the dataset. If the table contains data, all the data will be deleted.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ----------- | ------ | -------- | ------- | ------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the table to delete |
| `datasetId` | string | Yes | — | Required. Dataset ID of the table to delete |
| `tableId` | string | Yes | — | Required. Table ID of the table to delete |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the table to delete"
},
"datasetId": {
"type": "string",
"description": "Required. Dataset ID of the table to delete"
},
"tableId": {
"type": "string",
"description": "Required. Table ID of the table to delete"
}
},
"required": [
"PCID",
"projectId",
"datasetId",
"tableId"
]
}
```
***
## google-bigquery\_tables\_get
Gets the specified table resource by table ID. This method does not return the data in the table, it only returns the table resource, which describes the structure of this table.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ---------------- | ------ | -------- | ------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the requested table |
| `datasetId` | string | Yes | — | Required. Dataset ID of the requested table |
| `tableId` | string | Yes | — | Required. Table ID of the requested table |
| `selectedFields` | string | No | — | List of table schema fields to return (comma-separated). If unspecified, all fields are returned. A fieldMask cannot be used here because the fields will automatically be converted from camelCase to snake\_case and the conversion will fail if there are underscores. Since these are fields in BigQuery table schemas, underscores are allowed. |
| `view` | string | No | — | Optional. Specifies the view that determines which table information is returned. By default, basic table information and storage statistics (STORAGE\_STATS) are returned. |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the requested table"
},
"datasetId": {
"type": "string",
"description": "Required. Dataset ID of the requested table"
},
"tableId": {
"type": "string",
"description": "Required. Table ID of the requested table"
},
"selectedFields": {
"type": "string",
"description": "List of table schema fields to return (comma-separated). If unspecified, all fields are returned. A fieldMask cannot be used here because the fields will automatically be converted from camelCase to snake_case and the conversion will fail if there are underscores. Since these are fields in BigQuery table schemas, underscores are allowed."
},
"view": {
"type": "string",
"description": "Optional. Specifies the view that determines which table information is returned. By default, basic table information and storage statistics (STORAGE_STATS) are returned.",
"enum": [
"TABLE_METADATA_VIEW_UNSPECIFIED",
"BASIC",
"STORAGE_STATS",
"FULL"
]
}
},
"required": [
"PCID",
"projectId",
"datasetId",
"tableId"
]
}
```
***
## google-bigquery\_tables\_insert
Creates a new, empty table in the dataset.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ---------------------------- | --------- | -------- | ------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the new table |
| `datasetId` | string | Yes | — | Required. Dataset ID of the new table |
| `biglakeConfiguration` | object | No | — | Configuration for BigLake managed tables. |
| `cloneDefinition` | object | No | — | Information about base table and clone time of a table clone. |
| `clustering` | object | No | — | Configures table clustering. |
| `creationTime` | string | No | — | Output only. The time when this table was created, in milliseconds since the epoch. |
| `defaultCollation` | string | No | — | Optional. Defines the default collation specification of new STRING fields in the table. During table creation or update, if a STRING field is added to this table without explicit collation specified, then the table inherits the table default collation. A change to this field affects only fields added afterwards, and does not alter the existing fields. The following values are supported: \* 'und:ci': undetermined locale, case insensitive. \* '': empty string. Default to case-sensitive behavior. |
| `defaultRoundingMode` | string | No | — | Optional. Defines the default rounding mode specification of new decimal fields (NUMERIC OR BIGNUMERIC) in the table. During table creation or update, if a decimal field is added to this table without an explicit rounding mode specified, then the field inherits the table default rounding mode. Changing this field doesn't affect existing fields. |
| `description` | string | No | — | Optional. A user-friendly description of this table. |
| `encryptionConfiguration` | object | No | — | Encryption Configuration |
| `etag` | string | No | — | Output only. A hash of this resource. |
| `expirationTime` | string | No | — | Optional. The time when this table expires, in milliseconds since the epoch. If not present, the table will persist indefinitely. Expired tables will be deleted and their storage reclaimed. The defaultTableExpirationMs property of the encapsulating dataset can be used to set a default expirationTime on newly created tables. |
| `externalDataConfiguration` | object | No | — | External Data Configuration |
| `friendlyName` | string | No | — | Optional. A descriptive name for this table. |
| `id` | string | No | — | Output only. An opaque ID uniquely identifying the table. |
| `kind` | string | No | — | The type of resource ID. |
| `labels` | object | No | — | The labels associated with this table. You can use these to organize and group your tables. Label keys and values can be no longer than 63 characters, can only contain lowercase letters, numeric characters, underscores and dashes. International characters are allowed. Label values are optional. Label keys must start with a letter and each label in the list must have a different key. |
| `lastModifiedTime` | string | No | — | Output only. The time when this table was last modified, in milliseconds since the epoch. |
| `location` | string | No | — | Output only. The geographic location where the table resides. This value is inherited from the dataset. |
| `materializedView` | object | No | — | Definition and configuration of a materialized view. |
| `materializedViewStatus` | object | No | — | Status of a materialized view. The last refresh timestamp status is omitted here, but is present in the MaterializedViewDefinition message. |
| `maxStaleness` | string | No | — | Optional. The maximum staleness of data that could be returned when the table (or stale MV) is queried. Staleness encoded as a string encoding of sql IntervalValue type. |
| `model` | object | No | — | The model value |
| `numActiveLogicalBytes` | string | No | — | Output only. Number of logical bytes that are less than 90 days old. |
| `numActivePhysicalBytes` | string | No | — | Output only. Number of physical bytes less than 90 days old. This data is not kept in real time, and might be delayed by a few seconds to a few minutes. |
| `numBytes` | string | No | — | Output only. The size of this table in logical bytes, excluding any data in the streaming buffer. |
| `numLongTermBytes` | string | No | — | Output only. The number of logical bytes in the table that are considered "long-term storage". |
| `numLongTermLogicalBytes` | string | No | — | Output only. Number of logical bytes that are more than 90 days old. |
| `numLongTermPhysicalBytes` | string | No | — | Output only. Number of physical bytes more than 90 days old. This data is not kept in real time, and might be delayed by a few seconds to a few minutes. |
| `numPartitions` | string | No | — | Output only. The number of partitions present in the table or materialized view. This data is not kept in real time, and might be delayed by a few seconds to a few minutes. |
| `numPhysicalBytes` | string | No | — | Output only. The physical size of this table in bytes. This includes storage used for time travel. |
| `numRows` | string | No | — | Output only. The number of rows of data in this table, excluding any data in the streaming buffer. |
| `numTimeTravelPhysicalBytes` | string | No | — | Output only. Number of physical bytes used by time travel storage (deleted or changed data). This data is not kept in real time, and might be delayed by a few seconds to a few minutes. |
| `numTotalLogicalBytes` | string | No | — | Output only. Total number of logical bytes in the table or materialized view. |
| `numTotalPhysicalBytes` | string | No | — | Output only. The physical size of this table in bytes. This also includes storage used for time travel. This data is not kept in real time, and might be delayed by a few seconds to a few minutes. |
| `rangePartitioning` | object | No | — | Range Partitioning |
| `replicas` | object\[] | No | — | Optional. Output only. Table references of all replicas currently active on the table. |
| `requirePartitionFilter` | boolean | No | — | Optional. If set to true, queries over this table require a partition filter that can be used for partition elimination to be specified. |
| `resourceTags` | object | No | — | \[Optional] The tags associated with this table. Tag keys are globally unique. See additional information on [tags](https://cloud.google.com/iam/docs/tags-access-control#definitions). An object containing a list of "key": value pairs. The key is the namespaced friendly name of the tag key, e.g. "12345/environment" where 12345 is parent id. The value is the friendly short name of the tag value, e.g. "production". |
| `schema` | object | No | — | Schema of a table |
| `selfLink` | string | No | — | Output only. A URL that can be used to access this resource again. |
| `snapshotDefinition` | object | No | — | Information about base table and snapshot time of the snapshot. |
| `streamingBuffer` | object | No | — | Streaming Buffer |
| `tableConstraints` | object | No | — | The TableConstraints defines the primary key and foreign key. |
| `tableReference` | object | No | — | Table Reference |
| `tableReplicationInfo` | object | No | — | Replication info of a table created using `AS REPLICA` DDL like: `CREATE MATERIALIZED VIEW mv1 AS REPLICA OF src_mv` |
| `timePartitioning` | object | No | — | Time Partitioning |
| `type` | string | No | — | Output only. Describes the table type. The following values are supported: \* `TABLE`: A normal BigQuery table. \* `VIEW`: A virtual table defined by a SQL query. \* `EXTERNAL`: A table that references data stored in an external storage system, such as Google Cloud Storage. \* `MATERIALIZED_VIEW`: A precomputed view defined by a SQL query. \* `SNAPSHOT`: An immutable BigQuery table that preserves the contents of a base table at a particular time. See additional information on [table snapshots](/bigquery/docs/table-snapshots-intro). The default value is `TABLE`. |
| `view` | object | No | — | Describes the definition of a logical view. |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the new table"
},
"datasetId": {
"type": "string",
"description": "Required. Dataset ID of the new table"
},
"biglakeConfiguration": {
"type": "object",
"description": "Configuration for BigLake managed tables.",
"properties": {
"connectionId": {
"type": "string",
"description": "Required. The connection specifying the credentials to be used to read and write to external storage, such as Cloud Storage. The connection_id can have the form \"..\" or \"projects//locations//connections/\"."
},
"fileFormat": {
"type": "string",
"description": "Required. The file format the table data is stored in.",
"enum": [
"FILE_FORMAT_UNSPECIFIED",
"PARQUET"
]
},
"storageUri": {
"type": "string",
"description": "Required. The fully qualified location prefix of the external folder where table data is stored. The '*' wildcard character is not allowed. The URI should be in the format \"gs://bucket/path_to_table/\""
},
"tableFormat": {
"type": "string",
"description": "Required. The table format the metadata only snapshots are stored in.",
"enum": [
"TABLE_FORMAT_UNSPECIFIED",
"ICEBERG"
]
}
}
},
"cloneDefinition": {
"type": "object",
"description": "Information about base table and clone time of a table clone.",
"properties": {
"baseTableReference": {
"type": "object",
"description": "Base Table Reference"
},
"cloneTime": {
"type": "string",
"description": "Required. The time at which the base table was cloned. This value is reported in the JSON response using RFC3339 format."
}
}
},
"clustering": {
"type": "object",
"description": "Configures table clustering.",
"properties": {
"fields": {
"type": "array",
"items": {
"type": "string"
},
"description": "One or more fields on which data should be clustered. Only top-level, non-repeated, simple-type fields are supported. The ordering of the clustering fields should be prioritized from most to least important for filtering purposes. Additional information on limitations can be found here: https://cloud.google.com/bigquery/docs/creating-clustered-tables#limitations"
}
}
},
"creationTime": {
"type": "string",
"description": "Output only. The time when this table was created, in milliseconds since the epoch."
},
"defaultCollation": {
"type": "string",
"description": "Optional. Defines the default collation specification of new STRING fields in the table. During table creation or update, if a STRING field is added to this table without explicit collation specified, then the table inherits the table default collation. A change to this field affects only fields added afterwards, and does not alter the existing fields. The following values are supported: * 'und:ci': undetermined locale, case insensitive. * '': empty string. Default to case-sensitive behavior."
},
"defaultRoundingMode": {
"type": "string",
"description": "Optional. Defines the default rounding mode specification of new decimal fields (NUMERIC OR BIGNUMERIC) in the table. During table creation or update, if a decimal field is added to this table without an explicit rounding mode specified, then the field inherits the table default rounding mode. Changing this field doesn't affect existing fields.",
"enum": [
"ROUNDING_MODE_UNSPECIFIED",
"ROUND_HALF_AWAY_FROM_ZERO",
"ROUND_HALF_EVEN"
]
},
"description": {
"type": "string",
"description": "Optional. A user-friendly description of this table."
},
"encryptionConfiguration": {
"type": "object",
"description": "Encryption Configuration",
"properties": {
"kmsKeyName": {
"type": "string",
"description": "Optional. Describes the Cloud KMS encryption key that will be used to protect destination BigQuery table. The BigQuery Service Account associated with your project requires access to this encryption key."
}
}
},
"etag": {
"type": "string",
"description": "Output only. A hash of this resource."
},
"expirationTime": {
"type": "string",
"description": "Optional. The time when this table expires, in milliseconds since the epoch. If not present, the table will persist indefinitely. Expired tables will be deleted and their storage reclaimed. The defaultTableExpirationMs property of the encapsulating dataset can be used to set a default expirationTime on newly created tables."
},
"externalDataConfiguration": {
"type": "object",
"description": "External Data Configuration",
"properties": {
"autodetect": {
"type": "boolean",
"description": "Try to detect schema and format options automatically. Any option specified explicitly will be honored."
},
"avroOptions": {
"type": "object",
"description": "Options for external data sources."
},
"bigtableOptions": {
"type": "object",
"description": "Options specific to Google Cloud Bigtable data sources."
},
"compression": {
"type": "string",
"description": "Optional. The compression type of the data source. Possible values include GZIP and NONE. The default value is NONE. This setting is ignored for Google Cloud Bigtable, Google Cloud Datastore backups, Avro, ORC and Parquet formats. An empty string is an invalid value."
},
"connectionId": {
"type": "string",
"description": "Optional. The connection specifying the credentials to be used to read external storage, such as Azure Blob, Cloud Storage, or S3. The connection_id can have the form \"..\" or \"projects//locations//connections/\"."
},
"csvOptions": {
"type": "object",
"description": "Information related to a CSV data source."
},
"decimalTargetTypes": {
"type": "array",
"items": {
"type": "string",
"enum": [
"DECIMAL_TARGET_TYPE_UNSPECIFIED",
"NUMERIC",
"BIGNUMERIC",
"STRING"
]
},
"description": "Defines the list of possible SQL data types to which the source decimal values are converted. This list and the precision and the scale parameters of the decimal field determine the target type. In the order of NUMERIC, BIGNUMERIC, and STRING, a type is picked if it is in the specified list and if it supports the precision and the scale. STRING supports all precision and scale values. If none of the listed types supports the precision and the scale, the type supporting the widest range in the specified list is picked, and if a value exceeds the supported range when reading the data, an error will be thrown. Example: Suppose the value of this field is [\"NUMERIC\", \"BIGNUMERIC\"]. If (precision,scale) is: * (38,9) -> NUMERIC; * (39,9) -> BIGNUMERIC (NUMERIC cannot hold 30 integer digits); * (38,10) -> BIGNUMERIC (NUMERIC cannot hold 10 fractional digits); * (76,38) -> BIGNUMERIC; * (77,38) -> BIGNUMERIC (error if value exeeds supported range). This field cannot contain duplicate types. The order of the types in this field is ignored. For example, [\"BIGNUMERIC\", \"NUMERIC\"] is the same as [\"NUMERIC\", \"BIGNUMERIC\"] and NUMERIC always takes precedence over BIGNUMERIC. Defaults to [\"NUMERIC\", \"STRING\"] for ORC and [\"NUMERIC\"] for the other file formats."
},
"fileSetSpecType": {
"type": "string",
"description": "Optional. Specifies how source URIs are interpreted for constructing the file set to load. By default source URIs are expanded against the underlying storage. Other options include specifying manifest files. Only applicable to object storage systems.",
"enum": [
"FILE_SET_SPEC_TYPE_FILE_SYSTEM_MATCH",
"FILE_SET_SPEC_TYPE_NEW_LINE_DELIMITED_MANIFEST"
]
},
"googleSheetsOptions": {
"type": "object",
"description": "Options specific to Google Sheets data sources."
},
"hivePartitioningOptions": {
"type": "object",
"description": "Options for configuring hive partitioning detect."
},
"ignoreUnknownValues": {
"type": "boolean",
"description": "Optional. Indicates if BigQuery should allow extra values that are not represented in the table schema. If true, the extra values are ignored. If false, records with extra columns are treated as bad records, and if there are too many bad records, an invalid error is returned in the job result. The default value is false. The sourceFormat property determines what BigQuery treats as an extra value: CSV: Trailing columns JSON: Named values that don't match any column names Google Cloud Bigtable: This setting is ignored. Google Cloud Datastore backups: This setting is ignored. Avro: This setting is ignored. ORC: This setting is ignored. Parquet: This setting is ignored."
},
"jsonExtension": {
"type": "string",
"description": "Optional. Load option to be used together with source_format newline-delimited JSON to indicate that a variant of JSON is being loaded. To load newline-delimited GeoJSON, specify GEOJSON (and source_format must be set to NEWLINE_DELIMITED_JSON).",
"enum": [
"JSON_EXTENSION_UNSPECIFIED",
"GEOJSON"
]
},
"jsonOptions": {
"type": "object",
"description": "Json Options for load and make external tables."
},
"maxBadRecords": {
"type": "integer",
"description": "Optional. The maximum number of bad records that BigQuery can ignore when reading data. If the number of bad records exceeds this value, an invalid error is returned in the job result. The default value is 0, which requires that all records are valid. This setting is ignored for Google Cloud Bigtable, Google Cloud Datastore backups, Avro, ORC and Parquet formats."
},
"metadataCacheMode": {
"type": "string",
"description": "Optional. Metadata Cache Mode for the table. Set this to enable caching of metadata from external data source.",
"enum": [
"METADATA_CACHE_MODE_UNSPECIFIED",
"AUTOMATIC",
"MANUAL"
]
},
"objectMetadata": {
"type": "string",
"description": "Optional. ObjectMetadata is used to create Object Tables. Object Tables contain a listing of objects (with their metadata) found at the source_uris. If ObjectMetadata is set, source_format should be omitted. Currently SIMPLE is the only supported Object Metadata type.",
"enum": [
"OBJECT_METADATA_UNSPECIFIED",
"DIRECTORY",
"SIMPLE"
]
},
"parquetOptions": {
"type": "object",
"description": "Parquet Options for load and make external tables."
},
"referenceFileSchemaUri": {
"type": "string",
"description": "Optional. When creating an external table, the user can provide a reference file with the table schema. This is enabled for the following formats: AVRO, PARQUET, ORC."
},
"schema": {
"type": "object",
"description": "Schema of a table"
},
"sourceFormat": {
"type": "string",
"description": "[Required] The data format. For CSV files, specify \"CSV\". For Google sheets, specify \"GOOGLE_SHEETS\". For newline-delimited JSON, specify \"NEWLINE_DELIMITED_JSON\". For Avro files, specify \"AVRO\". For Google Cloud Datastore backups, specify \"DATASTORE_BACKUP\". For Apache Iceberg tables, specify \"ICEBERG\". For ORC files, specify \"ORC\". For Parquet files, specify \"PARQUET\". [Beta] For Google Cloud Bigtable, specify \"BIGTABLE\"."
},
"sourceUris": {
"type": "array",
"items": {
"type": "string"
},
"description": "[Required] The fully-qualified URIs that point to your data in Google Cloud. For Google Cloud Storage URIs: Each URI can contain one '*' wildcard character and it must come after the 'bucket' name. Size limits related to load jobs apply to external data sources. For Google Cloud Bigtable URIs: Exactly one URI can be specified and it has be a fully specified and valid HTTPS URL for a Google Cloud Bigtable table. For Google Cloud Datastore backups, exactly one URI can be specified. Also, the '*' wildcard character is not allowed."
}
}
},
"friendlyName": {
"type": "string",
"description": "Optional. A descriptive name for this table."
},
"id": {
"type": "string",
"description": "Output only. An opaque ID uniquely identifying the table."
},
"kind": {
"type": "string",
"description": "The type of resource ID."
},
"labels": {
"type": "object",
"description": "The labels associated with this table. You can use these to organize and group your tables. Label keys and values can be no longer than 63 characters, can only contain lowercase letters, numeric characters, underscores and dashes. International characters are allowed. Label values are optional. Label keys must start with a letter and each label in the list must have a different key."
},
"lastModifiedTime": {
"type": "string",
"description": "Output only. The time when this table was last modified, in milliseconds since the epoch."
},
"location": {
"type": "string",
"description": "Output only. The geographic location where the table resides. This value is inherited from the dataset."
},
"materializedView": {
"type": "object",
"description": "Definition and configuration of a materialized view.",
"properties": {
"allowNonIncrementalDefinition": {
"type": "boolean",
"description": "Optional. This option declares authors intention to construct a materialized view that will not be refreshed incrementally."
},
"enableRefresh": {
"type": "boolean",
"description": "Optional. Enable automatic refresh of the materialized view when the base table is updated. The default value is \"true\"."
},
"lastRefreshTime": {
"type": "string",
"description": "Output only. The time when this materialized view was last refreshed, in milliseconds since the epoch."
},
"maxStaleness": {
"type": "string",
"description": "[Optional] Max staleness of data that could be returned when materizlized view is queried (formatted as Google SQL Interval type)."
},
"query": {
"type": "string",
"description": "Required. A query whose results are persisted."
},
"refreshIntervalMs": {
"type": "string",
"description": "Optional. The maximum frequency at which this materialized view will be refreshed. The default value is \"1800000\" (30 minutes)."
}
}
},
"materializedViewStatus": {
"type": "object",
"description": "Status of a materialized view. The last refresh timestamp status is omitted here, but is present in the MaterializedViewDefinition message.",
"properties": {
"lastRefreshStatus": {
"type": "object",
"description": "Error details."
},
"refreshWatermark": {
"type": "string",
"description": "Output only. Refresh watermark of materialized view. The base tables' data were collected into the materialized view cache until this time."
}
}
},
"maxStaleness": {
"type": "string",
"description": "Optional. The maximum staleness of data that could be returned when the table (or stale MV) is queried. Staleness encoded as a string encoding of sql IntervalValue type."
},
"model": {
"type": "object",
"description": "The model value",
"properties": {
"modelOptions": {
"type": "object",
"description": "Deprecated."
},
"trainingRuns": {
"type": "array",
"items": {
"type": "object"
},
"description": "Deprecated."
}
}
},
"numActiveLogicalBytes": {
"type": "string",
"description": "Output only. Number of logical bytes that are less than 90 days old."
},
"numActivePhysicalBytes": {
"type": "string",
"description": "Output only. Number of physical bytes less than 90 days old. This data is not kept in real time, and might be delayed by a few seconds to a few minutes."
},
"numBytes": {
"type": "string",
"description": "Output only. The size of this table in logical bytes, excluding any data in the streaming buffer."
},
"numLongTermBytes": {
"type": "string",
"description": "Output only. The number of logical bytes in the table that are considered \"long-term storage\"."
},
"numLongTermLogicalBytes": {
"type": "string",
"description": "Output only. Number of logical bytes that are more than 90 days old."
},
"numLongTermPhysicalBytes": {
"type": "string",
"description": "Output only. Number of physical bytes more than 90 days old. This data is not kept in real time, and might be delayed by a few seconds to a few minutes."
},
"numPartitions": {
"type": "string",
"description": "Output only. The number of partitions present in the table or materialized view. This data is not kept in real time, and might be delayed by a few seconds to a few minutes."
},
"numPhysicalBytes": {
"type": "string",
"description": "Output only. The physical size of this table in bytes. This includes storage used for time travel."
},
"numRows": {
"type": "string",
"description": "Output only. The number of rows of data in this table, excluding any data in the streaming buffer."
},
"numTimeTravelPhysicalBytes": {
"type": "string",
"description": "Output only. Number of physical bytes used by time travel storage (deleted or changed data). This data is not kept in real time, and might be delayed by a few seconds to a few minutes."
},
"numTotalLogicalBytes": {
"type": "string",
"description": "Output only. Total number of logical bytes in the table or materialized view."
},
"numTotalPhysicalBytes": {
"type": "string",
"description": "Output only. The physical size of this table in bytes. This also includes storage used for time travel. This data is not kept in real time, and might be delayed by a few seconds to a few minutes."
},
"rangePartitioning": {
"type": "object",
"description": "Range Partitioning",
"properties": {
"field": {
"type": "string",
"description": "Required. [Experimental] The table is partitioned by this field. The field must be a top-level NULLABLE/REQUIRED field. The only supported type is INTEGER/INT64."
},
"range": {
"type": "object",
"description": "[Experimental] Defines the ranges for range partitioning."
}
}
},
"replicas": {
"type": "array",
"items": {
"type": "object",
"properties": {
"datasetId": {
"type": "string",
"description": "Required. The ID of the dataset containing this table."
},
"projectId": {
"type": "string",
"description": "Required. The ID of the project containing this table."
},
"tableId": {
"type": "string",
"description": "Required. The ID of the table. The ID can contain Unicode characters in category L (letter), M (mark), N (number), Pc (connector, including underscore), Pd (dash), and Zs (space). For more information, see [General Category](https://wikipedia.org/wiki/Unicode_character_property#General_Category). The maximum length is 1,024 characters. Certain operations allow suffixing of the table ID with a partition decorator, such as `sample_table$20190123`."
}
}
},
"description": "Optional. Output only. Table references of all replicas currently active on the table."
},
"requirePartitionFilter": {
"type": "boolean",
"description": "Optional. If set to true, queries over this table require a partition filter that can be used for partition elimination to be specified."
},
"resourceTags": {
"type": "object",
"description": "[Optional] The tags associated with this table. Tag keys are globally unique. See additional information on [tags](https://cloud.google.com/iam/docs/tags-access-control#definitions). An object containing a list of \"key\": value pairs. The key is the namespaced friendly name of the tag key, e.g. \"12345/environment\" where 12345 is parent id. The value is the friendly short name of the tag value, e.g. \"production\"."
},
"schema": {
"type": "object",
"description": "Schema of a table",
"properties": {
"fields": {
"type": "array",
"items": {
"type": "object"
},
"description": "Describes the fields in a table."
}
}
},
"selfLink": {
"type": "string",
"description": "Output only. A URL that can be used to access this resource again."
},
"snapshotDefinition": {
"type": "object",
"description": "Information about base table and snapshot time of the snapshot.",
"properties": {
"baseTableReference": {
"type": "object",
"description": "Base Table Reference"
},
"snapshotTime": {
"type": "string",
"description": "Required. The time at which the base table was snapshot. This value is reported in the JSON response using RFC3339 format."
}
}
},
"streamingBuffer": {
"type": "object",
"description": "Streaming Buffer",
"properties": {
"estimatedBytes": {
"type": "string",
"description": "Output only. A lower-bound estimate of the number of bytes currently in the streaming buffer."
},
"estimatedRows": {
"type": "string",
"description": "Output only. A lower-bound estimate of the number of rows currently in the streaming buffer."
},
"oldestEntryTime": {
"type": "string",
"description": "Output only. Contains the timestamp of the oldest entry in the streaming buffer, in milliseconds since the epoch, if the streaming buffer is available."
}
}
},
"tableConstraints": {
"type": "object",
"description": "The TableConstraints defines the primary key and foreign key.",
"properties": {
"foreignKeys": {
"type": "array",
"items": {
"type": "object"
},
"description": "Optional. Present only if the table has a foreign key. The foreign key is not enforced."
},
"primaryKey": {
"type": "object",
"description": "Represents the primary key constraint on a table's columns."
}
}
},
"tableReference": {
"type": "object",
"description": "Table Reference",
"properties": {
"datasetId": {
"type": "string",
"description": "Required. The ID of the dataset containing this table."
},
"projectId": {
"type": "string",
"description": "Required. The ID of the project containing this table."
},
"tableId": {
"type": "string",
"description": "Required. The ID of the table. The ID can contain Unicode characters in category L (letter), M (mark), N (number), Pc (connector, including underscore), Pd (dash), and Zs (space). For more information, see [General Category](https://wikipedia.org/wiki/Unicode_character_property#General_Category). The maximum length is 1,024 characters. Certain operations allow suffixing of the table ID with a partition decorator, such as `sample_table$20190123`."
}
}
},
"tableReplicationInfo": {
"type": "object",
"description": "Replication info of a table created using `AS REPLICA` DDL like: `CREATE MATERIALIZED VIEW mv1 AS REPLICA OF src_mv`",
"properties": {
"replicatedSourceLastRefreshTime": {
"type": "string",
"description": "Optional. Output only. If source is a materialized view, this field signifies the last refresh time of the source."
},
"replicationError": {
"type": "object",
"description": "Error details."
},
"replicationIntervalMs": {
"type": "string",
"description": "Required. Specifies the interval at which the source table is polled for updates."
},
"replicationStatus": {
"type": "string",
"description": "Optional. Output only. Replication status of configured replication.",
"enum": [
"REPLICATION_STATUS_UNSPECIFIED",
"ACTIVE",
"SOURCE_DELETED",
"PERMISSION_DENIED",
"UNSUPPORTED_CONFIGURATION"
]
},
"sourceTable": {
"type": "object",
"description": "Source Table"
}
}
},
"timePartitioning": {
"type": "object",
"description": "Time Partitioning",
"properties": {
"expirationMs": {
"type": "string",
"description": "Optional. Number of milliseconds for which to keep the storage for a partition. A wrapper is used here because 0 is an invalid value."
},
"field": {
"type": "string",
"description": "Optional. If not set, the table is partitioned by pseudo column '_PARTITIONTIME'; if set, the table is partitioned by this field. The field must be a top-level TIMESTAMP or DATE field. Its mode must be NULLABLE or REQUIRED. A wrapper is used here because an empty string is an invalid value."
},
"requirePartitionFilter": {
"type": "boolean",
"description": "If set to true, queries over this table require a partition filter that can be used for partition elimination to be specified. This field is deprecated; please set the field with the same name on the table itself instead. This field needs a wrapper because we want to output the default value, false, if the user explicitly set it."
},
"type": {
"type": "string",
"description": "Required. The supported types are DAY, HOUR, MONTH, and YEAR, which will generate one partition per day, hour, month, and year, respectively."
}
}
},
"type": {
"type": "string",
"description": "Output only. Describes the table type. The following values are supported: * `TABLE`: A normal BigQuery table. * `VIEW`: A virtual table defined by a SQL query. * `EXTERNAL`: A table that references data stored in an external storage system, such as Google Cloud Storage. * `MATERIALIZED_VIEW`: A precomputed view defined by a SQL query. * `SNAPSHOT`: An immutable BigQuery table that preserves the contents of a base table at a particular time. See additional information on [table snapshots](/bigquery/docs/table-snapshots-intro). The default value is `TABLE`."
},
"view": {
"type": "object",
"description": "Describes the definition of a logical view.",
"properties": {
"privacyPolicy": {
"type": "object",
"description": "Represents privacy policy that contains the privacy requirements specified by the data owner. Currently, this is only supported on views."
},
"query": {
"type": "string",
"description": "Required. A query that BigQuery executes when the view is referenced."
},
"useExplicitColumnNames": {
"type": "boolean",
"description": "True if the column names are explicitly specified. For example by using the 'CREATE VIEW v(c1, c2) AS ...' syntax. Can only be set for GoogleSQL views."
},
"useLegacySql": {
"type": "boolean",
"description": "Specifies whether to use BigQuery's legacy SQL for this view. The default value is true. If set to false, the view will use BigQuery's GoogleSQL: https://cloud.google.com/bigquery/sql-reference/ Queries and views that reference this view must use the same flag value. A wrapper is used here because the default value is True."
},
"userDefinedFunctionResources": {
"type": "array",
"items": {
"type": "object"
},
"description": "Describes user-defined function resources used in the query."
}
}
}
},
"required": [
"PCID",
"projectId",
"datasetId"
]
}
```
***
## google-bigquery\_tables\_list
Lists all tables in the specified dataset. Requires the READER dataset role.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ------------ | ------- | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the tables to list |
| `datasetId` | string | Yes | — | Required. Dataset ID of the tables to list |
| `maxResults` | integer | No | — | The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection. |
| `pageToken` | string | No | — | Page token, returned by a previous call, to request the next page of results |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the tables to list"
},
"datasetId": {
"type": "string",
"description": "Required. Dataset ID of the tables to list"
},
"maxResults": {
"type": "integer",
"description": "The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection."
},
"pageToken": {
"type": "string",
"description": "Page token, returned by a previous call, to request the next page of results"
}
},
"required": [
"PCID",
"projectId",
"datasetId"
]
}
```
***
## google-bigquery\_tables\_patch
Updates information in an existing table. The update method replaces the entire table resource, whereas the patch method only replaces fields that are provided in the submitted table resource. This method supports RFC5789 patch semantics.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| ---------------------------- | --------- | -------- | ------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the table to update |
| `datasetId` | string | Yes | — | Required. Dataset ID of the table to update |
| `tableId` | string | Yes | — | Required. Table ID of the table to update |
| `autodetect_schema` | boolean | No | — | Optional. When true will autodetect schema, else will keep original schema |
| `biglakeConfiguration` | object | No | — | Configuration for BigLake managed tables. |
| `cloneDefinition` | object | No | — | Information about base table and clone time of a table clone. |
| `clustering` | object | No | — | Configures table clustering. |
| `creationTime` | string | No | — | Output only. The time when this table was created, in milliseconds since the epoch. |
| `defaultCollation` | string | No | — | Optional. Defines the default collation specification of new STRING fields in the table. During table creation or update, if a STRING field is added to this table without explicit collation specified, then the table inherits the table default collation. A change to this field affects only fields added afterwards, and does not alter the existing fields. The following values are supported: \* 'und:ci': undetermined locale, case insensitive. \* '': empty string. Default to case-sensitive behavior. |
| `defaultRoundingMode` | string | No | — | Optional. Defines the default rounding mode specification of new decimal fields (NUMERIC OR BIGNUMERIC) in the table. During table creation or update, if a decimal field is added to this table without an explicit rounding mode specified, then the field inherits the table default rounding mode. Changing this field doesn't affect existing fields. |
| `description` | string | No | — | Optional. A user-friendly description of this table. |
| `encryptionConfiguration` | object | No | — | Encryption Configuration |
| `etag` | string | No | — | Output only. A hash of this resource. |
| `expirationTime` | string | No | — | Optional. The time when this table expires, in milliseconds since the epoch. If not present, the table will persist indefinitely. Expired tables will be deleted and their storage reclaimed. The defaultTableExpirationMs property of the encapsulating dataset can be used to set a default expirationTime on newly created tables. |
| `externalDataConfiguration` | object | No | — | External Data Configuration |
| `friendlyName` | string | No | — | Optional. A descriptive name for this table. |
| `id` | string | No | — | Output only. An opaque ID uniquely identifying the table. |
| `kind` | string | No | — | The type of resource ID. |
| `labels` | object | No | — | The labels associated with this table. You can use these to organize and group your tables. Label keys and values can be no longer than 63 characters, can only contain lowercase letters, numeric characters, underscores and dashes. International characters are allowed. Label values are optional. Label keys must start with a letter and each label in the list must have a different key. |
| `lastModifiedTime` | string | No | — | Output only. The time when this table was last modified, in milliseconds since the epoch. |
| `location` | string | No | — | Output only. The geographic location where the table resides. This value is inherited from the dataset. |
| `materializedView` | object | No | — | Definition and configuration of a materialized view. |
| `materializedViewStatus` | object | No | — | Status of a materialized view. The last refresh timestamp status is omitted here, but is present in the MaterializedViewDefinition message. |
| `maxStaleness` | string | No | — | Optional. The maximum staleness of data that could be returned when the table (or stale MV) is queried. Staleness encoded as a string encoding of sql IntervalValue type. |
| `model` | object | No | — | The model value |
| `numActiveLogicalBytes` | string | No | — | Output only. Number of logical bytes that are less than 90 days old. |
| `numActivePhysicalBytes` | string | No | — | Output only. Number of physical bytes less than 90 days old. This data is not kept in real time, and might be delayed by a few seconds to a few minutes. |
| `numBytes` | string | No | — | Output only. The size of this table in logical bytes, excluding any data in the streaming buffer. |
| `numLongTermBytes` | string | No | — | Output only. The number of logical bytes in the table that are considered "long-term storage". |
| `numLongTermLogicalBytes` | string | No | — | Output only. Number of logical bytes that are more than 90 days old. |
| `numLongTermPhysicalBytes` | string | No | — | Output only. Number of physical bytes more than 90 days old. This data is not kept in real time, and might be delayed by a few seconds to a few minutes. |
| `numPartitions` | string | No | — | Output only. The number of partitions present in the table or materialized view. This data is not kept in real time, and might be delayed by a few seconds to a few minutes. |
| `numPhysicalBytes` | string | No | — | Output only. The physical size of this table in bytes. This includes storage used for time travel. |
| `numRows` | string | No | — | Output only. The number of rows of data in this table, excluding any data in the streaming buffer. |
| `numTimeTravelPhysicalBytes` | string | No | — | Output only. Number of physical bytes used by time travel storage (deleted or changed data). This data is not kept in real time, and might be delayed by a few seconds to a few minutes. |
| `numTotalLogicalBytes` | string | No | — | Output only. Total number of logical bytes in the table or materialized view. |
| `numTotalPhysicalBytes` | string | No | — | Output only. The physical size of this table in bytes. This also includes storage used for time travel. This data is not kept in real time, and might be delayed by a few seconds to a few minutes. |
| `rangePartitioning` | object | No | — | Range Partitioning |
| `replicas` | object\[] | No | — | Optional. Output only. Table references of all replicas currently active on the table. |
| `requirePartitionFilter` | boolean | No | — | Optional. If set to true, queries over this table require a partition filter that can be used for partition elimination to be specified. |
| `resourceTags` | object | No | — | \[Optional] The tags associated with this table. Tag keys are globally unique. See additional information on [tags](https://cloud.google.com/iam/docs/tags-access-control#definitions). An object containing a list of "key": value pairs. The key is the namespaced friendly name of the tag key, e.g. "12345/environment" where 12345 is parent id. The value is the friendly short name of the tag value, e.g. "production". |
| `schema` | object | No | — | Schema of a table |
| `selfLink` | string | No | — | Output only. A URL that can be used to access this resource again. |
| `snapshotDefinition` | object | No | — | Information about base table and snapshot time of the snapshot. |
| `streamingBuffer` | object | No | — | Streaming Buffer |
| `tableConstraints` | object | No | — | The TableConstraints defines the primary key and foreign key. |
| `tableReference` | object | No | — | Table Reference |
| `tableReplicationInfo` | object | No | — | Replication info of a table created using `AS REPLICA` DDL like: `CREATE MATERIALIZED VIEW mv1 AS REPLICA OF src_mv` |
| `timePartitioning` | object | No | — | Time Partitioning |
| `type` | string | No | — | Output only. Describes the table type. The following values are supported: \* `TABLE`: A normal BigQuery table. \* `VIEW`: A virtual table defined by a SQL query. \* `EXTERNAL`: A table that references data stored in an external storage system, such as Google Cloud Storage. \* `MATERIALIZED_VIEW`: A precomputed view defined by a SQL query. \* `SNAPSHOT`: An immutable BigQuery table that preserves the contents of a base table at a particular time. See additional information on [table snapshots](/bigquery/docs/table-snapshots-intro). The default value is `TABLE`. |
| `view` | object | No | — | Describes the definition of a logical view. |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the table to update"
},
"datasetId": {
"type": "string",
"description": "Required. Dataset ID of the table to update"
},
"tableId": {
"type": "string",
"description": "Required. Table ID of the table to update"
},
"autodetect_schema": {
"type": "boolean",
"description": "Optional. When true will autodetect schema, else will keep original schema"
},
"biglakeConfiguration": {
"type": "object",
"description": "Configuration for BigLake managed tables.",
"properties": {
"connectionId": {
"type": "string",
"description": "Required. The connection specifying the credentials to be used to read and write to external storage, such as Cloud Storage. The connection_id can have the form \"..\" or \"projects//locations//connections/\"."
},
"fileFormat": {
"type": "string",
"description": "Required. The file format the table data is stored in.",
"enum": [
"FILE_FORMAT_UNSPECIFIED",
"PARQUET"
]
},
"storageUri": {
"type": "string",
"description": "Required. The fully qualified location prefix of the external folder where table data is stored. The '*' wildcard character is not allowed. The URI should be in the format \"gs://bucket/path_to_table/\""
},
"tableFormat": {
"type": "string",
"description": "Required. The table format the metadata only snapshots are stored in.",
"enum": [
"TABLE_FORMAT_UNSPECIFIED",
"ICEBERG"
]
}
}
},
"cloneDefinition": {
"type": "object",
"description": "Information about base table and clone time of a table clone.",
"properties": {
"baseTableReference": {
"type": "object",
"description": "Base Table Reference"
},
"cloneTime": {
"type": "string",
"description": "Required. The time at which the base table was cloned. This value is reported in the JSON response using RFC3339 format."
}
}
},
"clustering": {
"type": "object",
"description": "Configures table clustering.",
"properties": {
"fields": {
"type": "array",
"items": {
"type": "string"
},
"description": "One or more fields on which data should be clustered. Only top-level, non-repeated, simple-type fields are supported. The ordering of the clustering fields should be prioritized from most to least important for filtering purposes. Additional information on limitations can be found here: https://cloud.google.com/bigquery/docs/creating-clustered-tables#limitations"
}
}
},
"creationTime": {
"type": "string",
"description": "Output only. The time when this table was created, in milliseconds since the epoch."
},
"defaultCollation": {
"type": "string",
"description": "Optional. Defines the default collation specification of new STRING fields in the table. During table creation or update, if a STRING field is added to this table without explicit collation specified, then the table inherits the table default collation. A change to this field affects only fields added afterwards, and does not alter the existing fields. The following values are supported: * 'und:ci': undetermined locale, case insensitive. * '': empty string. Default to case-sensitive behavior."
},
"defaultRoundingMode": {
"type": "string",
"description": "Optional. Defines the default rounding mode specification of new decimal fields (NUMERIC OR BIGNUMERIC) in the table. During table creation or update, if a decimal field is added to this table without an explicit rounding mode specified, then the field inherits the table default rounding mode. Changing this field doesn't affect existing fields.",
"enum": [
"ROUNDING_MODE_UNSPECIFIED",
"ROUND_HALF_AWAY_FROM_ZERO",
"ROUND_HALF_EVEN"
]
},
"description": {
"type": "string",
"description": "Optional. A user-friendly description of this table."
},
"encryptionConfiguration": {
"type": "object",
"description": "Encryption Configuration",
"properties": {
"kmsKeyName": {
"type": "string",
"description": "Optional. Describes the Cloud KMS encryption key that will be used to protect destination BigQuery table. The BigQuery Service Account associated with your project requires access to this encryption key."
}
}
},
"etag": {
"type": "string",
"description": "Output only. A hash of this resource."
},
"expirationTime": {
"type": "string",
"description": "Optional. The time when this table expires, in milliseconds since the epoch. If not present, the table will persist indefinitely. Expired tables will be deleted and their storage reclaimed. The defaultTableExpirationMs property of the encapsulating dataset can be used to set a default expirationTime on newly created tables."
},
"externalDataConfiguration": {
"type": "object",
"description": "External Data Configuration",
"properties": {
"autodetect": {
"type": "boolean",
"description": "Try to detect schema and format options automatically. Any option specified explicitly will be honored."
},
"avroOptions": {
"type": "object",
"description": "Options for external data sources."
},
"bigtableOptions": {
"type": "object",
"description": "Options specific to Google Cloud Bigtable data sources."
},
"compression": {
"type": "string",
"description": "Optional. The compression type of the data source. Possible values include GZIP and NONE. The default value is NONE. This setting is ignored for Google Cloud Bigtable, Google Cloud Datastore backups, Avro, ORC and Parquet formats. An empty string is an invalid value."
},
"connectionId": {
"type": "string",
"description": "Optional. The connection specifying the credentials to be used to read external storage, such as Azure Blob, Cloud Storage, or S3. The connection_id can have the form \"..\" or \"projects//locations//connections/\"."
},
"csvOptions": {
"type": "object",
"description": "Information related to a CSV data source."
},
"decimalTargetTypes": {
"type": "array",
"items": {
"type": "string",
"enum": [
"DECIMAL_TARGET_TYPE_UNSPECIFIED",
"NUMERIC",
"BIGNUMERIC",
"STRING"
]
},
"description": "Defines the list of possible SQL data types to which the source decimal values are converted. This list and the precision and the scale parameters of the decimal field determine the target type. In the order of NUMERIC, BIGNUMERIC, and STRING, a type is picked if it is in the specified list and if it supports the precision and the scale. STRING supports all precision and scale values. If none of the listed types supports the precision and the scale, the type supporting the widest range in the specified list is picked, and if a value exceeds the supported range when reading the data, an error will be thrown. Example: Suppose the value of this field is [\"NUMERIC\", \"BIGNUMERIC\"]. If (precision,scale) is: * (38,9) -> NUMERIC; * (39,9) -> BIGNUMERIC (NUMERIC cannot hold 30 integer digits); * (38,10) -> BIGNUMERIC (NUMERIC cannot hold 10 fractional digits); * (76,38) -> BIGNUMERIC; * (77,38) -> BIGNUMERIC (error if value exeeds supported range). This field cannot contain duplicate types. The order of the types in this field is ignored. For example, [\"BIGNUMERIC\", \"NUMERIC\"] is the same as [\"NUMERIC\", \"BIGNUMERIC\"] and NUMERIC always takes precedence over BIGNUMERIC. Defaults to [\"NUMERIC\", \"STRING\"] for ORC and [\"NUMERIC\"] for the other file formats."
},
"fileSetSpecType": {
"type": "string",
"description": "Optional. Specifies how source URIs are interpreted for constructing the file set to load. By default source URIs are expanded against the underlying storage. Other options include specifying manifest files. Only applicable to object storage systems.",
"enum": [
"FILE_SET_SPEC_TYPE_FILE_SYSTEM_MATCH",
"FILE_SET_SPEC_TYPE_NEW_LINE_DELIMITED_MANIFEST"
]
},
"googleSheetsOptions": {
"type": "object",
"description": "Options specific to Google Sheets data sources."
},
"hivePartitioningOptions": {
"type": "object",
"description": "Options for configuring hive partitioning detect."
},
"ignoreUnknownValues": {
"type": "boolean",
"description": "Optional. Indicates if BigQuery should allow extra values that are not represented in the table schema. If true, the extra values are ignored. If false, records with extra columns are treated as bad records, and if there are too many bad records, an invalid error is returned in the job result. The default value is false. The sourceFormat property determines what BigQuery treats as an extra value: CSV: Trailing columns JSON: Named values that don't match any column names Google Cloud Bigtable: This setting is ignored. Google Cloud Datastore backups: This setting is ignored. Avro: This setting is ignored. ORC: This setting is ignored. Parquet: This setting is ignored."
},
"jsonExtension": {
"type": "string",
"description": "Optional. Load option to be used together with source_format newline-delimited JSON to indicate that a variant of JSON is being loaded. To load newline-delimited GeoJSON, specify GEOJSON (and source_format must be set to NEWLINE_DELIMITED_JSON).",
"enum": [
"JSON_EXTENSION_UNSPECIFIED",
"GEOJSON"
]
},
"jsonOptions": {
"type": "object",
"description": "Json Options for load and make external tables."
},
"maxBadRecords": {
"type": "integer",
"description": "Optional. The maximum number of bad records that BigQuery can ignore when reading data. If the number of bad records exceeds this value, an invalid error is returned in the job result. The default value is 0, which requires that all records are valid. This setting is ignored for Google Cloud Bigtable, Google Cloud Datastore backups, Avro, ORC and Parquet formats."
},
"metadataCacheMode": {
"type": "string",
"description": "Optional. Metadata Cache Mode for the table. Set this to enable caching of metadata from external data source.",
"enum": [
"METADATA_CACHE_MODE_UNSPECIFIED",
"AUTOMATIC",
"MANUAL"
]
},
"objectMetadata": {
"type": "string",
"description": "Optional. ObjectMetadata is used to create Object Tables. Object Tables contain a listing of objects (with their metadata) found at the source_uris. If ObjectMetadata is set, source_format should be omitted. Currently SIMPLE is the only supported Object Metadata type.",
"enum": [
"OBJECT_METADATA_UNSPECIFIED",
"DIRECTORY",
"SIMPLE"
]
},
"parquetOptions": {
"type": "object",
"description": "Parquet Options for load and make external tables."
},
"referenceFileSchemaUri": {
"type": "string",
"description": "Optional. When creating an external table, the user can provide a reference file with the table schema. This is enabled for the following formats: AVRO, PARQUET, ORC."
},
"schema": {
"type": "object",
"description": "Schema of a table"
},
"sourceFormat": {
"type": "string",
"description": "[Required] The data format. For CSV files, specify \"CSV\". For Google sheets, specify \"GOOGLE_SHEETS\". For newline-delimited JSON, specify \"NEWLINE_DELIMITED_JSON\". For Avro files, specify \"AVRO\". For Google Cloud Datastore backups, specify \"DATASTORE_BACKUP\". For Apache Iceberg tables, specify \"ICEBERG\". For ORC files, specify \"ORC\". For Parquet files, specify \"PARQUET\". [Beta] For Google Cloud Bigtable, specify \"BIGTABLE\"."
},
"sourceUris": {
"type": "array",
"items": {
"type": "string"
},
"description": "[Required] The fully-qualified URIs that point to your data in Google Cloud. For Google Cloud Storage URIs: Each URI can contain one '*' wildcard character and it must come after the 'bucket' name. Size limits related to load jobs apply to external data sources. For Google Cloud Bigtable URIs: Exactly one URI can be specified and it has be a fully specified and valid HTTPS URL for a Google Cloud Bigtable table. For Google Cloud Datastore backups, exactly one URI can be specified. Also, the '*' wildcard character is not allowed."
}
}
},
"friendlyName": {
"type": "string",
"description": "Optional. A descriptive name for this table."
},
"id": {
"type": "string",
"description": "Output only. An opaque ID uniquely identifying the table."
},
"kind": {
"type": "string",
"description": "The type of resource ID."
},
"labels": {
"type": "object",
"description": "The labels associated with this table. You can use these to organize and group your tables. Label keys and values can be no longer than 63 characters, can only contain lowercase letters, numeric characters, underscores and dashes. International characters are allowed. Label values are optional. Label keys must start with a letter and each label in the list must have a different key."
},
"lastModifiedTime": {
"type": "string",
"description": "Output only. The time when this table was last modified, in milliseconds since the epoch."
},
"location": {
"type": "string",
"description": "Output only. The geographic location where the table resides. This value is inherited from the dataset."
},
"materializedView": {
"type": "object",
"description": "Definition and configuration of a materialized view.",
"properties": {
"allowNonIncrementalDefinition": {
"type": "boolean",
"description": "Optional. This option declares authors intention to construct a materialized view that will not be refreshed incrementally."
},
"enableRefresh": {
"type": "boolean",
"description": "Optional. Enable automatic refresh of the materialized view when the base table is updated. The default value is \"true\"."
},
"lastRefreshTime": {
"type": "string",
"description": "Output only. The time when this materialized view was last refreshed, in milliseconds since the epoch."
},
"maxStaleness": {
"type": "string",
"description": "[Optional] Max staleness of data that could be returned when materizlized view is queried (formatted as Google SQL Interval type)."
},
"query": {
"type": "string",
"description": "Required. A query whose results are persisted."
},
"refreshIntervalMs": {
"type": "string",
"description": "Optional. The maximum frequency at which this materialized view will be refreshed. The default value is \"1800000\" (30 minutes)."
}
}
},
"materializedViewStatus": {
"type": "object",
"description": "Status of a materialized view. The last refresh timestamp status is omitted here, but is present in the MaterializedViewDefinition message.",
"properties": {
"lastRefreshStatus": {
"type": "object",
"description": "Error details."
},
"refreshWatermark": {
"type": "string",
"description": "Output only. Refresh watermark of materialized view. The base tables' data were collected into the materialized view cache until this time."
}
}
},
"maxStaleness": {
"type": "string",
"description": "Optional. The maximum staleness of data that could be returned when the table (or stale MV) is queried. Staleness encoded as a string encoding of sql IntervalValue type."
},
"model": {
"type": "object",
"description": "The model value",
"properties": {
"modelOptions": {
"type": "object",
"description": "Deprecated."
},
"trainingRuns": {
"type": "array",
"items": {
"type": "object"
},
"description": "Deprecated."
}
}
},
"numActiveLogicalBytes": {
"type": "string",
"description": "Output only. Number of logical bytes that are less than 90 days old."
},
"numActivePhysicalBytes": {
"type": "string",
"description": "Output only. Number of physical bytes less than 90 days old. This data is not kept in real time, and might be delayed by a few seconds to a few minutes."
},
"numBytes": {
"type": "string",
"description": "Output only. The size of this table in logical bytes, excluding any data in the streaming buffer."
},
"numLongTermBytes": {
"type": "string",
"description": "Output only. The number of logical bytes in the table that are considered \"long-term storage\"."
},
"numLongTermLogicalBytes": {
"type": "string",
"description": "Output only. Number of logical bytes that are more than 90 days old."
},
"numLongTermPhysicalBytes": {
"type": "string",
"description": "Output only. Number of physical bytes more than 90 days old. This data is not kept in real time, and might be delayed by a few seconds to a few minutes."
},
"numPartitions": {
"type": "string",
"description": "Output only. The number of partitions present in the table or materialized view. This data is not kept in real time, and might be delayed by a few seconds to a few minutes."
},
"numPhysicalBytes": {
"type": "string",
"description": "Output only. The physical size of this table in bytes. This includes storage used for time travel."
},
"numRows": {
"type": "string",
"description": "Output only. The number of rows of data in this table, excluding any data in the streaming buffer."
},
"numTimeTravelPhysicalBytes": {
"type": "string",
"description": "Output only. Number of physical bytes used by time travel storage (deleted or changed data). This data is not kept in real time, and might be delayed by a few seconds to a few minutes."
},
"numTotalLogicalBytes": {
"type": "string",
"description": "Output only. Total number of logical bytes in the table or materialized view."
},
"numTotalPhysicalBytes": {
"type": "string",
"description": "Output only. The physical size of this table in bytes. This also includes storage used for time travel. This data is not kept in real time, and might be delayed by a few seconds to a few minutes."
},
"rangePartitioning": {
"type": "object",
"description": "Range Partitioning",
"properties": {
"field": {
"type": "string",
"description": "Required. [Experimental] The table is partitioned by this field. The field must be a top-level NULLABLE/REQUIRED field. The only supported type is INTEGER/INT64."
},
"range": {
"type": "object",
"description": "[Experimental] Defines the ranges for range partitioning."
}
}
},
"replicas": {
"type": "array",
"items": {
"type": "object",
"properties": {
"datasetId": {
"type": "string",
"description": "Required. The ID of the dataset containing this table."
},
"projectId": {
"type": "string",
"description": "Required. The ID of the project containing this table."
},
"tableId": {
"type": "string",
"description": "Required. The ID of the table. The ID can contain Unicode characters in category L (letter), M (mark), N (number), Pc (connector, including underscore), Pd (dash), and Zs (space). For more information, see [General Category](https://wikipedia.org/wiki/Unicode_character_property#General_Category). The maximum length is 1,024 characters. Certain operations allow suffixing of the table ID with a partition decorator, such as `sample_table$20190123`."
}
}
},
"description": "Optional. Output only. Table references of all replicas currently active on the table."
},
"requirePartitionFilter": {
"type": "boolean",
"description": "Optional. If set to true, queries over this table require a partition filter that can be used for partition elimination to be specified."
},
"resourceTags": {
"type": "object",
"description": "[Optional] The tags associated with this table. Tag keys are globally unique. See additional information on [tags](https://cloud.google.com/iam/docs/tags-access-control#definitions). An object containing a list of \"key\": value pairs. The key is the namespaced friendly name of the tag key, e.g. \"12345/environment\" where 12345 is parent id. The value is the friendly short name of the tag value, e.g. \"production\"."
},
"schema": {
"type": "object",
"description": "Schema of a table",
"properties": {
"fields": {
"type": "array",
"items": {
"type": "object"
},
"description": "Describes the fields in a table."
}
}
},
"selfLink": {
"type": "string",
"description": "Output only. A URL that can be used to access this resource again."
},
"snapshotDefinition": {
"type": "object",
"description": "Information about base table and snapshot time of the snapshot.",
"properties": {
"baseTableReference": {
"type": "object",
"description": "Base Table Reference"
},
"snapshotTime": {
"type": "string",
"description": "Required. The time at which the base table was snapshot. This value is reported in the JSON response using RFC3339 format."
}
}
},
"streamingBuffer": {
"type": "object",
"description": "Streaming Buffer",
"properties": {
"estimatedBytes": {
"type": "string",
"description": "Output only. A lower-bound estimate of the number of bytes currently in the streaming buffer."
},
"estimatedRows": {
"type": "string",
"description": "Output only. A lower-bound estimate of the number of rows currently in the streaming buffer."
},
"oldestEntryTime": {
"type": "string",
"description": "Output only. Contains the timestamp of the oldest entry in the streaming buffer, in milliseconds since the epoch, if the streaming buffer is available."
}
}
},
"tableConstraints": {
"type": "object",
"description": "The TableConstraints defines the primary key and foreign key.",
"properties": {
"foreignKeys": {
"type": "array",
"items": {
"type": "object"
},
"description": "Optional. Present only if the table has a foreign key. The foreign key is not enforced."
},
"primaryKey": {
"type": "object",
"description": "Represents the primary key constraint on a table's columns."
}
}
},
"tableReference": {
"type": "object",
"description": "Table Reference",
"properties": {
"datasetId": {
"type": "string",
"description": "Required. The ID of the dataset containing this table."
},
"projectId": {
"type": "string",
"description": "Required. The ID of the project containing this table."
},
"tableId": {
"type": "string",
"description": "Required. The ID of the table. The ID can contain Unicode characters in category L (letter), M (mark), N (number), Pc (connector, including underscore), Pd (dash), and Zs (space). For more information, see [General Category](https://wikipedia.org/wiki/Unicode_character_property#General_Category). The maximum length is 1,024 characters. Certain operations allow suffixing of the table ID with a partition decorator, such as `sample_table$20190123`."
}
}
},
"tableReplicationInfo": {
"type": "object",
"description": "Replication info of a table created using `AS REPLICA` DDL like: `CREATE MATERIALIZED VIEW mv1 AS REPLICA OF src_mv`",
"properties": {
"replicatedSourceLastRefreshTime": {
"type": "string",
"description": "Optional. Output only. If source is a materialized view, this field signifies the last refresh time of the source."
},
"replicationError": {
"type": "object",
"description": "Error details."
},
"replicationIntervalMs": {
"type": "string",
"description": "Required. Specifies the interval at which the source table is polled for updates."
},
"replicationStatus": {
"type": "string",
"description": "Optional. Output only. Replication status of configured replication.",
"enum": [
"REPLICATION_STATUS_UNSPECIFIED",
"ACTIVE",
"SOURCE_DELETED",
"PERMISSION_DENIED",
"UNSUPPORTED_CONFIGURATION"
]
},
"sourceTable": {
"type": "object",
"description": "Source Table"
}
}
},
"timePartitioning": {
"type": "object",
"description": "Time Partitioning",
"properties": {
"expirationMs": {
"type": "string",
"description": "Optional. Number of milliseconds for which to keep the storage for a partition. A wrapper is used here because 0 is an invalid value."
},
"field": {
"type": "string",
"description": "Optional. If not set, the table is partitioned by pseudo column '_PARTITIONTIME'; if set, the table is partitioned by this field. The field must be a top-level TIMESTAMP or DATE field. Its mode must be NULLABLE or REQUIRED. A wrapper is used here because an empty string is an invalid value."
},
"requirePartitionFilter": {
"type": "boolean",
"description": "If set to true, queries over this table require a partition filter that can be used for partition elimination to be specified. This field is deprecated; please set the field with the same name on the table itself instead. This field needs a wrapper because we want to output the default value, false, if the user explicitly set it."
},
"type": {
"type": "string",
"description": "Required. The supported types are DAY, HOUR, MONTH, and YEAR, which will generate one partition per day, hour, month, and year, respectively."
}
}
},
"type": {
"type": "string",
"description": "Output only. Describes the table type. The following values are supported: * `TABLE`: A normal BigQuery table. * `VIEW`: A virtual table defined by a SQL query. * `EXTERNAL`: A table that references data stored in an external storage system, such as Google Cloud Storage. * `MATERIALIZED_VIEW`: A precomputed view defined by a SQL query. * `SNAPSHOT`: An immutable BigQuery table that preserves the contents of a base table at a particular time. See additional information on [table snapshots](/bigquery/docs/table-snapshots-intro). The default value is `TABLE`."
},
"view": {
"type": "object",
"description": "Describes the definition of a logical view.",
"properties": {
"privacyPolicy": {
"type": "object",
"description": "Represents privacy policy that contains the privacy requirements specified by the data owner. Currently, this is only supported on views."
},
"query": {
"type": "string",
"description": "Required. A query that BigQuery executes when the view is referenced."
},
"useExplicitColumnNames": {
"type": "boolean",
"description": "True if the column names are explicitly specified. For example by using the 'CREATE VIEW v(c1, c2) AS ...' syntax. Can only be set for GoogleSQL views."
},
"useLegacySql": {
"type": "boolean",
"description": "Specifies whether to use BigQuery's legacy SQL for this view. The default value is true. If set to false, the view will use BigQuery's GoogleSQL: https://cloud.google.com/bigquery/sql-reference/ Queries and views that reference this view must use the same flag value. A wrapper is used here because the default value is True."
},
"userDefinedFunctionResources": {
"type": "array",
"items": {
"type": "object"
},
"description": "Describes user-defined function resources used in the query."
}
}
}
},
"required": [
"PCID",
"projectId",
"datasetId",
"tableId"
]
}
```
***
## google-bigquery\_undelete\_dataset
Undeletes a dataset which is within time travel window based on datasetId. If a time is specified, the dataset version deleted at that time is undeleted, else the last live version is undeleted.
**Parameters:**
| Parameter | Type | Required | Default | Description |
| -------------- | ------ | -------- | ------- | ---------------------------------------------------------------------------------------------------------------------------- |
| `projectId` | string | Yes | — | Required. Project ID of the dataset to be undeleted |
| `datasetId` | string | Yes | — | Required. Dataset ID of dataset being deleted |
| `deletionTime` | string | No | — | Optional. The exact time when the dataset was deleted. If not specified, it will undelete the most recently deleted version. |
```json theme={null}
{
"type": "object",
"properties": {
"PCID": {
"type": "string",
"description": "Pink Connect ID for the authenticated connection"
},
"projectId": {
"type": "string",
"description": "Required. Project ID of the dataset to be undeleted"
},
"datasetId": {
"type": "string",
"description": "Required. Dataset ID of dataset being deleted"
},
"deletionTime": {
"type": "string",
"description": "Optional. The exact time when the dataset was deleted. If not specified, it will undelete the most recently deleted version."
}
},
"required": [
"PCID",
"projectId",
"datasetId"
]
}
```