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

このセクションでは、詳細ルールのクエリ構文を例を挙げて説明します。

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

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

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

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'

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

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

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

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

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

毎時レポート

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

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

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

日次レポート

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

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

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

週次レポート

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

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

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

月次レポート

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

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

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

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

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

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

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

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

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

特定の時間範囲についてのレポートを生成する場合は、次の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};

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

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

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

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

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

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

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

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

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

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

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

リスト レポート

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

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

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

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

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

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

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

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

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

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が大文字と小文字を区別するため、列名は小文字にする必要がある。
    - SERDEPROPERTIESにavro.schema.literalを指定する必要がある。

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

3. パーティションを追加する：
   テーブルを定義したら、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ファイルが存在する場合、クエリは失敗する可能性があります。

4. クエリを実行します。
   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])

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

カスタム関数は、テスト ルールまたはスケジュール設定されたレポートいずれかの実行中に、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};

カスタムのテーブル レポートの作成

10.6.1では、Hiveサーバでカスタムのテーブルを使用および作成できます。Reporting Engineはユーザ定義のテーブルでのクエリの実行をサポートし、1つのルール出力から新しいテーブルを作成できます。Warehouseルール ビルダUIでこの機能を有効にすると、ユーザは、Hiveサーバで使用可能なカスタム テーブルのリストを表示できます。

この機能を有効にするには、［Reporting Engine］＞［エクスプローラ］＞［ハイブ構成］に移動し、customTablesEnabledをTRUEに設定します。

標準ルールからのカスタム テーブルの作成

1つのSAWルールを含むレポートをスケジュール設定するには、Warehouse CTAS名を含む新しいテキスト入力を追加します。ユーザは、レポート内でルールの出力から作成されるカスタム テーブル名を指定できます。

注：この機能は、［スケジュール］ページでレポートに1つのSAWルールが含まれている場合にのみ使用可能です。それ以外の場合、このオプションは表示されません。

この機能を使用するプロセスを以下で説明します。

1. SAWでデータをフィルタするルールを作成します。

2. 作成したルールを含むレポートを作成します。

3. スケジュールを作成し、CTASテーブル名を入力します。
   

4. レポートを実行すると、Reporting Engineでは、以下のようにスケジュールの結果サマリが作成されます。
   

5. Reporting Engineの次のスキーマ更新または再起動時に、CTASテーブルがリストされます。