2GISユーザー統計：ETLルールとデータ前処理

ユーザーの好みを理解し、2GISサービスのパフォーマンスを評価するために、匿名化された情報を収集します。 私たちの顧客は、プロダクトマネージャー、コマースおよびマーケティングの代表者、パートナー、および個人のダッシュボードで統計を表示する広告主です。



ユーザー統計には21〜27個のパラメーターがあります。 これには、都市、見出し、会社などが含まれます。



多数のイベントパラメーターは、合計インジケーター、平均値、偏差、上位10、-100、-1000など、多数のレポートにつながります。 このシナリオでは、明日どのような情報が役立つかを予測することは困難です。 そして、この必要が生じた場合、できるだけ早くデータを提供する必要があります。



おなじみですか？

数字で

2GISの2600万人のユーザーが1日あたり約2億のイベントを形成しています。 これらは約2400 rpで、受信、処理、保存する必要があります。 得られたデータは、任意のアドホックおよび分析クエリ向けに最適化する必要があります。



課題は次のとおりです。

-データの準備（ETL）。 これは最も重要で時間のかかるステップです。

-集計を計算します（前処理）。



最初に、最初の質問を解決します。

前と同じように

むかしむかし、ビジネスインテリジェンスシステムはまったく異なって見えました。 少数の都市での作業には最適でしたが、新しい市場に参入したとき、このシステムは面倒で不便でした。

1. データはパーティション化されていないテーブルに保存され、標準の「挿入」および「更新」で更新されました。 データ配列全体に適用される操作。
2. 新しいデータクエリでは、次のインデックスでテーブルが大きくなりすぎます。
   
   a）新しいデータの受信時に再構築する必要がありました。
   
   b）より多くのスペースを占有しました。
3. 数百万のテーブルの結合操作はほとんど不可能でした。
4. 管理操作（バックアップ、圧縮、インデックスの再構築）には長い時間がかかりました。
5. 多次元データベースを処理するには、データ配列全体を毎日処理する必要がありました。 変更されていないものも。
6. 多次元データベースの分析クエリにも多くの時間がかかりました。

したがって、データを異なる方法で処理することにしました。

新しいアプローチ

パーティショニング+ファイル+ファイルグループ

パーティションとは、テーブルを単一の論理エンティティとして表現することです。一方、その部分（セクション）は、異なるファイルやファイルグループに物理的に存在できます。



テーブルは、パーティション関数を使用してパーティション分割されます。 パーティション化列の値​​の範囲境界を定義します。 パーティション関数に基づいて、パーティションスキームが構築されます。 パーティション関数の選択が重要です データ取得のためのクエリの利益は、クエリでパーティション列が使用されている場合のみです。 この場合、パーティション化スキームは、必要なデータを検索する場所を示します。







多くの場合、時刻（日、月、年）がパーティション関数として選択されます。 これは、データの履歴性によるものです。古いデータは変更されません。 これは、それらが配置されているセクションをファイルグループに配置し、古い期間に必要な場合にのみアクセスできることを意味します。 リソースを節約するために、遅いドライブに置くことさえできます。



パーティション関数として月を選択しました、なぜなら ほとんどのクエリは毎月作成されます。







ただし、いくつかの問題があります。

1. 貼り付けはまだ大きなテーブルで行われています。 インデックスがある場合、新しいデータを挿入するとインデックスが再構築され、インデックスの数が増えると必然的に新しいデータを挿入する操作が遅くなります。
2. 多次元データベースのパーティション分割には、最大2000万レコードのセクションを使用することをお勧めします。 私たちのセクションは、桁違いに大きくなりました。 これにより、前処理段階でパフォーマンスのギャップが発生しました。 セクションサイズの制御されない成長は、セクショニングの考え全体を無効にする可能性があります。

2番目の質問を解決するために、各期間のセクション数を増やしました。 月とパーティション関数として今月のセクションの特定のシリアル番号を使用すると、次のようになります。







最初の問題はより困難でした。 対処しましたが、ソリューションを評価するには、Columnstoreインデックスについて知る必要があります。

列ストアインデックス

公平に言えば、Columnstoreインデックスは古典的な意味でのインデックスではないことを言う価値があります。 動作が異なります。



バージョン2012以降、MS SQL ServerはColumnstore-データを列に格納することをサポートします。 行のデータの通常の保存とは異なり、そこにある情報は一度に1列ずつグループ化されて保存されます。



この形式にはいくつかの利点があります。



-要求された列のみが読み取られます。 一部の列はまったく記憶されない場合があります。

-列は非常に圧縮されています。 これにより、読み取るバイトが減少します。

-キーストアの概念は、Columnstoreインデックスには存在しません。 インデックス内のキー列の数の制限（16）は、Columnstoreインデックスには適用されません。 私たちの場合、これは重要です。なぜなら、 パラメーター（行ストア列）の数が16を大きく超えています。

-列ストアインデックスはパーティションテーブルで機能します。 パーティションテーブルの列ストアは、ベーステーブルとセクションで整列する必要があります。 したがって、パーティション化列がそのインデックスの列の1つである場合にのみ、パーティション化されたテーブルに対して非クラスター化Columnstoreインデックスを作成できます。 これは問題ではありません。なぜなら パーティション化は時間通りに行われます。







「素晴らしい！」と思いました。 「それがあなたの必要なものです。」 ただし、Columnstoreインデックスの1つの機能が問題であることが判明しました。SQLServer 2012では、Columnstoreインデックスを持つテーブルを更新できません。 操作「挿入」、「更新」、「削除」、および「マージ」は許可されていません。



各データ挿入操作でインデックスを削除および再構築するオプションは適用されないことが判明しました。 そのため、セクションを切り替えることで問題を解決しました。

セクション切り替え

テーブルに戻りましょう。 現在、彼女はColumnstoreインデックスを使用しています。







次のプロパティを使用して別のテーブルを作成します。



-すべて同じ列とデータ型。

-同じセクション、各月に1セクションのみ。

-Columnstoreインデックスなし。







新しいデータをそこにアップロードします。そこからセクションを安定したテーブルに転送します。



行こう



ステップ1.切り替えが必要なセクションを決定します。 2,000万レコードのセクションが必要です。 データをロードすると、特定の反復で、セクションの1つがいっぱいであることがわかります。







ステップ2.安定したテーブルで、切り替え可能なデータ用のセクションを作成します。 セクションは、対応するファイルグループ-2013年10月に作成する必要があります。9月のファイルグループにある既存の空のセクション（14）は、私たちには適していません。 データをロードするためのセクション（15）を作成します。 さらに、1つの追加セクション（16）を作成します。これは、次回「伝播」するためです。 常に伝播するには、最後に空のセクションが1つ必要です。







ステップ3.宛先セクションをステージングテーブルに切り替えます。







手順4.テーブルからデータを入力して、中間テーブルにデータをロードします。 その後、ステージングテーブルにColumnstoreインデックスを作成できます。 2000万件のレコードの場合、これは非常に迅速に行われます。







手順5.セクションをステージングテーブルから安定したテーブルに切り替えます。



今：



-列とデータ型は同じです。

-このセクションに対応するファイルグループ内の新しいセクション。

-新しいセクションで既にColumnstoreインデックスを作成しており、安定したテーブルのインデックスに完全に対応しています。







ステップ6.完全にきれいにするために、不要になった安定テーブルの空の（14）セクションを閉じます。







結論-ダウンロード用のテーブルは再びデータを受信する準備ができています。







安定したテーブルには、1つのセクションが追加されました（15）。 最後のセクション（16）は、手順2と同じ伝播の準備ができています。







一般に、タスクは達成されました。 ここから、データの前処理（準備）に進むことができます。

多次元データベース処理（OLAP）

お知らせします。任意のフィルタリング、グループ化、並べ替えを使用して、任意の列セットのオンラインデータ分析を提供する必要があります。 これを行うために、パーティション化もサポートする多次元OLAPデータベースを使用することにしました。



テーブルと同じセクションを作成します。 メインの「安定」テーブルについてのみ、リレーショナルデータベースに従って1-in-1セクションをカットします。

また、ロードテーブルには、1つの共通セクションで十分です。



これで、各セクションのすべてのパラメーターのデカルト積の計算単位（合計）が得られました。 ユーザークエリ中に、多次元データベースはクエリに対応するセクションを読み取り、それらの間で集計を合計します。

古い期間の圧縮

そのため、上記のすべてを行いましたが、同じ月がかなりの数のセクションに広がっていることがわかりました。 同時に、各月のセクション数は、ユーザー数、対象都市、プラットフォームなどとともに増加します。



これにより、毎月レポートを準備する時間が長くなります。 はい、そしてちょうど-追加のディスク領域を占有します。



1か月のセクションの内容を分析し、すべての重要なフィールドを集約することで圧縮できるという結論に達しました。 時間をかけて詳細に説明するだけで、最新の情報が得られます。 古い期間の圧縮は、履歴データが受信されなくなり、圧縮するセクションの数が最大の場合に特に効果的です。



私たちがそれをした方法：



ステップ1. 1か月のすべてのセクションを定義します。







ステップ2.ロードテーブルから残り物を追加します。







ステップ3.関連するすべてのフィールドを集計します。 操作は約1か月に1回実行されるため、ここではリソースを寄付することができます。



この段階では、得られたセクションのサイズが理想的な2,000万に対応していないことが判明する可能性があります。 少なくとも、各月の最後のセクションが不完全であることは、パフォーマンスにまったく影響しません。







ステップ4.最後に、多次元データベースを再描画することを忘れないでください。 完全な処理には約5〜6時間かかります。 これは、毎月の運用に完全に受け入れられます。

まとめ

大きなテーブルのパーティション分割は必須です

パーティショニングにより、ファイル、ファイルグループ、ディスクに負荷を分散できます。 さらに、セクショニングを使用しても、サイズが重要です。



将来的にそれらを使用して多次元データベースを構築するために、2000万のセクションを形成するという目標を設定しました。 いずれの場合も、セクションのサイズは解決する問題によって決定する必要があります。



パーティショニング機能も重要です。

列ストアインデックスが決定します！

アドホックリクエストはすべて網羅しています。 データサンプリングの新しいタスクが表示されたときに、インデックスを作成/再構築する必要はありません。



MS SQL Server 2012のColumnstoreインデックスの実装は、元のRowdataテーブルを実際に複製し、同じものを作成しますが、列ストレージを使用します。



それでも、インデックスが占有するデータの量は、タスクごとに特別なインデックスのセットを作成した場合よりもはるかに少なくなります。



挿入制約は、セクションの切り替えによってバイパスされます。

数字の結果

たとえば、テーブルの1つ：3,940,403,086行。 285 887,039 MB

リクエスト実行時間	パーティションテーブル	パーティションテーブル+列ストア	多次元OLAPデータベース
モスクワのiPhoneバージョンからの5月5日の通話数	8分 3秒	7秒	6秒
タイプAイベントの物理サイズ	285.9 GB	285.9 GB + 0.7 GBインデックス	67 GB

他にどんなオプションがありますか？

MSではありません

歴史的に、会社の企業開発はすべてマイクロソフトのソリューションに基づいています。 私たちは同じ方法で、原則として他のオプションを考慮しませんでした。 幸いなことに、MS SQL Serverは、すべての処理レベルで大きなテーブルの操作をサポートしています。 これらには以下が含まれます。



-リレーショナルデータベース（データウェアハウジング）;

-Sql Server Integration Services（ETL）;

-SQL Server Analysis Services（OLAP）。

MS SQL Server 2014

SQL Server 2014では、Columnstore機能がクラスター化されるように拡張されました 。 新しく受信したデータは、従来の（Rowstore）データストレージであるDeltastoreに分類され、蓄積されるとメインのColumnstoreに切り替わります。

パーティションのサイズを明確に決める必要がない場合、SQL Server 2014はユーザー統計の収集、処理、分析に最適なソリューションです。