EXECUTE IMMEDIATE FROM¶

Templating directive¶

You can use either one of the two templating directives.

The recommended directive uses valid SQL syntax:

--!jinja

Optionally, you can use the alternative directive:

#!jinja

Using content from staged files in a template¶

A template can load other staged files either directly through the SnowflakeFile API or through Jinja2’s include (https://jinja.palletsprojects.com/en/stable/templates/#include), import (https://jinja.palletsprojects.com/en/stable/templates/#import), and interitance (https://jinja.palletsprojects.com/en/stable/templates/#template-inheritance) features.

Files can be referenced by absolute paths:

{% include "@my_stage/path/to/my_template" %}
{% import "@my_stage/path/to/my_template" as my_template %}
{% extends "@my_stage/path/to/my_template" %}
{{ SnowflakeFile.open("@my_stage/path/to/my_template", 'r', require_scoped_url = False).read() }}

Include, import, and extends also support relative paths while the SnowflakeFile API supports scoped Snowflake file URLs:

{% include "my_template" %}
{% import "../my_template" as my_template %}
{% extends "/path/to/my_template" %}

See also:

EXECUTE IMMEDIATE

Absolute file path (absoluteFilePath)¶

namespace

Database and/or schema in which the internal or external stage resides, in the form of database_name.schema_name or schema_name. The namespace is optional if a database and schema are currently in use for the user session; otherwise, it is required.

stage_name

Name of the internal or external stage.

path

Case-sensitive path to the file in the stage.

filename

Name of the file to execute. It must contain syntactically correct and valid SQL statements. Each statement must be separated by a semicolon.

Relative file path (relativeFilePath)¶

path

Case-sensitive relative path to the file in the stage. Relative paths support established conventions such as a leading / to indicate the root of a stage’s file system, ./ to refer to the current directory (the directory the parent file is located in) and ../ to refer to the parent directory. For more information, see Usage notes.

filename

Name of the file to execute. It must contain syntactically correct and valid SQL statements. Each statement must be separated by a semicolon.

File errors¶

001501 (02000): File '<directory_name>' not found in stage '<stage_name>'.

001503 (42601): Relative file references like '<filename.sql>' cannot be used in top-level EXECUTE IMMEDIATE calls.

001003 (42000): SQL compilation error: syntax error line <n> at position <m> unexpected '<string>'.

Stage errors¶

002003 (02000): SQL compilation error: Stage '<stage_name>' does not exist or not authorized.

Access control errors¶

003001 (42501): Uncaught exception of type 'STATEMENT_ERROR' in file <file_name> on line <n> at position <m>:
SQL access control error: Insufficient privileges to operate on schema '<schema_name>'

See also: Stage errors.

Templating errors¶

001003 (42000): SQL compilation error:
syntax error line [n] at position [m] unexpected '{'.

000005 (XX000): Python Interpreter Error:
jinja2.exceptions.UndefinedError: '<key>' is undefined
in template processing

001510 (42601): Unable to use value of template variable '<key>'

001518 (42601): Size of expanded template exceeds limit of 100,000 bytes.

Basic example¶

This example executes the file create-inventory.sql located in stage my_stage.

1. Create a file named create-inventory.sql with the following statements:

   CREATE OR REPLACE TABLE my_inventory(
     sku VARCHAR,
     price NUMBER
   );
   
   EXECUTE IMMEDIATE FROM './insert-inventory.sql';
   
   SELECT sku, price
     FROM my_inventory
     ORDER BY price DESC;
   

   Copy

2. Create a file named insert-inventory.sql with the following statements:

   INSERT INTO my_inventory
     VALUES ('XYZ12345', 10.00),
            ('XYZ81974', 50.00),
            ('XYZ34985', 30.00),
            ('XYZ15324', 15.00);
   

   Copy

3. Create an internal stage my_stage:

   CREATE STAGE my_stage;
   

   Copy

4. Upload both local files to the stage using the PUT command:

   PUT file://~/sql/scripts/create-inventory.sql @my_stage/scripts/
     AUTO_COMPRESS=FALSE;
   
   PUT file://~/sql/scripts/insert-inventory.sql @my_stage/scripts/
     AUTO_COMPRESS=FALSE;
   

   Copy

5. Execute the create-inventory.sql script located in my_stage:

   EXECUTE IMMEDIATE FROM @my_stage/scripts/create-inventory.sql;
   

   Copy

   Returns:

   +----------+-------+
   | SKU      | PRICE |
   |----------+-------|
   | XYZ81974 |    50 |
   | XYZ34985 |    30 |
   | XYZ15324 |    15 |
   | XYZ12345 |    10 |
   +----------+-------+
   

A simple template example¶

1. Create a template file setup.sql with two variables and the templating directive:

   --!jinja
   
   CREATE SCHEMA {{env}};
   
   CREATE TABLE RAW (COL OBJECT)
       DATA_RETENTION_TIME_IN_DAYS = {{retention_time}};
   

   Copy

2. Create a stage — optional if you already have a stage to which you can upload files.

   For example, create an internal stage in Snowflake:

   CREATE STAGE my_stage;
   

   Copy

3. Upload the file to your stage.

   For example, use the PUT command from your local environment to upload file setup.sql to stage my_stage:

   PUT file://path/to/setup.sql @my_stage/scripts/
     AUTO_COMPRESS=FALSE;
   

   Copy

4. Execute the file setup.sql:

   EXECUTE IMMEDIATE FROM @my_stage/scripts/setup.sql
       USING (env=>'dev', retention_time=>0);
   

   Copy

A template example with macros, conditionals, loops, and imports¶

1. Create a template file containing a macro definition.

   For example, create a file macros.jinja in your local environment:

   {%- macro get_environments(deployment_type) -%}
     {%- if deployment_type == 'prod' -%}
       {{ "prod1,prod2" }}
     {%- else -%}
       {{ "dev,qa,staging" }}
     {%- endif -%}
   {%- endmacro -%}
   

   Copy

2. Create a template file and add the templating directive (--!jinja2) to the top of the file.

   After the templating directive, add an import statement to import the macro defined in the file that you created in the previous step. For example, create a file setup-env.sql in your local environment:

   --!jinja2
   {% from "macros.jinja" import get_environments %}
   
   {%- set environments = get_environments(DEPLOYMENT_TYPE).split(",") -%}
   
   {%- for environment in environments -%}
     CREATE DATABASE {{ environment }}_db;
     USE DATABASE {{ environment }}_db;
     CREATE TABLE {{ environment }}_orders (
       id NUMBER,
       item VARCHAR,
       quantity NUMBER);
     CREATE TABLE {{ environment }}_customers (
       id NUMBER,
       name VARCHAR);
   {% endfor %}
   

   Copy

3. Create a stage — optional if you already have a stage to which you can upload files.

   For example, create an internal stage in Snowflake:

   CREATE STAGE my_stage;
   

   Copy

4. Upload the file to your stage.

   For example, use the PUT command from your local environment to upload the files setup-env.sql and macros.jinja to the stage my_stage:

   PUT file://path/to/setup-env.sql @my_stage/scripts/
     AUTO_COMPRESS=FALSE;
   PUT file://path/to/macros.jinja @my_stage/scripts/
     AUTO_COMPRESS=FALSE;
   

   Copy

5. Preview the SQL statements rendered by the template to check for any problems with your Jinja2 code:

   EXECUTE IMMEDIATE FROM @my_stage/scripts/setup-env.sql
     USING (DEPLOYMENT_TYPE => 'prod') DRY_RUN = TRUE;
   

   Copy

   Returns:

   +----------------------------------+
   | rendered file contents           |
   |----------------------------------|
   | --!jinja2                        |
   | CREATE DATABASE prod1_db;        |
   |   USE DATABASE prod1_db;         |
   |   CREATE TABLE prod1_orders (    |
   |     id NUMBER,                   |
   |     item VARCHAR,                |
   |     quantity NUMBER);            |
   |   CREATE TABLE prod1_customers ( |
   |     id NUMBER,                   |
   |     name VARCHAR);               |
   | CREATE DATABASE prod2_db;        |
   |   USE DATABASE prod2_db;         |
   |   CREATE TABLE prod2_orders (    |
   |     id NUMBER,                   |
   |     item VARCHAR,                |
   |     quantity NUMBER);            |
   |   CREATE TABLE prod2_customers ( |
   |     id NUMBER,                   |
   |     name VARCHAR);               |
   |                                  |
   +----------------------------------+
   

6. Execute the file setup-env.sql:

   EXECUTE IMMEDIATE FROM @my_stage/scripts/setup-env.sql
     USING (DEPLOYMENT_TYPE => 'prod');
   

   Copy