Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

2023/01/21(土)に開催された「第12回 関西DB勉強会」での発表資料です。

データ分析用途で用いられる色々な関数の対応状況や、データ分析用途に向いたMySQL HeatWaveの概要などについて紹介しています。

YoshiakiYamasaki

February 09, 2023
Tweet

More Decks by YoshiakiYamasaki

Other Decks in Technology

Transcript

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

    View Slide

  2. 2
    2 | © 2023 Exture Inc.
    はじめに

    View Slide

  3. 3
    3 | © 2023 Exture Inc.
    自己紹介
    業務系DB(PostgreSQL)の技術支援・教育 → データ分析企業に転生

    View Slide

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

    View Slide

  5. 5
    5 | © 2023 Exture Inc.
    エクスチュア会社概要

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  9. Copyright © 2023 Oracle and/or its affiliates. All rights reserved.
    9
    オラクルクラウドのビジネス状況

    View Slide

  10. オラクルのクラウドビジネスは、高い成長率で拡大しています
    オラクル事業概況
    Copyright © 2023 Oracle and/or its affiliates. All rights reserved.
    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

  11. Cloud Integrated IaaS & PaaS Solution Scorecard Comparison
    Gartnerによる評価も急上昇し、他社クラウドに近いスコアを記録
    Copyright © 2023 Oracle and/or its affiliates. All rights reserved.
    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

  12. ガバメントクラウドにOracle Cloud Infrastructure(OCI)が採用決定
    Copyright © 2023 Oracle and/or its affiliates. All rights reserved.
    12
    デジタル庁におけるガバメントクラウド整備のためのクラウド
    サービスの提供-令和4年度募集-の公募結果について
    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
    13 | © 2023 Exture Inc.
    主要製品のデータ分析機能 in 2023

    View Slide

  14. 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
    15 | © 2023 Exture Inc.
    汎用集計関数
    入力値の集合から単一の結果を返す
    関数の例 PostgreSQL MySQL HeatWave BigQuery RedShift
    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
    16 | © 2023 Exture Inc.
    統計用関数
    統計集約によく用いられる関数、使い方は汎用集計関数と同様
    関数の例 PostgreSQL MySQL HeatWave BigQuery RedShift
    stddev_pop ○ ○ ○ ○ ○
    stddev_samp ○ ○ ○ ○ ○
    var_pop ○ ○ ○ ○ ○
    var_samp ○ ○ ○ ○ ○
    この写真 の作成者 不明な作成者 は CC BY-NC のライセンスを許諾されています
    母標準偏差
    標本標準偏差
    母分散
    標本分散

    View Slide

  17. 17
    17 | © 2023 Exture Inc.
    統計用関数
    統計集約によく用いられる関数、引数の取り方が特徴的
    関数の例 PostgreSQL MySQL HeatWave BigQuery RedShift
    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
    18 | © 2023 Exture Inc.
    順序集合集約関数(逆分散関数)
    ORDER BYを伴う順序を持った入力を前提とする分析関数
    関数の例 PostgreSQL MySQL HeatWave BigQuery RedShift
    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
    19 | © 2023 Exture Inc.
    ウィンドウ関数
    仮想的な行(ウィンドウ分割された範囲)に対して行う集計関数
    関数の例 PostgreSQL MySQL HeatWave BigQuery RedShift
    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
    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
    21 | © 2023 Exture Inc.
    格納アーキテクチャ、その他の高速化手段
    行指向/列指向、テーブルパーティショニングなど
    機能 PostgreSQL MySQL HeatWave BigQuery RedShift
    行指向/列指向 行 行 列 列 列
    ノード分散 × × ○ ○ ○
    テーブル
    パーティショニング
    ○ ○ - - -
    機能 PostgreSQL MySQL HeatWave BigQuery RedShift
    インメモリ × × ○

    ※BI Engine

    結果セットの一部
    キャッシュ
    パラレルクエリ
    並列処理

    汎用集計関数のみ
    × ○ ○ ○
    大規模向け索引 BRIN × - - -
    インメモリ、パラレルクエリ、大規模用インデックスなど

    View Slide

  22. 22
    22 | © 2023 Exture Inc.
    PostgreSQL 15

    View Slide

  23. 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

  24. 24
    24 | © 2023 Exture Inc.
    MySQL + Heatwave

    View Slide

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

    View Slide

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

    View Slide

  27. Copyright © 2023 Oracle and/or its affiliates. All rights reserved.
    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

  28. 28
    28 | © 2023 Exture Inc.
    ご清聴ありがとうございました

    View Slide

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

    View Slide

  30. オラクルクラウドのトライアルアカウントを作成し、MySQL HeatWave環境を構築&パフォーマンスの良さを体験!
    (おまけ)MySQL HeatWave のチュートリアル
    Copyright © 2023 Oracle and/or its affiliates. All rights reserved.
    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

  31. 31
    31 | © 2023 Exture Inc.
    データエンジニア
    積極採用中!

    View Slide

  32. 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