Citus 分散式 PostgreSQL 叢集 - SQL Reference(攝取、修改資料 DML)

插入資料

要將資料插入分散式表，您可以使用標準 PostgreSQL INSERT 命令。例如，我們從 Github 存檔資料集中隨機選擇兩行。

• INSERT
  • http://www.postgresql.org/docs/current/static/sql-insert.html

/*
CREATE TABLE github_events
(
  event_id bigint,
  event_type text,
  event_public boolean,
  repo_id bigint,
  payload jsonb,
  repo jsonb,
  actor jsonb,
  org jsonb,
  created_at timestamp
);
*/

INSERT INTO github_events VALUES (2489373118,'PublicEvent','t',24509048,'{}','{"id": 24509048, "url": "https://api.github.com/repos/SabinaS/csee6868", "name": "SabinaS/csee6868"}','{"id": 2955009, "url": "https://api.github.com/users/SabinaS", "login": "SabinaS", "avatar_url": "https://avatars.githubusercontent.com/u/2955009?", "gravatar_id": ""}',NULL,'2015-01-01 00:09:13');

INSERT INTO github_events VALUES (2489368389,'WatchEvent','t',28229924,'{"action": "started"}','{"id": 28229924, "url": "https://api.github.com/repos/inf0rmer/blanket", "name": "inf0rmer/blanket"}','{"id": 1405427, "url": "https://api.github.com/users/tategakibunko", "login": "tategakibunko", "avatar_url": "https://avatars.githubusercontent.com/u/1405427?", "gravatar_id": ""}',NULL,'2015-01-01 00:00:24');

向分散式表中插入行時，必須指定插入行的分佈列。根據分佈列，Citus 確定插入應該路由到的正確分片。 然後，查詢被轉發到正確的分片，並在該分片的所有副本上執行遠端插入命令。

有時將多個 insert 語句放在一個包含多行的單個 insert 中會很方便。 它也比重複資料庫查詢更有效。 例如，上一節中的示例可以像這樣一次性載入：

INSERT INTO github_events VALUES
  (
    2489373118,'PublicEvent','t',24509048,'{}','{"id": 24509048, "url": "https://api.github.com/repos/SabinaS/csee6868", "name": "SabinaS/csee6868"}','{"id": 2955009, "url": "https://api.github.com/users/SabinaS", "login": "SabinaS", "avatar_url": "https://avatars.githubusercontent.com/u/2955009?", "gravatar_id": ""}',NULL,'2015-01-01 00:09:13'
  ), (
    2489368389,'WatchEvent','t',28229924,'{"action": "started"}','{"id": 28229924, "url": "https://api.github.com/repos/inf0rmer/blanket", "name": "inf0rmer/blanket"}','{"id": 1405427, "url": "https://api.github.com/users/tategakibunko", "login": "tategakibunko", "avatar_url": "https://avatars.githubusercontent.com/u/1405427?", "gravatar_id": ""}',NULL,'2015-01-01 00:00:24'
  );

“From Select”子句（分散式彙總）

Citus 還支援 INSERT ... SELECT 語句 —— 根據選擇查詢的結果插入行。這是一種方便的填充表的方法，並且還允許使用 ON CONFLICT 子句進行“更新插入(upserts)”，這是進行分散式彙總的最簡單方法。

• 分散式彙總
  • https://docs.citusdata.com/en/v11.0-beta/develop/reference_dml.html#rollups

在 Citus 中，可以通過三種方式從 select 語句中插入。第一個是如果源表和目標表位於同一位置，並且 select/insert 語句都包含分佈列。在這種情況下，Citus 可以將 INSERT ... SELECT 語句下推以在所有節點上並行執行。

當 SELECT 查詢不需要協調器上的合併步驟時，可能會發生重新分割槽優化。它不適用於以下需要合併步驟的 SQL 功能：

• ORDER BY
• LIMIT
• OFFSET
• GROUP BY 當分佈列不是 group 鍵的一部分時
• 按源表中的非分佈列分割槽時的 Window(視窗)函式
• 非同位表之間的Join(連線)（即重新分割槽連線）

當源表和目標表沒有在同一位置，並且無法應用重新分割槽優化時，Citus 使用第三種方式執行 INSERT ... SELECT。 它從工作節點中選擇結果，並將資料拉到協調節點。協調器將行重定向回適當的分片。 因為所有資料都必須通過單個節點，所以這種方法效率不高。

如果對 Citus 使用哪種方法有疑問，請使用 EXPLAIN 命令，如 PostgreSQL 調優中所述。 當目標表的分片數量非常大時，禁用重新分割槽可能是明智之舉，
請參閱 citus.enable_repartitioned_insert_select (boolean)。

• PostgreSQL 調優
  • https://docs.citusdata.com/en/v11.0-beta/performance/performance_tuning.html#postgresql-tuning
• citus.enable_repartitioned_insert_select (boolean)
  • https://docs.citusdata.com/en/v11.0-beta/develop/api_guc.html#enable-repartitioned-insert-select

COPY 命令（批量載入）

要從檔案中批量載入資料，您可以直接使用 PostgreSQL 的 \COPY 命令。

首先通過執行下載我們的示例 github_events 資料集：

wget http://examples.citusdata.com/github_archive/github_events-2015-01-01-{0..5}.csv.gz
gzip -d github_events-2015-01-01-*.gz

然後，您可以使用 psql 複製資料（注意，此資料需要資料庫具有 UTF8 編碼）：

\COPY github_events FROM 'github_events-2015-01-01-0.csv' WITH (format CSV)

注意:

沒有跨分片的快照隔離的概念，這意味著與 COPY 併發執行的多分片 SELECT 可能會看到它在某些分片上提交，但在其他分片上沒有。
如果使用者正在儲存事件資料，他可能偶爾會觀察到最近資料中的小間隙。 如果這是一個問題，則由應用程式來處理（例如，從查詢中排除最新資料，或使用一些鎖）。

如果 COPY 未能開啟分片放置的連線，那麼它的行為方式與 INSERT 相同，即將放置標記為非活動，除非沒有更多活動的放置。 如果連線後發生任何其他故障，事務將回滾，因此不會更改後設資料。

使用匯總快取聚合

事件資料管道和實時儀表板等應用程式需要對大量資料進行亞秒級查詢。使這些查詢快速的一種方法是提前計算和儲存聚合。 這稱為“彙總”資料，它避免了在執行時處理原始資料的成本。 作為一個額外的好處，將時間序列資料彙總到每小時或每天的統計資料中也可以節省空間。 當不再需要其全部詳細資訊並且聚合足夠時，可能會刪除舊資料。

例如，這是一個通過 url 跟蹤頁面瀏覽量的分散式表：

CREATE TABLE page_views (
  site_id int,
  url text,
  host_ip inet,
  view_time timestamp default now(),

  PRIMARY KEY (site_id, url)
);

SELECT create_distributed_table('page_views', 'site_id');

一旦表中填充了資料，我們就可以執行聚合查詢來計算每個 URL 每天的頁面瀏覽量，限制到給定的站點和年份。

-- how many views per url per day on site 5?
SELECT view_time::date AS day, site_id, url, count(*) AS view_count
  FROM page_views
  WHERE site_id = 5 AND
    view_time >= date '2016-01-01' AND view_time < date '2017-01-01'
  GROUP BY view_time::date, site_id, url;

上述設定有效，但有兩個缺點。首先，當您重複執行聚合查詢時，它必須遍歷每個相關行並重新計算整個資料集的結果。 如果您使用此查詢來呈現儀表板，則將聚合結果儲存在每日頁面瀏覽量表中並查詢該表會更快。 其次，儲存成本將隨著資料量和可查詢歷史的長度成比例增長。 在實踐中，您可能希望在短時間內保留原始事件並檢視較長時間視窗內的歷史圖表。

為了獲得這些好處，我們可以建立一個 daily_page_views 表來儲存每日統計資訊。

CREATE TABLE daily_page_views (
  site_id int,
  day date,
  url text,
  view_count bigint,
  PRIMARY KEY (site_id, day, url)
);

SELECT create_distributed_table('daily_page_views', 'site_id');

在此示例中，我們在 site_id 列上同時分配了 page_views 和 daily_page_views。 這確保了與特定站點相對應的資料將位於同一節點上。 在每個節點上將兩個表的行保持在一起可以最大限度地減少節點之間的網路流量並實現高度並行執行。

一旦我們建立了這個新的分散式表，我們就可以執行 INSERT INTO ... SELECT 將原始頁面檢視彙總到聚合表中。 在下文中，我們每天彙總頁面瀏覽量。Citus 使用者通常在一天結束後等待一段時間來執行這樣的查詢，以容納遲到的資料。

-- roll up yesterday's data
INSERT INTO daily_page_views (day, site_id, url, view_count)
  SELECT view_time::date AS day, site_id, url, count(*) AS view_count
  FROM page_views
  WHERE view_time >= date '2017-01-01' AND view_time < date '2017-01-02'
  GROUP BY view_time::date, site_id, url;

-- now the results are available right out of the table
SELECT day, site_id, url, view_count
  FROM daily_page_views
  WHERE site_id = 5 AND
    day >= date '2016-01-01' AND day < date '2017-01-01';

上面的彙總查詢彙總了前一天的資料並將其插入 daily_page_views。 每天執行一次查詢意味著不需要更新彙總錶行，因為新一天的資料不會影響之前的行。

當處理遲到的資料或每天多次執行彙總查詢時，情況會發生變化。
如果任何新行與彙總表中已有的天數匹配，則匹配計數應增加。
PostgreSQL 可以使用 “ON CONFLICT” 來處理這種情況，
這是它進行 upserts 的技術。 這是一個例子。

• upserts
  • https://www.postgresql.org/docs/current/static/sql-insert.html#SQL-ON-CONFLICT

-- roll up from a given date onward,
-- updating daily page views when necessary
INSERT INTO daily_page_views (day, site_id, url, view_count)
  SELECT view_time::date AS day, site_id, url, count(*) AS view_count
  FROM page_views
  WHERE view_time >= date '2017-01-01'
  GROUP BY view_time::date, site_id, url
  ON CONFLICT (day, url, site_id) DO UPDATE SET
    view_count = daily_page_views.view_count + EXCLUDED.view_count;

更新和刪除

您可以使用標準 PostgreSQL UPDATE 和 DELETE 命令更新或刪除分散式表中的行。

DELETE FROM github_events
WHERE repo_id IN (24509048, 24509049);

UPDATE github_events
SET event_public = TRUE
WHERE (org->>'id')::int = 5430905;

• UPDATE
  • http://www.postgresql.org/docs/current/static/sql-update.html
• DELETE
  • http://www.postgresql.org/docs/current/static/sql-delete.html

當更新/刪除影響如上例中的多個分片時，Citus 預設使用單階段提交協議。
為了提高安全性，您可以通過設定啟用兩階段提交

SET citus.multi_shard_commit_protocol = '2pc';

如果更新或刪除僅影響單個分片，則它在單個工作節點內執行。在這種情況下，不需要啟用 2PC。 當按表的分佈列更新或刪除過濾器時，通常會發生這種情況：

-- since github_events is distributed by repo_id,
-- this will execute in a single worker node

DELETE FROM github_events
WHERE repo_id = 206084;

此外，在處理單個分片時，Citus 支援 SELECT ... FOR UPDATE。這是物件關係對映器 (ORM) 有時使用的一種技術，用於安全地：

1. 載入行
2. 在應用程式程式碼中進行計算
3. 根據計算更新行

選擇要更新的行會對它們設定寫鎖定，以防止其他程式導致“丟失更新(lost update)”異常。

BEGIN;

  -- select events for a repo, but
  -- lock them for writing
  SELECT *
  FROM github_events
  WHERE repo_id = 206084
  FOR UPDATE;

  -- calculate a desired value event_public using
  -- application logic that uses those rows...

  -- now make the update
  UPDATE github_events
  SET event_public = :our_new_value
  WHERE repo_id = 206084;

COMMIT;

僅雜湊分佈表和引用表支援此功能，並且僅那些具有 replication_factor 為 1 的表支援。

• replication_factor
  • https://docs.citusdata.com/en/v11.0-beta/develop/api_guc.html#replication-factor

最大化寫入效能

在大型機器上，INSERT 和 UPDATE/DELETE 語句都可以擴充套件到每秒約 50,000 個查詢。 但是，要達到這個速度，您將需要使用許多並行的、長期存在的連線並考慮如何處理鎖定。 有關更多資訊，您可以查閱我們文件的橫向擴充套件資料攝取部分。

• 橫向擴充套件資料攝取
  • https://docs.citusdata.com/en/v11.0-beta/performance/performance_tuning.html#scaling-data-ingestion

更多

Citus 分散式 PostgreSQL 叢集 - SQL Reference(建立和修改分散式表 DDL)