clickhouse-学习记录与总结

clickhouse 的学习记录与总结

一、一些常用语法

1. 数据导入与导出

# 数据导出: 通过命令行方式将数据导出到文本文件
echo 'select * from monitor.ch_lineage_records FORMAT TabSeparated' | curl chproxy.ziroom.com?database=monitor -uchlineage:G6fLEj8C15yU -d @- > ch_lineage_records.sql

# 数据导入
clickhouse-client -h10.30.2.16 -uadminr --password  --query="INSERT INTO monitor.ch_lineage_records FORMAT TabSeparated"
clickhouse-client -h10.30.2.16 -p9092 -uoffice --password tgavqQ8adscP --query="INSERT INTO monitor.ch_lineage_records FORMAT TabSeparated"

# 数据导出导入：JSON 格式
echo 'select * from monitor.ch_lineage_records FORMAT JSON' | curl chproxy.ziroom.com?database=monitor -uchlineage:G6fLEj8C15yU -d @- > ch_lineage_records.json
echo 'INSERT INTO monitor.ch_lineage_records FORMAT JSON' | curl 10.30.2.16:9092?database=monitor -uchlineage:IgCM9nyrBlyR -d @- < ch_lineage_records.json

# 数据导出导入，csv格式
echo 'select * from monitor.ch_lineage_records FORMAT CSV' | curl chproxy.ziroom.com?database=monitor -uchlineage:G6fLEj8C15yU -d @- > ch_lineage_records.csv
echo 'INSERT INTO monitor.ch_lineage_records FORMAT CSV' | curl 10.30.2.16:9092?database=monitor -uchlineage:IgCM9nyrBlyR -d @- < ch_lineage_records.csv

通过post请求发送，建表插入– sql 语句中的remote(ip,db,tab)

curl --location --request POST 'chproxy.ziroom.com/' \
--header 'Authorization: Basic Y2hsaW5lYWdlOkc2ZkxFajhDMTV5VQ==' \
--header 'Content-Type: text/plain' \
--header 'Cookie: INGRESSCOOKIE=1644278720.193.15880.215157' \
-d 'INSERT INTO FUNCTION remote('\''10.30.2.16'\'', '\''monitor'\'', '\''ch_lineage_records'\'') SELECT * FROM monitor.ch_lineage_records;'

2. post 方法，发送sql语句执行

curl --location --request POST 'http://10.30.2.16:9092' \
--header 'Authorization: Basic Y2hsaW5lYWdlOklnQ005bnlyQmx5Ug==' \
--header 'Content-Type: text/plain' \
-d 'SELECT 
                    ch_db_name, 
                    ch_table_name, 
                    argMax(owner, _ut) owner, 
                    multiIf(argMax(ch_cluster_name, _ut) = '\''olapch_2replicas_3shard'\'', '\''bich'\'', '\''servch'\'') AS cluster, 
                    max(_ut) as lastModifyTime  
                FROM monitor.ch_lineage_records
          
               group by ch_db_name, ch_table_name format JSON;'