clickhouse常用SQL语句，查询、建表、数据复制迁移、删除等

1. 查询 数据库容量

select
    sum(rows) as "总行数",
    formatReadableSize(sum(data_uncompressed_bytes)) as "原始大小",
    formatReadableSize(sum(data_compressed_bytes)) as "压缩大小",
    round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 0) "压缩率"
from system.parts;

2. 查看所有表

SELECT database, table, partition, name, active
FROM system.parts
WHERE table = 'table_name';

3. 常用查询

-- 列出数据库列表
show databases;

-- 列出数据库中表列表
show tables;

-- 创建数据库
create database test;

-- 删除一个表
drop table if exists test.t1;

-- 创建一个表
DROP TABLE visit_record_partition_month;
CREATE TABLE default.visit_record_partition_month (  `CREATE_DATE` DateTime,
    `PLATFORM_TYPE` String, `TERMINAL_UNIQUE_ID` String,`SYSTEM_UNIQUE_ID` String,
    `USER_UNIQUE_ID` String,`IP` String,`REFERER` String,`URL` String,`TITLE` String,
    `COOKIE` String,`SCOOKIE` String,`COUNTRY` String,`PROVINCE` String,
    `CITY` String,`SEARCH_ENGINE` String,`SEARCH_KEY` String,`SOURCE_TYPE` String,
    `SOURCE_HOST` String,`OS` String,`CLIENT` String,`IS_PC` String,`RESOLUTION` String,
    `COLORDEPTH` String,`LANGUAGE` String,`TYPE_CODE` String,`COLUMN_CLASS_CODE` String,
    `PUBLIC_INFO_ATTRIBUTE` String,`CLASS_CODE` String, `COLUMN_TYPE` String,
    `COLUMN_NAME` String,`LABELS` String,`COLUMN_ID` Int64 DEFAULT 0,`UNIT_ID` Int64 DEFAULT 0,
    `CAT_ID` Int64 DEFAULT 0) ENGINE = MergeTree 
   PARTITION BY toYYYYMM(CREATE_DATE) ORDER BY CREATE_DATE;
   
-- 插入测试数据
insert into default.visit_record_partition_month (CREATE_DATE,PLATFORM_TYPE, CITY) values ('2021-12-12 12:11:11',1, 'abc'), ('2022-01-12 12:11:11',2, 'bbbb');

-- 查询
select * from default.visit_record_partition_month;

-- 分区名可以用下语句查询
 SELECT table, partition, path FROM system.parts WHERE table = 'visit_record_partition_month';

-- 删除分区
alter table visit_record drop partition '202104';

4. MySQL导入到ClickHouse

进入clickhouse client命令界面操作，其他数据库也类似，建立远程连接即可

clickhouse-client -m -h 127.0.0.1  --password Lonsun#0717

执行转库SQL，经测试，256秒转换4837万数据，平均18.9万条每秒，平均94.33M每秒

INSERT INTO visit_record(CREATE_DATE,PLATFORM_TYPE,TERMINAL_UNIQUE_ID,SYSTEM_UNIQUE_ID,USER_UNIQUE_ID,IP,REFERER,URL,
TITLE,COOKIE,SCOOKIE,COUNTRY,PROVINCE,CITY,SEARCH_ENGINE,SEARCH_KEY,SOURCE_TYPE,SOURCE_HOST,OS,CLIENT,IS_PC,RESOLUTION,COLORDEPTH,LANGUAGE) 
select CREATE_DATE, 'EX',ifNull(IP,S_COOKIE),concat('sz_gova_', SITE_ID),ifNull(MEMBER_ID,''),ifNull(IP,''),ifNull(REFERER,''),ifNull(URL,''),ifNull(TITLE,''),ifNull(COOKIE,''),
ifNull(S_COOKIE,''),ifNull(COUNTRY,''),ifNull(PROVINCE,''),ifNull(CITY,''),ifNull(SEARCH_ENGINE,''),ifNull(SEARCH_KEY,''),ifNull(SOURCE_TYPE,''),ifNull(SOURCE_HOST,''),ifNull(OS,''),
ifNull(CLIENT,''),ifNull(IS_PC,''),ifNull(RESOLUTION,''),ifNull(COLOR_DEPTH,''),ifNull(LANGUAGE,'')
from mysql('xx.xxx.xx.xxx:3306', 'ex9_1', 'cms_site_chart_main', 'root', 'xylx1.t!@#') where ID > 484794271 order by ID;

5. clickhouse表数据迁移到新的分区表

INSERT INTO visit_record_partition_month SELECT * FROM visit_record;

-- 导入后，查看该表的现有分区和数据情况
 SELECT * FROM system.parts WHERE table = 'visit_record_partition_month';