目录

• 创建本地表
• 创建分布式表
• 清空表
• insert into select
• with查询
• 新增字段
• 查询数据
• 删除数据
• 睡眠
• groupArray
• select 组合字段

---

创建本地表

在test_cluster创建本地表rpt_ip_miss_country

• {占位符} 来自复制表宏配置
• /clickhouse/tables/ 是公共前缀 文档
• {shard} 是分片标识占位符，它会替换展开为分片标识
• {replica} 副本标识符
• CODEC 进行压缩 文档

create table if not exists test.rpt_ip_miss_country on cluster test_cluster
(
    ip      String CODEC(ZSTD(6)),
    ip_long UInt32 CODEC(ZSTD(6))
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/{database}/{table}', '{replica}')
partition by ip_long % 50
primary key ip;

---

创建分布式表

*_all 一般分布式表的命名规则

--分布式表
create table if not exists testdb.rpt_ip_miss_country_all on cluster test_cluster as testdb.rpt_ip_miss_country
Engine=Distributed('test_cluster','testdb', 'rpt_ip_miss_country', rand());

---

清空表

分布式表不支持直接清空表，清空本地表即可清空分布式表

--清空表
truncate table if exists testdb.rpt_ip_xx_country on cluster test_cluster;

---

insert into select

查询出的字段类型必须和目标表字段类型一致才能插入成功

• IPv4NumToString iplong转为字符串类型 文档
• CAST 转换数据类型 文档

--导入数据
insert into testdb.rpt_ip_xx_all (ip, ip_long)
select distinct IPv4NumToString(CAST(ip AS UInt32)) as ipv4, CAST(ip AS UInt32) as ip_long
from assets_report_table_all
where country = '';

---

select 组合字段

select ('1','2'); select '1','2';

groupArray

groupArray(x)

limit不生效

with 查询

with 查询出的结构作为变量，只能有一行数据

insert into testdb.ip_is_china_all (ip, ip_long, cidr, is_china)
select ipv4, ip_long, cidr, is_china
from (
         with (
             select(groupArray(ip), groupArray(cidr))
             from (
                   select ip, cidr
                   from testdb.rpt_ip_miss_country_all as miss
                            cross join testdb.cidr_ip_table_all as china
                   where miss.ip_long between china.ip_start_long and china.ip_end_long
                   order by china.range_size
                      )) as china_ips
         select toIPv4(ip)                                                                             as ipv4,
                ip,
                ip_long,
                if(has(china_ips.1, ip) == 1, arrayElement(china_ips.2, indexOf(china_ips.1, ip)), '') as cidr,
                notEmpty(cidr)                                                                         as is_china
         from testdb.rpt_ip_miss_country_all) as t;

• if(cond, then, else) 文档
• indexOf(arr,x)
• has(arr,elem)
• arrayElement(arr, n) 文档
• notEmpty(x) 文档
• groupArray(x) 创建参数值的数组
• uniq 统计数量 文档

insert into ip_xxx_table_all
select
    groupArray(ip_num)[1] as ipnum,
    groupArray(ip_num)[2] as country_num,
    groupArray(ip_num)[3] as no_geonum,
    groupArray(ip_num)[4] as china_lossip,
    groupArray(ip_num)[5] as notchina_lossip,
    subtractDays(now(),1)  as summary_date
from (
    select  toString(uniq(ip)) as ip_num  from assets_xxx_all
    union all
    select toString(uniq(country))  as country_num  from assets_xxx_all
    union all
    select  toString(uniq( ip)) as no_geonum  from assets_xx_all where country =''
    union all
    select toString(uniq( ip))  as china_lossip from ip_is_xx_all where is_china='1'
    union all
    select toString(uniq( ip))  as notchina_lossip from ip_is_xx_all where is_china='0'
)a;

---

新增字段

alter table testdb.ip_is_china on cluster test_cluster add column cidr String CODEC(ZSTD(6)) after is_china;
alter table testdb.ip_is_china_all on cluster test_cluster add column cidr String CODEC(ZSTD(6)) after is_china;

---

查询数据

-- 【一次性】插入测试数据
select * from testdb.assets_report_table_all where ip == toIPv4('192.186.63.17');
select * from testdb.ip_is_xx_all where ip == toIPv4('102.218.79.119');

---

删除数据

alter table testdb.ip_is_china_xx_table on cluster test_cluster delete where summary_date = '2021-12-22 10:48:57';
alter table testdb.ip_is_china_xx_table on cluster test_cluster delete where ipnum = '301359085';

---

睡眠

select sleep(3);

---