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

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

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

Transcript

1. 1 1 | © 2023 Exture Inc. 関西DB勉強会 2023.1.21 データ分析における PostgreSQL/MySQLの今 エクスチュア株式会社 日本PostgreSQLユーザ会 喜田紘介 日本オラクル株式会社 MySQL Cloud Evangelist 山﨑由章
2. 2 2 | © 2023 Exture Inc. はじめに
3. 3 3 | © 2023 Exture Inc. 自己紹介 業務系DB（PostgreSQL）の技術支援・教育 → データ分析企業に転生
4. 4 4 | © 2023 Exture Inc. データ分析に使うSQL データ分析に使うSQLは業務系と少し違う？ 💡データ分析において、データの発生～連携～蓄積～レポート化と様々なツールを駆使するが、 それらの共通言語としてSQLはよく出てくる SQLさえ書ければ、 私から素敵なインサイトを提供します！ ほら、簡単でしょ？ SQLをお客様に 教えてた僕 文法はわかる 転生直後の僕 統計関数？ ウィンドウ関数？ 普通の関数と どう違うの？？ WHERE句で絞れないGROUP BY 性能が気になって とてもじゃないけど実行する勇気がない！ 本セッションでは、自分がこの数週間で学んだデータ分析に使えるRDBの機能、関数などを整理し、 これからデータ分析に携わる人の手助けや、これまでの経験ではなじみの薄いDB機能を使いこなせるような ヒントを示せればと思います。
5. 5 5 | © 2023 Exture Inc. エクスチュア会社概要
6. 6 6 | © 2023 Exture Inc. エクスチュア株式会社 会社名 エクスチュア株式会社(Exture Inc.) 事業内容 マーケティングテクノロジスト事業 ビッグデータ事業 ソリューション販売事業 Adobe Analyticsの 2005年 日本サービス開始当初から15年以上の豊富な経験 デジタルマーケティング領域の経験値と、最先端のビックデータ領域を掛け合わせた柔軟な施策をワンストップで実現 BI MA その他保有データ デジタルマーケティング データ分析基盤 データ活用
7. 7 7 | © 2023 Exture Inc. トータルサポート エクスチュア株式会社 Adobe Analyticsの 2005年 日本サービス開始当初から15年以上の豊富な経験 デジタルマーケティング領域の経験値と、最先端のビックデータ領域を掛け合わせた柔軟な施策をワンストップで実現 集める・貯める 加工・管理する 可視化する 分析する 活用する データ品質管理 する
8. Oracle Database の技術支援・コンサル・教育など → MySQLチームに転職 自己紹介 Copyright © 2023 Oracle and/or its affiliates. All rights reserved. 8
9. Copyright © 2023 Oracle and/or its affiliates. All rights reserved. 9 オラクルクラウドのビジネス状況
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
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
12. ガバメントクラウドにOracle Cloud Infrastructure（OCI）が採用決定 Copyright © 2023 Oracle and/or its affiliates. All rights reserved. 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/
13. 13 13 | © 2023 Exture Inc. 主要製品のデータ分析機能 in 2023
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
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 演 劇 → ス ポ ー ツ → 映 画 → 花 火 →
16. 16 16 | © 2023 Exture Inc. 統計用関数 統計集約によく用いられる関数、使い方は汎用集計関数と同様 関数の例 PostgreSQL MySQL HeatWave BigQuery RedShift stddev_pop ◦ ◦ ◦ ◦ ◦ stddev_samp ◦ ◦ ◦ ◦ ◦ var_pop ◦ ◦ ◦ ◦ ◦ var_samp ◦ ◦ ◦ ◦ ◦ この写真 の作成者 不明な作成者 は CC BY-NC のライセンスを許諾されています 母標準偏差 標本標準偏差 母分散 標本分散
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などが対応
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までの値 ここで列名（ソートを指示）
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ではパラレルクエリできない（パラレルで結果セットを集めたあとに関数適用するような動作） ※ウィンドウ定義には対応
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
21. 21 21 | © 2023 Exture Inc. 格納アーキテクチャ、その他の高速化手段 行指向/列指向、テーブルパーティショニングなど 機能 PostgreSQL MySQL HeatWave BigQuery RedShift 行指向/列指向 行 行 列 列 列 ノード分散 × × ◦ ◦ ◦ テーブル パーティショニング ◦ ◦ - - - 機能 PostgreSQL MySQL HeatWave BigQuery RedShift インメモリ × × ◦ ◦ ※BI Engine △ 結果セットの一部 キャッシュ パラレルクエリ 並列処理 ◦ 汎用集計関数のみ × ◦ ◦ ◦ 大規模向け索引 BRIN × - - - インメモリ、パラレルクエリ、大規模用インデックスなど
22. 22 22 | © 2023 Exture Inc. PostgreSQL 15
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 ではウィンドウ関数処理時点で不要な結果を除去
24. 24 24 | © 2023 Exture Inc. MySQL + Heatwave
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ノード
26. Copyright © 2023 Oracle and/or its affiliates. All rights reserved. 26 • ベクトル化処理を容易にする独自フォーマットで エンコーディング＆圧縮後、チャンク分割 • CPUコア間やノード間でのデータ移動を最小限に 抑えられるように設計されている • Oracle LabsのProject RAPIDの成果を活用 • ゾーンマップにより、圧縮したデータの中から必要な データのみを展開 • 各チャンク毎に、主キー(分散キー)の 最小値/最大値を保持しているため、 クエリー実行時に必要なチャンクを判断可能 MySQL HeatWave での高速化の仕組み HeatWaveノードのデータ保持イメージ ※分散キーは主キー以外にも変更可能
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 使用時の注意点
28. 28 28 | © 2023 Exture Inc. ご清聴ありがとうございました
29. 29 29 | © 2023 Exture Inc. （おまけ）PostgreSQLの環境構築 初級者向け、Windows上で簡単にPostgreSQL on Linux環境を構築！SQL学習に最適！ Windows標準機能でLinuxを動かす！ PostgreSQL on WSL（AlmaLinux）の作り方 intel 12世代コアならノートPCでも パラレルクエリの検証が捗るね！
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) 画面キャプチャー付きで詳細に手順を解説しています！
31. 31 31 | © 2023 Exture Inc. データエンジニア 積極採用中！
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チームの職場環境などについて お伝えすることも可能ですので、少しでも興味を持って頂けた 方はお気軽に yoshiaki.yamasaki@oracle.com までご相談下さい！