Sql相关命令（Doris）

1、Doris建表

mysql> show create table myTable \G
*************************** 1. row ***************************
       Table: myTable
Create Table: CREATE TABLE `myTable` (
  `event_day` int(11) NOT NULL DEFAULT "19700101" COMMENT "日期",
  `event_type` smallint(6) NOT NULL DEFAULT "00" COMMENT "my comment",
  `key` char(32) NOT NULL DEFAULT "" COMMENT "my key",
  `some name` char(32) NOT NULL DEFAULT "00" COMMENT "some name",
  `pv` int(11) SUM NULL COMMENT "pv",
  `uv` int(11) SUM NULL COMMENT "uv",
  `dura` float SUM NULL COMMENT "dura"
) ENGINE=OLAP
AGGREGATE KEY(`event_day`, `event_type`, `key`, `some_name`)
COMMENT "serverless组件页面访问信息"
PARTITION BY RANGE(`event_day`)
(PARTITION p202102 VALUES [("-2147483648"), ("20210301")),
PARTITION p202103 VALUES [("20210301"), ("20210401")),
PARTITION p202104 VALUES [("20210401"), ("20210501")),
PARTITION p202105 VALUES [("20210501"), ("20210601")),
PARTITION p202106 VALUES [("20210601"), ("20210701")),
PARTITION p202107 VALUES [("20210701"), ("20210801")))
DISTRIBUTED BY HASH(`event_day`, `event_type`, `app_key`, `bhv_page`) BUCKETS 64 # 分桶数一般与集群机器数量相当
PROPERTIES (
"replication_num" = "3",  # 副本数量
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-2147483648",  # 保存几天，这个值表示永久保存，不删除
"dynamic_partition.end" = "3",  # 当前时间往后几天建分区
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_num" = "3",
"dynamic_partition.buckets" = "64",
"dynamic_partition.create_history_partition" = "false",
"dynamic_partition.hot_partition_num" = "0",  # 热分区，存储于SSD上的内容
"in_memory" = "false",
"storage_format" = "V2"
);

2、清空分区数据

truncate table myTable PARTITION(p202107)

3、将旧表数据导入新表

INSERT INTO new_table SELECT * FROM old_table  # 将旧表数据导入新表（新表中有的字段）
INSERT INTO new_table (name, country) SELECT app_name, country FROM old_table; # 指定字段导入

4、查看表的大小

show data from my_table;

5、sql分析函数

FIRST_VALUE(event_day) OVER (PARTITION BY version ORDER BY event_day) as event_day
LAST_VALUE
ROW_NUMBER