doris create hive doris create hive 表

一、创建一张表的艰辛路程

在 Doris 中，数据以表（Table）的形式进行逻辑上的描述。

创建一张合格的表，主要考虑以下几个方面：

1. 字段
2. 索引
3. 引擎
4. 模型
5. 分区
6. 分桶
7. 属性

1、Doris中的引擎

• olap
• mysql
• broker
• Hive

2、Doris中的三大模型：

• Aggregate
• Uniq
• Duplicate

3、Doris中分区类型

• Range
• List

4、建表示例

4.1 字段

Doris中的数据类型包括：

• bigint
• bigmap
• boolean
• char
• date
• datetime
• decimal
• double
• float
• Hll(HyperLogLog)
• int
• largeint
• smallint
• string
• tinyint
• Varchar

当我们创建表的时候，难免会遇见一张大表，大表的描述即某些个字段的值已经超出了你的想像，你不得不用varchar(65533) 设置。当然如果可以使用String这样的就非常nice，不过Doris比较坑的就是它的string在创建表的时候会自动转化为varchar(1)，所以面对大数据量的字段，只能使用varchar(65533)，sql如下：

create table kochava_db.kochava_event_primary(
date_occurred INT,
install_id BIGINT,
matched_to_id BIGINT,
date_received INT,
partition_date DATE,
android_id varchar(512),
adid varchar(512),
app_version varchar(512),
attribution_module varchar(512),
campaign_name varchar(512),
segment_name varchar(512),
city varchar(512),
country_code varchar(512),
creative_id varchar(512),
device_limit_tracking INT,
device_marketing_name varchar(512),
device_model varchar(512),
device_os_version varchar(512),
device_os_name varchar(512),
device_ua varchar(512),
device_ver varchar(512),
dma_code varchar(512),
event_dimensions varchar(512),
event_id varchar(512),
event_name varchar(512),
event_value FLOAT,
identifiers varchar(65533),
identity_link varchar(512),
idfa varchar(512),
idfv varchar(512),
ip_address varchar(512),
kochava_device_id varchar(512),
latitude varchar(512),
longitude varchar(512),
lookback_seconds varchar(512),
lookback_used varchar(512),
match_object varchar(65533),
matched_to_type varchar(512),
network_id varchar(512),
network_name varchar(512),
postal_code varchar(512),
properties varchar(65533),
is_reengagement varchar(512),
region varchar(512),
sdk_version varchar(512),
site_id varchar(512),
tracker_guid varchar(512),
tracker_name varchar(512),
valid_receipt varchar(512),
cost float,
cost_type varchar(512),
traffic_verified varchar(512),
traffic_verification_fail_reason varchar(512),
waterfall_level varchar(512),
agency_id varchar(512),
attribution_date varchar(512),
channel_type varchar(512),
ad_platform varchar(512),
event_att varchar(512),
event_att_detail varchar(512),
event_att_time varchar(512),
event_att_duration_sec varchar(512),
is_revenue varchar(512),
tracker_id varchar(512),
campaign_id varchar(512)
)ENGINE=olap 
unique key (date_occurred,install_id,matched_to_id,date_received,partition_date)
partition by range (partition_date) 
(
partition P20210819 VALUES LESS THAN ("2021-08-20"),
partition P20210820 VALUES LESS THAN ("2021-08-21"),
partition P20210821 VALUES LESS THAN ("2021-08-22"),
partition P20210822 VALUES LESS THAN ("2021-08-23"),
partition P20210823 VALUES LESS THAN ("2021-08-24"),
partition P20210824 VALUES LESS THAN ("2021-08-25"),
partition P20210825 VALUES LESS THAN ("2021-08-26"),
partition P20210826 VALUES LESS THAN ("2021-08-27"),
partition P20210828 VALUES LESS THAN ("2021-08-29"),
partition P20210829 VALUES LESS THAN ("2021-08-30"),
partition P20210830 VALUES LESS THAN ("2021-08-31"),
partition P20210831 VALUES LESS THAN ("2021-09-01")
)
distributed by hash (install_id) buckets 32
properties(
"replication_num" = "1",
"storage_format" = "V2",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "P",
"dynamic_partition.buckets" = "10",
"in_memory" = "TRUE" 
)

sql中字段比较多 ，其中有三个字段的大小已经是 varchar(65532) 了，此时建表 报错：

Execution failed: Error Failed to execute sql: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: errCode = 2, detailMessage = The size of a row (224286) exceed the maximal row size: 100000

大致意思就是单行大小超过了限制，怎么超出限制的呢？

其实是这样的，对于Doris，很类似mysql，在 mysql中，它要求一个行的定义长度不能超过65535。

（1）单个字段如果大于65535，则转换为TEXT 。

（2）单行最大限制为65535，这里不包括TEXT、BLOB。

所谓单行最大限制指的就是一张表中所有字段的所设置的长度不得超过65535字节，

例如一个表中有三个varchar字段长度30000，那么这个表的单行长度为：30000*3=90000，

大于65535则报错不能建表，这里乘以3是因为数据库用的utf8编码，3个字节表示一个字符。

很正常，这张表是建不起来的，那么怎么解决呢？

很简单！先赋值小的大小 比如varchar(100) ,然后使用alter语句更改列的大小，就OK了。

alter table kochava_event_primary modify column XXX varchar(65532)

4.2 索引

目前（即Doris现有版本）仅仅提到了三种索引：前缀索引、bitmap索引、布隆过滤器索引

– 前缀索引：

所谓前缀索引就是在表结构字段排序的基础上，根据给定前缀列，快速查询数据的索引方式。比如 有一张表如下

create table if not exists test.events_primary1
(
  event_id varchar(10),
  date_received INT,
  dimension_key varchar(1024),
  dimension_value varchar(1024),
  partition_date DATE()
) engine=olap
...

这里的前缀索引是什么呢？

我们将一行数据的前 36 个字节 作为这行数据的前缀索引。当遇到 VARCHAR 类型时，前缀索引会直接截断。

前缀索引计算示例：10bytes + （遇到了varchar） 前缀索引为 event_id

如果有表为：

create table if not exists test.events_primary2
(
  event_id bigint,
  date_received INT,
  dimension_key varchar(1024),
  dimension_value varchar(1024),
  partition_date DATE()
) engine=olap
...

前缀索引计算示例：8bytes + 4bytes +（遇到了varchar） 前缀索引为 event_id，date_received，dimension_key

那么好处是什么呢？

当我们执行如下两条sql查询时

select * from events_primary1 where date_received = xxx;

select * from events_primary2 where date_received = xxx;

明显第二条sql语句的效率是高于第一条的。

【注意】Doris在建完表之后是不支持修改前缀索引的，所以在建表之前，优先考虑字段的顺序和分配，不然就只能使用rollup了。

– bitmap索引

bitmap index：位图索引，是一种快速数据结构，能够加快查询速度，创建和删除本质上就是一个schema change作业

需要注意的是：

• 目前索引仅支持 bitmap 类型的索引。
• bitmap 索引仅在单列上创建。
• bitmap 索引能够应用在 Duplicate 数据模型的所有列和 Aggregate, Uniq 模型的key列上。
• bitmap 索引支持的数据类型如下:

• TINYINT
• SMALLINT
• INT
• UNSIGNEDINT
• BIGINT
• CHAR
• VARCHAR
• DATE
• DATETIME
• LARGEINT
• DECIMAL
• BOOL

• bitmap索引仅在 Segment V2 下生效。当创建 index 时，表的存储格式将默认转换为 V2 格式。

由于它可以支持删除 添加 ，所以在初次创建表的时候大可不必太过纠结。

– 布隆过滤器索引

这个索引是隐藏在 属性中的索引，如果是 olap 引擎，我们可以指定该索引。bloom filter 索引仅适用于查询条件为 in 和 equal 的情况，该列的值越分散效果越好 目前只支持以下情况的列:除了 TINYINT FLOAT DOUBLE 类型以外的 key 列及聚合方法为 REPLACE 的 value 列

4.3 引擎

Doris的引擎目前有四种，但主要分为两种：存数据和不存数据

olap引擎：存储数据，一般作为分析使用 ，也是默认的。

mysql引擎，broker引擎，hive引擎：Doris都不会存储数据 ，只会使用这些的元数据，类似于外部表使用。他们的配置都是在属性中设置的

4.4 模型

模型建的好，查询快一倍

Doris 目前的模型只有三种：Aggregate,Unique,Duplicate三种 ，相应的也有这三种key,分别是 AGGREGATE KEY，UNIQUE KEY，DUPLICATE KEY

默认是Duplicate 这种模型啥都接受，也不会做任何操作，如果上游架构在去重方面做的非常nice的话，并且下游数据不需要做任何聚合等操作，只需要简单的做存储，那么这种模型挺适合的。

如果上游数据架构在去重方面做的不行并且下游的数据保证不能被重复消费，那么需要使用唯一键进行约束，相同key的value会被覆盖。那么优先选用Unique 模型，它可以保证相同的记录按照时间顺序进行覆盖。当然也可以选用Aggregate，不过需要加上 replace

如果下游数据需要进行聚合，比如需要统计 A城市出现的次数 ，我们可以选择Aggregate 模型，设置key为city字段，同时 num字段根据city进行聚合，那么这样在存储中就已经实现了聚合。非常适合k,v形式的指标查询。

对于UNIQUE KEY来说 ，它非常类似clickhouse中ReplicationMerge引擎，可以根据相同的key进行合并，但同时也需要注意，如果需要聚合相同的key，那么我们必须将相同的key放入同一个桶/分区中，这样才能保证对同一条数据进行合并进行合并。UNIQUE KEY可以是多个，也可以是一个，当然这取决于我们的定义的key值，我们需要将key列放在最前面。经过测试和验证，这个设定有以下条件

问题一、

1064 - errCode = 2, detailMessage = The partition column could not be aggregated column, Time: 0.017000s

sql如下：

create table if not exists test.events_dimensions(
date_received INT,
event_id INT,
id bigint,
partition_date DATE,
dimension_key varchar(1024),
dimension_value varchar(1024)
)engine=olap
unique key (date_received,event_id)
partition by range (partition_date)
(
	PARTITION partition_name1 VALUES LESS THAN ('2021-08-31')
	)
DISTRIBUTED by hash (date_received) buckets 10
PROPERTIES("replication_num" = "1");

执行该sql报错 分区列不是聚合列。

首先先看一下它的前缀索引：4B+4B+8B+3B+(varchar) 即：date_received，event_id，id，partition_date，dimension_key

指定的unique key ： date_received，event_id

分区字段是 partition_date

分桶字段是 date_received

unique key、分区字段、分桶字段 都必须是 key 列，那么key列是什么呢？

如果是默认的 Duplicate 模型，那么它的 key 列 DUPLICATE KEY 是前缀索引前36个字节大小的字段前3，即date_received，event_id，id

如果是Aggregrate 模型，当然unique 是其特殊的模型，那么它的 key 列 是指定的列 ，这个列必须在表结构最前端并且指定的顺序必须一致，即 date_received，event_id

那么报错原因就找到了，分区字段必须是key列，但是我们指定的key只有date_received，event_id，所以报错了！

问题二、

1064 - errCode = 2, detailMessage = Key columns should be a ordered prefix of the schema., Time: 0.018000s

sql如下：

create table if not exists test.events_dimensions(
date_received INT,
event_id INT,
id bigint,
partition_date DATE,
dimension_key varchar(1024),
dimension_value varchar(1024)
)engine=olap
unique key (date_received,id)
partition by range (partition_date)
(
	PARTITION partition_name1 VALUES LESS THAN ('2021-08-31')
	)
DISTRIBUTED by hash (date_received) buckets 10
PROPERTIES("replication_num" = "1");

根据上面的结论，key列必须在表结构的最前面且顺序必须和表中顺序一致，value列在后，所以只需要调整 key列顺序即可，key列是date_received ，id

如果以怀疑的态度去尝试不同的可能，我们发现了上面这句 分区字段、分桶字段 都必须是 key 列

是有问题的，比如下面的两个sql就正好打脸了

CREATE TABLE `events_dimensions8` (
  `date_received` int(11) NULL COMMENT "",
  `event_id` int(11) NULL COMMENT "",
  `id` bigint(20) NULL COMMENT "",
  `dimension_key` varchar(1024) NULL COMMENT "",
  `partition_date` date NULL COMMENT "",
  `dimension_value` varchar(1024) NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`date_received`, `event_id`, `id`)
COMMENT "OLAP"
PARTITION BY RANGE(`partition_date`)
(PARTITION partition_name1 VALUES [('0000-01-01'), ('2021-08-31')))
DISTRIBUTED BY HASH(`date_received`) BUCKETS 10
PROPERTIES (
"replication_num" = "1",
"in_memory" = "false",
"storage_format" = "V2"
);

这里发现 partition_date 并非 key列 为什么他也能创建成功？再如下面的sql

CREATE TABLE `events_dimensions9` (
  `date_received` int(11) NULL COMMENT "",
  `event_id` int(11) NULL COMMENT "",
  `id` bigint(20) NULL COMMENT "",
  `dimension_key` varchar(1024) NULL COMMENT "",
  `partition_date` date NULL COMMENT "",
  `dimension_value` varchar(1024) NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`date_received`, `event_id`, `id`)
COMMENT "OLAP"
PARTITION BY RANGE(`partition_date`)
(PARTITION partition_name1 VALUES [('0000-01-01'), ('2021-08-31')))
DISTRIBUTED BY HASH(`dimension_key`) BUCKETS 10
PROPERTIES (
"replication_num" = "1",
"in_memory" = "false",
"storage_format" = "V2"
);

partition_date，dimension_key 和key列完全不搭噶，但也是能创建OK的。

官网中也提出：

Partition

Partition 列可以指定一列或多列。分区类必须为 KEY 列。多列分区的使用方式在后面 多列分区 小结介绍。
不论分区列是什么类型，在写分区值时，都需要加双引号。
分区数量理论上没有上限。
当不使用 Partition 建表时，系统会自动生成一个和表名同名的，全值范围的 Partition。该 Partition 对用户不可见，并且不可删改。

以及

Bucket

如果使用了 Partition，则 DISTRIBUTED ... 语句描述的是数据在各个分区内的划分规则。如果不使用 Partition，则描述的是对整个表的数据的划分规则。

分桶列可以是多列，但必须为 Key 列。分桶列可以和 Partition 列相同或不同。

***然鹅 ，如果是 DUPLICATE 模型 是没有这样的要求的。***如果是聚合类模型 那么必然报错：

1064 - errCode = 2, detailMessage = Distribution column[dimension_key] is not key column, Time: 0.018000s

4.5 分区分桶

Doris中数据被划分了若干个分片，即数据分桶。每个分片包含了数据的若干行，这些分片之间是没有交集的，在物理上也是独立存储。

多个分片在逻辑上会属于同一个分区，但是一个分片只能属于一个分区，一个分区包含了多个分片。

Doris中必然会出现分桶和分区，故在创建表的时候，需要指定 distributed by … ；当然有人会提出为什么不需要指定partition呢？如果不指定partition,那么doris中会默认创建一个全局的分区，这是用户看不到的。

4.5.1 Doris中的第一层数据划分：Partition

partition 支持两种划分方式：range和list

• Range 分区

• Less than 语句

• 构造分区

partition by range(partition_date)(
	partition p20210904 less than ("2021-09-05"),
  partition p20210905 less than ("2021-09-06"),
  partition p20210906 less than ("2021-09-07")
)

这句sql最终的分区为

p20210904 [min_value,2021-09-05)

P20210905 [2021-09-05,2021-09-06)

p20210904 [2021-09-06,2021-09-07)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1Hwjl5ea-1631003154627)(创建表.assets/image-20210907135320909.png)]

• 删除分区

alter table test drop partition p20210815

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dRMRerp7-1631003154628)(创建表.assets/image-20210907135754200.png)]

缺少了一块分区其他分区保持不变，此时如果想要去导入 20210815 的数据，就会报错 ，因为Doris不会去创建丢失的分区。

• 添加分区

alter table test add partition p20210815 values less than ("2021-08-16")

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mUNyPPsq-1631003154629)(创建表.assets/image-20210907140810421.png)]

如果重复添加已经存在的分区，则不会进行覆盖

1517 - errCode = 2, detailMessage = Duplicate partition name p20210815, Time: 0.018000s

• Fixed Range

• 构建分区

PARTITION BY RANGE(`partition_date`)
(
PARTITION p20210812 VALUES [("2021-08-12"),("2021-08-12")),
PARTITION p20210910 VALUES [("2021-09-10"),("2021-09-10"))
)

不同于less than ,fixed range可以自己写时间，但注意的是，也只支持左闭右开，但是官网提示是可以灵活的创建表，

• 删除分区
  同上
• 创建分区
  同上

• List 分区
  给定一个或者多个枚举值，如果数据满足枚举值中的任何一个，即命中该分区
  目前仅支持以下类型的列作为 List 分区列:
  BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DATE, DATETIME, CHAR, VARCHAR

• 单列分区

• 构建分区（List 分区列必须指定为 notnull）

PARTITION BY LIST(`city`)
(
    PARTITION `p_cn` VALUES IN ("Beijing"),
    PARTITION `p_usa` VALUES IN ("New York"),
    PARTITION `p_jp` VALUES IN ("Tokyo")
)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4GYlHKwA-1631003154629)(创建表.assets/image-20210907142356695.png)]

• 删除分区

alter table table_name drop partition p_cn

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mziPXXM1-1631003154630)(创建表.assets/image-20210907142526658.png)]

• 创建分区

alter table table_name add partition p_cn values in ("Beijing")

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pWg865LZ-1631003154630)(创建表.assets/image-20210907142656492.png)]

• 多列分区

• 构建分区

PARTITION BY LIST(`city`,date)
(
    PARTITION `p_cn` VALUES IN (("Beijing","2021-09-01"),("Jiangsu","2021-09-02"),("Shanghai","2021-09-03")),
    PARTITION `p_usa` VALUES IN (("New York","2021-09-01"),("Chicago","2021-09-02"),("Boston","2021-09-02"),("Alaska","2021-09-03")),
    PARTITION `p_jp` VALUES IN (("Tokyo","2021-09-01"),("Osaka","2021-09-02"),("Nagasaki","2021-09-02"),("Yokohama","2021-09-03"))
)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wrE61y3v-1631003154630)(创建表.assets/image-20210907143838023.png)]

• 删除分区

alter table table_name drop partition p_cn

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5zqxnW2b-1631003154631)(创建表.assets/image-20210907144305363.png)]

• 添加分区

alter table table_name add PARTITION `p_usa` VALUES IN (("New York","2021-09-01"),("Chicago","2021-09-02"),("Boston","2021-09-02"),("Alaska","2021-09-03")),

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wPd3DBy4-1631003154631)(创建表.assets/image-20210907144626722.png)]

4.6 属性

属性的概念非常大，不同的引擎它的属性是不一样的，正如olap引擎，它的设置可以是该表数据的初始存储介质、存储到期时间和副本数，也可以指定某列使用 bloom filter 索引 等等；如果是mysql引擎，可以设置mysql的一些属性，像host,port等链接信息。

一般的，我们使用olap引擎，主要关注以下几个属性：

1. 基本属性设置

属性	值	备注
storage_medium	[SSD|HDD]	设置存储位置
storage_cooldown_time	yyyy-MM-dd HH:mm:ss	设置存储过期时间
replication_num	3	存储副本数，默认为3，根据backends来决定设置
storage_format	V2	默认的存储格式，与Doris的版本更替相关

storage_medium：        用于指定该分区的初始存储介质，可选择 SSD 或 HDD。默认初始存储介质可通过fe的配置文件 `fe.conf` 中指定 `default_storage_medium=xxx`，如果没有指定，则默认为 HDD。
                        注意：当FE配置项 `enable_strict_storage_medium_check` 为 `True` 时，若集群中没有设置对应的存储介质时，建表语句会报错 `Failed to find enough host in all backends with storage medium is SSD|HDD`. 
storage_cooldown_time： 当设置存储介质为 SSD 时，指定该分区在 SSD 上的存储到期时间。
                        默认存放 30 天。
                        格式为："yyyy-MM-dd HH:mm:ss"
replication_num:        指定分区的副本数。默认为 3

当表为单分区表时，这些属性为表的属性。
当表为两级分区时，这些属性为附属于每一个分区。
如果希望不同分区有不同属性。可以通过 ADD PARTITION 或 MODIFY PARTITION 进行操作

2. 分区属性设置

属性	值	备注
dynamic_partition.enable	flase	是否开启动态分区，默认为flase
dynamic_partition.time_unit	HOUR|DAY|WEEK|MONTH	分区精度
dynamic_partition.start	${integer_value}	指定向前删除多少个分区
dynamic_partition.end	${integer_value}	指定提前创建的分区数量
dynamic_partition.prefix	${string_value}	指定创建的分区名前缀
dynamic_partition.buckets	${integer_value}	定自动创建的分区分桶数量

dynamic_partition.enable: 用于指定表级别的动态分区功能是否开启。默认为 true。
dynamic_partition.time_unit: 用于指定动态添加分区的时间单位，可选择为HOUR（小时），DAY（天），WEEK(周)，MONTH（月）。
                             注意：以小时为单位的分区列，数据类型不能为 DATE。
dynamic_partition.start: 用于指定向前删除多少个分区。值必须小于0。默认为 Integer.MIN_VALUE。
dynamic_partition.end: 用于指定提前创建的分区数量。值必须大于0。
dynamic_partition.prefix: 用于指定创建的分区名前缀，例如分区名前缀为p，则自动创建分区名为p20200108
dynamic_partition.buckets: 用于指定自动创建的分区分桶数量
dynamic_partition.create_history_partition: 用于创建历史分区功能是否开启。默认为 false。
dynamic_partition.history_partition_num: 当开启创建历史分区功能时，用于指定创建历史分区数量。

2. 其他设置

属性	值	备注
in_memory	false	是否加载到内存（默认flase）
function_column.sequence_type		创建UNIQUE_KEYS表时，可以指定一个sequence列，当KEY列相同时，将按照sequence列进行REPLACE(较大值替换较小值，否则无法替换)

1.当 in_memory 属性为 true 时，Doris会尽可能将该表的数据和索引Cache到BE 内存中
2.sequence_type用来指定sequence列的类型，可以为整型和时间类型