Google Cloud BigQuery Component
BigQuery is Google Cloud's fully managed, petabyte-scale, and cost-effective analytics data warehouse that lets you run analytics over vast amounts of data in near real time.
Component key: google-cloud-bigquery
Description
Google Cloud BigQuery is Google Cloud's fully managed, petabyte-scale, and cost-effective analytics data warehouse that enables running analytics over vast amounts of data in near real time.
API Documentation
This component was built using the Google Cloud BigQuery API.
The Google Cloud BigQuery component supports receiving notifications through Google Cloud Pub/Sub. This enables integrations to respond to events such as table changes, query completions, and data insertions.
Setting Up Pub/Sub Notifications for BigQuery
- In the Google Cloud console, navigate to the Pub/Sub page (Navigation Menu > More Products > Analytics > Pub/Sub).
- In the Topics page, click Create Topic.
- Enter a Topic ID (e.g.,
bigquery-notifications). - Leave the default values for the remaining options, and then click Create.
- Enter a Topic ID (e.g.,
- Create a subscription for the topic:
- Navigate to Pub/Sub > Subscriptions and click Create subscription.
- Enter a Subscription ID.
- For Select a Cloud Pub/Sub topic, select the topic created in the previous step.
- Under Delivery type, select Push and enter the webhook URL from the integration's trigger configuration.
- Click Create.
- Configure BigQuery to publish notifications to the Pub/Sub topic using the BigQuery API or the Google Cloud console.
When messages are published to the configured topic, they will be sent to the push endpoint configured in the integration trigger.
Connections
Private Key
key: googleServiceAccount| Input | Notes | Example |
|---|---|---|
| Client Email | The email address of the client to connect. | someone@example.com |
| Private Key | The private key of the client to connect. | |
| Scopes | Space delimited listing of scopes. https://developers.google.com/identity/protocols/oauth2/scopes#bigquery | https://www.googleapis.com/auth/bigquery https://www.googleapis.com/auth/cloud-platform https://www.googleapis.com/auth/cloud-platform.read-only |
OAuth 2.0
key: oauth2The Google BigQuery component authenticates requests through the Google Cloud Platform (GCP) OAuth 2.0 service. A GCP OAuth 2.0 app is required so the integration can authenticate and perform Google BigQuery tasks on the customer's behalf.
Prerequisites
- A Google Developer account is required - sign up at https://console.cloud.google.com/
Setup Steps
- Open up the Google BigQuery Console
- Click CREATE PROJECT to create a new GCP project, or select an existing project.
- The system will prompt to enable Google BigQuery for the project. Click ENABLE.
- On the sidebar, select Credentials.
- An OAuth 2.0 app includes a "Consent Screen" (the page that asks "Do you want to allow (Your Company) to access Google BigQuery on your behalf?"). Click CONFIGURE CONSENT SCREEN.
- The app will be externally available to end users, so choose a User Type of External.
- Fill out the OAuth consent screen with an app name (company or product name), support email, app logo, domain, etc.
- Domains can be ignored for now.
- On the next page, add these scopes to the app (these may not all be necessary, and should match the scopes in the connection definition):
https://www.googleapis.com/auth/bigqueryhttps://www.googleapis.com/auth/bigquery.insertdatahttps://www.googleapis.com/auth/cloud-platformhttps://www.googleapis.com/auth/cloud-platform.read-onlyhttps://www.googleapis.com/auth/devstorage.full_controlhttps://www.googleapis.com/auth/devstorage.read_onlyhttps://www.googleapis.com/auth/devstorage.read_write
- Enter some test users for testing purposes. The app will only work for those testing users until it is "verified" by Google. When ready for verification (verification includes the privacy policy statement, etc), click PUBLISH APP on the OAuth consent screen. This will allow end users to authorize the integration to access their Google BigQuery data.
- Once the "Consent Screen" is configured, open the Credentials page from the sidebar again.
- Click +CREATE CREDENTIALS and select OAuth client ID.
- Under Application type select Web application.
- Under Authorized redirect URIs enter the OAuth 2.0 callback URL:
https://oauth2.prismatic.io/callback - Click CREATE.
- Take note of the Client ID and Client Secret that are generated.
Make sure to publish the OAuth 2.0 app after testing it so users outside of the test users can authorize the integration to interact with Google BigQuery on their behalf.
Configure the Connection
Create a connection of type OAuth 2.0 and enter:
- Client ID: Enter the Client ID from the OAuth application
- Client Secret: Enter the Client Secret from the OAuth application
- Scopes: The default Google BigQuery scopes should be kept:
| https://www.googleapis.com/auth/bigquery | View and manage data in Google BigQuery and see the email address for the Google Account |
|---|---|
| https://www.googleapis.com/auth/bigquery.insertdata | Insert data into Google BigQuery |
| https://www.googleapis.com/auth/cloud-platform | See, edit, configure, and delete Google Cloud data and see the email address for the Google Account. |
| https://www.googleapis.com/auth/cloud-platform.read-only | View data across Google Cloud services and see the email address of the Google Account |
| https://www.googleapis.com/auth/devstorage.full_control | Manage data and permissions in Cloud Storage and see the email address for the Google Account |
| https://www.googleapis.com/auth/devstorage.read_only | View data in Google Cloud Storage |
| https://www.googleapis.com/auth/devstorage.read_write | Manage data in Cloud Storage and see the email address of the Google Account |
| Input | Notes | Example |
|---|---|---|
| Authorize URL | The Authorization URL for Google BigQuery. | https://accounts.google.com/o/oauth2/v2/auth?access_type=offline&prompt=consent |
| Client ID | The Google BigQuery app's Client Identifier. | |
| Client Secret | The Google BigQuery app's Client Secret. | |
| Scopes | Space delimited listing of scopes. https://developers.google.com/identity/protocols/oauth2/scopes#bigquery | https://www.googleapis.com/auth/bigquery https://www.googleapis.com/auth/bigquery.insertdata https://www.googleapis.com/auth/cloud-platform https://www.googleapis.com/auth/cloud-platform.read-only https://www.googleapis.com/auth/devstorage.full_control https://www.googleapis.com/auth/devstorage.read_only https://www.googleapis.com/auth/devstorage.read_write |
| Token URL | The Token URL for Google BigQuery. | https://oauth2.googleapis.com/token |
Triggers
PubSub Notification
Receive PubSub notifications from Google Cloud when events occur. | key: myTrigger
Data Sources
Select Project
A picklist of projects in your Google Cloud account. | key: projectsNames | type: picklist
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. |
{
"result": [
{
"label": "John Locke",
"key": "650"
},
{
"label": "John Doe",
"key": "47012"
}
]
}
Select Table
A picklist of tables in the specified dataset. | key: tablesNames | type: picklist
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Dataset ID | The unique identifier for the dataset. | my_dataset |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
{
"result": [
{
"label": "John Locke",
"key": "650"
},
{
"label": "John Doe",
"key": "47012"
}
]
}
Actions
Cancel Job
Requests that a job be cancelled. | key: cancelJob
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Job ID | The unique identifier for the job. | job_abc123xyz |
| Location | The geographic location where the dataset should reside. See https://cloud.google.com/bigquery/docs/locations for supported locations. | US |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
Create Dataset
Creates a new empty dataset. | key: createDataset
| Input | Notes | Example |
|---|---|---|
| Access | 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. | |
| Connection | The Google Cloud BigQuery connection to use. | |
| Creation Time | Output only. The time when this dataset was created, in milliseconds since the epoch. | 1609459200000 |
| Dataset Reference | A reference that identifies the dataset. | |
| Default Collation | 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. | und:ci |
| Default Encryption Configuration | The default encryption key for all tables in the dataset. Once this property is set, all newly-created partitioned tables in the dataset will have encryption key set to this value, unless table creation request (or query) overrides the key. | |
| Default Partition Expiration (ms) | 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. | 86400000 |
| Default Rounding Mode | 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. | |
| Default Table Expiration (ms) | 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. | 3600000 |
| Description | Optional. A descriptive name for the dataset. | This dataset contains sales data |
| ETag | Output only. A hash of the resource. | W/"abc123def456" |
| Friendly Name | Optional. A descriptive name for the dataset. | My Dataset |
| ID | 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. | my-project:my_dataset |
| Is Case Insensitive | 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. | false |
| Kind | Output only. The resource type. | bigquery#dataset |
| Labels | 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. | |
| Last Modified Time | Output only. The date when this dataset was last modified, in milliseconds since the epoch. | 1640995200000 |
| Location | The geographic location where the dataset should reside. See https://cloud.google.com/bigquery/docs/locations for supported locations. | US |
| Max Time Travel Hours | 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. | 168 |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
| Satisfies PZS | Output only. Reserved for future use. | false |
| Self Link | 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. | https://bigquery.googleapis.com/bigquery/v2/projects/my-project/datasets/my_dataset |
| Storage Billing Model | Optional. Updates storageBillingModel for the dataset. | |
| Tags | Output only. Tags for the Dataset. |
Create Job
Starts a new asynchronous job. | key: createJob
| Input | Notes | Example |
|---|---|---|
| Configuration | Required. Describes the job configuration. | |
| Connection | The Google Cloud BigQuery connection to use. | |
| ETag | Output only. A hash of the resource. | W/"abc123def456" |
| ID | 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. | my-project:my_dataset |
| Job Reference | Optional. Reference describing the unique-per-user name of the job. | |
| Kind | Output only. The resource type. | bigquery#dataset |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
| Self Link | 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. | https://bigquery.googleapis.com/bigquery/v2/projects/my-project/datasets/my_dataset |
| Statistics | Output only. Information about the job, including starting time and ending time of the job. | |
| Status | Output only. The status of this job. Examine this value when polling an asynchronous job to see if the job is complete. | |
| User Email | Output only. Email address of the user who ran the job. | user@example.com |
Create Routine
Creates a new routine in the dataset. | key: createRoutine
| Input | Notes | Example |
|---|---|---|
| Arguments | Input/output argument of a function or a stored procedure. | |
| Connection | The Google Cloud BigQuery connection to use. | |
| Creation Time | Output only. The time when this dataset was created, in milliseconds since the epoch. | 1609459200000 |
| Dataset ID | The unique identifier for the dataset. | my_dataset |
| Definition Body | 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 definitionBody 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 definitionBody is return '\n';\n Note that both \n are replaced with linebreaks. | concat(x, '\n', y) |
| Description | Optional. The description of the routine, if defined. | This dataset contains sales data |
| Determinism Level | Optional. The determinism level of the JavaScript UDF, if defined. One of DETERMINISM_LEVEL_UNSPECIFIED / DETERMINISTIC / NOT_DETERMINISTIC | DETERMINISTIC |
| ETag | Output only. A hash of the resource. | W/"abc123def456" |
| Imported Libraries | Optional. If language = 'JAVASCRIPT', this field stores the path of the imported JAVASCRIPT libraries. | ["gs://bucket/path/lib.js"] |
| Language | Optional. Defaults to 'SQL' if remoteFunctionOptions field is absent, not set otherwise. One of LANGUAGE_UNSPECIFIED / SQL / JAVASCRIPT / PYTHON / JAVA / SCALA | SQL |
| Last Modified Time | Output only. The date when this dataset was last modified, in milliseconds since the epoch. | 1640995200000 |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
| Remote Function Options | Optional. Remote function specific options. | |
| Return Table Type | Optional. Can be set only if routineType = 'TABLE_VALUED_FUNCTION'. If absent, the return table type is inferred from definitionBody at query time in each query that references this routine. If present, then the columns in the evaluated table result will be cast to match the column types specified in return table type, at query time. | |
| Return Type | Optional if language = 'SQL'; required otherwise. Cannot be set if routineType = 'TABLE_VALUED_FUNCTION'. If absent, the return type is inferred from definitionBody at query time in each query that references this routine. If present, then the evaluated result will be cast to the specified returned type at query time. | |
| Routine Reference | Reference describing the ID of this routine. | |
| Routine Type | The type of routine. One of ROUTINE_TYPE_UNSPECIFIED / SCALAR_FUNCTION / PROCEDURE / TABLE_VALUED_FUNCTION | SCALAR_FUNCTION |
| Spark Options | Optional. Spark specific options. |
Create Table
Creates a new, empty table in the dataset. | key: createTable
| Input | Notes | Example |
|---|---|---|
| Clustering | Clustering specification for the table. Must be specified with time-based partitioning, data in the table will be first partitioned and subsequently clustered. | |
| Connection | The Google Cloud BigQuery connection to use. | |
| Dataset ID | Dataset ID of the table to update. | my_dataset |
| Default Collation | 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. | und:ci |
| Default Rounding Mode | 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. | |
| Description | Optional. A descriptive name for the dataset. | This dataset contains sales data |
| Encryption Configuration | Custom encryption configuration (e.g., Cloud KMS keys). This shows the encryption configuration of the model data while stored in BigQuery storage. This field can be used with models.patch to update encryption key for an already encrypted model. | |
| Expiration Time | 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. | |
| External Data Configuration | Optional. Describes the data format, location, and other properties of a table stored outside of BigQuery. By defining these properties, the data source can then be queried as if it were a standard BigQuery table. | |
| Friendly Name | Optional. A descriptive name for the dataset. | My Dataset |
| Kind | Output only. The resource type. | bigquery#dataset |
| Labels | 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. | |
| Materialized View | Optional. The materialized view definition. | |
| Max Staleness | 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. | INTERVAL 1 DAY |
| Project ID | Project ID of the table to update. | my-project-12345 |
| Range Partitioning | If specified, configures range partitioning for this table. | |
| Require Partition Filter | When true, queries over this table require a partition filter that can be used for partition elimination to be specified. | false |
| Schema | Optional. Describes the schema of this table. | |
| Table Reference | Reference describing the ID of this routine. | |
| Time Partitioning | If specified, configures time-based partitioning for this table. | |
| View | Optional. The view definition. |
Delete Dataset
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. | key: deleteDataset
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Dataset ID | The unique identifier for the dataset. | my_dataset |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
Delete Job
Requests the deletion of the metadata of a job. | key: deleteJob
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Job ID | The unique identifier for the job. | job_abc123xyz |
| Location | The geographic location where the dataset should reside. See https://cloud.google.com/bigquery/docs/locations for supported locations. | US |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
Delete Model
Deletes the model specified by model ID from the dataset. | key: deleteModel
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Dataset ID | The unique identifier for the dataset. | my_dataset |
| Model ID | The unique identifier for the model. | my_model |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
Delete Routine
Deletes the routine specified by routine ID from the dataset. | key: deleteRoutine
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Dataset ID | The unique identifier for the dataset. | my_dataset |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
| Routine ID | The unique identifier for the routine. | my_routine |
Delete Table
Deletes the table specified by table ID from the dataset. | key: deleteTable
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Dataset ID | Dataset ID of the table to delete. | my_dataset |
| Project ID | Project ID of the table to delete. | my-project-12345 |
| Table ID | Table ID of the table to delete. | my_table |
Get Dataset
Returns the dataset specified by datasetID. | key: getDataset
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Dataset ID | The unique identifier for the dataset. | my_dataset |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
Get Job
Returns information about a specific job. | key: getJob
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Job ID | The unique identifier for the job. | job_abc123xyz |
| Location | The geographic location where the dataset should reside. See https://cloud.google.com/bigquery/docs/locations for supported locations. | US |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
Get Model
Gets the specified model resource by model ID. | key: getModel
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Dataset ID | The unique identifier for the dataset. | my_dataset |
| Model ID | The unique identifier for the model. | my_model |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
Get Policy
Gets the access control policy for a resource. | key: getPolicy
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Options | OPTIONAL: A GetPolicyOptions object for specifying options to tables.getIamPolicy. | |
| Resource | The resource for which the policy is being requested. See Resource names for the appropriate value for this field. | projects/my-project/datasets/my-dataset/tables/my-table |
Get Query Job Results
Receives the results of a query job. | key: getQueryJobResult
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Job ID | The unique identifier for the job. | job_abc123xyz |
| Location | The geographic location where the dataset should reside. See https://cloud.google.com/bigquery/docs/locations for supported locations. | US |
| Max Results | The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection. | 100 |
| Page Token | Page token returned by a previous call to request the next page of results. | CAESBAgCIAE |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
| Start Index | Zero-based index of the starting row. | 0 |
| Timeout (ms) | 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. | 10000 |
Get Routine
Gets the specified routine resource by routine ID. | key: getRoutine
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Dataset ID | The unique identifier for the dataset. | my_dataset |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
| Read Mask | If set, only the Routine fields in the field mask are returned in the response. If unset, all Routine fields are returned. This is a comma-separated list of fully qualified names of fields. Example: 'user.displayName,photo'. | user.displayName,photo |
| Routine ID | The unique identifier for the routine. | my_routine |
Get Service Account
Receives the service account for a project used for interactions with Google Cloud KMS | key: getServiceAccount
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
Get Table
Gets the specified table resource by table ID. | key: getTable
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Dataset ID | Dataset ID of the requested table. | my_dataset |
| Project ID | Project ID of the requested table. | my-project-12345 |
| Selected Fields | tabledata.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. | |
| Table ID | Table ID of the requested table. | my_table |
| View | Optional. Specifies the view that determines which table information is returned. By default, basic table information and storage statistics (STORAGE_STATS) are returned. One of TABLE_METADATA_VIEW_UNSPECIFIED / BASIC / STORAGE_STATS / FULL | FULL |
List Datasets
Lists all datasets in the specified project to which the user has been granted the READER dataset role. | key: listDatasets
| Input | Notes | Example |
|---|---|---|
| All | When true, lists all datasets, including hidden ones. | false |
| Connection | The Google Cloud BigQuery connection to use. | |
| Filter | An expression for filtering the results of the request by label. The syntax is 'labels.<name>[:<value>]'. Multiple filters can be ANDed together by connecting with a space. Example: 'labels.department:receiving labels.active'. See Filtering datasets using labels for details. | labels.department:receiving labels.active |
| Max Results | The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection. | 100 |
| Page Token | Page token returned by a previous call to request the next page of results. | CAESBAgCIAE |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
List Jobs
Lists all jobs that you started in the specified project. | key: listJobs
| Input | Notes | Example |
|---|---|---|
| All Users | When true, displays jobs owned by all users in the project. Default is false. | false |
| Connection | The Google Cloud BigQuery connection to use. | |
| Max Creation Time | Max value for job creation time, in milliseconds since the POSIX epoch. If set, only jobs created before or at this timestamp are returned. | 1640995200000 |
| Max Results | The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection. | 100 |
| Min Creation Time | Min value for job creation time, in milliseconds since the POSIX epoch. If set, only jobs created after or at this timestamp are returned. | 1609459200000 |
| Page Token | Page token returned by a previous call to request the next page of results. | CAESBAgCIAE |
| Parent Job ID | If set, shows only child jobs of the specified parent. Otherwise, shows all top-level jobs. | parent_job_abc123 |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
| Projection | Restrict information returned to a set of selected fields | |
| State Filter | Filter for job state. Valid values of this enum field are: DONE, PENDING, RUNNING. | ["DONE", "RUNNING"] |
List Models
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. | key: listModels
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Dataset ID | The unique identifier for the dataset. | my_dataset |
| Max Results | The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection. | 100 |
| Page Token | Page token returned by a previous call to request the next page of results. | CAESBAgCIAE |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
List Projects
Lists projects to which the user has been granted any project role. | key: listProjects
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Max Results | The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection. | 100 |
| Page Token | Page token returned by a previous call to request the next page of results. | CAESBAgCIAE |
List Routines
Lists all routines in the specified dataset. | key: listRoutines
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Dataset ID | The unique identifier for the dataset. | my_dataset |
| Filter | An expression for filtering the results of the request by label. The syntax is 'labels.<name>[:<value>]'. Multiple filters can be ANDed together by connecting with a space. Example: 'labels.department:receiving labels.active'. See Filtering datasets using labels for details. | labels.department:receiving labels.active |
| Max Results | The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection. | 100 |
| Page Token | Page token returned by a previous call to request the next page of results. | CAESBAgCIAE |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
| Read Mask | If set, only the Routine fields in the field mask are returned in the response. If unset, all Routine fields are returned. This is a comma-separated list of fully qualified names of fields. Example: 'user.displayName,photo'. | user.displayName,photo |
List Table Data (Deprecated)
Lists the content of a table in rows. Note: This action now uses jobs.query API as the tabledata.list API has been deprecated by Google. | key: listTableData
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Dataset ID | The unique identifier for the dataset. | my_dataset |
| Max Results | The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection. | 100 |
| Page Token | Page token returned by a previous call to request the next page of results. | CAESBAgCIAE |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
| Selected Fields | Subset of fields to return, supports select into sub fields. Example: selectedFields = 'a,e.d.f'; | |
| Start Index | Zero-based index of the starting row. | 0 |
| Table ID | The unique identifier for the table. | my_table |
List Tables
Lists all tables in the specified dataset. | key: listTables
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Dataset ID | Dataset ID of the tables to list. | my_dataset |
| Max Results | The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection. | 100 |
| Page Token | Page token returned by a previous call to request the next page of results. | CAESBAgCIAE |
| Project ID | Project ID of the tables to list. | my-project-12345 |
Patch Table
Patch information in an existing table. | key: patchTable
| Input | Notes | Example |
|---|---|---|
| Clustering | Clustering specification for the table. Must be specified with time-based partitioning, data in the table will be first partitioned and subsequently clustered. | |
| Connection | The Google Cloud BigQuery connection to use. | |
| Dataset ID | Dataset ID of the table to patch. | my_dataset |
| Default Collation | 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. | und:ci |
| Default Rounding Mode | 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. | |
| Description | Optional. A descriptive name for the dataset. | This dataset contains sales data |
| Encryption Configuration | Custom encryption configuration (e.g., Cloud KMS keys). This shows the encryption configuration of the model data while stored in BigQuery storage. This field can be used with models.patch to update encryption key for an already encrypted model. | |
| Expiration Time | 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. | |
| External Data Configuration | Optional. Describes the data format, location, and other properties of a table stored outside of BigQuery. By defining these properties, the data source can then be queried as if it were a standard BigQuery table. | |
| Friendly Name | Optional. A descriptive name for the dataset. | My Dataset |
| Kind | Output only. The resource type. | bigquery#dataset |
| Labels | 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. | |
| Materialized View | Optional. The materialized view definition. | |
| Max Staleness | 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. | INTERVAL 1 DAY |
| Project ID | Project ID of the table to patch. | my-project-12345 |
| Range Partitioning | If specified, configures range partitioning for this table. | |
| Require Partition Filter | When true, queries over this table require a partition filter that can be used for partition elimination to be specified. | false |
| Schema | Optional. Describes the schema of this table. | |
| Table ID | Table ID of the table to patch. | my_table |
| Table Reference | Reference describing the ID of this routine. | |
| Time Partitioning | If specified, configures time-based partitioning for this table. | |
| View | Optional. The view definition. |
Query Job
Runs a BigQuery SQL query synchronously and returns query results if the query completes within a specified timeout. | key: queryJob
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Connection Properties | Optional. Connection properties which can modify the query behavior. | |
| Create Session | When true, creates a new session using a randomly generated sessionId. If false, runs query with an existing sessionId 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. | false |
| Default Dataset | Optional. Specifies the default datasetId and projectId to assume for any unqualified table names in the query. If not set, all table names in the query string must be qualified in the format 'datasetId.tableId'. | |
| Dry Run | When 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. | false |
| Kind | Output only. The resource type. | bigquery#dataset |
| Labels | 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. | |
| Location | The geographic location where the dataset should reside. See https://cloud.google.com/bigquery/docs/locations for supported locations. | US |
| Maximum Bytes Billed | 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. | 1000000000 |
| Max Results | The maximum number of results to return in a single response page. Leverage the page tokens to iterate through the entire collection. | 100 |
| Parameter Mode | GoogleSQL only. Set to POSITIONAL to use positional (?) query parameters or to NAMED to use named (@myparam) query parameters in this query. | NAMED |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
| Query | Required. A query string to execute, using Google Standard SQL or legacy SQL syntax. Example: 'SELECT COUNT(f1) FROM myProjectId.myDatasetId.myTableId'. | SELECT COUNT(f1) FROM myProjectId.myDatasetId.myTableId |
| Query Parameters | Optional. An array of query parameters for a query. Reference to the Google docs for this input. https://cloud.google.com/bigquery/docs/reference/rest/v2/QueryParameter | |
| Request ID | Optional. A unique user provided identifier to ensure idempotent behavior for queries. Note that this is different from the jobId. It has the following properties: It is case-sensitive, limited to up to 36 ASCII characters. A UUID is recommended. Read only queries can ignore this token since they are nullipotent by definition. For the purposes of idempotency ensured by the requestId, a request is considered duplicate of another only if they have the same requestId 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, connectionProperties, queryParameters, useLegacySql are parameters that affect the result and are considered when determining whether a request is a duplicate, but properties like timeoutMs don't affect the result and are thus not considered. Dry run query requests are never considered duplicate of another request. 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. Its lifetime is limited to 15 minutes. In other words, if two requests are sent with the same requestId, but more than 15 minutes apart, idempotency is not guaranteed. | 550e8400-e29b-41d4-a716-446655440000 |
| Timeout (ms) | 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. | 10000 |
| Use Legacy SQL | Specifies whether to use BigQuery's legacy SQL dialect for this query. BigQuery's GoogleSQL | false |
| Use Query Cache | When true, looks 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. | true |
Raw Request
Send raw HTTP request to Google Cloud BigQuery | key: rawRequest
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Data | The HTTP body payload to send to the URL. | {"exampleKey": "Example Data"} |
| Debug Request | Enabling this flag will log out the current request. | false |
| File Data | File Data to be sent as a multipart form upload. | [{key: "example.txt", value: "My File Contents"}] |
| File Data File Names | File names to apply to the file data inputs. Keys must match the file data keys above. | |
| Form Data | The Form Data to be sent as a multipart form upload. | [{"key": "Example Key", "value": new Buffer("Hello World")}] |
| Header | A list of headers to send with the request. | User-Agent: curl/7.64.1 |
| Max Retry Count | The maximum number of retries to attempt. Specify 0 for no retries. | 0 |
| Method | The HTTP method to use. | |
| Query Parameter | A list of query parameters to send with the request. This is the portion at the end of the URL similar to ?key1=value1&key2=value2. | |
| Response Type | The type of data you expect in the response. You can request json, text, or binary data. | json |
| Retry On All Errors | If true, retries on all erroneous responses regardless of type. This is helpful when retrying after HTTP 429 or other 3xx or 4xx errors. Otherwise, only retries on HTTP 5xx and network errors. | false |
| Retry Delay (ms) | The delay in milliseconds between retries. This is used when 'Use Exponential Backoff' is disabled. | 0 |
| Timeout | The maximum time that a client will await a response to its request | 2000 |
| URL | Input the path only (/projects/{projectId}/jobs), The base URL is already included (https://bigquery.googleapis.com/bigquery/{version}). For example, to connect to https://bigquery.googleapis.com/bigquery/v2/projects/{projectId}/jobs, only /projects/{projectId}/jobs is entered in this field. | /projects/{projectId}/jobs |
| Use Exponential Backoff | Specifies whether to use a pre-defined exponential backoff strategy for retries. When enabled, 'Retry Delay (ms)' is ignored. | false |
| API Version | The API version to use. This is used to construct the base URL for the request. | v2 |
Set Policy
Sets the access control policy on the specified resource. | key: setPolicy
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Policy | The complete policy to be applied to the resource. The size of the policy is limited to a few 10s of KB. An empty policy is a valid policy but certain Google Cloud services (such as Projects) might reject them. | |
| Resource | The resource for which the policy is being requested. See Resource names for the appropriate value for this field. | projects/my-project/datasets/my-dataset/tables/my-table |
| Update Mask | OPTIONAL: A FieldMask specifying which fields of the policy to modify. Only the fields in the mask will be modified. If no mask is provided, the following default mask is used: paths: 'bindings, etag' This is a comma-separated list of fully qualified names of fields. Example: 'user.displayName,photo'. | user.displayName,photo |
Table Data Insert All
Streams data into BigQuery one record at a time without needing to run a load job. | key: tableDataInsertAll
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Dataset ID | The unique identifier for the dataset. | my_dataset |
| Ignore Unknown Values | When true, accepts rows that contain values that do not match the schema. The unknown values are ignored. Default is false, which treats unknown values as errors. | false |
| Kind | Output only. The resource type. | bigquery#dataset |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
| Rows | The complete policy to be applied to the resource. The size of the policy is limited to a few 10s of KB. An empty policy is a valid policy but certain Google Cloud services (such as Projects) might reject them. | |
| Skip Invalid Rows | When true, inserts all valid rows of a request even if invalid rows exist. Default is false, which causes the entire request to fail if any invalid rows exist. | false |
| Table ID | The unique identifier for the table. | my_table |
| Template Suffix | 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. | _20240101 |
Update Dataset
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. | key: updateDataset
| Input | Notes | Example |
|---|---|---|
| Access | 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. | |
| Connection | The Google Cloud BigQuery connection to use. | |
| Creation Time | Output only. The time when this dataset was created, in milliseconds since the epoch. | 1609459200000 |
| Dataset ID | The unique identifier for the dataset. | my_dataset |
| Dataset Reference | A reference that identifies the dataset. | |
| Default Collation | 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. | und:ci |
| Default Encryption Configuration | The default encryption key for all tables in the dataset. Once this property is set, all newly-created partitioned tables in the dataset will have encryption key set to this value, unless table creation request (or query) overrides the key. | |
| Default Partition Expiration (ms) | 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. | 86400000 |
| Default Rounding Mode | 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. | |
| Default Table Expiration (ms) | 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. | 3600000 |
| Description | Optional. A descriptive name for the dataset. | This dataset contains sales data |
| ETag | Output only. A hash of the resource. | W/"abc123def456" |
| Friendly Name | Optional. A descriptive name for the dataset. | My Dataset |
| ID | 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. | my-project:my_dataset |
| Is Case Insensitive | 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. | false |
| Kind | Output only. The resource type. | bigquery#dataset |
| Labels | 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. | |
| Last Modified Time | Output only. The date when this dataset was last modified, in milliseconds since the epoch. | 1640995200000 |
| Location | The geographic location where the dataset should reside. See https://cloud.google.com/bigquery/docs/locations for supported locations. | US |
| Max Time Travel Hours | 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. | 168 |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
| Satisfies PZS | Output only. Reserved for future use. | false |
| Self Link | 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. | https://bigquery.googleapis.com/bigquery/v2/projects/my-project/datasets/my_dataset |
| Storage Billing Model | Optional. Updates storageBillingModel for the dataset. | |
| Tags | Output only. Tags for the Dataset. |
Update Model
Patch specific fields in the specified model. | key: updateModel
| Input | Notes | Example |
|---|---|---|
| Connection | The Google Cloud BigQuery connection to use. | |
| Creation Time | Output only. The time when this dataset was created, in milliseconds since the epoch. | 1609459200000 |
| Dataset ID | The unique identifier for the dataset. | my_dataset |
| Default Trial ID | Output only. The default trialId to use in TVFs when the trialId is not passed in. For single-objective hyperparameter tuning models, this is the best trial ID. For multi-objective hyperparameter tuning models, this is the smallest trial ID among all Pareto optimal trials. | |
| Description | Optional. A descriptive name for the dataset. | This dataset contains sales data |
| Encryption Configuration | Custom encryption configuration (e.g., Cloud KMS keys). This shows the encryption configuration of the model data while stored in BigQuery storage. This field can be used with models.patch to update encryption key for an already encrypted model. | |
| ETag | Output only. A hash of the resource. | W/"abc123def456" |
| Expiration Time | 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. | |
| Feature Columns | 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. | |
| Friendly Name | Optional. A descriptive name for the dataset. | My Dataset |
| Hparam Search Spaces | Output only. Trials of a hyperparameter tuning model sorted by trialId. | |
| Hparam Trials | Output only. Trials of a hyperparameter tuning model sorted by trialId. | |
| Label Columns | 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 | 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. | |
| Last Modified Time | Output only. The date when this dataset was last modified, in milliseconds since the epoch. | 1640995200000 |
| Location | The geographic location where the dataset should reside. See https://cloud.google.com/bigquery/docs/locations for supported locations. | US |
| Model ID | The unique identifier for the model. | my_model |
| Model Reference | Unique identifier for this model. | |
| Model Type | Output only. Type of the model resource. | |
| Optimal Trial IDs | Output only. For single-objective hyperparameter tuning models, it only contains the best trial. For multi-objective hyperparameter tuning models, it contains all Pareto optimal trials sorted by trialId. | ["trial_1", "trial_2"] |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
| Training Runs | Information for all training runs in increasing order of startTime. |
Update Routine
Updates information in an existing routine. | key: updateRoutine
| Input | Notes | Example |
|---|---|---|
| Arguments | Input/output argument of a function or a stored procedure. | |
| Connection | The Google Cloud BigQuery connection to use. | |
| Creation Time | Output only. The time when this dataset was created, in milliseconds since the epoch. | 1609459200000 |
| Dataset ID | The unique identifier for the dataset. | my_dataset |
| Definition Body | 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 definitionBody 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 definitionBody is return '\n';\n Note that both \n are replaced with linebreaks. | concat(x, '\n', y) |
| Description | Optional. The description of the routine, if defined. | This dataset contains sales data |
| Determinism Level | Optional. The determinism level of the JavaScript UDF, if defined. One of DETERMINISM_LEVEL_UNSPECIFIED / DETERMINISTIC / NOT_DETERMINISTIC | DETERMINISTIC |
| ETag | Output only. A hash of the resource. | W/"abc123def456" |
| Imported Libraries | Optional. If language = 'JAVASCRIPT', this field stores the path of the imported JAVASCRIPT libraries. | ["gs://bucket/path/lib.js"] |
| Language | Optional. Defaults to 'SQL' if remoteFunctionOptions field is absent, not set otherwise. One of LANGUAGE_UNSPECIFIED / SQL / JAVASCRIPT / PYTHON / JAVA / SCALA | SQL |
| Last Modified Time | Output only. The date when this dataset was last modified, in milliseconds since the epoch. | 1640995200000 |
| Project ID | The unique identifier for the Google Cloud project. | my-project-12345 |
| Remote Function Options | Optional. Remote function specific options. | |
| Return Table Type | Optional. Can be set only if routineType = 'TABLE_VALUED_FUNCTION'. If absent, the return table type is inferred from definitionBody at query time in each query that references this routine. If present, then the columns in the evaluated table result will be cast to match the column types specified in return table type, at query time. | |
| Return Type | Optional if language = 'SQL'; required otherwise. Cannot be set if routineType = 'TABLE_VALUED_FUNCTION'. If absent, the return type is inferred from definitionBody at query time in each query that references this routine. If present, then the evaluated result will be cast to the specified returned type at query time. | |
| Routine Reference | Reference describing the ID of this routine. | |
| Routine Type | The type of routine. One of ROUTINE_TYPE_UNSPECIFIED / SCALAR_FUNCTION / PROCEDURE / TABLE_VALUED_FUNCTION | SCALAR_FUNCTION |
| Spark Options | Optional. Spark specific options. |
Update Table
Updates information in an existing table. | key: updateTable
| Input | Notes | Example |
|---|---|---|
| Clustering | Clustering specification for the table. Must be specified with time-based partitioning, data in the table will be first partitioned and subsequently clustered. | |
| Connection | The Google Cloud BigQuery connection to use. | |
| Dataset ID | Dataset ID of the table to update. | my_dataset |
| Default Collation | 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. | und:ci |
| Default Rounding Mode | 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. | |
| Description | Optional. A descriptive name for the dataset. | This dataset contains sales data |
| Encryption Configuration | Custom encryption configuration (e.g., Cloud KMS keys). This shows the encryption configuration of the model data while stored in BigQuery storage. This field can be used with models.patch to update encryption key for an already encrypted model. | |
| Expiration Time | 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. | |
| External Data Configuration | Optional. Describes the data format, location, and other properties of a table stored outside of BigQuery. By defining these properties, the data source can then be queried as if it were a standard BigQuery table. | |
| Friendly Name | Optional. A descriptive name for the dataset. | My Dataset |
| Kind | Output only. The resource type. | bigquery#dataset |
| Labels | 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. | |
| Materialized View | Optional. The materialized view definition. | |
| Max Staleness | 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. | INTERVAL 1 DAY |
| Project ID | Project ID of the table to update. | my-project-12345 |
| Range Partitioning | If specified, configures range partitioning for this table. | |
| Require Partition Filter | When true, queries over this table require a partition filter that can be used for partition elimination to be specified. | false |
| Schema | Optional. Describes the schema of this table. | |
| Table ID | Table ID of the table to update. | my_table |
| Table Reference | Reference describing the ID of this routine. | |
| Time Partitioning | If specified, configures time-based partitioning for this table. | |
| View | Optional. The view definition. |
Changelog
2026-02-12
Improved documentation and labeling
2026-01-08
Fixed input handling for actions that accept JSON data as a reference.