Informes: Reglas avanzadas de la base de datos de Warehouse

Document created by RSA Information Design and Development on May 1, 2018
Version 1Show Document
  • View in full screen mode

En esta sección se explica la sintaxis de la consulta de reglas avanzadas y se proporcionan ejemplos.

Sintaxis general de una regla avanzada

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

Ejemplo para definir una consulta avanzada 

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.

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) 

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. 

Programar un informe por hora

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.

Programar un informe diario

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.

Programar un informe semanal

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.

Programar un informe mensual

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.

Ejemplo de regla avanzada

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

Regla avanzada para un informe

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

Programar un informe diario

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 programación de un informe diario.

Conjunto de resultados de la programación de un informe diario

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 que se creó, la fila que se formateó 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”.

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

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.

Conjunto de resultados de partición de la tabla en función de un informe de ubicación

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.

Ejemplo de registros de combinación y sesiones basados en informe unique_id

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

Conjunto de resultados de registros de combinación y sesiones basados en informe unique_id

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.

Ejemplo de un informe de lista

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

Conjunto de resultados de un informe de lista

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.

Informe con parámetros

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

Conjunto de resultados de un informe con parámetros

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 e inserta el número n de las declaraciones ADD PARTITION HiveQL y los ejecuta en el servidor de Hive.

El argumento transmitido en la regla y la configuración de origen de datos de Hive en Reporting Engine determinan la ubicación y la estructura
del directorio. 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};

Creación de informes de tablas personalizadas

En 10.6.1, puede usar y crear tablas personalizadas en el servidor de Hive. Reporting Engine admite la ejecución de consultas en tablas definidas por el usuario y la capacidad de crear una tabla nueva desde la salida de una sola regla. Cuando esta función se habilita en la interfaz del usuario del generador de reglas de Warehouse, el usuario puede ver una lista de tablas personalizadas disponibles en el servidor de Hive.

Ejemplo para crear informes de tablas personalizadas

Para habilitar esta función, configure customTablesEnabled en VERDADERO navegando a Reporting Engine -> Explorar ->Configuración de Hive.

Configuración de tabla personalizada

Creación de tabla personalizada a partir de reglas regulares

Para programar un informe que contiene una sola regla SAW, se agrega un nuevo texto de entrada con un Nombre de CTAS de Warehouse. El usuario ahora puede especificar un nombre de tabla personalizada que se creará fuera de la salida de la regla en el informe.

Nota: Esta función solo está disponible si el informe contiene una sola regla SAW en la página Calendario. De lo contrario, esta opción está oculta.

A continuación, se explica el proceso para usar la función:

  1. Cree una regla para filtrar con los datos de SAW.Una regla para filtrar con los datos de SAW
  2. Cree un informe con la regla anterior.Informe de SAW

  3. Cree un programa y escriba el nombre de la tabla CTAS.
    Programe un informe con el nombre de la tabla CTAS

  4. Ejecute el informe y Reporting Engine creará el resumen de resultados para el programa, como se indica a continuación.
    Conjunto de resultados de informe con el nombre de la tabla CTAS

  5. En la siguiente actualización del esquema o en el reinicio de Reporting Engine, se enumera la tabla CTAS.Se muestra la tabla CTAS

You are here
Table of Contents > Apéndice > Reglas avanzadas de la base de datos de Warehouse

Attachments

    Outcomes