レポート:Warehouse DBの詳細ルール

Document created by RSA Information Design and Development on Feb 17, 2017
Version 1Show Document
  • View in full screen mode
  

このトピックでは、Warehouseデータ ソースのルールの例を示します。Warehouse DBルールの定義には、HIVEクエリーを使用します。次のモードを使用して、Warehouseデータ ソースのルールをシンプルなものから高度なものまで定義できます。

  • デフォルト モード 
  • エキスパート モード

詳細なルールは、DROP、CREATEなどの句を使って作成された複雑なHIVEクエリーを使用して定義できます。シンプルなルールの場合とは異なり、常に結果がテーブルに挿入されます。高度なHIVEクエリー言語の詳細については、「HIVE言語マニュアル」を参照してください。

以下の例では、エキスパート モードでの詳細なルールについて説明します。

  • 毎時、日次、週次、月次のレポート
  • ロケーション(データ配置)に基づくテーブル パーティション構成のレポート
  • unique_idに基づくログとセッションの結合のレポート
  • リスト レポート
  • レポートのパラメータ機能
  • 複数のロケーションを持つパーティション ベースのテーブル
  • カスタム関数を使用したパーティションの自動化(10.5.1 以降) 

詳細なルールの一般的な構文

次の図は、詳細なクエリーを定義する方法を示しています。

ExpertRule-TimeVar-input_hourly_103SP3.jpg

次の構文は、詳細なクエリーの例です。

DROP Table IF EXISTS sessions21022014;

CREATE External TABLE sessions21022014

ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.avro.AvroSerDe’

STORED AS INPUTFORMAT

'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'

LOCATION '/RSA/rsasoc/v1/sessions/data/2013/12/2’

TBLPROPERTIES('avro.schema.literal'='
{

"type":"record";

"name":"nextgen";

"fields":
[
{"name":"time",  "type":["long", "null"], "default":"null"},

{"name":"threat_category",  "type":["string", "null"], "default":"null"},

{"name":"ip_src",  "type":["string", "null"], "default":"null"},

{"name":"device_class",  "type":["string", "null"], "default":"null"}
]
'};

set mapred.input.dir.recursive=true;
set hive.mapred.supports.subdirectories=true;

select  from_unixtime(time), threat_category, ip.src from  time_variable where threat_category is not NULL and time >= ${report_starttime} and time <= ${report_endtime};

注:エキスパート モードのWarehouseルールでは、Reporting Engineは<hyphen> <hyphen>で始まる行をコメントとして扱います。
例:
set mapred.input.dir.recursive=true;
-- This is an Expert comment
set hive.mapred.supports.subdirectories=true;

詳細なクエリーの一般的な構文について次に説明します。

  1. 外部テーブルをドロップおよび作成し、行をフォーマットする:
    まず、テーブルがすでに存在する場合は、そのテーブルをドロップし、外部テーブルsessions21022014を作成します。
    DROP TABLE IF EXISTS sessions21022014
    CREATE EXTERNAL TABLE sessions21022014

    注: 外部テーブルを作成する必要があるのは、別のテーブルを使用している場合だけです。たとえば、sessions21022014とは別のテーブルを使用している場合は、そのテーブルをドロップして外部テーブルを作成する必要があります。

    次に、行形式をAvro.SerDeインタフェースとして指定し、レコードの処理方法についてHIVEに指示します。Avro.SerDeを使用すると、Avroデータの読み取りまたは書き込みをHIVEテーブルとして実行し、入力形式および出力形式として格納することができます。
    ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.Avro.SerDe'
    STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
    OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'

  1. HDFSのロケーションを指定する:
    次に、HDFSのロケーション「/RSA/rsasoc/v1/sessions/data/2013/12/2」を指定する必要があります。HIVEステートメントの実行前に、このロケーションからデータのクエリーが実行されます。ロケーションのパラメーターは、指定された日付入力に応じてフェッチされるデータを指定します。これは変数パラメータであるため、入力された日付に応じて値をフェッチできます。
  2. テーブル スキーマを定義する:
    次に、特定のデータ タイプを使用し、デフォルト値を「null」として列を定義することにより、テーブル スキーマを定義します。
    TBLPROPERTIES('avro.schema.literal'='
    {"type":"record";
    "name":"nextgen";
    "fields":
    [
    {"name":"ip_src",  "type":["string", "null"], "default":"null"}
    ]
    '};
  3. サブディレクトリを含むディレクトリからデータをインポートする:
    次に、HIVEですべてのサブディレクトリを繰り返しスキャンして、すべてのサブディレクトリからすべてのデータをフェッチできるようにする必要があります。
    set mapred.input.dir.recursive=true;
    set hive.mapred.supports.subdirectories=true;
  4. HIVEテーブルからデータをフェッチする:
    前述のすべてのステートメントを実行したら、HIVEクエリーのselect句を使用してデータベースのクエリーを実行し、HIVEテーブルからデータをフェッチすることができます。

毎時、日次、週次、月次のレポート

これらのルールの例では、2013年12月2日のさまざまなレポートを作成できます(後続の図を参照)。LOCATIONステートメントの日付変数は、作成可能な毎時、日次、週次、月次の各レポートに応じて変更することができます。

毎時レポート

このルールの例では、2013年12月2日の毎時レポートを作成できます。LOCATIONステートメントを変更することで毎時レポートを作成できます。

LOCATION '/RSA/rsasoc/v1/sessions/data/2013/12/2' -日付入力(2013/12/2)は年/月/日を示しています。このLOCATIONステートメントを使用して、2013年12月2日のデータ全体が取得されます。 

Expert_Rule_hourly_report_103SP3.png

このクエリーの結果セットは、毎時レポートになります。

日次レポート

このルールの例では、2013年12月の日次レポートを作成できます。LOCATIONステートメントを変更することで日次レポートを作成できます。

LOCATION '/RSA/rsasoc/v1/sessions/data/2013/12' -日付入力(2013/12)は年/月を示しています。このLOCATIONステートメントを使用して、2013年12月のデータ全体が取得されます。

Expert_Rule_daily_report_103SP3.png

このクエリーの結果セットは、日次レポートになります。

週次レポート

このルールの例では、2013年12月の週次レポートを作成できます。LOCATIONステートメントを変更することで週次レポートを作成できます。

LOCATION '/RSA/rsasoc/v1/sessions/data/2013/12' -日付入力(2013/12)は年/月を示しています。このLOCATIONステートメントを使用して、2013年12月のデータ全体が取得されます。

Expert_Rule_weekly_report_103SP3.png

このクエリーの結果セットは、週次レポートになります。

月次レポート

このルールの例では、2013年の月次レポートを作成できます。LOCATIONステートメントを変更することで月次レポートを作成できます。

LOCATION '/RSA/rsasoc/v1/sessions/data/2013' -日付入力(2013)は年を示しています。このLOCATIONステートメントを使用して、2013年のデータ全体が取得されます。

Expert_Rule_monthly_report_103SP3.png

このクエリーの結果セットは、月次レポートになります。

LOCATION定義の詳細については、「詳細なルールの一般的な構文」セクションの「HDFSのロケーションを指定する」を参照してください。

詳細なルールの結果セットを表示するには、次のステップを順番に実行する必要があります。

  1. 詳細なルールの定義
  2. 詳細なルールのレポートへの追加
  3. レポートのスケジュール設定
  4. レポート スケジュールの表示

次の図は、詳細なルールを定義する方法を示しています。

ExpertRule-TimeVar-input_103SP3.jpg

次の図は、詳細なルールをレポートに追加する方法を示しています(たとえば、AllEventCategories)。

add_expert_rule_to_report.png

次の図は、日次レポートをスケジュール設定する方法を示しています。

Expert_Rule_daily_report_103SP3.png

特定の時間範囲についてのレポートを生成する場合は、次の2つの変数を使用して、クエリーで時間範囲を定義する必要があります。

${report_starttime} - The starting time of the range in seconds.
${report_endtime} - The ending time of the range in seconds.

例:SELECT from_unixtime(time), threat_category, ip.src FROM time_variable WHERE threat_category is not NULL AND time >= ${report_starttime} AND time <= ${report_endtime};

次の図は、日次レポートをスケジュール設定した結果セットを示しています。

TimeVar-output_103SP3.JPG

ロケーションに基づくテーブル パーティションのレポート

このルールの例では、ロケーションに基づくテーブル パーティションを作成できます。各テーブルには、データの格納方法を指定する1つ以上のパーティション キーを構成できます。たとえば、STRINGのcountry_dstやtype STRINGのip_srcなどです。パーティション キーのそれぞれの一意の値は、テーブルのパーティションを定義します。

ここで取り上げた例では、HIVEクエリーを実行し、sessions05032014テーブルから宛先の国とIPアドレスをフェッチして、結果セットをこれらのフィールド別にグループ化します。

このルールでは、Warehouse内でavroデータ ファイルに対して作成されたテーブル、フォーマットされた行、ロケーション(ディレクトリ パス)に関する情報を提供し、HIVEクエリーに従った結果セットを返してクエリーから結果セットが返されたことを示します。これらのステートメントの詳細については、「詳細なルールの一般的な構文」セクションを参照してください。

ExpertRule-GroupByLocation_103SP3.jpg

次の図は、ロケーションに基づくテーブル パーティションのレポートを作成した結果セットを示しています。

TablePartition_103SP3.jpg

unique_idに基づくログとセッションの結合のレポート

このルールの例では、ログおよびセッション テーブルを結合してunique_id、ソースと宛先のIPアドレス、unique_idに基づくパケットIDをフェッチするルールを作成できます。

ここで取り上げた例では、HIVEクエリーを実行し、「unique_id」フィールドに基づいて結合を実行することによってsessions_tableとlogs_tableの両方から特定のフィールドをフェッチします。

このルールでは、Warehouse内でavroデータ ファイルに対して作成されたテーブル、フォーマットされた行、ロケーション(ディレクトリ パス)に関する情報を提供し、HIVEクエリーに従った結果セットを返してクエリーから結果セットが返されたことを示します。これらのステートメントの詳細については、「詳細なルールの一般的な構文」セクションを参照してください。

ExpertRule - Join_103SP3.png

次の図は、unique_idに基づいてログおよびセッション テーブルを結合した結果セットを示しています。

Join-output_103SP3.jpg

リスト レポート

このルールの例では、lists_testテーブルからソースと宛先のIPアドレスおよびデバイス タイプをフェッチするリスト レポートを作成できます。デバイス タイプはnullではなく、ソースのIPアドレスは適切なイベント リストからフェッチされます。

このルールでは、Warehouse内でavroデータ ファイルに対して作成されたテーブル、フォーマットされた行、ロケーション(ディレクトリ パス)に関する情報を提供し、HIVEクエリーに従った結果セットを返してクエリーから結果セットが返されたことを示します。これらのステートメントの詳細については、「詳細なルールの一般的な構文」セクションを参照してください。

ExpertRule-Lists_103SP3.JPG

次の図は、リスト レポートを実行した結果セットを示しています。

LISTS-output_103SP3.jpg

レポートのパラメータ機能

このルールの例では、指定された実行時変数${EnterIPDestination}に基づいてruntime_variableテーブルからソースと宛先のIPアドレスおよびデバイス タイプをフェッチするルールを作成できます。実行時に、宛先ip_dstのIPアドレスの値を入力するよう求められます。入力された値に基づいて、結果セットが表示されます。

このルールでは、Warehouse内でavroデータ ファイルに対して作成されたテーブル、フォーマットされた行、ロケーション(ディレクトリ パス)に関する情報を提供し、HIVEクエリーに従った結果セットを返してクエリーから結果セットが返されたことを示します。これらのステートメントの詳細については、「詳細なルールの一般的な構文」セクションを参照してください。

ExpertRule-RunTimeVar_103SP3.JPG

次の図は、レポートのパラメータ機能を実行した結果セットを示しています。

Run_Time_Variable-output_103SP3.jpg

複数のロケーションを持つパーティション ベースのテーブル

以下に、複数のロケーションを持つパーティション ベースのテーブルの例を示します。

set mapred.input.dir.recursive=true;
set hive.mapred.supports.subdirectories=true;
DROP TABLE IF EXISTS AVRO_COUNT;
CREATE EXTERNAL TABLE AVRO_COUNT
PARTITIONED BY (partition_id int)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
WITH SERDEPROPERTIES (
'avro.schema.literal'='{
"name": "my_record", "type": "record",
"fields": [
{"name":"sessionid", "type":["null", "long"], "default" : null},
{"name":"time", "type":["null", "long"], "default" : null}
]}'
)
STORED AS
INPUTFORMAT
'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat';
ALTER TABLE AVRO_COUNT ADD PARTITION(partition_id=0) LOCATION '/rsasoc/v1/sessions/data/2015/07/22/8';
ALTER TABLE AVRO_COUNT ADD PARTITION(partition_id=1) LOCATION '/rsasoc/v1/sessions/data/2015/07/22/9';
ALTER TABLE AVRO_COUNT ADD PARTITION(partition_id=2) LOCATION '/rsasoc/v1/sessions/data/2015/07/22/10/';
ALTER TABLE AVRO_COUNT ADD PARTITION(partition_id=3) LOCATION '/rsasoc/v1/sessions/data/2015/07/22/11/';
ALTER TABLE AVRO_COUNT ADD PARTITION(partition_id=4) LOCATION '/rsasoc/v1/sessions/data/2015/07/22/12/';
SELECT COUNT(*) as TOTAL FROM AVRO_COUNT WHERE time >= ${report_starttime} AND time
<= ${report_endtime};

複数のロケーションを持つパーティション ベースのテーブルについて次に説明します。

  1. HIVEですべてのサブ ディレクトリを繰り返しスキャンして、サブ ディレクトリからすべてのデータを読み取ることができるようにします。
    set mapred.input.dir.recursive=true;
    set hive.mapred.supports.subdirectories=true;

  2. 外部テーブルをドロップおよび作成し、行をフォーマットする:
    DROP TABLE IF EXISTS AVRO_COUNT;
    CREATE EXTERNAL TABLE AVRO_COUNT
    PARTITIONED BY (partition_id int)
    ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
    WITH SERDEPROPERTIES (
    'avro.schema.literal'='{
    "name": "my_record", "type": "record",
    "fields": [
    {"name":"sessionid", "type":["null", "long"], "default" : null},
    {"name":"time", "type":["null", "long"], "default" : null}
    ]}'
    )
    STORED AS
    INPUTFORMAT
    'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
    OUTPUTFORMAT
    'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat';

注: 外部テーブルを作成する必要があるのは、別のテーブルを使用している場合だけです。たとえば、AVRO_COUNTとは別のテーブルを使用している場合、そのテーブルをドロップして外部テーブルを作成する必要があります。

注:テーブルを作成するときに留意すべきポイント:
    - 「非外部」テーブルをドロップすることでデータが削除される。 
    - テーブルはpartition_idと呼ばれる単一の列にパーティション化され、Reporting Engineの標準列となる。
    - AVROファイルに指定された列が含まれていない場合があるため、列のデフォルト値はNullである。
    - HIVEは大文字と小文字を区別しないが、AVROが大文字と小文字を区別するため、列名は小文字にする必要がある。
    - SERDEPROPERTIESavro.schema.literalを指定する必要がある。

ルール構文の詳細については、「Apache HIVE」を参照してください。 

  1. パーティションを追加する:
    テーブルを定義したら、HIVEステートメントを実行する前にデータをクエリーする必要があるHDFSのロケーションを指定する必要があります。ロケーション パラメーターには、指定された日付に応じて、フェッチされるデータを指定します。データはHDFSの複数のロケーションまたはディレクトリに分散されます。各ロケーションに対して、パーティション列に一意の値を割り当てたパーティションを追加する必要があります。場所は、HDFS内の任意のディレクトリにすることができます。
    ALTER TABLE AVRO_COUNT ADD PARTITION(partition_id=0) LOCATION '/rsasoc/v1/sessions/data/2015/07/22/8';
    ALTER TABLE AVRO_COUNT ADD PARTITION(partition_id=1) LOCATION '/rsasoc/v1/sessions/data/2015/07/22/9';
    ALTER TABLE AVRO_COUNT ADD PARTITION(partition_id=2) LOCATION '/rsasoc/v1/sessions/data/2015/07/22/10/';
    ALTER TABLE AVRO_COUNT ADD PARTITION(partition_id=3) LOCATION '/rsasoc/v1/sessions/data/2015/07/22/11/';
    ALTER TABLE AVRO_COUNT ADD PARTITION(partition_id=4) LOCATION '/rsasoc/v1/sessions/data/2015/07/22/12/';

注:HIVEは、これらのロケーションにある各ファイルをAVROとして読み取ります。これらのロケーションのいずれかで使用可能な非AVROファイルが存在する場合、クエリーは失敗する可能性があります。

  1. クエリーを実行する
    SELECT COUNT(*) as TOTAL FROM AVRO_COUNT WHERE time >= ${report_starttime} AND time
    <= ${report_endtime};

    テーブルが作成されると、特定のクエリーを実行してデータをフィルタリングできます。たとえば、テーブルの作成後、次の例で示すようにデータをフィルタリングできます。 
    特定のソースIPアドレスを持つセッション:
    SELECT * FROM AVRO_COUNT WHERE time >= ${report_starttime} AND time <= ${report_endtime}  AND ip_src = '127.0.0.1';
    ユーザーの宛先に基づいてグループ化します。
    SELECT * FROM AVRO_COUNT WHERE time >= ${report_starttime} AND time <= ${report_endtime}  GROUP BY usr_dst;

カスタム関数を使用したパーティションの自動化

10.5.1では、カスタム関数を使用して、エキスパート モードでのユーザー定義テーブルへのパーティションの追加を自動化できます。

一般的な構文

RE WH CUSTOM ADDPARTITIONS(table, namespace, rollup, [starttime, endtime])

次の表では、カスタム関数の構文について説明します。

                      
#名前説明
1パーティションを追加する必要があるテーブルの名前。
2namespaceセッションまたはログを指定できます。
3rollup

この値によって、パーティションに含まれるディレクトリ パスのレベルが決まります。SQL Serverシステムのシステム レベル情報をすべて記録する
値には時間、日、分を指定できます。Warehouse Connectorが「日」ロールアップとして構成されている場合、この値を「時間」と設定すると、生成される結果は「ゼロ」になります。各パーティションの数とロケーションは、ルールの実行に使用される時間範囲とロールアップ値に基づいて決まります。

4(オプション)starttime、endtime

ルールに記載されている時間範囲以外の特定の時間範囲用のパーティションを生成するには、[エポック秒数]に開始時刻と終了時刻を指定する必要があります。

注:開始時刻および終了時刻に関しては、式はサポートされていません。

カスタム関数は、テスト ルールまたはスケジュール設定されたレポートいずれかの実行中に、Reporting Engineによってルールが実行されるときに呼び出されます。エキスパート ルールの実行時にReporting Engineは、関数宣言を識別するたびに、必要な
引数を抽出し、n個のADD PARTITION HiveQLステートメントを挿入して、それらをHive Server上で実行します。

ロケーションとディレクトリ構造は、ルールで渡された引数と、Reporting EngineのHiveデータソース構成によって
決定されます。パーティションの数は、指定されたロールアップと、ルールの実行中に使用される時間範囲によって決まります。たとえば、ロールアップが「時間」で、時間範囲が「過去2日間」の場合、48時間のために48個のパーティションが作成されます。
ロールアップが「日」と指定されている場合は、Reporting Engineが2個のパーティションを作成します(1日に1個)。

パーティションのクエリーは、Reporting EngineのHive構成属性「AlterTableTemplate」で設定されている構文テンプレートによって生成されます。

注:デフォルトでは、この関数によってテーブルへのパーティションの追加が開始されます(パーティションidは0からN-1)。そのため、テーブルをパーティションidという名前の整数1文字の列でパーティション化する必要があります。

次に、カスタム関数を使用して自動化されたパーティションの例を示します。
set mapred.input.dir.recursive=true;
set hive.mapred.supports.subdirectories=true;
DROP TABLE IF EXISTS AVRO_COUNT;

CREATE EXTERNAL TABLE AVRO_COUNT
PARTITIONED BY (partition_id int)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
WITH SERDEPROPERTIES (
'avro.schema.literal'='{ 
  "name": "my_record", "type": "record", 
  "fields": [ 
    {"name":"sessionid", "type":["null", "long"], "default" : null} 
   ,{"name":"time", "type":[ "null" , "long"], "default" : null} 
   ,{"name":"unique_id", "type":["null", "string"], "default" : null} 
   ]}'
)
STORED AS 
INPUTFORMAT 
'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
OUTPUTFORMAT 
'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat';

RE_WH_CUSTOM_ADDPARTITIONS(AVRO_COUNT, 'sessions', 'DAY');
SELECT COUNT(*) as TotalSessions FROM AVRO_COUNT
WHERE time >= ${report_starttime} AND time <= ${report_endtime};

 

You are here
Table of Contents > Reportsモジュールの参考資料 > ルールに関する参考情報 > zzWhDBRulDefMod > Warehouse DBの詳細ルール

Attachments

    Outcomes