Go to Qiita Advent Calendar 2024 Top
LoginSignup
2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@jnitin株式会社BeeX

RedshiftクラスタにTPC-DSベンチマークをかけてみる

Posted at

はじめに

本記事は個人メモです。
ベンチマークデータ以外、たいして有用な情報はありません。

以下の前記事にてTPC-DSベンチマークデータ 10TBをRedshiftの ra3.xlplus 2台構成に投入しました。

その環境を用いて実際にベンチマーク用のSQLを実行した結果をメモ代わりに残しておきます。

TPC-DSのER図や各テーブルの行数などは、前記事に記載してあります。

環境構成

  • クラスタタイプ：プロビジョニング版(サーバレスではないもの）
  • ノードタイプ：ra3.xlplus
  • 台数：2台
  • リージョン：東京リージョン
  • クラスタのパラメータ：デフォルト(自動 WLM)

ベンチマーク用SQL

AWS公式から、query_0.sql を実行しました。

上記フォルダにはquery_0.sql から query_10.sql までのファイルが配置されています。これらは実行するsqlの順番がそれぞれランダム化されたもので、同時実行性能の影響をみる場合などに利用します。今回は純粋な性能を見たいので query_0.sql だけ実行しました。

結果

  • query_0.sql の実行結果と、所要時間は以下の通りです。
  • 1時間以上かかったクエリを太字にしています。
num query duration
1 query96.tpl 9 minutes
2 query7.tpl 9 minutes
3 query75.tpl 38 minutes
4 query44.tpl 2 minutes
5 part1 query39.tpl 16 seconds
5 part2 query39.tpl 14 seconds
6 query80a.tpl 10 minutes
7 query32.tpl 6 seconds
8 query19.tpl 43 seconds
9 query25.tpl 3 minutes
10 query78.tpl 8 hours
11 query86a.tpl 3 minutes
12 query1.tpl 3 minutes
13 query91.tpl 36 seconds
14 query21.tpl 11 seconds
15 query43.tpl 3 minutes
16 query27a.tpl 12 minutes
17 query94.tpl 3 minutes
18 query45.tpl 2 minutes
19 query58.tpl 38 seconds
20 query64.tpl 41 minutes
21 query36a.tpl 15 minutes
22 query33.tpl 1 minutes
23 query46.tpl 5 minutes
24 query62.tpl 5 minutes
25 query16.tpl 13 minutes
26 query10.tpl 2 minutes
27 query63.tpl 1 minutes
28 query69.tpl 4 minutes
28 query69.tpl 4 minutes
29 query60.tpl 1 minutes
30 query59.tpl 8 minutes
31 query37.tpl 1 minutes
32 query98.tpl 16 seconds
33 query85.tpl 3 minutes
34 query70a.tpl 10 minutes
35 query67a.tpl 4 hours
36 query28.tpl 21 minutes
37 query81.tpl 3 minutes
38 query97.tpl 21 minutes
39 query66.tpl 3 minutes
40 query90.tpl 1 minutes
41 query17.tpl 5 minutes
42 query47.tpl 13 minutes
43 query95.tpl 3 minutes
44 query92.tpl 3 seconds
45 query3.tpl 2 minutes
46 query51.tpl 3 minutes
47 query35a.tpl 3 minutes
48 query49.tpl 2 minutes
49 query9.tpl 22 minutes
50 query31.tpl 4 minutes
51 query11.tpl 58 minutes
52 query93.tpl 26 minutes
53 query29.tpl 11 minutes
54 query38.tpl 48 minutes
55 query22a.tpl 1 minutes
56 query89.tpl 7 minutes
57 query15.tpl 5 minutes
58 query6.tpl 34 seconds
59 query52.tpl 50 seconds
60 query50.tpl 30 minutes
61 query42.tpl 6 seconds
62 query41.tpl 11 seconds
63 query8.tpl 19 seconds
64 query12.tpl 18 seconds
65 query20.tpl 10 seconds
66 query88.tpl 16 minutes
67 query82.tpl 55 seconds
68 query23.tpl 1 hours
68 part2 query23.tpl 39 minutes
69 query14a.tpl 21 minutes
69 part2 query14a.tpl 18 minutes
70 query57.tpl 18 minutes
71 query65.tpl 9 minutes
72 query71.tpl 3 minutes
73 query34.tpl 52 seconds
74 query48.tpl 1 minutes
75 query30.tpl 2 minutes
76 query74.tpl 37 minutes
77 query87.tpl 19 minutes
78 query77a.tpl 26 seconds
79 query73.tpl 36 seconds
80 query84.tpl 17 seconds
81 query54.tpl 32 seconds
82 query55.tpl 25 seconds
83 query56.tpl 30 seconds
84 query2.tpl 3 minutes
85 query26.tpl 2 minutes
86 query40.tpl 2 minutes
87 query72.tpl 45 minutes
88 query53.tpl 4 minutes
89 query79.tpl 11 minutes
90 query18a.tpl 17 minutes
91 query13.tpl 10 minutes
92 query24.tpl 49 minutes
92 part2 query24.tpl 12 minutes
93 query4.tpl 2 hours
94 query99.tpl 6 minutes
95 query68.tpl 2 minutes
96 query83.tpl 28 seconds
97 query61.tpl 2 minutes
98 query5a.tpl 3 minutes
99 query76.tpl 11 minutes

詳細

  • いくつかのクエリを気まぐれにピックアップしてみます。

最長クエリ(query78.tpl)

  • 最長クエリは、10番目に実施した以下のクエリです。

    • query78.tpl
    • 所要時間：8時間

  • クエリの中身は以下の通りです。

with
/* TPC-DS query78.tpl 0.10 */
ws as (
  select
    d_year AS ws_sold_year,
    ws_item_sk,
    ws_bill_customer_sk ws_customer_sk,
    sum(ws_quantity) ws_qty,
    sum(ws_wholesale_cost) ws_wc,
    sum(ws_sales_price) ws_sp
  from
    web_sales
    left join web_returns on wr_order_number = ws_order_number
    and ws_item_sk = wr_item_sk
    join date_dim on ws_sold_date_sk = d_date_sk
  where
    wr_order_number is null
  group by
    d_year,
    ws_item_sk,
    ws_bill_customer_sk
),
cs as (
  select
    d_year AS cs_sold_year,
    cs_item_sk,
    cs_bill_customer_sk cs_customer_sk,
    sum(cs_quantity) cs_qty,
    sum(cs_wholesale_cost) cs_wc,
    sum(cs_sales_price) cs_sp
  from
    catalog_sales
    left join catalog_returns on cr_order_number = cs_order_number
    and cs_item_sk = cr_item_sk
    join date_dim on cs_sold_date_sk = d_date_sk
  where
    cr_order_number is null
  group by
    d_year,
    cs_item_sk,
    cs_bill_customer_sk
),
ss as (
  select
    d_year AS ss_sold_year,
    ss_item_sk,
    ss_customer_sk,
    sum(ss_quantity) ss_qty,
    sum(ss_wholesale_cost) ss_wc,
    sum(ss_sales_price) ss_sp
  from
    store_sales
    left join store_returns on sr_ticket_number = ss_ticket_number
    and ss_item_sk = sr_item_sk
    join date_dim on ss_sold_date_sk = d_date_sk
  where
    sr_ticket_number is null
  group by
    d_year,
    ss_item_sk,
    ss_customer_sk
)
select
  ss_customer_sk,
  round(ss_qty /(coalesce(ws_qty, 0) + coalesce(cs_qty, 0)), 2) ratio,
  ss_qty store_qty,
  ss_wc store_wholesale_cost,
  ss_sp store_sales_price,
  coalesce(ws_qty, 0) + coalesce(cs_qty, 0) other_chan_qty,
  coalesce(ws_wc, 0) + coalesce(cs_wc, 0) other_chan_wholesale_cost,
  coalesce(ws_sp, 0) + coalesce(cs_sp, 0) other_chan_sales_price
from
  ss
  left join ws on (
    ws_sold_year = ss_sold_year
    and ws_item_sk = ss_item_sk
    and ws_customer_sk = ss_customer_sk
  )
  left join cs on (
    cs_sold_year = ss_sold_year
    and cs_item_sk = ss_item_sk
    and cs_customer_sk = ss_customer_sk
  )
where
  (
    coalesce(ws_qty, 0) > 0
    or coalesce(cs_qty, 0) > 0
  )
  and ss_sold_year = 2001
order by
  ss_customer_sk,
  ss_qty desc,
  ss_wc desc,
  ss_sp desc,
  other_chan_qty,
  other_chan_wholesale_cost,
  other_chan_sales_price,
  ratio
limit
  100;

上記のSQL文をchatgpt(4.0)に解説させると以下の通りです。

このSQLクエリは、各販売チャネル（web_sales, catalog_sales, store_sales）からの年間商品販売情報を結合し、比較分析を行っています。

特定の年（2001年）で、店舗での購入が他の販売チャネルからの購入よりも多い顧客を抽出しています。
まず、最初の三つのサブクエリ（ws、cs、ss）は、それぞれの販売チャネルから商品（item）、顧客（customer）、販売年（year）、数量（qty）、卸売価格（wholesale_cost）、販売価格（sales_price）の情報を取得します。
これは、各チャネルの商品販売量を計算するためです。

また、web_returns、catalog_returns、store_returnsという返品テーブルを参照していますが、ここで重要なのは、返品された商品は除外するという条件（wr_order_number is null等）です。
つまり、これらのサブクエリは、返品を除いた販売データを取得しています。

メインのselect文では、これらのサブクエリを結合しています。
ここで重要なのは、結合条件として年、商品、顧客が等しいという条件が用いられている点です。
つまり、同一年、同一商品、同一顧客についての販売データを比較することを意味しています。

結合後、店舗（ss）での購入数量（ss_qty）と他のチャネル（wsとcs）での購入数量（ws_qtyとcs_qty）の比率（ratio）を計算します。
その他にも、各チャネルでの卸売価格と販売価格を計算しています。

where節で、他のチャネルからの購入数量が0より大きい、かつ、販売年が2001年であるデータを抽出しています。
つまり、2001年において、他のチャネルからも購入があった顧客についてのみ分析を行っています。

最後に、order by節とlimit節で、上位100件の結果を取得します。
このとき、ソート条件としては、顧客ID、店舗での購入数量、卸売価格、販売価格、他のチャネルでの数量、卸売価格、販売価格、そして比率（すべて降順）が用いられています。
これは、店舗での購入が多い顧客を特定し、それらの顧客が他のチャネルでどれだけ商品を購入しているかを分析するためです。
  • 所感
    • データセットで最も巨大なファクトテーブル3つを横断的に比較するクエリです。
      • catalog_sales：144億行
      • web_sales：72億行
      • store_sales：288億行
    • さらにJOINを2回やっていることが長時間化をもたらしています。

intersectを多用するクエリ(query38.tpl)

  • 54番目に実施した以下のクエリを見てみます。

    • |query38.tpl
    • 所要時間：48分

  • クエリの中身は以下の通りです。

-- start template query38.tpl query 54 in stream 0
select /* TPC-DS query38.tpl 0.54 */  count(*) from (
    select distinct c_last_name, c_first_name, d_date
    from store_sales, date_dim, customer
          where store_sales.ss_sold_date_sk = date_dim.d_date_sk
      and store_sales.ss_customer_sk = customer.c_customer_sk
      and d_month_seq between 1190 and 1190 + 11
  intersect
    select distinct c_last_name, c_first_name, d_date
    from catalog_sales, date_dim, customer
          where catalog_sales.cs_sold_date_sk = date_dim.d_date_sk
      and catalog_sales.cs_bill_customer_sk = customer.c_customer_sk
      and d_month_seq between 1190 and 1190 + 11
  intersect
    select distinct c_last_name, c_first_name, d_date
    from web_sales, date_dim, customer
          where web_sales.ws_sold_date_sk = date_dim.d_date_sk
      and web_sales.ws_bill_customer_sk = customer.c_customer_sk
      and d_month_seq between 1190 and 1190 + 11
) hot_cust
limit 100;

上記のSQL文をchatgpt(4.0)に解説させると以下の通りです。

このクエリは、特定の期間（d_month_seqが1190から1201までの間）で店舗、カタログ、ウェブから商品を購入した顧客の一覧を取得し、それらの顧客の総数を計算するものです。
ただし、各顧客はその名前（姓と名）と購入日によって一意に識別されています。

具体的には、以下のような処理が行われています：

store_sales、date_dim、customerの3つのテーブルを結合し、特定の月の範囲内で店舗で購入した顧客の一覧を取得します。

同様に、catalog_sales、date_dim、customerを結合し、特定の月の範囲内でカタログで購入した顧客の一覧を取得します。

さらに、web_sales、date_dim、customerを結合し、特定の月の範囲内でウェブで購入した顧客の一覧を取得します。

これら3つのリストを比較し、すべての販売チャネルで購入した顧客（つまり、店舗、カタログ、ウェブのすべてで購入した顧客）を見つけます。
この結果から全体の顧客数をカウントします。

クエリ(query79.tpl)

  • 89番目に実施した以下のクエリを見てみます。
    • query79.tpl
    • 所要時間：11分
select /* TPC-DS query79.tpl 0.89 */ 
  c_last_name,c_first_name,substring(s_city,1,30),ss_ticket_number,amt,profit
  from
   (select ss_ticket_number
          ,ss_customer_sk
          ,store.s_city
          ,sum(ss_coupon_amt) amt
          ,sum(ss_net_profit) profit
    from store_sales,date_dim,store,household_demographics
    where store_sales.ss_sold_date_sk = date_dim.d_date_sk
    and store_sales.ss_store_sk = store.s_store_sk  
    and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
    and (household_demographics.hd_dep_count = 0 or household_demographics.hd_vehicle_count > 2)
    and date_dim.d_dow = 1
    and date_dim.d_year in (1999,1999+1,1999+2) 
    and store.s_number_employees between 200 and 295
    group by ss_ticket_number,ss_customer_sk,ss_addr_sk,store.s_city) ms,customer
    where ss_customer_sk = c_customer_sk
 order by c_last_name,c_first_name,substring(s_city,1,30), profit
limit 100;

出力結果(一部)
image.png

上記のSQL文をchatgpt(4.0)に解説させると以下の通りです。

このSQLクエリは、顧客の名前、都市、チケット番号、クーポンの総額、および利益に関するデータを取得しています。
クエリは以下の主要な部分から構成されています。
(略)
ソートと制限: 最後に、c_last_name、c_first_name、s_city（最初の30文字）、および profit に基づいて結果をソートし、最初の100行のみを取得しています。

このクエリの目的は、特定の条件を満たす売上データに基づいて、顧客ごとのクーポン使用総額と利益を計算し、これらの情報と共に顧客の名前と都市を取得することです。

[クエリとロード]の画面キャプチャは以下のとおりです。

image.png

クエリ(query4.tpl)

  • 93番目に実施した以下のクエリを見てみます。
    • query4.tpl
    • 所要時間：2時間

with /* TPC-DS query4.tpl 0.93 */ year_total as (
 select c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2) year_total
       ,'s' sale_type
 from customer
     ,store_sales
     ,date_dim
 where c_customer_sk = ss_customer_sk
   and ss_sold_date_sk = d_date_sk
 group by c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
 union all
 select c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum((((cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt)+cs_ext_sales_price)/2) ) year_total
       ,'c' sale_type
 from customer
     ,catalog_sales
     ,date_dim
 where c_customer_sk = cs_bill_customer_sk
   and cs_sold_date_sk = d_date_sk
 group by c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
union all
 select c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum((((ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt)+ws_ext_sales_price)/2) ) year_total
       ,'w' sale_type
 from customer
     ,web_sales
     ,date_dim
 where c_customer_sk = ws_bill_customer_sk
   and ws_sold_date_sk = d_date_sk
 group by c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
         )
  select  
                  t_s_secyear.customer_id
                 ,t_s_secyear.customer_first_name
                 ,t_s_secyear.customer_last_name
                 ,t_s_secyear.customer_email_address
 from year_total t_s_firstyear
     ,year_total t_s_secyear
     ,year_total t_c_firstyear
     ,year_total t_c_secyear
     ,year_total t_w_firstyear
     ,year_total t_w_secyear
 where t_s_secyear.customer_id = t_s_firstyear.customer_id
   and t_s_firstyear.customer_id = t_c_secyear.customer_id
   and t_s_firstyear.customer_id = t_c_firstyear.customer_id
   and t_s_firstyear.customer_id = t_w_firstyear.customer_id
   and t_s_firstyear.customer_id = t_w_secyear.customer_id
   and t_s_firstyear.sale_type = 's'
   and t_c_firstyear.sale_type = 'c'
   and t_w_firstyear.sale_type = 'w'
   and t_s_secyear.sale_type = 's'
   and t_c_secyear.sale_type = 'c'
   and t_w_secyear.sale_type = 'w'
   and t_s_firstyear.dyear =  1998
   and t_s_secyear.dyear = 1998+1
   and t_c_firstyear.dyear =  1998
   and t_c_secyear.dyear =  1998+1
   and t_w_firstyear.dyear = 1998
   and t_w_secyear.dyear = 1998+1
   and t_s_firstyear.year_total > 0
   and t_c_firstyear.year_total > 0
   and t_w_firstyear.year_total > 0
   and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end
           > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end
   and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end
           > case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end
 order by t_s_secyear.customer_id
         ,t_s_secyear.customer_first_name
         ,t_s_secyear.customer_last_name
         ,t_s_secyear.customer_email_address
limit 100;

上記のSQL文をchatgpt(4.0)に解説させると以下の通りです。

with句での一時テーブル（Common Table Expression, CTE）作成
まず、with句を使用してyear_totalという名前の一時テーブル（CTE）を作成しています。
このテーブルは、顧客（customer）と売上データ（store_sales, catalog_sales, web_sales）および日付データ（date_dim）を結合し、顧客ごとに特定の計算式でyear_totalを算出しています。

このCTEは3つの異なるセクションで組み立てられており、それぞれがunion allで結合されています。
各セクションはstore_sales、catalog_sales、web_salesに対応しています。

主要なselect文
次に、この一時テーブルをさまざまな条件でフィルタリングしています。

顧客IDと売上の種類（sale_type）で複数の行を結合しています。
各顧客に対して、1998年と1999年の売上を比較しています。
特定の計算式で1999年の成長率を比較し、特定の条件を満たす顧客の情報を選択しています。
出力
最後に、特定の条件を満たす顧客のID、名前、姓、メールアドレスを出力しています。
結果は最大100行に制限されています。

このSQLクエリは、特定のビジネスロジックまたは分析要件に基づいて設計されていると思われます。
具体的には、1998年と1999年の間で成長率が最も高い販売チャンネル（店舗、カタログ、ウェブ）を持つ顧客を特定しています。

[クエリとロード]の画面キャプチャは以下のとおりです。

image.png

以上です。

2
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?