简介
在
Doris
中,数据都以关系表(
Table
)的形式进行逻辑上的描述。
基本操作
创建用户
create user 'test' identified by 'test';
create database test_db;
grant all on test_db to test;
数据模型
Aggregate 模型
导入数据聚合
创建表
CREATE TABLE
IF
NOT EXISTS test_db.example_site_visit (
`user_id` LARGEINT NOT NULL COMMENT "用户 id",
`date` DATE NOT NULL COMMENT "数据灌入日期时间",
`city` VARCHAR ( 20 ) COMMENT "用户所在城市",
`age` SMALLINT COMMENT "用户年龄",
`sex` TINYINT COMMENT "用户性别",
`last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
`last_visit_date_not_null` DATETIME REPLACE_IF_NOT_NULL DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
`cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
`max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
`min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
) AGGREGATE KEY ( `user_id`, `date`, `city`, `age`, `sex` ) DISTRIBUTED BY HASH ( `user_id` ) BUCKETS 10;
插入数据
insert into test_db.example_site_visit values
(10000,'2017-10-01','北京',20,0,'2017-10-01 06:00:00','2017-10-01 06:00:00',20,10,10),
(10000,'2017-10-01','北京',20,0,'2017-10-01 07:00:00',NULL,15,2,2),
(10001,'2017-10-01','北京',30,1,'2017-10-01 17:05:45','2017-10-01 07:00:00',2,22,22),
(10002,'2017-10-02',' 上 海 ',20,1,'2017-10-02 12:59:12',null,200,5,5),
(10003,'2017-10-02','广州',32,0,'2017-10-02 11:20:00','2017-10-02 11:20:00',30,11,11),
(10004,'2017-10-01','深圳',35,0,'2017-10-01 10:00:15','2017-10-01 10:00:15',100,3,3),
(10004,'2017-10-03','深圳',35,0,'2017-10-03 10:20:22','2017-10-03 10:20:22',11,6,6);
注意:Insert into 单条数据这种操作在 Doris 里只能演示不能在生产使用,会引发写阻
塞。
select * from test_db.example_site_visit;
保留明细数据
创建表
CREATE TABLE
IF
NOT EXISTS test_db.example_site_visit2 (
`user_id` LARGEINT NOT NULL COMMENT "用户 id",
`date` DATE NOT NULL COMMENT "数据灌入日期时间",
`timestamp` DATETIME COMMENT "数据灌入时间,精确到秒",
`city` VARCHAR ( 20 ) COMMENT "用户所在城市",
`age` SMALLINT COMMENT "用户年龄",
`sex` TINYINT COMMENT "用户性别",
`last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
`cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
`max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
`min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
) AGGREGATE KEY ( `user_id`, `date`, `timestamp`, `city`, `age`, `sex` ) DISTRIBUTED BY HASH ( `user_id` ) BUCKETS 10;
插入数据
insert into test_db.example_site_visit2
values(10000,'2017-10-01','2017-10-01 08:00:05',' 北 京 ',20,0,'2017-10-01 06:00:00',20,10,10),
(10000,'2017-10-01','2017-10-01 09:00:05','北京',20,0,'2017-10-01 07:00:00',15,2,2),
(10001,'2017-10-01','2017-10-01 18:12:10','北京',30,1,'2017-10-01 17:05:45',2,22,22),
(10002,'2017-10-02','2017-10-02 13:10:00','上海',20,1,'2017-10-02 12:59:12',200,5,5),
(10003,'2017-10-02','2017-10-02 13:15:00','广州',32,0,'2017-10-02 11:20:00',30,11,11),
(10004,'2017-10-01','2017-10-01 12:12:48','深圳',35,0,'2017-10-01 10:00:15',100,3,3),
(10004,'2017-10-03','2017-10-03 12:38:20','深圳',35,0,'2017-10-03 10:20:22',11,6,6);
查看数据
select * from test_db.example_site_visit2;
导入数据与已有数据聚合
insert into test_db.example_site_visit values(10004,'2017-10-03','深圳',35,0,'2017-10-03 11:22:00',null,44,19,19),
(10005,'2017-10-03','长沙',29,1,'2017-10-03 18:11:02','2017-10-03 18:11:02',3,1,1);
查看数据
select * from test_db.example_site_visit;
Uniq 模型
创建表
CREATE TABLE IF NOT EXISTS test_db.user
(
`user_id` LARGEINT NOT NULL COMMENT "用户 id",
`username` VARCHAR(50) NOT NULL COMMENT "用户昵称",
`city` VARCHAR(20) COMMENT "用户所在城市",
`age` SMALLINT COMMENT "用户年龄",
`sex` TINYINT COMMENT "用户性别",
`phone` LARGEINT COMMENT "用户电话",
`address` VARCHAR(500) COMMENT "用户地址",
`register_time` DATETIME COMMENT "用户注册时间" )
UNIQUE KEY(`user_id`, `username`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 10;
插入数据
insert into test_db.USER values
(10000,'wuyanzu',' 北 京 ',18,0,12345678910,' 北 京 朝 阳 区 ','2017-10-01 07:00:00'),
(10000,'wuyanzu',' 北 京 ',19,0,12345678910,' 北 京 朝 阳 区 ','2017-10-01 07:00:00'),
(10000,'zhangsan','北京',20,0,12345678910,'北京海淀区','2017-11-15 06:10:20');
查询数据
select * from test_db.USER;
可以看到重复的数据替换了
Duplicate 模型(默认的表数据模型)
建表
CREATE TABLE
IF
NOT EXISTS test_db.example_log (
`timestamp` DATETIME NOT NULL COMMENT "日志时间",
`type` INT NOT NULL COMMENT "日志类型",
`error_code` INT COMMENT "错误码",
`error_msg` VARCHAR ( 1024 ) COMMENT "错误详细信息",
`op_id` BIGINT COMMENT "负责人 id",
`op_time` DATETIME COMMENT "处理时间"
) DUPLICATE KEY ( `timestamp`, `type` ) DISTRIBUTED BY HASH ( `timestamp` ) BUCKETS 10;
插入数据
INSERT INTO test_db.example_log
VALUES
( '2017-10-01 08:00:05', 1, 404, 'not found page', 101, '2017-10-01 08:00:05' ),
( '2017-10-01 08:00:05', 1, 404, 'not found page', 101, '2017-10-01 08:00:05' ),
( '2017-10-01 08:00:05', 2, 404, 'not found page', 101, '2017-10-01 08:00:06' ),
( '2017-10-01 08:00:06', 2, 404, 'not found page', 101, '2017-10-01 08:00:07' );
查询数据
select * from test_db.example_log;
动态分区
参数说明
示例
CREATE TABLE student_dynamic_partition1 ( id INT, time date, NAME VARCHAR ( 50 ), age INT ) DUPLICATE KEY ( id, time ) PARTITION BY RANGE ( time )() DISTRIBUTED BY HASH ( id ) buckets 10 PROPERTIES (
"dynamic_partition.enable" = "true" #开启动态分区,
"dynamic_partition.time_unit" = "DAY" #天为单位,
"dynamic_partition.start" = "-7" #当天的前7天,
"dynamic_partition.end" = "3" #当天的后3天,
"dynamic_partition.prefix" = "p" #分区前缀,
"dynamic_partition.buckets" = "10",
"replication_num" = "1"
);
查看分区调度情况
SHOW DYNAMIC PARTITION TABLES;
查看分区分配情况
SHOW PARTITIONS FROM student_dynamic_partition1;
如果数据插入的时候没有分区对应那么就是插入数据不成功的
设置创建历史分区(可以把start之前的分区创建出来)
ALTER TABLE student_dynamic_partition1
SET ( "dynamic_partition.create_history_partition" = "true" );
Rollup
ROLLUP
在多维分析中是“上卷”的意思,即将数据按某种指定的粒度进行进一步聚
合。
Aggregate 和 Uniq 模型中的 ROLLUP
作用就是粗化聚合逻辑
Duplicate 模型中的 ROLLUP
改变前缀索引顺序的作用