Etapa 2: Criar uma tabela

Agora que você tem um banco de dados, pode criar uma tabela do Athena para ele. A tabela criada será baseada nos dados de log de exemplo do Amazon CloudFront no local s3://athena-examples-myregion/cloudfront/plaintext/, em que myregion é a sua Região da AWS atual.

Os dados de log de exemplo estão no formato Tab-Separated Values (TSV – Valores separados por tabulação), o que significa que um caractere de tabulação é usado como delimitador para separar os campos. Veja no exemplo abaixo a aparência dos dados. Para legibilidade, as guias no trecho foram convertidas em espaços e o campo final foi reduzido.

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 o Athena leia esses dados, você pode criar uma instrução CREATE EXTERNAL TABLE direta, semelhante à apresentada abaixo. A instrução que cria a tabela define as colunas que são mapeadas para os dados, especifica como os dados são delimitados e o local do Amazon S3 que contém os dados de exemplo. Observe que, como o Athena espera fazer a varredura de todos os arquivos em uma pasta, a cláusula LOCATION especifica um local de pasta do Amazon S3 e não um arquivo específico.

Não use esse exemplo ainda, pois ele tem uma limitação importante que será explicada em 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/';

O exemplo cria uma tabela chamada cloudfront_logs e especifica um nome e tipo de dados para cada campo. Esses campos se tornam as colunas da tabela. Como date é uma palavra reservada, ela é escapada com caracteres de acento grave (`). ROW FORMAT DELIMITED significa que o Athena usará uma biblioteca padrão chamada LazySimpleSerDe para fazer o trabalho real de análise dos dados. O exemplo também especifica que os campos são separados por tabulação (FIELDS TERMINATED BY '\t') e que cada registro no arquivo termina com um caractere de nova linha (LINES TERMINATED BY '\n). Por fim, a cláusula LOCATION especifica o caminho no Amazon S3 onde estão localizados os dados reais que serão lidos.

Se tiver seus próprios dados separados por tabulação ou vírgula, você poderá usar uma instrução CREATE TABLE como a do exemplo que acabamos de apresentar, desde que seus campos não contenham informações aninhadas. No entanto, se você tiver uma coluna como ClientInfo, contendo informações aninhadas que usem um delimitador diferente, será necessário adotar outra abordagem.

Como extrair dados do campo ClientInfo

Observando os dados do exemplo, aqui está um exemplo completo da situação final do campo 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 você pode ver, esse é um campo multivalor. Como a instrução CREATE TABLE do exemplo apresentado especifica guias como delimitadores de campo, ela não pode decompor em colunas separadas componentes separados dentro do campo ClientInfo. Portanto, é necessário empregar uma nova instrução CREATE TABLE.

Para criar colunas com base nos valores dentro do campo ClientInfo, você pode usar uma expressão regular (regex) que contenha grupos de regex. Os grupos de regex especificados tornam-se as colunas separadas da tabela. Para usar uma regex na instrução CREATE TABLE, use a sintaxe conforme mostrado a seguir. Essa sintaxe instrui o Athena a usar a biblioteca Regex SerDe e a expressão regular que você especificar.

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

As expressões regulares podem ser úteis para criar tabelas com base em dados complexos CSV ou TSV, mas podem ser difíceis de escrever e manter. No entanto, há outras bibliotecas que você pode usar para formatos como JSON, Parquet e ORC. Para ter mais informações, consulte Escolha de um SerDe para seus dados.

Agora você está pronto para criar a tabela no editor de consultas do Athena. A instrução CREATE TABLE e a regex já foram incluídas para você.