SQL Script - Maia Documentation

SQL Script is an orchestration component that supports both Data Definition Language (DDL) and Data Manipulation Language (DML) statements. Use it to run statements such as CREATE, ALTER, and DROP (DDL), or INSERT, UPDATE, DELETE, and MERGE (DML) against your warehouse. The SQL script can contain multiple SQL statements. However, since there is no way to recover the output of the script, the statements should not be SELECT. If you wish to write SELECT queries, the SQL transformation component allows for custom SELECT statements where the output can be used as part of the transformation flow. There is no guarantee that multiple SQL statements will always run in the same session. You should avoid using transaction control statements such as COMMIT or ROLLBACK in your scripts. executes each query within an SQL Script component using a connection from a connection pool, meaning that each query may be executed by a different connection. When connections in the pool are first created, they use the warehouse that you defined as the default in the environment configuration. If you write your SQL statements in the SQL script property, automatically saves your SQL statements as you work. You can sample data and view variables while writing the script.

Use cases

This component is versatile and can be used to run DDL and DML operations directly against your warehouse. For example, you can use it to:

Create, alter, or drop database objects such as tables, views, and schemas.
Insert, update, delete, or merge records in your warehouse tables.
Create Snowflake stored procedures to encapsulate reusable logic.

Variables

This component supports the use of pipeline and project variables. For more information, read Project and pipeline variables. For any code written inside the component, the variable syntax ${variable} is supported. Read Using variables in Code Editor to learn more. Additionally, native variable syntax is supported for the following:

Snowflake variable syntax.
Databricks variable syntax. For some specific Databricks limitations and workarounds, read Variable substitution in Databricks, below.
Google BigQuery variable syntax.
Amazon Redshift has no direct equivalent to the $var syntax. The equivalent would be to use a Redshift mechanism such as SET / SHOW. Read SET and SHOW for details.

’s ${variable} syntax is supported for SQL statements written within the SQL Script component (i.e. when Script location is set to Component). This syntax is not supported in .sql files in Code Editor. Snowflake/Databricks/BigQuery variable syntax is supported regardless of whether you write your SQL statements directly in the SQL Script component or in a .sql file using Code Editor.

Properties

string

required

A human-readable name for the component.

drop-down

required

Component: Use the SQL script property to add your SQL statements to this component. This is the default setting.
File: Use the Select file property to select a .sql file in your project. The drop-down lists all .sql files from the repository connected to your project.

drop-down

Select a strategy for declaring project and pipeline variables as SQL variables. Before the component executes, no project or pipeline variables are declared as SQL variables for SQL scripts.

Include all: All variables you include in the script (using the syntax required by your warehouse as described in Variables, above) are declared and processed as SQL variables.
Include selected: You select which variables in the script are declared as SQL variables. Only variables you list in Variables to include are declared and processed as SQL variables.

The default is Include selected.This setting is ignored if Allow inline variable replacement has been set to Yes.

object selector

If you set Declare SQL variables to Include selected, you must list the specific variables you want to declare and use as SQL variables.This setting is ignored if Allow inline variable replacement has been set to Yes.

drop-down

If this is set to Yes, then all variables using ${variable_name} syntax are resolved before the script is sent to the warehouse for execution. The setting of Declare SQL variables and the Variables to include properties are ignored in this case.No is selected by default. We recommend leaving this set to No and using native SQL variables. When set to No, you need to use the Declare SQL variables property to declare variables in your SQL script as SQL variables.We recommend only selecting Yes if you already have scripts that use ${} syntax and do not want to convert these. If you select Yes, we recommend making sure that your script is not vulnerable to injection attacks from the content of your ${} expressions.

code editor

Add your SQL script into the code editor. This script is stored in the component.

drop-down

Select a .sql file in your project to run via this component. The drop-down lists all .sql files from the repository connected to your project.Double-click a .sql file in the Files panel to open that .sql file in the code editor.

Variable substitution in Databricks

Variable substitution won’t work if the Script location parameter is set to File and you’re using a SQL Warehouse (Classic or Serverless). In this scenario, the $(...) variable substitution syntax (for example, CREATE TABLE $(schema)) doesn’t function as expected. To work around this limitation, do one of the following:

In the Script location parameter, use the drop-down to select File with an all-purpose compute cluster
Select Component, and place your SQL directly in the SQL script parameter.

This limitation doesn’t affect procedural logic (e.g. DECLARE my_var = 123; CREATE TABLE my_var).
This limitation doesn’t apply when using an all-purpose compute cluster. However, make sure the variable is enclosed in backticks to prevent potential errors (e.g. `${var}`).
Snowflake, Amazon Redshift, and Google BigQuery aren’t affected by this issue.

Creating Snowflake stored procedures

Since CREATE PROCEDURE is a DDL statement, there is no dedicated low-code component for creating stored procedures. The SQL Script component is the appropriate tool for this operation. Snowflake supports stored procedures written in SQL, JavaScript, Python, Java, and Scala. Use the following SQL to create a stored procedure:

CREATE OR REPLACE PROCEDURE <schema_name>.<procedure_name>(<parameter_name> <data_type>)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    -- Your procedure logic here
    RETURN 'Done';
END;
$$;

To call the stored procedure, use a separate SQL Script component:

CALL <schema_name>.<procedure_name>('<argument>');

Each query in an SQL Script component may execute on a different connection. Create and call stored procedures in separate components to avoid session-related issues.

​Use cases

​Variables

​Properties

​Variable substitution in Databricks

​Creating Snowflake stored procedures

Use cases

Variables

Properties

Variable substitution in Databricks

Creating Snowflake stored procedures