修改记录
时间 | 内容 |
---|---|
2020年9月13日 | 第一次发布 |
分析型数据库AnalyticDB(原名 ADS)是阿里巴巴针对海量数据分析自主研发的实时高并发在线分析系统,可以针对万亿级别的数据进行多维度分析透视和业务探索。采用分布式计算,具有强大的实时计算能力。
主要特点就是实时和高并发,可以针对万亿级别的数据进行多纬度分析透视和业务探索。
AnalyticDB数据库支持多种列数据类型,如下:
– 待补充
ADS逻辑存储对象包括:数据库、表组、表,其中表分为维度表和事实表。
数据库,database 或 schema,在ADS中是最⾼层的对象,按数据库进⾏资源的分配、隔离和管理,实现了多租户的管理能⼒。
表组(table group)是⼀系列数据表的集合,通常将同⼀业务下的表归属到⼀个表组,便于表的分类和管理。
就是统一业务类型的表集合。
在ADS的LM引擎下,还要求Join的两张表是同一表组,而MMP引擎则没有这个要求。
分析型数据库中表组分为两类:维度表组和事实表组。
一个数据库可以创建多个表组。
CREATE TABLEGROUP [db_name.]tablegroup_name;
表按数据仓库模型分为:
表按更新模式分为:
ADS⽀持2级分区策略,将表数据分布到不同的节点,⼀级分区采⽤hash算法,⼆级分区采⽤list 算法。
一级分区方式大致和Hive的HashPartitioner一样。
如下图所示,事实表按ID进⾏⼀级分区,通过CRC32算法将不同ID值分布到不同的节点。⼆级分区 采⽤按⽇期(bigint类型)进⾏分区–每天⼀个⼆级分区。
HASH分区是事实表的一级分区,说明如下:
LIST分区是事实表的二级分区,说明如下:
AnalyticDB为解决⼤数据索引的问题,采⽤默认模式预先为所有列创建索引,可以在明确表的某⼀列不需要索引情况,可以显式的disable index。(牛逼。。。)
AnalyticDB为每个分区⾃动创建了下列索引:
对于倒排索引的解释参考:https://blog.csdn.net/starzhou/article/details/87519973
AnalyticDB的realtime类型的表必须包含主键字段,AnalyticDB⽀持realtime表insert/delete,通过主键进⾏相同记录的判断,确定唯⼀记录。
像前面介绍表,实时表可以通过delete、insert的方式更新数据,而在插入数据时就要根据主键来判断唯一值。
主键组成:(业务id+⼀级分区键+⼆级分区键),有些情况,业务id与⼀级分区相同。对于记录量特别⼤的表,从存储空间和insert性能考虑,⼀定要减少主键的字段数。在之前的公司,有用多个列的MD5值来作为主键的。
注意:
ADS支持将一列或多列进行排序,保证该列值相同或相近的数据存储在磁盘同一位置,这样的列叫做聚集列。
它的好处是,当以聚集列为查询条件时,查询结果保存在磁盘相同位置,可以减少IO次数,提高查询性能。
由于主聚集列只有⼀列,因此需要最合适的列作为主聚集列,聚集列的选择如下:
基本原理: AnalyticDB数据按列存储,对每列按固定记录数切块,作为IO的基本单位。如果数据块太⼤,容易导致单块有效数据量⽐例较⼩,增加单次IO latency;反之如果数据块太⼩,会增加IO次数,影响查 询性能。
配置建议: 需要根据业务本身查询特点,⽤户选择合适的块⼤⼩。对于包含聚集列(单块中有多条有效数据)或者内存资源较为充⾜情况下,适合采⽤较⼤的块⼤⼩(超过或等于32760);反之如果没有聚集列,同时查询结果的列个数⼜特别多时,建议设置较⼩的块⼤⼩。⽬前AnalyticDB默认块⼤⼩为32760。
注意:修改块⼤⼩只对新导⼊(或基线合并)的数据有效,对历史数据⽆效。
– 待补充
多值列是AnalyticDB特有的数据类型
暂时参考《阿里云 专有云企业版 V3.7.1 分析型数据库 用户指南 20190124》 – 6.2.2
AnalyticDB支持通过界面操作或SQL操作来创建事实表组。
在AnalyticDB数据库中,您只可以创建事实表组,维度表组是唯一的,在创建数据库时自动生成,命名为数据库名*_dimension_group*。
CREATE TABLEGROUP ads_demo --表组名
options(minRedundancy=2 executeTimeout=30001; --设置选项:副本数、超时时间
事实表组创建完成后,后续可根据实际需要修改事实表组的最小副本数和超时时间,但不支持修改表组名称。
同样支持界面修改和SQL修改。
ALTER tablegroup ads_demo minRedundancy = 4;
当想删除某个表组时,必须要先删除该表组下的所有表,然后才能删除表组。
只支持SQL删除。
DROP TABLEGROUP ads_demo;
-- 示例
-- 创建事实表
CREATE TABLE t_fact_orders
(
order_id varchar COMMENT '',
customer_id varchar COMMENT '',
goods_id bigint COMMENT '',
numbers bigint COMMENT '',
total_price double COMMENT '',
order_time timestamp COMMENT '',
order_date bigint COMMENT '',
PRIMARY KEY (order_id, customer_id, order_date) )
PARTITION BY HASH KEY (customer_id) PARTITION NUM 128 --一级分区 + 分区数
SUBPARTITION BY LIST KEY (order_date) --二级分区 + 二级分区最大分区数
SUBPARTITION OPTIONS (available_partition_num = 90)
--[CLUSTERED BY (col3,col4)] --CLUSTERED BY ⼦句⽤于指定聚集列
TABLEGROUP ads_demo --指定表组,同一表组的表才能hash join
OPTIONS (UPDATETYPE = 'realtime') --创建一张实时更新表,带主键,如果 updateType选项不填则默 认为批量更新表
COMMENT '';
-- 创建维度表:
CREATE DIMENSION TABLE t_dim_goods (
goods_id bigint comment '',
price double comment '',
class bigint comment '',
name varchar comment '',
update_time timestamp comment '',
primary key (goods_id)
)
OPTIONS (UPDATETYPE = 'realtime'); --维度表比较简单,用dimension指定就可以到维度表组
创建表成功后,表的列、一级分区、表名、表组、更新方式均不可更改(除非重建),但您可以修改查询超时时间、聚集列、注释,并且可以新增列。
-- 增加列
ALTER TABLE t_fact_orders ADD new_col varchar;
-- 二级分区数是可以修改的,最⼤⼆级分区数⽬前可以在建表后进⾏在线修改
ALTER TABLE [db_name.[table_name subpartition_available_partition_num = N;
-- 删除表
DROP TABLE tab_01;
-- 查看表中字段的顺序
SHOW CREATETABLE db_name.table_name;
ADS默认为所有列创建index,同时可以⽀持选择性取消列的索引。
什么时候该选择取消索引,参考原则:
创建表时指定某列为 disableIndex true,则会取消该列的索引;创建表后,不支持修改索引。
-- 取消索引示例
CREATE TABLE t_fact_orders
(
order_id varchar COMMENT '',
customer_id varchar COMMENT '',
goods_id bigint COMMENT '',
numbers bigint disableIndex true COMMENT '',
total_price double disableIndex true COMMENT '',
...
AnalyticDB 支持 JSON 数据类型和 JSON 索引。
创建表时,您可以指定列为 JSON 数据类型,语法示例如下:
CREATE TABLE t_fact_json (
id int COMMENT '',
data json,
PRIMARY KEY (id) )
PARTITION BY HASH KEY (id) PARTITION NUM 16
TABLEGROUP ads_demo
OPTIONS (UPDATETYPE='realtime')
COMMENT '';
创建表时,您可通过 jsonIndexAttrs ‘’ 语法指定要为JSON 中的哪些属性构建索引。如果不带 jsonIndexAttrs ‘<attributes to be indexed>’ ,则表示对 JSON 的所有属性都构建索引。注意,这里是说的对Json字段里面细分的哪些属性。
CREATE TABLE t_fact_json (
id int COMMENT '',
data json jsonIndexAttrs '$.name, $.company.company_address' comment '', --这里对json构建索引
PRIMARY KEY (id) )
PARTITION BY HASH KEY (id) PARTITION NUM 16
TABLEGROUP ads_demo
OPTIONS (UPDATETYPE = 'realtime')
COMMENT '';
插入数据示例:
insert into t_fact_json (id, data) values(0, '{"id":0,"name":"tjy", "age":0}');
查询数据:
select * from t_fact_json where json_extract(data, '$.company') = 'alibaba';
AnalyticDB 会在 FRONTNODE 节点构建本地 local 的内置数据库引擎,内置数据库引擎存储一定量的本地数据缓存表(Cache Table),以便您快速对本地单表进行查询。
基于Cache table,您可进行一定范围内的高效的分页数据查询。但 Cache Table 只能作为临时存储,不能作为永久性存储。
CREATE TABLE cache.table_name OPTIONS(cache=true)
AS
SELECT * FROM table_name;
/* +cache_id = 1683065103.38806.6.0.082539 */
SELECT * FROM cache.test_cache_table_1;
/* +cache_id = 1683065103.38806.6.0.082539 */
DROP TABLE cache.test_cache_table_1;
在 AnalyticDB 中,只有实时更新表(realtime)支持 DML 语言,批量更新表(batch)不支持。实时更新表支持的 DML 语句包括:INSERT 和 DELETE。
可以用Insert插入实时更新表,插入后有延迟,约一分钟后能查到数据。
一次提交16KB数据时,数据库性能处于最佳状态。现场实际使用时,建议根据表行长来确定一次提交的记录数 N ,N = 16KB/rowsize。
INSERT INTO table_name [ ( column [, ... ] ) ] VALUES [(),()]
INSERT INTO db_name.target_table_name [ ( column [, ... ] ) ]
SELECT col1, ... FROM db_name.source_table_name where ...;
-- 或者能保证字段顺序下:
INSERT INTO table_name(co1,col2,col3,...) VALUES(?,?,?,...)
INSERT INTO db_name.target_table_name
SELECT col1, ... FROM db_name.source_table_name
WHERE ...;
INSERT和INSERT IGNORE的区别如下:
在实际应用中,您可根据业务应用的需求来选择 INSERT 或 INSERT IGNORE 语句。
INSERT FROM SELECT 语句支持在LM(Local-Merge)、 MPP 和Native MPP三种引擎模式执行。
/*+engine=COMPUTENODE*/
INSERT INTO db_name.target_table_name (col1, col2, col3)
SELECT col1, col2, col3 FROM db_name.source_table_name
WHERE col4 = 'xxx';
/*+engine=MPP*/
INSERT INTO db_name.target_table_name (col1, col2, col3)
SELECT col1, col2, col3 FROM db_name.source_table_name
WHERE col4 = 'xxx';
/*+engine=MPP, mppNativeInsertFromSelect=true*/
INSERTINTO db_name.target_table_name (col1, col2, col3)
SELECT col1, col2, col3 FROM db_name.source_table_name
WHERE col4 ='xxx';
当通过 INSERT FROM SELECT 语句插入大量数据(1000万条以上的记录)时,您需要进行长时间的等待,此时您可通过 run_async=true Hint 来进行异步化执行。
进入异步化执行的语句后,可以通过查询元数据表 information_schema.async_task 来查看三天内异步化任务的执行状态,STATUS字段为SUCCESS执行成功,如下:
-- 加run_async=true hint来进入异步化执行
-- sql执行后会返回异步化执行的ID
/*+run_async=true, engine=mpp, mppNativeInsertFromSelect=true*/
INSERT INTO tpch_junlan.insert_from_select_test
SELECT * FROM lineitem;
--返回:
+-----------------------------------+
| ASYNC_TASK_ID |
+-----------------------------------+
| xxxxx_19010_1501141772740 |
+-----------------------------------+
SELECT * FROM information_schema.async_task
WHERE id = 'xxxxx_19010_1501141772740';
--返回:
+------------+-----------------------+-----------------------+
| CLUSTER_NAME | TABLE_SCHEMA | ID |
TASK_NAME | STATUS | MESSAGE
| PROCESS_ID | COMMAND
| CREATOR_ID | CREATE_TIME
| UPDATE_TIME |
+--------------+--------------+-----------------------------------
| dailybuild | tpch_junlan | xxxxx_19010_1501141772740 | Insert
From Select | SUCCESS | task has been processed successfully. |
2017072715493210008113606009999000098 | insert into tpch_junlan.
insert_from_select_test select * from lineitem | $ | 2017-07-
27 15:49:33.0 | 2017-07-27 15:50:02.0 |
+--------------+--------------+-----------------------------------
实时更新表可以delete表中的部分数据,但批量更新表是不可以删除数据的,只能整表drop掉。
注意:
AnalyticDB目前拥有 COMPUTENODE Local-Merge(简称LM))和 Full MPP Mode(简称MPP)两套计算引擎,两种计算引擎在 SELECT 查询时各有优缺点。同时,您还可通过Hint强制指定计算引擎。
LM是ADS默认的引擎,MMP是新增的引擎,两者区别如下:
对比项 | LM | MPP |
---|---|---|
优缺点 | 计算性能很好、并发能力强,但对部分跨一级分区列的计算支持差。 | 计算功能全面、支持跨一级分区列的计算,但查询响应时间和并发能力不如 LM 。 |
Hint写法 | /* +engine = COMPUTENODE */ | /* +engine = MPP */ |
个人理解两者的区别就是LM的计算性能和并发能力都比MMP强,但是在对一些复杂的查询或者跨一级分区列的查询不支持,比如数学函数、窗口函数、Group by仅非分区列等这种LM无法做到;
而MPP虽然计算性能和并发没有LM那么好,但具备LM所不支持的计算功能,这两者应该是互补关系。
交集:Intersect & Intersect distinct(交集后去重):返回两个查询结果的交集
并集:Union All & Union
差集:Minus :(返回仅存在于左查询结果集而不在右查询结果集的数据行)
MPP模式下的差集是用Except
SHOW 语句,您可以查询用户的数据库、表组、表信息,查询表的列信息,查询表的 DDL 建表语句,以及查询正在运行的 MPP 任务等
-- 查询用户的数据库列表
-- 指定 EXTRA 参数,输出关于数据库的更多信息
SHOW DATABASES [LIKE 'name_pattern'] [EXTRA];
-- 查询用户当前数据库下的表组列表
SHOW TABLEGROUPS;
SHOW TABLEGROUPS IN ads_demo;
-- 查询用户当前数据库(或表组)下的表的列表
SHOW TABLES [IN db_name[.tablegroup_name]]
-- 查询表的列信息
SHOW COLUMNS IN table_name;
-- 查询表的 DDL 建表语句。
SHOW CREATE TABLE [db_name.]table_name;
-- 查询当前正在运行的 MPP 任务
-- 如果指定 /*+cross-frontnode=true*/ Hint,则查询当前数据库实例所有正在运行的 MPP 任 务,否则只查询当前连接的 FRONTNODE 节点实例运行的 MPP 任务。
[/*+cross-frontnode=true*/]
SHOW PROCESSLIST MPP;
– 待补充
数据入库方式:AnalyticDB 中表的数据更新方式包括批量更新和实时更新两种,批量更新方式对应的 SQL 命令为LOAD DATA 批量导入,实时更新方式对应的 SQL 命令为 INSERT。
AnalyticDB 支持多种数据入库方式,包括但不限于以下方式:
注意事项:
在 DMS For AnalyticDB 控制台,选择菜单栏中的导入导出 > 导入。
如果 MaxCompute 的数据类型是以下类型,则必须手动改写成 AnalyticDB 支持的类型。
AnalyticDB 目标表的列名要与源表中的列名一致。MaxCompute 源表的列类型与 AnalyticDB 目标表的对应的列类型可以不一致,但二者必须能够成功转换
如果发生长尾,需要检查分区键是否合理,数据分布是否均匀,可以检查MaxCompute源表,按分区列group by并计算count():
odps@ garudadc>select __aid, count(*) as count
from dmj_ex_1.allcase_action
group by __aid order by count desc limit 5
--返回结果:
+------------+------------+
| __aid | count |
+------------+------------+
| 0 | 2124978 |
| 9 | 5197 |
| 6 | 5185 |
| 1 | 5172 |
| 5 | 5097 |
+------------+------------+
通过大数据开发套件(DataWorks)的数据集成任务里的数据同步进行操作。
这两部分用到时参考官方文档,这里不做说明。
– 待补充
– 待补充
ADS的事实表支持二级分区策略,一级分区采用Hash算法,二级分区采用List算法,通过二级分区策略,ADS可将表数据分布到不同节点。
在ADS中,事实表的逻辑存储如下图:
如上图,事实表一级分区按id进行求hash值,然后在对分区总数m求模运算,以此来将不同id值的数据分布到不同节点。
事实表的二级分区则按日期进行分区。单个二级分区的记录数不宜太小,比如:如果每天有2000万新增数据(每个一级分区每天新增记录数:2000万/32 = 62万),则建议按周划分二级分区(每个二级分区的总记录数:62万*7天 = 434万)。如果每天有300万新增数据,则建议按月划分二级分区。
在ADS中,维度表的逻辑存储则比较简单,采用复制的方式存储在每个节点上。如下:
在 AnalyticDB 中,数据表的分区存储示意图如下:
可以把下图当做6.1.1 的补充。
基本原理:AnalyticDB 的表一级分区采用 HASH 分区,可指定任意一列(不支持多列)作为分区列。HASH 分区通过标准 CRC 算法计算出 CRC 值,并将 CRC 值与分区数作模计算,得出每条记录的分区号。
在 AnalyticDB 中,调度模块会将同一个表组下所有表的相同分区分配在同一个计算节点上。因此,当多表使用分区列进行 JOIN 时,单计算节点内部直接计算,避免了跨机计算。
在ADS中,一级分区的选择依据如下(按优先级从高到低排):
一级分区个数选择:
一般情况下,每个一级分区下会包含多个二级分区。二级分区主要用于解决数据表需要按固定时间周期(例如:天、周、月、年)增加数据的问题,一般也是选择为按天、周、月、年这样时间特征的字段,同时二级分区还考虑了保留一定时间范围的历史数据。
二级分区采用 LIST 分区,不同值的个数即为二级分区数。
二级分区列是数据表中的一个 bigint 类型的列,通常为bigint类型的日期,如2020090310
二级分区适用场景:一般情况下,当一级分区数据量随时间增大到超过单个一级分区记录数最佳推荐值(2000万~3000万)时,需要考虑设计二级分区。二级分区可以理解为按队列方式管理分区个数,当超过最大定义数,最小值分区自动删除,循环使用空间,所以二级分区支持自动清除历史数据。
但是如果二级分区数过多,则会导致多次索引查询、性能下降,并且二级分区有自身的元数据信息,过多也会导致占用更多的内存。如果过少,则导致用户导入数据频率降低,从而影响数据实时性。
一般情况下,如果单个分区每日增量数据超过300万,则推荐按天进行二级分区;如需要存储的时间范围更长,则可按周、月进行规划。如果有二级分区,则保证一级分区下的每个二级分区的记录数在300万条到2000万条之间。另外虽然单表的最大二级分区数支持365*3个,但单表二级分区数推荐小于等于90,同时每个计算节点上总的二级分区个数不超过10 000个。
可以通过 DMS 管理工具修改表的聚集列。实时更新表修改后,新插入(INSERT)的数据在optimize 后才会生效。
在ADS中,实时更新表必须包含主键,同时数据的insert和delete操作都要根据主键来判断唯一记录。但ADS的主键构成和其他数据库有所不同,如下,可以是其他键的组合。
主键组成:业务 ID + 一级分区键 + 二级分区键。
如果表记录数特别大,从存储空间和 INSERT 性能考虑,一定要减少主键的字段数。
最主要的还是要从业务角度确保生成的主键在该表能代表唯一值。
因此,建议在选择列的数据类型时尽可能使用数值类型,减少使用字符串类型。
建表指导原则:
数据倾斜即数据在数据库中的存储分布不均衡,引起数据倾斜常见原因如下:
数据倾斜会给 AnalyticDB 带来存储溢出、计算长尾问题,从而导致数据库业务中断、查询超时。
具体来说:
在创建表前,您必须进行充分的业务数据调研和数据倾斜验证,以规避数据倾斜。
为规避数据倾斜,按一级分区列选择原则选择一级分区后,还需要注意以下事项:
调研一级分区不同值个数,一般要求不同值个数是设置的一级分区数的N倍,N要大于10,否则要进行第二步
select count(distinct 一级分区列) from tab
对一级分区键group by统计分区的数据总数来检查数据是否分布均匀
select 一级分区, count(*) from tab group by 一级分区列 order by count(*) desc
检查是否有空值(’’),并查询空值的数据量
select count(*) from t_fact_mail_status where org_code='';
– 待补充
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/192438.html原文链接:https://javaforall.cn