Reporting : Règles avancées liées à une base de données Warehouse

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

Cette rubrique fournit des exemples de règles de sources Warehouse Data. Vous pouvez définir des règles liées à une base de données Warehouse à l'aide des requêtes HIVE. Vous pouvez définir des règles simples et avancées pour la source Warehouse Data à l'aide des modes suivants :

  • Mode par défaut 
  • Mode Expert

Des règles avancées sont définies à l'aide de requêtes HIVE complexes, via les clauses DROP, CREATE, etc. Contrairement aux règles simples, nous insérons toujours les résultats dans une table. Pour plus d’informations sur le langage de requête avancée HIVE, consultez le manuel du langage HIVE.

Les exemples suivants illustrent des règles avancées en mode expert :

  • Rapport horaire, quotidien, hebdomadaire et mensuel
  • Partition de table basée sur le rapport d'emplacement
  • Joindre les logs et sessions en fonction du rapport unique_id
  • Rapport de liste
  • Rapport paramétré
  • Table partitionnée comportant différents emplacements
  • Partitionnement automatisé avec la fonction personnalisée (à partir de la version 10.5.1) 

Syntaxe générale d'une règle avancée

La figure suivante montre comment définir une requête avancée.

ExpertRule-TimeVar-input_hourly_103SP3.jpg

Voici un exemple de syntaxe pour une requête avancée :

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};

Remarque : Reporting Engine traite une ligne commençant par <tiret> <tiret> comme un commentaire dans la règle Warehouse Expert.
Par exemple,
set mapred.input.dir.recursive=true;
-- This is an Expert comment
set hive.mapred.supports.subdirectories=true;

La syntaxe générale d'une requête avancée est expliquée ci-dessous :

  1. Déplacer et créer une table externe, puis formater la ligne :
    Tout d'abord, nous déplaçons la table si elle existe déjà, et nous créons une table externe sessions21022014
    DROP TABLE IF EXISTS sessions21022014
    CREATE EXTERNAL TABLE sessions21022014

    Remarque :  Vous ne devez créer de table externe que si vous utilisez une autre table. Par exemple, si vous utilisez une autre table que sessions21022014, vous devez supprimer la table et créer une table externe.

    Spécifiez ensuite le format de ligne comme interface Avro.SerDe pour indiquer à HIVE comment l'enregistrement doit être traité. Avro.SerDe vous permet de lire ou écrire des données Avro sous forme de tables HIVE et de les stocker sous forme de format d’entrée et de format de sortie.
    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. Spécifiez l'emplacement HDFS :
    Ensuite, vous devez spécifier l'emplacement HDFS '/RSA/rsasoc/v1/sessions/data/2013/12/2' à partir duquel les données sont interrogées avant d'exécuter les instructions HIVE. Le paramètre d'emplacement indique les données à extraire en fonction de l'entrée de date indiquée. Il s'agit d'un paramètre variable. Vous pouvez extraire des valeurs en fonction de la date saisie.
  2. Définir le schéma de la table :
    Troisièmement, vous définissez le schéma de la table en définissant les colonnes avec un type de données spécifique et la valeur par défaut est 'null'.
    TBLPROPERTIES('avro.schema.literal'='
    {"type":"record";
    "name":"nextgen";
    "fields":
    [
    {"name":"ip_src",  "type":["string", "null"], "default":"null"}
    ]
    '};
  3. Importer les données à partir du répertoire contenant les sous-répertoires :
    Ensuite, vous devez activer HIVE afin qu'il analyse de manière récursive tous les sous-répertoires et qu'il extraie toutes les données à partir de tous les sous-répertoires.
    set mapred.input.dir.recursive=true;
    set hive.mapred.supports.subdirectories=true;
  4. Extraire les données à partir de la table HIVE :
    Lorsque vous avez exécuté toutes les instructions ci-dessus, vous pouvez envoyer une requête à la base de données avec la clause select  dans une requête HIVE pour extraire les données de la table HIVE.

Rapport horaire, quotidien, hebdomadaire et mensuel

Dans ces exemples de règles, vous pouvez créer différents rapports pour le 2 décembre 2013 (comme dans la figure ci-dessous). La variable de date dans l'instruction LOCATION peut être modifiée, selon laquelle vous pouvez créer un rapport horaire, quotidien, hebdomadaire et mensuel.

Rapport horaire

Dans cet exemple de règle, vous pouvez créer un rapport horaire pour le 2 décembre 2013. L'instruction LOCATION peut être modifiée pour générer un rapport horaire.

LOCATION '/RSA/rsasoc/v1/sessions/data/2013/12/2' - la date saisie (2013/12/2) indique l'année/le mois/le jour. Toutes les données du 2 décembre 2013 sont récupérées à l'aide de l'instruction « location ». 

Expert_Rule_hourly_report_103SP3.png

L'ensemble des résultats de cette requête sera présenté dans un rapport horaire.

Rapport quotidien

Dans cet exemple de règle, vous pouvez créer un rapport quotidien pour décembre 2013. L'instruction LOCATION peut être modifiée pour générer un rapport quotidien.

LOCATION '/RSA/rsasoc/v1/sessions/data/2013/12' - la date saisie (2013/12) indique l'année/le mois. Toutes les données de décembre 2013 sont récupérées à l'aide de l'instruction « location ».

Expert_Rule_daily_report_103SP3.png

L'ensemble des résultats de cette requête sera présenté dans un rapport quotidien.

Rapport hebdomadaire

Dans cet exemple de règle, vous pouvez créer un rapport hebdomadaire pour décembre 2013. L'instruction LOCATION peut être modifiée pour générer un rapport hebdomadaire.

LOCATION '/RSA/rsasoc/v1/sessions/data/2013/12' - la date saisie (2013/12) indique l'année/le mois. Toutes les données de décembre 2013 sont récupérées à l'aide de l'instruction « location ».

Expert_Rule_weekly_report_103SP3.png

L'ensemble des résultats de cette requête sera présenté dans un rapport hebdomadaire.

Rapport mensuel

Dans cet exemple de règle, vous pouvez créer un rapport mensuel pour l'année 2013. L'instruction LOCATION peut être modifiée pour générer un rapport mensuel.

LOCATION '/RSA/rsasoc/v1/sessions/data/2013' - la date saisie (2013) indique l'année. Toutes les données de l'année 2013 sont récupérées à l'aide de l'instruction « location ».

Expert_Rule_monthly_report_103SP3.png

L'ensemble des résultats de cette requête sera présenté dans un rapport mensuel.

Pour plus d'informations sur la définition de LOCATION, consultez Spécifier l'emplacement HDFS dans la section Syntaxe générale d'une règle avancée.

Vous devez réaliser les étapes suivantes dans l'ordre pour afficher l'ensemble de résultats d'une règle avancée :

  1. Définir une règle avancée
  2. Ajouter la règle avancée à un rapport
  3. Planifier un rapport
  4. Afficher un rapport planifié

La figure suivante montre comment définir une règle avancée.

ExpertRule-TimeVar-input_103SP3.jpg

La figure suivante montre comment ajouter une règle avancée à un rapport (par exemple, AllEventCategories).

add_expert_rule_to_report.png

La figure suivante vous montre comment planifier un rapport quotidien.

Expert_Rule_daily_report_103SP3.png

Si vous souhaitez générer un rapport pour une période spécifique, vous devez définir manuellement la période dans la requête à l'aide des deux variables suivantes :

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

Par exemple, 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};

La figure suivante montre l'ensemble de résultats de la planification d'un rapport quotidien.

TimeVar-output_103SP3.JPG

Partition de table basée sur le rapport d'emplacement

Dans cet exemple de règle, vous pouvez créer une partition de table basée sur l'emplacement. Chaque table peut disposer d'une ou de plusieurs clés de partition qui déterminent comment les données sont stockées. Par exemple, country_dst de type STRING et ip_src de type STRING. Chaque valeur unique des clés de partition définit une partition de la table.

Dans l'exemple fourni, nous exécutons une requête HIVE pour extraire le pays de destination et l'adresse IP de la source à partir de la table sessions05032014 et nous regroupons les résultats grâce à ces champs.

Cette règle fournit des informations sur la table créée, la ligne formatée, l'emplacement (chemin d'accès au répertoire) pour les fichiers de données avro dans Warehouse, et renvoie un ensemble de résultats en fonction de la requête HIVE pour indiquer que la requête a renvoyé un ensemble de résultats. Pour plus d'informations sur ces instructions, reportez-vous à la section Syntaxe générale d'une règle avancée.

ExpertRule-GroupByLocation_103SP3.jpg

La figure suivante montre l'ensemble de résultats de la création d'une partition de table basée sur un rapport d'emplacement.

TablePartition_103SP3.jpg

Joindre les logs et sessions en fonction du rapport unique_id

Dans cet exemple de règle, vous pouvez créer une règle pour joindre des tables de sessions et logs afin d'extraire unique_id, l'adresse IP de la source et de la destination, et l'ID de paquet basé sur unique_id.

Dans l'exemple fourni, nous exécutons une requête HIVE extraire certains champs de sessions_table et logs_table en réalisant une jointure basée sur le champ « unique_id ».

Cette règle fournit des informations sur la table créée, la ligne formatée, l'emplacement (chemin d'accès au répertoire) pour les fichiers de données avro dans Warehouse, et renvoie un ensemble de résultats en fonction de la requête HIVE pour indiquer que la requête a renvoyé un ensemble de résultats. Pour plus d'informations sur ces instructions, reportez-vous à la section Syntaxe générale d'une règle avancée.

ExpertRule - Join_103SP3.png

La figure suivante montre l'ensemble de résultats de la jointure de tables de sessions et logs en fonction de unique_id.

Join-output_103SP3.jpg

Rapport de liste

Dans cet exemple de règle, vous pouvez créer un rapport de liste pour extraire l'adresse IP de la source et de la destination, et le type de périphérique à partir de la table lists_test où le type de périphérique n'est pas nul et l'adresse IP de la source est extraite à partir de la liste d'événement adéquate.

Cette règle fournit des informations sur la table créée, la ligne formatée, l'emplacement (chemin d'accès au répertoire) pour les fichiers de données avro dans Warehouse, et renvoie un ensemble de résultats en fonction de la requête HIVE pour indiquer que la requête a renvoyé un ensemble de résultats. Pour plus d'informations sur ces instructions, reportez-vous à la section Syntaxe générale d'une règle avancée.

ExpertRule-Lists_103SP3.JPG

La figure suivante montre l'ensemble de résultats de l'exécution d'un rapport de liste.

LISTS-output_103SP3.jpg

Rapport paramétré

Dans cette règle d'exemple, vous pouvez créer une règle pour extraire les adresses IP de la source et de la destination, et le type de périphérique à partir de la table runtime_variable en fonction de la variable d'exécution ${EnterIPDestination}. Lors de l'exécution, il vous est demandé de saisir une valeur pour l'adresse IP de l'ip_dst de destination. Selon la valeur saisie, l'ensemble de résultats s'affiche.

Cette règle fournit des informations sur la table créée, la ligne formatée, l'emplacement (chemin d'accès au répertoire) pour les fichiers de données avro dans Warehouse, et renvoie un ensemble de résultats en fonction de la requête HIVE pour indiquer que la requête a renvoyé un ensemble de résultats. Pour plus d'informations sur ces instructions, reportez-vous à la section Syntaxe générale d'une règle avancée.

ExpertRule-RunTimeVar_103SP3.JPG

La figure suivante montre l'ensemble de résultats de l'exécution d'un rapport paramétré.

Run_Time_Variable-output_103SP3.jpg

Table partitionnée comportant différents emplacements

Le texte suivant est un exemple de table partitionnée comportant différents emplacements :

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};

La table partitionnée comportant différents emplacements se présente tel qu'il est expliqué ci-dessous.

  1. Activez HIVE afin qu'il analyse de manière récursive tous les sous-répertoires et qu'il lise toutes les données à partir des sous-répertoires.
    set mapred.input.dir.recursive=true;
    set hive.mapred.supports.subdirectories=true;

  2. Déplacez et créez une table externe, puis formatez les lignes :
    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';

Remarque :  Il est nécessaire de créer une table externe uniquement si vous utilisez une autre table. Par exemple, si vous utilisez une autre table qu'AVRO_COUNT, vous devez supprimer cette table et créer une table externe.

Remarque : Points à ne pas oublier lorsque vous créez une table :
    - la suppression d'une table non externe provoque la suppression des données ; 
    - la table est partitionnée sur une colonne unique, appelée partition_id, qui, et il s’agit de la colonne standard pour Reporting Engine.
    -La valeur par défaut d’une colonne est nulle, car le fichier AVRO ne peut-être pas contenir la colonne spécifiée.
    - les noms de colonne doivent contenir des lettres minuscules, car HIVE n'est pas sensible à la casse mais AVRO l'est ;
    - vous devez spécifier avro.schema.literal dans SERDEPROPERTIES.

Pour plus d'informations sur la syntaxe de la règle, consultez Apache HIVE. 

  1. Ajoutez des partitions :
    Lorsque vous définissez une table, vous devez spécifier les emplacements HDFS depuis lesquels les données doivent être interrogées avant d'exécuter les instructions HIVE. Le paramètre location spécifie les données à extraire en fonction de la date spécifiée. Les données sont réparties entre différents emplacements ou répertoires du système HDFS. Pour chaque emplacement, vous devez ajouter une partition avec des valeurs spécifiques attribuées à la colonne de partition. Les emplacements peuvent être n’importe quel répertoire dans le système 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/';

Remarque : HIVE lit chaque fichier présent à ces emplacements comme étant un fichier AVRO. Si ces emplacements comportent un fichier non AVRO, la requête peut échouer.

  1. Exécutez la requête
    SELECT COUNT(*) as TOTAL FROM AVRO_COUNT WHERE time >= ${report_starttime} AND time
    <= ${report_endtime};

    Lorsqu’une table est créée, vous pouvez exécuter des requêtes spécifiques pour filtrer les données. Par exemple,après avoir créé la table, vous pouvez filtrer les données de la manière illustrée dans les exemples ci-dessous. 
    Sessions avec une adresse IP source spécifique :
    SELECT * FROM AVRO_COUNT WHERE time >= ${report_starttime} AND time <= ${report_endtime}  AND ip_src = '127.0.0.1';
    Regrouper en fonction de la destination de l’utilisateur :
    SELECT * FROM AVRO_COUNT WHERE time >= ${report_starttime} AND time <= ${report_endtime}  GROUP BY usr_dst;

Partition automatisée avec la fonction custom

Dans la version 10.5.1, vous pouvez utiliser la fonction custom pour automatiser l'ajout de partitions à une table définie par l'utilisateur en mode expert.

Syntaxe générale

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

La table suivante décrit la syntaxe de la fonction custom :

                      
Numéro de sessionNameDescription
1tableNom de la table pour laquelle la partition a été ajoutée.
2espace de nommagenamespace peut correspondre à des sessions ou des logs.
3rollup

Cette valeur détermine le niveau du chemin de répertoire à inclure dans les partitions. La
valeur correspondante peut être HOUR, DAY ou MINUTE. Si Warehouse Connector est configuré pour la valeur Day de rollup, la valeur HOUR génère des résultats ZERO. Le nombre de partitions et l'emplacement de chaque partition dépendent de la période utilisée pour exécuter la règle et de la valeur de rollup.

4(Facultatif) starttime, endtime

Pour générer des partitions pour une période spécifique différente de celle mentionnée dans la règle, vous devez spécifier l'heure de début et l'heure de fin en Secondes Epoch.

Remarque : Les expressions ne sont pas prises en charge pour l'heure de début et l'heure de fin.

La fonction custom est appelée lorsque Reporting Engine exécute la règle, soit pendant l'exécution de la règle test, soit pendant le rapport planifié. Lors de l’exécution d’une règle Expert, chaque fois que Reporting Engine identifie la déclaration de fonction, il extrait les arguments requis
et insère le nombre n d’instructions ADD PARTITION HiveQL et les exécute sur le serveur Hive.

La structure des emplacements et des répertoires est déterminée par l'argument transmis dans la règle et la configuration de la source de données Hive
dans Reporting Engine. Le nombre de partitions dépend de la mise à jour spécifiée et la plage horaire utilisée lors de l'exécution de la règle. Par exemple, avec la valeur de rollup définie sur HOUR et la période sur PAST 2 Days, Reporting Engine génère 48 partitions pour 48 heures alors que,
avec la valeur de rollup définie sur DAY, Reporting Engine crée 2 partitions, une pour chaque jour.

La requête de partition est générée par le modèle de syntaxe, tel qu'il est défini dans l'attribut de configuration Hive AlterTableTemplate du Reporting Engine.

Remarque : Par défaut, cette fonction commence à ajouter des partitions à une table en numérotant les partitions de 0 à N-1. La table doit donc être partitionnée par colonne désignée par un seul nombre entier, appelé l'ID de partition.

Le texte suivant est un exemple de partition automatisée à l'aide de la fonction custom :
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 > Références du module Reporting > Références aux règles > zzWhDBRulDefMod > Règles avancées liées à une base de données Warehouse

Attachments

    Outcomes