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

原创

Eteng 2019-02-01 10:35:40 博主文章分类：云计算 ©著作权

©著作权归作者所有：来自51CTO博客作者Eteng的原创作品，请联系作者获取转载授权，否则将追究法律责任

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。进行加载数据工作时，最好的情况是整个工作量平分给所有的计算节点（EC2 node）的slice。当只加载一个大文件，或者加载多个大小差异较大的文件时，都可能导致计算节点工作量分配不均等，进而导致整个Redshift加载数据时性能低下。例如，我们Redshift有两个计算节点，在加载一个大文件时每个节点的工作负载如下：从上图可以看到，加载的任务落在了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导致不能及时响应。另外，为了加快Redshift的COPY ETL过程，我们还可以通过wlm_query_slot_count参数调整ETL queue的slot数量，从而增加queue的内存、计算能力及SQL查询并发数量。

                                             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/(10241024)) 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 服务业的发展，博思云为将持续投入资源开展智能混合云管理平台、图数据库的研发等。