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.