clickhouse?批量插入數(shù)據(jù)及ClickHouse常用命令詳解
一.安裝使用
ClickHouse是Yandex提供的一個(gè)開(kāi)源的列式存儲(chǔ)數(shù)據(jù)庫(kù)管理系統(tǒng),多用于聯(lián)機(jī)分析(OLAP)場(chǎng)景,可提供海量數(shù)據(jù)的存儲(chǔ)和分析,同時(shí)利用其數(shù)據(jù)壓縮和向量化引擎的特性,能提供快速的數(shù)據(jù)搜索。
Ⅰ).安裝
sudo yum install yum-utils sudo rpm --import https://repo.yandex.ru/clickhouse/CLICKHOUSE-KEY.GPG sudo yum-config-manager --add-repo https://repo.yandex.ru/clickhouse/rpm/stable/x86_64 sudo yum install clickhouse-server clickhouse-client sudo /etc/init.d/clickhouse-server start clickhouse-client
Ⅱ).配置
a).clickhouse-server
CLICKHOUSE_USER=username CLICKHOUSE_LOGDIR=${CLICKHOUSE_HOME}/log/clickhoue-server CLICKHOUSE_LOGDIR_USER=username CLICKHOUSE_DATADIR_OLD=${CLICKHOUSE_HOME}/data/old CLICKHOUSE_DATADIR=${CLICKHOUSE_HOME}/data
b).config.xml
... ... <!-- 配置日志參數(shù) --> <logger> <level>info</level> <log>${CLICKHOUSE_HOME}/log/clickhoue-server/clickhoue-server.log</log> <errorlog>${CLICKHOUSE_HOME}/log/clickhoue-server/clickhoue-server-error.log</errorlog> <size>100M</size> <count>5</count> </logger> <!-- 配置數(shù)據(jù)保存路徑 --> <path>${CLICKHOUSE_HOME}</> <tmp_path>${CLICKHOUSE_HOME}/tmp</> <user_files_path>${CLICKHOUSE_HOME}/user_files</> <!-- 配置監(jiān)聽(tīng) --> <listen_host>::</listen_host> <!-- 配置時(shí)區(qū) --> <timezone>Asiz/Shanghai</timezone> ... ...
Ⅲ).啟停服務(wù)
#### a).啟動(dòng)服務(wù) sudo service clickhouse-server start #### b).停止服務(wù) sudo service clickhouse-server stop
Ⅳ).客戶端訪問(wèn)
clickhouse-client
二.常用命令
Ⅰ).創(chuàng)建表
CREATE TABLE IF NOT EXISTS database.table_name ON cluster cluster_shardNum_replicasNum( 'id' UInt64, 'name' String, 'time' UInt64, 'age' UInt8, 'flag' UInt8 ) ENGINE = MergeTree PARTITION BY toDate(time/1000) ORDER BY (id,name) SETTINGS index_granularity = 8192
Ⅱ).創(chuàng)建物化視圖
CREATE MATERIALIZED VIEW database.view_name ON cluster cluster_shardNum_replicasNum ENGINE = AggregatingMergeTree PARTITION BY toYYYYMMDD(time) ORDER BY (id,name) AS SELECT toStartOfHour(toDateTime(time/1000)) as time, id, name, sumState( if (flag = 1, 1, 0)) AS successCount, sumState( if (flag = 0, 1, 0)) AS faildCount, sumState( if ((age < 10), 1, 0)) AS rang1Age, sumState( if ((age > 10) AND (age < 20), 2, 0)) AS rang2Age, sumState( if ((age > 20), 3, 0)) AS rang3Age, maxState(age) AS maxAge, minState(age) AS minAge FROM datasource.table_name GROUP BY time,id,name
Ⅲ).插入數(shù)據(jù)
a).普通數(shù)據(jù)插入
INSERT INTO database.table_name(id, name, age, flag) VALUES(1, 'test', 15, 0)
b).Json數(shù)據(jù)插入
INSERT INTO database.table_name FORMAT JSONEachRow{"id":"1", "name":"test", "age":"11", "flag":"1"}
Ⅳ).查詢數(shù)據(jù)
a).表數(shù)據(jù)查詢
SELECT * FROM database.table_name WHERE id=1
b).物化視圖查詢
SELECT id, name, sumMerge(successCount), sumMerge(faildCount), sumMerge(rang1Age), sumMerge(rang2Age), maxMerge(maxAge), minMerge(minAge) FROM database.view_name WHERE id=1 GROUP BY id, name
Ⅴ).創(chuàng)建NESTED表
CREATE TABLE IF NOT EXISTS database.table_name( 'id' UInt64, 'name' String, 'time' UInt64, 'age' UInt8, 'flag' UInt8 nested_table_name Nested ( sequence UInt32, id UInt64, name String, time UInt64, age UInt8, flag UInt8 socketAddr String, socketRemotePort UInt32, socketLocalPort UInt32, eventTime UInt64, exceptionClassName String, hashCode Int32, nextSpanId UInt64 )) ENGINE = MergeTree PARTITION BY toDate (time / 1000) ORDER BY (id, name, time) SETTINGS index_granularity = 8192
Ⅵ).NESTED表數(shù)據(jù)查詢
SELECT table1.*,table1.id FROM nest.table_name AS table1 array JOIN nested_table_name AS table2
Ⅶ).配置字典項(xiàng)
<dictionaries> <dictionary> <name>url</name> <source> <clickhouse> <host>hostname</host> <port>9000</port> <user>default</user> <password/> <db>dict</db> <table>url_dict</table> </clickhouse> </source> <lifetime> <min>30</min> <max>36</max> </lifetime> <layout> <hashed/> </layout> <structure> <id> <name>id</name> </id> <attribute> <name>hash_code</name> <type>String</type> <null_value/> </attribute> <attribute> <name>url</name> <type>String</type> <null_value/> </attribute> </structure> </dictionary> <dictionary> <name>url_hash</name> <source> <clickhouse> <host>hostname</host> <port>9000</port> <user>default</user> <password/> <db>dict</db> <table>url_hash</table> </clickhouse> </source> <lifetime> <min>30</min> <max>36</max> </lifetime> <layout> <complex_key_hashed/> </layout> <structure> <key> <attribute> <name>hash_code</name> <type>String</type> </attribute> </key> <attribute> <name>url</name> <type>String</type> <null_value/> </attribute> </structure> </dictionary> </dictionaries>
Ⅷ).字典查詢
SELECT id, dictGet('name', 'name', toUInt64(name)) AS name, dictGetString('url', 'url', tuple(url)) AS url FROM table_name
Ⅸ).導(dǎo)入數(shù)據(jù)
clickhouse-client --query="INSERT INTO database.table_name FORMAT CSVWithNames" < /path/import_filename.csv
Ⅹ).導(dǎo)出數(shù)據(jù)
clickhouse-client --query="SELECT * FROM database.table_name FORMAT CSV" sed 's/"http://g' > /path/export_filename.csv
Ⅺ).查看partition狀態(tài)
SELECT table, name, partition,active FROM system.parts WHERE database='database_name'
Ⅻ).清理partition
ALTER TABLE database.table_name ON cluster cluster_shardNum_replicasNum detach partition 'partition_id'
XIII).查看列的壓縮率
SELECT database, table, name, formatReadableSize(sum(data_compressed_bytes) AS c) AS comp, formatReadableSize(sum(data_uncompressed_bytes) AS r) AS raw, c/r AS comp_ratio FROM system.columns WHERE database='database_name' AND table='table_name' GROUP BY name
XIV).查看物化視圖的磁盤占用
clickhouse-client --query="SELECT partition,count(*) AS partition_num, formatReadableSize(sum(bytes)) AS disk_size FROM system.columns WHERE database='database_name' " --external --?le=***.sql --name=parts --structure='table String, name String, partition UInt64, engine String' -h hostname
到此這篇關(guān)于clickhouse 批量插入數(shù)據(jù)及ClickHouse常用命令的文章就介紹到這了,更多相關(guān)clickhouse 批量插入內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
解決springboot3.2集成mybatis-plus3.5.4.1報(bào)錯(cuò)的問(wèn)題
這篇文章給大家介紹了如何解決springboot3.2集成mybatis-plus3.5.4.1報(bào)錯(cuò)的問(wèn)題,文中通過(guò)圖文介紹的非常詳細(xì),具有一定的參考價(jià)值,需要的朋友可以參考下2023-12-12如何使用Java?8中DateTimeFormatter類型轉(zhuǎn)換日期格式詳解
這篇文章主要介紹了如何使用Java?8中DateTimeFormatter類型轉(zhuǎn)換日期格式詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2023-07-07SpringBoot普通類獲取spring容器中bean的操作
這篇文章主要介紹了SpringBoot普通類獲取spring容器中bean的操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-09-09spring?aop?pointcut?添加多個(gè)execution方式
這篇文章主要介紹了spring?aop?pointcut?添加多個(gè)execution方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2021-11-11圖解Java經(jīng)典算法冒泡選擇插入希爾排序的原理與實(shí)現(xiàn)
冒泡排序是一種簡(jiǎn)單的排序算法,它也是一種穩(wěn)定排序算法。其實(shí)現(xiàn)原理是重復(fù)掃描待排序序列,并比較每一對(duì)相鄰的元素,當(dāng)該對(duì)元素順序不正確時(shí)進(jìn)行交換。一直重復(fù)這個(gè)過(guò)程,直到?jīng)]有任何兩個(gè)相鄰元素可以交換,就表明完成了排序2022-09-09