clickhouse学习笔记

日期格式参考：https://blog.csdn.net/vkingnew/article/details/107618128

数据定义语言（DDL）

表创建

CREATE TABLE default.obu_52_1
(
    `id` String,
    `acceleration` Decimal(38, 20),
    `drivemode` UInt8,
    `positiontime` DateTime DEFAULT '0000-00-00 00:00:00',
    `vin` String DEFAULT 'null'
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(positiontime)
PRIMARY KEY (vin, positiontime)
ORDER BY (vin, positiontime)
SETTINGS index_granularity = 8192

表复制

CREATE TABLE obu_52_211115 AS obu_52_1

修改列属性

alter table vehicle_loc_52 modify column alert_type UInt8 DEFAULT -1;

删除数据

alter table vehicle_loc_52 delete where 1=1;

数据迁移

mysql导入clickhouse

• 关联mysql databases

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')

• 导入clickhouse，并id转String

insert into obu_52_211115 select CAST(id as String) as id,acceleration,drivemode,positiontime,vin from obu_old;

函数

日期格式转换

UnixTimestamp转DateTime

select toDateTime(1628033808);
┌─toDateTime(1628033808)─┐
│    2021-08-04 07:36:48 │
└────────────────────────┘

字符串相关

substring

# 从第二个字节开始截取三个字节
SELECT substring('china', 2, 3) AS str
┌─str─┐
│ hin │
└─────┘