Skip to content

Data Science › Data Engineering › 雲端資料倉儲

雲端資料倉儲 (Cloud Data Warehouses) ​

Snowflake、BigQuery、Redshift 都是受管、欄式、storage 與 compute(程度不一)分離的分析型倉儲。先看比較表抓差異,再依需要讀各平台細節。SQL 語法差異見 SQL 方言速查。

比較表 ​

面向SnowflakeBigQueryRedshift
雲端多雲(AWS / Azure / GCP)GCPAWS
Compute 模型virtual warehouse(各自 sizing、隔離 workload,按運作時間計費)serverless slot;on-demand 依掃描量計費,或購買保留容量provisioned cluster 或 Serverless
物件層級account → database → schema → tableproject → dataset → tablecluster → database → schema → table
減少掃描自動 micro-partition、clustering keypartition + clusterDISTKEY(跨節點分布)+ SORTKEY(節點內排序)
載入stage + COPY INTObq load、LOAD DATACOPY(從 S3)、Spectrum external schema
除錯入口Query Profile、Query / Copy Historyexecution details、bytes processed、--dry_runsystem tables(SVV_*、STL_*)
常見陷阱warehouse 沒設 auto-suspend、role 設計region 建立後不能改;沒用 partition filter 很貴;LIMIT 不減少掃描量data skew、SELECT *、distribution style 選錯

共通原則:欄式儲存下只選需要的欄;先用 partition / cluster / sort 讓查詢少掃資料;查詢慢時先看 compute 隔離與資料排列,而不是只改 SQL。

Snowflake ​

Snowflake 是受管的雲端資料倉儲,把雲端服務、查詢運算、儲存三層分開。不需要自己準備硬體或軟體,團隊可以把心力放在資料模型、權限、效能與擷取。

層角色
Cloud services協調使用者活動、認證、metadata、查詢最佳化
Query processingvirtual warehouse:MPP(大規模平行處理),把運算分散到多個節點
Database storage欄式格式,自動切成 micro-partitions
連線方式用途
Web UI(Snowsight)查詢、管理、監控
ODBC / JDBCBI 工具、Java 生態
Python connector、Snowpark、Spark connector程式與 notebook
SnowSQL / Snowflake CLI命令列、腳本化與排錯

Virtual Warehouse ​

virtual warehouse 是 Snowflake 的運算單位(查詢與載入都用它),與資料本身分開。另建 warehouse 的常見理由:依角色控管存取、分開 engineer 與 analyst 的 workload、慢查詢需要更大的運算、不同團隊要不同的成本配置。

sql
CREATE WAREHOUSE analytics_wh
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60          -- 閒置 60 秒自動暫停,避免空轉計費
  AUTO_RESUME = TRUE;

效能 ​

概念意義實務
欄式儲存只讀查詢用到的欄避免 SELECT *
Micro-partitions自動管理的小儲存分區,記錄各欄值範圍篩選條件能有效縮小範圍時,只讀需要的分區 (pruning)
Clustering key大表依常用篩選欄重新排列只對很大且常依同欄篩選的表設定
Query Profile查詢各步驟的時間與資源判斷問題在掃描量、計算量還是 warehouse 配置

角色與存取 ​

系統角色用途
ACCOUNTADMIN帳號層級(帳務等),應極少使用
SECURITYADMIN管理安全相關物件與授權
USERADMIN管理使用者與角色
SYSADMIN建立與管理 warehouse、database 等物件
PUBLIC所有使用者都有,只能用授權給它的物件

治理不只是表權限:account、user、role、warehouse 要一起設計;監控資訊通常只開放給高權限角色。

載入與監控 ​

text
從雲端儲存準備檔案 → 建立 stage 並設定權限(storage integration)→ COPY INTO 載入 → 檢查 copy history
sql
CREATE STAGE raw_stage URL = 's3://lab-bucket/raw/' STORAGE_INTEGRATION = s3_int;
COPY INTO lab.results FROM @raw_stage FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);
歷史內容保留
Query History過去執行的 SQL,用來找慢查詢與重現問題UI 約 14 天;SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY 約 1 年
Copy History載入的檔案、筆數、成功 / 失敗表層級檢視較即時;ACCOUNT_USAGE 檢視有延遲但保留較久

Snowflake 也提供 Time Travel(預設 1 天,企業版可延長)與 Marketplace / Data Sharing(直接取用其他帳號分享的資料,不必複製)。

BigQuery ​

BigQuery 是 Google Cloud 的 serverless 分析倉儲:沒有要管理的伺服器,compute 與 storage 分離,適合 OLAP,不適合用交易資料庫的心智理解。

層級角色
Project計費、權限、使用者的邊界
Datasetproject 內的邏輯容器,綁定 region
Table實際資料
sql
SELECT * FROM `project.dataset.table`;            -- 完整名稱;跨 project 查詢也用這個形式

主要 SQL 方言是 GoogleSQL;入口有 BigQuery Studio、bq CLI 與各語言 client。Google 也在 bigquery-public-data 提供公開資料集(含 gnomAD 等基因體資料),可直接查詢。

Region ​

限制影響
dataset 建立後不能改 region一開始就要選對(法規、延遲、資料所在)
一個查詢不能直接 join 不同 region 的資料跨區整合要先複製資料

載入 ​

bash
bq load --source_format=CSV --autodetect dataset.table gs://mybucket/mydata.csv
sql
LOAD DATA INTO dataset.table
FROM FILES (uris = ['gs://mybucket/mydata.csv'], format = 'CSV', skip_leading_rows = 1);

成本與效能 ​

做法理由
只選需要的欄on-demand 依掃描的欄位資料量計費
LIMIT 不會省錢仍掃描整欄;預覽資料用表格的 Preview
先 bq query --dry_run 或看編輯器的預估量執行前知道會掃多少
依日期 partition、常用篩選欄 clusterWHERE 帶 partition 條件才會只掃需要的分區
對大表設 require_partition_filter強制查詢必須帶 partition 條件
可接受誤差時用近似聚合APPROX_COUNT_DISTINCT、APPROX_QUANTILES、APPROX_TOP_SUM
DML 合併成少數大批支援 INSERT / UPDATE / DELETE / MERGE / CREATE TABLE AS,但不適合大量單筆寫入;UPDATE 必須有 WHERE

Redshift ​

Amazon Redshift 是 AWS 上的分散式欄式倉儲。語法源自 PostgreSQL(8.0.2)並加上自己的擴充,但效能心智完全不同:資料分散在多個節點,表設計直接決定 join 與聚合要搬多少資料。有 Serverless 與 provisioned cluster 兩種部署,常與 S3、RDS / Aurora、EMR、Kinesis 一起使用,並可透過 external schema(Spectrum)直接查 S3 上的資料。

資料分布 (Distribution Style) ​

類型做法適合
ALL整張表複製到每個節點小型 lookup / dimension 表
KEY依 DISTKEY 欄位的值分布常以同一個 key join 或聚合的大表
EVEN輪流分配到各節點沒有明顯 join key 的大表
AUTORedshift 依表大小自動選擇預設

兩張大表以各自的 DISTKEY join 時,相同 key 已在同一節點,不必跨節點搬資料。DISTKEY 選到值分布極不均勻的欄位會造成 data skew:少數節點做大部分工作。

排序 (SORTKEY) ​

SORTKEY 決定節點內資料的排序,讓 WHERE 範圍篩選、JOIN、GROUP BY、ORDER BY 可以跳過不需要的區塊。compound sort key 的欄位順序有意義:篩選時要用到前面的欄位才有效。

sql
CREATE TABLE fact_results (
  sample_id VARCHAR(32), test_code VARCHAR(16), result_date DATE, value DOUBLE PRECISION
)
DISTKEY (sample_id)
COMPOUND SORTKEY (result_date, test_code);

查詢最佳化 ​

做法理由
不要 SELECT *欄式儲存,多一欄就多一份 I/O
篩選條件貼近 join、用到 DISTKEY / SORTKEY 欄位讓過濾推到正確節點與資料範圍
關鍵 predicate 不要包函式(WHERE date_trunc('day', ts) = ...)無法利用排序跳過區塊

系統檢視 ​

檢視用途
SVV_TABLE_INFO表的 distribution style、sort key、skew(skew_rows)、大小
STL_ALERT_EVENT_LOG查詢觸發的效能警示與建議
SVV_ALL_SCHEMAS、SVV_ALL_TABLES、SVV_ALL_COLUMNS盤點 internal 與 external 物件
SVV_REDSHIFT_SCHEMAS、SVV_REDSHIFT_TABLES只看 Redshift 內部物件

Redshift 的效能從 DDL 就開始:查詢慢時,先看 distribution style、sort order、skew 與 alert log,再改 SQL。