clickhouse-常用sql

1.  修改字段属性；

ALTER TABLE CREGISTRATION MODIFY COLUMN RECORDSTATE Nullable(String);

2. 建表，日期操作，字符串相加， case

几天前数据：

select  *  from  ehrds.CDRUGADVICEDETAIL  where  INPUTDATE > addDays(NOW(), -3)

字符串转日期

SELECT parseDateTimeBestEffort('2022/04/27')            

日期转字符串

select formatDateTime(now(),'%Y-%m-%d')

取年份：

toYear(THIS_FOLLOW_DTAE)

相关文档：https://clickhouse.com/docs/zh/sql-reference/functions/date-time-functions

3. 分区情况查询：

SELECT database, table, partition, active, min_date, max_date
FROM system.parts
WHERE active
AND table = 'SYS_ESB_LOG';

整理分区：

OPTIMIZE TABLE SYS_ESB_LOG FINAL;

 

4. 删除

ALTER table ehrds.EHR_HYPERTENSION_FOLLOW DELETE
WHERE toYYYYMM(THIS_FOLLOW_DTAE) < 202101

-- 删除所有符合条件的行
ALTER TABLE table_name DELETE WHERE condition;

-- 删除指定列值为特定值的行
ALTER TABLE table_name DELETE column = value;

-- 删除多个列值都符合条件的行
ALTER TABLE table_name DELETE column1 = value1 AND column2 = value2;

-- 删除全部行（注意：这将永久性地删除表中的所有数据）
TRUNCATE TABLE table_name;

 

update:

ALTER TABLE RKSWXX update  CARD_NO =  hex(encrypt('aes-128-ecb',CARD_ID ,'1234567887456321'))  WHERE length(CARD_NO) < 10

4. Case

select 
       (case
         when cords.DISEASENAME like '%发热%' then
          '1'
         else
          DISEASECODE
       end) as symptom_code
      

 5.  ReplacingMergeTree 数据库手动去重

 OPTIMIZE TABLE CREGISTRATION final;

6. 修改语句执行时间

settings max_execution_time =9600

7、修改表名，插入数据：

rename table CTREATMENTRECORDS to CTREATMENTRECORDS_O;
INSERT into  CTREATMENTRECORDS  select * from CTREATMENTRECORDS_O settings max_execution_time =96000;

 8、远程调用插入数据  

先在要插入的数据库上授权：

grant REMOTE ON *.* to ehrds;  #命令行下执行
insert into ehrds.CREGISTRATION  select * from remote('10.88.59.208:8123','ehrds','CREGISTRATION','dbuser','dbpassword') where BUSINESSYEAR ='2022';  #执行远程插入

9、删除数据库等操作：

#查看版本
select version();                     
# 查看数据库
show databases;
# 删除数据库
drop database xx;
# 切换数据库，clickhouse实例的默认数据库：default
use default;

# 查看数据库下的表
show tables;
# 删除表
drop table table_name [on cluster default];

# 创建数据库
create database if not exists database_test on cluster default;
# 注意：1）on cluster cluster_name；这个指令使得操作能在集群范围内的节点都生效。 2）创建表、删除表时注意确认是否在集群所有节点创建，确保物理表与分布式表节点一致。

# 删除数据
alter table table_name delete [where condition];
# 清空表
truncate table table_name on cluster default;
# 更新语句
alter tavle table_name update count=1000 where name='cd';

10、索引，跳数索引

ALTER TABLE CREGISTRATION ADD INDEX creg_regdate DEPTCODE TYPE set(100) GRANULARITY 2;
ALTER TABLE CREGISTRATION ADD INDEX creg_paytype PATIENTTYPE_CODE TYPE set(100) GRANULARITY 2;

跳数索引类型
索引的类型

minmax：

ALTER TABLE  skip_table   ADD INDEX vix my_column  TYPE minmax GRANULARITY 2;
1 此类型值没有参数；它存储每个块的索引表达式的最小值和最大值；如果表达式是一个元组，它分别存储元组元素的每个成员的值；

** set**

ALTER TABLE  skip_table   ADD INDEX vix my_column  TYPE set(200) GRANULARITY 2;
1
此类型接收一个参数：max_size（每个块的值集数量）；这个集合包含块中的所有值(如果值的数量超过max_size则为空)；适用于块中包含多重复值的数据；

Bloom Filter Types
Bloom Filter 为一种判断集合中是否存在某个值的一个算法；该算法只能肯定 集合中一定不存在某个值；但不能肯定集合中一定存在某个值；可能存在某个值 = 该值也可能不存在（假包含）；
Bloom Filter 有三种类型：

1、bloom_filter

ALTER TABLE  skip_table   ADD INDEX vix my_column  TYPE bloom_filter(0.25) GRANULARITY 2;
1
此类型接受一个可选参数，该参数表示在0到1之间；用于设置假包含率；

2、tokenbf_v1

此类型接受三个参数；
（1）过滤器的大小（以字节为单位）（较大的过滤器具有较少的误报，在存储中需要一些成本），（2）应用的哈希函数数量（同样，更多的哈希过滤器减少误报）
（3）布隆过滤器哈希函数的种子
此索引仅适用于String、FixedString和Map类型的数据；它用于LIKE、EQUALS、in、hasToken()和类似的长字符串中单词和其他值的搜索

3、ngrambf_v1

更专业的ngrambf_v1。该索引的功能与tokenbf_v1相同。在Bloom filter设置之前需要一个额外的参数，即要索引的ngram的大小。一个ngram是长度为n的任何字符串，比如如果n是4，A short string会被分割为A sh`` sho, shor, hort, ort s, or st, r str, stri, trin, ring。这个索引对于文本搜索也很有用，特别是没有单词间断的语言

clickhouse跳数索引的最终目的是为了减少对分组的读取；应用ClickHouse数据跳过索引必须避免足够的颗粒读取，以抵消计算索引的成本。如果一个值在每个分组中都只出现一次； 那么任何类型的跳数索引都只会增加额外的开销；通常跳数索引列应与主键有之间有很强的相关性（可以通过表的 order by 添加额外的列）；

对索引的添加只能根据各数据的实际结构与分布情况来调整；在添加跳数索引前应优先调整修改主键信息；甚至使用投影或使用实体化视图等技术；即使跳数索引是合适的，也经常需要对索引和表进行仔细的调优。

clickhouse 数据库基础

clickhouse 是列式数据库；每列有单独的数据文件；主键列必须是排序列的前缀
【PRIMARY KEY（C,R,T） ； ORDER BY（C,R,T,Y,U）】；
ORDER BY 可以在主键基础上添加额外排序字段；
ORDER BY中各字段依次 按字典序进行排列；
primary.idx主索引 文件按照 index_granularity 对有序表数据进行分组；并记录主索引中各列最小值；

跳数索引也是在此基础上对列数据进行重新聚合分类检索； 所以主索引与排序字段的选择与排列顺序会直接影响到跳数索引的可用性