目录
一、ClickHouse常用命令
二、ClickHouse Client命令
三、ClickHouse官方数据
四、ClickHouse副本同步
五、ClickHouse分片同步
六、ClickHouse导入导出
七、ClickHouse jdbc连接
八、ClickHouse数据类型
九、ClickHouse数据库引擎
十、ClickHouse数据表引擎
十一、ClickHouse表函数
ClickHouse入门学习（一）：https://cloud.tencent.com/developer/article/1913751
ClickHouse入门学习（二）：https://cloud.tencent.com/developer/article/1913753
一、ClickHouse常用命令
代码语言：javascript复制systemctl start clickhouse-server.servicesystemctl stop clickhouse-server.servicesystemctl restart clickhouse-server.servicesystemctl status clickhouse-server.serviceclickhouse-client -u default --password "" --query "SELECT * FROM system.clusters"clickhouse-client --query="SELECT database,name,engine FROM system.tables WHERE database != 'system'"clickhouse-client -m --query "SELECT * FROM system.functions WHERE name LIKE '%arr%';"echo "SHOW DATABASES;" | clickhouse-client -mps aux | tail -n 2 | awk '{ printf("%st%sn", $1, $4) }' | clickhouse local -S "user String, memory Float64" -q "SELECT user, round(sum(memory), 2) as memoryTotal FROM table GROUP BY user ORDER BY memoryTotal DESC FORMAT Pretty"OPTIMIZE TABLE `tableName` FINAL;clickhouse local [args]
clickhouse client [args]
clickhouse benchmark [args]
clickhouse server [args]
clickhouse extract-from-config [args]
clickhouse compressor [args]
clickhouse format [args]
clickhouse copier [args]
clickhouse obfuscator [args]
clickhouse git-import [args]
clickhouse keeper [args]
clickhouse keeper-converter [args]
clickhouse install [args]
clickhouse start [args]
clickhouse stop [args]
clickhouse status [args]
clickhouse restart [args]
clickhouse hash-binary [args]二、ClickHouse Client命令
使用教程 | ClickHouse文档
①交互模式
代码语言：javascript复制clickhouse-client
clickhouse-client --host=... --port=... --user=... --password=...②启用多行查询
代码语言：javascript复制clickhouse-client -m
clickhouse-client --multiline③以批处理模式运行查询
代码语言：javascript复制clickhouse-client --query='SELECT 1'
echo 'SELECT 1' | clickhouse-client
clickhouse-client <<< 'SELECT 1'④从指定格式的文件中插入数据
代码语言：javascript复制clickhouse-client --query='INSERT INTO table VALUES' < data.txt
clickhouse-client --query='INSERT INTO table FORMAT TabSeparated' < data.tsv三、ClickHouse官方数据
Yandex.Metrica是一个网络分析服务，样本数据集不包括其全部功能，因此只有两个表可以创建:
hits表包含所有用户在服务所涵盖的所有网站上完成的每个操作。visits表包含预先构建的会话，而不是单个操作。
①下载官方数据集
代码语言：javascript复制curl https://datasets.clickhouse.tech/hits/tsv/hits_v1.tsv.xz | unxz --threads=`nproc` > hits_v1.tsv
curl https://datasets.clickhouse.tech/visits/tsv/visits_v1.tsv.xz | unxz --threads=`nproc` > visits_v1.tsv②创建数据库
代码语言：javascript复制clickhouse-client --query "CREATE DATABASE IF NOT EXISTS tutorial"③创建数据表
SQL压缩、格式化-BeJSON.com
代码语言：javascript复制CREATE TABLE tutorial.hits_v1 ( `WatchID` UInt64, `JavaEnable` UInt8, `Title` String, `GoodEvent` Int16, `EventTime` DateTime, `EventDate` Date, `CounterID` UInt32, `ClientIP` UInt32, `ClientIP6` FixedString(16), `RegionID` UInt32, `UserID` UInt64, `CounterClass` Int8, `OS` UInt8, `UserAgent` UInt8, `URL` String, `Referer` String, `URLDomain` String, `RefererDomain` String, `Refresh` UInt8, `IsRobot` UInt8, `RefererCategories` Array(UInt16), `URLCategories` Array(UInt16), `URLRegions` Array(UInt32), `RefererRegions` Array(UInt32), `ResolutionWidth` UInt16, `ResolutionHeight` UInt16, `ResolutionDepth` UInt8, `FlashMajor` UInt8, `FlashMinor` UInt8, `FlashMinor2` String, `NetMajor` UInt8, `NetMinor` UInt8, `UserAgentMajor` UInt16, `UserAgentMinor` FixedString(2), `CookieEnable` UInt8, `JavascriptEnable` UInt8, `IsMobile` UInt8, `MobilePhone` UInt8, `MobilePhoneModel` String, `Params` String, `IPNetworkID` UInt32, `TraficSourceID` Int8, `SearchEngineID` UInt16, `SearchPhrase` String, `AdvEngineID` UInt8, `IsArtifical` UInt8, `WindowClientWidth` UInt16, `WindowClientHeight` UInt16, `ClientTimeZone` Int16, `ClientEventTime` DateTime, `SilverlightVersion1` UInt8, `SilverlightVersion2` UInt8, `SilverlightVersion3` UInt32, `SilverlightVersion4` UInt16, `PageCharset` String, `CodeVersion` UInt32, `IsLink` UInt8, `IsDownload` UInt8, `IsNotBounce` UInt8, `FUniqID` UInt64, `HID` UInt32, `IsOldCounter` UInt8, `IsEvent` UInt8, `IsParameter` UInt8, `DontCountHits` UInt8, `WithHash` UInt8, `HitColor` FixedString(1), `UTCEventTime` DateTime, `Age` UInt8, `Sex` UInt8, `Income` UInt8, `Interests` UInt16, `Robotness` UInt8, `GeneralInterests` Array(UInt16), `RemoteIP` UInt32, `RemoteIP6` FixedString(16), `WindowName` Int32, `OpenerName` Int32, `HistoryLength` Int16, `BrowserLanguage` FixedString(2), `BrowserCountry` FixedString(2), `SocialNetwork` String, `SocialAction` String, `HTTPError` UInt16, `SendTiming` Int32, `DNSTiming` Int32, `ConnectTiming` Int32, `ResponseStartTiming` Int32, `ResponseEndTiming` Int32, `FetchTiming` Int32, `RedirectTiming` Int32, `DOMInteractiveTiming` Int32, `DOMContentLoadedTiming` Int32, `DOMCompleteTiming` Int32, `LoadEventStartTiming` Int32, `LoadEventEndTiming` Int32, `NSToDOMContentLoadedTiming` Int32, `FirstPaintTiming` Int32, `RedirectCount` Int8, `SocialSourceNetworkID` UInt8, `SocialSourcePage` String, `ParamPrice` Int64, `ParamOrderID` String, `ParamCurrency` FixedString(3), `ParamCurrencyID` UInt16, `GoalsReached` Array(UInt32), `OpenstatServiceName` String, `OpenstatCampaignID` String, `OpenstatAdID` String, `OpenstatSourceID` String, `UTMSource` String, `UTMMedium` String, `UTMCampaign` String, `UTMContent` String, `UTMTerm` String, `FromTag` String, `HasGCLID` UInt8, `RefererHash` UInt64, `URLHash` UInt64, `CLID` UInt32, `YCLID` UInt64, `ShareService` String, `ShareURL` String, `ShareTitle` String, `ParsedParams` Nested( Key1 String, Key2 String, Key3 String, Key4 String, Key5 String, ValueDouble Float64), `IslandID` FixedString(16), `RequestNum` UInt32, `RequestTry` UInt8 ) ENGINE = MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) CREATE TABLE tutorial.visits_v1 ( `CounterID` UInt32, `StartDate` Date, `Sign` Int8, `IsNew` UInt8, `VisitID` UInt64, `UserID` UInt64, `StartTime` DateTime, `Duration` UInt32, `UTCStartTime` DateTime, `PageViews` Int32, `Hits` Int32, `IsBounce` UInt8, `Referer` String, `StartURL` String, `RefererDomain` String, `StartURLDomain` String, `EndURL` String, `LinkURL` String, `IsDownload` UInt8, `TraficSourceID` Int8, `SearchEngineID` UInt16, `SearchPhrase` String, `AdvEngineID` UInt8, `PlaceID` Int32, `RefererCategories` Array(UInt16), `URLCategories` Array(UInt16), `URLRegions` Array(UInt32), `RefererRegions` Array(UInt32), `IsYandex` UInt8, `GoalReachesDepth` Int32, `GoalReachesURL` Int32, `GoalReachesAny` Int32, `SocialSourceNetworkID` UInt8, `SocialSourcePage` String, `MobilePhoneModel` String, `ClientEventTime` DateTime, `RegionID` UInt32, `ClientIP` UInt32, `ClientIP6` FixedString(16), `RemoteIP` UInt32, `RemoteIP6` FixedString(16), `IPNetworkID` UInt32, `SilverlightVersion3` UInt32, `CodeVersion` UInt32, `ResolutionWidth` UInt16, `ResolutionHeight` UInt16, `UserAgentMajor` UInt16, `UserAgentMinor` UInt16, `WindowClientWidth` UInt16, `WindowClientHeight` UInt16, `SilverlightVersion2` UInt8, `SilverlightVersion4` UInt16, `FlashVersion3` UInt16, `FlashVersion4` UInt16, `ClientTimeZone` Int16, `OS` UInt8, `UserAgent` UInt8, `ResolutionDepth` UInt8, `FlashMajor` UInt8, `FlashMinor` UInt8, `NetMajor` UInt8, `NetMinor` UInt8, `MobilePhone` UInt8, `SilverlightVersion1` UInt8, `Age` UInt8, `Sex` UInt8, `Income` UInt8, `JavaEnable` UInt8, `CookieEnable` UInt8, `JavascriptEnable` UInt8, `IsMobile` UInt8, `BrowserLanguage` UInt16, `BrowserCountry` UInt16, `Interests` UInt16, `Robotness` UInt8, `GeneralInterests` Array(UInt16), `Params` Array(String), `Goals` Nested( ID UInt32, Serial UInt32, EventTime DateTime, Price Int64, OrderID String, CurrencyID UInt32), `WatchIDs` Array(UInt64), `ParamSumPrice` Int64, `ParamCurrency` FixedString(3), `ParamCurrencyID` UInt16, `ClickLogID` UInt64, `ClickEventID` Int32, `ClickGoodEvent` Int32, `ClickEventTime` DateTime, `ClickPriorityID` Int32, `ClickPhraseID` Int32, `ClickPageID` Int32, `ClickPlaceID` Int32, `ClickTypeID` Int32, `ClickResourceID` Int32, `ClickCost` UInt32, `ClickClientIP` UInt32, `ClickDomainID` UInt32, `ClickURL` String, `ClickAttempt` UInt8, `ClickOrderID` UInt32, `ClickBannerID` UInt32, `ClickMarketCategoryID` UInt32, `ClickMarketPP` UInt32, `ClickMarketCategoryName` String, `ClickMarketPPName` String, `ClickAWAPSCampaignName` String, `ClickPageName` String, `ClickTargetType` UInt16, `ClickTargetPhraseID` UInt64, `ClickContextType` UInt8, `ClickSelectType` Int8, `ClickOptions` String, `ClickGroupBannerID` Int32, `OpenstatServiceName` String, `OpenstatCampaignID` String, `OpenstatAdID` String, `OpenstatSourceID` String, `UTMSource` String, `UTMMedium` String, `UTMCampaign` String, `UTMContent` String, `UTMTerm` String, `FromTag` String, `HasGCLID` UInt8, `FirstVisit` DateTime, `PredLastVisit` Date, `LastVisit` Date, `TotalVisits` UInt32, `TraficSource` Nested( ID Int8, SearchEngineID UInt16, AdvEngineID UInt8, PlaceID UInt16, SocialSourceNetworkID UInt8, Domain String, SearchPhrase String, SocialSourcePage String), `Attendance` FixedString(16), `CLID` UInt32, `YCLID` UInt64, `NormalizedRefererHash` UInt64, `SearchPhraseHash` UInt64, `RefererDomainHash` UInt64, `NormalizedStartURLHash` UInt64, `StartURLDomainHash` UInt64, `NormalizedEndURLHash` UInt64, `TopLevelDomain` UInt64, `URLScheme` UInt64, `OpenstatServiceNameHash` UInt64, `OpenstatCampaignIDHash` UInt64, `OpenstatAdIDHash` UInt64, `OpenstatSourceIDHash` UInt64, `UTMSourceHash` UInt64, `UTMMediumHash` UInt64, `UTMCampaignHash` UInt64, `UTMContentHash` UInt64, `UTMTermHash` UInt64, `FromHash` UInt64, `WebVisorEnabled` UInt8, `WebVisorActivity` UInt32, `ParsedParams` Nested( Key1 String, Key2 String, Key3 String, Key4 String, Key5 String, ValueDouble Float64), `Market` Nested( Type UInt8, GoalID UInt32, OrderID String, OrderPrice Int64, PP UInt32, DirectPlaceID UInt32, DirectOrderID UInt32, DirectBannerID UInt32, GoodID String, GoodName String, GoodQuantity Int32, GoodPrice Int64), `IslandID` FixedString(16) ) ENGINE = CollapsingMergeTree(Sign) PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID) SAMPLE BY intHash32(UserID) clickhouse-client --query "INSERT INTO tutorial.hits_v1 FORMAT TSV" --max_insert_block_size=100000 < hits_v1.tsv
clickhouse-client --query "INSERT INTO tutorial.visits_v1 FORMAT TSV" --max_insert_block_size=100000 < visits_v1.tsv⑤验证数据
代码语言：javascript复制clickhouse-client --query "SELECT COUNT(*) FROM tutorial.hits_v1"
clickhouse-client --query "SELECT COUNT(*) FROM tutorial.visits_v1"⑥查询示例
代码语言：javascript复制SELECT
StartURL AS URL,
AVG(Duration) AS AvgDuration
FROM tutorial.visits_v1
WHERE StartDate BETWEEN '2014-03-23' AND '2014-03-30'
GROUP BY URL
ORDER BY AvgDuration DESC
LIMIT 10SELECT
sum(Sign) AS visits,
sumIf(Sign, has(Goals.ID, 1105530)) AS goal_visits,
(100. * goal_visits) / visits AS goal_percent
FROM tutorial.visits_v1
WHERE (CounterID = 912887) AND (toYYYYMM(StartDate) = 201403) AND (domain(StartURL) = 'yandex.ru')⑦强制优化表
代码语言：javascript复制clickhouse-client --query "OPTIMIZE TABLE tutorial.hits_v1 FINAL"
clickhouse-client --query "OPTIMIZE TABLE tutorial.visits_v1 FINAL"四、ClickHouse副本同步
ClickHouse集群搭建：https://cloud.tencent.com/developer/article/1913751
hadoop001执行
代码语言：javascript复制create table t_order_rep (
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine =ReplicatedMergeTree('/clickhouse/table/shard01/t_order_rep','replica01')
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);insert into t_order_rep values
(101,'sku_001',1000.00,'2021-09-16 18:40:00'),
(102,'sku_002',2000.00,'2021-09-16 18:40:00'),
(103,'sku_004',2500.00,'2021-09-16 18:40:00'),
(104,'sku_002',2000.00,'2021-09-16 18:40:00'),
(105,'sku_003',3000.00,'2021-09-16 12:40:00');hadoop002执行
代码语言：javascript复制create table t_order_rep (
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine =ReplicatedMergeTree('/clickhouse/table/shard01/t_order_rep','replica02')
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);hadoop001、hadoop002执行查询，发现都能查到数据（副本同步成功）
代码语言：javascript复制clickhouse-client -m --query="SELECT * FROM t_order_rep;"
五、ClickHouse分片同步
1、在clickhouse集群的每个节点上定义clickhouse全局变量文件，文件根据metrika.xml定义
代码语言：javascript复制hadoop001 shard01 replica01 hadoop002 shard01 replica02 hadoop003 shard02 replica01 hadoop004 shard02 replica02 hadoop005 shard03 replica01 hadoop006 shard03 replica02
echo "export shard=shard01
export replica=replica01" > /home/clickhouse/macros2、source全局变量文件使其生效
代码语言：javascript复制source /home/clickhouse/macrosecho ${shard} ${replica}3、在每台机器上创建本地集群表
代码语言：javascript复制clickhouse-client -m --query "
create table st_order_mt on cluster cluster_3shards_2replicas (
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine=ReplicatedMergeTree('/clickhouse/tables/${shard}/st_order_mt','${replica}')
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);"已解决：创建之后这里有个bug，提示已经创建，并且不是在本机创建的。但是每台机器执行之后查看表却发现分别在各台机器上创建了一张表了，只是表的分片和副本和本机集群配置不一致
4、验证查询分布式表是否正确创建
代码语言：javascript复制clickhouse-client -m --query "SHOW CREATE TABLE st_order_mt"5、在任意一台机器上创建分布式集群表
代码语言：javascript复制clickhouse-client -m --query "
create table st_order_mt_all on cluster cluster_3shards_2replicas (
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine = Distributed(cluster_3shards_2replicas, default, st_order_mt, hiveHash(sku_id));"clickhouse-client -m --query "SHOW TABLES"6、任意一台集群节点向分布式集群表插入数据
将数据插入分布式表，分布式表会将数据平均分配到每个分片中，每个分片的副本之间再相互复制
代码语言：javascript复制clickhouse-client -m --query "
insert into st_order_mt_all values
(201,'sku_001',1000.00,'2021-03-01 19:00:00'),
(202,'sku_002',2000.00,'2021-03-01 19:00:00'),
(203,'sku_004',2500.00,'2021-03-01 19:00:00'),
(204,'sku_002',2000.00,'2021-03-01 19:00:00'),
(205,'sku_003',1200.00,'2021-03-02 19:00:00');"7、查看分布式集群表数据
分别查看本地表和分布式表，查询结果：分布式表查询全量，本地表每个分片查询的数据一致
代码语言：javascript复制clickhouse-client -m --query "SELECT * FROM st_order_mt;"clickhouse-client -m --query "SELECT * FROM st_order_mt_all;"8、删除本地集群表和分布式集群表
代码语言：javascript复制clickhouse-client -m --query "DROP TABLE st_order_mt_all"
clickhouse-client -m --query "DROP TABLE st_order_mt"
clickhouse-client -m --query "SHOW TABLES"
六、ClickHouse导入导出
①数据导入
代码语言：javascript复制cat /chbase/test_fetch.tsv | clickhouse-client --query "INSERT INTO test_fetch FORMAT TSV"②数据导出
代码语言：javascript复制clickhouse-client --query="SELECT * FROM test_fetch" > /chbase/test_fetch.tsv七、ClickHouse jdbc连接
- 官方驱动
- 第三方驱动:
- ClickHouse-Native-JDBC
- clickhouse4j
ClickHouse支持标准的JDBC协议，底层基于HTTP接口通信
①Maven依赖
代码语言：javascript复制<dependency>
<groupId>ru.yandex.clickhouse</groupId>
<artifactId>clickhouse-jdbc</artifactId>
<version>0.2.4</version>
</dependency>②本地标准连接
代码语言：javascript复制// 初始化驱动
Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
// url
String url = "jdbc:clickhouse://hadoop004:8123/default";
// 用户名密码
String username = "default";
String password = "";
// 登录
Connection conn = DriverManager.getConnection(url, username, password);
Statement st = conn.createStatement();
// 查询
ResultSet rs = st.executeQuery("SELECT 1");
rs.next();
System.out.printf(rs.getInt(1));③高可用分布式连接
代码语言：javascript复制//多个地址使用逗号分隔
String url1 = "jdbc:clickhouse://hadoop001:8123,hadoop002:8123,hadoop003:8123/default";
//设置JDBC参数
ClickHouseProperties clickHouseProperties = new ClickHouseProperties();
clickHouseProperties.setUser("default");
//声明数据源
BalancedClickhouseDataSource balanced = new BalancedClickhouseDataSource(url1, clickHouseProperties);
//对每个host进行ping操作, 排除不可用的dead连接
balanced.actualize();
//获得JDBC连接
Connection conn = balanced.getConnection();
Statement st = conn.createStatement();
//查询
ResultSet rs = st.executeQuery("SELECT 1 , hostName()");
rs.next();
System.out.println("res " rs.getInt(1) "," rs.getString(2));④连接实例
代码语言：javascript复制import ru.yandex.clickhouse.BalancedClickhouseDataSource;
import ru.yandex.clickhouse.settings.ClickHouseProperties;
import java.sql.*;
import java.util.Arrays;
public class ClickhouseDemo {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
ck_alone_test(); // 本地连接测试
ck_cluster_test(); // 分布式连接测试
}
/**
* jdbc本地连接
*/
private static void ck_alone_test() throws ClassNotFoundException, SQLException {
// 初始化驱动
Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
// url
String url = "jdbc:clickhouse://hadoop004:8123/default";
// 用户名密码
String username = "default";
String password = "";
// 登录
Connection conn = DriverManager.getConnection(url, username, password);
Statement st = conn.createStatement();
// 查询
// ResultSet rs = st.executeQuery("SELECT 1");
// System.out.printf(rs.getInt(1));
String sql = "SELECT database, `name`, uuid, engine, is_temporary, data_paths, metadata_path, "
" metadata_modification_time, create_table_query FROM system.tables t LIMIT 10";
ResultSet rs = st.executeQuery(sql);
int columnCount = rs.getMetaData().getColumnCount(); // 获取查询总列数
while(rs.next()) {
System.out.print(rs.getRow() "t"); // 获取当前行
for (int i = 1; i < columnCount 1; i ) {
String columnTypeName = rs.getMetaData().getColumnTypeName(i); // 字段类型
String columnName = rs.getMetaData().getColumnName(i); // 字段名称
Object object = rs.getObject(i);
if(object instanceof Array) {
Array array = rs.getArray(i);
String[] arr = (String[]) array.getArray();
System.out.print( i ":" columnTypeName ":" columnName ":" Arrays.toString(arr) "t");
} else {
System.out.print( i ":" columnTypeName ":" columnName ":" object "t");
}
}
System.out.println();
}
}
/**
* 高可用分布式连接
*/
private static void ck_cluster_test() throws SQLException {
// 多个地址使用逗号分隔
String url = "jdbc:clickhouse://hadoop001:8123,hadoop002:8123,hadoop003:8123,hadoop004:8123,hadoop005:8123,hadoop006:8123/default";
// 设置JDBC参数
ClickHouseProperties clickHouseProperties = new ClickHouseProperties();
clickHouseProperties.setUser("default"); // 设置用户
// 声明数据源
BalancedClickhouseDataSource balanced = new BalancedClickhouseDataSource(url, clickHouseProperties);
// 对每个host进行ping操作, 排除不可用的dead连接
balanced.actualize();
// 获得JDBC连接
Connection conn = balanced.getConnection();
Statement st = conn.createStatement();
// 查询
// ResultSet rs = st.executeQuery("SELECT 1 , hostName()");
// rs.next();
// System.out.println(rs.getInt(1) ", " rs.getString(2));
String sql = "SELECT hostName(), database, `name`, uuid, engine, is_temporary, data_paths, metadata_path, "
" metadata_modification_time, create_table_query FROM system.tables t LIMIT 10";
ResultSet rs = st.executeQuery(sql);
int columnCount = rs.getMetaData().getColumnCount(); // 获取查询总列数
while(rs.next()) {
System.out.print(rs.getRow() "t"); // 获取当前行
for (int i = 1; i < columnCount 1; i ) {
String columnTypeName = rs.getMetaData().getColumnTypeName(i); // 字段类型
String columnName = rs.getMetaData().getColumnName(i); // 字段名称
Object object = rs.getObject(i);
if(object instanceof Array) {
Array array = rs.getArray(i);
String[] arr = (String[]) array.getArray();
System.out.print( i ":" columnTypeName ":" columnName ":" Arrays.toString(arr) "t");
} else {
System.out.print( i ":" columnTypeName ":" columnName ":" object "t");
}
}
System.out.println();
}
}
}[ main] ClickHouseDriver INFO Driver registered
1 1:String:database:default 2:String:name:st_order_mt 3:UUID:uuid:69d6b7fe-7ff7-4090-a9d6-b7fe7ff70090 4:String:engine:ReplicatedMergeTree 5:UInt8:is_temporary:0 6:Array(String):data_paths:[/home/clickhouse/data/store/69d/69d6b7fe-7ff7-4090-a9d6-b7fe7ff70090/] 7:String:metadata_path:/home/clickhouse/data/store/a92/a92ed621-cf4a-4ec3-a92e-d621cf4a6ec3/st_order_mt.sql 8:DateTime:metadata_modification_time:2021-09-17 17:03:10.0 9:String:create_table_query:CREATE TABLE default.st_order_mt (`id` UInt32, `sku_id` String, `total_amount` Decimal(16, 2), `create_time` DateTime) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/st_order_mt', '{replica}') PARTITION BY toYYYYMMDD(create_time) PRIMARY KEY id ORDER BY (id, sku_id) SETTINGS index_granularity = 8192
2 1:String:database:default 2:String:name:st_order_mt_all 3:UUID:uuid:7f6a4841-b29c-48aa-bf6a-4841b29ce8aa 4:String:engine:Distributed 5:UInt8:is_temporary:0 6:Array(String):data_paths:[/home/clickhouse/data/store/7f6/7f6a4841-b29c-48aa-bf6a-4841b29ce8aa/] 7:String:metadata_path:/home/clickhouse/data/store/a92/a92ed621-cf4a-4ec3-a92e-d621cf4a6ec3/st_order_mt_all.sql 8:DateTime:metadata_modification_time:2021-09-17 17:07:47.0 9:String:create_table_query:CREATE TABLE default.st_order_mt_all (`id` UInt32, `sku_id` String, `total_amount` Decimal(16, 2), `create_time` DateTime) ENGINE = Distributed('cluster_3shards_2replicas', 'default', 'st_order_mt', hiveHash(sku_id))
3 1:String:database:system 2:String:name:aggregate_function_combinators 3:UUID:uuid:efb6fe74-288a-43a2-afb6-fe74288ab3a2 4:String:engine:SystemAggregateFunctionCombinators 5:UInt8:is_temporary:0 6:Array(String):data_paths:[] 7:String:metadata_path:/home/clickhouse/data/store/fc0/fc06fe39-95e5-4baa-bc06-fe3995e5dbaa/aggregate_function_combinators.sql 8:DateTime:metadata_modification_time:1970-01-01 08:00:00.0 9:String:create_table_query:
4 1:String:database:system 2:String:name:asynchronous_metric_log 3:UUID:uuid:ea97290d-9d8d-4fea-aa97-290d9d8d4fea 4:String:engine:MergeTree 5:UInt8:is_temporary:0 6:Array(String):data_paths:[/home/clickhouse/data/store/ea9/ea97290d-9d8d-4fea-aa97-290d9d8d4fea/] 7:String:metadata_path:/home/clickhouse/data/store/fc0/fc06fe39-95e5-4baa-bc06-fe3995e5dbaa/asynchronous_metric_log.sql 8:DateTime:metadata_modification_time:2021-09-16 10:50:00.0 9:String:create_table_query:CREATE TABLE system.asynchronous_metric_log (`event_date` Date, `event_time` DateTime, `event_time_microseconds` DateTime64(6), `metric` LowCardinality(String), `value` Float64) ENGINE = MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, event_time) SETTINGS index_granularity = 8192
5 1:String:database:system 2:String:name:asynchronous_metrics 3:UUID:uuid:afd6c74a-8839-42f7-afd6-c74a8839e2f7 4:String:engine:SystemAsynchronousMetrics 5:UInt8:is_temporary:0 6:Array(String):data_paths:[] 7:String:metadata_path:/home/clickhouse/data/store/fc0/fc06fe39-95e5-4baa-bc06-fe3995e5dbaa/asynchronous_metrics.sql 8:DateTime:metadata_modification_time:1970-01-01 08:00:00.0 9:String:create_table_query:
6 1:String:database:system 2:String:name:build_options 3:UUID:uuid:b933baaa-027c-4ad5-b933-baaa027cdad5 4:String:engine:SystemBuildOptions 5:UInt8:is_temporary:0 6:Array(String):data_paths:[] 7:String:metadata_path:/home/clickhouse/data/store/fc0/fc06fe39-95e5-4baa-bc06-fe3995e5dbaa/build_options.sql 8:DateTime:metadata_modification_time:1970-01-01 08:00:00.0 9:String:create_table_query:
7 1:String:database:system 2:String:name:clusters 3:UUID:uuid:8df6996f-2b66-462d-8df6-996f2b66362d 4:String:engine:SystemClusters 5:UInt8:is_temporary:0 6:Array(String):data_paths:[] 7:String:metadata_path:/home/clickhouse/data/store/fc0/fc06fe39-95e5-4baa-bc06-fe3995e5dbaa/clusters.sql 8:DateTime:metadata_modification_time:1970-01-01 08:00:00.0 9:String:create_table_query:
8 1:String:database:system 2:String:name:collations 3:UUID:uuid:f794a6a0-038d-47b4-b794-a6a0038de7b4 4:String:engine:SystemTableCollations 5:UInt8:is_temporary:0 6:Array(String):data_paths:[] 7:String:metadata_path:/home/clickhouse/data/store/fc0/fc06fe39-95e5-4baa-bc06-fe3995e5dbaa/collations.sql 8:DateTime:metadata_modification_time:1970-01-01 08:00:00.0 9:String:create_table_query:
9 1:String:database:system 2:String:name:columns 3:UUID:uuid:bd31b8ac-3d56-4b7d-bd31-b8ac3d560b7d 4:String:engine:SystemColumns 5:UInt8:is_temporary:0 6:Array(String):data_paths:[] 7:String:metadata_path:/home/clickhouse/data/store/fc0/fc06fe39-95e5-4baa-bc06-fe3995e5dbaa/columns.sql 8:DateTime:metadata_modification_time:1970-01-01 08:00:00.0 9:String:create_table_query:
10 1:String:database:system 2:String:name:contributors 3:UUID:uuid:d2a2ecd2-33a7-48e3-92a2-ecd233a778e3 4:String:engine:SystemContributors 5:UInt8:is_temporary:0 6:Array(String):data_paths:[] 7:String:metadata_path:/home/clickhouse/data/store/fc0/fc06fe39-95e5-4baa-bc06-fe3995e5dbaa/contributors.sql 8:DateTime:metadata_modification_time:1970-01-01 08:00:00.0 9:String:create_table_query:
1 1:String:hostName():hadoop006 2:String:database:default 3:String:name:st_order_mt 4:UUID:uuid:69d6b7fe-7ff7-4090-a9d6-b7fe7ff70090 5:String:engine:ReplicatedMergeTree 6:UInt8:is_temporary:0 7:Array(String):data_paths:[/home/clickhouse/data/store/69d/69d6b7fe-7ff7-4090-a9d6-b7fe7ff70090/] 8:String:metadata_path:/home/clickhouse/data/store/e10/e10f4a1c-ea84-4729-a10f-4a1cea84c729/st_order_mt.sql 9:DateTime:metadata_modification_time:2021-09-17 17:03:10.0 10:String:create_table_query:CREATE TABLE default.st_order_mt (`id` UInt32, `sku_id` String, `total_amount` Decimal(16, 2), `create_time` DateTime) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/st_order_mt', '{replica}') PARTITION BY toYYYYMMDD(create_time) PRIMARY KEY id ORDER BY (id, sku_id) SETTINGS index_granularity = 8192
2 1:String:hostName():hadoop006 2:String:database:default 3:String:name:st_order_mt_all 4:UUID:uuid:7f6a4841-b29c-48aa-bf6a-4841b29ce8aa 5:String:engine:Distributed 6:UInt8:is_temporary:0 7:Array(String):data_paths:[/home/clickhouse/data/store/7f6/7f6a4841-b29c-48aa-bf6a-4841b29ce8aa/] 8:String:metadata_path:/home/clickhouse/data/store/e10/e10f4a1c-ea84-4729-a10f-4a1cea84c729/st_order_mt_all.sql 9:DateTime:metadata_modification_time:2021-09-17 17:07:47.0 10:String:create_table_query:CREATE TABLE default.st_order_mt_all (`id` UInt32, `sku_id` String, `total_amount` Decimal(16, 2), `create_time` DateTime) ENGINE = Distributed('cluster_3shards_2replicas', 'default', 'st_order_mt', hiveHash(sku_id))
3 1:String:hostName():hadoop006 2:String:database:system 3:String:name:aggregate_function_combinators 4:UUID:uuid:9216cd27-9891-4084-9216-cd2798914084 5:String:engine:SystemAggregateFunctionCombinators 6:UInt8:is_temporary:0 7:Array(String):data_paths:[] 8:String:metadata_path:/home/clickhouse/data/store/b8e/b8e5b98f-2200-47c5-b8e5-b98f2200e7c5/aggregate_function_combinators.sql 9:DateTime:metadata_modification_time:1970-01-01 08:00:00.0 10:String:create_table_query:
4 1:String:hostName():hadoop006 2:String:database:system 3:String:name:asynchronous_metric_log 4:UUID:uuid:7cde19de-a2d1-42b8-bcde-19dea2d142b8 5:String:engine:MergeTree 6:UInt8:is_temporary:0 7:Array(String):data_paths:[/home/clickhouse/data/store/7cd/7cde19de-a2d1-42b8-bcde-19dea2d142b8/] 8:String:metadata_path:/home/clickhouse/data/store/b8e/b8e5b98f-2200-47c5-b8e5-b98f2200e7c5/asynchronous_metric_log.sql 9:DateTime:metadata_modification_time:2021-09-16 10:50:00.0 10:String:create_table_query:CREATE TABLE system.asynchronous_metric_log (`event_date` Date, `event_time` DateTime, `event_time_microseconds` DateTime64(6), `metric` LowCardinality(String), `value` Float64) ENGINE = MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, event_time) SETTINGS index_granularity = 8192
5 1:String:hostName():hadoop006 2:String:database:system 3:String:name:asynchronous_metrics 4:UUID:uuid:1ec9bfe3-b3d5-486c-9ec9-bfe3b3d5486c 5:String:engine:SystemAsynchronousMetrics 6:UInt8:is_temporary:0 7:Array(String):data_paths:[] 8:String:metadata_path:/home/clickhouse/data/store/b8e/b8e5b98f-2200-47c5-b8e5-b98f2200e7c5/asynchronous_metrics.sql 9:DateTime:metadata_modification_time:1970-01-01 08:00:00.0 10:String:create_table_query:
6 1:String:hostName():hadoop006 2:String:database:system 3:String:name:build_options 4:UUID:uuid:8ed23f5a-689b-4e3d-8ed2-3f5a689b7e3d 5:String:engine:SystemBuildOptions 6:UInt8:is_temporary:0 7:Array(String):data_paths:[] 8:String:metadata_path:/home/clickhouse/data/store/b8e/b8e5b98f-2200-47c5-b8e5-b98f2200e7c5/build_options.sql 9:DateTime:metadata_modification_time:1970-01-01 08:00:00.0 10:String:create_table_query:
7 1:String:hostName():hadoop006 2:String:database:system 3:String:name:clusters 4:UUID:uuid:e53de99c-4ad8-4304-a53d-e99c4ad88304 5:String:engine:SystemClusters 6:UInt8:is_temporary:0 7:Array(String):data_paths:[] 8:String:metadata_path:/home/clickhouse/data/store/b8e/b8e5b98f-2200-47c5-b8e5-b98f2200e7c5/clusters.sql 9:DateTime:metadata_modification_time:1970-01-01 08:00:00.0 10:String:create_table_query:
8 1:String:hostName():hadoop006 2:String:database:system 3:String:name:collations 4:UUID:uuid:d290a6a0-1994-4d0a-9290-a6a019940d0a 5:String:engine:SystemTableCollations 6:UInt8:is_temporary:0 7:Array(String):data_paths:[] 8:String:metadata_path:/home/clickhouse/data/store/b8e/b8e5b98f-2200-47c5-b8e5-b98f2200e7c5/collations.sql 9:DateTime:metadata_modification_time:1970-01-01 08:00:00.0 10:String:create_table_query:
9 1:String:hostName():hadoop006 2:String:database:system 3:String:name:columns 4:UUID:uuid:210e9b78-5f95-420c-a10e-9b785f95d20c 5:String:engine:SystemColumns 6:UInt8:is_temporary:0 7:Array(String):data_paths:[] 8:String:metadata_path:/home/clickhouse/data/store/b8e/b8e5b98f-2200-47c5-b8e5-b98f2200e7c5/columns.sql 9:DateTime:metadata_modification_time:1970-01-01 08:00:00.0 10:String:create_table_query:
10 1:String:hostName():hadoop006 2:String:database:system 3:String:name:contributors 4:UUID:uuid:58a99afd-8a75-4c59-98a9-9afd8a752c59 5:String:engine:SystemContributors 6:UInt8:is_temporary:0 7:Array(String):data_paths:[] 8:String:metadata_path:/home/clickhouse/data/store/b8e/b8e5b98f-2200-47c5-b8e5-b98f2200e7c5/contributors.sql 9:DateTime:metadata_modification_time:1970-01-01 08:00:00.0 10:String:create_table_query: 八、ClickHouse数据类型
1、基本数据类型
MySQL和ClickHouse对应的数据类型
ClickHouse | MySQL |
|---|---|
Int8 | TINY |
Int16 | SHORT |
Int32 | INT24 |
UInt32 | LONG |
UInt64 | LONGLONG |
Float32 | FLOAT |
Float64 | DOUBLE |
Decimal | DECIMAL, NEWDECIMAL |
Date | DATE, NEWDATE |
DateTime | DATETIME, TIMESTAMP |
DateTime64 | DATETIME2, TIMESTAMP2 |
Enum | ENUM |
String | STRING |
String | VARCHAR, VAR_STRING |
String | BLOB |
FixedString | BINARY |
查询所有数据类型
代码语言：javascript复制clickhouse-client --query="SELECT * FROM system.data_type_families"
1、整形
整形数据类型分无符号整形和有符号整形
名称 | 范围 | MySQL类型 |
|---|---|---|
UInt8 | 0:255 | Tinyint Unsigned |
UInt16 | 0:65535 | Smallint Unsigned |
UInt32 | 0:4294967295 | Int Unsigned |
UInt64 | 0:18446744073709551615 | Bigint Unsigned |
Int8 | -128:127 | Tinyint |
Int16 | -32768:32767 | Smallint |
Int32 | -2147483648:2147483647 | Int |
Int64 | -9223372036854775808:9223372036854775807 | Bigint |
2、浮点型
clickhouse的浮点型计算可能会出现精度丢失，建议以整数形式存储数据。
名称 | 范围 | MySQL类型 |
|---|---|---|
Float32 | | float |
Float64 | | double |
例如，将固定精度的数字转换为整数值。
代码语言：javascript复制SELECT 1 - 0.9;
3、定点数
有符号的定点数，可在加、减和乘法运算过程中保持精度。对于除法，最低有效数字会被丢弃（不舍入）。
Decimal参数
- P - 精度。有效范围：1:38，决定可以有多少个十进制数字（包括分数）。
- S - 规模。有效范围：0：P，决定数字的小数部分中包含的小数位数。
名称 | 范围 | MySQL类型 |
|---|---|---|
Decimal32 | -1 * 10^(9 - S) - 1 * 10^(9 - S) | decimal |
Decimal64 | -1 * 10^(18 - S) - 1 * 10^(18 - S) | decimal |
Decimal128 | -1 * 10^(38 - S) - 1 * 10^(38 - S) | decimal |
4、字符串
String 字符串可以任意长度的。它可以包含任意的字节集，包含空字节。
FixedString(N) 定长字符串
5、日期类型
Date
日期类型，用两个字节存储，表示从 1970-01-01到当前的日期值，日期中没有存储时区信息。
DateTime
时间戳类型，用四个字节（无符号的）存储 Unix 时间戳），最小值为 0000-00-00 00:00:00。
默认客户端连接使用服务端时区，可以通过启用客户端命令行选项 --use_client_time_zone 来设置使用客户端时间。
6、枚举类型
Enum 保存 'string'= integer 的对应关系。
Enum8 用 'String'= Int8 对描述。
Enum16 用 'String'= Int16 对描述。
代码语言：javascript复制SELECT ('welcome'), ('to'), ('china') AS x, toTypeName(x);
SELECT ('welcome','welcome1','welcome2'), ('to','to1','to2'), ('china','china1','china2') AS x, toTypeName(x);
7、域（domain）
IPv4是与UInt32类型保持二进制兼容的Domain类型，其用于存储IPv4地址的值。
IPv6是与FixedString(16)类型保持二进制兼容的Domain类型，其用于存储IPv6地址的值。
8、UUID（通用唯一标识符）
通用唯一标识符(UUID)是一个16字节的数字，用于标识记录。
代码语言：javascript复制clickhouse-client --query="SELECT generateUUIDv4()"9、Nullable（可空类型）
允许用特殊标记 (NULL) 表示缺失值，可以与 TypeName 的正常值存放一起。
例如，Nullable(Int8) 类型的列可以存储 Int8 类型值，而没有值的行将存储 NULL。
Nullable 类型字段不能包含在表索引中。
注意事项：使用 Nullable 几乎总是对性能产生负面影响。
10、Nothing（空值）
此数据类型的唯一目的是表示不是期望值的情况。 所以不能创建一个 Nothing 类型的值。
Nothing 类型也可以用来表示空数组
代码语言：javascript复制SELECT toTypeName(array());
SELECT tuple(1, NULL) AS x, toTypeName(x);
2、GEO类型（实验性）
1、Point（点）
点由其X和Y坐标表示，存储为tuple(Float 64，Float 64)
代码语言：javascript复制SELECT (4.245, 7.4563) AS x, toTypeName(x);2、Ring（圆环）
圆环是一个简单的多边形，没有孔，存储为点数组：Array(Point)
代码语言：javascript复制SELECT [(0, 0), (1, 0), (1, 1), (0, 1), (0, 0)] AS x, toTypeName(x);3、Polygon（多边形）
多边形是一个具有孔的多边形，存储成一个环阵列： Array(Ring)
外部阵列的第一个元素是多边形的外部形状，之后所有元素都是孔
代码语言：javascript复制SELECT [[(20, 20), (50, 20), (50, 50), (20, 50)], [(30, 30), (50, 50), (50, 30)]] AS x, toTypeName(x);4、MultiPolygon（多重多边形）
多重多边形由多个多边形组成，存储为一个多边形数组：Array(Polygon)
代码语言：javascript复制SELECT [[[(0, 0), (10, 0), (10, 10), (0, 10)]], [[(20, 20), (50, 20), (50, 50), (20, 50)],[(30, 30), (50, 50), (50, 30)]]] AS x, toTypeName(x);3、复合数据类型
1、Map(key, value)
Map(key, value)数据类型存储 键值对
参数类型
- 键—String、Integer、LowCardinality或FixedString
- 值—String、Integer、Array、LowCardinality或FixedString
创建带 Map(key, value) 列的临时表，并插入数据
代码语言：javascript复制DROP TABLE IF EXISTS t_test_map;
CREATE TEMPORARY TABLE t_test_map (a Map(String, UInt64));以键值的方式插入数据
代码语言：javascript复制INSERT INTO t_test_map VALUES ({'key1':1, 'key2':10}), ({'key1':2,'key2':20}), ({'key1':3,'key2':30});Map(key, value) 列类型的查询
代码语言：javascript复制-- 查询所有键值;
SELECT a FROM t_test_map;
-- 指定键查询值;
SELECT a['key2'] FROM t_test_map;以map的方式插入数据
代码语言：javascript复制INSERT INTO t_test_map VALUES (map('key1', 1, 'key2', 2, 'key3', 3));Map(key, value) 列类型的查询
代码语言：javascript复制-- 查询所有键值;
SELECT a FROM t_test_map;
-- 指定键查询值;
SELECT a['key2'] FROM t_test_map;Tuple 转换成 Map
代码语言：javascript复制SELECT CAST(([1, 2, 3], ['Ready', 'Steady', 'Go']), 'Map(UInt8, String)') AS x, toTypeName(x);
2、Tuple（元组）
元组Tuple：每个数据都有单独的类型。tuple类型只能存储在内存表或临时查询中。
代码语言：javascript复制SELECT (1, 2) AS x, toTypeName(x);
3、Array（阵列）
数组函数 | ClickHouse文档
阵列 Array(T)：T 可以是任意类型，包含数组类型。
但不推荐使用多维数组，ClickHouse 对多维数组的支持有限。
例如，不能存储在 MergeTree 表中存储多维数组。
代码语言：javascript复制SELECT array(1, 2) AS x, toTypeName(x);
SELECT [1, 2] AS x, toTypeName(x);
SELECT array(1, 2, NULL) AS x, toTypeName(x);
SELECT [1,2,3,4,5] AS x, toTypeName(x), arrayCount(x);
4、Interval（间隔）
表示时间和日期间隔的数据类型家族。Interval 数据类型值不能存储在表中。
代码语言：javascript复制SELECT toTypeName(INTERVAL 3 DAY);
SELECT now() AS x, toTypeName(x);
支持的时间间隔类型
- SECOND
- MINUTE
- HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
5、Nested（镶嵌数据结构）
Nested(Name1 Type1, Name2 Type2, …)
嵌套数据结构类似于嵌套表。嵌套数据结构的参数（列名和类型）与 CREATE 查询类似
- 每个表可以包含任意多行嵌套数据结构
- 只支持一级嵌套
- 嵌套结构的列中，若列的类型是数组类型，那么该列其实和多维数组是相同的
- 目前嵌套层级的支持很局限（MergeTree 引擎中不支持存储这样的列）
- 可以简单地把嵌套数据结构当做是所有列都是相同长度的多列数组。
- 不能对整个嵌套数据结构执行 SELECT，只能明确列出属于它一部分列
CREATE TABLE test.visits
(
CounterID UInt32,
StartDate Date,
Sign Int8,
IsNew UInt8,
VisitID UInt64,
UserID UInt64,
...
Goals Nested
(
ID UInt32,
Serial UInt32,
EventTime DateTime,
Price Int64,
OrderID String,
CurrencyID UInt32
),
...
) ENGINE = CollapsingMergeTree(StartDate, intHash32(UserID), (CounterID, StartDate, intHash32(UserID), VisitID), 8192, Sign);SELECT
Goals.ID,
Goals.EventTime
FROM test.visits
WHERE CounterID = 101500 AND length(Goals.ID) < 5
LIMIT 10九、ClickHouse数据库引擎
默认情况下
ClickHouse使用Atomic数据库引擎。它提供了可配置的table engines和SQL dialect
ClickHouse支持七大数据库引擎
MySQL、MaterializeMySQL、Lazy、Atomic
PostgreSQL、MaterializedPostgreSQL、Replicated
1、MySQL
作用
- MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中
- MySQL引擎允许对MySQL表进行INSERT和SELECT查询，但不允许执行RENAME、CREATE TABLE、ALTER操作
语法
代码语言：javascript复制CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')clickhouse -m --query="CREATE DATABASE mysql_db ENGINE = MySQL('localhost:3306', 'dbname', 'username', 'password')"clickhouse-client --query="SHOW TABLES FROM mysql_db"2、MaterializeMySQL（实验性）
作用
- 创建ClickHouse数据库，包含MySQL中所有的表，以及这些表中的所有数据。
- ClickHouse服务器作为MySQL副本工作。它读取binlog并执行DDL和DML查询。
前提
- MySQL中的每个表都应该包含PRIMARY KEY
- 对于包含ENUM字段值超出范围（在ENUM签名中指定）的行的表，复制将不起作用
语法
代码语言：javascript复制CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializeMySQL('host:port', ['database' | database], 'user', 'password') [SETTINGS ...]clickhouse --query="CREATE DATABASE mysql ENGINE = MaterializeMySQL('localhost:3306', 'dbname', 'username', 'password'); SHOW TABLES FROM mysql;"支持的数据类型
MySQL | ClickHouse |
|---|---|
TINY | Int8 |
SHORT | Int16 |
INT24 | Int32 |
LONG | UInt32 |
LONGLONG | UInt64 |
FLOAT | Float32 |
DOUBLE | Float64 |
DECIMAL, NEWDECIMAL | Decimal |
DATE, NEWDATE | Date |
DATETIME, TIMESTAMP | DateTime |
DATETIME2, TIMESTAMP2 | DateTime64 |
ENUM | Enum |
STRING | String |
VARCHAR, VAR_STRING | String |
BLOB | String |
BINARY | FixedString |
不支持其他类型。如果MySQL表包含此类类型的列，ClickHouse抛出异常"Unhandled data type"并停止复制。
Data Replication
MaterializeMySQL不支持直接INSERT, DELETE和UPDATE查询. 但是，它们是在数据复制方面支持的：
- MySQL的INSERT查询转换为INSERT并携带_sign=1.
- MySQL的DELETE查询转换为INSERT并携带_sign=-1.
- MySQL的UPDATE查询转换为INSERT并携带_sign=-1, INSERT和_sign=1
查询MaterializeMySQL表
SELECT查询MaterializeMySQL表有一些细节:
- 如果_version在SELECT中没有指定，则使用FINAL修饰符。所以只有带有MAX(_version)的行才会被选中。
- 如果_sign在SELECT中没有指定，则默认使用WHERE _sign=1。因此，删除的行不会包含在结果集中。 结果包括列中的列注释，因为它们存在于SQL数据库表中。
Index Conversion
MySQL的PRIMARY KEY和INDEX子句在ClickHouse表中转换为ORDER BY元组。
ClickHouse只有一个物理顺序，由ORDER BY子句决定。要创建一个新的物理顺序，使用materialized views。
Notes
- 带有_sign=-1的行不会从表中物理删除。
- MaterializeMySQL引擎不支持级联UPDATE/DELETE查询。
- 复制很容易被破坏。
- 禁止对数据库和表进行手工操作。
- MaterializeMySQL受optimize_on_insert设置的影响。当MySQL服务器中的表发生变化时，数据会合并到MaterializeMySQL数据库中相应的表中。
3、Lazy
作用
- 在最后一次访问之后，只在RAM中保存expiration_time_in_seconds秒。只能用于*Log表。
- 它是为存储许多小的*Log表而优化的，对于这些表，访问之间有很长的时间间隔。
语法：
代码语言：javascript复制CREATE DATABASE testlazy ENGINE = Lazy(expiration_time_in_seconds);4、Atomic
它支持非阻塞的DROP TABLE和RENAME TABLE查询和原子的EXCHANGE TABLES t1 AND t2查询。默认情况下使用Atomic数据库引擎。
语法
代码语言：javascript复制 CREATE DATABASE dbname[ ENGINE = Atomic];数据库Atomic中的所有表都有唯一的UUID，并将数据存储在目录/clickhouse_path/store/xxx/xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy/，其中xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy是该表的UUID，UUID是自动生成的。
5、PostgreSQL
作用
- 允许连接到远程PostgreSQL服务。支持读写操作(SELECT和INSERT查询)，以在ClickHouse和PostgreSQL之间交换数据。
- 在SHOW TABLES和DESCRIBE TABLE查询的帮助下，从远程PostgreSQL实时访问表列表和表结构。
- 支持表结构修改(ALTER TABLE ... ADD|DROP COLUMN)。如果use_table_cache参数(参见下面的引擎参数)设置为1，则会缓存表结构，不会检查是否被修改，但可以用DETACH和ATTACH查询进行更新。
语法：
代码语言：javascript复制CREATE DATABASE dbname
ENGINE = PostgreSQL('host:port', 'database', 'username', 'password'[, `use_table_cache`]);支持的数据类型
PostgerSQL | ClickHouse |
|---|---|
DATE | Date |
TIMESTAMP | DateTime |
REAL | Float32 |
DOUBLE | Float64 |
DECIMAL, NUMERIC | Decimal |
SMALLINT | Int16 |
INTEGER | Int32 |
BIGINT | Int64 |
SERIAL | UInt32 |
BIGSERIAL | UInt64 |
TEXT, CHAR | String |
INTEGER | Nullable(Int32) |
ARRAY | Array |
6、MaterializedPostgreSQL（实验性）
作用
- 使用PostgreSQL数据库表的初始数据转储创建ClickHouse数据库，并启动复制过程，即执行后台作业，以便在远程PostgreSQL数据库中的PostgreSQL数据库表上发生新更改时应用这些更改。
- ClickHouse服务器作为PostgreSQL副本工作。它读取WAL并执行DML查询。DDL不是复制的，但可以处理
先决
- 在postgresql配置文件中将wal_level设置为
logical，将max_replication_slots设置为2。 - 每个复制表必须具有以下一个replica identity:
- default (主键)
- index
- TOAST不支持值转换。将使用数据类型的默认值。
语法
代码语言：javascript复制CREATE DATABASE [IF NOT EXISTS] dbname [ON CLUSTER cluster]
ENGINE = MaterializedPostgreSQL('host:port', ['database' | database], 'username', 'password') [SETTINGS ...]CREATE DATABASE database1
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_max_block_size = 65536,
materialized_postgresql_tables_list = 'table1,table2,table3';
SELECT * FROM database1.table1;7、Replicated（试验性）
作用
- 该引擎基于Atomic引擎。它支持通过将DDL日志写入ZooKeeper并在给定数据库的所有副本上执行的元数据复制。
- 一个ClickHouse服务器可以同时运行和更新多个复制的数据库。但是同一个复制的数据库不能有多个副本。
语法
代码语言：javascript复制CREATE DATABASE testdb ENGINE = Replicated('zoo_path', 'shard_name', 'replica_name') [SETTINGS ...]node1 :) CREATE DATABASE r ENGINE=Replicated('some/path/r','shard1','replica1');
node2 :) CREATE DATABASE r ENGINE=Replicated('some/path/r','shard1','other_replica');
node3 :) CREATE DATABASE r ENGINE=Replicated('some/path/r','other_shard','{replica}');CREATE TABLE r.rmt (n UInt64) ENGINE=ReplicatedMergeTree ORDER BY n;SELECT cluster, shard_num, replica_num, host_name, host_address, port, is_local
FROM system.clusters WHERE cluster='r';引擎参数
- zoo_path — ZooKeeper地址，同一个ZooKeeper路径对应同一个数据库。
- shard_name — 分片的名字。数据库副本按shard_name分组到分片中。
- replica_name — 副本的名字。同一分片的所有副本的副本名称必须不同。
Note
对于ReplicatedMergeTree表，如果没有提供参数，则使用默认参数:/clickhouse/tables/{uuid}/{shard}和{replica}。这些可以在服务器设置default_replica_path和default_replica_name中更改。宏{uuid}被展开到表的uuid， {shard}和{replica}被展开到服务器配置的值，而不是数据库引擎参数。但是在将来，可以使用Replicated数据库的shard_name和replica_name。
十、ClickHouse数据表引擎
ClickHouse表引擎：表引擎 | ClickHouse文档
表引擎的关键作用
- 数据的存储方式和位置，写到哪里以及从哪里读取数据
- 支持哪些查询以及如何支持
- 并发数据访问
- 索引的使用（如果存在）
- 是否可以执行多线程请求
- 数据复制参数
ClickHouse表引擎有四大类，分别有
- MergeTree合并树引擎
- log日志引擎
- Integration集成引擎
- Special特殊引擎
1、MergeTree合并树引擎
1、MergeTree（合并树）
引擎描述
- MergeTree适用于高负载任务的最通用和功能最强大的表引擎，它支持快速插入数据并进行后续的后台数据处理、支持数据复制、支持分区和其他引擎不支持的其他功能。
MergeTree系列的引擎被设计用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多。
主要特点
- 存储的数据按主键排序
- 如果指定了 分区键 的话，可以使用分区
- 支持数据副本
- 支持数据采样
建表语法
代码语言：javascript复制CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
...
INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]语法示例
代码语言：javascript复制ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTINGS index_granularity=8192MergeTree参数
ENGINE- 引擎名和参数。ENGINE = MergeTree().MergeTree引擎没有参数。ORDER BY— 排序键。如果没有使用PRIMARY KEY显式指定的主键，ClickHouse 会使用排序键作为主键。如果不需要排序，可以使用ORDER BY tuple(). 参考 选择主键PARTITION BY— 分区键 ，可选项。要按月分区，可以使用表达式toYYYYMM(date_column)SAMPLE BY- 用于抽样的表达式，可选项TTL- 指定行存储的持续时间并定义数据片段在硬盘和卷上的移动逻辑的规则列表，可选项。- 表达式中必须存在至少一个
Date或DateTime类型的列，比如：TTL date INTERVAl 1 DAY SETTINGS— 控制MergeTree行为的额外参数，可选项：index_granularity— 索引粒度。索引中相邻的『标记』间的数据行数。默认值8192min_index_granularity_bytes- 允许的最小数据粒度，默认值：1024b。merge_with_ttl_timeout— TTL合并频率的最小间隔时间，单位：秒。默认值: 86400 (1 天)。merge_max_block_size— 在块中进行合并操作时的最大行数限制。默认值：8192storage_policy— 存储策略。 参见 使用具有多个块的设备进行数据存储.max_compress_block_size- 在数据压缩写入表前，未压缩数据块的最大大小。min_compress_block_size- 在数据压缩写入表前，未压缩数据块的最小大小。max_partitions_to_read- 一次查询中可访问的分区最大数。
2、ReplacingMergeTree（唯一合并树）
引擎描述
- 该引擎和 MergeTree 的不同之处在于它会删除排序键值相同的重复项。
- 数据的去重只会在数据合并期间进行。合并会在后台一个不确定的时间进行，因此你无法预先作出计划。有一些数据可能仍未被处理。尽管你可以调用
OPTIMIZE语句发起计划外的合并，但请不要依靠它，因为OPTIMIZE语句会引发对数据的大量读写。 - 因此，
ReplacingMergeTree适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。
建表语法
代码语言：javascript复制CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
) ENGINE = ReplacingMergeTree([ver])
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]ReplacingMergeTree参数
- ver — 版本列。类型为 UInt*, Date 或 DateTime。可选参数。在数据合并的时候，ReplacingMergeTree 从所有具有相同排序键的行中选择一行留下：
- 如果
ver列未指定，保留最后一条。 - 如果
ver列已指定，保留ver值最大的版本。
- 如果
3、SummingMergeTree（求和合并树）
引擎描述
- 该引擎继承自 MergeTree。区别在于，当合并
SummingMergeTree表的数据片段时，ClickHouse 会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值。如果主键的组合方式使得单个键值对应于大量的行，则可以显著的减少存储空间并加快数据查询的速度。 - 我们推荐将该引擎和
MergeTree一起使用。例如，在准备做报告的时候，将完整的数据存储在MergeTree表中，并且使用SummingMergeTree来存储聚合数据。这种方法可以使你避免因为使用不正确的主键组合方式而丢失有价值的数据。
建表语法
代码语言：javascript复制CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
) ENGINE = SummingMergeTree([columns])
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]SummingMergeTree 的参数
columns- 包含了将要被汇总的列的列名的元组。可选参数。 所选的列必须是数值类型，并且不可位于主键中。- 如果没有指定
columns，ClickHouse 会把所有不在主键中的数值类型的列都进行汇总。
语法示例
代码语言：javascript复制CREATE TABLE summtt (
key UInt32,
value UInt32
) ENGINE = SummingMergeTree()
ORDER BY keyINSERT INTO summtt Values(1,1),(1,2),(2,1)SELECT key, sum(value) FROM summtt GROUP BY key4、AggregatingMergeTree（聚合合并树）
引擎描述
- 该引擎继承自 MergeTree，并改变了数据片段的合并逻辑。 ClickHouse 会将一个数据片段内所有具有相同主键（准确的说是 排序键）的行替换成一行，这一行会存储一系列聚合函数的状态。
- 可以使用 AggregatingMergeTree 表来做增量数据的聚合统计，包括物化视图的数据聚合。
- 引擎使用以下类型来处理所有列：
- [AggregateFunction](https://clickhouse.tech/docs/zh/sql-reference/data-types/aggregatefunction/)
- [SimpleAggregateFunction](https://clickhouse.tech/docs/zh/sql-reference/data-types/simpleaggregatefunction/)SimpleAggregateFunction 的性能优于具有相同聚合函数的 AggregateFunctionAggregatingMergeTree 没有任何额外的设置参数，在分区合并时，在每个数据分区内，会按照ORDER BY聚合。使用何种聚合函数，以及针对哪些列字段计算，是通过定义AggregateFunction 数据类型实现的。数据的写入和查询都与寻常不同。在写入数据时，需要调用*State函数；而在查询数据 时，则需要调用相应的*Merge函数。建表语句
代码语言：javascript复制CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
) ENGINE = AggregatingMergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[TTL expr]
[SETTINGS name=value, ...]聚合物化视图示例
代码语言：javascript复制CREATE MATERIALIZED VIEW test.basic
ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)
AS SELECT
CounterID,
StartDate,
sumState(Sign) AS Visits,
uniqState(UserID) AS Users
FROM test.visits
GROUP BY CounterID, StartDate; 向 test.visits 表中插入数据
INSERT INTO test.visits ...数据会同时插入到表和视图中，并且视图 test.basic 会将里面的数据聚合。
要获取聚合数据，我们需要在 test.basic 视图上执行类似 SELECT ... GROUP BY ... 这样的查询
SELECT
StartDate,
sumMerge(Visits) AS Visits,
uniqMerge(Users) AS Users
FROM test.basic
GROUP BY StartDate
ORDER BY StartDate;5、CollapsingMergeTree（折叠合并树）
引擎描述
- 该引擎继承于 MergeTree，并在数据块合并算法中添加了折叠行的逻辑。
CollapsingMergeTree会异步的删除（折叠）这些除了特定列Sign有1和-1的值以外，其余所有字段的值都相等的成对的行。没有成对的行会被保留。更多的细节请看折叠部分。- 因此，该引擎可以显著的降低存储量并提高
SELECT查询效率。
建表语句
代码语言：javascript复制CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
) ENGINE = CollapsingMergeTree(sign)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]CollapsingMergeTree 参数
sign— 类型列的名称：1是“状态”行，-1是“取消”行。- 列数据类型 —
Int8。
语句示例
代码语言：javascript复制CREATE TABLE UAct(
UserID UInt64,
PageViews UInt8,
Duration UInt8,
Sign Int8
) ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserIDINSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1)
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1),(4324182021466249494, 6, 185, 1)SELECT * FROM UAct代码语言：javascript复制┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐ │ 4324182021466249494 │ 5 │ 146 │ -1 │ │ 4324182021466249494 │ 6 │ 185 │ 1 │ └─────────────────────┴───────────┴──────────┴──────┘ ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐ │ 4324182021466249494 │ 5 │ 146 │ 1 │ └─────────────────────┴───────────┴──────────┴──────┘
SELECT * FROM UAct FINAL┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐ │ 4324182021466249494 │ 6 │ 185 │ 1 │ └─────────────────────┴───────────┴──────────┴──────┘
这种查询数据的方法是非常低效的。不要在大表中使用它。
6、VersionedCollapsingMergeTree（版本折叠合并树）
引擎描述
- 允许快速写入不断变化的对象状态。
- 删除后台中的旧对象状态。 这显着降低了存储体积。
- 允许以多个线程的任何顺序插入数据
Version列有助于正确折叠行，即使它们以错误的顺序插入
建表语句
代码语言：javascript复制CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
) ENGINE = VersionedCollapsingMergeTree(sign, version)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]引擎参数
- sign — 指定行类型的列名: 1 是一个 “state” 行, -1 是一个 “cancel” 行。列数据类型应为 Int8.
- version — 指定对象状态版本的列名。列数据类型应为 UInt*.
底层算法
- 当ClickHouse合并数据部分时，它会删除具有相同主键和版本但
Sign值不同的一对行. 行的顺序并不重要。 - 当ClickHouse插入数据时，它会按主键对行进行排序。 如果
Version列不在主键中，ClickHouse将其隐式添加到主键作为最后一个字段并使用它进行排序。
引擎规则
- 如果有必要从VersionedCollapsingMergeTree 表中得到完全 “collapsed” 的数据，聚合是必需的。
- 要完成折叠，请使用 GROUP BY 考虑符号的子句和聚合函数。 例如，要计算数量，请使用 sum(Sign) 而不是 count(). 要计算的东西的总和，使用 sum(Sign * x) 而不是 sum(x)，并添加 HAVING sum(Sign) > 0.
- 聚合 count, sum 和 avg 可以这样计算。 聚合 uniq 如果对象至少具有一个非折叠状态，则可以计算。 聚合 min 和 max 无法计算是因为 VersionedCollapsingMergeTree 不保存折叠状态值的历史记录。
- 如果您需要提取数据 “collapsing” 但是，如果没有聚合（例如，要检查是否存在其最新值与某些条件匹配的行），则可以使用 FINAL 修饰 FROM 条件这种方法效率低下，不应与大型表一起使用。
语句示例
代码语言：javascript复制CREATE TABLE UAct (
UserID UInt64,
PageViews UInt8,
Duration UInt8,
Sign Int8,
Version UInt8
) ENGINE = VersionedCollapsingMergeTree(Sign, Version)
ORDER BY UserID;INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1, 1);
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1, 1),(4324182021466249494, 6, 185, 1, 2);SELECT * FROM UAct代码语言：txt复制┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐ │ 4324182021466249494 │ 5 │ 146 │ 1 │ 1 │ └─────────────────────┴───────────┴──────────┴──────┴─────────┘ ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐ │ 4324182021466249494 │ 5 │ 146 │ -1 │ 1 │ │ 4324182021466249494 │ 6 │ 185 │ 1 │ 2 │ └─────────────────────┴───────────┴──────────┴──────┴─────────┘
我们在这里看到了什么，折叠的部分在哪里？我们使用两个创建了两个数据部分 `INSERT` 查询。 该 `SELECT` 查询是在两个线程中执行的，结果是行的随机顺序。由于数据部分尚未合并，因此未发生折叠。 ClickHouse在我们无法预测的未知时间点合并数据部分。 这就是为什么我们需要聚合:SELECT
UserID,
sum(PageViews * Sign) AS PageViews,
sum(Duration * Sign) AS Duration,
Version
FROM UAct
GROUP BY UserID, Version
HAVING sum(Sign) > 0┌──────────────UserID─┬─PageViews─┬─Duration─┬─Version─┐ │ 4324182021466249494 │ 6 │ 185 │ 2 │ └─────────────────────┴───────────┴──────────┴─────────┘
如果我们不需要聚合，并希望强制折叠，我们可以使用 FINAL 修饰符 FROM 条款
SELECT * FROM UAct FINAL┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐ │ 4324182021466249494 │ 6 │ 185 │ 1 │ 2 │ └─────────────────────┴───────────┴──────────┴──────┴─────────┘
这是一个非常低效的方式来选择数据。 不要把它用于数据量大的表。
7、GraphiteMergeTree（石墨合并树）
引擎描述
代码语言：txt复制 该引擎用来对 [Graphite](https://graphite.readthedocs.io/en/latest/index.html)数据进行瘦身及汇总。对于想使用CH来存储Graphite数据的开发者来说可能有用。如果不需要对Graphite数据做汇总，那么可以使用任意的CH表引擎；但若需要，那就采用 `GraphiteMergeTree` 引擎。它能减少存储空间，同时能提高Graphite数据的查询效率。建表语句
代码语言：javascript复制CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
Path String,
Time DateTime,
Value <Numeric_type>,
Version <Numeric_type>
...
) ENGINE = GraphiteMergeTree(config_section)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]含有Graphite数据集的表应该包含以下的数据列：
- 指标名称(Graphite sensor)，数据类型：
String - 指标的时间度量，数据类型：
DateTime - 指标的值，数据类型：任意数值类型
- 指标的版本号，数据类型： 任意数值类型
CH以最大的版本号保存行记录，若版本号相同，保留最后写入的数据。
引擎参数
config_section- 配置文件中标识汇总规则的节点名称
汇总配置的参数
代码语言：txt复制 汇总的配置参数由服务器配置的 [graphite_rollup](https://clickhouse.com/docs/zh/operations/server-configuration-parameters/settings/#server_configuration_parameters-graphite) 参数定义。参数名称可以是任意的。允许为多个不同表创建多组配置并使用。汇总配置的结构如下：所需的列
path_column_name— 保存指标名称的列名 (Graphite sensor). 默认值:Path.time_column_name— 保存指标时间度量的列名. Default value:Time.value_column_name— The name of the column storing the value of the metric at the time set intime_column_name.默认值:Value.version_column_name- 保存指标的版本号列. 默认值:Timestamp.
模式Patterns
patterns 的结构：
pattern
regexp
function
pattern
regexp
age precision
...
pattern
regexp
function
age precision
...
pattern
...
default
function
age precision
...模式必须严格按顺序配置：
- 不含
functionorretention的Patterns - 同时含有
functionandretention的Patterns default的Patterns.
CH在处理行记录时，会检查 pattern节点的规则。每个 pattern（含default）节点可以包含 function 用于聚合操作，或retention参数，或者两者都有。如果指标名称和 regexp相匹配，相应 pattern的规则会生效；否则，使用 default节点的规则。
pattern 和 default 节点的字段设置:
regexp– 指标名的pattern.age– 数据的最小存活时间(按秒算).precision– 按秒来衡量数据存活时间时的精确程度. 必须能被86400整除 (一天的秒数).function– 对于存活时间在[age, age precision]之内的数据，需要使用的聚合函数
配置示例
代码语言：javascript复制<graphite_rollup>
<version_column_name>Version</version_column_name>
<pattern>
<regexp>click_cost</regexp>
<function>any</function>
<retention>
<age>0</age>
<precision>5</precision>
</retention>
<retention>
<age>86400</age>
<precision>60</precision>
</retention>
</pattern>
<default>
<function>max</function>
<retention>
<age>0</age>
<precision>60</precision>
</retention>
<retention>
<age>3600</age>
<precision>300</precision>
</retention>
<retention>
<age>86400</age>
<precision>3600</precision>
</retention>
</default>
</graphite_rollup>2、log日志引擎
代码语言：txt复制 日志引擎分别有StripeLog 、Log、TinyLog。这些引擎是为了需要写入许多小数据量（少于一百万行）的表的场景而开发的。共同属性
- 数据存储在磁盘上。
- 写入时将数据追加在文件末尾。
- 不支持突变操作。
- 不支持索引。这意味着
SELECT在范围查询时效率不高。 - 非原子地写入数据。如果某些事情破坏了写操作，例如服务器的异常关闭，你将会得到一张包含了损坏数据的表。
Log 和 StripeLog 引擎支持
- 并发访问数据的锁。
INSERT请求执行过程中表会被锁定，并且其他的读写数据的请求都会等待直到锁定被解除。如果没有写数据的请求，任意数量的读请求都可以并发执行。
- 并行读取数据。
- 在读取数据时，ClickHouse 使用多线程。 每个线程处理不同的数据块。
Log 、 StripeLog 和 StripeLog 引擎的差异
- Log 引擎为表中的每一列使用不同的文件。
- StripeLog 将所有的数据存储在一个文件中。
- TinyLog 引擎不支持并行读取和并发数据访问，并将每一列存储在不同的文件中。它比其余两种支持并行读取的引擎的读取速度更慢，并且使用了和 Log 引擎同样多的描述符。你可以在简单的低负载的情景下使用它。
- StripeLog 引擎在操作系统中使用更少的描述符，但是 Log 引擎提供更高的读性能。 TinyLog 引擎是该系列中最简单的引擎并且提供了最少的功能和最低的性能。
1、TinyLog
引擎描述
- 最简单的表引擎，用于将数据存储在磁盘上。
- 每列都存储在单独的压缩文件中。写入时，数据将附加到文件末尾。
- TinyLog 表用于小批量处理的中间数据。
并发数据访问不受任何限制：
- 如果同时从表中读取并在不同的查询中写入，则读取操作将抛出异常
- 如果同时写入多个查询中的表，则数据将被破坏。
底层原理
- 这种表引擎的典型用法是 write-once：首先只写入一次数据，然后根据需要多次读取。查询在单个流中执行。
- 此引擎适用于相对较小的表（建议最多1,000,000行）。如果您有许多小表，则使用此表引擎是适合的，因为它比Log引擎更简单（需要打开的文件更少）。
- 当您拥有大量小表时，可能会导致性能低下，但在可能已经在其它 DBMS 时使用过，则您可能会发现切换使用 TinyLog 类型的表更容易。不支持索引。
2、StripeLog
适用场景
- 该引擎属于日志引擎系列。请在日志引擎系列文章中查看引擎的共同属性和差异。
- 在你需要写入许多小数据量（小于一百万行）的表的场景下使用这个引擎。
- StripeLog 引擎不支持 ALTER UPDATE 和 ALTER DELETE 操作。
写数据
StripeLog引擎将所有列存储在一个文件中。- 对每一次
Insert请求，ClickHouse 将数据块追加在表文件的末尾，逐列写入。
ClickHouse 为每张表写入以下文件：
data.bin— 数据文件。index.mrk— 带标记的文件。标记包含了已插入的每个数据块中每列的偏移量。
读数据
- 带标记的文件使得 ClickHouse 可以并行的读取数据。
- 这意味着 SELECT 请求返回行的顺序是不可预测的。使用 ORDER BY 子句对行进行排序。
建表语句
代码语言：javascript复制CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
column1_name [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
column2_name [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
) ENGINE = StripeLog插入数据（使用两次 INSERT 请求会在 data.bin 文件中创建两个数据块）
INSERT INTO stripe_log_table VALUES (now(),'REGULAR','The first regular message')
INSERT INTO stripe_log_table VALUES (now(),'REGULAR','The second regular message'),(now(),'WARNING','The first warning message')直接查询会根据线程和数据块返回数据（顺序不确定）
代码语言：javascript复制SELECT * FROM stripe_log_table┌───────────timestamp─┬─message_type─┬─message────────────────────┐ │ 2019-01-18 14:27:32 │ REGULAR │ The second regular message │ │ 2019-01-18 14:34:53 │ WARNING │ The first warning message │ └─────────────────────┴──────────────┴────────────────────────────┘ ┌───────────timestamp─┬─message_type─┬─message───────────────────┐ │ 2019-01-18 14:23:43 │ REGULAR │ The first regular message │ └─────────────────────┴──────────────┴───────────────────────────┘
对结果排序查询
代码语言：javascript复制SELECT * FROM stripe_log_table ORDER BY timestamp┌───────────timestamp─┬─message_type─┬─message────────────────────┐ │ 2019-01-18 14:23:43 │ REGULAR │ The first regular message │ │ 2019-01-18 14:27:32 │ REGULAR │ The second regular message │ │ 2019-01-18 14:34:53 │ WARNING │ The first warning message │ └─────────────────────┴──────────────┴────────────────────────────┘
3、Log
Log 与 TinyLog 的不同之处
- “标记” 的小文件与列文件存在一起。这些标记写在每个数据块上，并且包含偏移量，这些偏移量指示从哪里开始读取文件以便跳过指定的行数。
- 这使得可以在多个线程中读取表数据。对于并发数据访问，可以同时执行读取操作，而写入操作则阻塞读取和其它写入。
- Log引擎不支持索引。同样，如果写入表失败，则该表将被破坏，并且从该表读取将返回错误。Log引擎适用于临时数据，write-once 表以及测试或演示目的。
3、Integration集成引擎
1、Kafka
Kafka 特性：
- 发布或者订阅数据流。
- 容错存储机制。
- 处理流数据。
老版格式
代码语言：javascript复制Kafka(kafka_broker_list, kafka_topic_list, kafka_group_name, kafka_format
[, kafka_row_delimiter, kafka_schema, kafka_num_consumers])新版格式
代码语言：javascript复制Kafka SETTINGS
kafka_broker_list = 'localhost:9092',
kafka_topic_list = 'topic1,topic2',
kafka_group_name = 'group1',
kafka_format = 'JSONEachRow',
kafka_row_delimiter = 'n',
kafka_schema = '',
kafka_num_consumers = 2必要参数：
kafka_broker_list– 以逗号分隔的 brokers 列表 (localhost:9092)。kafka_topic_list– topic 列表 (my_topic)。kafka_group_name– Kafka 消费组名称 (group1)。如果不希望消息在集群中重复，请在每个分片中使用相同的组名。kafka_format– 消息体格式。使用与 SQL 部分的FORMAT函数相同表示方法，例如JSONEachRow。了解详细信息，请参考Formats部分。
可选参数：
kafka_row_delimiter- 每个消息体（记录）之间的分隔符。kafka_schema– 如果解析格式需要一个 schema 时，此参数必填。例如，capnproto 需要 schema 文件路径以及根对象schema.capnp:Message的名字。kafka_num_consumers– 单个表的消费者数量。默认值是：1，如果一个消费者的吞吐量不足，则指定更多的消费者。消费者的总数不应该超过 topic 中分区的数量，因为每个分区只能分配一个消费者。
语法示例
代码语言：javascript复制 CREATE TABLE queue (
timestamp UInt64,
level String,
message String
) ENGINE = Kafka('localhost:9092', 'topic', 'group1', 'JSONEachRow');
SELECT * FROM queue LIMIT 5;
CREATE TABLE queue2 (
timestamp UInt64,
level String,
message String
) ENGINE = Kafka SETTINGS kafka_broker_list = 'localhost:9092',
kafka_topic_list = 'topic',
kafka_group_name = 'group1',
kafka_format = 'JSONEachRow',
kafka_num_consumers = 4;
CREATE TABLE queue2 (
timestamp UInt64,
level String,
message String
) ENGINE = Kafka('localhost:9092', 'topic', 'group1')
SETTINGS kafka_format = 'JSONEachRow',
kafka_num_consumers = 4;消费的消息会被自动追踪，因此每个消息在不同的消费组里只会记录一次。如果希望获得两次数据，则使用另一个组名创建副本。
消费组可以灵活配置并且在集群之间同步。例如，如果群集中有10个主题和5个表副本，则每个副本将获得2个主题。 如果副本数量发生变化，主题将自动在副本中重新分配。
SELECT 查询对于读取消息并不是很有用（调试除外），因为每条消息只能被读取一次。使用物化视图创建实时线程更实用。您可以这样做：
- 使用引擎创建一个 Kafka 消费者并作为一条数据流。
- 创建一个结构表。
- 创建物化视图，改视图会在后台转换引擎中的数据并将其放入之前创建的表中。
当 MATERIALIZED VIEW 添加至引擎，它将会在后台收集数据。可以持续不断地从 Kafka 收集数据并通过 SELECT 将数据转换为所需要的格式。
语法示例
代码语言：javascript复制 CREATE TABLE queue (
timestamp UInt64,
level String,
message String
) ENGINE = Kafka('localhost:9092', 'topic', 'group1', 'JSONEachRow');
CREATE TABLE daily (
day Date,
level String,
total UInt64
) ENGINE = SummingMergeTree(day, (day, level), 8192);
CREATE MATERIALIZED VIEW consumer TO daily
AS SELECT toDate(toDateTime(timestamp)) AS day, level, count() as total
FROM queue GROUP BY day, level;
SELECT level, sum(total) FROM daily GROUP BY level;为了提高性能，接受的消息被分组为 max_insert_block_size 大小的块。如果未在 stream_flush_interval_ms 毫秒内形成块，则不关心块的完整性，都会将数据刷新到表中。
停止接收主题数据或更改转换逻辑，请 detach 物化视图：
代码语言：javascript复制 DETACH TABLE consumer;
ATTACH TABLE consumer;如果使用 ALTER 更改目标表，为了避免目标表与视图中的数据之间存在差异，推荐停止物化视图。
配置
与 GraphiteMergeTree 类似，Kafka 引擎支持使用ClickHouse配置文件进行扩展配置。可以使用两个配置键：全局 (kafka) 和 主题级别 (kafka_*)。首先应用全局配置，然后应用主题级配置（如果存在）。
<!-- Global configuration options for all tables of Kafka engine type -->
<kafka>
<debug>cgrp</debug>
<auto_offset_reset>smallest</auto_offset_reset>
</kafka>
<!-- Configuration specific for topic "logs" -->
<kafka_logs>
<retry_backoff_ms>250</retry_backoff_ms>
<fetch_min_bytes>100000</fetch_min_bytes>
</kafka_logs>有关详细配置选项列表，请参阅 librdkafka配置参考。在 ClickHouse 配置中使用下划线 (_) ，并不是使用点 (.)。例如，check.crcs=true 将是 <check_crcs>true</check_crcs>。
2、MySQL
引擎描述
MySQL 引擎可以对存储在远程 MySQL 服务器上的数据执行 SELECT 查询
语法格式
代码语言：javascript复制MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);参数说明
host:port— MySQL 服务器地址。database— 数据库的名称。table— 表名称。user— 数据库用户。password— 用户密码。replace_query— 将INSERT INTO查询是否替换为REPLACE INTO的标志。如果replace_query=1，则替换查询'on_duplicate_clause'— 将ON DUPLICATE KEY UPDATE 'on_duplicate_clause'表达式添加到INSERT查询语句中。例如：impression = VALUES(impression) impression。如果需要指定'on_duplicate_clause'，则需要设置replace_query=0。如果同时设置replace_query = 1和'on_duplicate_clause'，则会抛出异常。
MySQL 引擎不支持 可为空 数据类型，因此，当从MySQL表中读取数据时，NULL 将转换为指定列类型的默认值（通常为0或空字符串）。
3、ODBC
引擎描述
ClickHouse 允许通过 ODBC 方式连接到外部数据库。为了安全地实现 ODBC 连接，ClickHouse 使用了一个独立程序 clickhouse-odbc-bridge
语法格式
代码语言：javascript复制CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1],
name2 [type2],
...
) ENGINE = ODBC(connection_settings, external_database, external_table)表结构可以与源表结构不同:
- 列名应与源表中的列名相同，但您可以按任何顺序使用其中的一些列。
- 列类型可能与源表中的列类型不同。 ClickHouse尝试将数值映射 到ClickHouse的数据类型。
- 设置
external_table_functions_use_nulls来定义如何处理 Nullable 列. 默认值是 true, 当设置为 false 时 - 表函数将不会使用 nullable 列，而是插入默认值来代替 null. 这同样适用于数组数据类型中的 null 值.
引擎参数
connection_settings— 在odbc.ini配置文件中，连接配置的名称.external_database— 在外部 DBMS 中的数据库名.external_table—external_database中的表名.
MySQL权限设置
代码语言：javascript复制CREATE USER 'clickhouse'@'localhost' IDENTIFIED BY 'clickhouse';
GRANT ALL PRIVILEGES ON *.* TO 'clickhouse'@'clickhouse' WITH GRANT OPTION;/etc/odbc.ini中配置连接
[mysqlconn]
DRIVER = /usr/local/lib/libmyodbc5w.so
SERVER = 127.0.0.1
PORT = 3306
DATABASE = test
USERNAME = clickhouse
PASSWORD = clickhousemysql> CREATE TABLE `test`.`test` (
-> `int_id` INT NOT NULL AUTO_INCREMENT,
-> `int_nullable` INT NULL DEFAULT NULL,
-> `float` FLOAT NOT NULL,
-> `float_nullable` FLOAT NULL DEFAULT NULL,
-> PRIMARY KEY (`int_id`));
Query OK, 0 rows affected (0,09 sec)
mysql> insert into test (`int_id`, `float`) VALUES (1,2);
Query OK, 1 row affected (0,00 sec)
mysql> select * from test;
-------- -------------- ------- ----------------
| int_id | int_nullable | float | float_nullable |
-------- -------------- ------- ----------------
| 1 | NULL | 2 | NULL |
-------- -------------- ------- ----------------
1 row in set (0,00 sec)ClickHouse中的表，从MySQL表中检索数据:
代码语言：javascript复制CREATE TABLE odbc_t
(
`int_id` Int32,
`float_nullable` Nullable(Float32)
)
ENGINE = ODBC('DSN=mysqlconn', 'test', 'test')SELECT * FROM odbc_t┌─int_id─┬─float_nullable─┐ │ 1 │ ᴺᵁᴸᴸ │ └────────┴────────────────┘
另外参阅
- ODBC 外部字典
- ODBC 表函数
4、JDBC
引擎描述
- 允许CH通过 JDBC 连接到外部数据库。
- 要实现JDBC连接，CH需要使用以后台进程运行的程序 clickhouse-jdbc-bridge。
- 该引擎支持 Nullable 数据类型。
语法格式
代码语言：javascript复制CREATE TABLE [IF NOT EXISTS] [db.]table_name
(
columns list...
)
ENGINE = JDBC(datasource_uri, external_database, external_table)引擎参数
- datasource_uri — 外部DBMS的URI或名字.
- URI格式: jdbc:<driver_name>://<host_name>:<port>/?user=<username>&password=<password>.
- MySQL示例: jdbc:mysql://localhost:3306/?user=root&password=root.
- external_database — 外部DBMS的数据库名.
- external_table — external_database中的外部表名或类似select * from table1 where column1=1的查询语句.
语法示例
代码语言：javascript复制mysql> CREATE TABLE `test`.`test` (
-> `int_id` INT NOT NULL AUTO_INCREMENT,
-> `int_nullable` INT NULL DEFAULT NULL,
-> `float` FLOAT NOT NULL,
-> `float_nullable` FLOAT NULL DEFAULT NULL,
-> PRIMARY KEY (`int_id`));
Query OK, 0 rows affected (0,09 sec)
mysql> insert into test (`int_id`, `float`) VALUES (1,2);
Query OK, 1 row affected (0,00 sec)
mysql> select * from test;
------ ---------- ----- ----------
| int_id | int_nullable | float | float_nullable |
------ ---------- ----- ----------
| 1 | NULL | 2 | NULL |
------ ---------- ----- ----------
1 row in set (0,00 sec)CREATE TABLE jdbc_table
(
`int_id` Int32,
`int_nullable` Nullable(Int32),
`float` Float32,
`float_nullable` Nullable(Float32)
)
ENGINE JDBC('jdbc:mysql://localhost:3306/?user=root&password=root', 'test', 'test')SELECT * FROM jdbc_table代码语言：javascript复制┌─int_id─┬─int_nullable─┬─float─┬─float_nullable─┐ │ 1 │ ᴺᵁᴸᴸ │ 2 │ ᴺᵁᴸᴸ │ └────────┴──────────────┴───────┴────────────────┘
INSERT INTO jdbc_table(`int_id`, `float`)
SELECT toInt32(number), toFloat32(number * 1.0) FROM system.numbers5、HDFS
引擎描述
- 这个引擎提供了与 Apache Hadoop 生态系统的集成，允许通过 ClickHouse 管理 HDFS 上的数据。这个引擎类似于
- 文件 和 URL 引擎，但提供了 Hadoop 的特定功能。
语法格式
代码语言：javascript复制ENGINE = HDFS(URI, format)参数解析
URI参数是 HDFS 中整个文件的 URI。format参数指定一种可用的文件格式。 执行SELECT查询时，格式必须支持输入，以及执行INSERT查询时，格式必须支持输出. 你可以在 格式 章节查看可用的格式。- 路径部分
URI可能包含 glob 通配符。 在这种情况下，表将是只读的。
语法示例
代码语言：javascript复制CREATE TABLE hdfs_engine_table (name String, value UInt32) ENGINE=HDFS('hdfs://hdfs1:9000/other_storage', 'TSV')INSERT INTO hdfs_engine_table VALUES ('one', 1), ('two', 2), ('three', 3)SELECT * FROM hdfs_engine_table LIMIT 6引擎特点
- 读取和写入可以并行
- 不支持:
ALTER和SELECT...SAMPLE操作。- 索引。
- 复制。
路径中的通配符
代码语言：txt复制 多个路径组件可以具有 globs。 对于正在处理的文件应该存在并匹配到整个路径模式。 文件列表的确定是在 `SELECT` 的时候进行（而不是在 `CREATE` 的时候）。*— 替代任何数量的任何字符，除了/以及空字符串。?— 代替任何单个字符.{some_string,another_string,yet_another_one}— 替代任何字符串'some_string', 'another_string', 'yet_another_one'.{N..M}— 替换 N 到 M 范围内的任何数字，包括两个边界的值.
带 {} 的结构类似于 远程 表函数。
通配符示例
假设我们在 HDFS 上有几个 TSV 格式的文件，文件的 URI 如下:
- ‘hdfs://hdfs1:9000/some_dir/some_file_1’
- ‘hdfs://hdfs1:9000/some_dir/some_file_2’
- ‘hdfs://hdfs1:9000/some_dir/some_file_3’
- ‘hdfs://hdfs1:9000/another_dir/some_file_1’
- ‘hdfs://hdfs1:9000/another_dir/some_file_2’
- ‘hdfs://hdfs1:9000/another_dir/some_file_3’
以下方法可以创建由所有六个文件组成的表
代码语言：javascript复制CREATE TABLE table_with_range (name String, value UInt32) ENGINE = HDFS('hdfs://hdfs1:9000/{some,another}_dir/some_file_{1..3}', 'TSV')CREATE TABLE table_with_question_mark (name String, value UInt32) ENGINE = HDFS('hdfs://hdfs1:9000/{some,another}_dir/some_file_?', 'TSV')CREATE TABLE table_with_asterisk (name String, value UInt32) ENGINE = HDFS('hdfs://hdfs1:9000/{some,another}_dir/*', 'TSV')创建具有名为文件的表 file000, file001, … , file999:
CREARE TABLE big_table (name String, value UInt32) ENGINE = HDFS('hdfs://hdfs1:9000/big_dir/file{0..9}{0..9}{0..9}', 'CSV') 注意：如果文件列表包含带有前导零的数字范围，请单独使用带有大括号的构造或使用 ?.
HDFS Namenode HA支持
libhdfs3 支持 HDFS namenode HA
从一个HDFS节点 复制 hdfs-site.xml 文件到 /etc/clickhouse-server/ 目录下
代码语言：javascript复制cp /usr/local/hadoop/hadoop/etc/hadoop/hdfs-site.xml /etc/clickhouse-server/将一下配置代码添加到 clickhouse 的配置文件中
代码语言：javascript复制vim /etc/clickhouse-server/config.xml <!-- hdfs引擎集成，HA配置 -->
<hdfs>
<libhdfs3_conf>/etc/clickhouse-server/hdfs-site.xml</libhdfs3_conf>
</hdfs>chown clickhouse:clickhouse /etc/clickhouse-server/config.xml
grep hdfs /etc/clickhouse-server/config.xml重启 clickhouse 服务
代码语言：javascript复制systemctl daemon-reload
systemctl restart clickhouse-server.service CREATE TABLE hdfs_user_csv (name String, age UInt32) ENGINE=HDFS('hdfs:/ns1/data/clickhouse/user.csv', 'CSV')6、MongoDB
引擎描述
- MongoDB 引擎是只读表引擎，允许从远程 MongoDB 集合中读取数据(
SELECT查询)。 - 引擎只支持非嵌套的数据类型。不支持
INSERT查询。
CREATE TABLE [IF NOT EXISTS] [db.]table_name
(
name1 [type1],
name2 [type2],
...
) ENGINE = MongoDB(host:port, database, collection, user, password);引擎参数
- host:port — MongoDB 服务器地址
- database — 数据库名称
- collection — 集合名称
- user — MongoDB 用户
- password — 用户密码
语法示例
代码语言：javascript复制CREATE TABLE mongo_table
(
key UInt64,
data String
) ENGINE = MongoDB('mongo1:27017', 'test', 'simple_table', 'testuser', 'clickhouse');SELECT COUNT() FROM mongo_table;6、PostgreSQL
引擎描述
- PostgreSQL 引擎允许 ClickHouse 对存储在远程 PostgreSQL 服务器上的数据执行
SELECT和INSERT查询
语法格式
代码语言：javascript复制CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
...
) ENGINE = PostgreSQL('host:port', 'database', 'table', 'user', 'password'[, `schema`]);表结构可以与 PostgreSQL 源表结构不同:
- 列名应与 PostgreSQL 源表中的列名相同，但您可以按任何顺序使用其中的一些列。
- 列类型可能与源表中的列类型不同。 ClickHouse尝试将数值映射 到ClickHouse的数据类型。
- 设置
external_table_functions_use_nulls来定义如何处理 Nullable 列. 默认值是 1, 当设置为 0 时 - 表函数将不会使用 nullable 列，而是插入默认值来代替 null. 这同样适用于数组数据类型中的 null 值.
引擎参数
host:port— PostgreSQL 服务器地址database— 数据库名称table— 表名称user— PostgreSQL 用户password— 用户密码schema— Non-default table schema. 可选
底层细节
- 在 PostgreSQL 上的
SELECT查询以COPY (SELECT ...) TO STDOUT的方式在只读 PostgreSQL 事务中运行，每次SELECT查询后提交。 - 简单的
WHERE子句，如=，！=，>，>=，<，<=，和IN是在PostgreSQL 服务器上执行。 - 所有的连接、聚合、排序、
IN [ array ]条件和LIMIT采样约束都是在 PostgreSQL 的查询结束后才在ClickHouse中执行的。 - 在 PostgreSQL 上的
INSERT查询以COPY "table_name" (field1, field2, ... fieldN) FROM STDIN的方式在 PostgreSQL 事务中运行，每条INSERT语句后自动提交。 - PostgreSQL 的
Array类型会被转换为 ClickHouse 数组。 - 要小心 - 一个在 PostgreSQL 中的数组数据，像
type_name[]这样创建，可以在同一列的不同表行中包含不同维度的多维数组。但是在 ClickHouse 中，只允许在同一列的所有表行中包含相同维数的多维数组。
语法示例
代码语言：javascript复制postgres=# CREATE TABLE "public"."test" (
"int_id" SERIAL,
"int_nullable" INT NULL DEFAULT NULL,
"float" FLOAT NOT NULL,
"str" VARCHAR(100) NOT NULL DEFAULT '',
"float_nullable" FLOAT NULL DEFAULT NULL,
PRIMARY KEY (int_id));
CREATE TABLE
postgres=# INSERT INTO test (int_id, str, "float") VALUES (1,'test',2);
INSERT 0 1
postgresql> SELECT * FROM test;
int_id | int_nullable | float | str | float_nullable
-------- -------------- ------- ------ ----------------
1 | | 2 | test |
(1 row)CREATE TABLE default.postgresql_table
(
`float_nullable` Nullable(Float32),
`str` String,
`int_id` Int32
)
ENGINE = PostgreSQL('localhost:5432', 'public', 'test', 'postges_user', 'postgres_password');SELECT * FROM postgresql_table WHERE str IN ('test');┌─float_nullable─┬─str──┬─int_id─┐
│ ᴺᵁᴸᴸ │ test │ 1 │
└────────────────┴──────┴────────┘非默认模式语法示例
代码语言：javascript复制postgres=# CREATE SCHEMA "nice.schema";
postgres=# CREATE TABLE "nice.schema"."nice.table" (a integer);
postgres=# INSERT INTO "nice.schema"."nice.table" SELECT i FROM generate_series(0, 99) as t(i)CREATE TABLE pg_table_schema_with_dots (a UInt32)
ENGINE PostgreSQL('localhost:5432', 'clickhouse', 'nice.table', 'postgrsql_user', 'password', 'nice.schema');7、S3
引擎描述
- 这个引擎提供与Amazon S3生态系统的集成。这个引擎类似于HDFS引擎，但提供了 S3 特有的功能。
语法格式
代码语言：javascript复制CREATE TABLE s3_engine_table (name String, value UInt32)
ENGINE = S3(path, [aws_access_key_id, aws_secret_access_key,] format, [compression])引擎参数
path— 带有文件路径的 Bucket url。在只读模式下支持以下通配符:*,?,{abc,def}和{N..M}其中N,M是数字,'abc','def'是字符串. 更多信息见下文.format— 文件的格式.aws_access_key_id,aws_secret_access_key- AWS 账号的长期凭证. 你可以使用凭证来对你的请求进行认证.参数是可选的. 如果没有指定凭据, 将从配置文件中读取凭据. 更多信息参见 使用 S3 来存储数据.compression— 压缩类型. 支持的值:none,gzip/gz,brotli/br,xz/LZMA,zstd/zst. 参数是可选的. 默认情况下，通过文件扩展名自动检测压缩类型.
语法示例
代码语言：javascript复制CREATE TABLE s3_engine_table (name String, value UInt32) ENGINE=S3('https://storage.yandexcloud.net/my-test-bucket-768/test-data.csv.gz', 'CSV', 'gzip');INSERT INTO s3_engine_table VALUES ('one', 1), ('two', 2), ('three', 3);SELECT * FROM s3_engine_table LIMIT 2;┌─name─┬─value─┐
│ one │ 1 │
│ two │ 2 │
└──────┴───────┘底层细节
- 读取和写入可以是并行的
- 不支持：
ALTER和SELECT...SAMPLE操作- 索引
- 复制
通配符语法
path 参数可以使用类 bash 的通配符来指定多个文件。对于正在处理的文件应该存在并匹配到整个路径模式。 文件列表的确定是在 SELECT 的时候进行（而不是在 CREATE 的时候）。
*— 替代任何数量的任何字符，除了/以及空字符串。?— 代替任何单个字符.{some_string,another_string,yet_another_one}— 替代'some_string', 'another_string', 'yet_another_one'字符串.{N..M}— 替换 N 到 M 范围内的任何数字，包括两个边界的值. N 和 M 可以以 0 开头，比如000..078
带 {} 的结构类似于 远程 表函数。
示例
- ‘https://storage.yandexcloud.net/my-test-bucket-768/some_prefix/some_file_1.csv’
- ‘https://storage.yandexcloud.net/my-test-bucket-768/some_prefix/some_file_2.csv’
- ‘https://storage.yandexcloud.net/my-test-bucket-768/some_prefix/some_file_3.csv’
- ‘https://storage.yandexcloud.net/my-test-bucket-768/another_prefix/some_file_1.csv’
- ‘https://storage.yandexcloud.net/my-test-bucket-768/another_prefix/some_file_2.csv’
- ‘https://storage.yandexcloud.net/my-test-bucket-768/another_prefix/some_file_3.csv’
以下方法可创建由以上六个文件组成的数据表
代码语言：javascript复制CREATE TABLE table_with_range (name String, value UInt32) ENGINE = S3('https://storage.yandexcloud.net/my-test-bucket-768/{some,another}_prefix/some_file_{1..3}', 'CSV');CREATE TABLE table_with_question_mark (name String, value UInt32) ENGINE = S3('https://storage.yandexcloud.net/my-test-bucket-768/{some,another}_prefix/some_file_?', 'CSV');CREATE TABLE table_with_asterisk (name String, value UInt32) ENGINE = S3('https://storage.yandexcloud.net/my-test-bucket-768/{some,another}_prefix/*', 'CSV');使用文件file-000.csv, file-001.csv, … , file-999.csv来创建表
CREATE TABLE big_table (name String, value UInt32) ENGINE = S3('https://storage.yandexcloud.net/my-test-bucket-768/big_prefix/file-{000..999}.csv', 'CSV');如果文件列表中包含有从零开头的数字范围，请对每个数字分别使用带括号的结构，或者使用?。
S3 相关的设置
以下设置可以在查询执行前设置，也可以放在配置文件中。
s3_max_single_part_upload_size- 使用单文件上传至 S3 的对象的最大文件大小。默认值是64Mb。s3_min_upload_part_size- 使用S3多文件块上传时，文件块的最小文件大小。默认值是512Mb。s3_max_redirects- 允许的最大S3重定向跳数。默认值是10。s3_single_read_retries- 单次读取时的最大尝试次数。默认值是4。
安全考虑：如果恶意用户可以指定任意的 S3 网址，s3_max_redirects参数必须设置为零，以避免SSRF攻击；或者，必须在服务器配置中指定remote_host_filter。
8、 EmbeddedRocksDB
代码语言：txt复制 这个引擎允许 ClickHouse 与 [rocksdb](http://rocksdb.org/) 进行集成。语法格式
代码语言：javascript复制CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
) ENGINE = EmbeddedRocksDB PRIMARY KEY(primary_key_name)必要参数:
primary_key_name– 主键，可以是列中的任意列名- 必须指定
primary key, 仅支持主键中的一个列. 主键将被序列化为二进制的rocksdb key. - 主键以外的列将以相应的顺序在二进制中序列化为
rocksdb值. - 带有键
equals或in过滤的查询将被优化为从rocksdb进行多键查询.
语法示例
代码语言：javascript复制CREATE TABLE test
(
`key` String,
`v1` UInt32,
`v2` String,
`v3` Float32,
)
ENGINE = EmbeddedRocksDB
PRIMARY KEY key9、RabbitMQ
代码语言：txt复制 该引擎允许 ClickHouse 与 [RabbitMQ](https://www.rabbitmq.com/) 进行集成.RabbitMQ 可以让你:
- 发布或订阅数据流。
- 在数据流可用时进行处理。
语法格式
代码语言：javascript复制CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
) ENGINE = RabbitMQ SETTINGS
rabbitmq_host_port = 'host:port',
rabbitmq_exchange_name = 'exchange_name',
rabbitmq_format = 'data_format'[,]
[rabbitmq_exchange_type = 'exchange_type',]
[rabbitmq_routing_key_list = 'key1,key2,...',]
[rabbitmq_row_delimiter = 'delimiter_symbol',]
[rabbitmq_schema = '',]
[rabbitmq_num_consumers = N,]
[rabbitmq_num_queues = N,]
[rabbitmq_queue_base = 'queue',]
[rabbitmq_deadletter_exchange = 'dl-exchange',]
[rabbitmq_persistent = 0,]
[rabbitmq_skip_broken_messages = N,]
[rabbitmq_max_block_size = N,]
[rabbitmq_flush_interval_ms = N]必要参数:
rabbitmq_host_port– 主机名:端口号 (比如,localhost:5672).rabbitmq_exchange_name– RabbitMQ exchange 名称.rabbitmq_format– 消息格式. 使用与SQLFORMAT函数相同的标记，如JSONEachRow。 更多信息，请参阅 Formats 部分.
可选参数:
rabbitmq_exchange_type– RabbitMQ exchange 的类型:direct,fanout,topic,headers,consistent_hash. 默认是:fanout.rabbitmq_routing_key_list– 一个以逗号分隔的路由键列表.rabbitmq_row_delimiter– 用于消息结束的分隔符.rabbitmq_schema– 如果格式需要模式定义，必须使用该参数。比如, Cap’n Proto 需要模式文件的路径以及根schema.capnp:Message对象的名称.rabbitmq_num_consumers– 每个表的消费者数量。默认：1。如果一个消费者的吞吐量不够，可以指定更多的消费者.rabbitmq_num_queues– 队列的总数。默认值:1. 增加这个数字可以显著提高性能.rabbitmq_queue_base- 指定一个队列名称的提示。这个设置的使用情况如下.rabbitmq_deadletter_exchange- 为dead letter exchange指定名称。你可以用这个 exchange 的名称创建另一个表，并在消息被重新发布到 dead letter exchange 的情况下收集它们。默认情况下，没有指定 dead letter exchange。Specify name for a dead letter exchange.rabbitmq_persistent- 如果设置为 1 (true), 在插入查询中交付模式将被设置为 2 (将消息标记为 'persistent'). 默认是:0.rabbitmq_skip_broken_messages– RabbitMQ 消息解析器对每块模式不兼容消息的容忍度。默认值：0. 如果rabbitmq_skip_broken_messages = N，那么引擎将跳过 N 个无法解析的 RabbitMQ 消息（一条消息等于一行数据）。rabbitmq_max_block_sizerabbitmq_flush_interval_ms
语法示例
代码语言：javascript复制 CREATE TABLE queue (
key UInt64,
value UInt64,
date DateTime
) ENGINE = RabbitMQ SETTINGS rabbitmq_host_port = 'localhost:5672',
rabbitmq_exchange_name = 'exchange1',
rabbitmq_format = 'JSONEachRow',
rabbitmq_num_consumers = 5,
date_time_input_format = 'best_effort';RabbitMQ 服务器配置应使用 ClickHouse 配置文件添加。
必要配置:
代码语言：javascript复制 <rabbitmq>
<username>root</username>
<password>clickhouse</password>
</rabbitmq>可选配置:
代码语言：javascript复制 <rabbitmq>
<vhost>clickhouse</vhost>
</rabbitmq>引擎描述
SELECT对于读取消息不是特别有用（除了调试），因为每个消息只能读取一次。使用物化视图创建实时线程更为实用。要做到这一点:
- 使用引擎创建一个 RabbitMQ 消费者，并将其视为一个数据流。
- 创建一个具有所需结构的表。
- 创建一个物化视图，转换来自引擎的数据并将其放入先前创建的表中。
当物化视图加入引擎时，它开始在后台收集数据。这允许您持续接收来自 RabbitMQ 的消息，并使用 SELECT 将它们转换为所需格式。
一个 RabbitMQ 表可以有多个你需要的物化视图。
数据可以根据rabbitmq_exchange_type和指定的rabbitmq_routing_key_list进行通道。
每个表不能有多于一个 exchange。一个 exchange 可以在多个表之间共享 - 因为可以使用路由让数据同时进入多个表。
Exchange 类型的选项:
direct- 路由是基于精确匹配的键。例如表的键列表:key1,key2,key3,key4,key5, 消息键可以是等同他们中的任意一个.fanout- 路由到所有的表 (exchange 名称相同的情况) 无论是什么键都是这样.topic- 路由是基于带有点分隔键的模式. 比如:*.logs,records.*.*.2020,*.2018,*.2019,*.2020.headers- 路由是基于key=value的匹配，设置为x-match=all或x-match=any. 例如表的键列表:x-match=all,format=logs,type=report,year=2020.consistent_hash- 数据在所有绑定的表之间均匀分布 (exchange 名称相同的情况). 请注意，这种 exchange 类型必须启用 RabbitMQ 插件:rabbitmq-plugins enable rabbitmq_consistent_hash_exchange.
设置rabbitmq_queue_base可用于以下情况:
- 来让不同的表共享队列, 这样就可以为同一个队列注册多个消费者，这使得性能更好。如果使用
rabbitmq_num_consumers和/或rabbitmq_num_queues设置，在这些参数相同的情况下，实现队列的精确匹配。 - 以便在不是所有消息都被成功消费时，能够恢复从某些持久队列的阅读。要从一个特定的队列恢复消耗 - 在
rabbitmq_queue_base设置中设置其名称，不要指定rabbitmq_num_consumers和rabbitmq_num_queues（默认为1）。要恢复所有队列的消费，这些队列是为一个特定的表所声明的 - 只要指定相同的设置。rabbitmq_queue_base,rabbitmq_num_consumers,rabbitmq_num_queues。默认情况下，队列名称对表来说是唯一的。 - 以重复使用队列，因为它们被声明为持久的，并且不会自动删除。可以通过任何 RabbitMQ CLI 工具删除）
为了提高性能，收到的消息被分组为大小为 max_insert_block_size 的块。如果在stream_flush_interval_ms毫秒内没有形成数据块，无论数据块是否完整，数据都会被刷到表中。
如果rabbitmq_num_consumers和/或rabbitmq_num_queues设置与rabbitmq_exchange_type一起被指定，那么:
- 必须启用
rabbitmq-consistent-hash-exchange插件. - 必须指定已发布信息的
message_id属性（对于每个信息/批次都是唯一的）。
对于插入查询时有消息元数据，消息元数据被添加到每个发布的消息中:messageID和republished标志（如果值为true，则表示消息发布不止一次） - 可以通过消息头访问。
不要在插入和物化视图中使用同一个表。
示例:
代码语言：javascript复制 CREATE TABLE queue (
key UInt64,
value UInt64
) ENGINE = RabbitMQ SETTINGS rabbitmq_host_port = 'localhost:5672',
rabbitmq_exchange_name = 'exchange1',
rabbitmq_exchange_type = 'headers',
rabbitmq_routing_key_list = 'format=logs,type=report,year=2020',
rabbitmq_format = 'JSONEachRow',
rabbitmq_num_consumers = 5;
CREATE TABLE daily (key UInt64, value UInt64)
ENGINE = MergeTree() ORDER BY key;
CREATE MATERIALIZED VIEW consumer TO daily
AS SELECT key, value FROM queue;
SELECT key, value FROM daily ORDER BY key;虚拟列
_exchange_name- RabbitMQ exchange 名称._channel_id- 接收消息的消费者所声明的频道ID._delivery_tag- 收到消息的DeliveryTag. 以每个频道为范围._redelivered- 消息的redelivered标志._message_id- 收到的消息的ID；如果在消息发布时被设置，则为非空._timestamp- 收到的消息的时间戳；如果在消息发布时被设置，则为非空.
4、Special特殊引擎
1、Distributed（分布式）
引擎描述
- 分布式引擎本身不存储数据, 但可以在多个服务器上进行分布式查询。
- 读是自动并行的。读取时，远程服务器表的索引（如果有的话）会被使用。
- 将会从位于«clustername»集群中 default.hits 表所有服务器上读取数据。
- 远程服务器不仅用于读取数据，还会对尽可能对数据做部分处理。例如，对于使用 GROUP BY 的查询，数据首先在远程服务器聚合，之后返回聚合函数的中间状态给查询请求的服务器。再在请求的服务器上进一步汇总数据。
- SELECT 查询会被发送到所有分片，并且无论数据在分片中如何分布（即使数据完全随机分布）都可正常工作。
- 添加新分片时，不必将旧数据传输到该分片。你可以给新分片分配大权重然后写新数据 - 数据可能会稍分布不均，但查询会正确高效地运行。
- 数据是异步写入的。对于分布式表的 INSERT，数据块只写本地文件系统。之后会尽快地在后台发送到远程服务器。
- 如果在 INSERT 到分布式表时服务器节点丢失或重启（如，设备故障），则插入的数据可能会丢失。如果在表目录中检测到损坏的数据分片，则会将其转移到«broken»子目录，并不再使用。 启用 max_parallel_replicas 选项后，会在分表的所有副本上并行查询处理。
语法格式
Distributed(clustername, default, hits, sharding_key)
引擎参数：
- 服务器配置文件中的集群名
- 远程数据库名
- 远程表名
- 数据分片键（可选）
数据库名参数除了用数据库名之外，也可用返回字符串的常量表达式。例如：currentDatabase()。
下面的情况，你需要关注分片方案：
- 使用需要特定键连接数据（ IN 或 JOIN ）的查询。如果数据是用该键进行分片，则应使用本地 IN 或 JOIN 而不是 GLOBAL IN 或 GLOBAL JOIN，这样效率更高。
- 使用大量服务器（上百或更多），但有大量小查询（个别客户的查询 - 网站，广告商或合作伙伴）。为了使小查询不影响整个集群，让单个客户的数据处于单个分片上是有意义的。或者，正如我们在 Yandex.Metrica 中所做的那样，你可以配置两级分片：将整个集群划分为«层»，一个层可以包含多个分片。单个客户的数据位于单个层上，根据需要将分片添加到层中，层中的数据随机分布。然后给每层创建分布式表，再创建一个全局的分布式表用于全局的查询。
2、MaterializedView（物化视图）
物化视图的使用（更多信息请参阅 CREATE TABLE ）。它需要使用一个不同的引擎来存储数据，这个引擎要在创建物化视图时指定。当从表中读取时，它就会使用该引擎。
物化视图一般使用 AggregatingMergeTree 聚合引擎，用于特点场景的数据查询比MergeTre拥有更高的性能。关于物化视图的操作可查看上文的 AggregatingMergeTree 表引擎。
3、Dictionary（字典）
Dictionary 引擎将字典数据展示为一个ClickHouse的表。Dictionary 引擎将字典数据展示为一个ClickHouse的表。数据存储在 system.dictionaries表中。
例如，考虑使用一个具有以下配置的 products 字典：
<dictionaries>
<dictionary>
<name>products</name>
<source>
<odbc>
<table>products</table>
<connection_string>DSN=some-db-server</connection_string>
</odbc>
</source>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
<layout>
<flat/>
</layout>
<structure>
<id>
<name>product_id</name>
</id>
<attribute>
<name>title</name>
<type>String</type>
<null_value></null_value>
</attribute>
</structure>
</dictionary>
</dictionaries>查询字典中的数据：
代码语言：javascript复制SELECT
name,
type,
key,
attribute.names,
attribute.types,
bytes_allocated,
element_count,
source
FROM system.dictionaries
WHERE name = 'products'┌─name─────┬─type─┬─key────┬─attribute.names─┬─attribute.types─┬─bytes_allocated─┬─element_count─┬─source──────────┐
│ products │ Flat │ UInt64 │ ['title'] │ ['String'] │ 23065376 │ 175032 │ ODBC: .products │
└──────────┴──────┴────────┴─────────────────┴─────────────────┴─────────────────┴───────────────┴─────────────────┘你可以使用 dictGet* 函数来获取这种格式的字典数据。
当你需要获取原始数据，或者是想要使用 JOIN 操作的时候，这种视图并没有什么帮助。对于这些情况，你可以使用 Dictionary 引擎，它可以将字典数据展示在表中。
语法：
代码语言：javascript复制CREATE TABLE %table_name% (%fields%) engine = Dictionary(%dictionary_name%)`示例：
代码语言：javascript复制create table products (product_id UInt64, title String) Engine = Dictionary(products);
CREATE TABLE products
(
product_id UInt64,
title String,
)
ENGINE = Dictionary(products)Ok.
0 rows in set. Elapsed: 0.004 sec.看一看表中的内容。
代码语言：javascript复制select * from products limit 1;
SELECT *
FROM products
LIMIT 1┌────product_id─┬─title───────────┐
│ 152689 │ Some item │
└───────────────┴─────────────────┘
1 rows in set. Elapsed: 0.006 sec.4、Merge（合并引擎）
引擎特点
Merge引擎 不同于MergeTree引擎，Merge引擎本身不存储数据，但可用于同时从任意多个其他的表中读取数据。- 读是自动并行的，不支持写入。读取时，那些被真正读取到数据的表的索引（如果有的话）会被使用。
Merge 引擎的参数：一个数据库名和一个用于匹配表名的正则表达式。
语法示例：
代码语言：javascript复制Merge(hits, '^WatchLog')数据会从 hits 数据库中表名匹配正则 ‘^WatchLog’ 的表中读取。
除了数据库名，你也可以用一个返回字符串的常量表达式。例如， currentDatabase() 。
正则表达式 — re2 (支持 PCRE 一个子集的功能)，大小写敏感。
了解关于正则表达式中转义字符的说明可参看 «match» 一节。
当选择需要读的表时，Merge 表本身会被排除，即使它匹配上了该正则。这样设计为了避免循环。
当然，是能够创建两个相互无限递归读取对方数据的 Merge 表的，但这并没有什么意义。
Merge 引擎的一个典型应用是可以像使用一张表一样使用大量的 TinyLog 表。
示例 2 ：
我们假定你有一个旧表（WatchLog_old），你想改变数据分区了，但又不想把旧数据转移到新表（WatchLog_new）里，并且你需要同时能看到这两个表的数据。
代码语言：javascript复制CREATE TABLE WatchLog_old(date Date, UserId Int64, EventType String, Cnt UInt64)
ENGINE=MergeTree(date, (UserId, EventType), 8192);
INSERT INTO WatchLog_old VALUES ('2018-01-01', 1, 'hit', 3);
CREATE TABLE WatchLog_new(date Date, UserId Int64, EventType String, Cnt UInt64)
ENGINE=MergeTree PARTITION BY date ORDER BY (UserId, EventType) SETTINGS index_granularity=8192;
INSERT INTO WatchLog_new VALUES ('2018-01-02', 2, 'hit', 3);
CREATE TABLE WatchLog as WatchLog_old ENGINE=Merge(currentDatabase(), '^WatchLog');
SELECT *
FROM WatchLog
┌───────date─┬─UserId─┬─EventType─┬─Cnt─┐
│ 2018-01-01 │ 1 │ hit │ 3 │
└────────────┴────────┴───────────┴─────┘
┌───────date─┬─UserId─┬─EventType─┬─Cnt─┐
│ 2018-01-02 │ 2 │ hit │ 3 │
└────────────┴────────┴───────────┴─────┘虚拟列
虚拟列是一种由表引擎提供而不是在表定义中的列。换种说法就是，这些列并没有在 CREATE TABLE 中指定，但可以在 SELECT 中使用。
下面列出虚拟列跟普通列的不同点：
- 虚拟列不在表结构定义里指定。
- 不能用
INSERT向虚拟列写数据。 - 使用不指定列名的
INSERT语句时，虚拟列要会被忽略掉。 - 使用星号通配符（
SELECT *）时虚拟列不会包含在里面。 - 虚拟列不会出现在
SHOW CREATE TABLE和DESC TABLE的查询结果里。
Merge 类型的表包括一个 String 类型的 _table 虚拟列。（如果该表本来已有了一个 _table 的列，那这个虚拟列会命名为 _table1 ；如果 _table1 也本就存在了，那这个虚拟列会被命名为 _table2 ，依此类推）该列包含被读数据的表名。
如果 WHERE/PREWHERE 子句包含了带 _table 的条件，并且没有依赖其他的列（如作为表达式谓词链接的一个子项或作为整个的表达式），这些条件的作用会像索引一样。这些条件会在那些可能被读数据的表的表名上执行，并且读操作只会在那些满足了该条件的表上去执行。
5、File（文件引擎）
数据源是以 Clickhouse 支持的一种输入格式（TabSeparated，Native等）存储数据的文件。
用法示例：
- 从 ClickHouse 导出数据到文件。
- 将数据从一种格式转换为另一种格式。
- 通过编辑磁盘上的文件来更新 ClickHouse 中的数据。
在 ClickHouse 服务器中的使用
代码语言：javascript复制File(Format)选用的 Format 需要支持 INSERT 或 SELECT 。有关支持格式的完整列表，请参阅 格式。
ClickHouse 不支持给 File 指定文件系统路径。它使用服务器配置中 路径 设定的文件夹。
使用 File(Format) 创建表时，它会在该文件夹中创建空的子目录。当数据写入该表时，它会写到该子目录中的 data.Format 文件中。
你也可以在服务器文件系统中手动创建这些子文件夹和文件，然后通过 ATTACH 将其创建为具有对应名称的表，这样你就可以从该文件中查询数据了。
注意
注意这个功能，因为 ClickHouse 不会跟踪这些文件在外部的更改。在 ClickHouse 中和 ClickHouse 外部同时写入会造成结果是不确定的。
示例：
1. 创建 file_engine_table 表：
CREATE TABLE file_engine_table (name String, value UInt32) ENGINE=File(TabSeparated)默认情况下，Clickhouse 会创建目录 /var/lib/clickhouse/data/default/file_engine_table 。
2. 手动创建 /var/lib/clickhouse/data/default/file_engine_table/data.TabSeparated 文件，并且包含内容：
$ cat data.TabSeparated
one 1
two 23. 查询这些数据:
代码语言：javascript复制SELECT * FROM file_engine_table┌─name─┬─value─┐
│ one │ 1 │
│ two │ 2 │
└──────┴───────┘在 Clickhouse-local 中的使用使用 clickhouse-local 时，File 引擎除了 Format 之外，还可以接收文件路径参数。
可以使用数字或名称来指定标准输入/输出流，例如 0 或 stdin，1 或 stdout。
$ echo -e "1,2n3,4" | clickhouse-local -q "CREATE TABLE table (a Int64, b Int64) ENGINE = File(CSV, stdin); SELECT a, b FROM table; DROP TABLE table"功能实现
- 读操作可支持并发，但写操作不支持
- 不支持:
ALTERSELECT ... SAMPLE- 索引
- 副本
6、Null
引擎特点
- 当写入 Null 类型的表时，将忽略数据。从 Null 类型的表中读取时，返回空。
- 但是，可以在 Null 类型的表上创建物化视图。写入表的数据将转发到视图中。
7、Set（集合）
引擎特点
- 始终存在于 RAM 中的数据集。它适用于IN运算符的右侧（请参见 «IN运算符» 部分）。
- 可以使用 INSERT 向表中插入数据。新元素将添加到数据集中，而重复项将被忽略。但是不能对此类型表执行 SELECT 语句。检索数据的唯一方法是在 IN 运算符的右半部分使用它。
- 数据始终存在于 RAM 中。对于 INSERT，插入数据块也会写入磁盘上的表目录。启动服务器时，此数据将加载到 RAM。也就是说，重新启动后，数据仍然存在。
- 对于强制服务器重启，磁盘上的数据块可能会丢失或损坏。在数据块损坏的情况下，可能需要手动删除包含损坏数据的文件。
8、Join（关联表）
引擎特点
- 使用 JOIN操作的一种可选的数据结构。
语法格式（建表语句详情参见创建表.）
代码语言：javascript复制CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
) ENGINE = Join(join_strictness, join_type, k1[, k2, ...])引擎参数
join_strictness– JOIN 限制.join_type– JOIN 类型.k1[, k2, ...]– 进行JOIN操作时USING语句用到的key列
使用`join_strictness` 和 `join_type` 参数时不需要用引号, 例如, `Join(ANY, LEFT, col1)`. 这些参数必须和进行join操作的表相匹配。否则，CH不会报错，但是可能返回错误的数据。语法示例
创建左关联表:
代码语言：javascript复制CREATE TABLE id_val(`id` UInt32, `val` UInt32) ENGINE = TinyLogINSERT INTO id_val VALUES (1,11)(2,12)(3,13)创建 Join 右边的表:
CREATE TABLE id_val_join(`id` UInt32, `val` UInt8) ENGINE = Join(ANY, LEFT, id)INSERT INTO id_val_join VALUES (1,21)(1,22)(3,23)表关联:
代码语言：javascript复制SELECT * FROM id_val ANY LEFT JOIN id_val_join USING (id) SETTINGS join_use_nulls = 1┌─id─┬─val─┬─id_val_join.val─┐
│ 1 │ 11 │ 21 │
│ 2 │ 12 │ ᴺᵁᴸᴸ │
│ 3 │ 13 │ 23 │
└────┴─────┴─────────────────┘作为一种替换方式，可以从 Join表获取数据，需要设置好join的key字段值。
SELECT joinGet('id_val_join', 'val', toUInt32(1))┌─joinGet('id_val_join', 'val', toUInt32(1))─┐
│ 21 │
└────────────────────────────────────────────┘数据查询及插入
可以使用 INSERT语句向 Join引擎表中添加数据。如果表是通过指定 ANY限制参数来创建的，那么重复key的数据会被忽略。指定 ALL限制参数时，所有行记录都会被添加进去。
不能通过 SELECT 语句直接从表中获取数据。请使用下面的方式：
- 将表放在
JOIN的右边进行查询 - 调用 joinGet函数，就像从字典中获取数据一样来查询表。
使用限制及参数设置
创建表时，会应用下列设置参数：
- join_use_nulls
- max_rows_in_join
- max_bytes_in_join
- join_overflow_mode
- join_any_take_last_row
Join表不能在 GLOBAL JOIN操作中使用
Join表创建及 查询时，允许使用join_use_nulls参数。如果使用不同的join_use_nulls设置，会导致表关联异常（取决于join的类型）。当使用函数 joinGet时，请在建表和查询语句中使用相同的 join_use_nulls 参数设置。
数据存储
Join表的数据总是保存在内存中。当往表中插入行记录时，CH会将数据块保存在硬盘目录中，这样服务器重启时数据可以恢复。- 如果服务器非正常重启，保存在硬盘上的数据块会丢失或被损坏。这种情况下，需要手动删除被损坏的数据文件。
9、URL
引擎描述
- 用于管理远程 HTTP/HTTPS 服务器上的数据。该引擎类似 文件 引擎。
Format必须是 ClickHouse 可以用于SELECT查询的一种格式，若有必要，还要可用于INSERT。有关支持格式的完整列表，请查看格式。URL必须符合统一资源定位符的结构。指定的URL必须指向一个HTTP 或 HTTPS 服务器。对于服务端响应，不需要任何额外的 HTTP 头标记。INSERT和SELECT查询会分别转换为POST和GET请求。对于POST请求的处理，远程服务器必须支持 分块传输编码
语法示例
1. 在 Clickhouse 服务上创建一个 url_engine_table 表：
CREATE TABLE url_engine_table (word String, value UInt64)
ENGINE=URL('http://127.0.0.1:12345/', CSV)2. 用标准的 Python 3 工具库创建一个基本的 HTTP 服务并启动它：
代码语言：javascript复制from http.server import BaseHTTPRequestHandler, HTTPServer
class CSVHTTPServer(BaseHTTPRequestHandler):
def do_GET(self):
self.send_response(200)
self.send_header('Content-type', 'text/csv')
self.end_headers()
self.wfile.write(bytes('Hello,1nWorld,2n', "utf-8"))
if __name__ == "__main__":
server_address = ('127.0.0.1', 12345)
HTTPServer(server_address, CSVHTTPServer).serve_forever()python3 server.py3. 查询请求:
代码语言：javascript复制SELECT * FROM url_engine_table┌─word──┬─value─┐
│ Hello │ 1 │
│ World │ 2 │
└───────┴───────┘功能实现
- 读写操作都支持并发
- 不支持：
ALTER和SELECT...SAMPLE操作。- 索引。
- 副本。
10、View（视图）
代码语言：txt复制 用于构建视图（有关更多信息，请参阅 `CREATE VIEW 查询`）。 它不存储数据，仅存储指定的 `SELECT` 查询。 从表中读取时，它会运行此查询（并从查询中删除所有不必要的列）。 11、Memory（内存表）
代码语言：txt复制 Memory 引擎以未压缩的形式将数据存储在 RAM 中。数据完全以读取时获得的形式存储。换句话说，从这张表中读取是很轻松的。并发数据访问是同步的。锁范围小：读写操作不会相互阻塞。不支持索引。查询是并行化的。在简单查询上达到最大速率（超过10 GB /秒），因为没有磁盘读取，不需要解压缩或反序列化数据。（值得注意的是，在许多情况下，与 MergeTree 引擎的性能几乎一样高）。重新启动服务器时，表中的数据消失，表将变为空。通常，使用此表引擎是不合理的。但是，它可用于测试，以及在相对较少的行（最多约100,000,000）上需要最高性能的查询。 Memory 引擎是由系统用于临时表进行外部数据的查询（请参阅 «外部数据用于请求处理» 部分），以及用于实现 `GLOBAL IN`（请参见 «IN 运算符» 部分）。12、Buffer（缓存）
引擎描述
- 缓冲数据写入 RAM 中，周期性地将数据刷新到另一个表。在读取操作时，同时从缓冲区和另一个表读取数据。
Buffer(database, table, num_layers, min_time, max_time, min_rows, max_rows, min_bytes, max_bytes)引擎参数
- database，table - 要刷新数据的表。可以使用返回字符串的常量表达式而不是数据库名称。 num_layers - 并行层数。在物理上，该表将表示为 num_layers 个独立缓冲区。建议值为16。min_time，max_time，min_rows，max_rows，min_bytes，max_bytes - 从缓冲区刷新数据的条件。
- 如果满足所有 «min» 条件或至少一个 «max» 条件，则从缓冲区刷新数据并将其写入目标表。min_time，max_time — 从第一次写入缓冲区时起以秒为单位的时间条件。min_rows，max_rows - 缓冲区中行数的条件。min_bytes，max_bytes - 缓冲区中字节数的条件。
- 写入时，数据从 num_layers 个缓冲区中随机插入。或者，如果插入数据的大小足够大（大于 max_rows 或 max_bytes ），则会绕过缓冲区将其写入目标表。
- 每个 «num_layers» 缓冲区刷新数据的条件是分别计算。例如，如果 num_layers = 16 且 max_bytes = 100000000，则最大RAM消耗将为1.6 GB。
语法示例
代码语言：javascript复制CREATE TABLE merge.hits_buffer AS merge.hits ENGINE = Buffer(merge, hits, 16, 10, 100, 10000, 1000000, 10000000, 100000000)引擎底层
- 创建一个 «merge.hits_buffer» 表，其结构与 «merge.hits» 相同，并使用 Buffer 引擎。写入此表时，数据缓冲在 RAM 中，然后写入 «merge.hits» 表。创建了16个缓冲区。如果已经过了100秒，或者已写入100万行，或者已写入100 MB数据，则刷新每个缓冲区的数据；或者如果同时已经过了10秒并且已经写入了10,000行和10 MB的数据。例如，如果只写了一行，那么在100秒之后，都会被刷新。但是如果写了很多行，数据将会更快地刷新。
- 当服务器停止时，使用 DROP TABLE 或 DETACH TABLE，缓冲区数据也会刷新到目标表。
- 可以为数据库和表名在单个引号中设置空字符串。这表示没有目的地表。在这种情况下，当达到数据刷新条件时，缓冲器被简单地清除。这可能对于保持数据窗口在内存中是有用的。
- 从 Buffer 表读取时，将从缓冲区和目标表（如果有）处理数据。
- 请注意，Buffer 表不支持索引。换句话说，缓冲区中的数据被完全扫描，对于大缓冲区来说可能很慢。（对于目标表中的数据，将使用它支持的索引。）
- 如果 Buffer 表中的列集与目标表中的列集不匹配，则会插入两个表中存在的列的子集。
- 如果类型与 Buffer 表和目标表中的某列不匹配，则会在服务器日志中输入错误消息并清除缓冲区。
- 如果在刷新缓冲区时目标表不存在，则会发生同样的情况。
- 如果需要为目标表和 Buffer 表运行 ALTER，我们建议先删除 Buffer 表，为目标表运行 ALTER，然后再次创建 Buffer 表。
- 如果服务器异常重启，缓冲区中的数据将丢失。
- PREWHERE，FINAL 和 SAMPLE 对缓冲表不起作用。这些条件将传递到目标表，但不用于处理缓冲区中的数据。因此，我们建议只使用Buffer表进行写入，同时从目标表进行读取。
- 将数据添加到缓冲区时，其中一个缓冲区被锁定。如果同时从表执行读操作，则会导致延迟。
- 插入到 Buffer 表中的数据可能以不同的顺序和不同的块写入目标表中。因此，Buffer 表很难用于正确写入 CollapsingMergeTree。为避免出现问题，您可以将 «num_layers» 设置为1。
- 如果目标表是复制表，则在写入 Buffer 表时会丢失复制表的某些预期特征。数据部分的行次序和大小的随机变化导致数据不能去重，这意味着无法对复制表进行可靠的 «exactly once» 写入。
- 由于这些缺点，我们只建议在极少数情况下使用 Buffer 表。
- 当在单位时间内从大量服务器接收到太多 INSERTs 并且在插入之前无法缓冲数据时使用 Buffer 表，这意味着这些 INSERTs 不能足够快地执行。
- 请注意，一次插入一行数据是没有意义的，即使对于 Buffer 表也是如此。这将只产生每秒几千行的速度，而插入更大的数据块每秒可以产生超过一百万行（参见 «性能» 部分）。
十一、ClickHouse表函数
ClickHouse表函数：导言 | ClickHouse文档
ClickHouse表函数是用来构造表的方法，通过 CREATE TABLE AS <table_function()> 语法创建
ClickHouse目前支持通过以下九种方式创建表数据
函数 | 描述 |
|---|---|
file | 创建一个file引擎表。 |
merge | 创建一个merge引擎表。 |
numbers | 创建一个单列的表，其中包含整数。 |
remote | 允许您访问远程服务器，而无需创建分布式表。 |
url | 创建一个URL引擎表。 |
mysql | 创建一个MySQL引擎表。 |
jdbc | 创建一个JDBC引擎表。 |
odbc | 创建一个ODBC引擎表。 |
hdfs | 创建一个HDFS引擎表。 |
1、file引擎表
①设置 user_files_path 路径，clickhouse的file表函数只读这个路径下的文件
代码语言：javascript复制grep user_files_path /etc/clickhouse-server/config.xml<user_files_path>/home/clickhouse/data/user_files/</user_files_path>
② 在user_files_path 路径下构造 file 数据
代码语言：javascript复制echo "1,2,3
3,2,1
78,43,45" > test.csv③file表函数查询数据
代码语言：javascript复制clickhouse client -m --query="SELECT * FROM file('test.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')"echo "SELECT * FROM file('test.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')" | clickhouse client④file表函数插入数据
代码语言：javascript复制clickhouse client -m --query="INSERT INTO FUNCTION file('test.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32') VALUES (1, 2, 3), (3, 2, 1); SELECT * FROM file('test.csv', 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32');"⑤路径中的通配符
多个路径组件可以具有通配符。 对于要处理的文件必须存在并与整个路径模式匹配（不仅后缀或前缀）。
*— 替换任意数量的任何字符，除了/包括空字符串。?— 替换任何单个字符。{some_string,another_string,yet_another_one}— 替换任何字符串'some_string', 'another_string', 'yet_another_one'。{N..M}— 替换范围从N到M的任何数字（包括两个边界）。
使用 {} 的构造类似于 remote)表函数。
2、merge引擎表
merge(db_name, 'tables_regexp') – 创建一个临时Merge表。
有关更多信息，请参见 “Table engines, Merge”。
表结构取自遇到的第一个与正则表达式匹配的表。
3、numbers表函数
numbers(N) – 返回一个包含单个 ‘number’ 列(UInt64)的表，其中包含从0到N-1的整数。 numbers(N, M) - 返回一个包含单个 ‘number’ 列(UInt64)的表，其中包含从N到(N M-1)的整数。 类似于 system.numbers 表，它可以用于测试和生成连续的值, numbers(N, M) 比 system.numbers更有效。
以下查询是等价的：生成一列10行的数据，分别为0 1 2 3 4 5 6 7 8 9
代码语言：javascript复制clickhouse client -m --query="SELECT * FROM numbers(10)"
clickhouse client -m --query="SELECT * FROM numbers(0, 10)"
clickhouse client -m --query="SELECT * FROM system.numbers LIMIT 10"4、remote远程表
①从远程服务器查询数据
代码语言：javascript复制clickhouse client -m --query="SELECT * FROM remote('127.0.0.1', db.remote_engine_table)"②将远程服务器中的数据插入表中
代码语言：javascript复制echo "CREATE TABLE remote_table (name String, value UInt32) ENGINE=Memory" | clickhouse client
echo "INSERT INTO FUNCTION remote('127.0.0.1', currentDatabase(), 'remote_table') VALUES ('test', 42)" | clickhouse-client
echo "SELECT * FROM remote_table" | clickhouse-client5、URL引擎表
①从HTTP服务器获取的包含 String 和 UInt32 类型的列，以CSV格式返回
代码语言：javascript复制clickhouse-client -m --query="SELECT * FROM url('http://127.0.0.1:12345/', CSV, 'column1 String, column2 UInt32')"②将 URL 的数据插入到表中
clickhouse-client -m --query="CREATE TABLE test_table (column1 String, column2 UInt32) ENGINE=Memory"
clickhouse-client -m --query="INSERT INTO FUNCTION url('http://127.0.0.1:8123/?query=INSERT INTO test_table FORMAT CSV', 'CSV', 'column1 String, column2 UInt32') VALUES ('http interface', 42)"
clickhouse-client -m --query="SELECT * FROM test_table"6、MySQL引擎表
①从ClickHouse中查询MySQL数据
代码语言：javascript复制clickhouse-client --query="SELECT * FROM mysql('localhost:3306', 'dbname', 'tbname', 'username', 'password')"②支持使用|并列进行多副本查询
clickhouse -m --query=SELECT * FROM mysql(`mysql1:3306|mysql2:3306|mysql3:3306`, 'dbname', 'tbname', 'username', 'password')"
clickhouse -m --query=SELECT * FROM mysql(`mysql{1|2|3}:3306`, 'dbname', 'tbname', 'username', 'password')"③插入数据
代码语言：javascript复制clickhouse-client --query="INSERT INTO FUNCTION mysql('localhost:3306', 'dbname', 'tbname', 'username', 'password', 1) (id, name) VALUES (2, '科比')"④更新数据
代码语言：javascript复制clickhouse-client --query="INSERT INTO TABLE FUNCTION mysql('localhost:3306', 'dbname', 'tbname', 'username', 'password', 0, 'UPDATE id = id 1') (id, name) VALUES (1, '乔丹')"7、JDBC引擎表
jdbc(datasource, schema, table) -返回通过JDBC驱动程序连接的表。
此表函数需要单独的 clickhouse-jdbc-bridge 程序才能运行，否则会报以下错误
DB::Exception: clickhouse-jdbc-bridge is not running. Please, start it manually.
①yum 安装执行 clickhouse-jdbc-bridge 程序
代码语言：javascript复制yum install -y https://github.com/ClickHouse/clickhouse-jdbc-bridge/releases/download/v2.0.2/clickhouse-jdbc-bridge-2.0.2-1.noarch.rpm
clickhouse-jdbc-bridge &②Java 安装执行 clickhouse-jdbc-bridge 程序
代码语言：javascript复制wget https://github.com/ClickHouse/clickhouse-jdbc-bridge/releases/download/v2.0.2/clickhouse-jdbc-bridge-2.0.2-shaded.jar
wget -P config/datasources https://raw.githubusercontent.com/ClickHouse/clickhouse-jdbc-bridge/master/misc/quick-start/jdbc-bridge/config/datasources/ch-server.json
java -jar clickhouse-jdbc-bridge-2.0.2-shaded.jar③使用 jdbc 引擎查询 MySQL 表
代码语言：javascript复制clickhouse -m --query="SELECT * FROM jdbc('jdbc:mysql://localhost:3306/?user=root&password=123456', 'dbname', 'tbname')"clickhouse client -m --query="SELECT * FROM jdbc('mysql://localhost:3306/?user=root&password=root', 'select * from dbname.tbname')"8、ODBC引擎表
ClickHouse允许通过 ODBC 方式连接到外部数据库.
①MySQL配置ODBC
代码语言：javascript复制vim /etc/odbc.ini[mysqlconn]
DRIVER = /usr/local/lib/libmyodbc5w.so
SERVER = 127.0.0.1
PORT = 3306
DATABASE = test
USERNAME = clickhouse
PASSWORD = clickhouse②在MySQL服务器中创建和配置此用户
代码语言：javascript复制CREATE USER 'clickhouse'@'localhost' IDENTIFIED BY 'clickhouse';
GRANT ALL PRIVILEGES ON *.* TO 'clickhouse'@'clickhouse' WITH GRANT OPTION;clickhouse -m --query="SELECT * FROM odbc('DSN=mysqlconn', 'dbname', 'dbtable')"③使用ODBC引擎创建MySQL映射表
代码语言：javascript复制CREATE TABLE odbc_tb
(
`id` Int32,
`name` String
)
ENGINE = ODBC('DSN=mysqlconn', 'mysql_dbname', 'mysql_tbname')9、HDFS引擎表
根据HDFS中的文件创建表。 该表函数类似于 url 和 文件
代码语言：javascript复制clickhouse-client -m --query="SELECT * FROM hdfs('hdfs://ns1:9000/clickhouse/file/', 'TSV', 'id UInt32, name String, age UInt32')"ClickHouse入门学习（一）：https://cloud.tencent.com/developer/article/1913751
ClickHouse入门学习（二）：https://cloud.tencent.com/developer/article/1913753
