ETL: ExecuteSqlNonQuery

Execute SQL DML or DDL statements that modify data or database schema without returning results. Use for in-pipeline database operations in ETL.

Description

Execute a SQL statement or a batch of SQL statements that modify the data or the schema (DML or DDL).

MySQL, PostgreSQL, Microsoft SQL Server and Amazon Redshift connections:

Syntax

ExecuteSqlNonQuery(connection_id, sql_statement[, param1] [,…paramN])

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.

param11 … paramN - optional parameters to pass to the query. Use ? as placeholder in the query. Make sure to use arguments of the data types that are compatible with the columns’ data types. Do not enclose the placeholders in quotes.

Google BigQuery connections:

Syntax

ExecuteSqlNonQuery(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:

{
  "useQueryCache" : boolean
 ,"useLegacySql" : boolean
}

• 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).

Examples

ExecuteSqlNonQuery('mysql_55','INSERT INTO events (description) VALUES (\'Started processing data\'); UPDATE Products SET Price = Price * 2;')

ExecuteSqlNonQuery('mysql_55','INSERT INTO events (description) VALUES (?)',event_data)

ExecuteSqlNonQuery('bq_58', 'INSERT INTO events_stg (id, ts, edata) SELECT * FROM events_old WHERE ts < \'2017-01-01\';','{"useQueryCache" : false}')

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 statement executed successfully.
• rowsAffected - numbers of rows affected (depends on database platform)
• errorCode - returned error code if status is false (statement failed)
• 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.