FutureTechNight#16_クラウド時代のDWH

Public Copyright © 2021 by Future Architect, Inc. FutureTechNight #16
Cloud DWH Technology Innovation Group 2021年9月3日

Copyright © 2021 by Future Architect, Inc. Public - 2
- アジェンダ 1. 登壇者自己紹介(杉江・中山) 2. 会社紹介(中山) 3. クラウドDWHアーキテクチャ概要(中山) 4. データベースインフラストラクチャ(杉江) 5. データベースソフトウェア(中山) 6. 検証結果(杉江) 7. 今後に向けて(杉江)

- 自己紹介 • 名前中山祐一（ナカヤマユウイチ） • 社歴 2012年12月中途入社（2社目） • 所属 Technology Innovation Group • 趣味スポーツ観戦、ツーリング、マインドスポーツ（ポーカー、麻雀など）

- • 名前杉江　伸祐 • 社歴 2006年キャリア入社 • 所属 Technology Innovation Group 　　データベース技術を中心に社内全般的に活動しています。

- 経営とITをデザインしてお客様の企業価値向上に貢献

- 自社ビジネス顧客の価値の最大化流通製造物流金融 TIG SAIG SIG CSIG DXチームメディアユニット … 関連会社

- Technology Innovation Groupについて主なミッション「最先端、かつ先進的なテクノロジーのプロフェッショナル集団」「プロジェクト品質と生産性の向上」「自社サービス事業の立ち上げ」 TIG CSIG SAIG AI特化セキュリティ特化 Security IoT ・・・ BigData AI Cloud

- データウェアハウスアーキテクチャの振り返り（2010年頃～） ▪ 大量のリソース（CPU/Memory/Storage)を必要とするデータウェアハウスのアーキテクチャは 2つの方式が存在。2010年ごろからコスト面のメリットが大きいためMPP方式アーキテクチャをベースとしたSWが多く採用されてきた。※トランザクションDB(例えばOracle/PostgreSQL/MySQLなど)は引き続きSMP型が基本です。 System-bus Storage Memory (Shard Memory) CPU cache CPU cache CPU cache CPU cache Network(High-Speed) Storage Server CPU, Memory Storage Server CPU, Memory Storage Server CPU, Memory Storage Server CPU, Memory (1)SMP (2)MPP 1台のサーバ内で物理メモリを共有する並列コンピューティング方式性能アップ：サーバに搭載する CPUを増やす容量アップ：サーバに搭載するストレージを増やす複数台のサーバの組み合わせによる並列コンピューティング方式性能アップ：サーバを増設する容量アップ：サーバを増設する SMP(Symmetric Multi-Processing) MPP(Massively Parallel Processing)

- DWH-RDBアップデート検証の背景 ▪ MPP型のDWH製品はオンプレミスでも従来より採用されているが、近年クラウド技術を利用したクラウド MPP型ともよべるものが出てきた。 ▪ クラウドMPP型は「コンピュートとストレージが分離されたアーキテクチャ」でクラウドの持つElasticity(弾力性、伸縮性)を持つため従来にはない利用が可能。 (1)従来のMPP型 (2)クラウドMPP型ストレージが見えないと計算できないので常にコンピュートノードは全台オンラインストレージが分離されているため計算するときのみサーバを起動 AQUA(RA3インスタンス)のリリース (2021/4)によりクラウドMPP型にも対応

- Storage Storage 「コンピュートとストレージが分離されたアーキテクチャ」とは ▪ コンピュートとストレージが分離されたことで、クラウドの持つElasticity(弾力性、伸縮性)をDB に対しても適用できるようになった。サーバ Storage Compute Storage サーバ Storage サーバ Storage サーバ Storage (1)従来のMPP型 (2)クラウドMPP型ユーザーの設定した項目をもとにデータは分散配置例：サーバー#1：店CD 001～010 　　　サーバー#2：店CD 011～020 データ配置制御は不要サーバを増設して対応（すべてのサーバのスペックアップという手もある） DB性能アップ DB容量アップ課金モデルデータ配置サーバを増設して対応（すべてのサーバのスペックアップという手もある）固定課金モデル（利用サーバの台数だけ課金）一部サーバの停止を行うとDBは利用できない。コンピュートサーバーを追加、スケールアップで対応（処理が終了したら停止可能） Compute コンピュートサーバーを追加、スケールアップで対応（処理が終了したら停止可能）従量課金モデル（コンピュートサーバーを利用しただけ課金）クエリーを実施しなければ課金はされない Compute 性能・容量アップはサーバ追加(スケールアウト) 処理のニーズに応じて Computeサーバの追加/削除容量に制限はない

- ピックアップ対象 ▪ 対象としている製品は以下の3製品でいずれもストレージとコンピュートは分離した構成 Snowflake BigQuery Synapse Analytics

- ① Snowflake(1) ▪ クラウドインフラ（コンピュートインスタンス、ストレージ）を利用して構築されたDBaaS（データベースサービス）提供をしている。 ▪ 処理実行サーバ(Warehouse)は用途に応じて同時にいくつでもオンデマンドで立ち上げることが可能で従量制のメリットが活かせる。 Micro Partition Storage Cache Result Cache Warehouse WH WH Cloud Service 認証認可、メタ情報、オプティマイザ、トランザクション管理、セキュリティ、Web UI Availability zone Storage … Client Client S3にデータは保存 DBインスタンスは EC2を利用して実現 (On-Demand) クエリーエンジン DBデータ DB管理

- ① Snowflake(2) ▪ AWS/GCP/Azureの任意のパブリッククラウドで提供されているものを選択可能 ▪ 自社システムのあるパブリッククラウドのサービスを選択するのがデータ転送の点で有利 https://docs.snowﬂake.com/ja/user-guide/intro-regions.html

- ① Snowflake(3) ▪ Snowflakeの課金は主に処理を実行したサーバ(Warehouse)のインスタンスタイプと処理時間（秒単位) によって決定される。Warehouseを使った分だけが課金されるモデルとなっている。 ※ストレージサイズ、転送量の課金も存在します。 • 課金はCreditと呼ばれる課金単位で計算される • エディションに応じて Creditの単価が決まる。 • Creditは最低単位を60秒として、それ以上は秒単位で発生 • クラスタの起動時間で課金（自動起動、自動停止設定で制御可能） • Storageはfixed price https://www.snowﬂake.com/pricing/?lang=ja&utm_cta=website-nav-platform-platform-pricing https://docs.snowﬂake.com/en/user-guide/warehouses-overview.html

- ②GCP BigQuery (1) ▪ GCP上に構築されたデータ分析のための基盤 ▪ BigQueryとは分散ストレージ上にデータを配置し、GCPのもつクエリーエンジン(Dremel)が一体化されて提供されたものを意味する。 DBデータ DB管理クエリーエンジン

- ② GCP BigQuery(2) ▪ 課金は主にクエリー実行でスキャンされたデータ量で決定する。クエリー処理を実行した分だけが課金されるモデルとなっている。（コンピュートインスタンスサイズという概念は存在しない） ※ストレージサイズ、転送量の課金も存在します。 https://cloud.google.com/bigquery/pricing/?hl=ja

- ③Azure Synapse Analytics(1) ▪ 従来からあるSQL Serverをもとにした SQL Data WarehouseとSpark(Hadoop)の2つの基盤を統合して Azureとしての統合したデータ分析プラットフォームとして提供したもの ▪ DWHとしてはSQL Data Warehouseのことを指す。

- ③ Azure Synapse Analytics(2) ▪ コンピュートとストレージが分離した構成でコンピュートノードの起動停止と要件に応じたサイズ変更が可能。 ▪ データは60個固定で分散する構成となる。

- Snowflake BigQuery Azure Synapse Analytics サービス提供クラウド AWS/GCP/Azure GCP Azure 構成 DBデータストレージ分散ストレージ上に配置分散ストレージ上に配置 SSD上のブロックストレージ（推測）コンピュートノードクエリー単位でコンピュートインスタンスサイズを決めることができるコンピュート性能を制御する概念は存在しない事前にコンピュートインスタンスサイズを決める DBエンジン Snowflake Dremel SQL Server Datawarehouse (SQL Serverがベース) 課金クエリー課金体系クエリー単位の処理時間で課金クエリー単位のデータスキャンサイズで課金コンピュートインスタンスの起動時間で課金その他データサイズ、転送サイズ、 DBエンジンエディションデータサイズ、転送サイズデータサイズ、転送サイズ 1.データベースアーキテクチャ概要のまとめ ▪ SnowflakeとBig Queryのアーキテクチャに類似点が多く、Elasticity(弾力性、伸縮性)はより優れている。 ▪ 今回はSnowflakeとBig Queryを中心に解説していく

- 技術調査について ▪ Googleの技術情報は比較的オープンであるが、Azure,AWSはあまり情報が公開されていない。しかしながら、インフラストラクチャを考えるうえで技術的要素は類似していると考えるのが妥当。 ▪ インフラストラクチャ技術についてはGoogleの情報をもとに確認をしていく。 ▪ いずれにせよ詳細な情報がないので推測ベースであること。さらに最新の情報をもとにしているわけではないので注意が必要

- ①ストレージ：データベースのストレージ ▪ 一般的なRDBのデータは物理的にはページやデータブロックと呼ばれる管理単位で保持されている。 ▪ 行単位に更新が発生することを前提としており、IOはすべてページ単位で実行される。 ▪ ページサイズは主に8KB～32KBで管理されており、データアクセスはランダム（連続したアクセスを前提としない）で行われることを前提としている。 http://naka.wankuma.com/site/column/server/00007.htm https://xtech.nikkei.com/it/article/COLUMN/20060113/227234/ ページサイズ単位でランダムに IOが発生する

- ①ストレージ：データベースのストレージ ▪ ページ単位のランダムアクセスを行うことを前提としているため、DBサーバではブロックデバイスと呼ばれる方法でディスクを利用している。ブロックデバイスではアクセス管理のためブロックと呼ばれる単位に切り分けて一意のIDが付与することで、ランダムなRead/Writeアクセスを高速に実行することが可能。この一意のIDはmountしたサーバで管理されているため、他のサーバからはアクセスできない。（このあたりだいぶ簡略化して説明してます） ▪ AWSの場合、EBS(Elastic Block Store)を利用する。 AWSのデータベースサービスRDSのインスタンス仕様 AWS EBS データベースソフトウェアファイルシステム物理レイヤ論理レイヤ (AWSインフラストラクチャ) アプリケーションレイヤ (AWSサービス) DBページ単位のアクセス HDD/SSD ブロックデバイス (disk mount)

- ①ストレージ：クラウド型データベースのストレージ ▪ クラウド型DWHでは、ブロックストレージではなく分散ファイルシステムで管理する。 ▪ 分散ファイルシステムとは「複数のサーバがネットワークを経由してファイルにアクセスすることを可能にするファイルシステム」のこと。見かけ上一つのファイルだが複数に分割されて複数のサーバに配置される。 ▪ AWSのS3(Simple Storage System)、GCPのColossus(旧GFS)、HDFS(Hadoop)が相当する。これらは概念的な点では共通していて大きな違いはない。 http://research.google.com/archive/gfs.html https://phoenixnap.com/kb/apache-hadoop-architecture-explained

- ①ストレージ：クラウド型データベースのストレージ ▪ 分散ファイルシステムの特徴比較的大きなサイズを多数保存することが得意。RDBのストレージとは異なり、Read処理はシーケンシャル（一定量を連続して）アクセス、Write処理もシーケンシャル(一定量を連続して)アクセスでデータを保存する。既存ファイルの更新は追記する方式で保存する RDBのブロックストレージはIOPS(秒間何回ブロックアクセスが発生するか)を重視しているのに対して、分散ファイルシステムはスループット(秒間の転送量)を重視する設計 Read処理はファイル全てをReadする必要はなく、byte単位のアクセスが可能なAPIが用意されている https://dev.classmethod.jp/articles/aws_s3_getobject_error/

- ①ストレージ： GCPのストレージシステム「Colossus」(1) ▪ 2003年にGFS(Google File System)が公表(論文:The Google File System)された。現在はその後継のColossusがGCPでのストレージシステムで利用されている。 ▪ 利用者のファイル認識しているファイルは実態としてChunkと呼ばれる単位に分割して複数のサーバに分割して保存する構成。 ▪ ColossusはBig Query(Datawarehouse),Spanner(RDB)、BigTable(KVS)で利用されている Chunk Server Chunk Server Chunk Server Chunk Server Chunk Server Chunk Server Chunk Server Chunk Server Chunk Server Chunk Server 【無数のストレージサーバ群で構成】ファイルを1つのサーバに保持しておくと、障害発生時に使用が不可能となってしまう。 https://research.google/pubs/pub51/

- ①ストレージ： GCPのストレージシステム「Colossus」(2) ▪ 可用性（サーバダウンやディスク故障でも継続して動作する）を可能とするため一つのファイルをChunk(64KB？詳細不明)に分割して複数台のサーバで保持。この保持方法はErasure Encodingが採用されている。 File ABCDの4つに分割して、 3か所（3台のサーバ)に保持 Replication Erasure Encoding https://techblog.yahoo.co.jp/infrastructure/hdfs_erasure_coding/

- ②ネットワーク：高速ネットワーク ▪ 「ストレージ層では冗長化のためにデータは複数個所で保存する」つまり、高速なネットワークが不可欠なアーキテクチャを前提としている。 https://research.google/pubs/pub51/ Client Chunk Server Chunk Server Chunk Server • 別のChunk ServerにはChain上にデータを転送 • 転送はpipeline的に(受信後すぐに別 Chunk Serverに転送される） • チェックサムも同時に作成【Write】【Read】 Chunk Checksum • ChunkをReadする際にchecksum でデータ検証 • Chunkは部分Readであっても全量をReadする。 • Checksumは32bitで微小なのでIO への影響はない check 全二重Traffic in out in out in

- ②ネットワーク： GCPのネットワーク「Jupiter」 ▪ 2015年にGCPでのDC内ネットワークについての論文が公開された。(Jupiter Rising: A Decade of Clos Topologies and Centralized Control in Google’s Datacenter Network) SDNを利用したネットワークの経路にボトルネックを生じさせない「複数の経路を利用した負荷分散」構成 ※SDN: Software Defined Network 普及期に入った40GbEを利用して最大構成で1.3Pbps（1つのサーバ間では10Gbps)が可能な広帯域ネットワーク構成 https://research.google/pubs/pub43837/

- ②ネットワーク： AWS & GCP ▪ AWSはインスタンスタイプによりサーバのネットワーク帯域が決定する。GCPのように情報はオープンになっていないが広帯域なサーバ(EC2インスタンス)を利用可能 ▪ AWSの特殊なサーバを除くとGCPのネットワークレイヤの違いはあまりないのでは。 https://pages.awscloud.com/rs/112-TZM-766/images/B1-06.pdf https://cloud.google.com/compute/docs/machine-types

- ③コンピュート：Snowflake & BigQuery ▪ 2つのアーキテクチャでコンセプトが大きく異なるのが「コンピュート」の部分 Snowflakeはクエリ実行時に明示的にインスタンスサイズ(サーバスペック)を指定する。 Big Queryは処理を細切れにして、巨大なGoogleリソースを動的に使用する。 Warehouse サーバインスタンスｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅ slot ユーザーが明示的にインスタンスサイズを指定いくつでも起動可能リソースを動的に割り当て（固定化できない）左の図では３つ使用されている広帯域 Network 広帯域 Network Storage Network Compute

- ③コンピュート：データセンターリソースととコンテナ ▪ GCP(Googleサービスも含めて)ではすべてのソフトウェアがコンテナで実行されている。 ▪ コンテナはデータセンターの無数のサーバを一つのリソースとみなして稼働しているともいえる。 ◆ 無数のサーバ(数万台)を一つのリソースとしてコンテナ（アプリケーション）を稼働させることが可能。ｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅｺﾝﾃﾅ複数のサーバでクラスタ化させてオーケストレーションツールで管理する ◆ 1か月間における8個のクラスタ（1クラスタで1万台程度)のCPUとメモリ使用率の平均値（処理の優先度での積み上げグラフ）平準化されていて突発的な要求にも対応が可能になっている（無駄な余力は少なくなるよう常に計算している） https://research.google/pubs/pub49065/

- ③コンピュート：Big Queryのクエリー処理とコンテナ関係 ▪ 処理（クエリー）を細分化してキューイング ▪ キューイングされた処理を「いい感じに」クエリーエンジン(Dremelと呼称)がコンテナに割り当てて並列で処理を実施（並列実行できる場合とできない場合が当然あります） ▪ Dremelコンテナ間のデータの受け渡しは分散ファイルシステム(Colossus)もしくはメモリファイルシステムを経由して行われる。 shard shard shard ｺﾝﾃﾅ scheduler (1)クエリー処理をshardと呼ばれる単位に細分化してキューイング (2)SchedulerがDremelコンテナを割り当てキュー (3)Dremelの処理が実行結果はファイルシステムに保存ファイルファイル Memory Memory

- ③コンピュート： Big Queryのコンピュート管理 ▪ コンピューティング能力(CPU/Memory)は「slot」と呼ばれる抽象的な単位で管理されている。重いクエリーはスロット消費が多く、軽いクエリーはスロット消費が少なくなり、オプティマイザにより決められる。 ▪ スロットは通常の利用ではアカウント当たり、2000スロット割り当てられているがムラがある。 2000スロットは常時確実に利用できるのではなく、非常にムラがある。同じクエリーでも時間帯によって割り当てられるスロット数にもムラがある。(実行時間もムラがある) ▪ GCPのデータセンターの中でサーバリソースを固定化させないというコンセプトからくるBig Queryの大きな特徴 https://cloud.google.com/bigquery/docs/slot s 2000スロット要求しているが1000スロットまでが利用可能なためキューイングされる。クエリーの同時実行状況によって1クエリーに同時に割り当てられるスロットの数が変化する。

- ③コンピュート：Big Queryのコンピュートリソースのムラ ▪ エンタープライズの利用ではムラがあるのは非常に使いにくいのが実情。 ▪ 2020年5月にSlot割り当てを固定化する設定が可能になった。

- 2.データベースインフラストラクチャのまとめ ▪ クラウド型データウェアハウスはデータセンター内の高速なネットワークと無限のストレージと無限のコンピュートリソースからなるインフラストラクチャーを利用して構成されている。 ▪ 専用のインフラストラクチャを用意しているわけではなく、既存のクラウド技術を利用している。そのため規模の経済が働きコストモデルに優れる。

- データベースソフトウェアの構成要素 ▪ DBアーキテクチャは3つの要素から構成される 1. DBデータ：分散ファイルシステム（クラウドストレージ）上のファイルに保持 2. DBメタ情報：DB管理情報を保持。クエリー実行時のデータフィルタにも利用される 3. クエリーエンジン：クエリー実行本体 ▪ SnowflakeとBig Queryのソフトウェア実装上の概念は非常に類似している（次ページ以降で解説）クエリーエンジン DBデータ DBメタ情報 DBエンジン DBデータ DBメタ情報

- ①DBデータ：データ保存の流れ ▪ DBデータを保持するまでの概念はBigQueryとSnowflakeは類似している。 ▪ 並列処理が行われて、並列処理(スレッド)毎にデータファイルが分散ファイルシステムに作成される。 ▪ Update/deleteは必ず追記処理（新しいファイルが作成される） ▪ データは圧縮して保持される。データ Meta情報データデータデータデータ分散ファイルシステム (S3@AWS/Cloud Storage@GCP) Load File 分散ファイルシステム (S3@AWS/Cloud Storage@GCP) • S3などのストレージにあるファイルを取り込む(Data Load) • 一つのファイルを分割して並列でデータを取り込む（分割単位はファイルサイズを見て判断する） • 図ではファイルロードだが、insert-select でも動作は同じ並列実行 • 物理的なデータフォーマットに従って変換する。 • 一定のファイルサイズ(数10MB単位)毎に分割される。 • 一つの並列処理(スレッド)が作成するのは1ファイル • 並列スレッドにてメモリに展開してファイル圧縮を行い実体として保存する。 • ファイル作成時にMeta情報を保存 DBデータは数10MB程度のファイルに分割されて保持される

- ①DBデータ：データフォーマット「列or行」という概念 ▪ データフォーマットは「行」「列」という二つの考え方がある。DWHでは列指向が一般的に使われるがデータ処理特性に応じて優劣あるためどちらがいいとは一概には言えない。 ▪ BigQueryは「列指向」、Snowflakeは「その他」の方式となる。 (1)行指向 (2-1)列指向 • データは行形式で格納される • 行ロックが可能なためトランザクション型データベースで使用されている。 • データは列ごとに別々に格納される • DWHでは参照列が限定されることが多いので有利な形式（ただし完全でもない） • 列の値のバリエーションを集約することでデータ圧縮が可能 (2-2)行・列選択可能 • テーブル単位で行指向、列指向を選択可能 • 処理の特徴に応じて選択する (3)その他

- ①DBデータ：データフォーマット(BigQuery) ▪ Capacitorという、オープンソースの列指向フォーマットのparquetフォーマットに近い形式で保持（正確にはparquetの前身となった形式） ▪ 列ごとにファイルは作成される。ファイルの作成と同時にメタ情報も同時に保存される。（メタ情報については後述） https://www.slideshare.net/yoheiazekatsu/parquet-207178617 メタデータデータファイルとしては複数に分割列単位で複数のファイルが作成メタデータ

- ①DBデータ：データフォーマット(Snowflake) ▪ 列指向と行指向のハイブリッドなデータフォーマットで保持する。 ▪ 行ごとにデータは取り込むが、データファイル内では列ごとにデータ固めて保持する。（圧縮もされる） ▪ ファイル作成後にメタ情報も登録される。（メタ情報については後述） Cus_id Name age 6282 John 42 2547 Anne 21 3249 Susan 65 8349 Jeremiah 42 1228 Tim 36 物理的なデータファイルのフォーマットテーブルイメージ

- ②DBメタデータ：データのメタ情報とスキャン ▪ テーブル定義、権限情報などのデータベースのディクショナリ情報とは別に、保存されたデータ（ファイル）の中身の情報をメタ情報として保持している。 ▪ 内容としてはMin/Max, distinct(値のバリエーション)が保存される。【メタ情報の例】 file0002に保存されている1番目の列(field1)はmin=26, max=50である。つまり、field1=30のデータはfile002にしか存在しない file0001 file0002 file0002 Select count(field1) where field1 between 30 and 40;   skip skip scan デーtが存在するファイルのみをスキャン可能

- ③データアクセス：データ更新とデータファイルの関係 ▪ データ登録・更新・削除は必ずファイル単位で実施され、データは追記されて保存される。 ▪ メタ情報としてテーブル操作（トランザクション）毎のファイル番号を管理して保持する。 ▪ 古いファイルは即座に削除されずに時間指定のクエリやテーブルリカバリに使用される（設定により一定時間後に削除） file001 ファイル Tranid=0121 2020/10/01 10:01 file002 file003 file001 Tranid=0121 2020/10/01 10:01 file002 file003 file004 Tranid=0231 2020/10/01 14:20 メタ情報 Update table A Set col1=100 Where itemcd=101 ②SQL実行(file02に存在するレコードをupdate) file001 Tranid=0121 2020/10/01 10:01 file002 file003 file004 Tranid=0231 2020/10/01 14:20 Tranid=0238 2020/10/01 14:20 delete from table A Where itemcd=005 ③SQL実行(file01に存在するレコードをdelete) file005 • データロード時にfile001-003の3ファイル作成された • メタ情報の更新データロードデータファイルメタ情報データファイルメタ情報データファイル • File002をもとにupdate後のデータfile004を作成 • メタ情報の更新 • File001をもとにdelete後のデータfile005を作成 • メタ情報の更新 ①あるテーブルのデータをロード

- 1.検証の考え方  データ分析クエリー性能は「データ種(FROM)」「抽出条件(WHERE)」「集計項目数(SELECT)」「分析粒度(GROUP BY)」の4項目で決まる。（実際はその後に、二次加工処理が行われる場合が多い）   データベースアーキテクチャの特徴を明確にするため、検証は2つの手法で行う。   1. 抽出処理：検索条件に基づき、結合結果を抽出する処理 2. 集約処理：抽出後のデータをグループ関数により集計する処理データ種分析粒度集計項目数出力方法データ種、抽出条件、集計項目数、分析粒度で分析対象となるデータ件数が決まる。検索クエリーは2つのステップで実行されているとみなすことができるため、本検証においては、抽出処理と集約処理の２段階に分けて考える。 × × ］＝テーブル定義・テーブル件数（データ集約） × 抽出条件加工方法＝組合せ、並べ替え × FROM SELECT WHERE GROUP BY （データ絞込み）検証①抽出処理検証②集約処理

- 2.各諸元値  今回用いたデータについて「表1.諸元値一覧」に記載  カレンダー5年分 1,826日  商品数 10,000件  店舗数 1,000件  売り上げ明細 219,000,000件   店舗数1000 ｘ3年分1095日ｘ売り上げ品目 200  在庫明細 5,475,000,000件  店舗数1000 ｘ3年分1095日ｘ在庫品目 5,000  検証はファクト表１：ディメンション表３のFutureでよくあるテーブル構成で実施  抽出条件についてはディメンション表を指定  日付キー  商品キー  店舗キー  売上個数  売上額  ・・・  商品キー  SKUコード  商品コード  ブランドコード  日付キー  年月日  曜日区分  月  店舗キー  店舗コード  エリアコード  販路コード  ディメンション表  商品マスタ  ディメンション表  カレンダーマスタ  ディメンション表  　店舗マスタ  ファクト表  　売上・在庫明細  諸元値一覧

- ①データ量と処理時間　2.集約関数(3製品)  データの集計が行われるのが一般的なため、売上明細に対して週・月・年、商品単位、店舗単位での組み合わせで集約を行う。   集約度は日付(週、月、年) 、商品(1、1/10、1/100) 、店舗(1、1/10、1/100)の組み合わせでGROUP BYにより絞り込みを行う。     売上明細テーブル 2016年日付キー商品キー店舗キー売上個数売上額・・・商品キー SKUコード商品コードブランドコード日付キー年月日年月年週曜日区分店舗キー店舗コードエリアコード販路コードディメンション表商品マスタディメンション表カレンダーマスタディメンション表　店舗マスタファクト表　売上明細 2017年 2018年 ①スキャン範囲  1年分で固定  集約なし 1/7  1/30  1/100  ・・・＜結果出力行数のイメージ＞   ②日付、商品、店舗の組み合わせで合計値を算出（GROUP BY）   約7320万行約1046万行約244万行約73万行 SELECT … SUM(…), SUM(…), SUM(…), … FROM T_URIAGE T INNER JOIN M_CALENDAR INNER JOIN IWATEST.M_SHOHIN INNER JOIN IWATEST.M_TENPO WHERE 　M_CALENDAR.MD_NEN = 2016 GROUP BY M_CALENDAR.HIZUKE_CD, M_SHOHIN.SKU_CD, M_TENPO.TENPO_CD 試験シナリオパターン低←　　集約度　　→高 ③SELECTした結果を集計してINSERT

- ①データ量と処理時間　2.集約関数(3製品)  データの集計が行われるのが一般的なため、売上明細に対して週・月・年、商品単位、店舗単位での組み合わせで集約を行う。   集約度は日付(週、月、年) 、商品(1、1/10、1/100) 、店舗(1、1/10、1/100)の組み合わせでGROUP BYにより絞り込みを行う。     出力行数が著しく多いケース(DB書き込み時間が多い)を除き、集約度が低く集計結果レコード数が多い場合を除き、処理時間はほぼ一定となる。   Synapseのみ、クラスタリングキーと処理方式により若干ばらつきがある。   処理時間は一定を維持する  集約の仕方とクラスタリングキーと分散方式によりばらつきがあるが   処理時間はある程度一定を維持する  Snowflake  BigQuery  Synapse  低←　　集約度　　→高低←　　集約度　　→高低←　　集約度　　→高集約度が低い場合は集計結果レコード数が多いため、テーブルへの書き出し時間が長い  集約度と処理時間集約度と処理時間集約度と処理時間処理時間は一定を維持する 

- ①データ量と処理時間　3. 内部処理時間(3製品)  3製品それぞれにおいて、1年分のデータに対して集約関数（SUM＋GROUP BY）を実施した時の各処理時間の内訳は以下の通り。   全体的に集約関数の処理時間は少なく、テーブルスキャンの時間が大半である。   内部処理時間の内訳としては、今回のように7300万件を対象とするなどデータ量が多い場合は、集計処理を行ったとしても大半がテーブルスキャンの時間となる。  処理時間の大半は  テーブルスキャンとなる   処理時間の大半は  テーブルスキャンとなる   処理の大半は  RoundRobinMove  (データスキャンと移動)となる   Snowflake  BigQuery  Synapse  処理時間内訳処理時間内訳処理時間内訳

- AWS Redshiftの最新状況 ▪ AWS Redshiftも優れたSWで実績も十分あり、各種PJでも活用はされている。唯一、「コンピュートとストレージ」が一体化していることによる課題があった性能は満足しているが、容量が不足しているからコンピュート追加（サーバの追加）が必要コンピュートのスペックを変更するのに時間がかかり24-365運用を考えると難しい。 ▪ この課題は認識されていて、「コンピュートとストレージ」を分離させる進化が行われている。今後の動きに注目。 Amazon Redshift

- Snowflakeのマルチクラウド戦略 ▪ Snowflakeはマルチクラウド戦略をとっておりパブリッククラウドを制約としない展開を行っている。 ▪ 現状、GCPでは展開が進んでいないのがわかる。これはSnowflakeとBig Queryのアーキテクチャが類似していてニーズが少ないためかもしれない。

- Big Query Omni ▪ 「AWSもGCPもクラウド技術は類似している。Big Queryの実装では専用HWを使っていない」つまり、Big QueryもAWSでも動くことを意味する。 ▪ 実際、AWSで動くBig Queryのアルファ版がリリースされた。優れたSWを武器に自社クラウドに閉じない展開が続くかもしれない。 https://cloud.google.com/blog/ja/products/data-analytics/introducing-bigquery-omni

- 参考情報 ▪ 一般的情報に加えて… 【特に参考になるサイト】 • グーグルのクラウドを支えるテクノロジ https://www.school.ctc-g.co.jp/columns/nakai2/ • BigQuery under the hood https://cloud.google.com/blog/products/gcp/bigquery-under-the-hood • Snowflakeドキュメント https://docs.snowflake.com/ja/ 【特に参考になる書籍】

FutureTechNight#16_クラウド時代のDWH

FutureTechNight#16_クラウド時代のDWH

Other Decks in Technology

Featured

Transcript