# Consulta de los registros de consulta de Amazon Route 53 Resolver
<a name="querying-r53-resolver-logs"></a>

Puede crear tablas de Athena para sus registros de consulta de Amazon Route 53 Resolver y consultarlas desde Athena.

El registro de consultas de Route 53 Resolver se utiliza para el registro de consultas de DNS realizadas por recursos dentro de una VPC, recursos en las instalaciones que utilizan un punto de conexión de Resolver saliente para resolución DNS recursiva y consultas que utilizan reglas de firewall DNS de Route 53 Resolver para bloquear, permitir o monitorear una lista de dominios. Para obtener más información acerca del registro de consultas de Resolver, consulte [Registro de consultas de Resolver](https://docs.aws.amazon.com/Route53/latest/DeveloperGuide/resolver-query-logs.html) en la *Guía para desarrolladores de Amazon Route 53*. Para obtener información sobre cada uno de los campos de los registros, consulte [Valores que aparecen en los registros de consulta de Resolver](https://docs.aws.amazon.com/Route53/latest/DeveloperGuide/resolver-query-logs-format.html) en la *Guía para desarrolladores de Amazon Route 53*.

**Topics**
+ [Creación de la tabla para los registros de consultas de Resolver](querying-r53-resolver-logs-creating-the-table.md)
+ [Uso de la proyección de particiones](querying-r53-resolver-logs-partitioning-example.md)
+ [Consultas de ejemplo](querying-r53-resolver-logs-example-queries.md)

# Creación de la tabla para los registros de consultas de Resolver
<a name="querying-r53-resolver-logs-creating-the-table"></a>

Puede utilizar el Editor de consultas de la consola de Athena para crear y consultar una tabla para los registros de consulta de Route 53 Resolver.

**Para crear y consultar una tabla de Athena para los registros de consulta de Route 53 Resolver**

1. Abra la consola de Athena en [https://console.aws.amazon.com/athena/](https://console.aws.amazon.com/athena/home).

1. En el Editor de consultas de Athena, ingrese la siguiente instrucción `CREATE TABLE`. Reemplace los valores de la cláusula `LOCATION` con los correspondientes a la ubicación de los registros de Resolver de Amazon S3.

   ```
   CREATE EXTERNAL TABLE r53_rlogs (
     version string,
     account_id string,
     region string,
     vpc_id string,
     query_timestamp string,
     query_name string,
     query_type string,
     query_class
       string,
     rcode string,
     answers array<
       struct<
         Rdata: string,
         Type: string,
         Class: string>
       >,
     srcaddr string,
     srcport int,
     transport string,
     srcids struct<
       instance: string,
       resolver_endpoint: string
       >,
     firewall_rule_action string,
     firewall_rule_group_id string,
     firewall_domain_list_id string
    )
        
   ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
   LOCATION 's3://amzn-s3-demo-bucket/AWSLogs/aws_account_id/vpcdnsquerylogs/{vpc-id}/'
   ```

   Dado que los datos del registro de consulta de Resolver están en formato JSON, la instrucción CREATE TABLE utiliza una [biblioteca de SerDe JSON](json-serde.md) para analizar los datos.
**nota**  
El SerDe espera que cada documento JSON esté en una sola línea de texto sin caracteres de terminación de línea que separen los campos del registro. Si el texto JSON está en formato de impresión, puede recibir un mensaje de error como HIVE\$1CURSOR\$1ERROR: la fila no es un objeto JSON válido o HIVE\$1CURSOR\$1ERROR: JsonParseException: fin de entrada inesperado: marcador de cierre esperado para OBJECT cuando intenta consultar la tabla después de crearla. Para obtener más información, consulte los [Archivos de datos JSON](https://github.com/rcongiu/Hive-JSON-Serde#json-data-files) en la documentación de OpenX SerDE en GitHub. 

1. Elija **Ejecutar consulta**. La instrucción crea una tabla de Athena llamada `r53_rlogs` cuyas columnas representan cada uno de los campos de los datos de registro de Resolver.

1. En el Editor de consultas de la consola de Athena, ejecute la siguiente consulta para verificar que se ha creado la tabla.

   ```
   SELECT * FROM "r53_rlogs" LIMIT 10
   ```

# Uso de la proyección de particiones
<a name="querying-r53-resolver-logs-partitioning-example"></a>

En el siguiente ejemplo, se muestra una instrucción `CREATE TABLE` para los registros de consultas de Resolver que utiliza la proyección de particiones y se encuentra particionada por VPC y por fecha. Para obtener más información sobre la proyección de particiones, consulte [Uso de proyección de particiones con Amazon Athena](partition-projection.md).

```
CREATE EXTERNAL TABLE r53_rlogs (
  version string,
  account_id string,
  region string,
  vpc_id string,
  query_timestamp string,
  query_name string,
  query_type string,
  query_class string,
  rcode string,
  answers array<
    struct<
      Rdata: string,
      Type: string,
      Class: string>
    >,
  srcaddr string,
  srcport int,
  transport string,
  srcids struct<
    instance: string,
    resolver_endpoint: string
    >,
  firewall_rule_action string,
  firewall_rule_group_id string,
  firewall_domain_list_id string
)
PARTITIONED BY (
`date` string,
`vpc` string
)
ROW FORMAT SERDE      'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT          'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION              's3://amzn-s3-demo-bucket/route53-query-logging/AWSLogs/aws_account_id/vpcdnsquerylogs/'
TBLPROPERTIES(
'projection.enabled' = 'true',
'projection.vpc.type' = 'enum',
'projection.vpc.values' = 'vpc-6446ae02',
'projection.date.type' = 'date',
'projection.date.range' = '2023/06/26,NOW',
'projection.date.format' = 'yyyy/MM/dd',
'projection.date.interval' = '1',
'projection.date.interval.unit' = 'DAYS',
'storage.location.template' = 's3://amzn-s3-demo-bucket/route53-query-logging/AWSLogs/aws_account_id/vpcdnsquerylogs/${vpc}/${date}/'
)
```

# Consultas de ejemplo
<a name="querying-r53-resolver-logs-example-queries"></a>

Los siguientes ejemplos muestran algunas consultas que puede efectuar desde Athena en los registros de consultas de Resolver.

## Ejemplo 1: registros de consulta en orden de query\$1timestamp descendente
<a name="querying-r53-resolver-logs-example-1-query-logs-in-descending-query_timestamp-order"></a>

La siguiente consulta muestra los resultados del registro en orden `query_timestamp` descendente.

```
SELECT * FROM "r53_rlogs"
ORDER BY query_timestamp DESC
```

## Ejemplo 2: registros de consulta dentro de las horas de inicio y finalización especificadas
<a name="querying-r53-resolver-logs-example-2-query-logs-within-specified-start-and-end-times"></a>

La consulta a continuación consulta los registros entre las medianoche y las 8 h del 24 de septiembre de 2020. Sustituye las horas de inicio y finalización de acuerdo con sus propios requisitos.

```
SELECT query_timestamp, srcids.instance, srcaddr, srcport, query_name, rcode
FROM "r53_rlogs"
WHERE (parse_datetime(query_timestamp,'yyyy-MM-dd''T''HH:mm:ss''Z')
     BETWEEN parse_datetime('2020-09-24-00:00:00','yyyy-MM-dd-HH:mm:ss') 
     AND parse_datetime('2020-09-24-00:08:00','yyyy-MM-dd-HH:mm:ss'))
ORDER BY query_timestamp DESC
```

## Ejemplo 3: registros de consulta basados en un patrón de nombre de consulta de DNS especificado
<a name="querying-r53-resolver-logs-example-3-query-logs-based-on-a-specified-dns-query-name-pattern"></a>

La siguiente consulta selecciona registros cuyo nombre de consulta incluye la cadena “example.com”.

```
SELECT query_timestamp, srcids.instance, srcaddr, srcport, query_name, rcode, answers
FROM "r53_rlogs"
WHERE query_name LIKE '%example.com%'
ORDER BY query_timestamp DESC
```

## Ejemplo 4: solicitudes de registro de consultas sin respuesta
<a name="querying-r53-resolver-logs-example-4-query-log-requests-with-no-answer"></a>

La siguiente consulta selecciona entradas de registro en las que la solicitud no recibió respuesta.

```
SELECT query_timestamp, srcids.instance, srcaddr, srcport, query_name, rcode, answers
FROM "r53_rlogs"
WHERE cardinality(answers) = 0
```

## Ejemplo 5: registros de consultas con una respuesta específica
<a name="querying-r53-resolver-logs-example-5-query-logs-with-a-specific-answer"></a>

La siguiente consulta muestra los registros en los que el valor `answer.Rdata` tiene la dirección IP especificada.

```
SELECT query_timestamp, srcids.instance, srcaddr, srcport, query_name, rcode, answer.Rdata
FROM "r53_rlogs"
CROSS JOIN UNNEST(r53_rlogs.answers) as st(answer)
WHERE answer.Rdata='203.0.113.16';
```