Introducción

Este tutorial le mostrará cómo utilizar Amazon Athena para consultar datos. Creará una tabla basada en datos de muestra almacenados en Amazon Simple Storage Service, la consultará y verificará los resultados de la consulta.

El tutorial utiliza recursos activos, por lo que se le cobrará por las consultas que ejecute. No se le cobrará por los datos de muestra en la ubicación que utiliza este tutorial, pero si carga sus propios archivos de datos a Amazon S3, sí se aplican cargos.

Requisitos previos

• Si aún no lo ha hecho, regístrese para conseguir una Cuenta de AWS.

• Con la misma cuenta y Región de AWS (por ejemplo, Oeste de EE. UU. [Oregón]) que utiliza para Athena, siga los pasos para crear un bucket en Amazon S3 a fin de retener los resultados de las consultas de Athena. Configurará este bucket para que sea la ubicación de salida de consultas.

Paso 1: Crear una base de datos

Primero debe crear una base de datos en Athena.

Para crear una base de datos de Athena

1. Abra la consola de Athena en https://console.aws.amazon.com/athena/.

2. Si es la primera vez que visita la consola de Athena en su Región de AWS actual, elija Explore the query editor (Explorar el editor de consultas) para abrir el editor de consultas. De lo contrario, Athena abre la consulta en el editor de consultas.

3. Elija Edit Settings (Editar configuración) para configurar una ubicación de resultados de la consulta en Amazon S3.

   

4. En Manage settings (Administrar configuración), realice una de las siguientes operaciones:

   • En el cuadro Location of query result (Ubicación del resultado de la consulta), ingrese la ruta de acceso al bucket que creó en Amazon S3 para los resultados de la consulta. Prefije la ruta con s3://.

   • Elija Browse S3 (Navegar S3), elija el bucket de Amazon S3 que creó para su región actual y, a continuación, elija Choose (Elegir).

   

5. Elija Guardar.

6. Elija Editor para cambiar al editor de consultas.

   

7. A la derecha del panel de navegación, puede utilizar el editor de consultas de Athena para ingresar y ejecutar consultas e instrucciones.

   

8. Para crear una base de datos denominada mydatabase, escriba la siguiente instrucción CREATE DATABASE.

   CREATE DATABASE mydatabase

9. Elija Run (Ejecutar) o pulse Ctrl+ENTER.

10. En la lista Database (Base de datos) de la izquierda, elija mydatabase para convertirla en su base de datos actual.

    

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 Formatos de datos y SerDes compatibles.

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

Para crear una tabla en Athena

1. En el panel de navegación, en Database (Base de datos), asegúrese de que mydatabase esté seleccionado.

2. Para obtener más espacio en el editor de consultas, puede elegir el icono de flecha para contraer el panel de navegación.

   

3. Para crear una pestaña para una nueva consulta, elija el signo más (+) en el editor de consultas. Puede tener hasta diez pestañas de consulta abiertas a la vez.

   

4. Para cerrar una o varias pestañas de consultas, elija la flecha situada junto al signo más. Para cerrar todas las pestañas a la vez, elija la flecha y, a continuación, elija Close all tabs (Cerrar todas las pestañas).

   

5. En el panel de consultas, escriba la siguiente instrucción CREATE EXTERNAL TABLE. La expresión regular desglosa la información del sistema operativo, el navegador y la versión del navegador del campo ClientInfo en los datos del registro.

   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,
     os STRING,
     Browser STRING,
     BrowserVersion STRING
     ) 
     ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
     WITH SERDEPROPERTIES (
     "input.regex" = "^(?!#)([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+[^\(]+[\(]([^\;]+).*\%20([^\/]+)[\/](.*)$"
     ) LOCATION 's3://athena-examples-myregion/cloudfront/plaintext/';

6. En la instrucción LOCATION, reemplace myregion por la Región de AWS que utiliza actualmente (por ejemplo, us-west-1).

7. Elija Ejecutar.

   Se crea la tabla cloudfront_logs y aparece debajo de la lista Tables (Tablas) de la base de datos mydatabase.

Paso 3: Consultar los datos

Ahora que ha creado la tabla cloudfront_logs en Athena a partir de los datos en Amazon S3, puede ejecutar consultas SQL en la tabla y ver los resultados en Athena. Para obtener más información acerca del uso de SQL en Athena, consulte Referencia de SQL para Athena.

Para ejecutar una consulta

1. Elija el signo más (+) para abrir una nueva pestaña de consulta e ingrese la siguiente instrucción SQL en el panel de consultas.

   SELECT os, COUNT(*) count 
   FROM cloudfront_logs 
   WHERE date BETWEEN date '2014-07-05' AND date '2014-08-05' 
   GROUP BY os

2. Elija Ejecutar.

   Los resultados tendrán el siguiente aspecto:

   

3. Para guardar los resultados de una consulta en un archivo .csv, elija Download results (Descargar resultados).

   

4. Para ver o ejecutar consultas anteriores, elija la pestaña Recent queries (Consultas recientes).

   

5. Para descargar los resultados de una consulta anterior desde la pestaña Recent queries (Consultas recientes), seleccione la consulta y, a continuación, elija Download results (Descargar resultados). Las consultas se retienen durante 45 días.

   

6. Para descargar una o más cadenas de consultas SQL recientes como un archivo CSV, elija Download CSV (Descargar CSV).

   

   Para obtener más información, consulte Trabajo con resultados de las consultas, consultas recientes y archivos de salida.

Guardar las consultas

Puede guardar las consultas que cree o edite en el editor de consultas con un nombre. Athena almacena estas consultas en la pestaña Saved queries (Consultas guardadas). Puede utilizar la pestaña Saved queries (Consultas guardadas) para recuperar, ejecutar, cambiarles el nombre o eliminar las consultas guardadas. Para obtener más información, consulte Uso de consultas guardadas.

Atajos de teclado y sugerencias de escritura anticipada

El editor de consultas de Athena ofrece numerosos atajos de teclado para realizar acciones como ejecutar una consulta, formatear una consulta y efectuar operaciones de línea y buscar y reemplazar. Para obtener más información y una lista completa de atajos, consulte Improve productivity by using keyboard shortcuts in Amazon Athena query editor (Mejorar la productividad mediante el uso de atajos de teclado en el editor de consultas de Amazon Athena) en el blog sobre macrodatos de AWS.

El editor de consultas de Athena admite sugerencias de código de escritura anticipada para una experiencia de creación de consultas más rápida. Para que pueda escribir consultas SQL con mayor precisión y eficiencia, ofrece las siguientes características:

• A medida que escribe, aparecen sugerencias en tiempo real para palabras clave, variables locales, fragmentos y elementos del catálogo.

• Al escribir el nombre de una base de datos o de una tabla seguido de un punto, el editor muestra de forma oportuna una lista de tablas o columnas entre las que puede elegir.

• Al pasar el ratón por encima de una sugerencia de fragmento, aparece una sinopsis que muestra un breve resumen de la sintaxis y del uso del fragmento.

• Para mejorar la legibilidad del código, también se actualizaron las palabras clave y sus reglas de resaltado a fin de adaptarlas a la sintaxis más reciente de Trino y Hive.

Esta característica está habilitada de forma predeterminada. Para habilitar o deshabilitar la característica, utilice las preferencias del editor de código (icono con forma de engranaje) en la parte inferior derecha de la ventana del editor de consultas.

Conexión con otros orígenes de datos

En este tutorial se utilizó un origen de datos de Amazon S3 en formato CSV. Para obtener información sobre el uso de Athena con AWS Glue, consulte Uso de AWS Glue para conectarse a orígenes de datos en Amazon S3. También puede conectar Athena a distintos orígenes de datos mediante controladores ODBC y JDBC, metaalmacenes externos de Hive y conectores de origen de datos de Athena. Para obtener más información, consulte Conexión con orígenes de datos.