Develop and run code in Snowflake Notebooks¶

This topic describes how to write and run SQL, Python, and Markdown code in Snowflake Notebooks.

笔记本单元格基础知识

This section introduces some basic cell operations. When you create a notebook, three example cells are displayed. You can modify those cells or add new ones.

创建新单元格

Snowflake Notebooks support three types of cells: SQL, Python, and Markdown. To create a new cell, you can either hover over an existing cell or scroll to the bottom of the notebook, then select one of the buttons for the cell type you want to add.

使用以下方法之一为现有单元格更改语言：

• 选择语言下拉菜单，然后选择其他语言。

  

• Use keyboard shortcuts.

编辑单元格

为防止编辑冲突，一次仅有一名用户能编辑一个单元格。如有其他用户尝试编辑活动单元格，系统将显示通知。60 秒未活动后，单元格将可供编辑。

移动单元格

您可以通过使用鼠标拖放单元格或使用操作菜单来移动单元格：

1. (Option 1) Hover your mouse over the existing cell you want to move. Select the (drag and drop) icon on the left side of the cell and move the cell to its new location.
2. (Option 2) Select the vertical ellipsis (actions) menu. Then select the appropriate action.

删除单元格

要删除单元格，请在笔记本中完成以下步骤：

1. Select the vertical ellipsis (more actions) menu.
2. Select Delete.
3. Select Delete again to confirm.

You can also use a keyboard shortcut to delete a cell.

For considerations when using Python and SQL cells, see Considerations for running notebooks.

Run cells in Snowflake Notebooks¶

To run Python and SQL cells in Snowflake Notebooks, you can:

• 运行单个单元格：频繁更新代码时请选择此选项。

  • Press CMD + return on a Mac keyboard, or CTRL + Enter on a Windows keyboard.
  • Select , or Run this cell only.

• Run all cells in a notebook in sequential order: Choose this option before presenting or sharing a notebook to ensure that the recipients see the most current information. This option executes all SQL and Python code cells in the notebook from top to bottom. If an error occurs in any cell, execution will halt and subsequent cells will not run. This behavior also applies to scheduled notebooks. For example, if you run a notebook that has 10 cells, and in cell 2 there is a SQL syntax error, the notebook will stop running after cell 2.

  • Press CMD + shift + return on a Mac keyboard, or CTRL + Shift + Enter on a Windows keyboard.
  • Select Run all.

• 运行单元格并前进到下一个单元格：选择此选项可以运行单元格并更快地前进到下一个单元格。

  • Press shift + return on a Mac keyboard, or Shift + Enter on a Windows keyboard.
  • Select the vertical ellipsis (more actions) for a cell, and choose Run cell and advance.

• 运行上述全部：运行引用早期单元格结果的单元格时选择此选项。

  • Select the vertical ellipsis (more actions) for a cell, and choose Run all above.

• Run all below: Choose this option when running a cell that later cells depend on. This option runs the current cell and all following cells.

  • Select the vertical ellipsis (more actions) for a cell, and choose Run all below.

当一个单元格正在运行时，其他运行请求将排队，并将在当前正在运行的单元格完成后执行。

折叠和展开单元格

您可以通过选择笔记本顶部的其中一个单元格显示选项来控制笔记本的可见程度：

1. Select the vertical ellipsis (more actions) menu.
2. Select Show/hide all and choose the appropriate option:
   • 显示全部： 显示每个单元格的代码和结果。
   • 仅显示代码： 隐藏结果并仅显示代码单元格。
   • 仅显示结果： 隐藏代码并仅显示输出。
   • 全部隐藏： 折叠所有单元格的代码和结果。

这些选项在以下情况下很有用：

• 您想专注于阅读代码或查看结果。
• 您正在展示或共享您的笔记本。
• 您需要更高效地浏览大型笔记本。

复制单元格

复制单元格可以帮助解决以下问题：

• 测试查询或函数的变体。
• 在不替换工作版本的情况下进行调试。
• 并排比较不同的输出。
• 在不丢失原始单元格的情况下重复使用代码或修改现有单元格。

要复制笔记本单元格，请执行以下操作：

1. From the cell to duplicate, select the vertical ellipsis (more actions) menu.
2. Select Duplicate.

单元格的副本会紧接着出现在原始单元格的下方。

单元格小地图

单元格小地图显示在笔记本的右侧栏中，提供了笔记本中所有单元格的可拖动紧凑列表。小地图中的每个条目对应于一个代码或文本单元格，并反映了单元格出现的顺序。

• 当前单元格： 所选单元格将在小地图中突出显示。
• 重新排序： 在小地图中拖放项目可快速更改笔记本中单元格的顺序。
• 导航： 点击小地图中的单元格名称可直接跳转到该单元格。

此功能对于更有效地浏览大型笔记本和重新组织内容非常有用。

带参数运行笔记本

When you use the EXECUTE NOTEBOOK command to run a notebook, you can pass arguments to the notebook. In a Python cell in the notebook, you can access these arguments by using the sys.argv variable, which is a built-in Python list that holds command-line arguments.

通过向笔记本传递实参，您可以自定义笔记本行为。您可以：

• 对笔记本执行进行个性化或自定义。
• 重复使用同一笔记本进行多次输入。
• 支持自动化或任务调度。

示例

In a Python cell in the notebook, you can access the arguments by using the sys.argv variable.

查看传递给笔记本的所有实参

打印传递给笔记本的完整实参列表。

import sys
print(sys.argv)

如果使用以下命令执行笔记本：

EXECUTE NOTEBOOK MY_DATABASE.PUBLIC.MY_NOTEBOOK(
  'parameter_string a,b,c,d',
  'target_database=PROD_DB'
);

输出将是：

['parameter_string', 'a,b,c,d', 'target_database=PROD_DB']

打印每个实参

循环遍历并单独打印每个实参。

for arg in sys.argv:
    print(arg)

输出将是：

parameter_string
a,b,c,d
target_database=PROD_DB

访问特定实参

访问第二个实参。

second_param = sys.argv[1]
print(second_param)

输出将是：

a,b,c,d

解析包含逗号分隔值的实参

如果实参包含逗号分隔的值列表，可以将其拆分为单独的值。

value_list = sys.argv[1].split(",")
print(value_list)

输出将是：

['a', 'b', 'c', 'd']

您也可以循环遍历这些值：

for value in value_list:
  print(value)

提取包含键值对的实参

If an argument includes a key-value pair (for example, key=value), extract the value.

target_database = sys.argv[2].split("=")
print(target_database[1])

输出将是：

PROD_DB

单个字符串的替代语法

You can set a session variable to the value of an argument and pass the session variable to the notebook.

SET PARAMS = 'parameter_string a,b,c,d';
EXECUTE NOTEBOOK MY_DATABASE.PUBLIC.MY_NOTEBOOK($PARAMS);

查看参数化运行的结果

To view the result of a notebook run that was triggered using EXECUTE NOTEBOOK:

1. Sign in to Snowsight.
2. In the navigation menu, select Projects » Notebooks.
3. Select the Calendar icon.
4. Select View run history.
5. 找到对应的笔记本执行记录并打开结果。

系统将打开一个只读笔记本，显示该次运行的结果。

备注

• sys.argv contains only the strings passed via EXECUTE NOTEBOOK.
• Only strings are supported. If another data type (such as an integer) is passed, it will be interpreted as NULL. For more information, see EXECUTE NOTEBOOK.

检查单元格状态

单元格运行的状态由单元格显示的颜色表示。此状态颜色显示在两个位置：单元格的左侧边框和右侧的单元格导航图中。

单元格状态颜色：

• 蓝点：该单元格已被修改但尚未运行。
• 红色：该单元格在当前会话中运行，并且发生了错误。
• 绿色：该单元格在当前会话中运行，没有错误。
• 移动绿色：该单元格当前正在运行。
• Gray: The cell has run in a previous session and the results shown are from the previous session. Cell results from the previous interactive session are kept for 7 days. Interactive session means the user runs the notebook in an interactive manner in Snowsight rather than those that were run by a schedule or the EXECUTE NOTEBOOK SQL command.
• Blinking gray: The cell is waiting to be run after you select Run All.

单元格运行完成后，运行所花费的时间将显示在单元格的顶部。选择此文本可查看运行详细信息，包括开始和结束时间以及总耗时。

SQL 单元格包含其他信息，例如用于运行查询的仓库、返回的行以及指向查询 ID 页面的超链接。

停止正在运行的单元格

To stop the execution of any code cells that are currently running, select Stop on the top right of the cell. You can also select Stop on the top right of the Notebooks page. While cells are running, Run all becomes Stop.

这会停止执行当前正在运行的单元格以及所有已计划运行的后续单元格。

键盘快捷键

Snowflake Notebooks support various keyboard shortcuts to help accelerate your development process.

You can also see the list of keyboard shortcuts by selecting the keyboard icon at the bottom right corner, and then selecting Keyboard shortcuts.

In addition, you can use the same keyboard shortcuts that you use for worksheets. See Perform tasks with keyboard shortcuts.

使用 Markdown 格式化文本¶

要将 Markdown 包含在笔记本中，请添加一个 Markdown 单元格：

1. Use a keyboard shortcut and select Markdown, or select + Markdown.
2. Select the Edit markdown pencil icon or double-click the cell, and start writing Markdown.

您可以输入有效的 Markdown 来格式化文本单元格。键入时，格式化的文本会显示在 Markdown 语法下方。

To view only the formatted text, select the Done editing checkmark icon.

Markdown 基础知识¶

本部分介绍基本的 Markdown 语法，帮助您入门。

标头

Heading level	Markdown syntax	Example
Top level	Copy codeExpand code block # Top-level Header Show lessSee more Scroll to top
2nd-level	Copy codeExpand code block ## 2nd-level Header Show lessSee more Scroll to top
3rd-level	Copy codeExpand code block ### 3rd-level Header Show lessSee more Scroll to top

Expand

Show lessSee more

内联文本格式

Text format	Markdown syntax	Example
Italics	Copy codeExpand code block *italicized text* Show lessSee more Scroll to top
Bold	Copy codeExpand code block **bolded text** Show lessSee more Scroll to top
Link	Copy codeExpand code block [Link text](url) Show lessSee more Scroll to top

Expand

Show lessSee more

列表

List type	Markdown syntax	Example
Ordered list	Copy codeExpand code block 1. first item 2. second item 3. Nested first 4. Nested second Show lessSee more Scroll to top
Unordered list	Copy codeExpand code block - first item - second item - Nested first - Nested second Show lessSee more Scroll to top

Expand

Show lessSee more

代码格式

Language	Markdown syntax	Example
Python	Copy codeExpand code block ```python import pandas as pd df = pd.DataFrame([1,2,3]) ``` Show lessSee more Scroll to top Copy codeExpand code block </td> <td> <Figure src="/static/images/snowsight/notebooks/notebooks-md-python.png" alt="Python code snippet in Markdown"> </Figure> </td> </tr> <tr> <td>SQL</td> <td> ```markdown ```sql SELECT * FROM MYTABLE Show lessSee more Scroll to top Copy codeExpand code block </td> <td> <Figure src="/static/images/snowsight/notebooks/notebooks-md-sql.png" alt="SQL code snippet in Markdown"> </Figure> </td> </tr> </tbody> </Table> </div> **Embed images** <div className="colwidths-given"> <Table> <colgroup> <col style={{width: "25.0%"}} /> <col style={{width: "25.0%"}} /> <col style={{width: "50.0%"}} /> </colgroup> <thead> <tr> <th>File type</th> <th>Markdown syntax</th> <th>Example</th> </tr> </thead> <tbody> <tr> <td>Image</td> <td> ```markdown  Show lessSee more Scroll to top

Expand

Show lessSee more

For a notebook that demonstrates these Markdown examples, see the Markdown cells (https://github.com/Snowflake-Labs/snowflake-demo-notebooks/blob/main/Visual%20Data%20Stories%20with%20Snowflake%20Notebooks/Visual%20Data%20Stories%20with%20Snowflake%20Notebooks.ipynb) section of the visual data stories notebook.

Understanding cell outputs¶

When you run a Python cell, the notebook displays the following types of output from the cell are displayed in the results:

• Any results written to the console, such as logs, errors, and warnings and output from print() statements.

• DataFrames are automatically printed with Streamlit’s interactive table display (https://docs.streamlit.io/develop/api-reference/data/st.dataframe), st.dataframe().

  • The supported DataFrame display types include pandas DataFrame, Snowpark DataFrames, and Snowpark Tables.
  • For Snowpark, printed DataFrames are evaluated eagerly without the need to run the .show() command. If you prefer not to evaluate the DataFrame eagerly, for example when running the notebook in non-interactive mode, Snowflake recommends removing the DataFrame print statements to speed up the overall runtime of your Snowpark code.

• Visualizations are rendered in outputs. To learn more about visualizing your data, see Visualize data in Snowflake Notebooks.

Additionally, you can access the results of your SQL query in Python and vice versa. See Reference cells and variables in Snowflake Notebooks.

Cell output limits¶

Only 10,000 rows or 8 MB of DataFrame output is shown as cell results, whichever is lower. However, the entire DataFrame is still available in the notebook session for use. For example, even though the entire DataFrame isn’t rendered, you can still perform data transformation tasks.

For each cell, only 20 MB of output is allowed. If the size of the cell output exceeds 20 MB, the output will be dropped. Consider splitting the content into multiple cells if that happens.

Reference cells and variables in Snowflake Notebooks¶

You can reference the previous cell results in a notebook cell. For example, to reference the result of a SQL cell or the value of a Python variable, see the following tables:

Referencing SQL output in Python cells:

SELECT 'FRIDAY' as SNOWDAY, 0.2 as CHANCE_OF_SNOW
UNION ALL
SELECT 'SATURDAY',0.5
UNION ALL
SELECT 'SUNDAY', 0.9;

snowpark_df = cell1.to_df()

my_df = cell1.to_pandas()

Referencing variables in SQL code:

SELECT * FROM {{cell2}} where PRICE > 500

c = "USA"

SELECT * FROM my_table WHERE COUNTRY = '{{c}}'

column_name = "COUNTRY"

SELECT * FROM my_table WHERE {{column_name}} = 'USA'

Considerations for running notebooks¶

• Notebooks run using caller’s rights. For additional considerations, see Changing the session context for a notebook.

• You can import Python libraries to use in a notebook. For details, see Import Python packages to use in notebooks.

• When referencing objects in SQL cells, you must use fully qualified object names, unless you are referencing object names in a specified database or schema. See Changing the session context for a notebook.

• Notebook drafts are saved every three seconds.

• You can use Git integration to maintain notebook versions.

• You can configure an idle timeout setting to automatically shut down the notebook session once the setting is met. For information, see Idle time and reconnection.

• Notebook cell results are only visible to the user who ran the notebook and are cached across sessions. Reopening a notebook displays past results from the last time the user ran the notebook using Snowsight.

• BEGIN … END (Snowflake Scripting) is not supported in SQL cells. Instead, use the Session.sql().collect() method in a Python cell to run the scripting block. Chain the sql call with a call to collect to immediately execute the SQL query.

  The following code runs a Snowflake scripting block using the session.sql().collect() method:

  Copy codeExpand code block

  from snowflake.snowpark.context import get_active_session
  session = get_active_session()
  code_to_run = """
  BEGIN
   CALL TRANSACTION_ANOMALY_MODEL!DETECT_ANOMALIES(
       INPUT_DATA => SYSTEM$REFERENCE('TABLE', 'ANOMALY_INFERENCE'),
       TIMESTAMP_COLNAME =>'DATE',
       TARGET_COLNAME => 'TRANSACTION_AMOUNT',
       CONFIG_OBJECT => {'prediction_interval': 0.95}
   );
  
   LET x := SQLID;
   CREATE TABLE ANOMALY_PREDICTIONS AS SELECT * FROM TABLE(RESULT_SCAN(:x));
  END;
  """
  data = session.sql(code_to_run).collect(block=True);

  Show lessSee more

  Scroll to top