Paso 2: crear una tabla

Ahora que tiene una base de datos, puede crear una tabla de Athena para ella. La tabla que cree se basará en los datos de registro de muestra de Amazon CloudFront en la ubicación s3://athena-examples-myregion/cloudfront/plaintext/, donde myregion es su Región de AWS actual.

Los datos de registro de muestra están en formato de valores separados por tabulaciones (TSV, tab-separated values), lo que significa que se utiliza un carácter de tabulación como delimitador para separar los campos. Los datos son similares al siguiente ejemplo. Por motivos de legibilidad, las pestañas del extracto se han convertido en espacios y el campo final se ha acortado.

2014-07-05 20:00:09 DFW3 4260 10.0.0.15 GET eabcd12345678.cloudfront.net /test-image-1.jpeg 200 - Mozilla/5.0[...]
2014-07-05 20:00:09 DFW3 4252 10.0.0.15 GET eabcd12345678.cloudfront.net /test-image-2.jpeg 200 - Mozilla/5.0[...]
2014-07-05 20:00:10 AMS1 4261 10.0.0.15 GET eabcd12345678.cloudfront.net /test-image-3.jpeg 200 - Mozilla/5.0[...]

Para permitir que Athena lea estos datos, puede ejecutar una instrucción directa CREATE EXTERNAL TABLE como la siguiente. La instrucción que crea la tabla define las columnas que se mapean a los datos. Además, especifica cómo se delimitan los datos y determina la ubicación de Amazon S3 que contiene los datos de muestra. Tenga en cuenta que, dado que Athena espera escanear todos los archivos de una carpeta, la cláusula LOCATION especifica una ubicación de carpeta de Amazon S3, no un archivo específico.

No utilice este ejemplo todavía, ya que tiene una limitación importante que se explicará en breve.

CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_logs (
  `Date` DATE,
  Time STRING,
  Location STRING,
  Bytes INT,
  RequestIP STRING,
  Method STRING,
  Host STRING,
  Uri STRING,
  Status INT,
  Referrer STRING,
  ClientInfo STRING
  ) 
  ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\t'
  LINES TERMINATED BY '\n'
  LOCATION 's3://athena-examples-my-region/cloudfront/plaintext/';

En el ejemplo, se crea una tabla llamada cloudfront_logs y se especifica un nombre y un tipo de datos para cada campo. Estos campos se convierten en las columnas de la tabla. Dado que date es una palabra reservada, se aplica escape con acentos graves (`). ROW FORMAT DELIMITEDsignifica que Athena utilizará una biblioteca predeterminada llamada LazySimpleSerDe para realizar el trabajo real de analizar los datos. En el ejemplo también se especifica que los campos están separados por tabulaciones (FIELDS TERMINATED BY '\t') y que cada registro en el archivo termina en un carácter de nueva línea (LINES TERMINATED BY '\n). Por último, la cláusula LOCATION especifica la ruta de acceso en Amazon S3 donde se encuentran los datos reales que se van a leer.

Si tiene sus propios datos separados por tabulaciones o comas, puede utilizar una instrucción CREATE TABLE como en el ejemplo anterior, siempre y cuando los campos no contengan información anidada. Sin embargo, si tiene una columna como ClientInfo, que contiene información anidada y utiliza un delimitador diferente, necesitará un enfoque diferente.

Extracción de datos del campo ClientInfo

Con respecto a los datos de muestra, aquí hay un ejemplo completo del campo final ClientInfo:

Mozilla/5.0%20(Android;%20U;%20Windows%20NT%205.1;%20en-US;%20rv:1.9.0.9)%20Gecko/2009040821%20IE/3.0.9

Como puede ver, este es un campo de múltiples valores. Debido a que la instrucción CREATE TABLE del ejemplo anterior especifica tabulaciones como delimitadores de campo, los componentes separados dentro del campo ClientInfo no se pueden dividir en columnas separadas. Por lo tanto, se requiere una nueva instrucción CREATE TABLE.

Puede utilizar una expresión regular (regex) que contenga grupos de regex para crear columnas a partir de los valores dentro del campo ClientInfo. Los grupos de expresiones regulares que especifique se convertirán en columnas de tabla independientes. Para usar una expresión regular en la instrucción CREATE TABLE, utilice una sintaxis como la siguiente. Esta sintaxis indica a Athena que utilice la biblioteca SerDe de Regex y la expresión regular que especifique.

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
  WITH SERDEPROPERTIES ("input.regex" = "regular_expression")

Las expresiones regulares pueden resultar útiles para crear tablas a partir de datos CSV o TSV complejos, pero pueden ser difíciles de escribir y mantener. Afortunadamente, existen otras bibliotecas que puede utilizar para formatos como JSON, Parquet y ORC. Para obtener más información, consulte Elección de un valor de SerDe para los datos.

Ahora ya puede crear la tabla en el editor de consultas de Athena. La instrucción CREATE TABLE y expresiones regulares se le proporcionan.