面向列式数据库的分析工作流

NoSQL 入门

Jake Roach

Data Engineer

在 Snowflake 中使用公用表表达式

公用表表达式（CTE）：

使用 WITH 定义的命名子查询/临时表
创建可后续查询的对象
减少被查询或参与 JOIN 的数据量
更模块化，便于排错

WITH <cte-name> AS (
    SELECT
        ....
    FROM <table-name>
    [JOIN | WHERE | ...]
)

SELECT
    ...
FROM <cte-name>;

¹ https://docs.snowflake.com/en/user-guide/queries-cte

编写公用表表达式

WITH premium_books AS (
    SELECT
        title,
        author,
        avg_reviews
    FROM books
    WHERE price > 25.00
)
SELECT
    author,
    MIN(avg_reviews) AS min_avg_reviews,
    MAX(avg_reviews) AS max_avg_reviews
FROM premium_books
GROUP BY author;

创建 premium_books 临时对象
向下游复用 premium_books

可创建多个临时对象：

WITH 
    <first-name> AS (...),
    <second-name> AS (...),
    ...
...
;

Snowflake 中的视图

视图：

使查询结果可像表一样访问
分为非物化与物化

CREATE VIEW <view-name> AS
  SELECT
      ...
  FROM <table-name>
  [WHERE | JOIN | ...];

在 Snowflake 中创建视图

CREATE VIEW premium_books AS
    SELECT
        title,
        author,
        avg_reviews
    FROM books
    WHERE price >= 25.00;

SELECT * FROM premium_books;

在调用 premium_books 时执行查询
查询的"命名定义"

CREATE MATERIALIZED VIEW premium_books AS
    SELECT
        title,
        author,
        avg_reviews
    FROM books
    WHERE price >= 25.00;

SELECT * FROM premium_books;

首次执行后结果被存储
查询更快，但需刷新

Passons à la pratique !

NoSQL 入门