AWS Redshift ETL的几个性能最佳实践

迈不过友情╰ 2022-01-11 00:29 544阅读 0赞

Amazon Redshift 是一个支持SQL查询的、快速、可扩展的列式存储数据库，它支持PB级的数量查询，是适用于企业级的数据仓库。同时Redshift支持大规模并发查询、支持结果集缓存，响应查询时间最快至亚秒，比起其他数据仓库快将近十倍。借助 Redshift，您的等待时间更少，可将更多时间用于获取数据见解。

ETL在计算机领域是一个很流行的概念，意指将数据从一个或多个源头复制到目标系统的一个过程，其中包含三个步骤：  
1，Extract 从数据源中选择/提取需要导出的数据  
2，Transform 将导出的数据根据业务需要进行必要的格式/表现形式上的转换  
3，Load 将转换后的数据导入目标系统

在使用Redshift之前需要将数据导入Redshift，即Redshift的ETL。例如数据库的迁移，将旧数据库中的数据转移到Redshift等等。  
本文旨在分享我们bosicloud在日常工作中关于Redshift ETL方面的一些技巧及建议：

1，使用COPY命令将多个、大小相当的文件加载到Redshift  
Amazon Redshift是一个MPP数据库，即大规模并行处理数据库，Redshift的背后是一个EC2集群，每个计算节点（ec2）进一步细分为slice，所有slice平分计算节点的处理能力。 每个节点的slice数量取决于群集的节点类型。 例如，每个DS2.XLARGE计算节点都有两个slice，而每个DS2.8XLARGE计算节点有16个slice。  
![AWS Redshift ETL的几个性能最佳实践][AWS Redshift ETL]  
进行加载数据工作时，最好的情况是整个工作量平分给所有的计算节点（EC2 node）的slice。当只加载一个大文件，或者加载多个大小差异较大的文件时，都可能导致计算节点工作量分配不均等，进而导致整个Redshift加载数据时性能低下。例如，我们Redshift有两个计算节点，在加载一个大文件时每个节点的工作负载如下：  
![AWS Redshift ETL的几个性能最佳实践][AWS Redshift ETL 1]  
从上图可以看到，加载的任务落在了compute-0头上，而compute-1则是闲置的，这是一个木桶原理，Redshift最终花费的时间等于工作时间最长的那个计算节点。  
所以我们最好将大文件切割为多个大小相同的小文件，并且文件总数量正好是计算节点的整倍数，从而每个计算节点可以分到数目相同的小文件。另外，我们还建议将这些小文件进行压缩，例如gzip, lzop, or bzip2因为Redshift加载及存储数据时都支持这几种压缩格式。显然，压缩后的数据更小了，加载的工作量也就更小了。

2，使用workload management合理调整Redshift queue  
Redshift通过workload management（WLM）管理着多个queue，用户提交SQL查询任务到Redshift时，SQL会根据提交者所在的group被分派到group对应的queue排队等候执行。Redshift的内存及计算能力被分成许多个单元/单位，一个slot代表一个单元的内存及计算能力，一个slot同一时间可以执行一个SQL查询任务，不同的queue拥有不同数量的slot，slot的数量决定该queue能够同时并发执行多少个SQL查询任务。因为ETL往往伴随着许多COMMIT 操作，而COMMIT 都很消耗计算资源。为了进行ETL的同时不影响普通用户提交SQL query，我们bosicloud建议将ETL和普通用户提交的SQL query分发到不同的queue中。否则普通用户query可能由于等待ETL COMMIT导致不能及时响应。  
![AWS Redshift ETL的几个性能最佳实践][AWS Redshift ETL 2]  
另外，为了加快Redshift的COPY ETL过程，我们还可以通过wlm\_query\_slot\_count参数调整ETL queue的slot数量，从而增加queue的内存、计算能力及SQL查询并发数量。  
![AWS Redshift ETL的几个性能最佳实践][AWS Redshift ETL 3]

Redshift的WLM设置界面

3，使用”BEGIN…COMMIT”减少COMMIT次数前面我们提到ELT是一个多步骤的任务，每个步骤最后往往需要执行一个COMMIT，而COMMIT又是一个昂贵的操作。所以我们bosicloud建议使用BEGIN…END 将能够合并的多个步骤尽量合并为一个步骤并只执行一次COMMIT，例如：  
Begin  
CREATE temporary staging\_table;  
INSERT INTO staging\_table SELECT .. FROM source;  
DELETE FROM table1 WHERE ???=???;  
INSERT INTO table1 SELECT .. FROM staging\_table;  
DELETE FROM table2 WHERE ???=???;  
INSERT INTO table2 SELECT .. FROM staging\_table;  
Commit

4，使用Redshift Spectrum for ad-hoc查询  
在以往，我们可以会为了做ad-hoc查询而将S3上的数据ETL加载到Redshift进行查询。如果仅为了偶尔的一两次查询而进行ETL，这个ETL将显得非常昂贵、不划算。别忘了AWS最近推出了Redshift Spectrum新功能，即您可以直接利用Redshift Spectrum查询S3上的数据而无需将数据加载到Redshift中，虽然Specturm查询相比普通Redshift查询较慢，但比起ETL来说查询速度仍然是大大提升了。

5，关于ETL健康检查的SQL实用脚本：  
 返回过去1天内queue的统计信息，例如最大队列长度和队列时间  
select startqueue,node, datediff(ms,startqueue,startwork) as queue\_time, datediff(ms, startwork, endtime) as commit\_time, queuelen  
from stl\_commit\_stats  
where startqueue >= dateadd(day, -1, current\_Date)  
order by queuelen desc , queue\_time desc;  
 返回一个星期内所执行的COPY的相关信息，如COPY的开始时间（Starttime），所在queue的ID(query),SQL语句（querytxt），COPY的文件数量（n\_files）及文件大小（size\_mb）等等：  
select q.starttime, s.query, substring(q.querytxt,1,120) as querytxt,  
s.n\_files, size\_mb, s.time\_seconds,  
s.size\_mb/decode(s.time\_seconds,0,1,s.time\_seconds) as mb\_per\_s  
from (select query, count(*) as n\_files,  
sum(transfer\_size/(1024*1024)) as size\_MB, (max(end\_Time) -  
min(start\_Time))/(1000000) as time\_seconds , max(end\_time) as end\_time  
from stl\_s3client where http\_method = 'GET' and query > 0  
and transfer\_time > 0 group by query ) as s  
LEFT JOIN stl\_Query as q on q.query = s.query  
where s.end\_Time >= dateadd(day, -7, current\_Date)  
order by s.time\_Seconds desc, size\_mb desc, s.end\_time desc  
limit 50;

 建立view视图查看每个表空间使用情况，请考虑将空间增长较快的表的内容unload到S3.

CREATE OR REPLACE VIEW admin.v\_space\_used\_per\_tbl  
AS with info\_table as ( SELECT TRIM(pgdb.datname) AS dbase\_name  
,TRIM(pgn.nspname) as schemaname  
,TRIM(pgc.relname) AS tablename  
,id AS tbl\_oid  
,b.mbytes AS megabytes  
,CASE WHEN pgc.reldiststyle = 8  
THEN a.rows\_all\_dist  
ELSE a.rows END AS rowcount  
,CASE WHEN pgc.reldiststyle = 8  
THEN a.unsorted\_rows\_all\_dist  
ELSE a.unsorted\_rows END AS unsorted\_rowcount  
,CASE WHEN pgc.reldiststyle = 8  
THEN decode( det.n\_sortkeys,0, NULL,DECODE( a.rows\_all\_dist,0,0, (a.unsorted\_rows\_all\_dist::DECIMAL(32)/a.rows\_all\_dist)*100))::DECIMAL(20,2)  
ELSE decode( det.n\_sortkeys,0, NULL,DECODE( a.rows,0,0, (a.unsorted\_rows::DECIMAL(32)/a.rows)*100))::DECIMAL(20,2) END  
AS pct\_unsorted  
FROM ( SELECT  
db\_id  
,id  
,name  
,MAX(ROWS) AS rows\_all\_dist  
,MAX(ROWS) - MAX(sorted\_rows) AS unsorted\_rows\_all\_dist  
,SUM(rows) AS rows  
,SUM(rows)-SUM(sorted\_rows) AS unsorted\_rows  
FROM stv\_tbl\_perm  
GROUP BY db\_id, id, name  
) AS a  
INNER JOIN  
pg\_class AS pgc  
ON pgc.oid = a.id  
INNER JOIN  
pg\_namespace AS pgn  
ON pgn.oid = pgc.relnamespace  
INNER JOIN  
pg\_database AS pgdb  
ON pgdb.oid = a.db\_id  
INNER JOIN (SELECT attrelid,  
MIN(CASE attisdistkey WHEN 't' THEN attname ELSE NULL END) AS "distkey",  
MIN(CASE attsortkeyord WHEN 1 THEN attname ELSE NULL END) AS head\_sort,  
MAX(attsortkeyord) AS n\_sortkeys,  
MAX(attencodingtype) AS max\_enc,  
SUM(case when attencodingtype <> 0 then 1 else 0 end)::DECIMAL(20,3)/COUNT(attencodingtype)::DECIMAL(20,3) *100.00 as pct\_enc  
FROM pg\_attribute  
GROUP BY 1) AS det ON det.attrelid = a.id  
LEFT OUTER JOIN  
( SELECT  
tbl  
,COUNT(*) AS mbytes  
FROM stv\_blocklist  
GROUP BY tbl  
) AS b  
ON a.id=b.tbl  
WHERE pgc.relowner > 1)  
select info.\*  
,CASE WHEN info.rowcount = 0 THEN 'n/a'  
WHEN info.pct\_unsorted >= 20 THEN 'VACUUM SORT recommended'  
ELSE 'n/a'  
END AS recommendation  
from info\_table info;

 找出本周内最费时间的前50个SQL查询（多个相同的sql查询时间合并计算）  
\-- query runtimes  
select trim(database) as DB, count(query) as n\_qry, max(substring (qrytext,1,80)) as qrytext, min(run\_seconds) as "min" , max(run\_seconds) as "max", avg(run\_seconds) as "avg", sum(run\_seconds) as total, max(query) as max\_query\_id,  
max(starttime)::date as last\_run, aborted,  
listagg(event, ', ') within group (order by query) as events  
from (  
select userid, label, stl\_query.query, trim(database) as database, trim(querytxt) as qrytext, md5(trim(querytxt)) as qry\_md5, starttime, endtime, datediff(seconds, starttime,endtime)::numeric(12,2) as run\_seconds,  
aborted, decode(alrt.event,'Very selective query filter','Filter','Scanned a large number of deleted rows','Deleted','Nested Loop Join in the query plan','Nested Loop','Distributed a large number of rows across the network','Distributed','Broadcasted a large number of rows across the network','Broadcast','Missing query planner statistics','Stats',alrt.event) as event  
from stl\_query  
left outer join ( select query, trim(split\_part(event,':',1)) as event from STL\_ALERT\_EVENT\_LOG where event\_time >= dateadd(day, -7, current\_Date) group by query, trim(split\_part(event,':',1)) ) as alrt on alrt.query = stl\_query.query  
where userid <> 1  
\-- and (querytxt like 'SELECT%' or querytxt like 'select%' )  
\-- and database = ''  
and starttime >= dateadd(day, -7, current\_Date)  
)  
group by database, label, qry\_md5, aborted  
order by total desc limit 50;

【关于博思云为】  
作为一家专业的云计算服务型企业，博思云为专为客户提供 AWS 上的运营服务：包括架构咨询服务、迁移服务、云安全集成服务、混合云管理服务、大数据服务以及 DevOps 服务。目前，博思云为在大数据、DevOps、架构、数据库以及操作系统等都已取得厂商认证，在上海、南京、杭州、武汉等地设有分公司。为创新服务模式、引领 IT 服务业的发展，博思云为将持续投入资源开展智能混合云管理平台、图数据库的研发等。  
![AWS Redshift ETL的几个性能最佳实践][AWS Redshift ETL 4]

转载于:https://blog.51cto.com/703356/2348367

[AWS Redshift ETL]: https://s1.51cto.com/images/blog/201902/01/d02b78c4418044b5142a329bdb59165f.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[AWS Redshift ETL 1]: https://s1.51cto.com/images/blog/201902/01/ddc25b7783298cf3eb2fa6b7bdcf13fb.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[AWS Redshift ETL 2]: https://s1.51cto.com/images/blog/201902/01/184d6faeee2580bb3533efd5cfdb08b7.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[AWS Redshift ETL 3]: https://s1.51cto.com/images/blog/201902/01/6e6b387ce7441ea93635ac340ec34f85.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[AWS Redshift ETL 4]: https://s1.51cto.com/images/blog/201902/01/459e1075d124e48ddea7ccf20b5034f6.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=