データ分析におけるPostgreSQL、MySQLの今 / PostgreSQL and MySQL in data analysis

1
1 | © 2023 Exture Inc.
関西DB勉強会
2023.1.21
データ分析における
PostgreSQL/MySQLの今
エクスチュア株式会社
日本PostgreSQLユーザ会
喜田紘介
日本オラクル株式会社
MySQL Cloud Evangelist
山﨑由章

View Slide

2
はじめに

View Slide

3
自己紹介
業務系DB（PostgreSQL）の技術支援・教育 → データ分析企業に転生

View Slide

4
データ分析に使うSQL
データ分析に使うSQLは業務系と少し違う？
💡データ分析において、データの発生～連携～蓄積～レポート化と様々なツールを駆使するが、
それらの共通言語としてSQLはよく出てくる
SQLさえ書ければ、
私から素敵なインサイトを提供します！
ほら、簡単でしょ？ SQLをお客様に
教えてた僕
文法はわかる
転生直後の僕
統計関数？
ウィンドウ関数？
普通の関数と
どう違うの？？
WHERE句で絞れないGROUP BY
性能が気になって
とてもじゃないけど実行する勇気がない！
本セッションでは、自分がこの数週間で学んだデータ分析に使えるRDBの機能、関数などを整理し、
これからデータ分析に携わる人の手助けや、これまでの経験ではなじみの薄いDB機能を使いこなせるような
ヒントを示せればと思います。

View Slide

5
エクスチュア会社概要

View Slide

6
会社名エクスチュア株式会社(Exture Inc.)
事業内容マーケティングテクノロジスト事業
ビッグデータ事業
ソリューション販売事業
Adobe Analyticsの 2005年日本サービス開始当初から15年以上の豊富な経験
デジタルマーケティング領域の経験値と、最先端のビックデータ領域を掛け合わせた柔軟な施策をワンストップで実現
BI
MA
その他保有データ
デジタルマーケティングデータ分析基盤データ活用

View Slide

7
トータルサポート
Adobe Analyticsの 2005年日本サービス開始当初から15年以上の豊富な経験
デジタルマーケティング領域の経験値と、最先端のビックデータ領域を掛け合わせた柔軟な施策をワンストップで実現
集める・貯める加工・管理する可視化する分析する活用する
データ品質管理
する

View Slide

Oracle Database の技術支援・コンサル・教育など → MySQLチームに転職
自己紹介
Copyright © 2023 Oracle and/or its affiliates. All rights reserved.
8

View Slide

9
オラクルクラウドのビジネス状況

View Slide

オラクルのクラウドビジネスは、高い成長率で拡大しています
オラクル事業概況
10
0%
5%
10%
15%
20%
25%
30%
35%
40%
45%
0 5 10 15 20 25 30
直近四半期の各クラウドベンダーの業績
各社発表資料より作成
Oracle Cloud全体 +48%
Oracle Cloud Infrastructure +59%
Cloud Application (SaaS) +45%
Fusion ERP Cloud +28％
NetSuite ERP Cloud +29%
(FY23Q2, Dec.12.2022, Constant Currencyベース)
Revenue B$
• Oracle: 43% to $3.8 billion (Dec.12.2022)
• Google Cloud: 38% to $6.8 billion (Sep.30.2022)
• SAP: 38% to $3.28 billion (Sep.30.2022)
• AWS: 27% to $20.5 billion (Sep.30.2022)
• Microsoft: 24% to $25.7 billion (Sep.30.2022)
• ServiceNow: 22% to $1.74 billion (Sep.30.2022)
• IBM: 19% to $5.2 billion (Sep.30.2022)
• Workday: 23% to $1.37 billion (July.31.2022)
• Salesforce: 21% to $7.14 billion (July.31.2022)
• Snowflake: 83% to $466M (July.31.2022)
Oracle
Google Cloud
SAP
Growth
Microsoft
AWS
Salesforce
IBM
Workday
ServiceNow
Cloud CAGR

View Slide

Cloud Integrated IaaS & PaaS Solution Scorecard Comparison
Gartnerによる評価も急上昇し、他社クラウドに近いスコアを記録
11
R E Q U I R E D S O L U T I O N S S C O R E S
O V E R A L L W E I G H T E D S C O R E S
2019 2020 2021
88
94
93 AWS
66
84
78
AZURE
38
ORACLE
49
75
71 GOOGLE
33
40 IBM
78
62
2019 2020 2021
97
98
98 AWS
70
92
85
AZURE
45
90
74
ORACLE
53
81
76
GOOGLE
38
50 IBM

View Slide

ガバメントクラウドにOracle Cloud Infrastructure（OCI）が採用決定
12
デジタル庁におけるガバメントクラウド整備のためのクラウド
サービスの提供－令和４年度募集－の公募結果について
https://www.digital.go.jp/assets/contents/node/basic_page/field_ref_resources/d6b5753c-c4eb-4ee6-
92d0-21b3fa945a82/b90d9207/20221003_policies_gov_cloud_outline_01.pdf
https://www.oracle.com/jp/news/announcement/oracle-japan-selected-as-government-cloud-service
provider-2022-10-06/

View Slide

13
13 | © 2023 Exture Inc.
主要製品のデータ分析機能 in 2023

View Slide

14
14 | © 2023 Exture Inc.
汎用集計関数
入力値の集合から単一の結果を返す
関数の例 PostgreSQL MySQL HeatWave BigQuery RedShift
sum ○ ○ ○ ○ ○
avg ○ ○ ○ ○ ○
max
min
○ ○ ○ ○ ○
count ○ ○ ○ ○ ○
汎用集計関数の例 – GROUP BYを伴い、グループにつき1行の結果を返す
SELECT date,avg(price) AS avg_price
FROM sales
GROUP BY date;
date avg_price
2023-1-1 12345
2023-1-2 23456
2023-1-3 54321

View Slide

15
15 | © 2023 Exture Inc.
汎用集計関数
入力値の集合から単一の結果を返す
bit_and ○ ○ × ○ ○
bit_or ○ ○ × ○ ○
bit_xor ○ ○ × ○ ×
bool_and
logical_and
○ × × ○ ○
bool_or
logical_or
○ × × ○ ○
汎用集計関数の例 – GROUP BYを伴い、グループにつき1行の結果を返す
SELCT city, bit_or(likes)
FROM userlikes
GROUP BY city;
bit_orの利用例：https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_BIT_OR.html
city bit_or
Los Angeles 127
San Francisco 255
bit_or 2進
127 01111111
255 11111111
0 1 1 1 1 1 1 1
0 1 0 0 0 1 0 1
0 1 0 1 1 0 0 1
0 1 1 0 1 0 1 1
演
劇
→
ス
ポ
ー
ツ
→
映
画
→
花
火
→

View Slide

16
16 | © 2023 Exture Inc.
統計用関数
統計集約によく用いられる関数、使い方は汎用集計関数と同様
stddev_pop ○ ○ ○ ○ ○
stddev_samp ○ ○ ○ ○ ○
var_pop ○ ○ ○ ○ ○
var_samp ○ ○ ○ ○ ○
この写真の作成者不明な作成者は CC BY-NC のライセンスを許諾されています
母標準偏差
標本標準偏差
母分散
標本分散

View Slide

17
17 | © 2023 Exture Inc.
統計用関数
統計集約によく用いられる関数、引数の取り方が特徴的
corr（Y,X） ○ × × ○ ×
covar_pop（Y,X） ○ × × ○ ×
covar_samp（Y,X） ○ × × ○ ×
regr_avgx（Y,X）
regr_avgy（Y,X）
○ × × × ×
regr_count（Y,X） ○ × × × ×
regr_intercept（Y,X） ○ × × × ×
regr_r2（Y,X） ○ × × × ×
regr_slope（Y,X） ○ × × × ×
regr_sxx（Y,X）
regr_sxy（Y,X）
regr_syy（Y,X）
○ × × × ×
RedShift/サポートされていないPostgreSQL関数 https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_unsupported-postgresql-functions.html
BigQuery/SnowflakeとBigQueryの書き換え https://cloud.google.com/bigquery/docs/migration/snowflake-sql?hl=ja
他の製品では、Oracle、DB2、Snowflakeなどが対応

View Slide

18
18 | © 2023 Exture Inc.
順序集合集約関数（逆分散関数）
ORDER BYを伴う順序を持った入力を前提とする分析関数
mode
top
approx_top_count
○ × × ○ ○
median × × × × ○
percentile_cont(fraction) ○ × × ○ ○
percentile_disc(fraction) ○ × × ○ ○
PostgreSQLではパラレルクエリできない（パラレルで結果セットを集めたあとに関数適用するような動作）
順序集合集約関数の例 – ソートされた列値をインプット（WITHIN GROUP句）
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY price)
FROM sales;
引数は列名でなく、0～1までの値
ここで列名（ソートを指示）

View Slide

19
19 | © 2023 Exture Inc.
ウィンドウ関数
仮想的な行（ウィンドウ分割された範囲）に対して行う集計関数
row_number() ○ ○ × ○ ○
rank() ○ ○ × ○ ○
dense_rank() ○ ○ × ○ ○
percent_rank() ○ ○ × ○ ○
cume_dist() ○ ○ × ○ ○
ntile() ○ ○ × ○ ○
lag() ○ ○ × ○ ○
lead() ○ ○ × ○ ○
first_value() ○ ○ × ○ ○
last_value() ○ ○ × ○ ○
nth_value(value,n) ○ ○ × ○ ○
PostgreSQLではパラレルクエリできない（パラレルで結果セットを集めたあとに関数適用するような動作）
※ウィンドウ定義には対応

View Slide

20
20 | © 2023 Exture Inc.
ウィンドウ関数
仮想的な行（ウィンドウ分割された範囲）に対して行う集計関数
地域日付気温
札幌 2023/1/1 2
札幌 2023/1/2 1
札幌 2023/1/3 2
札幌 2023/1/4 3
札幌 2023/1/5 0
札幌 2023/1/6 1
札幌 2023/1/7 2
札幌 2023/1/8 1
札幌 2023/1/9 4
札幌 2023/1/10 2
札幌 2023/1/11 3
東京 2023/1/1 4
東京 2023/1/2 5
東京 2023/1/3 3
東京 2023/1/4 5
東京 2023/1/5 3
東京 2023/1/6 4
東京 2023/1/7 4
東京 2023/1/8 7
東京 2023/1/9 4
東京 2023/1/10 5
東京 2023/1/11 5
大阪 2023/1/1 7
大阪 2023/1/2 10
大阪 2023/1/3 8
大阪 2023/1/4 3
大阪 2023/1/5 4
大阪 2023/1/6 3
大阪 2023/1/7 2
大阪 2023/1/8 1
大阪 2023/1/9 4
大阪 2023/1/10 4
大阪 2023/1/11 5
普通の
GROUP BY 地域
1.9℃
4.5℃
4.6℃
avg(気温) avg(気温)
1.9
1.9
1.9
1.9
1.9
1.9
1.9
1.9
1.9
1.9
1.9
4.5
4.5
4.5
4.5
4.5
4.5
4.5
4.5
4.5
4.5
4.5
4.6
4.6
4.6
4.6
4.6
4.6
4.6
4.6
4.6
4.6
4.6
PARTITION BY 地域
avg(気温)
地域日付気温
札幌 2023/1/1 2
札幌 2023/1/2 1
札幌 2023/1/3 2
札幌 2023/1/4 3
札幌 2023/1/5 0
札幌 2023/1/6 1
札幌 2023/1/7 2
札幌 2023/1/8 1
札幌 2023/1/9 4
札幌 2023/1/10 2
札幌 2023/1/11 3
東京 2023/1/1 4
東京 2023/1/2 5
東京 2023/1/3 3
東京 2023/1/4 5
東京 2023/1/5 3
東京 2023/1/6 4
東京 2023/1/7 4
東京 2023/1/8 7
東京 2023/1/9 4
東京 2023/1/10 5
東京 2023/1/11 5
大阪 2023/1/1 7
大阪 2023/1/2 10
大阪 2023/1/3 8
大阪 2023/1/4 3
大阪 2023/1/5 4
大阪 2023/1/6 3
大阪 2023/1/7 2
大阪 2023/1/8 1
大阪 2023/1/9 4
大阪 2023/1/10 4
大阪 2023/1/11 5
PARTITION BY 地域 ORDER BY 日付
ROWS 3日 PRECEDING
1.7
2.0
1.7
1.3
1.0
1.3
2.3
2.3
3.0
4.0
4.3
3.7
4.0
3.7
5.0
5.0
5.3
4.7
8.3
7.0
5.0
3.3
3.0
2.0
2.3
3.0
4.3

View Slide

21
21 | © 2023 Exture Inc.
格納アーキテクチャ、その他の高速化手段
行指向/列指向、テーブルパーティショニングなど
機能 PostgreSQL MySQL HeatWave BigQuery RedShift
行指向/列指向行行列列列
ノード分散 × × ○ ○ ○
テーブル
パーティショニング
○ ○ - - -
機能 PostgreSQL MySQL HeatWave BigQuery RedShift
インメモリ × × ○
○
※BI Engine
△
結果セットの一部
キャッシュ
パラレルクエリ
並列処理
○
汎用集計関数のみ
× ○ ○ ○
大規模向け索引 BRIN × - - -
インメモリ、パラレルクエリ、大規模用インデックスなど

View Slide

22
22 | © 2023 Exture Inc.
PostgreSQL 15

View Slide

23
23 | © 2023 Exture Inc.
ウィンドウ関数の性能向上
純増（減）していくことが明らかな場合（row_numberやlank）に、WHERE条件を考慮して不要な集計を中断することで性能向上
area sales_date price
tokyo 2023-01-01 1200
osaka 2023-01-01 1000
nagoya 2023-01-01 1900
tokyo 2023-01-02 1000
osaka 2023-01-02 1200
nagoya 2023-01-02 1200
tokyo 2023-01-03 1100
osaka 2023-01-03 1700
nagoya 2023-01-03 1600
tokyo 2023-01-04 1300
osaka 2023-01-04 1000
nagoya 2023-01-04 1200
tokyo 2023-01-05 1500
osaka 2023-01-05 1100
nagoya 2023-01-05 1000
area sales_date price row_number
tokyo 2023-01-05 1500 1
tokyo 2023-01-04 1300 2
tokyo 2023-01-01 1200 3
tokyo 2023-01-03 1100 4
tokyo 2023-01-02 1000 5
osaka 2023-01-03 1700 1
osaka 2023-01-02 1200 2
osaka 2023-01-05 1100 3
osaka 2023-01-01 1000 4
osaka 2023-01-04 1000 5
nagoya 2023-01-01 1900 1
nagoya 2023-01-03 1600 2
nagoya 2023-01-02 1200 3
nagoya 2023-01-04 1200 4
nagoya 2023-01-05 1000 5
SELECT * ,row_number()
OVER (PARTITION BY area
ORDER BY price DESC) rn
FROM sales
SELECT * FROM (
SELECT * ,row_number()
OVER (PARTITION BY area
ORDER BY price DESC) rn
FROM sales) sales_rn
WHERE rn <= 3 ;
area sales_date price row_number
tokyo 2023-01-05 1500 1
tokyo 2023-01-04 1300 2
tokyo 2023-01-01 1200 3
tokyo 2023-01-03 1100 4
tokyo 2023-01-02 1000 5
osaka 2023-01-03 1700 1
osaka 2023-01-02 1200 2
osaka 2023-01-05 1100 3
osaka 2023-01-01 1000 4
osaka 2023-01-04 1000 5
nagoya 2023-01-01 1900 1
nagoya 2023-01-03 1600 2
nagoya 2023-01-02 1200 3
nagoya 2023-01-04 1200 4
nagoya 2023-01-05 1000 5
v14までは不要行を含めたすべて計算したうえで最後にWHERE条件を適用
PostgreSQL15 ではウィンドウ関数処理時点で不要な結果を除去

View Slide

性能が良くてコストパフォーマンスが高いクラウドDWHサービス
MySQLだけで超高速な分析処理を実現！
✓ MySQLのバックエンドにHeatWaveノードが存在し、
クエリーアクセラレーターとして動作
✓ 列指向、インメモリでデータを持ち、ノード分散＆
チャンク分割による超並列処理を実現
✓ 従来通り、OLTP系の処理も実行可能
(データ変更時は自動的にHeatWaveノードに
更新が反映される)
圧倒的なパフォーマンスとコスト効率化！
✓ TPC-HにおいてRedshift、Snowflake、
BigQuery、 Azure Synapse Analyticsより
も安価な環境で高速処理を実現
✓ ベンチマークテスト再現手順も公表中
MySQL HeatWave Database Service
25
OLAP
アプリケーション
OLTP
アプリケーション
OLTP
Engine
Query
Accelerator
M ySQL
HeatWave
Database
Service
https://www.oracle.com/mysql/heatwave/performance/
HeatWaveノード
MySQLノード

View Slide

26
• ベクトル化処理を容易にする独自フォーマットで
エンコーディング＆圧縮後、チャンク分割
• CPUコア間やノード間でのデータ移動を最小限に
抑えられるように設計されている
• Oracle LabsのProject RAPIDの成果を活用
• ゾーンマップにより、圧縮したデータの中から必要な
データのみを展開
• 各チャンク毎に、主キー(分散キー)の
最小値/最大値を保持しているため、
クエリー実行時に必要なチャンクを判断可能
MySQL HeatWave での高速化の仕組み
HeatWaveノードのデータ保持イメージ
※分散キーは主キー以外にも変更可能

View Slide

27
• HeatWaveで高速化できないクエリーは、フロントにあるMySQL Database Serviceで処理されます。
• HeatWaveの制限事項
https://dev.mysql.com/doc/heatwave/en/heatwave-limitations.html
• 現時点では、まだMySQLで使用できる全てのデータ型、関数に対応できていません。
随時改善を続けていますので、最新情報はHeatWaveのドキュメントで確認下さい。
• HeatWaveがサポートしているデータ型
https://dev.mysql.com/doc/heatwave/en/heatwave-supported-data-types.html
• HeatWaveがサポートしている関数
https://dev.mysql.com/doc/heatwave/en/heatwave-function-operator-reference.html
• HeatWaveで高速化できる/できないの判断はクエリー全体で行われるため、UNION、サブクエリを多用していると
HeatWaveで高速化できなくなる確率が上がります。
• あるサブクエリーにHeatWaveがサポートできない関数を使用している
=> このクエリー全体がHeatWaveで高速化できなくなる
MySQL HeatWave 使用時の注意点

View Slide

29
29 | © 2023 Exture Inc.
（おまけ）PostgreSQLの環境構築
初級者向け、Windows上で簡単にPostgreSQL on Linux環境を構築！SQL学習に最適！
Windows標準機能でLinuxを動かす！
PostgreSQL on WSL（AlmaLinux）の作り方
intel 12世代コアならノートPCでも
パラレルクエリの検証が捗るね！

View Slide

オラクルクラウドのトライアルアカウントを作成し、MySQL HeatWave環境を構築＆パフォーマンスの良さを体験！
（おまけ）MySQL HeatWave のチュートリアル
30
入門編 - Oracle Cloud Infrastructure を使ってみよう
https://oracle-japan.github.io/ocitutorials/beginners/
チュートリアル : Oracle Cloud Infrastructure を使ってみよう
https://oracle-japan.github.io/ocitutorials/
OCIのチュートリアルページにMySQL Database Service、HeatWaveのチュートリアルを掲載しています！(入門編：その9、その10)
画面キャプチャー付きで詳細に手順を解説しています！

View Slide

WE ARE HIRING!!
日本オラクルのMySQLグローバルビジネスユニットでは
Oracle Cloud Infrastructureで展開する
MySQL HeatWave Database Serviceの営業担当者を
募集しています！
進化を続けるMySQLと共にグローバルなチームで活躍してみませんか？
詳細は以下ページをご参照下さい。
https://oracle.taleo.net/careersection/1/jobdetail.ftl?job=22000FE5
なお、MySQL製品全般のパートナー営業担当者も募集しています。
詳細は以下ページをご参照下さい。
https://oracle.taleo.net/careersection/2/jobdetail.ftl?job=23000045
※応募前に、カジュアルにMySQLチームの職場環境などについて
お伝えすることも可能ですので、少しでも興味を持って頂けた
方はお気軽に [email protected] までご相談下さい！

View Slide

データ分析におけるPostgreSQL、MySQLの今 / PostgreSQL and MySQL in data analysis

データ分析におけるPostgreSQL、MySQLの今 / PostgreSQL and MySQL in data analysis

YoshiakiYamasaki

More Decks by YoshiakiYamasaki

Other Decks in Technology

Featured

Transcript