前言

本文翻譯自 Altinity 針對 ClickHouse 的系列技術文章。面向聯機分析處理（OLAP）的開源分析引擎 ClickHouse，因其優良的查詢性能，PB 級的資料規模，簡單的架構，被國内外公司廣泛采用。

阿裡雲 EMR-OLAP 團隊，基于開源 ClickHouse 進行了系列優化，提供了開源 OLAP 分析引擎 ClickHouse 的雲上托管服務。EMR ClickHouse 完全相容開源版本的産品特性，同時提供叢集快速部署、叢集管理、擴容、縮容和監控告警等雲上産品功能，并且在開源的基礎上優化了 ClickHouse 的讀寫性能，提升了 ClickHouse 與 EMR 其他元件快速內建的能力。通路

https://help.aliyun.com/document_detail/212195.html

了解詳情。

譯者：何源（荊杭），阿裡雲計算平台事業部進階産品專家

（圖源Altinity，侵删）

在 ClickHouse 物化視圖中使用 Join

CREATE MATERIALIZED VIEW download_daily_mv
TO download_daily AS
SELECT
  day AS day, userid AS userid, count() AS downloads,
  sum(gb) as total_gb, sum(price) as total_price
FROM (
  SELECT
    toDate(when) AS day,
    userid AS userid,
    download.bytes / (1024*1024*1024) AS gb,
    gb * price.price_per_gb AS price
  FROM download LEFT JOIN price ON download.userid = price.userid
)
GROUP BY userid, day

我們現在可以通過加載資料來測試視圖。我們首先加載帶有使用者名和價格資訊的兩個次元表。

INSERT INTO price VALUES (25, 0.10), (26, 0.05), (27, 0.01);
INSERT INTO user VALUES (25, 'Bob'), (26, 'Sue'), (27, 'Sam');

接下來，我們将示例樣本資料添加到 download 事實表中。下面的 INSERT 添加了 5000 行，按 user 表中列出的 userid 值均勻分布。

INSERT INTO download
  WITH
    (SELECT groupArray(userid) FROM user) AS user_ids
  SELECT
    now() + number * 60 AS when,
    user_ids[(number % length(user_ids)) + 1] AS user_id,
    rand() % 100000000 AS bytes
  FROM system.numbers
  LIMIT 5000

此時我們可以看到，物化視圖将資料填充到 download_daily 中。下面是一個示例查詢。

SELECT day, downloads, total_gb, total_price
FROM download_daily WHERE userid = 25
┌────────day─┬─downloads─┬───────────total_gb─┬────────total_price─┐
│ 2020-07-14 │       108 │  5.054316438734531 │ 0.5054316438734532 │
│ 2020-07-15 │       480 │  22.81532768998295 │  2.281532768998296 │
│ 2020-07-16 │       480 │  21.07045224122703 │  2.107045224122702 │
│ 2020-07-17 │       480 │ 21.606687822379172 │ 2.1606687822379183 │
│ 2020-07-18 │       119 │  5.548438269644976 │ 0.5548438269644972 │
└────────────┴───────────┴────────────────────┴────────────────────┘

目前還不錯。但我們還能更進一步。我們首先看看 ClickHouse 背後的原理。

要有效地使用物化視圖，了解其背後的原理是大有幫助的。物化視圖作為後插入觸發器對單個表運作。如果物化視圖定義中的查詢包括 Join，那麼來源表就是 Join 中的左側表。

在我們的示例中，download 是左側表。是以，任何對 download 的插入都會導緻一個分片被寫入 download_daily。盡管将值添加到了 Join 中，但對 user 的插入沒有效果。

如果我們建立一個更有趣的物化視圖，就很容易展示這種行為。讓我們定義一個對 user 表進行右側外部聯接的視圖。在這種情況下，我們将使用一個簡單的 MergeTree 表，這樣我們就可以看到所有生成的行，而不用像 SummingMergeTree 那樣進行合并。下面是一個簡單的目标表，後面是一個物化視圖，它将從 download 表填充目标表。

CREATE TABLE download_right_outer (
  when DateTime,
  userid UInt32,
  name String,
  bytes UInt64
) ENGINE=MergeTree
PARTITION BY toYYYYMM(when)
ORDER BY (when, userid)
CREATE MATERIALIZED VIEW download_right_outer_mv
TO download_right_outer
AS SELECT
  when AS when,
  user.userid AS userid,
  user.name AS name,
  bytes AS bytes
FROM download RIGHT OUTER JOIN user ON (download.userid = user.userid)

當我們在 download 表中插入一行時會發生什麼？物化視圖為 user 表中的每次插入*以及*任何不比對的行生成一行，因為我們進行的是右側外部聯接。（你可能已經注意到了，這個視圖也有一個潛在缺陷。我們很快就會處理這個問題。）

INSERT INTO download VALUES (now(), 26, 555)
SELECT * FROM download_right_outer
┌────────────────when─┬─userid─┬─name─┬─bytes─┐
│ 2020-07-12 17:27:35 │     26 │ Sue  │   555 │
└─────────────────────┴────────┴──────┴───────┘
┌────────────────when─┬─userid─┬─name─┬─bytes─┐
│ 0000-00-00 00:00:00 │     25 │ Bob  │     0 │
│ 0000-00-00 00:00:00 │     27 │ Sam  │     0 │
└─────────────────────┴────────┴──────┴───────┘

另一方面，如果你在 user 表中插入一行，物化視圖中不會發生任何變化。

INSERT INTO user VALUES (28, 'Kate')
SELECT * FROM download_right_outer
┌────────────────when─┬─userid─┬─name─┬─bytes─┐
│ 2020-07-12 17:27:35 │     26 │ Sue  │   555 │
└─────────────────────┴────────┴──────┴───────┘
┌────────────────when─┬─userid─┬─name─┬─bytes─┐
│ 0000-00-00 00:00:00 │     25 │ Bob  │     0 │
│ 0000-00-00 00:00:00 │     27 │ Sam  │     0 │
└─────────────────────┴────────┴──────┴───────┘

隻有當你向 download 表添加更多的行時，才會看到新使用者行的效果。

對多個表 Join

像 SELECT 語句一樣，物化視圖可以對多個表 Join。在第一個示例中，我們 Join 了下載下傳價格（因 userid 而異）。現在我們來 Join 第二個 user 表，該表将 userid 映射到一個 username。在這個示例中，我們将添加一個新的目标表，其中添加了 username 列。由于 username 不是聚合，我們也将其添加到 ORDER BY。這将防止 SummingMergeTree 引擎嘗試聚合它。

CREATE TABLE download_daily_with_name (
  day Date,
  userid UInt32,
  username String,
  downloads UInt32,
  total_gb Float64,
  total_price Float64
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(day) ORDER BY (userid, day, username)

現在我們來定義物化視圖，它以簡單直接的方式擴充了第一個示例的 SELECT。

CREATE MATERIALIZED VIEW download_daily_with_name_mv
TO download_daily_with_name AS
SELECT
  day AS day, userid AS userid, user.name AS username,
  count() AS downloads, sum(gb) as total_gb, sum(price) as total_price
FROM (
  SELECT
    toDate(when) AS day,
    userid AS userid,
    download.bytes / (1024*1024*1024) AS gb,
    gb * price.price_per_gb AS price
  FROM download LEFT JOIN price ON download.userid = price.userid
) AS join1
LEFT JOIN user ON join1.userid = user.userid
GROUP BY userid, day, username

你可以截斷 download 表并重新加載資料，以此測試新視圖。這将留給讀者作為練習。

ClickHouse SELECT 語句支援廣泛的 Join 類型，這為物化視圖所實作的轉換提供了很大的靈活性。靈活性可能是把雙刃劍，因為它創造了更多的機會，有可能産生并非預期的結果。

例如，如果你在 download 中插入一條 userid 30 的行，會發生什麼？這個 userid 在 user 表或 price 表中都不存在。

INSERT INTO download VALUES (now(), 30, 222)

簡而言之：如果你不仔細定義物化視圖，該行可能不會出現在目标表中。為了確定比對，你必須進行 LEFT OUTER JOIN 或者 FULL OUTER JOIN。這是有道理的，因為這和運作 SELECT 本身産生的行為是一樣的。download_right_outer_mv 示例正是存在如上所述的問題。

視圖定義也會産生不易察覺的文法錯誤。例如，遺漏 GROUP BY 項會導緻令人費解的失敗。下面是一個簡單示例。

CREATE MATERIALIZED VIEW download_daily_join_old_style_mv
ENGINE = SummingMergeTree PARTITION BY toYYYYMM(day)
ORDER BY (userid, day) POPULATE AS SELECT 
    toDate(when) AS day, 
    download.userid AS userid, 
    user.username AS name, 
    count() AS downloads, 
    sum(bytes) AS bytes
FROM download INNER JOIN user ON download.userid = user.userid
GROUP BY userid, day  -- Column `username` is missing!
Received exception from server (version 20.3.8):
Code: 10.DB::Exception: Received from localhost:9000.DB::Exception: Not found column name in block. There are only columns: userid, toStartOfDay(when), count(), sum(bytes).

哪兒出問題了？username 列中遺漏了 GROUP BY。ClickHouse 拒絕視圖定義的做法是合理的，但報錯資訊有點難以解讀。

最後，當列在聯接的表之間重疊時，務必要仔細指定列。下面是與上文的 RIGHT OUTER JOIN 示例略有不同的版本。

CREATE MATERIALIZED VIEW download_right_outer_mv
TO download_right_outer
AS SELECT
  when AS when,
  userid,  
  user.name AS name,
  bytes AS bytes
FROM download RIGHT OUTER JOIN user ON (download.userid = user.userid)

當你在 download 中插入行時，你會得到如下的結果，其中 userid 已從不比對的行中删除。

SELECT * FROM download_right_outer
┌────────────────when─┬─userid─┬─name─┬─bytes─┐
│ 0000-00-00 00:00:00 │      0 │ Sue  │     0 │
│ 0000-00-00 00:00:00 │      0 │ Sam  │     0 │
└─────────────────────┴────────┴──────┴───────┘
┌────────────────when─┬─userid─┬─name─┬─bytes─┐
│ 2020-07-12 18:04:56 │     25 │ Bob  │   222 │
└─────────────────────┴────────┴──────┴───────┘

在這種情況下，ClickHouse 似乎輸入了預設值，而不是從 user.userid 配置設定值。你必須明确地命名列值并且使用 AS userid 來配置設定名稱。如果你單獨運作 SELECT 查詢，是達不到這種效果的。這種行為看起來像一個缺陷。

物化視圖是 ClickHouse 使用者可用的最通用的功能之一。物化視圖是由一個 SELECT 語句填充的，該 SELECT 可以 Join 多個表。要了解的關鍵是，ClickHouse 僅觸發 Join 中最左側的表。其他表可提供用于轉換的資料，但是視圖不會對這些表上的插入做出反應。

Join 帶來了新的靈活性，但也可能導緻意料之外的結果。是以，最好仔細測試物化視圖，尤其是存在 Join 時。

您已經了解了在 ClickHouse 中處理實時更新相關内容，本系列還包括其他内容：

在 ClickHouse 中處理實時更新
使用新的 TTL move，将資料存儲在合适的地方
在 ClickHouse 物化視圖中使用 Join（本文）
ClickHouse 聚合函數和聚合狀态
ClickHouse 中的嵌套資料結構

原文連結：

https://altinity.com/blog/2020-07-14-joins-in-clickhouse-materialized-views

擷取更多 EMR ClickHouse 相關資訊，可檢視産品文檔：

釘釘掃描下方二維碼加入産品交流群一起參與讨論~

【ClickHouse 技術系列】- 在 ClickHouse 物化視圖中使用 Join前言在 ClickHouse 物化視圖中使用 Join

前言

在 ClickHouse 物化視圖中使用 Join

目錄

對多個表 Join

繼續閱讀

Flash AS3 連續加載外部若幹圖檔

DB2表壓縮功能

華為筆試軟體

項目管理那些事兒

OS --written test1

OS-written test2

壓縮編碼M-JPEG、MPEG4、H.264

虛拟主機會影響到SEO嗎

轉詳解C#資料庫存取圖檔三大方式

解讀2008年網絡技術熱詞之雲計算

《eWEEK》：09年5大科技發展趨勢雲計算居首

BMP檔案結構及圖像每行位元組計算方法

磁盤結構及在Linux中的命名

雲計算面試題及答案，雲計算主要就業崗位

雲計算面試題——mysql/存儲引擎/備份

雲計算面試題——檔案/權限/分區/軟體包管理