Snowflake SQL 資料函式庫查詢

– 11 分鐘閱讀

Snowflake 作為雲端資料倉儲解決方案，提供高效的資料查詢和分析能力。本從 SQL 的基本概念開始，逐步深入 Snowflake 的核心功能，涵蓋資料函式庫的建立、資料載入、SQL 查詢語法以及各種子句的應用。除了 Snowsight 網頁介面操作說明外，也提供 SnowSQL CLI 的使用，讓使用者能更彈性地操作 Snowflake。此外，中也包含 TPCH 範例資料函式庫的建置與使用說明，方便讀者進行實作練習。

SQL 與 Snowflake 的基礎介紹

SQL（Structured Query Language）是一種用於管理和操作資料函式庫的標準語言。它是一種非程式式語言，意味著使用者定義需要執行的操作，但不指定如何執行這些操作。SQL 語言主要分為兩類別：資料定義語言（DDL）和資料操作語言（DML）。DDL 用於建立或修改資料函式庫結構，如表格、索引和檢視等；而 DML 則用於插入、修改、刪除資料表中的資料列，以及檢索資料。

SQL 的特性與重要性

SQL 的非程式式特性使得它能夠讓資料函式庫伺服器決定如何最有效地檢索資料。例如，當執行一個統計每個客戶銷售額並列出前十名客戶的報告時，使用者只需撰寫一個 SELECT 陳述式來彙總銷售資料，而資料函式庫伺服器則會決定如何高效地從多個表格中提取資料。

Snowflake Scripting 語言

Snowflake 提供了一種名為 Snowflake Scripting 的語言，允許使用者宣告變數、使用迴圈和條件陳述式（如 if-then-else），以及偵測和處理例外。這種語言結合了 SQL 的資料操作陳述式（如 SELECT 和 INSERT）和傳統程式式語言的控制結構，將在後續章節中詳細介紹。

建立範例資料函式庫

Snowflake 提供了多個範例資料函式庫，供使用者熟悉其 SQL 實作。其中一個範例資料函式庫 TPCH_SF1 被廣泛用於本文的範例中。由於 TPCH_SF1 資料函式庫規模較大（包含超過 870 萬行資料），本文採用了一個較小的子集（約 330,000 行）來進行示範。

範例資料函式庫的結構

範例資料函式庫包含八個表格，涉及客戶訂單、零件供應商等資訊，模擬了一個銷售汽車零件的公司場景。附錄 A 提供了這些表格的視覺化表示及其之間的關聯。

設定 Snowflake 帳戶

Snowflake 是一種 SaaS（軟體即服務）解決方案，無需在本機安裝任何軟體。使用者只需透過網頁瀏覽器即可與 Snowflake 互動。以下是建立 Snowflake 帳戶的步驟：

  1. 前往 www.snowflake.com 並點選頁面右上角的「START FOR FREE」按鈕。
  2. 填寫基本資訊（姓名、電子郵件、公司名稱、職位和國家）並點選「CONTINUE」。
  3. 選擇 Standard 版本和雲端服務供應商，並同意相關條款後點選「GET STARTED」。
  4. 透過電子郵件啟用帳戶。
  5. 設定使用者名稱和密碼，並登入帳戶。

Snowflake 提供 30 天的免費試用期，之後需要提供信用卡資訊以繼續使用。使用者可以透過「Admin > Usage」頁面監控使用成本。

降低成本的建議

  1. 選擇適當的虛擬倉函式庫大小：在處理範例資料函式庫時，使用 X-Small 虛擬倉函式庫以節省資源。
  2. 調整自動暫停時間：將虛擬倉函式庫的自動暫停時間設定為較短的時間（例如 3 至 4 分鐘），以避免不必要的成本。

建立範例資料函式庫的步驟

  1. 建立 Worksheet：在 Snowsight 中，Worksheet 是與資料函式庫互動的主要介面。建立一個名為 Learning_Snowflake_SQL 的 Worksheet。
  2. 建立資料函式庫：在 Worksheet 中執行 CREATE DATABASE learning_sql; 命令，以建立範例資料函式庫。

程式碼範例：建立資料函式庫

CREATE DATABASE learning_sql;

內容解密：

此 SQL 陳述式用於建立一個名為 learning_sql 的新資料函式庫。當執行此命令時，Snowflake 會建立一個新的資料函式庫，並自動在其中建立一個名為 Public 的預設 Schema。這個 Schema 將用於存放範例資料函式庫中的表格。

資料函式庫建置與資料載入

在開始使用Snowflake進行SQL學習之前，您需要建立一個包含範例資料的資料函式庫。以下提供了兩種方法來建立這個資料函式庫。

方法一：從TPCH_SF1複製資料

首先，您需要檢查Snowflake提供的範例資料函式庫是否可用。具體步驟如下：

  1. 檢查範例資料函式庫

    • 導航至「Data > Databases」選單，檢視可用的資料函式庫列表。
    • 如果在SNOWFLAKE_SAMPLE_DATA資料函式庫下看到TPCH_SF1，則可以繼續下一步。

  2. 建立並填充表格

    • 在執行建立表格的命令之前，需要指定您要使用的資料函式庫和schema。
      USE DATABASE LEARNING_SQL;
USE SCHEMA PUBLIC;
    • 然後，執行以下命令來建立表格並從TPCH_SF1複製資料：
      CREATE TABLE REGION AS SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION;
CREATE TABLE NATION AS SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION;
CREATE TABLE PART AS 
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.PART 
WHERE MOD(P_PARTKEY, 50) = 8;
CREATE TABLE PARTSUPP AS 
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.PARTSUPP 
WHERE MOD(PS_PARTKEY, 50) = 8;
CREATE TABLE SUPPLIER AS 
WITH SP AS (SELECT DISTINCT PS_SUPPKEY FROM PARTSUPP)
SELECT S.* FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.SUPPLIER S
INNER JOIN SP ON S.S_SUPPKEY = SP.PS_SUPPKEY;
CREATE TABLE LINEITEM AS 
SELECT L.* FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM L
INNER JOIN PART P ON P.P_PARTKEY = L.L_PARTKEY;
CREATE TABLE ORDERS AS 
WITH LI AS (SELECT DISTINCT L_ORDERKEY FROM LINEITEM)
SELECT O.* FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS O
INNER JOIN LI ON O.O_ORDERKEY = LI.L_ORDERKEY;
CREATE TABLE CUSTOMER AS 
WITH O AS (SELECT DISTINCT O_CUSTKEY FROM ORDERS)
SELECT C.* FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER C
INNER JOIN O ON C.C_CUSTKEY = O.O_CUSTKEY;

程式碼解析：

  • 以上SQL命令首先指定了要使用的資料函式庫和schema。
  • 接著，為每個表格執行CREATE TABLE命令，並從TPCH_SF1範例資料函式庫中選擇特定的資料進行填充。
  • 使用了CTE（Common Table Expressions）和內連線（INNER JOIN）來篩選和關聯資料。

方法二：從GitHub檔案載入資料

如果TPCH_SF1範例資料函式庫不可用，您可以從GitHub下載CSV檔案並載入到您的資料函式庫中。

  1. 建立表格

    • 從GitHub下載Learning_Snowflake_SQL_Schema.sql檔案並執行其中的CREATE TABLE陳述式。

  2. 載入資料

    • 對每個表格，使用Snowflake的「Load Data」功能，從GitHub下載的CSV檔案中載入資料。
    • 具體步驟包括：
      1. 在「Database > LEARNING_SQL > PUBLIC > Tables」下找到目標表格。
      2. 點選「Load Table」選單選項。
      3. 瀏覽並選擇相應的CSV檔案。
      4. 選擇「Delimited Files (CSV or TSV)」作為檔案格式。
      5. 完成載入過程。

資料載入圖解說明

此圖示說明瞭如何使用Snowflake的介面載入CSV檔案到指定的表格中。

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title Snowflake SQL 資料函式庫查詢

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

圖表翻譯： 此圖展示了使用Snowflake介面將CSV檔案載入到指定表格的步驟。首先選擇目標表格，接著點選「Load Table」，然後瀏覽並選擇對應的CSV檔案，並指設定檔案格式，最後完成資料載入。

本文使用的排版慣例

本文採用以下排版慣例：

  • 斜體字：表示新術語、URLs、電子郵件地址、檔名和檔案副檔名。
  • 固定寬度字型：用於程式列表，以及段落中參照程式元素，如變數或函式名稱、資料函式庫、資料型別、環境變數、陳述式和關鍵字。
  • 固定寬度粗體字：顯示應該由使用者逐字輸入的命令或其他文字，以及當前討論中特別感興趣的程式碼。
  • <固定寬度斜體字或尖括號中的固定寬度字>：表示應該由使用者提供的數值或由上下文決定的數值所取代的文字。

內容解密：

本文的排版慣例旨在提高可讀性，並區分不同型別的內容。例如，固定寬度字型用於顯示程式碼，使得讀者能夠輕易識別程式元素。透過這些排版慣例，讀者可以更有效地理解書中的內容。

Snowflake SQL 查詢入門

Snowflake SQL 是用於查詢和管理 Snowflake 雲端資料倉儲的 SQL 語言。讓我們深入瞭解如何使用 SELECT 陳述式來執行查詢。

查詢基礎

如果您已按照前言中的步驟進行操作，那麼您應該已經建立了 Snowflake 帳戶並建立了範例資料函式庫。要使用 Snowsight 執行查詢，請在螢幕左側選擇「Worksheets」按鈕，然後點選右上角的「+」按鈕。您的新工作表將出現在左側面板中，名稱使用目前的日期和時間，但您可以將其重新命名為 Learning_Snowflake_SQL 以便用於本文中的範例。

設定 Schema

在執行任何查詢之前，請將 schema 設定為 Learning_SQL 資料函式庫中的 Public schema：

use schema learning_sql.public;

輸入此命令後，點選螢幕右上角的箭頭以執行命令，如圖 1-1 所示。

每次登入 Snowsight 時，您都需要執行此 use schema 陳述式，然後再對範例資料函式庫執行查詢。

Snowflake 網頁介面與 SnowSQL CLI 的比較

本文中的大多數範例將使用 Snowflake 的 CLI（稱為 SnowSQL），而不是 Snowsight 網頁介面。這樣可以將範例中的 SQL 陳述式從書中複製出來（假設您正在使用線上副本），並消除不必要的雜亂。

使用 SnowSQL 的注意事項

使用 SnowSQL 時，請始終在 SQL 陳述式的末尾新增分號，然後按 Enter 鍵執行陳述式。此外，除了使用 use schema 命令設定目前的 schema 外，我還使用以下陳述式設定 SnowSQL 中的提示：

!set prompt_format=[schema]>;

這就是為什麼我的所有範例都會顯示提示 PUBLIC>，但您可以根據自己的喜好使用預設的提示格式。

程式碼範例的使用

本文提供的程式碼範例可以在 GitHub 上下載。您可以將這些程式碼範例用於您的程式和檔案中，但如果您要重製大量的程式碼，則需要獲得我們的許可。

程式碼範例的使用規範

  • 您可以在程式和檔案中使用本文提供的程式碼範例，無需獲得我們的許可，除非您要重製大量的程式碼。
  • 如果您要撰寫一個使用本文中多個程式碼片段的程式，則無需獲得許可。
  • 如果您要參照本文並在回答問題時參照範例程式碼，則無需獲得許可。
  • 如果您要將本文中的大量範例程式碼納入您的產品檔案中，則需要獲得許可。

內容解密：

此處的內容主要介紹了 Snowflake SQL 的查詢基礎和相關工具的使用，包括 Snowsight 和 SnowSQL CLI。同時，也提到了程式碼範例的使用規範和相關注意事項。這些資訊對於初學者瞭解 Snowflake SQL 的基本操作和相關工具的使用非常有幫助。

Snowflake 資料函式庫查詢基礎

Snowflake 是一種強大的雲端資料倉儲解決方案，提供了靈活且高效的資料查詢與分析能力。本章節將介紹 Snowflake 查詢的基本概念，包括查詢語法、結果集處理及各種查詢子句的使用方法。

簡單查詢範例

首先，讓我們從一個簡單的查詢開始，傳回目前日期：

PUBLIC>select current_date;
+
---
-
---
-
---
---
+
| CURRENT_DATE |
|
---
-
---
-
---
---
|
| 2023-03-19   |
+
---
-
---
-
---
---
+

這個查詢呼叫了內建函式 current_date()，預設傳回 YYYY-MM-DD 格式（4 位年份、2 位月份、2 位日期）的日期。select 陳述式傳回一個結果集，該結果集由一行或多行資料組成，每行包含一或多個欄位。在這個範例中，結果集包含一行資料和一個欄位。

內容解密：

  1. current_date() 函式用於取得目前日期。
  2. 結果集的格式由 Snowflake 自動產生，包括欄位名稱和邊框。
  3. 結果集的概念與資料函式庫表格類別似，都是由行和欄位組成。

查詢資料表

接下來，讓我們查詢範例資料函式庫中最小的表格 Region：

PUBLIC>select *
from region;
+
---
-
---
-
---
--+
---
-
---
-
---
--+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-+
| R_REGIONKEY | R_NAME      | R_COMMENT                          |
|
---
-
---
-
---
--+
---
-
---
-
---
--|
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-+
| 0           | AFRICA      | lar deposits. blithely final pac...|
| 1           | AMERICA     | hs use ironic, even requests. s    |
| 2           | ASIA        | ges. thinly even pinto beans ca    |
| 3           | EUROPE      | ly final courts cajole furiously...|
| 4           | MIDDLE EAST | uickly special accounts cajole c...|
+
---
-
---
-
---
--+
---
-
---
-
---
--+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-+

內容解密：

  1. select * 表示傳回所有欄位。
  2. from 子句指定了要檢索的資料表。
  3. Region 表格包含 5 行資料和 3 個欄位。
  4. 第三欄的註解是隨機產生的，這是範例資料函式庫的特點。

資料表結構描述

若要查看錶格中的可用欄位，可以使用 Snowflake 的 describe 陳述式：

PUBLIC>describe table region;
+
---
-
---
-
---
--+
---
-
---
-
---
---
+
---
-
---
-+
---
-
---
+
---
-
---
--+...
| name        | type         | kind   | null? | default |...
|
---
-
---
-
---
--+
---
-
---
-
---
---
+
---
-
---
-+
---
-
---
+
---
-
---
--+...
| R_REGIONKEY | NUMBER(38,0) | COLUMN | N     | NULL    |...
| R_NAME      | VARCHAR(25)  | COLUMN | N     | NULL    |...
| R_COMMENT   | VARCHAR(152) | COLUMN | Y     | NULL    |...
+
---
-
---
-
---
--+
---
-
---
-
---
---
+
---
-
---
-+
---
-
---
+
---
-
---
--+...

內容解密：

  1. R_REGIONKEY 是數值型別欄位，用於儲存唯一數值。
  2. R_NAME 和 R_COMMENT 是字元型別欄位，分別儲存地區名稱和註解。
  3. VARCHAR 表示可變長度的字元欄位。

命令引數

許多 Snowflake 的內建命令，如 show tables，都包含多個可選引數來影響命令的執行方式。例如，使用 terse 引數可以限制傳回的欄位數量：

PUBLIC>show terse tables in PUBLIC;
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
+
---
-
---
---
+
---
-
---
+
| created_on                    | name     | kind  |
|
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
+
---
-
---
---
+
---
-
---
+
| 2023-02-28 06:55:21.382 -0800 | CUSTOMER | TABLE |
| ...                           | ...      | ...   |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
+
---
-
---
---
+
---
-
---
+

內容解密：

  1. show tables 命令用於顯示目前資料函式庫中的表格資訊。
  2. terse 引數限制了傳回的欄位數量，使結果更加簡潔。

查詢子句

Snowflake 查詢由多個子句組成。除了前面介紹的 select 和 from 子句外，還有其他六種子句可用，如下表所示：

| 子句名稱 | 功能說明 | |

-|

| | select | 指定結果集中包含的欄位 | | from | 指定要檢索的資料表及表格之間的連線方式 | | where | 根據條件過濾結果集中的行 | | group by | 將結果集中的行按照共同的值分組 | | having | 根據分組後的結果過濾結果集 | | qualify | 根據視窗函式的結果過濾結果集（見第14章） | | order by | 對結果集按照一或多個欄位進行排序 | | limit | 限制結果集中的行數 |

在這些子句中，只有 select 是必需的。某些子句需要與其他子句搭配使用，例如，having 子句必須與 group by 子句一起使用。後續章節將詳細介紹這些子句的使用方法。

玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。