2023/01/21(土)に開催された「第12回 関西DB勉強会」での発表資料です。
データ分析用途で用いられる色々な関数の対応状況や、データ分析用途に向いたMySQL HeatWaveの概要などについて紹介しています。
11 | © 2023 Exture Inc.関西DB勉強会2023.1.21データ分析におけるPostgreSQL/MySQLの今エクスチュア株式会社日本PostgreSQLユーザ会喜田紘介日本オラクル株式会社MySQL Cloud Evangelist山﨑由章
View Slide
22 | © 2023 Exture Inc.はじめに
33 | © 2023 Exture Inc.自己紹介業務系DB(PostgreSQL)の技術支援・教育 → データ分析企業に転生
44 | © 2023 Exture Inc.データ分析に使うSQLデータ分析に使うSQLは業務系と少し違う?💡データ分析において、データの発生~連携~蓄積~レポート化と様々なツールを駆使するが、それらの共通言語としてSQLはよく出てくるSQLさえ書ければ、私から素敵なインサイトを提供します!ほら、簡単でしょ? SQLをお客様に教えてた僕文法はわかる転生直後の僕統計関数?ウィンドウ関数?普通の関数とどう違うの??WHERE句で絞れないGROUP BY性能が気になってとてもじゃないけど実行する勇気がない!本セッションでは、自分がこの数週間で学んだデータ分析に使えるRDBの機能、関数などを整理し、これからデータ分析に携わる人の手助けや、これまでの経験ではなじみの薄いDB機能を使いこなせるようなヒントを示せればと思います。
55 | © 2023 Exture Inc.エクスチュア会社概要
66 | © 2023 Exture Inc.エクスチュア株式会社会社名 エクスチュア株式会社(Exture Inc.)事業内容 マーケティングテクノロジスト事業ビッグデータ事業ソリューション販売事業Adobe Analyticsの 2005年 日本サービス開始当初から15年以上の豊富な経験デジタルマーケティング領域の経験値と、最先端のビックデータ領域を掛け合わせた柔軟な施策をワンストップで実現BIMAその他保有データデジタルマーケティング データ分析基盤 データ活用
77 | © 2023 Exture Inc.トータルサポートエクスチュア株式会社Adobe Analyticsの 2005年 日本サービス開始当初から15年以上の豊富な経験デジタルマーケティング領域の経験値と、最先端のビックデータ領域を掛け合わせた柔軟な施策をワンストップで実現集める・貯める 加工・管理する 可視化する 分析する 活用するデータ品質管理する
Oracle Database の技術支援・コンサル・教育など → MySQLチームに転職自己紹介Copyright © 2023 Oracle and/or its affiliates. All rights reserved.8
Copyright © 2023 Oracle and/or its affiliates. All rights reserved.9オラクルクラウドのビジネス状況
オラクルのクラウドビジネスは、高い成長率で拡大していますオラクル事業概況Copyright © 2023 Oracle and/or its affiliates. All rights reserved.100%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)OracleGoogle CloudSAPGrowthMicrosoftAWSSalesforceIBMWorkdayServiceNowCloud CAGR
Cloud Integrated IaaS & PaaS Solution Scorecard ComparisonGartnerによる評価も急上昇し、他社クラウドに近いスコアを記録Copyright © 2023 Oracle and/or its affiliates. All rights reserved.11R E Q U I R E D S O L U T I O N S S C O R E SO V E R A L L W E I G H T E D S C O R E S2019 2020 2021889493 AWS668478AZURE38ORACLE497571 GOOGLE3340 IBM78622019 2020 2021979898 AWS709285AZURE459074ORACLE538176GOOGLE3850 IBM
ガバメントクラウドに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.pdfhttps://www.oracle.com/jp/news/announcement/oracle-japan-selected-as-government-cloud-serviceprovider-2022-10-06/
1313 | © 2023 Exture Inc.主要製品のデータ分析機能 in 2023
1414 | © 2023 Exture Inc.汎用集計関数入力値の集合から単一の結果を返す関数の例 PostgreSQL MySQL HeatWave BigQuery RedShiftsum ○ ○ ○ ○ ○avg ○ ○ ○ ○ ○maxmin○ ○ ○ ○ ○count ○ ○ ○ ○ ○汎用集計関数の例 – GROUP BYを伴い、グループにつき1行の結果を返すSELECT date,avg(price) AS avg_priceFROM salesGROUP BY date;date avg_price2023-1-1 123452023-1-2 234562023-1-3 54321
1515 | © 2023 Exture Inc.汎用集計関数入力値の集合から単一の結果を返す関数の例 PostgreSQL MySQL HeatWave BigQuery RedShiftbit_and ○ ○ × ○ ○bit_or ○ ○ × ○ ○bit_xor ○ ○ × ○ ×bool_andlogical_and○ × × ○ ○bool_orlogical_or○ × × ○ ○汎用集計関数の例 – GROUP BYを伴い、グループにつき1行の結果を返すSELCT city, bit_or(likes)FROM userlikesGROUP BY city;bit_orの利用例:https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_BIT_OR.htmlcity bit_orLos Angeles 127San Francisco 255bit_or 2進127 01111111255 111111110 1 1 1 1 1 1 10 1 0 0 0 1 0 10 1 0 1 1 0 0 10 1 1 0 1 0 1 1演劇→スポーツ→映画→花火→
1616 | © 2023 Exture Inc.統計用関数統計集約によく用いられる関数、使い方は汎用集計関数と同様関数の例 PostgreSQL MySQL HeatWave BigQuery RedShiftstddev_pop ○ ○ ○ ○ ○stddev_samp ○ ○ ○ ○ ○var_pop ○ ○ ○ ○ ○var_samp ○ ○ ○ ○ ○この写真 の作成者 不明な作成者 は CC BY-NC のライセンスを許諾されています母標準偏差標本標準偏差母分散標本分散
1717 | © 2023 Exture Inc.統計用関数統計集約によく用いられる関数、引数の取り方が特徴的関数の例 PostgreSQL MySQL HeatWave BigQuery RedShiftcorr(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.htmlBigQuery/SnowflakeとBigQueryの書き換え https://cloud.google.com/bigquery/docs/migration/snowflake-sql?hl=ja他の製品では、Oracle、DB2、Snowflakeなどが対応
1818 | © 2023 Exture Inc.順序集合集約関数(逆分散関数)ORDER BYを伴う順序を持った入力を前提とする分析関数関数の例 PostgreSQL MySQL HeatWave BigQuery RedShiftmodetopapprox_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までの値ここで列名(ソートを指示)
1919 | © 2023 Exture Inc.ウィンドウ関数仮想的な行(ウィンドウ分割された範囲)に対して行う集計関数関数の例 PostgreSQL MySQL HeatWave BigQuery RedShiftrow_number() ○ ○ × ○ ○rank() ○ ○ × ○ ○dense_rank() ○ ○ × ○ ○percent_rank() ○ ○ × ○ ○cume_dist() ○ ○ × ○ ○ntile() ○ ○ × ○ ○lag() ○ ○ × ○ ○lead() ○ ○ × ○ ○first_value() ○ ○ × ○ ○last_value() ○ ○ × ○ ○nth_value(value,n) ○ ○ × ○ ○PostgreSQLではパラレルクエリできない(パラレルで結果セットを集めたあとに関数適用するような動作)※ウィンドウ定義には対応
2020 | © 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.91.91.91.91.91.91.91.91.91.91.94.54.54.54.54.54.54.54.54.54.54.54.64.64.64.64.64.64.64.64.64.64.6PARTITION 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 5PARTITION BY 地域 ORDER BY 日付ROWS 3日 PRECEDING1.72.01.71.31.01.32.32.33.04.04.33.74.03.75.05.05.34.78.37.05.03.33.02.02.33.04.3
2121 | © 2023 Exture Inc.格納アーキテクチャ、その他の高速化手段行指向/列指向、テーブルパーティショニングなど機能 PostgreSQL MySQL HeatWave BigQuery RedShift行指向/列指向 行 行 列 列 列ノード分散 × × ○ ○ ○テーブルパーティショニング○ ○ - - -機能 PostgreSQL MySQL HeatWave BigQuery RedShiftインメモリ × × ○○※BI Engine△結果セットの一部キャッシュパラレルクエリ並列処理○汎用集計関数のみ× ○ ○ ○大規模向け索引 BRIN × - - -インメモリ、パラレルクエリ、大規模用インデックスなど
2222 | © 2023 Exture Inc.PostgreSQL 15
2323 | © 2023 Exture Inc.ウィンドウ関数の性能向上純増(減)していくことが明らかな場合(row_numberやlank)に、WHERE条件を考慮して不要な集計を中断することで性能向上area sales_date pricetokyo 2023-01-01 1200osaka 2023-01-01 1000nagoya 2023-01-01 1900tokyo 2023-01-02 1000osaka 2023-01-02 1200nagoya 2023-01-02 1200tokyo 2023-01-03 1100osaka 2023-01-03 1700nagoya 2023-01-03 1600tokyo 2023-01-04 1300osaka 2023-01-04 1000nagoya 2023-01-04 1200tokyo 2023-01-05 1500osaka 2023-01-05 1100nagoya 2023-01-05 1000area sales_date price row_numbertokyo 2023-01-05 1500 1tokyo 2023-01-04 1300 2tokyo 2023-01-01 1200 3tokyo 2023-01-03 1100 4tokyo 2023-01-02 1000 5osaka 2023-01-03 1700 1osaka 2023-01-02 1200 2osaka 2023-01-05 1100 3osaka 2023-01-01 1000 4osaka 2023-01-04 1000 5nagoya 2023-01-01 1900 1nagoya 2023-01-03 1600 2nagoya 2023-01-02 1200 3nagoya 2023-01-04 1200 4nagoya 2023-01-05 1000 5SELECT * ,row_number()OVER (PARTITION BY areaORDER BY price DESC) rnFROM salesSELECT * FROM (SELECT * ,row_number()OVER (PARTITION BY areaORDER BY price DESC) rnFROM sales) sales_rnWHERE rn <= 3 ;area sales_date price row_numbertokyo 2023-01-05 1500 1tokyo 2023-01-04 1300 2tokyo 2023-01-01 1200 3tokyo 2023-01-03 1100 4tokyo 2023-01-02 1000 5osaka 2023-01-03 1700 1osaka 2023-01-02 1200 2osaka 2023-01-05 1100 3osaka 2023-01-01 1000 4osaka 2023-01-04 1000 5nagoya 2023-01-01 1900 1nagoya 2023-01-03 1600 2nagoya 2023-01-02 1200 3nagoya 2023-01-04 1200 4nagoya 2023-01-05 1000 5v14までは不要行を含めたすべて計算したうえで最後にWHERE条件を適用PostgreSQL15 ではウィンドウ関数処理時点で不要な結果を除去
2424 | © 2023 Exture Inc.MySQL + Heatwave
性能が良くてコストパフォーマンスが高いクラウドDWHサービスMySQLだけで超高速な分析処理を実現!✓ MySQLのバックエンドにHeatWaveノードが存在し、クエリーアクセラレーターとして動作✓ 列指向、インメモリでデータを持ち、ノード分散&チャンク分割による超並列処理を実現✓ 従来通り、OLTP系の処理も実行可能(データ変更時は自動的にHeatWaveノードに更新が反映される)圧倒的なパフォーマンスとコスト効率化!✓ TPC-HにおいてRedshift、Snowflake、BigQuery、 Azure Synapse Analyticsよりも安価な環境で高速処理を実現✓ ベンチマークテスト再現手順も公表中MySQL HeatWave Database ServiceCopyright © 2023 Oracle and/or its affiliates. All rights reserved.25OLAPアプリケーションOLTPアプリケーションOLTPEngineQueryAcceleratorM ySQLHeatWaveDatabaseServicehttps://www.oracle.com/mysql/heatwave/performance/HeatWaveノードMySQLノード
Copyright © 2023 Oracle and/or its affiliates. All rights reserved.26• ベクトル化処理を容易にする独自フォーマットでエンコーディング&圧縮後、チャンク分割• CPUコア間やノード間でのデータ移動を最小限に抑えられるように設計されている• Oracle LabsのProject RAPIDの成果を活用• ゾーンマップにより、圧縮したデータの中から必要なデータのみを展開• 各チャンク毎に、主キー(分散キー)の最小値/最大値を保持しているため、クエリー実行時に必要なチャンクを判断可能MySQL HeatWave での高速化の仕組みHeatWaveノードのデータ保持イメージ※分散キーは主キー以外にも変更可能
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 使用時の注意点
2828 | © 2023 Exture Inc.ご清聴ありがとうございました
2929 | © 2023 Exture Inc.(おまけ)PostgreSQLの環境構築初級者向け、Windows上で簡単にPostgreSQL on Linux環境を構築!SQL学習に最適!Windows標準機能でLinuxを動かす!PostgreSQL on WSL(AlmaLinux)の作り方intel 12世代コアならノートPCでもパラレルクエリの検証が捗るね!
オラクルクラウドのトライアルアカウントを作成し、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)画面キャプチャー付きで詳細に手順を解説しています!
3131 | © 2023 Exture Inc.データエンジニア積極採用中!
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] までご相談下さい!