查看“Doris基础”的源代码

==== 连接 ====
 mysql -h fe_server -P query_port -uroot
 mysql -h 192.168.0.158 -P 9030 -uroot

==== Create ====
===== User =====
 create user bi identified by '1234';
 -- set password for bi = password('abcd1234');
 grant all on bi to bi;

==== 数据模型 ====
默认副本数为3。如果 BE 节点数量小于3，则需指定副本数小于等于 BE 节点数量。

非默认副本数，需要在建表时指定。

properties ("replication_allocation" = "tag.location.default: 1")

===== Aggregate =====
聚合模型
* Value 列会按照设置的 AggregationType 进行聚合，如：sum, max, replace 等
* AGGREGATE KEY() 指定 key，未被指定的，需要提供  AggregationType，如：`cost` BIGINT SUM DEFAULT "0" 
* 读时合并（merge on read)，因此在一些聚合查询上性能不佳
 create table test_a
 (
    ky   int,
    name varchar(10),
    val  int sum default "0" 
 )
 aggregate key(ky, name)
 distributed by hash(`ky`) buckets 1
 properties (
    "replication_allocation" = "tag.location.default: 1"
 )

===== Unique =====
唯一模型
* 保持 key 列的唯一，新值替换旧值
* 写时合并（merge on write）
* 可以在 be.conf 中添加配置项 disable_storage_page_cache=false，可能会优化数据导入性能
 create table test_u
 (
    ky   int,
    name varchar(10),
    val  int
 )
 unique key(ky, name)
 distributed by hash(ky) buckets 1
 properties (
    "replication_allocation" = "tag.location.default: 1",
    "enable_unique_key_merge_on_write" = "true"
 )

===== Duplicate =====
可重复模型
* 不对导入数据做任何操作
* 建表语句中指定的 DUPLICATE KEY，只是用来指明底层数据按照那些列进行排序。（更贴切的名称应该为 “Sorted Column”）
 create table test
 (
    ky   int,
    name varchar(10),
    val  int
 )
 distributed by hash(ky) buckets 1
 properties (
    "replication_allocation" = "tag.location.default: 1",
    "enable_duplicate_without_keys_by_default" = "true"
 )

===== 分区、分桶 =====
* list
10 个分区，6 个桶，3 个副本
 create table test_p
 (
    part tinyint not null,
    ky   int,
    name varchar(10),
    val  int
 )
 duplicate key(part, ky)
 partition by list(part)
 (
    partition p_0 values in(0),
    partition p_1 values in(1),
    partition p_2 values in(2),
    partition p_3 values in(3),
    partition p_4 values in(4),
    partition p_5 values in(5),
    partition p_6 values in(6),
    partition p_7 values in(7),
    partition p_8 values in(8),
    partition p_9 values in(9)
    -- partition p_0 values in(2,4,6,8,0),
    -- partition p_1 values in(1,3,5,7,9)
 )
 distributed by hash(ky) buckets 6
 properties (
    "replication_allocation" = "tag.location.default: 3"
 )

* range
 create table test1
 (
    part tinyint not null,
    ky   int,
    name varchar(10),
    val  int
 )
 duplicate key(part, ky)
 partition by range(part)
 (
    partition p_0 VALUES less than (5),
    partition p_1 VALUES less than (10),
    partition p_9 VALUES less than maxvalue
    -- partition p_0 VALUES [(0), (5)),
    -- partition p_1 VALUES [(6), (10))
 )
 distributed by hash(ky) buckets 6
 properties (
    "replication_allocation" = "tag.location.default: 3"
 )


[[分类:Develop]]
[[分类:DB]]
[[分类:Doris]]