Doris常用命令

一、集群操作

1、连接doris

mysql -h host -P9030 -u user -p'password' databases

2、增加用户

CREATE USER IF NOT EXISTS user_name IDENTIFIED BY 'password'

3、账户设置默认

用户设置默认集群

SET PROPERTY FOR {user} 'default_cloud_cluster' = '{clusterName}';

 

 

二、用户操作

1、建库

create database event_center;

2、建表

DROP TABLE IF EXISTS event_center;
--带唯一键
CREATE TABLE IF NOT EXISTS event_center
(
    dt DATE AS (FROM_UNIXTIME(event_timestamp / 1000)) COMMENT '日期分区字段（自动从event_timestamp生成）',
    md5 VARCHAR(64) COMMENT '唯一键',
    event_id VARCHAR(64) COMMENT '事件ID',
    sdk_version VARCHAR(32) COMMENT 'SDK版本',
    sdk_did VARCHAR(128) COMMENT '设备ID',
    sdk_ip VARCHAR(32) COMMENT 'SDK IP地址',
    `name` VARCHAR(64) COMMENT '应用名称', 
    cert_user_number BIGINT COMMENT '用户编号',
    event_timestamp BIGINT COMMENT '事件时间戳（毫秒）',
    sdk_timestamp BIGINT COMMENT 'SDK时间戳（毫秒）',
    kafka_timestamp BIGINT COMMENT 'Kafka时间戳（毫秒）',
    sink_kafka_timestamp BIGINT COMMENT 'Sink Kafka时间戳（毫秒）',
    params JSON COMMENT '事件参数（JSON格式）',
    INDEX idx_event_id (event_id) USING BITMAP COMMENT 'event_id位图索引（基数<5000，优化等值查询）'
)
ENGINE=OLAP
UNIQUE KEY(dt,md5)
-- 排序键：决定数据在分桶内的排序，用于前缀索引和查询优化
--DUPLICATE KEY(event_id)
PARTITION BY RANGE(dt)
(
    -- 初始分区：建表时必需，后续由动态分区自动管理
    PARTITION p_initial VALUES [('1970-01-01'), ('2026-01-07'))
)
-- 分桶键：决定数据分布在哪些BE节点上，用于并行查询和负载均衡，高基数>1万
DISTRIBUTED BY HASH(md5) BUCKETS 32
PROPERTIES (
    "replication_num" = "3",
    "bloom_filter_columns" = "event_id",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-90",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "32",
    "storage_format" = "V2",
    "compression" = "LZ4"
);

 

 

 

 

其它操作参考

https://help.aliyun.com/zh/selectdb/copy-of-basic-user-permissions?spm=a2c4g.11186623.help-menu-2503500.d_2_0_5_0.23954f6atb2gb6&scm=20140722.H_2593269._.OR_help-T_cn~zh-V_1