clickhouse_sql
/data/app/clickhouse2/bin/clickhouse-client --multiquery -h 127.0.0.1 --port 19000 --user default --password eb22d98eaf028b1ca5c36548193d604a
echo "select allConcat from _cw_distributed_db.upsf_ods_upb_tbl_market_activity_log_info_ulsp_ads where discountId = '324219180' and cardNo = '6200582203326' and transDt >= '2021-07-03 00:00:00' and transDt < '2021-07-03 02:25:34'"| /data/app/clickhouse2/bin/clickhouse-benchmark -c 1 -d 5 -i 10 -h 127.0.0.1 -p 19000 --user default --password eb22d98eaf028b1ca5c36548193d604a
/data/app/clickhouse2/bin/clickhouse-client -m -h 127.0.0.1 --port 19000 --user default --password eb22d98eaf028b1ca5c36548193d604a
cd /data/app/clickhouse2/
cd /data/appData/clickhouse2/
select * from system.clusters;
/data/app/clickhouse2/bin/clickhouse-server restart
service clickhouse-server start
service clickhouse-server stop
service clickhouse-server restart
[root@node4 ~]# ps -ef|grep clickhouse
root 2499 1 0 6月26 ? 00:00:29 /data/app/clickhouse2/bin/clickhouse-server --daemon --config-file=/data/app/clickhouse2/etc/clickhouse-server/config.xml --log-file=/data/logs/clickhouse2/clickhouse-server.log --pid-file=/data/app/clickhouse2/clickhouse2.pid
root 18940 1 0 10:46 ? 00:00:01 /data/app/clickhouse2/bin/clickhouse-server status
root 24491 21557 0 10:56 pts/2 00:00:00 grep --color=auto clickhouse
DROP TABLE IF EXISTS `shard1`.`test_tbl_market_activity_dict_hostname_replica`;
DROP TABLE IF EXISTS `shard2`.`test_tbl_market_activity_dict_hostname_replica`;
DROP TABLE IF EXISTS `shard3`.`test_tbl_market_activity_dict_hostname_replica`;
DROP TABLE IF EXISTS `_cw_distributed_db`.`test_tbl_market_activity_log_info_ulsp`;
DROP TABLE IF EXISTS `test`.`tbl_market_activity_log_info_ulsp`;
-------查看后台进程
show processlist；
select * from system.processes;
select user,query_id,query,elapsed,memory_usage from system.processes;
---------杀死后台进程
-- 通过上面指令获取到进程相关信息后，可以用query_id条件kill进程
KILL QUERY WHERE query_id='2-857d-4a57-9ee0-327da5d60a90'
-- 杀死default用户下的所有进程
KILL QUERY WHERE user='default'
cd /data/app/clickhouse2/etc/clickhouse-server
ls
cp config.xml config.xmlbak2
vi config.xml
<distributed_ddl>
<!-- Path in ZooKeeper to queue with DDL queries -->
<path>/clickhouse/task_queue/ddl</path>
<cleanup_delay_period>60</cleanup_delay_period>
<task_max_lifetime>86400</task_max_lifetime>
<max_tasks_in_queue>1000</max_tasks_in_queue>
</distributed_ddl>
-----
CREATE TABLE test.tbl_market_activity_log_info_ulsp ON CLUSTER up_ck_cluster
(
`transDt` String,
`discountId` String,
`cardNo` String,
`mobile` String,
`userId` String,
`logId` String,
`transChnl` Nullable(String),
`_cw_insert_time` DateTime MATERIALIZED now(),
`_cw_uuid` UUID MATERIALIZED generateUUIDv4()
)
ENGINE = ReplicatedMergeTree(up_ck_cluster,test,tbl_market_activity_log_info_instead_ulsp,rand())
PARTITION BY subString(transDt,1,10)
ORDER BY (transDt,discountId,cardNo,mobile,userId,logId);
SQL 错误 [80]: ClickHouse exception, code: 80, host: 10.0.12.14, port: 18123; Code: 80, e.displayText() = DB::Exception: Seems like cluster is configured for cross-replication, but zookeeper_path for ReplicatedMergeTree is not specified or contains {uuid} macro. It's not supported for cross replication, because tables must have different UUIDs. Please specify unique zookeeper_path explicitly. (version 20.8.12.2)
[root@node1 ~]# ps -ef|grep clickhouse
root 16755 1 0 7月02 ? 01:49:05 java -jar send-clickhouse-data-1.0-SNAPSHOT.jar
root 41085 1 67 6月29 ? 7-10:01:30 /data/app/clickhouse2/bin/clickhouse-server --daemon --config-file=/data/app/clickhouse2/etc/clickhouse-server/config.xml --log-file=/data/logs/clickhouse2/clickhouse-server.log --pid-file=/data/app/clickhouse2/clickhouse2.pid
root 45409 41868 0 14:17 pts/0 00:00:00 grep --color=auto clickhouse
ps -ef| grep clickhouse-server| grep -v grep| awk '{print $2}' | xargs kill
cd /data/app/clickhouse2/
nohup /data/app/clickhouse2/bin/clickhouse-server --daemon --config-file=/data/app/clickhouse2/etc/clickhouse-server/config.xml --log-file=/data/logs/clickhouse2/clickhouse-server.log --pid-file=/data/app/clickhouse2/clickhouse2.pid >/dev/null 2>&1 &
CREATE TABLE shard1_ads.upsf_ods_upb_tbl_market_activity_log_info_ulsp_replica ON CLUSTER ads_cluster1 AS shard1.upsf_ods_upb_tbl_market_activity_log_info_ulsp_replica (
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/upsf_ods_upb/tbl_market_activity_log_info_ulsp', '{replica}')
PARTITION BY subString(transDt,1,10)
ORDER BY (transDt,discountId,cardNo,mobile,userId,logId)
SETTINGS index_granularity = 8192;
CREATE TABLE shard1_ads.upsf_ods_upb_tbl_market_activity_log_info_ulsp_replica AS shard1.upsf_ods_upb_tbl_market_activity_log_info_ulsp_replica
ENGINE = ReplicatedMergeTree('/clickhouse/tables/ads_cluster1-shard1_ads/upsf_ods_upb/tbl_market_activity_log_info_ulsp', 'replica1')
PARTITION BY subString(transDt,1,10)
ORDER BY (transDt,discountId,cardNo,mobile,userId,logId)
SETTINGS index_granularity = 8192;
CREATE TABLE shard1_ads.upsf_ods_upb_tbl_market_activity_log_info_ulsp_replica AS shard1.upsf_ods_upb_tbl_market_activity_log_info_ulsp_replica
ENGINE = ReplicatedMergeTree('/clickhouse/tables/ads_cluster1-shard1_ads/upsf_ods_upb/tbl_market_activity_log_info_ulsp', 'replica2')
PARTITION BY subString(transDt,1,10)
ORDER BY (transDt,discountId,cardNo,mobile,userId,logId)
SETTINGS index_granularity = 8192;
CREATE TABLE IF NOT EXISTS _cw_distributed_db.upsf_ods_upb_tbl_market_activity_log_info_ulsp_ads AS _cw_distributed_db.upsf_ods_upb_tbl_market_activity_log_info_ulsp
ENGINE = Distributed(ads_cluster1, '', `upsf_ods_upb_tbl_market_activity_log_info_ulsp_replica`, rand());
/data/app/clickhouse2/bin/clickhouse-client -m -h 127.0.0.1 --port 19000 --user default --password eb22d98eaf028b1ca5c36548193d604a --query="_cw_distributed_db.tbl_market_log_info_all where transDt >= '2021-07-03 00:00:00' AND transDt < '2021-07-04 00:00:00" > /data/clickhouse/backup/_cw_distributed_db/tbl_market_log_info_all_2021-07-03.tsv
INSERT INTO _cw_distributed_db.upsf_ods_upb_tbl_market_activity_log_info_ulsp_ads select * from _cw_distributed_db.tbl_market_log_info_all where transDt >= '2021-07-03 00:00:00' AND transDt < '2021-07-04 00:00:00';
TRUNCATE TABLE IF EXISTS shard1_ads.upsf_ods_upb_tbl_market_activity_log_info_ulsp_replica;
