Práticas recomendadas ao usar o Athena com o AWS Glue - Amazon Athena
Nomes de bancos de dados, tabelas e colunasUsar crawlers do AWS GlueTrabalhar com arquivos CSVIndexação e filtragem de partições do AWS GlueTrabalhar com dados geoespaciaisUsar trabalhos do AWS Glue para ETL com o Athena

Práticas recomendadas ao usar o Athena com o AWS Glue

Ao usar o Athena com o AWS Glue Data Catalog, é possível usar o AWS Glue para criar bancos de dados e tabelas (esquema) para serem consultados no Athena ou usar o Athena para criar um esquema e usá-lo no AWS Glue e nos serviços relacionados. Este tópico apresenta considerações e melhores práticas durante o uso de um dos métodos.

Nos bastidores, o Athena usa o Trino para processar as instruções DML e o Hive para processar as instruções DDL que criam e modificam o esquema. Com essas tecnologias, existem algumas convenções que devem ser seguidas para que o Athena e o AWS Glue funcionem bem juntos.

Neste tópico

Nomes de bancos de dados, tabelas e colunas

Ao criar um esquema no AWS Glue para consulta no Athena, considere o seguinte:

  • Os caracteres aceitáveis para nomes de bancos de dados, nomes de tabelas e nomes de colunas no AWS Glue devem ser strings em UTF-8 e em minúsculas. Observe que o Athena reduz automaticamente muda para minúsculas todos os nomes em maiúsculas nas consultas DDL ao criar bancos de dados, tabelas ou colunas. A string não pode ter menos do que 1 ou mais de 255 bytes de comprimento. Os caracteres que podem ser usados incluem espaços.

  • Atualmente, é possível ter espaços iniciais no início dos nomes. Como pode ser difícil detectar esses espaços iniciais e eles podem causar problemas de usabilidade após a criação, evite inadvertidamente criar nomes de objetos que contenham espaços iniciais.

  • Se você usar um modelo AWS::Glue::Database do AWS CloudFormation para criar um banco de dados do AWS Glue e não especificar um nome de banco de dados, o AWS Glue gera automaticamente um nome de banco de dados no formato resource_name—random_string que não é compatível com o Athena.

  • Você pode usar o Gerenciador de Catálogos do AWS Glue para renomear colunas, mas não nomes de tabelas ou nomes de banco de dados. Para resolver essa limitação, é necessário usar uma definição do banco de dados antigo para criar um banco de dados com o novo nome. Em seguida, use as definições das tabelas do banco de dados antigo para recriar as tabelas no novo banco de dados. Para isso, você pode usar a AWS CLI ou o SDK do AWS Glue. Para obter as etapas, consulte Usar a AWS CLI para recriar um banco de dados do AWS Glue e suas tabelas.

Para obter mais informações sobre bancos de dados e tabelas no AWS Glue, consulte Bancos de dados e Tabelas no Guia do desenvolvedor do AWS Glue.

Usar crawlers do AWS Glue

Os crawlers do AWS Glue ajudam a descobrir o esquema para conjuntos de dados e registrá-los no catálogo de dados do AWS Glue. Os crawlers passam pelos dados e determinam o esquema. Além disso, o crawler pode detectar e registrar partições. Para obter mais informações, consulte Definir crawlers no Guia do desenvolvedor do AWS Glue. Tabelas de dados que foram rastreadas com sucesso podem ser consultadas no Athena.

nota

O Athena não reconhece os padrões de exclusão que você especifica para um crawler do AWS Glue. Por exemplo, se você tem um bucket do Amazon S3 com os arquivos .csv e .json e exclui os arquivos .json do crawler, o Athena consulta os dois grupos de arquivos. Para evitar isso, coloque os arquivos que você deseja excluir em um local diferente.

Programar um crawler para manter a sincronização entre o AWS Glue Data Catalog e o Amazon S3

Os crawlers do AWS Glue podem ser configurados para serem executados em uma programação ou sob demanda. Para obter mais informações, consulte Programações baseadas em hora para trabalhos e crawlers no Guia do desenvolvedor do AWS Glue.

Se você tiver dados que chegam a uma tabela particionada em um horário fixo, poderá configurar um crawler do AWS Glue para ser executado de acordo com a programação para detectar e atualizar as partições da tabela. Isso pode eliminar a necessidade de executar um comando MSCK REPAIR possivelmente longo e caro ou executar manualmente um comando ALTER TABLE ADD PARTITION. Para obter mais informações, consulte Partições de tabela no Guia do desenvolvedor do AWS Glue.

Usar várias origens de dados com crawlers

Quando um crawler do AWS Glue verifica o Amazon S3 e detecta vários diretórios, ele usa uma heurística para determinar onde a raiz de uma tabela está na estrutura do diretório e quais diretórios são partições da tabela. Em alguns casos, quando o esquema detectado em dois ou mais diretórios é semelhante, o crawler pode tratá-lo como partições, em vez de tabelas à parte. Uma maneira de ajudar o crawler a descobrir tabelas individuais é adicionar o diretório raiz de cada tabela como um armazenamento de dados para o crawler.

As seguintes partições no Amazon S3 são um exemplo:

s3://DOC-EXAMPLE-BUCKET/folder1/table1/partition1/file.txt
s3://DOC-EXAMPLE-BUCKET/folder1/table1/partition2/file.txt
s3://DOC-EXAMPLE-BUCKET/folder1/table1/partition3/file.txt
s3://DOC-EXAMPLE-BUCKET/folder1/table2/partition4/file.txt
s3://DOC-EXAMPLE-BUCKET/folder1/table2/partition5/file.txt

Se o esquema de table1 e table2 for semelhante, e uma única origem dos dados for definida como s3://DOC-EXAMPLE-BUCKET/folder1/ no AWS Glue, o crawler poderá criar uma única tabela com duas colunas de partição: uma com table1 e table2 e outra com partition1 a partition5.

Para fazer com que o crawler do AWS Glue crie duas tabelas separadas, defina o crawler para ter duas fontes de dados, s3://DOC-EXAMPLE-BUCKET/folder1/table1/ e s3://DOC-EXAMPLE-BUCKET/folder1/table2, conforme mostrado no procedimento a seguir.

Para adicionar um armazenamento de dados do S3 a um crawler existente no AWS Glue

  1. Faça login no AWS Management Console e abra o console do AWS Glue em https://console.aws.amazon.com/glue/.

  2. No painel de navegação, escolha Rastreadores.

  3. Escolha o link para o seu crawler e, em seguida, escolha Edit (Editar).

  4. Em Step 2: Choose data sources and classifiers (Etapa 2: Escolher fontes de dados e classificadores), escolha Edit(Editar).

  5. Em Data sources (Fontes de dados), escolha Add a data source (Adicionar uma fonte de dados).

  6. Na caixa de diálogo Add data source (Adicionar fonte de dados), em S3 path (Caminho do S3), escolha Browse (Procurar).

  7. Escolha o bucket que deseja usar e, em seguida, escolha Choose (Escolher).

    A fonte de dados que você adicionou aparece na lista Data sources (Fontes de dados).

  8. Escolha Próximo.

  9. Na página Configure security settings (Definir configurações de segurança), crie ou escolha um perfil do IAM para o crawler e, em seguida, escolha Next (Próximo).

  10. Certifique-se de que o caminho do S3 termine em uma barra à direita e, em seguida, escolha Add an S3 data source (Adicionar uma fonte de dados do S3).

  11. Na página Set output and scheduling (Definir saída e programação), em Output configuration (Configuração da saída), escolha o banco de dados de destino.

  12. Escolha Próximo.

  13. Na página Review and update (Revisar e atualizar), revise as escolhas feitas. Para editar uma etapa, escolha Edit (Editar).

  14. Selecione Atualizar.

Sincronizar o esquema da partição para evitar “HIVE_PARTITION_SCHEMA_MISMATCH”

Para cada tabela no AWS Glue Data Catalog que tenha colunas de partição, o esquema é armazenado no nível de tabela e para cada partição individual dentro da tabela. O esquema de partições é preenchido por um crawler do AWS Glue com base no exemplo de dados lido dentro da partição. Para ter mais informações, consulte Usar várias origens de dados com crawlers.

Quando o Athena executa uma consulta, ela valida o esquema da tabela e o esquema de todas as partições necessárias para a consulta. A validação compara os tipos de dados da coluna em ordem e verifica se eles correspondem às colunas que se sobrepõem. Isso evita operações inesperadas, como adicionar ou remover colunas no meio de uma tabela. Se o Athena detectar que o esquema de uma partição é diferente do esquema da tabela, o Athena talvez não possa processar a consulta e emita uma falha com HIVE_PARTITION_SCHEMA_MISMATCH.

Existem algumas maneiras de corrigir esse problema. Primeiro, se os dados tiverem sido adicionados acidentalmente, você poderá remover os arquivos de dados que causam a diferença no esquema, ignorar a partição e rastrear novamente os dados. Segundo, você pode descartar a partição individual e executar MSCK REPAIR no Athena para recriá-la usando o esquema da tabela. Essa segunda opção só funcionará se você tiver certeza de que o esquema aplicado continuará lendo os dados corretamente.

Atualizar metadados de tabelas

Depois de um rastreamento, o crawler do AWS Glue atribui automaticamente determinados metadados para ajudar a torná-los compatíveis com outras tecnologias externas, como Apache Hive, Presto e Spark. Às vezes, o crawler pode atribuir incorretamente propriedades de metadados. Corrija manualmente as propriedades no AWS Glue antes de consultar a tabela usando o Athena. Para obter mais informações, consulte Exibir e editar detalhes da tabela no Guia do desenvolvedor do AWS Glue.

O AWS Glue pode atribuir indevidamente metadados quando um arquivo CSV tem aspas em torno de cada campo de dados, processando a propriedade serializationLib incorretamente. Para ter mais informações, consulte Dados CSV entre aspas.

Trabalhar com arquivos CSV

Às vezes, os arquivos CSV têm aspas em valores de dados destinados a cada coluna e talvez haja valores de cabeçalho incluídos em arquivos CSV, que não fazem parte dos dados a serem analisados. Ao usar o AWS Glue para criar um esquema com base nesses arquivos, siga as orientações desta seção.

Dados CSV entre aspas

Você pode ter um arquivo CSV com campos de dados entre aspas duplas, como no seguinte exemplo:

"John","Doe","123-555-1231","John said \"hello\""
"Jane","Doe","123-555-9876","Jane said \"hello\""

Para executar uma consulta no Athena em uma tabela criada com base em um arquivo CSV que tenha valores entre aspas, você deve modificar as propriedades da tabela no AWS Glue para usar o OpenCSVSerDe. Para obter mais informações sobre o OpenCSV SerDe, consulte OpenCSVSerDe para processar CSV.

Para editar as propriedades da tabela no console do AWS Glue

  1. No painel de navegação do console do AWS Glue, escolha Tables.

  2. Escolha o link da tabela que deseja editar e, em seguida, escolha Actions (Ações) e Edit table (Editar tabela).

  3. Na página Edit table (Editar tabela), faça as seguintes alterações:

    • Em Serialization lib (Biblioteca de serialização), insira org.apache.hadoop.hive.serde2.OpenCSVSerde.

    • Em Serde parameters (Parâmetros do Serde), insira os seguintes valores para as chaves escapeChar, quoteChar e separatorChar:

      • Em escapeChar, insira uma barra invertida (\).

      • Em quoteChar, insira aspas duplas (").

      • Em separatorChar, insira uma vírgula (,).

  4. Escolha Salvar.

Para obter mais informações, consulte Exibir e editar detalhes da tabela no Guia do desenvolvedor do AWS Glue.

Atualizar as propriedades da tabela do AWS Glue de forma programática

Você pode usar a operação da API AWS Glue UpdateTable ou o comando da CLI update-table para modificar o bloco SerDeInfo na definição da tabela, conforme mostrado no exemplo de JSON a seguir.

"SerDeInfo": {
   "name": "",
   "serializationLib": "org.apache.hadoop.hive.serde2.OpenCSVSerde",
   "parameters": {
      "separatorChar": ","
      "quoteChar": "\""
      "escapeChar": "\\"
      }
},

Arquivos CSV com cabeçalhos

Quando você define uma tabela no Athena com uma instrução CREATE TABLE, pode usar a propriedade de tabela skip.header.line.count para ignorar os cabeçalhos nos dados CSV, conforme mostrado no exemplo a seguir.

...
STORED AS TEXTFILE
LOCATION 's3://DOC-EXAMPLE-BUCKET/csvdata_folder/';
TBLPROPERTIES ("skip.header.line.count"="1")

Se preferir, remova os cabeçalhos CSV com antecedência para que as informações do cabeçalho não sejam incluídas nos resultados da consulta do Athena. Uma maneira de fazer isso é usar os trabalhos do AWS Glue, que excutam o trabalho de Extract, Transform, and Load (ETL - Extração, transformação e carga). Você pode escrever scripts no AWS Glue usando uma linguagem que é uma extensão do dialeto PySpark Python. Para obter mais informações, consulte Criação de trabalhos no AWS Glue no Guia do desenvolvedor do AWS Glue.

O exemplo a seguir mostra uma função em um script do AWS Glue que escreve um quadro dinâmico usando from_options e define a opção de formato writeHeader como falsa, o que remove as informações do cabeçalho:

glueContext.write_dynamic_frame.from_options(frame = applymapping1, connection_type = "s3", connection_options = {"path": "s3://DOC-EXAMPLE-BUCKET/MYTABLEDATA/"}, format = "csv", format_options = {"writeHeader": False}, transformation_ctx = "datasink2")

Indexação e filtragem de partições do AWS Glue

Quando o Athena consulta tabelas particionadas, ele recupera e filtra as partições de tabela disponíveis para o subconjunto relevante para a sua consulta. À medida que novos dados e partições são adicionados, mais tempo torna-se necessário para processar as partições, e o runtime da consulta pode aumentar. Se você tiver uma tabela com um grande número de partições que aumenta ao longo do tempo, considere o uso de indexação e filtragem de partições do AWS Glue. A indexação de partições permite que o Athena otimize o processamento das partições e melhore a performance das consulta em tabelas altamente particionadas. A configuração da filtragem de partições nas propriedades de uma tabela é um processo de duas etapas:

  1. Criar um índice de partição em AWS Glue.

  2. Habilitar a filtragem de partições para a tabela.

Criar um índice de partição

Para conhecer as etapas da criação de um índice de partição no AWS Glue, consulte Trabalhar com índices de partição no Guia do desenvolvedor do AWS Glue. Para saber quais são as limitações dos índices de partição no AWS Glue, consulte a seção Sobre índices de partição na mesma página.

Habilitar filtragem de partição

Para habilitar a filtragem de partição para a tabela, você deve definir uma nova propriedade de tabela no AWS Glue. Para conhecer as etapas da definição de propriedades de tabela no AWS Glue, consulte a página Configurar a projeção de partições. Quando você editar os detalhes da tabela no AWS Glue, adicione o seguinte par de chave-valor à seção Table properties (Propriedades de tabela):

  • Para Key (Chave), adicione partition_filtering.enabled

  • Para Value (Valor), adicione true

Você pode desabilitar a filtragem de partições nessa tabela a qualquer momento definindo o valor partition_filtering.enabled como false.

Depois de concluir as etapas acima, você pode retornar ao console do Athena para consultar os dados.

Para mais informações sobre usar a indexação e filtragem de partição, consulte Melhorar o desempenho de consultas Amazon Athena usando índices de partição AWS Glue Data Catalog no AWSBlog de Big Data.

Trabalhar com dados geoespaciais

O AWS Glue não oferece suporte nativo a Well-known Text (WKT – Texto bem conhecido), Well-Known Binary (WKB – Binário bem conhecido) ou outros tipos de dados PostGIS. O classificador do AWS Glue analisa dados geoespaciais e os classifica usando tipos de dados compatíveis para o formato, como varchar para CSV. Assim como ocorre com outras tabelas do AWS Glue, pode ser necessário atualizar as propriedades das tabelas criadas a partir de dados geoespaciais para permitir que o Athena analise esses tipos de dados no estado em que se encontram. Para ter mais informações, consulte Usar crawlers do AWS Glue e Trabalhar com arquivos CSV. O Athena pode não ser capaz de analisar alguns tipos de dados geoespaciais em tabelas do AWS Glue no estado em que se encontram. Para obter mais informações sobre como trabalhar com dados geoespaciais no Athena, consulte Consultar dados geoespaciais.

Usar trabalhos do AWS Glue para ETL com o Athena

Os trabalhos do AWS Glue realizam operações de ETL. Um trabalho do AWS Glue executa um script que extrai dados de fontes, transforma os dados e os carrega em destinos. Para obter mais informações, consulte Criação de trabalhos no AWS Glue no Guia do desenvolvedor do AWS Glue.

Criar tabelas usando o Athena para trabalhos ETL do AWS Glue

As tabelas que você cria no Athena devem ter uma propriedade de tabela adicionada chamada classification, que identifica o formato dos dados. Isso permite que o AWS Glue use as tabelas para trabalhos de ETL. Os valores de classificação podem ser avro, csv, json, orc, parquet ou xml. Veja abaixo um exemplo da instrução CREATE TABLE no Athena:

CREATE EXTERNAL TABLE sampleTable (
  column1 INT,
  column2 INT
  ) STORED AS PARQUET
  TBLPROPERTIES (
  'classification'='parquet')

Se a propriedade da tabela não tiver sido adicionada quando a tabela foi criada, a propriedade poderá ser adicionada com o console do AWS Glue.

Para adicionar a propriedade de classificação da tabela usando o console do AWS Glue

  1. Faça login no AWS Management Console e abra o console do AWS Glue em https://console.aws.amazon.com/glue/.

  2. No painel de navegação do console, escolha Tables (Tabelas).

  3. Escolha o link da tabela que deseja editar e, em seguida, escolha Actions (Ações) e Edit table (Editar tabela).

  4. Role para baixo até a seção Table properties (Propriedades da tabela).

  5. Escolha Adicionar.

  6. Em Chave, digite classification.

  7. Em Value (Valor), insira um tipo de dado (por exemplo, json).

  8. Escolha Salvar.

    Na seção Table details (Detalhes da tabela), o tipo de dado que você inseriu aparece no campo Classification (Classificação) da tabela.

Para obter mais informações, consulte Trabalhar com tabelas no Guia do desenvolvedor do AWS Glue.

Com usar trabalhos de ETL para otimizar a performance da consulta

Os trabalhos do AWS Glue podem ajudar a transformar os dados em um formato que otimiza a performance das consultas no Athena. Os formatos de dados têm um grande impacto na performance e nos custos das consultas no Athena.

É recomendável usar os formatos de dados Parquet e ORC. O AWS Glue permite a gravação nesses dois formatos de dados, o que pode facilitar e agilizar a transformação de dados em um formato ideal para o Athena. Para obter mais informações sobre esses formatos e outras maneiras de melhorar o desempenho, leia Principais dicas para ajuste do desempenho do Amazon Athena.

Converter tipos de dados SMALLINT e TINYINT em INT ao converter em ORC

Para reduzir as chances de o Athena não conseguir ler os tipos de dados SMALLINT e TINYINT produzidos por um trabalho de ETL do AWS Glue, converta SMALLINT e TINYINT em INT ao usar o assistente ou ao escrever um script para um trabalho de ETL.

Automatizar trabalhos do AWS Glue para ETL

Você pode configurar trabalhos de ETL do AWS Glue para serem executados automaticamente com base em gatilhos. Esse recurso é ideal quando há dados de fora da AWS sendo enviados para um bucket do Amazon S3 em um formato inadequado para consultas no Athena. Para obter mais informações, consulte Iniciar trabalhos do AWS Glue usando gatilhos no Guia do desenvolvedor do AWS Glue.