[视频]云原生数据仓库 AnalyticDB PostgreSQL 版解析与实践(上)｜学习笔记（二）

开发者学堂课程【数据仓库 ACP 认证课程：[视频]云原生数据仓库 AnalyticDB PostgreSQL 版解析与实践(上) 】学习笔记，与课程紧密联系，让用户快速学习知识。

课程地址：https://developer.aliyun.com/learning/course/928/detail/14626

[视频]云原生数据仓库 AnalyticDB PostgreSQL 版解析与实践(上)

 

三、产品相关概念

1.数据库实例及操作

（1）创建实例-规格选择

Segment

数量越多，单条查询性能越好;CPU核数越多，并发能力越好

计算节点/计算组

CPU内存资源被该节点/组内所有Segment共享

（2）建表:ADB PG逻辑架构

ADB PG实例

云平台上的一个MPP数据库集群，创建时分配固定资源，包含一组数据库、模式、表对象和数据以及用户

数据库( Database )

一个ADB PG实例中，包含多个逻辑Database，其中可包含模式等对象

模式(Schema )

逻辑概念，数据库中的逻辑空间，包含一系列表，视图等对象

表(Table )

数据分布定义∶按分布键Hash值，随机Random，或复制Replication三种方式，进行节点间数据分布

Hash分布不一定是均匀的，随机分布是均匀的

存储格式定义︰支持指定按行存储，或者按列存储

压缩算法定义(可选)︰支持多种高性能数据压缩算法

分区表支持（可选)∶对于大表，支持按区间Range ,或值LIST进行分区，且支持多级分区

（3）建表>表分布方式:在MPP节点间的三种分布方式

Hash分布是有规律的，随机分布没有规律，但是均匀的。

（4）建表并导入数据-分布与分区分区越多，存储的文件越多

定义分区是根据业务查询需要定义，一定是有分布的

（5）建表>分布键：表的分布键选择原则

①选择数据分布均匀的列

若选择的分布列数值分布不均匀，则可能导致数据倾斜。某些Segment分区节点存储数据多(查询负载高)。

根据木桶原理，时间消耗会卡在数据多的节点上.故不应选择bool类型，时间日期类型数据作为分布

②选择经常需要JOIN的列作为分布键

当JOIN键和分布键一致时,可以在 Segment分区节点

内部完成JOIN.

否则需要将一个表进行重分布来实现重分布关联或者广播其中小表来实现广播关联，后两种方式都会有较大的网络开销。

③选择高频率查询条件列作为分布键

从而可能实现按分布键做节点segment的裁剪

④默认表的主键为分布键

若表没有主键，则默认将第一列当做分布键

⑤其他原则

谨慎选择随机分布DISTRIBUTEDRANDOMLY，这将使得上本地关联，或者节点裁剪不可能实现

小表可选择复制表模式，在所有Segment上均保存一份全量数据

（6）建表>分布键：

create table tl(c1 int, c2 int) distributed by (c1);

通过下述语句查看表数据的倾斜情况︰

select gp_segment_id, count1) from tl group by 1 order by 2 desc;

gp_segment_id | count

---------+--------

2 | 131191

0 |     72

1 |     68

( 3 rows )

如果发现某些Segment上存储的数据明显多于其他Segment，该表存在数据倾斜。建议选取数据分布平均的列作为分布列

alter table tl set distributed by (c2 ) ;

（7）建表>压缩:多种压缩算法，成本和性能取得平衡

数据压缩可用于列存表或者行存追加表，平均3倍以上数据压缩率

推荐使用 ZSTD 与 LZ4

 CREATE TABLE foo (a int, b text)

WITH (appendonly=true, orientation=column, compresstype=zstd, compresslevel=9)DISTRIBUTED BY (a);

（8）统计信息

优化建议∶

统计信息收集方式可基于全库、表、列级别，可根据实际情况确定收集范围

导入数据后、超过20%数据更新(IUD)后、创建索引后，需进行统计信息收集

用户ETL任务过程中，会涉及多次IUD，可根据客户业务情况，在其中适当添加analyze语句

调优过程中，从执行计划中看到表行数估算为1行，计划中出现较多的Broadcast、Sort+GroupByAgg、NestLoop等算子时，考虑对相应数据表进行analyze

导入数据

create table t1 (a int, b int);

insert into t1 select vv from generate_series(1,1000) as v;analyze t1; --第一次加载大量数据后，重新收集统计信息

ETL 过程

truncate t1;

insert into t1 select * from t2;

insert into t1 select * from t3;

analyze t1; --重建数据后，重新收集统计信息

select a,b,c from t1 join t2 on t1.c=t2.d where t2.b = 2;

（9）建表并导入数据-建表示例

（10）建表并导入数据

DBStack 形态还支持 gpfdist 外表导入

COPY性能在30-50找Mb/s， OSS导入性能取决于带宽与节点个数，并行导入的方式

（11）执行SQL 查询

2.权限管理

（1）权限管理∶逻辑结构及其权限关系

①实例权限：

白名单配置

②数据库权限：

grant赋予是否允许连接或创造schema的权限

Revoke回收

③schema权限：

grant赋予允许查询schema中的对象

revoke回收

④object权限：

grant赋予

revoke回收

实例权限主要控制实例连接鉴权

数据库级别权限包括：

是否允许连接数据库

是否允许在数据库中创建schema

默认允许public角色性出的任

默认不允许除了超级用户和owner之外的任何人在数据库中创建schema

默认会自动创建名为public的schema,且允许任何人在里面创建对象

schema级别权限包括：

是否允许查看schema中的对象

是否允许在schema中创建对象

默认情况下新建的schema的权限不会赋予给public角色，因此除了超级用户和owner ,任何人都没有权限查看schema中的对象或者在schema中新建对象

（2）权限管理∶授予和撤销权限

①授予权限

关键字GRANT

GRANT权限ON对象类型对象名TO用户名.如:

GRANT SELECT ON TABLE table TO user1; --允许user1 select table.

直接对表的 SELECT 权限赋予 user

GRANT SELECT ON TABLE table TO public; --允许所有人select table撤销权限

②关键字REVOKE

REVOKE权限ON对象类型对象名FROM用户名.如:

REVOKE SELECT ON TABLE table FROM user1; --不再允许user1 select table

（3）权限管理:查询权限

SELECT n.nspname as "Schema",

c.reIname as "Name",

CASE c.relkind

WHEN 'r'THEN 'table'

WHEN 'v' THEN 'view'

WHEN 'm’THEN 'materialized view'

WHEN 'S" THEN 'sequence'

WHEN 'f THEN 'foreign table'

END as "Type",

pg_catalog.array_to_string(c.relacl,E\n')AS "Access privileges"

FROM pg_catalog.pg_class c

LEFT JOIN pg_catalog.pg_namespace n ON n.oid =c.relnamespace

WHERE c.relkind IN ('r, 'v' , 'm', 'S', 'f)

ANDn.nspname !~"^pg_'AND

pg_catalog.pg_table_is_visible(c.oid)

ORDER BY 1,2;

Access privileges:

其格式为rolename=xox/yyyy,表明yyyy 用户将xoox权限赋予给了rolename.若rolename为空,则表明public. xoox中每一个字符表明一个特定权限:

r -- SELECT ("read")

w -- UPDATE ("write")

a -- INSERT ("append")

d -- DELETE

D -- TRUNCATE

x -- REFERENCES

t -- TRIGGER

X -- EXECUTE

U -- USAGE

C -- CREATE

c -- CONNEC

T -- TEMPORARY

arwdDxt -- ALl PRIVILEGES(for tables, varies for other objects)

* -- grant option for preceding privilege

2. UDF 和存储过程

在AnalyticDB PostgreSQL中，创建UDF和存储过程都是采用CREATE FUNCTION语法。

不同于ORACLE、MYSQL等数据库，PostgreSQL中并没有专门用于创建存储过程的CREATE PROCEDURE语法。

以SQL过程语言PL/pgSQL用法最为广泛，最为贴近内核。

将多个数据加工成函数

PL/pgSQL

①用于创建函数和触发器过程

②为 SQL 语言增加控制结构

③执行复杂的计算

④继承所有用户定义类型、函数、操作符

⑤定义为被服务器信任的语言

⑥容易使用

（1）UDF和存储过程:PL/pgsQL基本结构介绍

AnalyticDB PostgresQL 函数通常结构如下:

Begin and 是一种语法格式

（2）UDF和存储过程∶块结构介绍

PL/pgSQL是一个块结构语言，函数体由块结构组成，定义如下︰

BLOCK

[ <> ]

[ DECLARE

declarations ]

BEGIN

statementsEND [ label ];

注意事项

块中的每个声明和每条语句都是用一个分号终止。

块结构支持嵌套使用。子块用于逻辑分组，在子块中声明的变量在其范围之内，将屏蔽跟这个子块外部有着同样的名字的变量。

BEGIN之后不要分号。

END之后要分号。最外层的可缺省。

END后的标签要和块开始的标签保持一致。

所有关键字不区分大小写，默认转换成小写，除非被双引号引用。

注释的方法和普通SQL一样。

PL/pgSQL里用于语句块分组的BEGIN/END不是开始或者结束事务。

（3）UDF和存储过程∶块结构-示例

CREATE OR REPLACE FUNCTION somefunc( RETURNS integer As $$

<< outerblock >>

DECLARE

quantity integer := 30;

BEGIN

RAISE NOTICE "Quantity here is %" , quantity,-- Prints 30

quantity := 50,

--

-- Create a subblock

DECLARE

quantity integer := 80;

BEGIN

RAISE NOTICE "Quantity here is %" , quantity,-- Prints 80

RAISE NOTICE "Outer quantity here is %" ,outerblock.quantity,-- Prints 50

END;

RAISE NOTICE "Quantity here is %" , quantity, -- Prints 50

RETURN quantity,

END;

$$ LANGUAGE plpgsql;