Informes: Reglas avanzadas de la base de datos de Warehouse

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

En este tema se proporcionan ejemplos de reglas de origen de datos de Warehouse. Puede definir reglas de base de datos de Warehouse mediante consultas de HIVE. Puede definir reglas simples y avanzadas para el origen de datos de Warehouse mediante los siguientes modos:

  • Modo Predeterminado 
  • Modo experto

Las reglas avanzadas se definen mediante consultas HIVE complejas que se crean con las cláusulas DROP, CREATE, etc. A diferencia de las reglas simples, los resultados se insertan siempre en una tabla. Para obtener más información sobre el lenguaje de consulta HIVE avanzado, consulte Manual de lenguaje HIVE.

En los siguientes ejemplos se ilustran las reglas avanzadas en el modo experto:

  • Informe por hora, diario, semanal y mensual
  • Partición de la tabla basada en informe de ubicación
  • Registros de combinación y sesiones basados en informe unique_id
  • Informe de lista
  • Informe con parámetros
  • Tabla basada en partición con varias ubicaciones
  • Partición automatizada mediante función personalizada (10.5.1 en adelante) 

Sintaxis general de una regla avanzada

En la figura siguiente se muestra cómo definir una consulta avanzada.

ExpertRule-TimeVar-input_hourly_103SP3.jpg

La siguiente sintaxis es un ejemplo de una consulta avanzada:

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;

seleccione  from_unixtime(time), threat_category, ip.src desde  time_variable , donde threat_category no es NULL y time >= ${report_starttime} y time <= ${report_endtime};

Nota: Reporting Engine considera como comentario una línea que comienza con <guion> <guion> en una regla de Expert Warehouse.
Por ejemplo,
set mapred.input.dir.recursive=true;
-- This is an Expert comment
set hive.mapred.supports.subdirectories=true;

A continuación se explica la sintaxis general de una consulta avanzada:

  1. Desplegar y crear una tabla externa, y luego formatear la fila:
    Primero, la tabla se descarta si ya existe y se crea una tabla externa sessions21022014
    DROP TABLE IF EXISTS sessions21022014
    CREATE EXTERNAL TABLE sessions21022014

    Nota:  Solo debe crear una tabla externa si usa otra tabla. Por ejemplo, si usa otra tabla además de sessions21022014, debe descartar la tabla y crear una tabla externa.

    A continuación, especifique el formato de fila como interfaz Avro.SerDe para instruir a HIVE en cuanto a cómo procesar un registro. Avro.SerDe le permite leer o escribir datos Avro como tablas HIVE y almacenarlos como formato de entrada y formato de salida.
    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. Especificar la ubicación de HDFS:
    En segundo lugar, debe especificar la ubicación de HDFS “/RSA/rsasoc/v1/sessions/data/2013/12/2” desde donde se consultan los datos antes de ejecutar las declaraciones HIVE. El parámetro de ubicación especifica los datos que se van a buscar en función de la entrada de fecha proporcionada. Este es un parámetro variable, por tanto, se puede buscar valores en función de la fecha introducida.
  2. Definir el esquema de la tabla:
    En tercer lugar, defina el esquema de la tabla mediante la definición de columnas con un tipo de datos específico y el valor predeterminado como “nulo”.
    TBLPROPERTIES('avro.schema.literal'='
    {"type":"record";
    "name":"nextgen";
    "fields":
    [
    {"name":"ip_src",  "type":["string", "null"], "default":"null"}
    ]
    '};
  3. Importar datos de un directorio que contiene subdirectorios:
    A continuación, debe permitir que HIVE escanee recurrentemente todos los subdirectorios y busque todos los datos de todos los subdirectorios.
    set mapred.input.dir.recursive=true;
    set hive.mapred.supports.subdirectories=true;
  4. Buscar datos de la tabla HIVE:
    Una vez que ejecute todas las declaraciones anteriores, puede consultar la base de datos con la cláusula select de la consulta HIVE para buscar los datos de la tabla HIVE.

Informe por hora, diario, semanal y mensual

En estas reglas de ejemplo, puede crear varios informes para 2 de diciembre de 2013 (como en la figura de abajo). La variable de fecha en la declaración LOCATION puede modificarse, según lo cual puede crear un informe por hora, diario, semanal y mensual.

Informes por hora

En esta regla de ejemplo, puede crear un informe por hora para 2 de diciembre de 2013. La declaración LOCATION se puede modificar para generar un informe por hora

LOCATION '/RSA/rsasoc/v1/sessions/data/2013/12/2' : la entrada de fecha (2013/12/2) indica año/mes/día. La totalidad de los datos correspondiente a 2 de diciembre de 2013 se recupera con esta declaración de ubicación. 

Expert_Rule_hourly_report_103SP3.png

El conjunto de resultados de esta consulta será un informe por hora.

Informe diario

En esta regla de ejemplo, puede crear un informe diario para diciembre de 2013. La declaración LOCATION se puede modificar para generar un informe diario.

LOCATION '/RSA/rsasoc/v1/sessions/data/2013/12': la entrada de fecha (2013/12) indica año/mes. La totalidad de los datos correspondiente a diciembre de 2013 se recupera con esta declaración de ubicación.

Expert_Rule_daily_report_103SP3.png

El conjunto de resultados de esta consulta será un informe diario.

Informe semanal

En esta regla de ejemplo, puede crear un informe semanal para diciembre de 2013. La declaración LOCATION se puede modificar para generar un informe semanal.

LOCATION '/RSA/rsasoc/v1/sessions/data/2013/12': la entrada de fecha (2013/12) indica año/mes. La totalidad de los datos correspondiente a diciembre de 2013 se recupera con esta declaración de ubicación.

Expert_Rule_weekly_report_103SP3.png

El conjunto de resultados de esta consulta será un informe semanal.

Informe mensual

En esta regla de ejemplo, puede crear un informe mensual para el año 2013. La declaración LOCATION se puede modificar para generar un informe mensual.

LOCATION '/RSA/rsasoc/v1/sessions/data/2013' : la entrada de fecha (2013) indica el año. La totalidad de los datos correspondiente al año 2013 se recupera con esta declaración de ubicación.

Expert_Rule_monthly_report_103SP3.png

El conjunto de resultados de esta consulta será un informe mensual.

Para obtener más información sobre la definición de LOCATION, consulte Especificar la ubicación de HDFS en la sección Sintaxis general de una regla avanzada.

Debe realizar los siguientes pasos en secuencia para ver el conjunto de resultados de una regla avanzada:

  1. Definir una regla avanzada
  2. Agrega una regla avanzada a un informe
  3. Programar un informe
  4. Ver un informe programado

En la figura siguiente se muestra cómo definir una regla avanzada.

ExpertRule-TimeVar-input_103SP3.jpg

En la figura siguiente se muestra cómo agregar una regla avanzada a un informe (por ejemplo, AllEventCategories).

add_expert_rule_to_report.png

En la figura siguiente se muestra cómo programar un informe diario.

Expert_Rule_daily_report_103SP3.png

Si desea generar un informe con un rango de tiempo específico, debe definir manualmente el rango de tiempo en la consulta utilizando las siguientes dos variables:

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

Por ejemplo,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};

En la siguiente figura se muestra el conjunto de resultados de la calendarización de un informe diario.

TimeVar-output_103SP3.JPG

Partición de la tabla basada en informe de ubicación

En esta regla de ejemplo, puede crear una partición de la tabla basándose en la ubicación. Cada tabla puede tener una o más claves de partición, que determinan cómo se almacenan los datos. Por ejemplo, un country_dst de tipo STRING y un ip_src de tipo STRING. Cada valor único de las claves de partición define una partición de la tabla.

En el ejemplo, ejecutamos una consulta HIVE a buscar el país de destino y la dirección IP de origen de la tabla sessions05032014 y agrupamos el conjunto de resultados según estos campos.

Esta regla proporciona información sobre la tabla creada, la fila formateada y la ubicación (ruta del directorio) de archivos de datos avro en Warehouse, y devuelve un conjunto de resultados de acuerdo con la consulta HIVE para indicar que la consulta devolvió un conjunto de resultados. Para obtener más información sobre estas declaraciones, consulte la sección Sintaxis general de una regla avanzada.

ExpertRule-GroupByLocation_103SP3.jpg

En la siguiente figura se muestra el conjunto de resultados de la creación de una partición de tabla en función del informe de ubicación.

TablePartition_103SP3.jpg

Registros de combinación y sesiones basados en informe unique_id

En esta regla de ejemplo, puede crear una regla para combinar registros y tablas de sesiones para buscar unique_id, la dirección IP de origen y destino, y el ID de paquete en función de unique_id.

En el ejemplo dado, ejecutamos una consulta HIVE para buscar ciertos campos, tanto de la sessions_table como de la logs_table mediante la realización de una combinación basada en el campo “unique_id”.

Esta regla proporciona información sobre la tabla creada, la fila formateada y la ubicación (ruta del directorio) de archivos de datos avro en Warehouse, y devuelve un conjunto de resultados de acuerdo con la consulta HIVE para indicar que la consulta devolvió un conjunto de resultados. Para obtener más información sobre estas declaraciones, consulte la sección Sintaxis general de una regla avanzada.

ExpertRule - Join_103SP3.png

En la siguiente figura se muestra el conjunto de resultados de la combinación de registros y tablas de sesiones basadas en unique_id.

Join-output_103SP3.jpg

Informe de lista

En esta regla de ejemplo, puede crear un informe de lista para buscar la dirección IP de origen y destino, y el tipo de dispositivo de la tabla lists_test, donde el tipo de dispositivo no es nulo y la dirección IP de origen se obtiene de la lista de eventos correspondiente.

Esta regla proporciona información sobre la tabla creada, la fila formateada y la ubicación (ruta del directorio) de archivos de datos avro en Warehouse, y devuelve un conjunto de resultados de acuerdo con la consulta HIVE para indicar que la consulta devolvió un conjunto de resultados. Para obtener más información sobre estas declaraciones, consulte la sección Sintaxis general de una regla avanzada.

ExpertRule-Lists_103SP3.JPG

En la siguiente figura se muestra el conjunto de resultados de la ejecución de un informe de lista.

LISTS-output_103SP3.jpg

Informe con parámetros

En este ejemplo de regla, puede crear una regla para buscar direcciones IP de origen y destino, y el tipo de dispositivo de la tabla runtime_variable en función de la variable de hora de ejecución especificada ${EnterIPDestination}. En tiempo de ejecución, se le pedirá que introduzca un valor para la dirección IP de destino, ip_dst. El conjunto de resultados se muestra según el valor que se ingresó.

Esta regla proporciona información sobre la tabla creada, la fila formateada y la ubicación (ruta del directorio) de archivos de datos avro en Warehouse, y devuelve un conjunto de resultados de acuerdo con la consulta HIVE para indicar que la consulta devolvió un conjunto de resultados. Para obtener más información sobre estas declaraciones, consulte la sección Sintaxis general de una regla avanzada.

ExpertRule-RunTimeVar_103SP3.JPG

En la siguiente figura se muestra el conjunto de resultados de la ejecución de un informe con parámetros.

Run_Time_Variable-output_103SP3.jpg

Tabla basada en partición con varias ubicaciones

El siguiente es un ejemplo de la tabla basada en partición con varias ubicaciones:

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 tabla basada en partición con varias ubicaciones es como se explica a continuación:

  1. Permita que HIVE escanee recurrentemente todos los subdirectorios y que lea todos sus datos.
    set mapred.input.dir.recursive=true;
    set hive.mapred.supports.subdirectories=true;

  2. Descarte y cree una tabla externa y formatee las filas:
    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';

Nota:  Solo debe crear una tabla externa si usa otra tabla. Por ejemplo, si usa otra tabla además de AVRO_COUNT, debe descartar la tabla y crear una tabla externa.

Nota: Puntos que debe recordar cuando crea una tabla:
    - Si descarta una tabla “no externa”, se eliminan los datos. 
    - La tabla está particionada en una sola columna denominada partition_id, que es la columna estándar para Reporting Engine.
    - El valor predeterminado de cualquier columna es nulo, porque es probable que el archivo AVRO no contenga la columna especificada.
    - Los nombres de las columnas deben estar en minúscula, porque HIVE no distingue mayúsculas de minúsculas, a diferencia de AVRO.
    - Debe especificar avro.schema.literal en SERDEPROPERTIES.

Para obtener más información sobre la sintaxis de regla, consulte Apache HIVE. 

  1. Agregar particiones:
    Una vez que define una tabla, debe especificar las ubicaciones de HDFS desde donde se deben consultar los datos antes de ejecutar las declaraciones HIVE. El parámetro de ubicación especifica los datos que se buscarán según la fecha que se especifique. Los datos se distribuyen entre varias ubicaciones o directorios de HDFS. Para cada ubicación debe agregar una partición con valores únicos asignados a la columna de la partición. Las ubicaciones pueden ser cualquier directorio en 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/';

Nota: HIVE lee cada archivo en estas ubicaciones como AVRO. Si en una de estas ubicaciones está disponible un archivo no AVRO, la consulta puede fallar.

  1. Ejecute la consulta
    SELECT COUNT(*) as TOTAL FROM AVRO_COUNT WHERE time >= ${report_starttime} AND time
    <= ${report_endtime};

    Cuando se crea una tabla, puede ejecutar consultas específicas para filtrar los datos. Por ejemplo, después de crear la tabla, puede filtrar los datos como se muestra en los siguientes ejemplos: 
    Sesiones con una dirección IP de origen específica:
    SELECT * FROM AVRO_COUNT WHERE time >= ${report_starttime} AND time <= ${report_endtime}  AND ip_src = '127.0.0.1';
    Agrupar por en función del destino del usuario:
    SELECT * FROM AVRO_COUNT WHERE time >= ${report_starttime} AND time <= ${report_endtime}  GROUP BY usr_dst;

Partición automatizada mediante función personalizada

En la versión 10.5.1, puede usar la función personalizada para automatizar la adición de particiones en una tabla definida por el usuario en el modo experto.

Sintaxis general

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

En la siguiente tabla se describe la sintaxis de la función personalizada:

                      
NúmeroNombreDescripción
1Tabla El nombre de la tabla para el cual se debe agregar la partición.
2namespaceEl espacio de nombres puede ser sesiones o registros.
3rollup

Este valor determina el nivel de ruta del directorio que se incluirá en las particiones. El
valor puede ser HORA, DÍA o MINUTO. Si Warehouse Connector está configurado para acumulación por día, la configuración de este valor como HORA genera CERO resultados. El número y la ubicación de cada partición se basa en el rango de tiempo que se utiliza para ejecutar la regla y el valor de acumulación.

4(Opcional) starttime, endtime

Para generar particiones para un rango de tiempo determinado que no sea el rango de tiempo que se menciona en la regla, debe especificar starttime y endtime en segundos Epoch.

Nota: No se admiten expresiones para starttime y endtime.

La función personalizada se invoca cuando Reporting Engine ejecuta la regla durante la regla de prueba o el informe programado. Durante la ejecución de una regla experta, siempre que Reporting Engine identifica la declaración de función, extrae los argumentos requeridos,
inserta declaraciones ADD PARTITION HiveQL  y las ejecuta en el servidor de Hive.

La ubicación y la estructura del directorio las determina el argumento transmitido en la regla y la configuración de origen de datos de Hive
en Reporting Engine. La cantidad de particiones depende de la acumulación especificada y del rango de tiempo utilizado durante la ejecución de la regla. Por ejemplo, con la acumulación como HORA y el rango de tiempo como ÚLTIMOS 2 DÍAS, se generan 48 particiones para 48 horas, mientras que
con la acumulación como DÍA, Reporting Engine crea 2 particiones, una para cada día.

La consulta de la partición se genera en la plantilla de sintaxis como se establece en el atributo de configuración de Hive de Reporting Engine, AlterTableTemplate.

Nota: De forma predeterminada, esta función inicia la adición de particiones a una tabla con ID de partición de 0 a n-1. Por lo tanto, esto requiere que la tabla se particione por la columna de número entero único denominada ID de partición.

El siguiente es un ejemplo de una partición automatizada mediante la función personalizada:
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};

 

Previous Topic:Referencias de reglas
You are here
Table of Contents > Referencias del módulo Reporting > Referencias de reglas > zzWhDBRulDefMod > Reglas avanzadas de la base de datos de Warehouse

Attachments

    Outcomes