Examples of using Git with Snowflake

Examples in this topic describe how to use an integrated Git repository when developing Snowflake applications and how to execute SQL scripts in a repository.

Use a Git repository file as a stored procedure handler

After you’ve set up integration between Snowflake and your Git repository, you can use files from the repository as handler code in stored procedures and UDFs. Note that, as with other staged handlers, you must qualify the handler function name with the name of its containing class or module.

This example describes how to use Python handler code from the repository in a stored procedure.

Code required by this example

The handler in this example depends on a database created with SQL code similar to the following:

CREATE DATABASE example_db;
USE DATABASE example_db;
CREATE SCHEMA example_schema;
USE SCHEMA example_schema;

CREATE OR REPLACE TABLE employees(id NUMBER, name VARCHAR, role VARCHAR);
INSERT INTO employees (id, name, role) VALUES (1, 'Alice', 'op'), (2, 'Bob', 'dev'), (3, 'Cindy', 'dev');
Copy

The example uses the following Python handler code contained in filter.py:

from snowflake.snowpark.functions import col

def filter_by_role(session, table_name, role):
  df = session.table(table_name)
  return df.filter(col("role") == role)
Copy

Commit the file and refresh the repository stage

  1. From your Git client, add the code to the repository.

    Code in the following example uses the git command-line tool to add and commit the handler file to the local repository, then push it to the remote repository referenced by the repository stage in Snowflake:

    $ git add python-handlers/filter.py
$ git commit -m "Adding code to filter by role"
$ git push
    Copy

  2. In Snowflake, refresh the repository stage.

    Assuming you’ve set up integration between Snowflake and your Git repository, resulting in a repository stage, you can refresh the stage by fetching from the repository.

    Using Snowflake to refresh from your repository is similar to working with other Git client tools, where you fetch from the repository before beginning work to ensure that you have the latest changes.

    Code in the following example executes the ALTER GIT REPOSITORY command to retrieve the latest changes from the repository. The code generates a full clone that includes branches, tags, and commits.

    ALTER GIT REPOSITORY snowflake_extensions FETCH;
    Copy

Create and execute a procedure that uses the file in the repository

  1. In Snowflake, write the procedure.

    When you write a procedure, you can reference its handler code at the code file’s location in the repository stage. For example, to refer to a file python-handlers/filter.py in the main branch of a repository synchronized to a Git repository stage called snowflake_extensions, you would use syntax similar to the following:

    @snowflake_extensions/branches/main/python-handlers/filter.py
    Copy

    Code in the following example creates a procedure called filter_by_role, specifying handler code stored in the repository stage:

    CREATE OR REPLACE PROCEDURE filter_by_role(tableName VARCHAR, role VARCHAR)
  RETURNS TABLE(id NUMBER, name VARCHAR, role VARCHAR)
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.9'
  PACKAGES = ('snowflake-snowpark-python')
  IMPORTS = ('@example_db.example_schema.snowflake_extensions/branches/main/python-handlers/filter.py')
  HANDLER = 'filter.filter_by_role';
    Copy

  2. Execute the procedure.

    The following code executes the procedure.

    CALL filter_by_role('employees', 'dev');
    Copy

    The following is an example of output from the procedure.

    ---------------------
| ID | NAME  | ROLE |
---------------------
| 2  | Bob   | dev  |
---------------------
| 3  | Cindy | dev  |
---------------------

Use a Git repository file to configure new accounts

This example describes how to execute a SQL script contained in a repository stage. The script in the example creates a user and role.

This example uses the EXECUTE IMMEDIATE FROM command to execute the SQL statements contained in a file in the repository stage.

With EXECUTE IMMEDIATE FROM, you can execute (from any Snowflake session) scripts you manage in your Git repository. For example, you might have a script that sets up every new Snowflake account in your organization. The script might contain statements to create users, roles, objects, and grant privileges on the account and objects.

  1. Create the file setup.sql with the following contents:

    CREATE ROLE analyst;

CREATE USER gladys;

GRANT ROLE analyst TO USER gladys;

SHOW GRANTS TO USER gladys;
    Copy

  2. Commit your SQL file to your Git repository. For detailed instructions, see Commit the file and refresh the repository stage.

    Use the git command line tool to commit the file to your Git repository:

    git add scripts/setup.sql
git commit -m "Adding code to set up new accounts"
git push
    Copy

  3. In Snowflake, refresh the repository stage. For detailed instructions, see Commit the file and refresh the repository stage.

    Refresh the repository stage configuration_repo:

    ALTER GIT REPOSITORY configuration_repo FETCH;
    Copy

  4. In Snowflake, execute the file in your repository stage:

    Note

    The user executing the following statement must use a role that has the required privileges to execute all statements in the file. For more information, see Access control requirements.

    EXECUTE IMMEDIATE FROM @configuration_repo/branches/main/scripts/setup.sql;
    Copy

    The EXECUTE IMMEDIATE FROM commands returns the results of the last SQL statement in the file:

    +-------------------------------+---------+------------+--------------+--------------+
| created_on                    | role    | granted_to | grantee_name | granted_by   |
|-------------------------------+---------+------------+--------------+--------------|
| 2023-07-24 22:07:04.354 -0700 | ANALYST | USER       | GLADYS       | ACCOUNTADMIN |
+-------------------------------+---------+------------+--------------+--------------+
Language: English