ExecuteBQInsert

Description

Starts a query job in Google BigQuery and writes the query results to a destination table.

Syntax

ExecuteBQInsert(connection_id, sql_query, configuration_json)

Arguments

  connection_id (string) - The unique identifier of the connection to use to execute the query. You can find it in the connection's page. 

  sql_query  - String expression that evaluates to a query that returns a scalar value.

  configuration_json - optional string:


{
  "destinationTable" : {
 	  "tableId" : string
 	 ,"datasetId" : string
 	 ,"projectId" : string
 	}
 ,"useQueryCache" : boolean
 ,"useLegacySql" : boolean
 ,"jobReference" : { 
           "jobId" : string
          , "projectId" : string  
         }
  ,"writeDisposition": string
  ,"createDisposition": string
  ,"maximumBillingTier" : int
}
  • destinationTable.tableId - string expression. Specifies the name of the destination table.
  • destinationTable.datasetId - optional string expression. Specifies the destination table's dataset. Defaults to connection's default dataset.
  • destinationTable.projectId - optional string expression. Specifies the destination table's project. Defaults to connection's default project.
  • useQueryCache (optional) - boolean expression. Specifies whether to look for the result in the query cache (default value is true).
  • useLegacySql (optional) - boolean expression. Specifies whether to use BigQuery's legacy SQL dialect for the query (default value is false).
  • jobReference.jobId (optional) - string expression. Specifies a job id that can be queried afterwards.
  • jobReference.projectId (optional) - string expression. Specifies a project to add the job to. If jobId is specified and projectId is empty, the connection's projectId is used.
  • writeDisposition (optional) - string expression. The following values are supported:
    • WRITE_TRUNCATE: If the table already exists, BigQuery overwrites the table data.
    • WRITE_APPEND: If the table already exists, BigQuery appends the data to the table.
    • WRITE_EMPTY: If the table already exists and contains data, a 'duplicate' error is returned in the job result.
    The default value is WRITE_EMPTY.
  • createDisposition (optional) - string expression. The following values are supported:
    • CREATE_IF_NEEDED: If the table does not exist, BigQuery creates the table.
    • CREATE_NEVER: The table must already exist. If it does not, a 'notFound' error is returned in the job result.
    The default value is CREATE_IF_NEEDED.
  • maximumBillingTier (optional) - Limits the billing tier for this job. Queries that have resource usage beyond this tier will fail. If not specified, defaults to your project default.
  • Examples

    ExecuteBQInsert('bq_58', 'SELECT * FROM events_old WHERE ts < \'2017-01-01\';','{"destinationTable" : {"tableId" : "events_stg"}}')

    Notes

    This function only executes in job runtime. When validating a package with variables that use the function or in X-console, the function returns null.

    Return value datatype

    The function returns a map with the following keys:

    • status - true if the stsatement executed successfully.
    • exception - returned exception string if status is false (statement failed)

    Impact of null value

    If the connection_id or sql_query are null, null is returned.

Feedback and Knowledge Base