Writing Python Code

How to use Python and Python with the Snowpark API.

Do you want to write Python code?

1. First, let's create a new project in the BlackDiamond Studio web portal by clicking the + button.

2. Select the Code from templates option. With this project type, you can use several code templates to facilitate your work. Templates are a set of source code files and pre-installed dependencies that help you start coding faster and easier.

3. Name your project.

4. Set up the project repository. You can create a new repository in BlackDiamond Studio powered by GitLab or connect your existing GitHub repository.

5. As an optional step, connect to your Snowflake account. This is required to deploy to Snowflake.

6. Select the Python language and select the template of your choice. This will populate your repository with Python code that you can use to deploy to Snowflake with the Snowpark API.

7. Click the Finish setup button.

8. Click the Open in IDE button on the project page.

Now that you are in the BlackDiamond Studio IDE, what do you want to do?

1. I want to use Snowpark with Python

Getting started

1. In your project repository, you will find the main.py file that contains a basic example of how to create a Snowpark session, which then can be used to interact with the Snowflake account you should be connected to.

2. A Snowflake connection is required to use Snowpark with Python. This connection can be set up when you create the project in the web portal, or it can be managed in the BlackDiamond Studio IDE with the SQL Tools extension.

3. The SQL Tools extension can be accessed by clicking on the cylinder icon on the Activity Bar (on the left side of the IDE).

Creating and exporting a Stored Procedure / User Defined Function (UDF)

1. Snowflake stored procedures and UDFs can be written in Python and then exported to your Snowflake account.

2. Stored procedures and UDFs can be called or can be used during queries after being exported. They can be invoked from your SQL code or even using Snowpark.

3. If you want to create and export a Stored Procedure / UDF, follow these steps:

   1. Make sure you have a Python file (with .py extension).

   2. Right-click on the Python file and then click on Black Diamond: Export Python UDF. This will create a template file called UDF-Export-Snowflake-Python.sql, located in the target folder.
   3. Replace the placeholders in the UDF-Export-Snowflake-Python.sql template file with the actual values that you want to use. If you are creating and exporting a UDF, you can ignore or remove section 3.1, since it is used for creating a Stored Procedure. Likewise, if you are exporting a Stored Procedure, you can ignore or remove section 3.2, since it is used for creating a UDF.

   4. After filling the placeholders, you can run the SQL commands. Left-click on the UDF-Export-Snowflake-Python.sql and then click on Black Diamond: Deploy. This will execute the SQL script and create the UDF/Procedure.

   5. You can reorganize the template and change it in order to create multiple functions/procedures with the same script.

2. I just want to use Python, I do not need Snowpark

Getting started

• An example of how to write a "Hello World!" program on Python is given at the beginning of the main.py file.

• You can code just like you would typically with Python.

• You can remove the Snowpark code if you don't need it (this includes the user_defined_functions.py and the procedures.py files.

Recommendations

Deploying UDFs/Procedures

• Make sure the packages you want to import are installed in the Snowflake account you are connected to. This query can help you verify the packages that are already installed in the account:

select * from information_schema.packages where language = 'python';

• The procedures must receive a session as the first parameter. This parameter should not be included in the CREATE PROCEDURE statement.