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

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

坚持是一种态度

于 2022-02-18 17:58:35 发布

1646 收藏 6分类专栏： 数据库及存储技术 大数据开发 文章标签： sql 数据库 database版权

数据库及存储技术同时被 2 个专栏收录38 篇文章2 订阅订阅专栏

大数据开发12 篇文章0 订阅订阅专栏文章目录1. 查询 数据库容量2. 查看所有表3. 常用查询4. MySQL导入到ClickHouse5. clickhouse表数据迁移到新的分区表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, activeFROM system.partsWHERE 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#07171执行转库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;1234565. clickhouse表数据迁移到新的分区表INSERT INTO visit_record_partition_month SELECT * FROM visit_record;

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

————————————————版权声明：本文为CSDN博主「坚持是一种态度」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。原文链接：https://blog.csdn.net/u010882234/article/details/123008705

相关链接