clickhouse 创建表，删除表，插入语句

########### 删除某个 clickhouse 表

drop table db_center.QT_PLATE_VAL_INFO_EX_LYR

 

########### 创建表

CREATE TABLE db_center.QT_PLATE_VAL_INFO_EX_LYR
(
    `PLATE_UNI_CODE`  Int64, 
    `END_DATE` DateTime,

    `PLATE_LYR`   Nullable(Float64),
    `PLATE_TTM`   Nullable(Float64),
    `PLATE_PB`   Nullable(Float64),

    `PLATE_CODE` Nullable(String),
    `PLATE_NAME` Nullable(String),
    
    `ISVALID` Int64,
    `CREATETIME` DateTime DEFAULT toDateTime(now())    ,
    `UPDATETIME` DateTime DEFAULT toDateTime(now())    
)
ENGINE = ReplacingMergeTree
PARTITION BY toYYYYMM(END_DATE)
ORDER BY (END_DATE, PLATE_UNI_CODE)
SETTINGS index_granularity = 8192;

设置默认值
 `CREATETIME` DateTime DEFAULT toDateTime(now())    
按照 END_DATE 分区
PARTITION BY toYYYYMM(END_DATE)


插入的数据，会根据 ORDER BY 
下面的  END_DATE, PLATE_UNI_CODE 去重。
这个去重是一个异步操作。

ENGINE = ReplacingMergeTree

ORDER BY (END_DATE, PLATE_UNI_CODE)

########### 查询表

select * from db_center.QT_PLATE_VAL_INFO_EX_LYR

########### 插入 数据 

INSERT INTO db_center.QT_PLATE_VAL_INFO_EX_LYR
(PLATE_UNI_CODE,END_DATE,PLATE_LYR, PLATE_TTM, PLATE_PB, PLATE_CODE, PLATE_NAME, ISVALID ,CREATETIME )
values(5004120011, '2023-04-07 00:00:00',
2.001,1.002, 1.003,
'881011', 'test001',
1, '2023-04-07 16:52:02'
)

 

## 让clickhouse 表 立即执行去重操作

optimize table db_center.QT_PLATE_VAL_INFO_EX_LYR