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
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...