ClickHouse(06)ClickHouse的數據表創建語句詳細解析

来源:https://www.cnblogs.com/the-pig-of-zf/archive/2022/09/19/16708795.html
-Advertisement-
Play Games

當前伺服器上創建表(單節點) 創建新表具有幾種種語法形式,具體取決於用例。預設情況下,僅在當前伺服器上創建表。分散式DDL查詢作為子句實現,該子句另外描述。 語法形式 使用顯式架構 CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cl ...


目錄

當前伺服器上創建表(單節點)

創建新表具有幾種種語法形式,具體取決於用例。預設情況下,僅在當前伺服器上創建表。分散式DDL查詢作為子句實現,該子句另外描述。

語法形式

使用顯式架構

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1] [compression_codec] [TTL expr1],
    name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr2] [compression_codec] [TTL expr2],
    ...
) ENGINE = engine

使用[db_name.]參數可以為數據表指定資料庫,如果不指定此參數,則預設會使用default資料庫。

末尾的ENGINE參數,它被用於指定數據表的引擎。表引擎決定了數據表的特性,也決定了數據將會被如何存儲及載入。例如示例中使用的Memory表引擎,是ClickHouse最簡單的表引擎,數據只會被保存在記憶體中,在服務重啟時數據會丟失。

從相同結構的表複製創建

CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [db2.]name2 [ENGINE = engine]

創建與另一個表具有相同結構的表。您可以為表指定其他引擎。如果未指定引擎,則將使用與表相同的引擎。

從表函數創建

CREATE TABLE [IF NOT EXISTS] [db.]table_name AS table_function()

創建與指定表函數的結果相同的表。創建的表也將以與指定的相應表函數相同的方式工作。

什麼是表函數?簡單來說就是一個可以返回一張表的函數。下麵是一個表函數的例子,from後面跟著的就是一個表函數。

CREATE TABLE Orders
ENGINE = MergeTree
ORDER BY OrderID AS
SELECT *
FROM mysql('10.42.134.136:4000', 'databas', 'Orders', 'root', '1234') 

目前的表函數有下麵幾個,這裡暫時不展開講。

ClickHouse表函數

從選擇查詢創建

CREATE TABLE [IF NOT EXISTS] [db.]table_name[(name1 [type1], name2 [type2], ...)] ENGINE = engine AS SELECT ...

使用引擎創建具有類似於查詢結果的結構的表,並使用來自的數據填充該表。創建的時候,還可以顯式指定列說明。

使用IF NOT EXISTS,如果表已存在且已指定,則查詢不會執行任何操作。

查詢中的子句之後可以有其他子句。

分散式集群創建表

ClickHouse支持集群模式,一個集群擁有1到多個節點。CREATE、ALTER、DROP、RENMAE及TRUNCATE這些DDL語句,都支持分散式執行。這意味著,如果在集群中任意一個節點上執行DDL語句,那麼集群中的每個節點都會以相同的順序執行相同的語句。這項特性意義非凡,它就如同批處理命令一樣,省去了需要依次去單個節點執行DDL的煩惱。
將一條普通的DDL語句轉換成分散式執行十分簡單,只需加上ON CLUSTER cluster_name聲明即可。

例如,執行下麵的語句後將會對ch_cluster集群內的所有節點廣播這條DDL語句:

CREATE TABLE partition_v3 ON CLUSTER ch_cluster( 
    ID String,
    URL String,
    EventTime Date
) ENGINE =  MergeTree()
PARTITION BY toYYYYMM(EventTime)
ORDER BY ID

ch_cluster是集群的名稱。

臨時表

ClickHouse也有臨時表的概念,創建臨時表的方法是在普通表的基礎之上添加TEMPORARY關鍵字,它的完整語法如下所示:

CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name (
    name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
    name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
)

相比普通表而言,臨時表有如下兩點特殊之處:

  • 它的生命周期是會話綁定的,所以它只支持Memory表引擎,如果會話結束,數據表就會被銷毀;
  • 臨時表不屬於任何資料庫,所以在它的建表語句中,既沒有資料庫參數也沒有表引擎參數。

臨時表的優先順序是大於普通表的。當兩張數據表名稱相同的時候,會優先讀取臨時表的數據

分區表

數據分區(partition)和數據分片(shard)是完全不同的兩個概念。數據分區是針對本地數據而言的,是數據的一種縱向切分。而數據分片是數據的一種橫向切分。藉助數據分區,在後續的查詢過程中能夠跳過不必要的數據目錄,從而提升查詢的性能。
不是所有的表引擎都可以使用分區,目前只有合併樹(MergeTree)家族系列的表引擎才支持數據分區。由PARTITION BY指定分區鍵,下麵的數據表partition_00使用了日期欄位作為分區鍵,並將其格式化為年月的形式:

CREATE TABLE partition_00 ( 
    ID String,
    URL String,
    EventTime Date
) ENGINE =  MergeTree()
PARTITION BY toYYYYMM(EventTime) 
ORDER BY ID

創建表語句關鍵字解析

空值或非空修飾符

列定義中數據類型後面的修飾符可以指定允許或不允許其值為Null。

CREATE TABLE Orders
(

    `order_id` String,
    `created_at` Nullable(DateTime),
    `updated_at` Nullable(DateTime)
)
ENGINE = MergeTree
ORDER BY (order_id)
SETTINGS index_granularity = 8192

上面的例子中created_at和updated_at可以插入一個NULL值,反之不可以。

預設值表達式

[DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1]

表欄位支持四種預設值表達式的定義方法,分別是DEFAULT、MATERIALIZED、EPHEMERAL和ALIAS。無論使用哪種形式,表欄位一旦被定義了預設值,它便不再強制要求定義數據類型,因為ClickHouse會根據預設值進行類型推斷。

例:URLDomain String DEFAULT domain(URL)

預設值表達式的定義方法之間也存在著不同之處,可以從如下三個方面進行比較。

  1. 數據寫入:在數據寫入時,只有DEFAULT類型的欄位可以出現在INSERT語句中。而MATERIALIZED、EPHEMERAL和ALIAS都不能被顯式賦值,它們只能依靠計算取值。例如試圖為MATERIALIZED類型的欄位寫入數據,將會得到如下的錯誤。
    DB::Exception: Cannot insert column URL, because it is MATERIALIZED column..
  2. 數據查詢:在數據查詢時,只有DEFAULT類型的欄位可以通過SELECT *返回。而MATERIALIZED、EPHEMERAL和ALIAS類型的欄位不會出現在SELECT *查詢的返回結果集中。
  3. 數據存儲:在數據存儲時,只有DEFAULT和MATERIALIZED類型的欄位才支持持久化。如果使用的表引擎支持物理存儲(例如TinyLog表引擎),那麼這些列欄位將會擁有物理存儲。而ALIAS、EPHEMERAL類型的欄位不支持持久化,它的取值總是需要依靠計算產生,數據不會落到磁碟。
  4. EPHEMERAL只能在CREATE語句的預設值中引用。

怎麼理解這四種表達式呢?它其實就是列值的四種生成方式。DEFAULT是在插入的時候計算填充,MATERIALIZED和ALIAS是在查詢的時候,或者說用到的時候填充,而EPHEMERAL,有點類似於我們在建表的時候,創建一個變數,一個代碼塊。

一般表達式

DEFAULT expr

正常預設值。如果INSERT查詢未指定相應的列,則將通過計算相應的表達式來填充它。

物化表達式

MATERIALIZED expr

物化欄位列。這樣的欄位不能在INSERT語句中指定值插入,因為這樣的欄位總是通過使用其他欄位計算出來的。

臨時表達式

EPHEMERAL expr

臨時欄位列。這樣的列不存儲在表中,不能被SELECT 查詢,但可以在CREATE語句的預設值中引用。

別名錶達式

ALIAS expr

欄位別名。這樣的列根本不存儲在表中。其值不能插入到表中,並且在通過SELECT * 查詢,不會出現在結果集。如果在查詢分析期間擴展了別名,則可以在SELECT中使用它。

主鍵

您可以在創建表時定義主鍵。可以通過兩種方式指定主鍵:

-- 內部定義
CREATE TABLE db.table_name
(
    name1 type1, name2 type2, ...,
    PRIMARY KEY(expr1[, expr2,...])]
)
ENGINE = engine;

-- 外部定義
CREATE TABLE db.table_name
(
    name1 type1, name2 type2, ...
)
ENGINE = engine
PRIMARY KEY(expr1[, expr2,...]);

警告:不能在一個查詢中以兩種方式組合。

約束

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1],
    ...
    CONSTRAINT constraint_name_1 CHECK boolean_expr_1,
    ...
) ENGINE = engine

boolean_expr_1可以通過任何布爾表達式。如果為表定義了約束,則將針對查詢中的每一行檢查每個約束。如果不滿足任何約束,伺服器將引發包含約束名稱和檢查表達式的異常。INSERT添加大量約束可能會對大型查詢的性能產生負面影響。

數據TTL

TTL即Time To Live,表達式正常為:TTL expr1。只能為合併樹族表指定。

它表示數據的存活時間。在MergeTree中,可以為某個列欄位或整張表設置TTL。當時間到達時,如果是列欄位級別的TTL,則會刪除這一列的數據;如果是表級別的TTL,則會刪除整張表的數據;如果同時設置了列級別和表級別的TTL,則會以先到期的那個為主。

無論是列級別還是表級別的TTL,都需要依托某個DateTime或Date類型的欄位,通過對這個時間欄位的INTERVAL操作,來表述TTL的過期時間。

如下麵的例子。

-- 表示數據的存活時間是time_col時間的3天之後。
TTL time_col + INTERVAL 3 DAY 上述

-- 表示數據的存活時間是time_col時間的1月之後
TTL time_col + INTERVAL 1 MONTH。

-- INTERVAL完整的操作包括SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER和YEAR。

列級別TTL

CREATE TABLE ttl_table_v1(
    id String,
    create_time DateTime,
    code String TTL create_time + INTERVAL 10 SECOND,
    type UInt8 TTL create_time + INTERVAL 10 SECOND
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY id

表級別TTL

CREATE TABLE ttl_table_v2(
    id String,
    create_time DateTime,
    code String TTL create_time + INTERVAL 1 MINUTE,
    type UInt8
)ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY create_time
TTL create_time + INTERVAL 1 DAY   

clickhouse壓縮與編碼

列壓縮

我們可以每個單獨列定義壓縮方法,這樣可以減少數據存儲的空間。可以指定編解碼器以引用預設壓縮,這可能取決於運行時中的不同設置(和數據屬性)

CREATE TABLE codec_example
(
    dt Date CODEC(ZSTD),
    ts DateTime CODEC(LZ4HC),
    float_value Float32 CODEC(NONE),
    double_value Float64 CODEC(LZ4HC(9)),
    value Float32 CODEC(Delta, ZSTD)
)
ENGINE = <Engine>
...

下表引擎支持壓縮:

  • MergeTree family:支持列壓縮編解碼器,並通過壓縮設置選擇預設壓縮方法。
  • Log family:預設情況下使用壓縮方法,並支持列壓縮編解碼器lz4。
  • Set:僅支持預設壓縮。
  • Join:僅支持預設壓縮。

目前clickhouse支持的壓縮演算法

  1. 通用編碼
  • None:無壓縮
  • LZ4:預設的壓縮演算法,預設值也是使用預設的壓縮演算法
  • LZ4HC[(level)]:z4高壓縮率壓縮演算法版本, level預設值為9,支持[112],推薦選用[49]
  • ZSTD[(level)]:zstd壓縮演算法,level預設值為1,支持[1~22]
  1. 特殊編碼
  • LowCardinality:枚舉值小於1w的字元串
  • Delta:時間序列類型的數據,不會對數據進行壓縮
  • T64:比較適合Int類型數據
  • DoubleDelta:適用緩慢變化的序列:比如時間序列,對於遞增序列效果很好
  • Gorilla:使用緩慢變化的數值類型

特殊編碼與通用的壓縮演算法相比,區別在於,通用的LZ4和ZSTD壓縮演算法是普適行的,不關心數據的分佈特點,而特殊編碼類型對於特定場景下的數據會有更好的壓縮效果。

ClickHouse相關資料分享

ClickHouse經典中文文檔分享

參考文章

ClickHouse(06)ClickHouse的數據表創建語句詳細解析

本文來自博客園,作者:張飛的豬,轉載請註明原文鏈接:https://www.cnblogs.com/the-pig-of-zf/p/16708795.html

作者公眾號:張飛的豬大數據分享,不定期分享大數據學習的總結和相關資料,歡迎關註。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 本文技術方案支持.Net/.Net Core/.Net Framework 數據分頁,幾乎是任何應用系統的必備功能。但當數據量較大時,分頁操作的效率就會變得很低。大數據量分頁時,一個操作耗時5秒、10秒、甚至更長時間都是有可能的,但這在用戶使用的角度是不可接受的…… 數據分頁往往有三種常用方案。 第 ...
  • 關於/dev/null,以及如何使用它 今天在看MIT的一個課程時,老師給的程式實例中有一個地方沒弄明白: #!/bin/bash echo "Starting program at $(date)" # Date will be substituted echo "Running program ...
  • 源碼安裝apache腳本部署 [root@localhost ~]# ls anaconda-ks.cfg httpd.tar.xz [root@localhost ~]# tar xf httpd.tar.xz 解壓存放腳本的壓縮包 [root@localhost ~]# ls anaconda- ...
  • lrTimelapse for Mac是MAC OS上的一款視頻編輯軟體,lrTimelapse mac是一款配合 Adobe Lightroom、Adobe Camera RAW 和 Adobe After Effects 等程式製作延時攝影的軟體。lrtimelapse mac將帶你到下一個水平 ...
  • sed高階用法 sed編輯器 sed是一種流編輯器,流編輯器會在編輯器處理數據之前基於預先提供的一組規則來編輯數據流。 1.sed編輯器工作流程 sed編輯器可以根據命令來處理數據流中的數據,這些命令要麼從命令行中輸入,要麼存儲在一個命令文本文件中。 sed的工作流程主要包括讀取、執行和顯示三個過程 ...
  • 零基礎學MySQL 筆記目錄:(https://www.cnblogs.com/wenjie2000/p/16378441.html) 一個問題 淘寶網,京東、微信,抖音都有各自的功能,那麼當我們退出系統的時候,下次再訪問時,為什麼信息還存在? =》資料庫 解決之道-文件、資料庫 為瞭解決上述問題, ...
  • 案例1:MySQL8.0實現資料庫冷備份和還原 10.0.0.10 -- MySQL8.0 #停止資料庫 [root@CentOS8 my.cnf.d]# systemctl stop mysqld.service #備份數據 [root@CentOS8 ~]# scp -pr /var/lib/m ...
  • 在日常開發工作中,我經常會遇到需要統計總數的場景,比如:統計訂單總數、統計用戶總數等。一般我們會使用MySQL 的count函數進行統計,但是隨著數據量逐漸增大,統計耗時也越來越長,最後竟然出現慢查詢的情況,這究竟是什麼原因呢?本篇文章帶你一下學習一下。 ...
一周排行
    -Advertisement-
    Play Games
  • 下麵是一個標準的IDistributedCache用例: public class SomeService(IDistributedCache cache) { public async Task<SomeInformation> GetSomeInformationAsync (string na ...
  • 這個庫提供了在啟動期間實例化已註冊的單例,而不是在首次使用它時實例化。 單例通常在首次使用時創建,這可能會導致響應傳入請求的延遲高於平時。在註冊時創建實例有助於防止第一次Request請求的SLA 以往我們要在註冊的時候實例單例可能會這樣寫: //註冊: services.AddSingleton< ...
  • 最近公司的很多項目都要改單點登錄了,不過大部分都還沒敲定,目前立刻要做的就只有一個比較老的項目 先改一個試試手,主要目標就是最短最快實現功能 首先因為要保留原登錄方式,所以頁面上的改動就是在原來登錄頁面下加一個SSO登錄入口 用超鏈接寫的入口,頁面改造後如下圖: 其中超鏈接的 href="Staff ...
  • Like運算符很好用,特別是它所提供的其中*、?這兩種通配符,在Windows文件系統和各類項目中運用非常廣泛。 但Like運算符僅在VB中支持,在C#中,如何實現呢? 以下是關於LikeString的四種實現方式,其中第四種為Regex正則表達式實現,且在.NET Standard 2.0及以上平... ...
  • 一:背景 1. 講故事 前些天有位朋友找到我,說他們的程式記憶體會偶發性暴漲,自己分析了下是非托管記憶體問題,讓我幫忙看下怎麼回事?哈哈,看到這個dump我還是非常有興趣的,居然還有這種游戲幣自助機類型的程式,下次去大玩家看看他們出幣的機器後端是不是C#寫的?由於dump是linux上的程式,剛好win ...
  • 前言 大家好,我是老馬。很高興遇到你。 我們為 java 開發者實現了 java 版本的 nginx https://github.com/houbb/nginx4j 如果你想知道 servlet 如何處理的,可以參考我的另一個項目: 手寫從零實現簡易版 tomcat minicat 手寫 ngin ...
  • 上一次的介紹,主要圍繞如何統一去捕獲異常,以及為每一種異常添加自己的Mapper實現,並且我們知道,當在ExceptionMapper中返回非200的Response,不支持application/json的響應類型,而是寫死的text/plain類型。 Filter為二方包異常手動捕獲 參考:ht ...
  • 大家好,我是R哥。 今天分享一個爽飛了的面試輔導 case: 這個杭州兄弟空窗期 1 個月+,面試了 6 家公司 0 Offer,不知道問題出在哪,難道是杭州的 IT 崩盤了麽? 報名面試輔導後,經過一個多月的輔導打磨,現在成功入職某上市公司,漲薪 30%+,955 工作制,不咋加班,還不捲。 其他 ...
  • 引入依賴 <!--Freemarker wls--> <dependency> <groupId>org.freemarker</groupId> <artifactId>freemarker</artifactId> <version>2.3.30</version> </dependency> ...
  • 你應如何運行程式 互動式命令模式 開始一個互動式會話 一般是在操作系統命令行下輸入python,且不帶任何參數 系統路徑 如果沒有設置系統的PATH環境變數來包括Python的安裝路徑,可能需要機器上Python可執行文件的完整路徑來代替python 運行的位置:代碼位置 不要輸入的內容:提示符和註 ...