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"
);----冷热分层，DUPLICATE KEY明细模型，不去重
CREATE TABLE `ods_table` (
`traceid` varchar(500) NULL COMMENT "追踪ID",
`dt` date NULL COMMENT "日期分区",
`__FILENAME__` varchar(500) NULL COMMENT "日志文件路径",
`__HOSTNAME__` varchar(200) NULL COMMENT "主机名",
`__SOURCE__` varchar(500) NULL COMMENT "来源IP",
`__TIMESTAMP__` bigint NULL COMMENT "时间戳(毫秒)",
`content` json NULL COMMENT "日志内容",
`env` varchar(500) NULL COMMENT "环境(production/test等)",
`level` varchar(500) NULL COMMENT "日志级别(INFO/WARN/ERROR等)",
`message` text NULL COMMENT "日志消息体",
`time` datetime NULL COMMENT "日志时间"
) ENGINE=OLAP
DUPLICATE KEY(`traceid`, `dt`)
COMMENT '日志'
PARTITION BY RANGE(`dt`)
(
PARTITION p_202601 VALUES [('2026-01-01'), ('2026-02-01'))("storage_policy" = "20250927-17-51-月分区表降冷"),
PARTITION p_202602 VALUES [('2026-02-01'), ('2026-03-01'))("storage_policy" = "20250927-17-51-月分区表降冷"),
PARTITION p_202603 VALUES [('2026-03-01'), ('2026-04-01'))("storage_policy" = "20250927-17-51-月分区表降冷"),
PARTITION p_202604 VALUES [('2026-04-01'), ('2026-05-01'))("storage_policy" = "20250927-17-51-月分区表降冷"),
PARTITION p_202605 VALUES [('2026-05-01'), ('2026-06-01'))("storage_policy" = "20250927-17-51-月分区表降冷"),
PARTITION p_202606 VALUES [('2026-06-01'), ('2026-07-01')),
PARTITION p_202607 VALUES [('2026-07-01'), ('2026-08-01')),
PARTITION p_202608 VALUES [('2026-08-01'), ('2026-09-01')),
PARTITION p_202609 VALUES [('2026-09-01'), ('2026-10-01')),
PARTITION p_202610 VALUES [('2026-10-01'), ('2026-11-01'))
)
DISTRIBUTED BY HASH(`traceid`) BUCKETS 3
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "MONTH",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-2147483648",
"dynamic_partition.end" = "2",
"dynamic_partition.prefix" = "p_",
"dynamic_partition.replication_allocation" = "tag.location.default: 3",
"dynamic_partition.buckets" = "3",
"dynamic_partition.create_history_partition" = "false",
"dynamic_partition.history_partition_num" = "-1",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"dynamic_partition.storage_policy" = "",
"dynamic_partition.storage_medium" = "HDD",
"dynamic_partition.start_day_of_month" = "1",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"compression" = "ZSTD",
"light_schema_change" = "true",
"storage_policy" = "20250927-17-51-月分区表降冷",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);-----主键模型去重
CREATE TABLE `ods_table` (
`dt` date NULL COMMENT "日期分区",
`traceid` varchar(500) NULL COMMENT "追踪ID",
`__FILENAME__` varchar(500) NULL COMMENT "日志文件路径",
`__HOSTNAME__` varchar(200) NULL COMMENT "主机名",
`__SOURCE__` varchar(500) NULL COMMENT "来源IP",
`__TIMESTAMP__` bigint NULL COMMENT "时间戳(毫秒)",
`content` json NULL COMMENT "日志内容",
`env` varchar(500) NULL COMMENT "环境(production/test等)",
`level` varchar(500) NULL COMMENT "日志级别(INFO/WARN/ERROR等)",
`message` text NULL COMMENT "日志消息体",
`time` datetime NULL COMMENT "日志时间"
) ENGINE=OLAP
UNIQUE KEY(`dt`, `traceid`)
COMMENT '日志表'
PARTITION BY RANGE(`dt`)
(
PARTITION p_202601 VALUES [('2026-01-01'), ('2026-02-01')),
PARTITION p_202602 VALUES [('2026-02-01'), ('2026-03-01')),
PARTITION p_202603 VALUES [('2026-03-01'), ('2026-04-01')),
PARTITION p_202604 VALUES [('2026-04-01'), ('2026-05-01')),
PARTITION p_202605 VALUES [('2026-05-01'), ('2026-06-01')),
PARTITION p_202606 VALUES [('2026-06-01'), ('2026-07-01')),
PARTITION p_202607 VALUES [('2026-07-01'), ('2026-08-01')),
PARTITION p_202608 VALUES [('2026-08-01'), ('2026-09-01')),
PARTITION p_202609 VALUES [('2026-09-01'), ('2026-10-01')),
PARTITION p_202610 VALUES [('2026-10-01'), ('2026-11-01'))
)
DISTRIBUTED BY HASH(`dt`) BUCKETS 3
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "MONTH",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-2147483648",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p_",
"dynamic_partition.replication_allocation" = "tag.location.default: 3",
"dynamic_partition.buckets" = "3",
"dynamic_partition.create_history_partition" = "false",
"dynamic_partition.history_partition_num" = "-1",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"dynamic_partition.storage_policy" = "",
"dynamic_partition.storage_medium" = "HDD",
"dynamic_partition.start_day_of_month" = "1",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"compression" = "ZSTD",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);
--------CREATE TABLE ds_sr.dwd_ord_stream (
`sub_order_no` varchar(320) NOT NULL COMMENT "子订单号",
`course_id` bigint(20) NOT NULL COMMENT "课程ID",
`order_no` varchar(256) NULL COMMENT "主订单号",
`user_id` bigint(20) NULL COMMENT "用户ID(收货手机号匹配）",
`user_name` varchar(1000) NULL COMMENT "用户名称（收货手机号匹配）",
`user_phone` varchar(256) NULL COMMENT "用户手机号",
`order_status` bigint(20) NULL COMMENT "订单状态 10-待支付",
`sku_type` int(11) NULL COMMENT "课程类型 0-无",
`sku_pay_type` varchar(256) NULL COMMENT "付费类型",
`business_id` int(11) NULL COMMENT "业务线ID",
`log_time` varchar(65533) NULL COMMENT "数据写入时间"
) ENGINE=OLAP
PRIMARY KEY(`sub_order_no`,`course_id`)
COMMENT "子订单资源明细表@wang"
DISTRIBUTED BY HASH(`sub_order_no`,`course_id`) BUCKETS 32
PROPERTIES (
"compression" = "LZ4",
"datacache.enable" = "true",
"enable_async_write_back" = "false",
"enable_persistent_index" = "true",
"persistent_index_type" = "CLOUD_NATIVE",
"replication_num" = "3",
"storage_volume" = "def_volume_prod_v2"
);
三、元数据探查
1、所有库
SELECT * FROM information_schema.schemata;
2、表元数据
SELECT table_name, table_type, engine, table_rows, data_length, create_time
FROM information_schema.tables
WHERE table_schema = 'your_db';3、字段元数据
SELECT column_name, ordinal_position, column_default, is_nullable,
data_type, column_type, column_key, column_comment
FROM information_schema.columns
WHERE table_schema = 'your_db' AND table_name = 'your_table'
ORDER BY ordinal_position;4、索引约束
SELECT * FROM information_schema.statistics
WHERE table_schema = 'your_db' AND table_name = 'your_table';
其它操作参考
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
浙公网安备 33010602011771号