SQLによる時間の節約の物語

ビッグデータを扱うためのプラットフォームを提供している会社があります。 このプラットフォームを使用すると、遺伝データを保存および管理できます。 プラットフォームの本格的な操作には、2秒以内にランタイムで動的クエリを処理する機能が必要です。 しかし、この障壁を克服する方法は？ 既存のシステムを変換するために、SQLデータウェアハウスを使用することが決定されました。 詳細は猫の下を見てください！

状況

ヒトゲノムの約99.5％が共有されているため、DNAシーケンスでは通常、シーケンスサンプルと個々の既知の参照ゲノムを比較します。 比較結果は、拡張子が.vcfの生データファイルに保存されます。 ゲノムサンプルと参照ゲノムの違いのみが含まれています。 これには約100万行が含まれており、各行は遺伝子突然変異、つまり遺伝子インデックスと、特定のインデックスの参照ゲノムの値とは異なる値を表しています。

結果のVCFファイルは、プラットフォームで使用可能なさまざまなスクリプトを適用するために準備する正規化手順を受けます。 外部の遺伝子データベースからメタデータと注釈を追加する一連の手順です。 正規化プロセスでは、約100万行と約50列を含む巨大なファイルが作成されます。

問題

医師と研究者は、ダッシュボードを使用してランタイムで動的なクエリを処理し、患者のシーケンス結果を確認できる必要があります。 基本的なクエリは、いくつかの列の内容によるサンプルのフィルタリングをサポートしています。 高度なクエリでは、サンプルを参照データベース（6000万行）に関連付けることができます。 より詳細なシナリオは、単一のクエリのフレームワーク内で一連の結合操作を行うことを意味します。これにより、血統の遺伝分析が可能になり、外部参照データベースのリポジトリに接続できます。

クエリはダッシュボードを使用して生成されるため、2秒以内に結果を取得する必要がありました。 結果を取得するのにさらに時間がかかった場合、操作は失敗したと見なされました。 ランタイムでこのようなクエリを処理するには（たとえば、各サンプルに多数の行と列が含まれている場合）、大量のコンピューティングリソースが必要であり、多くの時間がかかります。

相互作用

この有望な企業との協力の目的は、多くのスケーラブルなストレージシステムアーキテクチャの有効性をさらに評価するために、クエリ処理のパフォーマンスを測定することでした。

ClouderaクラスタでのImpalaの起動

Impalaは 、大規模なデータセットの対話型調査分析の一部としてSQLクエリを処理する場合、および長い処理時間でバッチタスクを実行するためのHiveおよびMapReduceモジュールに推奨されます。

Impalaは、上記のタイプのリクエストをより効率的に処理するカラムストレージシステムも使用します。 これは、物理データがディスクに保存される方法が原因です。 SELECTクエリを実行して複数の列を投影すると、選択した列の対応するブロックのみがディスクから読み取られます。これは、同じ列の異なる行のデータが隣り合っているためです。 列ストレージの詳細については、 こちらを参照してください 。

HDInsightクラスターでのSparkの起動

Sparkは、そもそもHDFSでビッグデータを処理するために使用することが推奨されるテクノロジーです。 Sparkは、他のテクノロジー（Impalaなど）を使用するためにシステムを正常に準備します。 Sparkテクノロジーの詳細については、 こちらをご覧ください。

SQLデータウェアハウス

SQL Data Warehouseは、分散型の大規模並列処理（MPP）データベース管理システムです。 ノード間のデータの多様性と処理により、SQL Data Warehouseは柔軟なスケーラビリティを提供します。 SQL Data Warehouseの詳細については、 こちらを参照してください 。

お問い合わせ

サンプルシナリオをサポートするクエリの数に同意しました。 異なるストレージシステムに同じサンプルファイルを使用し、これらの各ファイルのリクエストを処理するのにかかる時間を確認しました。 明確にするために、次のクエリは「SQLのイメージで」記述されています。 それらは、独自の構文に従って各ストレージシステムによって処理されます。

参照データベーステーブルには、VCFファイルの同じ構造内に6,000万行と、各遺伝子の追加メタデータが含まれています。 正規化プロセスでは、このデータを使用して、サンプルの.vcfファイルの行に注釈を付けます。

次の表に、クエリの説明とパフォーマンス測定の結果を示します。

。	テクノロジー	インパラ	Spark in HD Insight	Clouderaのスパーク	SQLデータウェアハウス	SQLデータウェアハウス
。	クラスタータイプ	3 x D13	3 x D12	3 x DS13（各5 P30ドライブ）	400 DWU（データウェアハウスユニット）	1000 DWU（データウェアハウスユニット）
索引	スクリプト	と	と	と	と	と
1	サンプルから選択し、遺伝子の位置でソートし、最初の100エントリを取得します	2	5	2	1	1
2	参照データベースのテーブルから選択し、1つのフィールドでフィルタリングし、別のフィールドでソートし、最初の100レコードを取得します	11	96	38	2	6
3	サンプルを遺伝子識別子で参照データベースのテーブルにリンクし、遺伝子位置でソートし、最初の100エントリを取得します	775	347	168	15	6
4	サンプルを遺伝子IDで参照データベーステーブルにリンクし、最初の100エントリを取得します	211	275	121	1	1
5	サンプルを遺伝子識別子で参照データベースのテーブルにリンクし、1つのフィールドでフィルタリングし、遺伝子の位置でソートし、最初の100エントリを取得します	13	111	61	1	1
6	サンプルを遺伝子識別子ごとに参照データベースのテーブルにリンクし、1つのフィールドでグループ化し、別のフィールドで並べ替え、最初の100レコードを取得します	37	41	23	5	2
7	参照データベースのテーブルから選択し、1つのフィールドでグループ化し、別のフィールドでソートし、最初の100レコードを取得します	12	25	20	15	7
8	参照データベースのテーブルに存在しないサンプルファイルから行を選択し、最初の100エントリを取得します	37	367	432	5	2
9	遺伝子識別子による4つのサンプルのリンク、1つのフィールドによるフィルター、別のフィールドによるソート、最初の100エントリの取得	7	16	20	3	2
10	6,000万件のレコードを含むテーブルに100万件のレコードを追加します	7	35	33	4	3
11	新しい空のテーブルに100万件のレコードを追加します	4,5	35	33	6	21
12	新しい空のテーブルに6,000万件のレコードを追加します	140	500	280	214	124

おわりに

得られた結果を考慮すると、上記の代替オプションは処理時間（2秒）の要件を満たしていません。

以下の手順に従うことをお勧めします。

1. 短期的には、リレーショナルDBMS（SQL Server、MySql、Postgresなど）を使用して、ダッシュボードから送信されるほとんどのリアルタイムクエリを処理します。これらのクエリは2秒以内に処理する必要があります。 実行計画が全表スキャンに対応していないことを確認してください。
2. 小さなHadoopクラスター（Spark、Impala、HDFS、およびParquetを含む）をデプロイします。 ダッシュボードから送信される個々の複雑なWebリクエストをオフロードするために使用します。リレーショナルDBMSを必要とせずにImpalaに1秒未満で応答します。 しばらくしてから、Impalaを使用して最も複雑でリソースを集中的に使用するタスクを実行し、リレーショナルDBMSの外部で処理されるクエリの数を増やします。
3. ImpalaとSparkを使用して、追加のカスタムクエリを使用し、データの準備を最適化します。 SparkとImpalaで生データを前処理し、適切なデータスライスをリレーショナルDBMSにロードします。 Impalaテクノロジーを使用して、Webアプリケーションで新しい特別なリクエストを作成し、インターフェイスに適切な変更（たとえば、「待機...」、「ダウンロード...」）を追加して、応答時間が長くなってもユーザーの作業に影響しないようにします。