Tips on how to use EXEC ON DB

Overview

The EXEC ON DB operation allows you to execute native SQL commands on any supported database engine, including Snowflake, SQL Server, MySQL, Postgres... The script provided inside the EXEC operation needs to be in the expected database language; for example, T-SQL is the flavor of SQL to be used when the connection points to a SQL Server or Azure Fabric endpoint.

Calling a database engine natively through this command provides powerful options to shape complex payloads and work with staging tables that may be required to keep state information for long-running transactions.

The PER_ROW option is explained in the Claim Check Pattern how-to section.

Certain targets are exposed as HTTP endpoints, such as Databricks. This command only operates on database engines.

Simple Example

In this example, we are simply executing a database script to log information without changing the current pipeline data set. If an error occurs in this script, the pipeline will stop execution and log the error unless the CONTINUE_ON_ERROR option is used.

EXEC ON DB [sql]
(
    INSERT INTO [logtable] (addedOn, message) VALUES (getdate(), 'The pipeline "@jobkey" is executing');
);

Accessing the Pipeline Data Set

In some scenarios, you will need to materialize the current pipeline data set and use it within the script itself. DataZen uses the @pipelinedata() notation to allow you to access the pipeline data within the script. In this example, we are joining the pipeline data with another table and replace the result as the new pipeline data set.

The script inside the EXEC operation can be as complex as needed and can include calling stored procedures, functions, and views.

EXEC ON DB [sql]
(
    SELECT * FROM @pipelinedata() P JOIN [states] S ON P.[stateCode] = S.[state];
) WITH REPLACE;