日本皇冠官网旗舰店价格表及图片,博彩技巧赌博轮盘,央视卡塔尔世界杯团队(中国)·官方网站

引言

這篇文章還是關于 ClickHouse 提供 Postgres 集成系列文章的一部分。在上一篇文章中，我們探討了 Postgres 函數和表引擎，并以分析工作負載為例，演示了如何將事務數據從 Postgres 遷移到 ClickHouse。在這篇文章中，我們將展示如何結合使用 Postgres 數據與流行的 ClickHouse 字典功能來加速查詢——特別是連接。在文章最后，我們將展示如何使用 Postgres 表引擎將分析查詢的結果從 ClickHouse 推回 Postgres。當用戶需要在終端用戶應用程序中顯示匯總數據，但又希望將統計數據的繁重計算工作卸載給 ClickHouse 時，就可以利用這種“反向 ETL”過程。

如果你想更深入地研究這些示例并重現它們，ClickHouse Cloud 是一個很好的起點——啟動一個集群并獲得 300 美元的免費額度，加載數據，處理下基礎設施，然后進行查詢！

對于本文的示例，我們還是只使用 ClickHouse Cloud 的一個開發實例。對于 Postgres 實例，我們還繼續使用 Supabase，它提供的免費套餐已足夠我們的示例使用。本文假設用戶已經將英國房價數據集加載到 ClickHouse，這是上一篇博文中的一個步驟。數據集加載也可以不使用 Postgres，而是使用這里列出的步驟。

使用基于 Postgres 的詞典

正如我們在之前的博文中重點介紹的那樣，字典可以用來加速 ClickHouse 查詢，特別是涉及連接的時候?？紤]這樣一個例子，我們的目標是找出英國在過去 20 年里價格變化最大的地區（根據 ISO 3166-2）。請注意，ISO 3166-2 編碼不同于郵政編碼，它代表的區域更大，但更重要的是，它在 Superset 這樣的工具中可視化這類數據時非常有用。

在 JOIN 時，我們要使用一個郵政編碼到區域編碼的映射表，可以下載并加載到 codes 表中，如下所示。數據有 100 多萬行，加載到 Supabase 免費實例大約需要一分鐘。假設這份數據現在只在 Postgres 中，所以我們將在 Postgres 中連接這個數據來響應查詢。

注意：ISO 3166-2 編碼到郵政編碼的映射表是從房價數據集生成的，并使用了 play.clickhouse.com 環境中的地區編碼列表。雖然這個數據集可以滿足我們的需求，但并不完整或詳盡，僅涵蓋房價數據集中的郵政編碼。用于生成文件的查詢可以從這里獲取。

wget https://datasets-documentation.s3.amazonaws.com/uk-house-prices/postgres/uk_postcode_to_iso.sql


psql -c "CREATE TABLE uk_postcode_to_iso
(
        id serial,
        postcode varchar(8) primary key,
        iso_code char(6)
);"


psql -c "CREATE INDEX ON uk_postcode_to_iso (iso_code);"
psql < uk_postcode_to_iso.sql


psql -c "select count(*) from uk_postcode_to_iso;"
  count
---------
 1272836
(1 row)


psql -c "	iming" -c "SELECT iso_code, round(avg(((median_2022 - median_2002)/median_2002) * 100)) AS percent_change FROM (
  SELECT postcode1 || ' ' || postcode2 AS postcode, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median_2002 FROM uk_price_paid WHERE extract(year from date) = '2002' GROUP BY postcode
) med_2002 INNER JOIN (
  SELECT postcode1 || ' ' || postcode2 AS postcode, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median_2022 FROM uk_price_paid WHERE extract(year from date) = '2022' GROUP BY postcode
) med_2022 ON med_2002.postcode=med_2022.postcode INNER JOIN (
    SELECT iso_code, postcode FROM uk_postcode_to_iso
) postcode_to_iso ON med_2022.postcode=postcode_to_iso.postcode GROUP BY iso_code ORDER BY percent_change DESC LIMIT 10;"


Timing is on.


iso_code | percent_change
----------+----------------
 GB-TOF   |            403
 GB-KEC   |            380
 GB-MAN   |            360
 GB-SLF   |            330
 GB-BGW   |            321
 GB-HCK   |            313
 GB-MTY   |            306
 GB-AGY   |            302
 GB-RCT   |            293
 GB-BOL   |            292
(10 rows)


Time:?48523.927?ms?(00:48.524)

這個查詢相當復雜，比我們上一篇文章中的查詢成本更高，上一篇文章只計算了倫敦房價變化最大的地區的郵政編碼。雖然我們可以利用 EXTRACT(year FROM date 索引（就像這個執行計劃里那樣），但并沒有機會用到城鎮索引。

我們還可以將 ISO 代碼數據加載到 ClickHouse 表中，重新連接，并根據需要調整語法。或者，我們可能會傾向于將映射留在 Postgres 中，因為其變化相當頻繁。如果在 ClickHouse 中執行連接，將產生以下查詢。注意一下，與使用 postgres 函數相比，我們如何使用 PostgreSQL 表引擎創建 uk_postcode_to_iso 來簡化查詢語法。

CREATE TABLE uk_postcode_to_iso AS postgresql('db.zcxfcrchxescrtxsnxuc.supabase.co', 'postgres', 'uk_postcode_to_iso', 'postgres', '')


SELECT
    iso_code,
    round(avg(percent_change)) AS avg_percent_change
FROM
(
    SELECT
        postcode,
        medianIf(price, toYear(date) = 2002) AS median_2002,
        medianIf(price, toYear(date) = 2022) AS median_2022,
        ((median_2022 - median_2002) / median_2002) * 100 AS percent_change
    FROM uk_price_paid
    GROUP BY concat(postcode1, ' ', postcode2) AS postcode
    HAVING isNaN(percent_change) = 0
) AS med_by_postcode
INNER JOIN uk_postcode_to_iso ON uk_postcode_to_iso.postcode = med_by_postcode.postcode
GROUP BY iso_code
ORDER BY avg_percent_change DESC
LIMIT 10


┌─iso_code─┬─avg_percent_change─┐
│ GB-TOF   │                403 │
│ GB-KEC   │                380 │
│ GB-MAN   │                360 │
│ GB-SLF   │                330 │
│ GB-BGW   │                321 │
│ GB-HCK   │                313 │
│ GB-MTY   │                306 │
│ GB-AGY   │                302 │
│ GB-RCT   │                293 │
│ GB-BOL   │                292 │
└──────────┴────────────────────┘


10rowsinset.Elapsed:4.131sec.Processed29.01millionrows,305.27MB(7.02millionrows/s.,73.90MB/s.)

這并沒有達到我們想要的效果。我們可以創建一個 PostgreSQL 支持的字典，而不是為映射創建一個 ClickHouse 表，如下所示：


CREATE DICTIONARY uk_postcode_to_iso_dict
(
`postcode` String,
`iso_code` String
)
PRIMARY KEY postcode
SOURCE(POSTGRESQL(
   port 5432
   host 'db.ebsmckuuiwnvyiniuvdt.supabase.co'
   user 'postgres'
   password ''
   db 'postgres'
   table 'uk_postcode_to_iso'
   invalidate_query 'SELECT max(id) as mid FROM uk_postcode_to_iso'
))
LIFETIME(300)
LAYOUT(complex_key_hashed())


//force loading of dictionary
SELECT dictGet('uk_postcode_to_iso_dict', 'iso_code', 'BA5 1PD')


┌─dictGet('uk_postcode_to_iso_dict', 'iso_code', 'BA5 1PD')─┐
│ GB-SOM                                                    │
└───────────────────────────────────────────────────────────┘


1rowinset.Elapsed:0.885sec.

該字典將基于 LIFETIME 子句定期更新，并自動同步任何更改。在這種情況下，我們還定義了一個 invalidate_query 子句，它通過返回單個值來控制何時從數據源重新加載數據集。如果這個值發生變化，則重新加載字典——在這個例子中，是當最大 id 發生變化時。在生產場景中，我們可能會希望查詢能夠通過修改時間字段檢測更新。

使用這個字典，我們現在可以修改查詢，并利用表保存在本地內存中的事實進行快速查找。注意，我們也可以避免 join：


SELECT
    iso_code,
    round(avg(percent_change)) AS avg_percent_change
FROM
(
    SELECT
        dictGet('uk_postcode_to_iso_dict', 'iso_code', postcode) AS iso_code,
        medianIf(price, toYear(date) = 2002) AS median_2002,
        medianIf(price, toYear(date) = 2022) AS median_2022,
        ((median_2022 - median_2002) / median_2002) * 100 AS percent_change
    FROM uk_price_paid
    GROUP BY concat(postcode1, ' ', postcode2) AS postcode
    HAVING isNaN(percent_change) = 0
)
GROUP BY iso_code
ORDER BY avg_percent_change DESC
LIMIT 10


┌─iso_code─┬─avg_percent_change─┐
│ GB-TOF   │                403 │
│ GB-KEC   │                380 │
│ GB-MAN   │                360 │
│ GB-SLF   │                330 │
│ GB-BGW   │                321 │
│ GB-HCK   │                313 │
│ GB-MTY   │                306 │
│ GB-AGY   │                302 │
│ GB-RCT   │                293 │
│ GB-BOL   │                292 │
└──────────┴────────────────────┘


10rowsinset.Elapsed:0.444sec.Processed27.73millionrows,319.84MB(62.47millionrows/s.,720.45MB/s.)

這樣更好。感興趣的話，可以在 Superset 等工具中將這些數據可視化，以便更好地理解這些 ISO 編碼 —— 我們之前關于 Superset 的博文中提供了類似的例子。

將結果推回 Postgres

到目前為止，我們已經演示了將數據從 Postgres 遷移到 ClickHouse 用于分析工作負載的價值。如果將這個過程看成是一個 ETL 過程，那么在某些時候，我們可能會希望反轉這個工作流，將分析結果加載回 Postgres 中。我們可以使用本系列之前的文章中介紹的表引擎來實現。

假設我們希望將每個月的銷售統計數據匯總傳回 Postgres，并按郵編、類型、是否是新房子，以及是永久產權還是租賃產權進行匯總。我們假想的網站將在列表的每一頁上顯示這些統計數據，幫助用戶了解該地區的歷史市場狀況。此外，他們希望能夠隨著時間的推移顯示這些統計數據。為了降低 Postgres 生產實例的負載，他們將計算過程卸載給 ClickHouse，并定期將結果推回匯總表。

實際上，這不是一個特別重的查詢，可以在 Postgres 中調度。

下面，在創建表并插入分析查詢的結果之前，我們創建了一個由 Postgres 支持的 ClickHouse 數據庫。


CREATE TABLE summary_prices(
postcode1 varchar(8),
                type varchar(13),
                is_new SMALLINT,
                duration varchar(9),
                sold integer,
                month Date,
                avg_price integer,
                quantile_prices integer[]);


// create Postgres engine table in ClickHouse
CREATE TABLE summary_prices AS postgresql('db.zcxfcrchxescrtxsnxuc.supabase.co', 'postgres', 'summary_prices', 'postgres', '')


//check connectivity
SELECT count()
FROM summary_prices


┌─count()─┐
│       0 │
└─────────┘


1 row in set. Elapsed: 0.337 sec.


// insert the result of our query to Postgres
INSERT INTO summary_prices SELECT
    postcode1,
    type,
    is_new,
    duration,
    count() AS sold,
    month,
    avg(price) AS avg_price,
    quantilesExactExclusive(0.25, 0.5, 0.75, 0.9, 0.95, 0.99)(price) AS quantile_prices
FROM uk_price_paid
WHERE postcode1 != ''
GROUP BY
    toStartOfMonth(date) AS month,
    postcode1,
    type,
    is_new,
    duration
ORDER BY
    postcode1 ASC,
    type ASC,
    is_new ASC,
    duration ASC,
    month ASC


0rowsinset.Elapsed:25.714sec.Processed27.69millionrows,276.98MB(775.43thousandrows/s.,7.76MB/s.)

現在，我們的站點可以運行一個簡單的查詢，獲取一個區域中同一類型的房屋的歷史價格統計。


postgres=> SELECT postcode1, month, avg_price, quantile_prices FROM summary_prices WHERE postcode1='BA5' AND type='detached' AND is_new=0 and duration='freehold' LIMIT 10;
 postcode1 |   month    | avg_price |              quantile_prices
-----------+------------+-----------+--------------------------------------------
 BA5       | 1995-01-01 |    108000 | {64000,100000,160000,160000,160000,160000}
 BA5       | 1995-02-01 |     95142 | {86500,100000,115000,130000,130000,130000}
 BA5       | 1995-03-01 |    138991 | {89487,95500,174750,354000,354000,354000}
 BA5       | 1995-04-01 |     91400 | {63750,69500,130000,165000,165000,165000}
 BA5       | 1995-05-01 |    110625 | {83500,94500,149750,170000,170000,170000}
 BA5       | 1995-06-01 |    124583 | {79375,118500,173750,185000,185000,185000}
 BA5       | 1995-07-01 |    126375 | {88250,95500,185375,272500,272500,272500}
 BA5       | 1995-08-01 |    104416 | {67500,95000,129750,200000,200000,200000}
 BA5       | 1995-09-01 |    103000 | {70000,97000,143500,146000,146000,146000}
 BA5       | 1995-10-01 |     90800 | {58375,72250,111250,213700,223000,223000}
(10rows)

小結

在本系列文章中，我們展示了 ClickHouse 和 Postgres 的互補性，并通過示例演示了如何使用原生 ClickHouse 函數和表引擎輕松地在兩個數據庫之間遷移數據。在這篇文章中，我們介紹了基于 Postgres 的字典，以及如何使用它來加速涉及頻繁變化數據集的查詢的連接。最后，我們執行了一個“反向 ETL”操作，將分析查詢的結果推回 Postgres，供可能面向用戶的應用程序使用。

審核編輯：彭靜

聲明：本文內容及配圖由入駐作者撰寫或者入駐合作網站授權轉載。文章觀點僅代表作者本人，不代表電子發燒友網立場。文章及其配圖僅供工程師學習之用，如有內容侵權或者其他違規問題，請聯系本站處理。舉報投訴