Spectrum查询优化
在本实验中，我们将展示如何通过利用分区和列式文件存储来优化 Redshift Spectrum 查询性能
创建Dimension Table
在Query Editor中创建Dimension Table：
DROP TABLE IF EXISTS customer;
CREATE TABLE customer (
c_custkey integer not null sortkey,
c_name varchar(25) not null,
c_address varchar(25) not null,
c_city varchar(10) not null,
c_nation varchar(15) not null,
c_region varchar(12) not null,
c_phone varchar(15) not null,
c_mktsegment varchar(10) not null)
diststyle all;
DROP TABLE IF EXISTS dwdate;
CREATE TABLE dwdate (
d_datekey integer not null sortkey,
d_date varchar(19) not null,
d_dayofweek varchar(10) not null,
d_month varchar(10) not null,
d_year integer not null,
d_yearmonthnum integer not null,
d_yearmonth varchar(8) not null,
d_daynuminweek integer not null,
d_daynuminmonth integer not null,
d_daynuminyear integer not null,
d_monthnuminyear integer not null,
d_weeknuminyear integer not null,
d_sellingseason varchar(13) not null,
d_lastdayinweekfl varchar(1) not null,
d_lastdayinmonthfl varchar(1) not null,
d_holidayfl varchar(1) not null,
d_weekdayfl varchar(1) not null)
diststyle all;
通过运行以下脚本将数据加载到维度表中, 这会将数据集从 S3 加载到 Redshift 集群中。预计脚本需要几分钟才能完成。Customer和dwdata表分别由3M和2556条记录组成:
copy customer from 's3://awssampledbuswest2/ssbgz/customer'
iam_role default
gzip region 'us-west-2';
copy dwdate from 's3://awssampledbuswest2/ssbgz/dwdate'
iam_role default
gzip region 'us-west-2';
创建External Schema
创建一个引用Redshift 集群外部的数据集的External Schema。默认情况下，Redshift 在Glue Data Catalog中存储描述外部database和schema的元数据。创建后，可以从 Glue 或 Athena 查看schema：
CREATE EXTERNAL SCHEMA clickstream
from data catalog database 'clickstream'
iam_role default
CREATE EXTERNAL DATABASE IF NOT EXISTS;
在Glue Database中，可以看到上面的SQL语句自动创建出了Glue Database：
使用Glue Crawler ，爬取 s3://redshift-spectrum-bigdata-blog-datasets/clickstream-csv10 和 s3://redshift-spectrum-bigdata-blog-datasets/clickstream-parquet1 ，以创建外部表 clickstream.clickstream-csv10 和 clickstream.clickstream-parquet1 。
进入Glue Crawlers页面，创建crawler：
输入爬网程序名称clickstream，然后单击*“下一步”*：
添加新的 s3 数据源：
选择S3作为Data source，并选择s3://redshift-immersionday-labs/data/clickstream路径：
选择创建 IAM 角色：
选择数据库clickstream：
点击创建。创建完成后，运行Crawler：
等待运行完成：
完成运行后，在 Glue中看到两个新表：
单击uservisits_parquet1表，注意到recordCount大约有38亿条，是一个很大的表：
这个表里面尚未设置列名称。另外，字段col0设置为String数据类型。该字段表示adRevenue，应设置为double数据类型：
单击*“Edit”*并调整列名称和数据类型：
例如col0要更改成adrevenue，并设置为double类型：
按上面的步骤，完成所有编辑。 完成后表架构应如下所示：
执行SQL查询
返回 SQL 客户端工具并运行下面的查询。此查询在 Redshift 中的维度表与 S3 中的clickstream事实表(Fact table)之间执行联接，实现了混合查询来自数据湖和数据仓库的数据：
-- 此SQL查询客户id 1 到 3 的细分市场数据, 返回数据集过去 3 个月的广告总收入, 广告收入数据源自 S3，而细分市场等属性则源自 Redshift 中的维度表：
SELECT c.c_name, c.c_mktsegment, t.prettyMonthYear, SUM(uv.adRevenue)
FROM clickstream.uservisits_csv10 as uv
RIGHT OUTER JOIN customer as c ON c.c_custkey = uv.custKey
INNER JOIN (
SELECT DISTINCT d_yearmonthnum, (d_month||','||d_year) as prettyMonthYear
FROM dwdate
WHERE d_yearmonthnum >= 199810) as t ON uv.yearMonthKey = t.d_yearmonthnum
WHERE c.c_custkey <= 3
GROUP BY c.c_name, c.c_mktsegment, t.prettyMonthYear, uv.yearMonthKey
ORDER BY c.c_name, c.c_mktsegment, uv.yearMonthKey ASC
由于将访问近 38 亿条记录，因此该查询预计需要几分钟才能完成。查询结果应如下：
查询了将近9分钟。
性能诊断
有一些方法可以诊断 Redshift Spectrum 的查询：
- Explain - 提供查询执行计划，其中包括有关将哪些处理推送到 Spectrum 的信息。例如，上述查询有一个步骤“S3 Seq Scan clickstream.uservisits_csv10”，表示 Spectrum 在 S3 上执行扫描。
- （
Provisioned Cluster）SVL_S3QUERY_SUMMARY - Redshift Spectrum 查询的统计信息存储在此表。虽然执行计划提供了成本估计，但该表存储了过去查询运行的实际统计信息。 - （Serverless）SYS_EXTERNAL_QUERY_DETAIL - 和上面一样，就是表名不同
诊断结果揭示了为什么我们的查询花了这么长时间。
由于实验是是用的Serverless，所以使用SYS_EXTERNAL_QUERY_DETAIL 表来查：
SELECT
query_id,
file_location,
End_time - Start_time as elapsed,
total_partitions,
qualified_partitions,
scanned_files,
returned_rows,
returned_bytes
FROM SYS_EXTERNAL_QUERY_DETAIL
ORDER BY query_id, start_time DESC;
如果是provisioned cluster，则执行以下查询：
select elapsed, s3_scanned_rows, s3_scanned_bytes, s3query_returned_rows, s3query_returned_bytes, files, avg_request_parallelism from svl_s3query_summary where query = pg_last_query_id() order by query,segment;
执行explain语句：
EXPLAIN
SELECT c.c_name, c.c_mktsegment, t.prettyMonthYear, SUM(uv.adRevenue)
FROM clickstream.uservisits_csv10 as uv
RIGHT OUTER JOIN customer as c ON c.c_custkey = uv.custKey
INNER JOIN (
SELECT DISTINCT d_yearmonthnum, (d_month||','||d_year) as prettyMonthYear
FROM dwdate WHERE d_yearmonthnum >= 199810) as t ON uv.yearMonthKey = t.d_yearmonthnum
WHERE c.c_custkey <= 3
GROUP BY c.c_name, c.c_mktsegment, t.prettyMonthYear, uv.yearMonthKey
ORDER BY c.c_name, c.c_mktsegment, uv.yearMonthKey ASC
里面表示会扫描S3，但没有用到分区，我们将在实验的下一部分中更详细地探讨这一点：
使用分区进行优化
分区是提高扫描效率的关键手段。之前，我们运行了爬虫，它创建了外部表和分区。回到 Glue页面，单击uservisits_csv10表。可以看到列customer和visityearmonth被设置为分区键：
- 如果有兴趣了解如何设置分区的详细信息，请参阅文档 。
- 可以导航到以下位置来探索为Redshift Spectrum 数据集提供服务的 S3 存储桶： https://s3.console.aws.amazon.com/s3/buckets/redshift-immersionday-labs/data/clickstream/uservisits_csv10/
其中每个文件包含特定customer和一年中月份的专有数据。这允许您按Customer和year/month将数据划分为逻辑子集，如上下图所示。通过分区，查询引擎可以定位文件的子集：
- 只针对特定客户
- 仅特定月份的数据
- 特定客户和年份/月份的组合
请注意，正确的分区选择取决于实际的工作负载。应根据要优化的主要查询和数据配置文件来选择分区。对于那些实施自己的点击流分析的人来说，年/月/地区等分区方案通常很有意义。对于存在大量客户且每个客户的数据很少的用例，选择在分区方案中使用customer id并不是最佳选择。本示例中使用的数据集和方案对于多租户广告技术平台或物联网平台等场景来说是实用的。在这些情况下，客户（租户）数量适中，每个客户都有大量数据。
通过运行以下查询来观察利用分区对我们的查询的影响:
SELECT c.c_name, c.c_mktsegment, t.prettyMonthYear, SUM(uv.adRevenue)
FROM clickstream.uservisits_csv10 as uv
RIGHT OUTER JOIN customer as c ON c.c_custkey = uv.customer
INNER JOIN
(SELECT DISTINCT d_yearmonthnum, (d_month||','||d_year) as prettyMonthYear
FROM dwdate
WHERE d_yearmonthnum >= 199810) as t ON uv.yearMonthKey = t.d_yearmonthnum
WHERE c.c_custkey <= 3
GROUP BY c.c_name, c.c_mktsegment, t.prettyMonthYear, uv.yearMonthKey
ORDER BY c.c_name, c.c_mktsegment, uv.yearMonthKey ASC
先前查询的连接条件已被修改。我们没有加入合成键custKey ，而是使用我们在数据建模过程中创建的分区键*customer 。*此查询的运行时间应比之前的快两倍：
实际运行时间确实如此：
从SYS_EXTERNAL_QUERY_DETAIL表中再次查询：
SELECT
query_id,
file_location,
End_time - Start_time as elapsed,
total_partitions,
qualified_partitions,
scanned_files,
returned_rows,
returned_bytes
FROM SYS_EXTERNAL_QUERY_DETAIL
ORDER BY query_id, start_time DESC;
看到扫描和文件和数据条目数量都减少了将近一倍，这解释了为什么我们的查询运行速度大约是原来的两倍：
我们的数据均匀分布在所有客户中，并且通过使用客户分区键查询 6 个客户中的 3 个，数据库引擎能够智能地扫描包含客户 1,2 和 3 的数据子集。
然而，扫描仍然非常低效，我们也可以从利用年/月分区键中受益。
- 运行以下查询：
SELECT c.c_name, c.c_mktsegment, t.prettyMonthYear, SUM(uv.adRevenue)
FROM clickstream.uservisits_csv10 as uv
RIGHT OUTER JOIN customer as c ON c.c_custkey = uv.customer
INNER JOIN (
SELECT DISTINCT d_yearmonthnum, (d_month||','||d_year) as prettyMonthYear
FROM dwdate
WHERE d_yearmonthnum >= 199810) as t ON uv.visitYearMonth = t.d_yearmonthnum -- << Change >> ON uv.yearMonthKey = t.d_yearmonthnum
WHERE c.c_custkey <= 3
GROUP BY c.c_name, c.c_mktsegment, t.prettyMonthYear, uv.yearMonthKey
ORDER BY c.c_name, c.c_mktsegment, uv.yearMonthKey ASC
先前查询的连接条件已被修改。我们不使用合成键yearMonthKey，而是使用分区键visitYearMonth。我们最新的查询同时使用customer分区和时间分区，如果您运行此查询几次，会看到执行时间在 8 秒范围内，比原来的执行时间缩短了20倍：
重新从SYS_EXTERNAL_QUERY_DETAIL表中查询：
SELECT
query_id,
file_location,
End_time - Start_time as elapsed,
total_partitions,
qualified_partitions,
scanned_files,
returned_rows,
returned_bytes
FROM SYS_EXTERNAL_QUERY_DETAIL
ORDER BY query_id, start_time DESC;
观察到 Redshift Spectrum 扫描并返回计算查询所需的确切行数 (66,270,117):
使用parquet文件查询
运行以下查询。运行此查询几次后，您应该观察到执行时间在 4 秒范围内。
SELECT c.c_name, c.c_mktsegment, t.prettyMonthYear, uv.totalRevenue
FROM (
SELECT customer, visitYearMonth, SUM(adRevenue) as totalRevenue
FROM clickstream.uservisits_parquet1
WHERE customer <= 3 and visitYearMonth >= 199810
GROUP BY customer, visitYearMonth) as uv
RIGHT OUTER JOIN customer as c ON c.c_custkey = uv.customer
INNER JOIN (
SELECT DISTINCT d_yearmonthnum, (d_month||','||d_year) as prettyMonthYear
FROM dwdate WHERE d_yearmonthnum >= 199810) as t ON uv.visitYearMonth = t.d_yearmonthnum
ORDER BY c.c_name, c.c_mktsegment, uv.visitYearMonth ASC;
此查询改进了我们之前的查询:
- 我们在查询
clickstream.uservisits_parquet1表而不是clickstream.uservisits_csv10。这两个表包含相同的数据集，但处理方式不同。表clickstream.uservisits_parquet1包含 parquet 格式的数据。Parquet 是一种列式格式，通过提供查询选择的属性的压缩和高效检索，为分析工作负载带来 I/O 优势。此外，“1”与“10”后缀表示每个分区的所有数据都存储在单个文件中，而不是像 CSV 数据集那样存储在十个文件中。
重新从SYS_EXTERNAL_QUERY_DETAIL表查询：
SELECT
query_id,
file_location,
End_time - Start_time as elapsed,
total_partitions,
qualified_partitions,
scanned_files,
returned_rows,
returned_bytes
FROM SYS_EXTERNAL_QUERY_DETAIL
ORDER BY query_id, start_time DESC;
统计数据揭示了一些性能改进的来源。即使由于压缩而扫描相同数量的行，扫描的字节也会减少，扫描的文件从90个降到9个：
原生 Redshift 与 Spectrum比较
我们仍然可以通过将数据加载到 Redshift 中来进行查询。事实上，当我们在本机 Redshift 中专门执行时，我们的最后一个查询运行得更快。
根据经验，不以 I/O 为主且涉及多个联接的查询在本机 Redshift 中可以得到更好的优化。此外，本机 Redshift 中的延迟也显著降低，对于对查询有严格性能 SLA 的用例，可能需要考虑专门使用 Redshift 来支持这些查询。
但当需要执行大型扫描时，可以从Spectrum中受益：以更低的成本获得更高的性能。例如，假设我们需要让业务分析师能够交互式地发现大量历史数据中的见解。