Amazon Redshift Spectrum を使用してネストされたデータ型を扱う

Redshift Spectrum は Amazon Redshift の機能で、Amazon S3 に保存されたデータを直接クエリでき、ネストされたデータ型をサポートします。この記事では、ネストされたデータ型からメリットが得られるユースケース、ネストされたデータ型で Amazon Redshift Spectrum を使用して優れたパフォーマンスとストレージ効率を達成する方法、およびネストされたデータ型のいくつかの制約について説明します。

この記事では、ダミーデータで生成されたデータセットを使用します。テーブルスキーマを表示できます。データセットを試したい場合、Redshift クラスターをデプロイし、そこで DDL を実行して、この記事のクエリ例を使用するか、独自のクエリを作成します。

データモデリング

多くのシナリオでは、データは階層で生成されます。たとえば、顧客がいくつかのアイテムを購入したとします。分析のために、ストレージを節約したりデータ処理を高速化したりするためのさまざまなデータモデリングアプローチがあります。ストレージ効率を達成するための一般的なアプローチの 1 つは、次元モデルです。

次のテーブルに、ダミーの顧客データを示します。

次のテーブルにはダミーの注文データが含まれており、外部キーのユーザー名を介して顧客テーブルにリンクされています。

次元モデルでは、各顧客の情報は 1 回だけ保存されます。顧客がさまざまな時間に複数のアイテムを注文できたとしても、重複したデータはありません。

次元モデルはストレージに最適です。ただし、データを効率的に処理することは困難になり得ます。データの全体像を把握するには、2 つのテーブルを結合して階層を復元する必要があります。

たとえば、顧客が Mark Lee が過去 3 か月に購入したアイテムの数と合計支出を調べるには、クエリは顧客と注文のテーブルを結合する必要があります。次のコードを参照してください。

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;

各トランザクションで複数のアイテムを購入する可能性のある何百万もの顧客がいる場合、結合は非常にコストがかかる可能性があります。急成長するデータセットは非常に大きくなる可能性があるため、分散システムに保存する必要があります。結合を実行するには、ネットワークを通じてデータをシャッフルする必要があり、コストはさらに大きくなります。

ストレージがより安価になるにつれて、人々はフラット化されたモデルを使用し始めています。このモデルでは、処理効率を高めるためにデータが事前に結合されています。次のテーブルは、顧客と注文の情報が 1 つのレコードに保存され、分析の準備ができていることを示しています。

このモデルは、分散システムでもうまく機能します。各行には完全な情報が含まれているため、任意のノードで処理でき、データをシャッフルする必要はありません。列形式を使用してデータを保存することもできます。これにより、クエリエンジンは行全体ではなく、必要な列のみを読み取ることができます。この手法により、分析のパフォーマンスが向上し、ストレージ効率が向上します。

どちらのモデルにも長所と短所があります。次元モデルはストレージの効率と計算能力のトレードオフであり、フラット化モデルは処理の効率とストレージのトレードオフです。

両方の長所を実現する新しいデータ型がいくつか利用できます。子レコードを別のテーブルに配置する代わりに、親レコードにネストして、結合を行わずに完全な情報を取得できます。親レコードを複製することなく、データを事実上非正規化します。

次の図は、このワークフローを示しています。

このモデルをスキーマフルな階層データセットに適用できます。顧客と注文の例を続けると、顧客は複数のアイテムを購入する可能性がありますが、各注文アイテムには製品 ID、価格、ベンダーなどの同じタイプの情報が含まれています。

階層は明確で一貫しています。データをネストされた構造化スキーマにマップできます。これは、SQL 言語を介して効率的に保存してアクセスできます。

次のテーブルは、前の例のネストされたデータを表したものです。

次のグラフは、3 つのモデルのストレージ使用量を比較しています (すべて parquet 形式で)。

グラフは、ネストされた構造が次元モデルと同じくらい効率的なストレージであることを示しています。

ネストされたデータ型の使用

ネストされたデータ型は、いくつかの一般的なデータパターンの構造化データ型です。ネストされたデータ型は、struct、array、map をサポートします。

struct はリレーショナルテーブルに似ています。オブジェクトのプロパティをグループ化します。たとえば、顧客プロファイルに名前、住所、E メール、誕生日が含まれている場合、次のスキーマとして表示されます。

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 は 1 対多の関係を保存します。たとえば、顧客が複数の配送先住所または電話番号を持っている場合があります。顧客に複数の電話番号がある場合、次のスキーマとして表示されます。

Phonenumbers array<string>

データは次のコードとして表示されます。

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

map は、キーと値のペアのコレクションです。それを struct<key, value> のリストと考えることができます。たとえば、顧客に特定の報酬設定がある場合、次のスキーマとして表示されます。

preference map<string,boolean> 

データは次のコードとして表示されます。

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

ネストされたデータは、メンバーとして別のネストされたデータ型を持つことができます。最も一般的なものは、struct の配列です。たとえば、注文に複数のアイテムが含まれる場合、次のスキーマとして表示される可能性があります。

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

それらを組み合わせて、複雑なオブジェクトを作成できます。たとえば、顧客のオンライントランザクションは次のスキーマとして表示されます。

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、データモデリングを簡素化し、良好なパフォーマンスを実現するといった多くの利点があります。以下は、ネストされたデータ型の利点を受けられる一般的なユースケースです。

親子関係

ネストされたデータ型は、連結して保存することにより、親子 (要約と詳細) の関係を維持します。多くの場合、これはデータの分析方法と一致します。たとえば、顧客の購買習慣を分析するには、以下を見つける必要がある場合があります。

• 頻繁に購入するが、毎回少数のアイテムのみを購入する顧客。このような顧客はおそらく、送料をカバーする年間メンバーシップを望んでいます。
• あまり頻繁に購入しないが、1 つのトランザクションで多くのアイテムを購入する顧客。このような顧客はおそらく、送料無料の特典や割引を期待しています。

トランザクションごとに顧客が購入するアイテムの平均数など、注文データからのサポート情報が必要です。

少なくとも 1 週間に 1 回、毎回 3 個以下のアイテムをオンラインで注文する顧客のリストを検索するには、次のコードを使用します。

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;

ネストされた注文の詳細では、アイテムごとの情報は、トランザクションごとに顧客を基準としてすでにグループ化されています。子の集約は簡単です。注文の詳細を集計して顧客を分類できます。非正規化テーブルを使用する場合、GROUP BY を 2 回実行する必要があります。クエリにも時間がかかる可能性があります。次のコードを参照してください。

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;

四半期に 1 回のみ、少なくとも 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;

親子データ分析にネストされたデータ型を使用する利点は他にも、リソースの使用量を削減できることがあります。100 万件の顧客トランザクションがある場合、アイテムの注文は 5 倍を超える可能性があります。たとえば、日々ミシガン州に出荷される商品の数を見つけるには、次のコードを使用します。

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% ある可能性があります。処理する必要がある注文数は 500 万件ではなく、15 万件です。これにより、フラット化モデルと比較して、処理するデータと使用するリソースが大幅に削減されます。

親子のユースケースの場合、ネストされたデータ型により、子の単純な集約、より効率的なフィルタリング、グループ化、ウィンドウ化、ストレージの節約が行えます。

多対多の関係

顧客はさまざまなベンダーから多くのアイテムを購入でき、ベンダーは多くの顧客に製品を販売できます。これは多対多の関係です。

次元モデルでは、customers テーブル、orders テーブル、および transactions テーブルの 3 つのテーブルが必要です。最も多くの顧客を抱えているトップベンダーを見つけるには、3 つのテーブルを結合する必要があります。次のコードを参照してください。

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;

次元モデルクエリと比較して、ネストされたモデルは 2〜3 倍高速です。これは、数百万行しかない比較的小さなデータセットです。大規模なデータセットの場合、パフォーマンスの向上はさらに大きく、リソースの使用量は少なくなります。

スパースで頻繁に変更されるデータ

オンラインストアで注文した顧客に報酬を提供するとします。トランザクションごとに、顧客は、送料無料、同日配達、割引、クーポンなどの 1 つ以上の報酬を選択できます。報酬がどれほど効果的かに応じて、報酬の種類を頻繁に変更したり、新しい種類を追加したり、人気のない種類を削除したりする必要があります。

フラット化モデルにデータを保存する場合、このデータを追跡するための 2 つの一般的なオプションがあります。最初の方法は、報酬のタイプごとに 1 つの列を持つテーブルを作成することです。最初に考えられるすべての報酬を考え、列を作成する必要があります。これにより、テーブルが広くなり、データが非常にまばらになる可能性があります。または、報酬タイプを追加または削除するときに、テーブルスキーマを変更できます。これにより、メンテナンス作業が増え、履歴データが失われる可能性があります。次のテーブルは、この方法を示しています (下のテーブルの例のすべての transaction_id データは偽装されています)。

2 番目のオプションは、行ごとに 1 つの報酬を保存することです。これにより、テーブルが広くなる問題と、スキーマを絶えず更新する負担が回避できます。このアプローチは、単一の報酬のみを分析する必要がある場合に適しています。より多くの顧客が割引やクーポンよりも無料配送と同日配達を好む場合など、報酬間に相関関係があるかどうかを確認したい場合、このオプションは一層複雑です。このモデルには、より多くのストレージも必要です。次のテーブルに、この方法を示します。

妥協案は、JSON 文字列を使用して、選択した報酬を 1 つの列にまとめて保存することです。これにより、スキーマの変更が回避されます。次のコードを参照してください。

preference varchar(65535)	

次のテーブルは、データがどのように JSON 文字列に格納されるかを示しています。

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;

このソリューションは受け入れられますが、ネストされたデータ型マップを使用することで、ストレージの効率とパフォーマンスが向上する可能性があります。次のコードを参照してください。

preference map<string, boolean>

次のテーブルは、データがマップに保存される方法を示しています。

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;

マップタイプでは、任意のキーと値のペアを追加できます。スキーマを変更せずにいつでも新しい報酬タイプを追加でき、新しい報酬をすぐに分析できます。

マップタイプの主な利点は、柔軟なスキーマをサポートし、スキーマを頻繁に更新する必要がないことです。ただし、パフォーマンス上の利点はあまりありません。パフォーマンスが最優先事項である場合、フラット化されたテーブルをお勧めします。また、アクセス頻度の高い列をフラット化し、アクセス頻度の低い列にマップを使用することもできます。

ネストされたデータ型の制約

ネストされたデータ型は多くのユースケースで役立ちますが、次の制約があります。

• 子データのサイズには厳しい制限がある。
• 追加のみが可能で、データの更新は難しく、遅い。1 つの子属性を変更する場合でも、ネストされたオブジェクト全体を書き換える必要があります。
• 処理は親レコードレベルで分割される。子データが大きく歪んでいると、問題が発生する可能性があります。
• クエリエンジンは、ネストされたデータのすべてのタイプの分析をサポートしない可能性がある。
• Amazon Redshift Spectrum のネストされたデータの「制約」。

まとめ

この記事では、ネストされたデータ型の利点と、ネストされたデータ型がストレージ効率、パフォーマンスの向上、分析の簡素化に役立つユースケースについて説明しました。ネストされたデータ型が理想的なソリューションになり得るユースケースは他にもたくさんあります。実際にやってみて、どうだったかを教えてください。

著者について

Juan Yu は、AWS のデータウェアハウススペシャリストソリューションアーキテクトです。