Doris学习笔记_doris update

1、建表语句样例

CREATE TABLE hps.CODOMAIN (

CODOMAIN_ID VARCHAR(32) NOT NULL COMMENT '主键ID(同值域代码)',

CODE VARCHAR(255) COMMENT '值域代码',

NAME VARCHAR(255) COMMENT '值域名称',

DESCRIBE_INFO VARCHAR(255) COMMENT '描述',

IS_DEL VARCHAR(32) COMMENT '删除标识',

CREATE_DATE VARCHAR(36) COMMENT '创建时间',

CREATE_BY VARCHAR(64) COMMENT '创建人账号',

UPDATE_DATE VARCHAR(36) COMMENT '更新时间',

UPDATE_BY VARCHAR(64) COMMENT '修改人账号'

) ENGINE = olap UNIQUE KEY(CODOMAIN_ID) COMMENT '值域码表' DISTRIBUTED BY HASH(CODOMAIN_ID) BUCKETS 1 PROPERTIES("replication_num" = '1');

注：

ENGINE = 默认olap | mysql | broker

unique | Duplicate | Aggregate KEY(DETAIL_ID)

DISTRIBUTED BY HASH(DETAIL_ID) BUCKETS 1;

数据模型

DORIS 的数据模型主要分为3类:

• Duplicate 明细模型

• Aggregate 聚合模型

• Unique 唯一主键模型

聚合模型

聚合模型需要用户在建表时显式的将列分为 Key 列和 Value 列。该模型会自动的对 Key 相同的行，在 Value 列上进行聚合操作

1. SUM：求和，多行的 Value 进行累加。

2. REPLACE：替代，下一批数据中的 Value 会替换之前导入过的行中的 Value。

3. MAX：保留最大值。

4. MIN：保留最小值。

5. REPLACE_IF_NOT_NULL：表示，当遇到 null 值则不更新

明细模型

明细模型是 DORIS 默认使用的数据模型。该数据模型不会对导入的数据进行任何处理。表中的数据即用户导入的原始数据。

建表语句中指定的 DUPLICATE KEY，只是用来指明底层数据按照那些列进行排序。

唯一主键模型

Unique 模型完全可以用聚合模型中的 REPLACE 方式替代。其内部的实现方式和数据存储方式也完全一样

暂时无法在飞书文档外展示此内容

等同于

暂时无法在飞书文档外展示此内容

数据模型的选择建议

因为数据模型在建表时就已经确定，且无法修改。所以，选择一个合适的数据模型非常重要。

1. Aggregate 模型可以通过预聚合，极大地降低聚合查询时所需扫描的数据量和查询的计算量，非常适合有固定模式的报表类查询场景。但是该模型对 count(*) 查询很不友好。同时因为固定了 Value 列上的聚合方式，在进行其他类型的聚合查询时，需要考虑语意正确性。

2. Unique 模型针对需要唯一主键约束的场景，可以保证主键唯一性约束。但是无法利用 ROLLUP 等预聚合带来的查询优势（因为本质是 REPLACE，没有 SUM 这种聚合方式）。

3. Duplicate 适合任意维度的 Ad-hoc 查询。虽然同样无法利用预聚合的特性，但是不受聚合模型的约束，可以发挥列存模型的优势（只读取相关列，而不需要读取所有 Key 列）。

分区与分桶

Doris 支持两层的数据划分。第一层是 Partition，仅支持 Range 的划分方式。第二层是 Bucket（Tablet），仅支持 Hash 的划分方式。

也可以仅使用一层分区。使用一层分区时，只支持 Bucket 划分。

分区：

➢ Partition列可以指定一列或多列。分区类必须为 KEY列。多列分区的使用方式在

后面介绍。

➢ 不论分区列是什么类型，在写分区值时，都需要加 双引号 。

➢ 分区数量理论上没有上限。

➢ 当不使用 Partition建表时，系统会自动生成一个和表名同名的，全值范围的

Partition。该 Partition对用户不可见，并且不可删改。

Range Partition:

暂时无法在飞书文档外展示此内容

List Partition:

暂时无法在飞书文档外展示此内容

分桶：

暂时无法在飞书文档外展示此内容

1 如果使用了 Partition，则 DISTRIBUTED ... 语句描述的是数据在各个分区内的划

分规则。如果不使用 Partition，则描述的是对整个表的数据的划分规则。

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

3 分桶列的选择，是在 查询吞吐 和 查询并发 之间的一种权衡：

①如果选择多个分桶列，则数据分布更均匀。

如果一个查询条件不包含所有分桶列的等值条件，那么该查询会触发所有分桶同时

扫描，这样查询的吞吐会增加，单个查询的延迟随之降低。这个方式适合大吞吐低并发

的查询场景。

②如果仅选择一个或少数分桶列，则对应的点查询可以仅触发一个分桶扫描。

此时，当多个点查询并发时，这些查询有较大的概率分别触发不同的分桶扫描，各

个查询之间的 IO影响较小（尤其当不同桶分布在不同磁盘上时 ），所以这种方式适合

高并发的点查询场景。

4 分桶的数量理论上没有上限。

常用SQL

1、show full columns from 库名.表名 -------查看表基本信息

2、CREATE DATABASE [IF NOT EXISTS] db_name ------------创建库

3、ALTER TABLE 表名 ADD COLUMN 字段 VARCHAR(1) COMMENT '注释'; -------新增字段

4、ALTER TABLE 表名 modify column 字段 varchar(200) null comment '注释'   ------修改字段

5、CREATE INDEX IDX_IDENTITY_TYPE_CODE_IC ON report_lab_detail_stats1 (IDENTITY_TYPE_CODE) USING BITMAP COMMENT '证件类型'; -------新增索引

6、DROP INDEX 索引名 ON 表名;-------删除索引

7、ALTER TABLE 表名 DROP COLUMN 字段 ------删除字段

8、创建用户

#创建业务用户

CREATE USER 'yewu01'@'%' IDENTIFIED BY 'yewu01@1234#';

CREATE USER 'yewu02'@'%' IDENTIFIED BY 'yewu02@1234#';

#合理限制业务用户的链接数量

SET PROPERTY FOR 'yewu01' 'max_user_connections' = '32';

SET PROPERTY FOR 'yewu01' 'max_user_connections' = '32';

#dataviz_data_base：yewu01读写，yewu02只读

GRANT ALL ON dataviz_data_base.* TO 'yewu01'@'%';

GRANT SELECT_PRIV ON dataviz_data_base.* TO 'yewu02'@'%';

字段类型

TINYINT 1字节 范围：-2^7 + 1 ~ 2^7 - 1

SMALLINT 2字节 范围：-2^15 + 1 ~ 2^15 - 1

INT 4字节 范围：-2^31 + 1 ~ 2^31 - 1

BIGINT 8字节 范围 ：-2^63 + 1 ~ 2^63 - 1

LARGEINT 16字节 范围 ：-2^127 + 1 ~ 2^127 - 1

FLOAT 4字节 支持科学计数法

DOUBLE 12字节 支持科学计数法

DECIMAL[(precision, scale)] 16字节 保证精度的小数类型。默认是 DECIMAL(10, 0) precision: 1 ~ 27 scale: 0 ~ 9 其中整数部分为 1 ~ 18 不支持科学计数法

DATE 3字节字节 范围：0000-01-01 ~ 9999-12-31

DATETIME 8字节字节 范围：0000-01-01 00:00:00 ~ 9999-12-31 23:59:59

CHAR[(length)] 定长字符串。长度范围：1 ~ 255。默认为认为1

VARCHAR[(length)] 变长字符串。长度范围：1 ~ 65533

BOOLEAN 与TINYINT一样一样，，0代表代表false，，1代代表表true

HLL 1~16385个字节个字节 hll列类型，不需要指定长度和默认值、列类型，不需要指定长度和默认值、长度根据数据的聚合程度系统内控制，并且HLL列只能通列只能通过配套的过配套的hll_union_agg、、Hll_cardinality、、hll_hash进行查询或使进行查询或使用用

BITMAP bitmap列类型，不需要指定长度和默列类型，不需要指定长度和默认值。表示整型的集合，元素最大支持认值。表示整型的集合，元素最大支持到到2^64 - 1

STRING 变长字符串，字符串，0.15版本支持，版本支持，最大支最大支持持2147483643 字节（字节（2GB-4）），长度，长度还还受受be 配置配置string_type_soft_limit, 实际能存储的最大长度取两者最小值。只能用在value 列，不能用在列，不能用在 key 列和分区、分桶列

注意事项

1、doris 数据字段值是区分空串和null的，和oracle有区别，需要进行null值处理。

2、（ update 语句目前仅支持 Unique Key 模型）

3、replication_num 最大副本数量取决于集群中独立IP的数量（注意不是 BE数量）

关键字

&& add admin after aggregate all alter and anti as asc authors backend backends backup begin between bigint bitmap bitmap_union boolean both broker buckets builtin by cancel case cast chain char character charset check cluster clusters collate collation column columns comment commit committed config connection connection_id consistent count create cross cube current current_user data database databases date datetime day decimal decommission default delete desc describe distinct distinctpc distinctpc distinctpcsa distinctpcsa distributed distribution dynamic div double drop dropp duplicate else end engine engines enter errors events except exists explain export external extract false file first float follower following for format free from frontend frontends full function functions global grant grants group grouping hash having help hll hll_union hour hub identified if in index indexes infile inner insert int integer intermediate intersect interval into is isnull isolation install join key keys kill label largeint last left less level like limit link load local location max maxvalue merge migrate migrations min minus minute modify month name names negative no not null nulls observer offset on only open or order outer outfile over partition partitions password path pause plugin plugins preceding primary proc procedure processlist properties property query quota random range range read real recover regexp release rename repair repeatable replace replace_if_not_null replica repositories repository resource resources restore resume returns revoke right rlike role roles rollback rollup routine row rows schema schemas second select semi serializable session set sets show smallint snapshot soname split start status stop storage string sum superuser sync system table tables tablet task temporary terminated than then timestamp tinyint to transaction triggers trim true truncate type types unbounded uncommitted union unique unsigned use user using uninstall value values varchar variables view materialized warnings week whitelist when where whitelist with work write year ||