ClickHouse 实操

目录

一、基础操作

1、时间函数

2、alter 函数

3、删除数据

4、删表语句

5、local 表为了可以在往其他节点上也去写数据，需要在其他节点上也创建表。

6、local 表的创建

   7、base 表的创建

8、进入ck客户端

二、优化

1、控制存储类型 （不使用 Nullable 类型进行存储）

2、时间字段的类型

---

一、基础操作

1、时间函数

        时间日期函数 | ClickHouse Docs

        toYear、toMonth、toDayOfMonth、toHour、toMinute、toSecond

2、alter 函数

        alter | ClickHouse Docs

        增加一个列，需要在集群中的每个节点都执行一遍。

ALTER TABLE tpl_im_mec_vehicle_base_test_local ADD COLUMN track_id UInt32 AFTER dt

3、删除数据

        alter table  dw_071000.tpl_im_mec_vehicle_test_base_local delete where altitude = 0;

4、删表语句

drop table if exists dw_110000.tpl_im_mec_vehicle_base_local ON CLUSTER clickhouse_3shards_1replicas ;

5、local 表为了可以在往其他节点上也去写数据，需要在其他节点上也创建表。

      -- 在 其他节点创建表时 需要加上 IF NOT EXISTS

6、local 表的创建

CREATE TABLE IF NOT EXISTS dw_110000.tpl_im_mec_vehicle_base_local ON CLUSTER clickhouse_3shards_1replicas
(
    `id` String COMMENT '唯一id',
    `source` UInt8 DEFAULT '1' COMMENT '1-边缘计算设备',
    `device_id` String COMMENT '设备id',
    `device_pile_no` String COMMENT '设备桩号',
    `altitude` double COMMENT '高程',
    `heading` double COMMENT '航向偏离正北的角度，顺指针为正',
    `report_time` DateTime DEFAULT now() COMMENT '上报时间',
    `plate_number` String COMMENT '车牌号',
    `obstacle_id` String COMMENT '交通参与者id',
    `plate_color` String COMMENT '车牌颜色',
    `vehicle_type` UInt8 COMMENT '车辆类型，1-小汽车，2-货车，3-大巴车，4-面包车，5-危化品车，99-其他',
    `vehicle_color` String COMMENT '车辆颜色',
    `vehicle_speed` UInt16 COMMENT '车辆速度 km/h',
    `direction` UInt8 COMMENT '1-雄安方向，2-北京方向',
    `line` UInt8 COMMENT '车道号，1-1车道，2-2车道，3-3车道，4-4车道，5-应急车道',
    `road_section` UInt8 COMMENT '路段归属，1 北京-义和庄枢纽，2 义和庄枢纽-涿州东，\n  3 涿州东-固安西，4 固安西-东湾枢纽，5 东湾枢纽-高碑店东，6 高碑店东-泗庄，7 泗庄-雄安，8 雄安-容城东',
    `latitude` Decimal(17,10) DEFAULT '0.00000000000000' COMMENT '纬度',
    `longitude` Decimal(17,10) DEFAULT '0.00000000000000' COMMENT '经度',
    `delete_flag` UInt8 DEFAULT '0' COMMENT '删除标识 0正常 1删除',
    `ts` UInt64 COMMENT 'ts',
    `dt` String COMMENT '日期 yyyy-MM-dd',
    `create_time` DateTime DEFAULT now() COMMENT '创建时间',
    `update_time` DateTime DEFAULT now() COMMENT '更新时间'
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY report_time
ORDER BY report_time
TTL create_time + toIntervalMonth(3)
SETTINGS index_granularity = 8192
COMMENT 'MEC过车数据'

;

   7、base 表的创建

CREATE TABLE IF NOT EXISTS dw_110000.tpl_im_mec_vehicle_base
ON cluster clickhouse_3shards_1replicas AS dw_110000.tpl_im_mec_vehicle_base_local
ENGINE = Distributed(clickhouse_3shards_1replicas, dw_110000, tpl_im_mec_vehicle_base_local, rand());

8、进入ck客户端

        在集群上进行的操作，相当于在本地。

clickhouse-client -u default --password ****** -m

二、优化

1、控制存储类型 （不使用 Nullable 类型进行存储）

        因为存储Nullable列时需要创建一个额外的文件来存储NULL的标记，并且Nullable列无法被索引。因为除非极特殊情况，应直接使用字段默认值表示空，或者自行指定一个在业务中无意义的值。

 

2、时间字段的类型

         建表时能用数值型或日期时间型表示的字段能不用字符串就不要用字符串，全String类型在以Hive为中心的数仓建设中常见，但ClickHouse环境不应受此影响。

        虽然ClickHouse底层将DateTime存储为时间戳Long类型，但不建议存储Long类型，因为DateTime不需要经过函数转换处理，执行效率高、可读性好。