Ejemplo de Amazon Data Firehose - Amazon Athena
Uso del tipo dateElección de claves de particiónUso de prefijos personalizados y particiones dinámicas

Ejemplo de Amazon Data Firehose

Cuando se usa Firehose para entregar datos a Amazon S3, la configuración predeterminada escribe objetos con claves que se parecen al siguiente ejemplo:

s3://DOC-EXAMPLE-BUCKET/prefix/yyyy/MM/dd/HH/file.extension

Para crear una tabla de Athena que encuentre las particiones de forma automática en el momento de la consulta, en lugar de tener que agregarlas al catálogo de datos de AWS Glue Data Catalog a medida que llegan nuevos datos, puede usar la proyección de particiones.

En el siguiente ejemplo de CREATE TABLE se usa la configuración predeterminada de Firehose.


CREATE EXTERNAL TABLE my_ingested_data (
 ...
)
...
PARTITIONED BY (
 datehour STRING
)
LOCATION "s3://DOC-EXAMPLE-BUCKET/prefix/"
TBLPROPERTIES (
 "projection.enabled" = "true",
 "projection.datehour.type" = "date",
 "projection.datehour.format" = "yyyy/MM/dd/HH",
 "projection.datehour.range" = "2021/01/01/00,NOW",
 "projection.datehour.interval" = "1",
 "projection.datehour.interval.unit" = "HOURS",
 "storage.location.template" = "s3://DOC-EXAMPLE-BUCKET/prefix/${datehour}/"
)

La cláusula TBLPROPERTIES de la instrucción CREATE TABLE indica a Athena lo siguiente:

  • Usar la proyección de la partición al consultar la tabla

  • La clave de partición datehour es de tipo date (que incluye una hora opcional)

  • Cómo se formatean las fechas

  • El rango de fechas y horas. Tenga en cuenta que los valores deben estar separados por una coma, no por un guion.

  • Dónde encontrar los datos en Amazon S3.

Al consultar la tabla, Athena calcula los valores de datehour y usa la plantilla de ubicación de almacenamiento para generar una lista de ubicaciones de partición.

Uso del tipo date

Cuando se usa el tipo date para una clave de partición proyectada, se debe especificar un rango. Como no tiene datos para fechas anteriores a la creación del flujo de entrega de Firehose, puede usar la fecha de creación como inicio. Y como no tiene datos para fechas en el futuro, puede usar el token especial NOW como fin.

En el ejemplo de CREATE TABLE, la fecha de inicio se especifica como 1 de enero de 2021 a medianoche UTC.

nota

Configure un rango que se ajuste lo más posible a sus datos para que Athena busque solo las particiones existentes.

Cuando se ejecuta una consulta en la tabla de ejemplo, Athena usa las condiciones de la clave de partición datehour en combinación con el rango para generar valores. Analice la siguiente consulta:

SELECT *
FROM my_ingested_data
WHERE datehour >= '2020/12/15/00'
AND datehour < '2021/02/03/15'

La primera condición de la consulta SELECT usa una fecha que es anterior al inicio del rango de fechas especificado por la instrucción CREATE TABLE. Como la configuración de la proyección de la partición especifica que no hay particiones para las fechas anteriores al 1 de enero de 2021, Athena busca datos solo en las siguientes ubicaciones, e ignora las fechas anteriores a la consulta.

s3://DOC-EXAMPLE-BUCKET/prefix/2021/01/01/00/
s3://DOC-EXAMPLE-BUCKET/prefix/2021/01/01/01/
s3://DOC-EXAMPLE-BUCKET/prefix/2021/01/01/02/
...
s3://DOC-EXAMPLE-BUCKET/prefix/2021/02/03/12/
s3://DOC-EXAMPLE-BUCKET/prefix/2021/02/03/13/
s3://DOC-EXAMPLE-BUCKET/prefix/2021/02/03/14/

Del mismo modo, si la consulta se ejecuta en una fecha y hora anteriores al 3 de febrero de 2021 a las 15:00 h, la última partición reflejaría la fecha y hora actuales, no la fecha y hora de la condición de consulta.

Si quiere consultar los datos más recientes, puede aprovechar el hecho de que Athena no genera fechas futuras y especificar solo una datehour de inicio, como en el siguiente ejemplo.

SELECT *
FROM my_ingested_data
WHERE datehour >= '2021/11/09/00'

Elección de claves de partición

Puede especificar cómo la proyección de particiones asigna las ubicaciones de las particiones a las claves de partición. En el ejemplo de CREATE TABLE de la sección anterior, la fecha y la hora se han combinado en una clave de partición denominada datehour, pero se pueden usar otros esquemas. Por ejemplo, también podría configurar una tabla con claves de partición separadas para el año, el mes, el día y la hora.

No obstante, si se dividen las fechas en año, mes y día no se puede utilizar el tipo de proyección de particiones date. Una alternativa es separar la fecha de la hora para seguir aprovechando el tipo de proyección de particiones date, pero realizar consultas que especifiquen intervalos de horas más fáciles de leer.

Con esto en mente, el siguiente ejemplo CREATE TABLE separa la fecha de la hora. Debido a que date es una palabra reservada en SQL, en el ejemplo se utiliza day como nombre para la clave de partición que representa la fecha.


CREATE EXTERNAL TABLE my_ingested_data2 (
 ...
)
...
PARTITIONED BY (
 day STRING,
 hour INT
)
LOCATION "s3://DOC-EXAMPLE-BUCKET/prefix/"
TBLPROPERTIES (
 "projection.enabled" = "true",
 "projection.day.type" = "date",
 "projection.day.format" = "yyyy/MM/dd",
 "projection.day.range" = "2021/01/01,NOW",
 "projection.day.interval" = "1",
 "projection.day.interval.unit" = "DAYS",
 "projection.hour.type" = "integer",
 "projection.hour.range" = "0,23",
 "projection.hour.digits" = "2",
 "storage.location.template" = "s3://DOC-EXAMPLE-BUCKET/prefix/${day}/${hour}/"
)

En la instrucción CREATE TABLE del ejemplo, la hora es una clave de partición independiente, configurada como un número entero. La configuración de la clave de partición de la hora especifica el rango de 0 a 23, y que la hora debe formatearse con dos dígitos cuando Athena genera las ubicaciones de la partición.

Una consulta para la tabla my_ingested_data2 podría verse así:

SELECT *
FROM my_ingested_data2
WHERE day = '2021/11/09'
AND hour > 3

Tipos de claves de partición y tipos de proyección de particiones

Tenga en cuenta que la clave datehour en el primer ejemplo CREATE TABLE está configurada como date en la configuración de la proyección de particiones, pero el tipo de la clave de partición es string. Lo mismo ocurre con day en el segundo ejemplo. Los tipos de configuración de proyección de particiones solo le dicen a Athena cómo formatear los valores cuando genera las ubicaciones de las particiones. Los tipos que se especifican no cambian el tipo de la clave de partición: en las consultas, datehour y day son de tipo string.

Cuando una consulta incluye una condición como day = '2021/11/09', Athena analiza la cadena a la derecha de la expresión con el formato de fecha especificado en la configuración de la proyección de particiones. Después de que Athena verifique que la fecha está dentro del rango configurado, usa de nuevo el formato de fecha para insertar la fecha como una cadena en la plantilla del almacén.

De forma similar, para una condición de consulta como day > '2021/11/09', Athena analiza el lado derecho y genera una lista de todas las fechas coincidentes dentro del rango configurado. A continuación, usa el formato de fecha para insertar cada fecha en la plantilla de ubicación de almacenamiento para crear la lista de ubicaciones de partición.

Escribir la misma condición como día day > '2021-11-09' o día day > DATE '2021-11-09' no funciona. En el primer caso, el formato de la fecha no coincide (observe los guiones en lugar de las barras inclinadas), y en el segundo caso, los tipos de datos no coinciden.

Uso de prefijos personalizados y particiones dinámicas

Firehose puede configurarse con prefijos personalizados y partición dinámica. Mediante estas características, puede configurar las claves de Amazon S3 y establecer esquemas de partición más compatibles con su caso de uso. También puede usar la proyección de particiones con estos esquemas de partición y configurarlos en consecuencia.

Por ejemplo, puede usar la característica de prefijo personalizado para obtener claves de Amazon S3 que tengan fechas con formato ISO en lugar del esquema predeterminado yyyy/MM/dd/HH.

También puede combinar los prefijos personalizados con el particionamiento dinámico para extraer una propiedad como customer_id de los mensajes de Firehose, como en el siguiente ejemplo.

prefix/!{timestamp:yyyy}-!{timestamp:MM}-!{timestamp:dd}/!{partitionKeyFromQuery:customer_id}/

Con ese prefijo de Amazon S3, el flujo de entrega de Firehose escribiría objetos en claves como s3://DOC-EXAMPLE-BUCKET/prefix/2021-11-01/customer-1234/file.extension. Para una propiedad como customer_id, cuyos valores pueden no conocerse de antemano, se puede usar el tipo de proyección de particiones injected y usar una instrucción CREATE TABLE como la siguiente:

CREATE EXTERNAL TABLE my_ingested_data3 (
 ...
)
...
PARTITIONED BY (
 day STRING,
 customer_id STRING
)
LOCATION "s3://DOC-EXAMPLE-BUCKET/prefix/"
TBLPROPERTIES (
 "projection.enabled" = "true",
 "projection.day.type" = "date",
 "projection.day.format" = "yyyy-MM-dd",
 "projection.day.range" = "2021-01-01,NOW",
 "projection.day.interval" = "1",
 "projection.day.interval.unit" = "DAYS",
 "projection.customer_id.type" = "injected",
 "storage.location.template" = "s3://DOC-EXAMPLE-BUCKET/prefix/${day}/${customer_id}/"
)

Cuando se consulta una tabla que tiene una clave de partición de tipo injected, la consulta debe incluir un valor para esa clave de partición. Una consulta para la tabla my_ingested_data3 podría verse así:

SELECT *
FROM my_ingested_data3
WHERE day BETWEEN '2021-11-01' AND '2021-11-30'
AND customer_id = 'customer-1234'

Fechas con formato ISO

Ya que los valores de la clave de partición day tienen formato ISO, también puede usar el tipo DATE para la clave de partición del día en lugar de STRING, como en el siguiente ejemplo:

PARTITIONED BY (day DATE, customer_id STRING)

Cuando se consulta, esta estrategia permite usar las funciones de fecha en la clave de partición sin analizar ni iniciar, como en el siguiente ejemplo:

SELECT *
FROM my_ingested_data3
WHERE day > CURRENT_DATE - INTERVAL '7' DAY
AND customer_id = 'customer-1234'
nota

Al especificar una clave de partición del tipo DATE, se supone que ha utilizado la característica de prefijo personalizado para crear claves de Amazon S3 con fechas con formato ISO. Si utiliza el formato predeterminado de Firehose de yyyy/MM/dd/HH, debe especificar la clave de partición como tipo string aunque la propiedad de la tabla correspondiente sea de tipo date, como en el siguiente ejemplo:

PARTITIONED BY ( 
  `mydate` string)
TBLPROPERTIES (
  'projection.enabled'='true', 
   ...
  'projection.mydate.type'='date',
  'storage.location.template'='s3://DOC-EXAMPLE-BUCKET/prefix/${mydate}')