大数据存储技术之ClickHouse入门学习（二）

systemctl start clickhouse-server.service

systemctl stop clickhouse-server.service

systemctl restart clickhouse-server.service

systemctl status clickhouse-server.service

clickhouse-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 -m

ps aux | tail -n +2 | awk '{ printf("%s\t%s\n", $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-client --host=... --port=... --user=... --password=...

clickhouse-client -m
clickhouse-client --multiline

clickhouse-client --query='SELECT 1'
echo 'SELECT 1' | clickhouse-client
clickhouse-client <<< 'SELECT 1'

clickhouse-client --query='INSERT INTO table VALUES' < data.txt
clickhouse-client --query='INSERT INTO table FORMAT TabSeparated' < data.tsv

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

clickhouse-client --query "CREATE DATABASE IF NOT EXISTS tutorial"

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

clickhouse-client --query "SELECT COUNT(*) FROM tutorial.hits_v1"
clickhouse-client --query "SELECT COUNT(*) FROM tutorial.visits_v1"

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 10

SELECT
    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')

clickhouse-client --query "OPTIMIZE TABLE tutorial.hits_v1 FINAL"
clickhouse-client --query "OPTIMIZE TABLE tutorial.visits_v1 FINAL"

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');

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);

clickhouse-client -m --query="SELECT * FROM t_order_rep;"

echo "export shard=shard01
export replica=replica01" > /home/clickhouse/macros

source /home/clickhouse/macros

echo ${shard} ${replica}

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);"

clickhouse-client -m --query "SHOW CREATE TABLE st_order_mt"

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"

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');"

clickhouse-client -m --query "SELECT * FROM st_order_mt;"

clickhouse-client -m --query "SELECT * FROM st_order_mt_all;"

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"

cat /chbase/test_fetch.tsv | clickhouse-client --query "INSERT INTO test_fetch FORMAT TSV"

clickhouse-client --query="SELECT * FROM test_fetch" > /chbase/test_fetch.tsv

<dependency>
<groupId>ru.yandex.clickhouse</groupId>
<artifactId>clickhouse-jdbc</artifactId>
<version>0.2.4</version>
</dependency>

// 初始化驱动
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));

//多个地址使用逗号分隔
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));

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-client --query="SELECT * FROM system.data_type_families"

SELECT 1 - 0.9;

SELECT ('welcome'), ('to'), ('china') AS x, toTypeName(x);

SELECT ('welcome','welcome1','welcome2'), ('to','to1','to2'), ('china','china1','china2') AS x, toTypeName(x);

clickhouse-client --query="SELECT generateUUIDv4()"

SELECT toTypeName(array());

SELECT tuple(1, NULL) AS x, toTypeName(x);

SELECT (4.245, 7.4563) AS x, toTypeName(x);

SELECT [(0, 0), (1, 0), (1, 1), (0, 1), (0, 0)] AS x, toTypeName(x);

SELECT [[(20, 20), (50, 20), (50, 50), (20, 50)], [(30, 30), (50, 50), (50, 30)]] AS x, toTypeName(x);

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);

DROP TABLE IF EXISTS t_test_map;
CREATE TEMPORARY TABLE t_test_map (a Map(String, UInt64));

INSERT INTO t_test_map VALUES ({'key1':1, 'key2':10}), ({'key1':2,'key2':20}), ({'key1':3,'key2':30});

-- 查询所有键值;
SELECT a FROM t_test_map;
-- 指定键查询值;
SELECT a['key2'] FROM t_test_map;

INSERT INTO t_test_map VALUES (map('key1', 1, 'key2', 2, 'key3', 3));

-- 查询所有键值;
SELECT a FROM t_test_map;
-- 指定键查询值;
SELECT a['key2'] FROM t_test_map;

SELECT CAST(([1, 2, 3], ['Ready', 'Steady', 'Go']), 'Map(UInt8, String)') AS x, toTypeName(x);

SELECT (1, 2) AS x, toTypeName(x);

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);

SELECT toTypeName(INTERVAL 3 DAY);

SELECT now() AS x, toTypeName(x);

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

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"

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;"

CREATE DATABASE testlazy ENGINE = Lazy(expiration_time_in_seconds);

  CREATE DATABASE dbname[ ENGINE = Atomic];

CREATE DATABASE dbname
ENGINE = PostgreSQL('host:port', 'database', 'username', 'password'[, `use_table_cache`]);

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;

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';

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, ...]

ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTINGS index_granularity=8192

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, ...]

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, ...]

CREATE TABLE summtt (
    key UInt32,
    value UInt32
) ENGINE = SummingMergeTree()
ORDER BY key

INSERT INTO summtt Values(1,1),(1,2),(2,1)

SELECT key, sum(value) FROM summtt GROUP BY key

- [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函数。

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, ...]

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;

INSERT INTO test.visits ...

SELECT
    StartDate,
    sumMerge(Visits) AS Visits,
    uniqMerge(Users) AS Users
FROM test.basic
GROUP BY StartDate
ORDER BY StartDate;

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, ...]

CREATE TABLE UAct(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8
) ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID

INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1)
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1),(4324182021466249494, 6, 185, 1)

SELECT * FROM UAct

SELECT * FROM UAct FINAL

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, ...]

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

    我们在这里看到了什么，折叠的部分在哪里？我们使用两个创建了两个数据部分 `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

SELECT * FROM UAct FINAL

    该引擎用来对 [Graphite](https://graphite.readthedocs.io/en/latest/index.html)数据进行瘦身及汇总。对于想使用CH来存储Graphite数据的开发者来说可能有用。如果不需要对Graphite数据做汇总，那么可以使用任意的CH表引擎；但若需要，那就采用 `GraphiteMergeTree` 引擎。它能减少存储空间，同时能提高Graphite数据的查询效率。

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\_rollup](https://clickhouse.com/docs/zh/operations/server-configuration-parameters/settings/#server_configuration_parameters-graphite) 参数定义。参数名称可以是任意的。允许为多个不同表创建多组配置并使用。汇总配置的结构如下：

pattern
    regexp
    function
pattern
    regexp
    age + precision
    ...
pattern
    regexp
    function
    age + precision
    ...
pattern
    ...
default
    function
    age + precision
    ...

<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>

    日志引擎分别有StripeLog 、Log、TinyLog。这些引擎是为了需要写入许多小数据量（少于一百万行）的表的场景而开发的。

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 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')

SELECT * FROM stripe_log_table

SELECT * FROM stripe_log_table ORDER BY timestamp

Kafka(kafka_broker_list, kafka_topic_list, kafka_group_name, kafka_format
      [, kafka_row_delimiter, kafka_schema, kafka_num_consumers])

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

  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;

  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;

  DETACH TABLE consumer;
  ATTACH TABLE consumer;

  <!-- 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>

MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1],
    name2 [type2],
    ...
) ENGINE = ODBC(connection_settings, external_database, external_table)

CREATE USER 'clickhouse'@'localhost' IDENTIFIED BY 'clickhouse';
GRANT ALL PRIVILEGES ON *.* TO 'clickhouse'@'clickhouse' WITH GRANT OPTION;

[mysqlconn]
DRIVER = /usr/local/lib/libmyodbc5w.so
SERVER = 127.0.0.1
PORT = 3306
DATABASE = test
USERNAME = clickhouse
PASSWORD = clickhouse

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 odbc_t
(
    `int_id` Int32,
    `float_nullable` Nullable(Float32)
)
ENGINE = ODBC('DSN=mysqlconn', 'test', 'test')

SELECT * FROM odbc_t

CREATE TABLE [IF NOT EXISTS] [db.]table_name
(
    columns list...
)
ENGINE = JDBC(datasource_uri, external_database, external_table)

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

INSERT INTO jdbc_table(`int_id`, `float`)
SELECT toInt32(number), toFloat32(number * 1.0) FROM system.numbers

ENGINE = HDFS(URI, format)

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

    多个路径组件可以具有 globs。 对于正在处理的文件应该存在并匹配到整个路径模式。 文件列表的确定是在 `SELECT` 的时候进行（而不是在 `CREATE` 的时候）。

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')

CREARE TABLE big_table (name String, value UInt32) ENGINE = HDFS('hdfs://hdfs1:9000/big_dir/file{0..9}{0..9}{0..9}', 'CSV')

cp /usr/local/hadoop/hadoop/etc/hadoop/hdfs-site.xml /etc/clickhouse-server/

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

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')

CREATE TABLE [IF NOT EXISTS] [db.]table_name
(
    name1 [type1],
    name2 [type2],
    ...
) ENGINE = MongoDB(host:port, database, collection, user, password);

CREATE TABLE mongo_table
(
    key UInt64,
    data String
) ENGINE = MongoDB('mongo1:27017', 'test', 'simple_table', 'testuser', 'clickhouse');

SELECT COUNT() FROM mongo_table;

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`]);

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 │
└────────────────┴──────┴────────┘

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');

CREATE TABLE s3_engine_table (name String, value UInt32)
ENGINE = S3(path, [aws_access_key_id, aws_secret_access_key,] format, [compression])

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 │
└──────┴───────┘

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');

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');

    这个引擎允许 ClickHouse 与 [rocksdb](http://rocksdb.org/) 进行集成。

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)

CREATE TABLE test
(
    `key` String,
    `v1` UInt32,
    `v2` String,
    `v3` Float32,
)
ENGINE = EmbeddedRocksDB
PRIMARY KEY key

    该引擎允许 ClickHouse 与 [RabbitMQ](https://www.rabbitmq.com/) 进行集成.

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]

  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>
    <username>root</username>
    <password>clickhouse</password>
 </rabbitmq>

 <rabbitmq>
    <vhost>clickhouse</vhost>
 </rabbitmq>

  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;

<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>

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 │
└──────────┴──────┴────────┴─────────────────┴─────────────────┴─────────────────┴───────────────┴─────────────────┘

CREATE TABLE %table_name% (%fields%) engine = Dictionary(%dictionary_name%)`

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.

select * from products limit 1;

SELECT *
FROM products
LIMIT 1

┌────product_id─┬─title───────────┐
│        152689 │ Some item       │
└───────────────┴─────────────────┘

1 rows in set. Elapsed: 0.006 sec.

Merge(hits, '^WatchLog')

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 │
└────────────┴────────┴───────────┴─────┘

File(Format)

CREATE TABLE file_engine_table (name String, value UInt32) ENGINE=File(TabSeparated)

$ cat data.TabSeparated
one 1
two 2

SELECT * FROM file_engine_table

┌─name─┬─value─┐
│ one  │     1 │
│ two  │     2 │
└──────┴───────┘

$ echo -e "1,2\n3,4" | clickhouse-local -q "CREATE TABLE table (a Int64, b Int64) ENGINE = File(CSV, stdin); SELECT a, b FROM table; DROP TABLE table"

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_type` 参数时不需要用引号, 例如, `Join(ANY, LEFT, col1)`. 这些参数必须和进行join操作的表相匹配。否则，CH不会报错，但是可能返回错误的数据。

CREATE TABLE id_val(`id` UInt32, `val` UInt32) ENGINE = TinyLog

INSERT INTO id_val VALUES (1,11)(2,12)(3,13)

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)

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 │
└────┴─────┴─────────────────┘

SELECT joinGet('id_val_join', 'val', toUInt32(1))

┌─joinGet('id_val_join', 'val', toUInt32(1))─┐
│                                         21 │
└────────────────────────────────────────────┘

CREATE TABLE url_engine_table (word String, value UInt64)
ENGINE=URL('http://127.0.0.1:12345/', CSV)

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,1\nWorld,2\n', "utf-8"))

if __name__ == "__main__":
    server_address = ('127.0.0.1', 12345)
    HTTPServer(server_address, CSVHTTPServer).serve_forever()

python3 server.py

SELECT * FROM url_engine_table

┌─word──┬─value─┐
│ Hello │     1 │
│ World │     2 │
└───────┴───────┘

    用于构建视图（有关更多信息，请参阅 `CREATE VIEW 查询`）。 它不存储数据，仅存储指定的 `SELECT` 查询。 从表中读取时，它会运行此查询（并从查询中删除所有不必要的列）。        

    Memory 引擎以未压缩的形式将数据存储在 RAM 中。数据完全以读取时获得的形式存储。换句话说，从这张表中读取是很轻松的。并发数据访问是同步的。锁范围小：读写操作不会相互阻塞。不支持索引。查询是并行化的。在简单查询上达到最大速率（超过10 GB /秒），因为没有磁盘读取，不需要解压缩或反序列化数据。（值得注意的是，在许多情况下，与 MergeTree 引擎的性能几乎一样高）。重新启动服务器时，表中的数据消失，表将变为空。通常，使用此表引擎是不合理的。但是，它可用于测试，以及在相对较少的行（最多约100,000,000）上需要最高性能的查询。

    Memory 引擎是由系统用于临时表进行外部数据的查询（请参阅 «外部数据用于请求处理» 部分），以及用于实现 `GLOBAL IN`（请参见 «IN 运算符» 部分）。

Buffer(database, table, num_layers, min_time, max_time, min_rows, max_rows, min_bytes, max_bytes)

CREATE TABLE merge.hits_buffer AS merge.hits ENGINE = Buffer(merge, hits, 16, 10, 100, 10000, 1000000, 10000000, 100000000)

grep user_files_path /etc/clickhouse-server/config.xml

<user_files_path>/home/clickhouse/data/user_files/</user_files_path>

echo "1,2,3
3,2,1
78,43,45" > test.csv

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

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');"

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"

clickhouse client -m --query="SELECT * FROM remote('127.0.0.1', db.remote_engine_table)"

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-client

clickhouse-client -m --query="SELECT * FROM url('http://127.0.0.1:12345/', CSV, 'column1 String, column2 UInt32')"

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"

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')"

clickhouse-client --query="INSERT INTO FUNCTION mysql('localhost:3306', 'dbname', 'tbname', 'username', 'password', 1) (id, name) VALUES (2, '科比')"

clickhouse-client --query="INSERT INTO TABLE FUNCTION mysql('localhost:3306', 'dbname', 'tbname', 'username', 'password', 0, 'UPDATE id = id + 1') (id, name) VALUES (1, '乔丹')"

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 &

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

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')"

vim /etc/odbc.ini

[mysqlconn]
DRIVER = /usr/local/lib/libmyodbc5w.so
SERVER = 127.0.0.1
PORT = 3306
DATABASE = test
USERNAME = clickhouse
PASSWORD = clickhouse

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')"

CREATE TABLE odbc_tb
(
    `id` Int32,
    `name` String
)
ENGINE = ODBC('DSN=mysqlconn', 'mysql_dbname', 'mysql_tbname')

clickhouse-client -m --query="SELECT * FROM hdfs('hdfs://ns1:9000/clickhouse/file/', 'TSV', 'id UInt32, name String, age UInt32')"