亚马逊AWS官方博客

利用 Amazon Redshift Spectrum 使用嵌套数据类型

Original URL: https://amazonaws-china.com/blogs/big-data/working-with-nested-data-types-using-amazon-redshift-spectrum/

前言

作为一个托管的数据仓库服务,Amazon Redshift从它发布至今已经帮助全球成千上万的客户实现了PB级别数据的分析能力,实现了复杂SQL的快速查询。但随着数据的飞速增长,我们看到越来越多的客户数据开始逼近EB级别。对于这样体量的大数据,虽然Redshift也可以支持快速的复杂SQL查询,但毕竟我们需要启动更多的Redshift集群,消耗更多的CPU和存储成本,同时还要付出更多的数据加载时间。相反如果我们为了节省资源和成本把数据放在S3上,通过EMR集群也可以实现快速低成本的数据清理,但针对复杂的(诸如Join类)的查询速度会很慢,不能很好支持。这形成了一个鱼与熊掌不可兼得的选择题。

为了真正摆脱数据分析的瓶颈、消灭暗数据,我们的客户需要既能高效执行复杂的查询,又能享受高度可扩展的数据并行处理,也能利用近乎无限的低成本的S3存储资源,还要可以支持多种常用的数据格式。满足这种”既又也还”的任性就是Redshift Spectrum的使命。

Redshift Spectrum 是 Amazon Redshift 的一项功能,允许直接查询存储在 Amazon S3 上的数据,并支持嵌套数据类型。此文将讨论哪些用例可从嵌套数据类型中获益,如何将 Amazon Redshift Spectrum 与嵌套数据类型配合使用以实现出色的性能和存储效率,以及嵌套数据类型的一些局限性。

此博文使用虚拟数据生成的数据集。可以查看其表架构https://redshift-immersionday-labs.s3-us-west-2.amazonaws.com/data/nested-customerorders/demo_ddls.sql)如果想尝试数据集,请部署 Redshift 集群,在其中执行 DDL并使用本文中的查询示例。

数据建模

在很多情况下,数据是按层次结构生成的。例如客户购买了多件商品,为了进行分析,有多种数据建模方法可以节省存储空间或加快数据处理速度。实现存储效率的一种常用方法是维度建模。

下表显示了虚拟客户数据。

用户名 姓名 性别 地址 邮件 出生日期
1 erin15 Sarah Newman F 795 Nancy Shoal Apt.684 Phillipschester, MI 01979 josephlyons@yahoo.com 4/24/10
2 shepherdlisa Mark Lee M 754 Michelle Gateway Port Johnstad, ME 35695 guerrerotodd@hotmail.com 11/10/32
3 palmerpaul Jennifer Marshall F 869 Harrell Forges Apt.111 East Monica, MO 01243 heather65@hotmail.com 3/11/07
4 brettmcgee Travis Wilson M 535 Lisa Flat East Andrew, ID 43332 bellkim@gmail.com 3/22/10
5 torresdiana Ashley Hoffman F 7815 Lauren Ranch Ambertown, FL 93225 franklinjonathan@hotmail.com 5/14/60

下表包含虚拟订单数据,该数据通过外键username链接到客户表。

用户名 交易日期 发货日期 商品 价格
1 erin15 10/11/19 10/13/19 10 4794
2 erin15 10/11/19 10/12/19 7 1697
3 erin15 10/7/19 10/9/19 2 15
4 erin15 10/6/19 10/10/19 5 1744
5 erin15 10/5/19 10/10/19 7 6346

在维度模型中,每个客户的信息仅存储一次。即使客户在不同时间订购多个项目,也不存在重复数据。

维度模型最适合存储。但处理数据会很有挑战性。要全面了解数据,需要join两个表。

例如,要找出客户 Mark Lee 在过去三个月中购买了多少商品及其总支出,查询需要加入“客户和订单”表。参阅以下代码:

Select c.username, o.transaction_date, o.shipping_date, sum(items), sum(price)

from customers c inner join orders o on (c.username = o.username)

where c.name = ‘Mark Lee’

and transaction_date > DATEADD(month, -3, GETDATE())

group by 1,2,3;

每项交易中,数百万名客户可能购买多个商品,这时join的代价可能很高昂。快速增长的数据集可能很大,因此需要将其存储在分布式系统中。要执行join,需要通过网络对数据进行shuffle,这样的代价会很高。

随着存储越来越便宜,人们开始使用扁平化模型。在此模型中,预先join数据以提高处理效率。下表显示,客户和订单信息存储在同一记录中,可随时进行分析。

 

用户名 姓名 性别 地址 邮件 出生日期 交易日期 发货日期 商品 价格
erin15 Sarah Newman F 795 Nancy Shoal Apt.684 Phillipschester, MI 01979 josephlyons@yahoo.com 4/24/10 9/14/19 10/12/19 2 1237
erin15 Sarah Newman F 795 Nancy Shoal Apt.684 Phillipschester, MI 01979 josephlyons@yahoo.com 4/24/10 9/16/19 10/9/19 8 4824
erin15 Sarah Newman F 795 Nancy Shoal Apt.684 Phillipschester, MI 01979 josephlyons@yahoo.com 4/24/10 9/17/19 10/10/19 9 4392
erin15 Sarah Newman F 795 Nancy Shoal Apt.684 Phillipschester, MI 01979 josephlyons@yahoo.com 4/24/10 9/17/19 10/9/19 3 1079
erin15 Sarah Newman F 795 Nancy Shoal Apt.684 Phillipschester, MI 01979 josephlyons@yahoo.com 4/24/10 9/25/19 10/7/19 1 208
erin15 Sarah Newman F 795 Nancy Shoal Apt.684 Phillipschester, MI 01979 josephlyons@yahoo.com 4/24/10 10/2/19 10/5/19 10 3689
erin15 Sarah Newman F 795 Nancy Shoal Apt.684 Phillipschester, MI 01979 josephlyons@yahoo.com 4/24/10 10/5/19 10/10/19 7 6346
erin15 Sarah Newman F 795 Nancy Shoal Apt.684 Phillipschester, MI 01979 josephlyons@yahoo.com 4/24/10 10/6/19 10/10/19 5 1744
erin15 Sarah Newman F 795 Nancy Shoal Apt.684 Phillipschester, MI 01979 josephlyons@yahoo.com 4/24/10 10/7/19 10/9/19 2 15
erin15 Sarah Newman F 795 Nancy Shoal Apt.684 Phillipschester, MI 01979 josephlyons@yahoo.com 4/24/10 10/11/19 10/13/19 10 4794
erin15 Sarah Newman F 795 Nancy Shoal Apt.684 Phillipschester, MI 01979 josephlyons@yahoo.com 4/24/10 10/11/19 10/12/19 7 1697
palmerpaul Jennifer Marshall F 869 Harrell Forges Apt.111 East Monica, MO 01243 heather65@hotmail.com 3/11/07 9/14/19 9/22/19 6 4642
palmerpaul Jennifer Marshall F 869 Harrell Forges Apt.111 East Monica, MO 01243 heather65@hotmail.com 3/11/07 9/17/19 9/21/19 1 527
palmerpaul Jennifer Marshall F 869 Harrell Forges Apt.111 East Monica, MO 01243 heather65@hotmail.com 3/11/07 10/9/19 10/12/19 5 408
torresdiana Ashley Hoffman F 7815 Lauren Ranch Ambertown, FL 93225 franklinjonathan@hotmail.com 5/14/60 9/17/19 9/28/19 9 5452

该模型在分布式系统上也能很好地运行。由于每行都包含完整信息,因此可以在任何节点上对其进行处理,而无需混洗数据。还可以使用列式存储数据,这样查询引擎仅读取所需的列,而不会读取整行,这样就提高了分析性能和存储效率。

两种模式各有利弊。维度模型牺牲计算能力换取存储效率,而扁平化模型牺牲存储换取处理效率。

有些新的数据类型可以综合上述两方面的优点。这种方法不再需要将子记录放入另一个表,而是可以将其嵌套在父记录中并获取完整信息,而无需执行join操作。这样无需复制父记录,可以以有效地范式化数据。

下图展示了此工作流。

可以将此模型应用于完全结构化层次数据集schemaful hierarchy dataset)。继续以客户和订单示例为例,尽管客户可能会购买多件商品,但是每个订单商品都包含相同类型的信息,例如产品 ID、价格和供应商。层次结构清晰且一致。可以将数据映射到嵌套的结构化Schema,通过 SQL 语言有效存储和访问该Schema。

下表是上一个示例的嵌套数据表示。

用户名 姓名 性别 地址 邮件 出生日期 交易日期 发货日期 商品 价格
erin15 Sarah Newman F 795 Nancy Shoal Apt.684 Phillipschester, MI 01979 josephlyons@yahoo.com 4/24/10 9/14/19 10/12/19 2 1237
9/16/19 10/9/19 8 4824
9/17/19 10/10/19 9 4392
9/17/19 10/9/19 3 1079
9/25/19 10/7/19 1 208
10/2/19 10/5/19 10 3689
10/5/19 10/10/19 7 6346
10/6/19 10/10/19 5 1744
10/7/19 10/9/19 2 15
10/11/19 10/13/19 10 4794
10/11/19 10/12/19 7 1697
palmerpaul Jennifer Marshall F 869 Harrell Forges Apt.111 East Monica, MO 01243 heather65@hotmail.com 3/11/07 9/14/19 9/22/19 6 4642
9/17/19 9/21/19 1 527
10/9/19 10/12/19 5 408
torresdiana Ashley Hoffman F 7815 Lauren Ranch Ambertown, FL 93225 franklinjonathan@hotmail.com 5/14/60 9/17/19 9/28/19 9 5452

下图比较了三个模型(全部为 parquet 格式)的存储使用情况。

该图表明,嵌套结构的存储效率与维度模型一样。

使用嵌套数据类型

嵌套数据类型是某些常见数据模式的结构化数据类型。嵌套数据类型支持structs, arrays和maps。

struct类似于关系表。它将对象属性分组在一起。例如,如果客户资料包含客户的姓名、地址、电子邮件和生日,如下Schema:

customer struct<
              username:string,
              name:string,
              sex:string,
              address:string,
              mail:string,
              birthdate:string>

如下代码:

    "customer": {
        "username": "kevin35",
        "name": "Nancy Alvarez",
        "sex": "F",
        "address": "05472 Kathleen Turnpike\nNew Ashley, NV 84430",
        "mail": "darrellbaker@yahoo.com",
        "birthdate": "1961-02-05"
    }

array存储一对多关系。例如,一个客户可能有多个送货地址或电话号码。如果客户有多个电话号码,则阵列会显示为以下Schema:

Phonenumbers array<string>

如下代码:

[‘555-5555’, ‘555-1234’]

map是key-value的集合。可以将其视为struct <key, value> 元素的列表。例如,如果客户具有特定的奖励偏好,则会显示为以下Schema:

preference map<string,boolean>

如下代码:

{one_day_delivery=true,
 coupon=false,
 free_shipping=true}

嵌套数据可以包含其他嵌套数据类型。最常见的是struct中的array类型。例如,包含多件商品的订单可能显示为以下Schema:

orders array<
            struct<
              product_id:string,
              price:int,
              onsale:boolean,
              tax:int,
              weight:int,
              others:int,
              vendor:string>
            > 

可通过组合它们来创建复杂的对象。例如,客户的在线交易显示为以下Schema:

customer struct<
              username:string,
              name:string,
              sex:string,
              address:string,
              mail:string,
              birthdate:string> ,
  shipping_address array<
                      struct<
                        name:string,
                        street_address:string,
                        city:string,
                        postcode:string>
                      > ,
  creditcard string ,
  transaction_date string ,
  shipping_date string ,
  membership string ,
  preference map<string,boolean> ,
  orders array<
            struct<
              product_id:string,
              price:int,
              onsale:boolean,
              tax:int,
              weight:int,
              others:int,
              vendor:string>
            > ,
  platform string ,
  comments string 

Hive、Spark、Presto 和 Redshift Spectrum 等常用查询引擎支持嵌套数据类型。这些引擎支持的 SQL 语法可能不同。为了一致,本文中的所有查询示例均使用 Amazon Redshift Spectrum。更多信息,请参阅教程:使用 Amazon Redshift Spectrum 查询嵌套数据

嵌套数据类型的用例

嵌套数据类型可以简化 ETL及数据建模,并且性能良好。以下是一些常见的使用嵌套数据类型的情况。

父子数据关系

嵌套数据类型通过并置(collocated)存储来保持父子数据关系(摘要-细节)。例如,要分析客户的购买习惯,可能需要查找以下内容:

  • 经常购买但每次只购买少量物品的客户。他们可能希望获得免运费的年度会员资格。
  • 购买频率较低但一次交易中购买许多商品的客户。他们可能希望获得免费送货或折扣。

需要从订单数据中获得相关的信息,例如,客户平均每笔交易购买多少商品。

要查找每周至少在线订购一次且每次少于四件商品的客户名单,使用以下代码:

with purchases as (
select co.customer.username as customer, co.transaction_date as transaction_date, co.customer.address as address,
  (select count(*) from co.orders) as total_items,
  (select sum(case when onsale = true then 1 else 0 end) from co.orders) as items_onsale
from demo.customer_order_nested_parq co )
select customer, count(transaction_date) as tran_cnt, avg(total_items) 
from purchases 
where total_items <= 3 and items_onsale > 0 
      and transaction_date >= '2019-09-01' 
group by 1 having tran_cnt >= 4;

使用嵌套的订单详细信息,每件商品的信息已按每次交易时的客户分组。子聚合简单直接;可以汇总订单详细信息来对客户进行分类。如果使用反范式化表(denormalized table),则必须执行两次 GROUP BY。查询也可能需要较长时间。参阅以下代码:

with purchases as (
select cc_username as customer, transaction_date, cc_address as address,
  count(*) as total_items,
  sum(case when co_onsale = true then 1 else 0 end) as items_onsale
from demo.customer_order_flatten_parq 
group by 1,2,3)
select customer, count(transaction_date) as tran_cnt, avg(total_items) 
from purchases 
where total_items <= 3 and items_onsale > 0 
      and transaction_date > '2019-09-01'
group by 1 having tran_cnt >= 4;

要查找每季度订购至少 10 件商品且总支出较高的客户,使用以下代码:

with purchases as (
select co.customer.username as customer, co.transaction_date as transaction_date,
  (select count(*) from co.orders) as total_items,
  (select sum(price) from co.orders) as total_spending
from demo.customer_order_nested_parq co )
select customer, count(transaction_date) as tran_cnt, avg(total_spending) from purchases 
where total_items >= 10 and total_spending > 5000 and transaction_date > '2019-07-01' transaction_date < '2019-09-30'
group by 1 having tran_cnt < 2
order by 3 desc;

使用嵌套数据类型进行父子数据分析还有另一项优势,就是减少使用的资源。如果有一百万个客户交易,订单数量可能达到五倍以上。例如,要查找每天有多少货物发往密歇根州,使用以下代码:

select co.shipping_date, sum(coo.weight)
from demo.customer_order_nested_parq co, co.orders coo
where co.customer.address like '%MI 012__'
group by 1
order by 1;

假设 3% 的客户订单发往密歇根州,则在筛选客户数据之后,大约有 3% 的匹配交易。只需要处理 15 万个商品订单,而不是 500 万个。与扁平化模型相比,要处理的数据和使用的资源大大减少。

对于父子用例,嵌套数据类型提供子代上的直接聚合,更有效的筛选、分组、窗口化和节约存储。

多对多关系

客户可以从不同供应商处购买许多商品,而供应商则可以向许多客户出售同一产品。这是一种多对多关系。

在维度模型中,需要三个表:customers、orders和transactions。要找到拥有最多客户的顶级供应商,需要join这三个表。参阅以下代码:

select vendor, transaction_date, count(distinct cc.username)
from customers cc,
     transactions tt,
     orders oo
where cc.username = tt.username
and oo.transaction_id = tt.transaction_id
and tt.transaction_date >= '2019-01-01' 
group by 1,2
order by 3 desc;

采用嵌套数据类型后,查询类似于使用维度模型进行查询。但是,由于订单数据与客户交易并置,因此可即时联结它们。参阅以下代码:

select coo.vendor, co.transaction_date, count(distinct co.customer.username)
from demo.customer_order_nested_parq co,
co.orders coo
where co.transaction_date > '2019-01-01'
group by 1,2
order by 3 desc;

再举一个例子,供应商 Smith PLC 在 2019 年 10 月10 日举行了一场大型销售活动。您想了解在这次销售中哪些客户购买了其产品,哪些客户是花费最多的顶级客户。为此,使用以下代码:

select co.customer.username, count(coo.product_id), sum(coo.price)
from demo.customer_order_nested_parq co, co.orders coo
where co.transaction_date = '2019-10-10'
and (select count(*) from co.orders 
     where vendor = 'Smith PLC' and onsale = true) > 0
group by 1
order by 3 desc;

与维度模型查询相比,嵌套模型的查询速度快两到三倍。这是在只有几百万行的较小数据集上。对于更大的数据集,性能提升更大,并且使用的资源更少。

稀疏和频繁更改的数据

假设想奖励在在线商店订购的客户。对于每笔交易,客户可以选择一个或多个奖励,例如免费送货、一日送达、折扣或优惠券。根据奖励是否有效,必须经常修改奖励类型、添加新奖励或删除不受欢迎的奖励。

如果将数据存储在扁平化模型中,则有两个常用方法来跟踪此数据。方法一,创建一个表,每类奖励一列。必须一开始就考虑所有可能的奖励并创建这些列。这可能导致表很宽,并且数据非常稀疏。或者,可以在想要添加或删除奖励类型时修改表的schema。这将增加维护工作量,并可能丢失历史记录数据。下表演示了此方法(下表示例中的所有交易 ID 数据均为虚构)。

 

交易 ID 免费送货 一日送达 折扣 优惠券
pklein35966659391853535 FALSE TRUE TRUE
rebeccawiliams228880139768961 FALSE TRUE
brooke39180013629693040 TRUE FALSE TRUE TRUE
jchapman4283556333561927 FALSE TRUE FALSE FALSE
mariamartin3515336516983566 FALSE FALSE TRUE

选项二,每行存储一个奖励。这避免了表过宽的问题和不断更新架构的麻烦。如果只需要分析单个奖励,则适合采用该方法。如果要查看奖励之间是否存在任何关联,例如,如果更多顾客更喜欢免费送货和一日送达,而不是折扣和优惠券,则此选项会更复杂。此模型还需要更多存储空间。下表演示了此方法。

交易 ID 奖励类型
pklein35966659391853535 免费送货 FALSE
pklein35966659391853535 一日送达 TRUE
pklein35966659391853535 优惠券 TRUE
rebeccawiliams228880139768961 一日送达 FALSE
rebeccawiliams228880139768961 优惠券 TRUE
brooke39180013629693040 免费送货 TRUE
brooke39180013629693040 一日送达 FALSE
brooke39180013629693040 折扣 TRUE
brooke39180013629693040 优惠券 TRUE

折衷方案,是使用 JSON 字符串将选定的奖励存储在同一列中,从而避免更改架构。参阅以下代码:

preference varchar(65535)

下表显示了 JSON 字符串中的数据存储方式:

交易 ID 偏好
pklein35966659391853535 {“coupon”:true, “free_shipping”:false,”one_day_delivery”:true}
rebeccawiliams228880139768961 {“coupon”:true, one_day_delivery”:false}
brooke39180013629693040 {“coupon”:true, “discount”:true, “free_shipping”:true,”one_day_delivery”:false}
jchapman4283556333561927 {“coupon”:false, “discount”:false, “free_shipping”:false, “one_day_delivery”:true}
mariamartin3515336516983566 {“discount”:true, “free_shipping”:false,”one_day_delivery”:false}

可以通过使用 JSON 函数提取奖励数据来对其进行分析。请参阅以下代码:

select correlation, count(username) from (
select username,
(case when 
    (json_extract_path_text(preference,'free_shipping')  = 'true' and  
     json_extract_path_text(preference,'one_day_delivery')  = 'true') 
     then 1
 when 
    (json_extract_path_text(preference,'discount') = 'true' and  
     json_extract_path_text(preference,'coupon')  = 'true') 
     then 2
else 0 
 end) as correlation
from demo.transactions
  )
group by 1;

此解决方案可接受,但是通过使用嵌套数据类型map,可以提高存储效率和性能。请参阅以下代码:

preference map<string, boolean>

下表显示了映射中的数据存储方式:

交易 ID 偏好
pklein35966659391853535 {coupon=true, free_shipping=false,one_day_delivery=true}
rebeccawiliams228880139768961 {coupon=true, one_day_delivery=false}
brooke39180013629693040 {coupon=true, discount=true, free_shipping=true,one_day_delivery=false}
jchapman4283556333561927 {coupon=false, discount=false, free_shipping=false, one_day_delivery=true}
mariamartin3515336516983566 {discount=true, free_shipping=false,one_day_delivery=false}

 

可以使用 SQL 分析单个奖励或多个奖励。例如,要查找有多少客户更喜欢免费送货,使用以下代码:

select count(distinct co.customer.username)
from demo.customer_order_nested_parq co, co.preference cm
where cm.key = 'free_shipping' and cm.value = true;

要查找有多少客户更喜欢免费送货和一日送达而不是优惠券或折扣,使用以下代码:

with customer_rewards as (
select co.customer.username as customer,
 (select count(*) from co.preference cm 
where cm.key = 'free_shipping' and cm.value = true) as shipping_pref,
 (select count(*) from co.preference cm 
where cm.key = 'one_day_delivery' and cm.value = true) as delivery_pref,
 (select count(*) from co.preference cm 
where cm.key = 'coupon' and cm.value = true) as coupon_pref,
 (select count(*) from co.preference cm 
where cm.key = 'discount' and cm.value = true) as discount_pref
from demo.customer_order_nested_parq co;
select case when shipping_pref > 0 and delivery_pref > 0 then 1
            when coupon_pref > 0 and discount_pref > 20 then 2
            else 0
       end as correlation, count(customer)
from customer_rewards
group by 1;

map类型允许添加任何key-value对。可以随时添加新的奖励类型,而无需更改架构,并且可以立即分析新的奖励。

map类型的主要优点是支持灵活的schema,并且无需频繁更新schema。但是,没有太多的性能优势。如果性能是最重要的,则建议使用扁平化表flattened table。还可以扁平化最常访问的列,并将map用于访问频率较低的列。

嵌套数据类型的局限性

尽管嵌套数据类型在许多场景中很有用,但它们有以下局限性:

  • 对子数据大小有严格的限制。
  • 您只能附加,并且更新数据既困难又缓慢。即使只修改一个子属性,也需要重写整个嵌套对象。
  • 处理在父记录级别进行拆分。如果子数据严重不均匀,则可能会出现问题。
  • 查询引擎可能不支持对嵌套数据进行所有类型的分析。

Amazon Redshift Spectrum 嵌套数据局限性见下面链接。https://docs.amazonaws.cn/redshift/latest/dg/nested-data-restrictions.html

小结

此博文讨论了嵌套数据类型的优势及示例,在这些示例中,嵌套数据类型可以帮助提高存储效率、性能或简化分析。在许多情况下,嵌套数据类型可以是理想的解决方案。动手试试吧,分享您的经验!

更多参考

  1. 挖掘EB级别数据的价值 – Redshift Spectrum介绍及最佳实践

https://amazonaws-china.com/cn/blogs/china/value-of-mining-eb-level-data-redshift-spectrum-introduction-and-best-practice/

  1. 教程:使用 Amazon Redshift Spectrum 查询嵌套数据

https://docs.amazonaws.cn/redshift/latest/dg/tutorial-query-nested-data.html

 


关于作者

 Juan Yu 女士是 AWS 的数据仓库专家级解决方案架构师。