Doris笔记
# 显示分区 SHOW PARTITIONS from stg_pay_dt; # 删除分区 ALTER TABLE stg_pay_dt DROP PARTITION p20211011; # 添加分区 ALTER TABLE stg_pay_dt ADD PARTITION IF NOT EXISTS p20211011 VALUES LESS THAN('20211012'); # 删除表 DROP TABLE IF EXISTS `stg_kafka_device_message_data`; # 创建表 CREATE TABLE stg_kafka_device_message_data ( dt INT COMMENT '分区时间(采用的数据时间而非消费数据的时间)', message VARCHAR(4000) COMMENT '原始数据', receive_time DATETIME REPLACE COMMENT '接收时间' ) AGGREGATE KEY ( dt, message ) PARTITION BY RANGE ( dt ) ( PARTITION p20211229 VALUES LESS THAN ( '20211230' ) ) DISTRIBUTED BY HASH ( dt ) buckets 10 PROPERTIES ( "replication_num" = "1" ); # OFFSET_BEGINNING 、 OFFSET_END,创建消费kafka的导入任务 CREATE ROUTINE LOAD load_kafka_device_message_data ON stg_kafka_device_message_data COLUMNS ( dt=from_unixtime( CAST( get_json_string ( message, '$.timestamp' ) AS BIGINT ) / 1000, '%Y%m%d' ), message, receive_time=DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s') ) PROPERTIES ( "desired_concurrent_number" = "1", "max_error_number" = "1000" ) FROM KAFKA ( "kafka_broker_list" = "172.17.46.202:9092", "kafka_topic" = "device.message", "property.group.id" = "kafka-consumer2-data-import", "property.kafka_default_offsets" = "OFFSET_BEGINNING" ); -- 查看导入任务 SHOW routine Load; -- 暂停导入kafka任务 PAUSE ROUTINE LOAD FOR load_kafka_iot_realtime_data; -- 恢复导入任务 RESUME ROUTINE LOAD FOR load_kafka_iot_realtime_data; -- 删除导入kafka任务 STOP ROUTINE LOAD FOR load_kafka_iot_realtime_data; -- 查看建表语句 show create table ods_fireHydrant_dt; -- 修改表字段信息 ALTER TABLE ods_fireHydrant_dt MODIFY COLUMN address VARCHAR(400) REPLACE COMMENT "设备地址"; -- 创建动态分区, HOUR、DAY、WEEK、MONTH(当指定为 HOUR 时,动态创建的分区名后缀格式为 yyyyMMddHH,例如2020032501。小时为单位的分区列数据类型不能为 DATE,要用datetime。) CREATE TABLE tb1 ( k1 DATE ) PARTITION BY RANGE(k1) () DISTRIBUTED BY HASH(k1) PROPERTIES ( "replication_num" = "1", "dynamic_partition.enable" = "true", "dynamic_partition.time_unit" = "DAY", "dynamic_partition.end" = "1", "dynamic_partition.prefix" = "p", "dynamic_partition.buckets" = "8" );
-- 动态分区的属性可以修改,例如需要起/停动态分区的功能,可以通过ALTER TABLE来完成。
ALTER TABLE site_access SET("dynamic_partition.enable"="false");
ALTER TABLE site_access SET("dynamic_partition.enable"="true");
-- 动态分区线程的执行频率,默认为600秒(10分钟),即每10分钟进行一次调度 ADMIN SET FRONTEND CONFIG ("dynamic_partition_check_interval_seconds" = "3600"); -- 修改表名 alter TABLE tb1 RENAME tb2; -- 替换表示例(表结构需保持一致) ALTER TABLE raw_mongo_fi_iot_hld_history_record REPLACE WITH TABLE stg_kafka_mongo_history_data PROPERTIES('swap' = 'true'); -- 清空表数据 TRUNCATE table stg_strem_mango_history_ws; -- 修改副本数 /*(最大副本数量取决于集群中独立 IP 的数量(注意不是 BE 数量)。Doris 中副本分布的原则是, 不允许同一个 Tablet 的副本分布在同一台物理机上,而识别物理机即通过 IP。 所以,即使在同一台物理机上部署了 3 个或更多 BE 实例,如果这些 BE 的 IP 相同,则依然只能设置副本数为 1)*/ alter TABLE tableName set ("default.replication_num" = "3"); -- 查看服务器BE节点 SHOW BACKENDS;