Como faço para criar e consultar uma tabela externa no Amazon Redshift Spectrum?

Resolução

Para consultar dados do Amazon Simple Storage Service (Amazon S3), você não precisa carregar dados nas tabelas do Amazon Redshift. Quando o Redshift Spectrum está processando consultas, os dados permanecem no seu bucket do S3.

Importante: certifique-se de que seu cluster do Amazon Redshift e o bucket do S3 estejam na mesma região da AWS.

Para criar uma tabela externa com o Redshift Spectrum, conclua as seguintes etapas:

1. Crie um perfil do AWS Identity and Access Management (AWS IAM) para o Amazon Redshift.

2. Anexe as seguintes políticas do IAM para conceder ao Amazon Redshift as permissões necessárias para acessar seu catálogo de dados: 
   Se você usa o AWS Glue Data Catalog, anexe as políticas IAM AmazonS3ReadOnlyAccess e AWSGlueConsoleFullAccess ao seu perfil.
   Se você usa o catálogo de dados do Amazon Athena, anexe a política do IAM do AmazonAthena FullAccess ao seu perfil.

3. Associe o perfil do IAM ao seu cluster do Amazon Redshift.

4. Crie o esquema externo.

5. Baixe os arquivos de dados e faça o upload deles em um bucket do S3 na sua região. O exemplo a seguir usa os arquivos de dados de amostra TICKIT para o S3:

   s3://<bucket_name>/tickit/spectrum/event/' and 's3://bucket_name/tickit/spectrum/sales/

   Observação: substitua bucket-name pelo nome do bucket do S3.

6. Crie uma tabela externa. O exemplo a seguir cria uma tabela externa para seus dados EVENT:

   create external table spectrum.event(
       eventid integer,
       venueid smallint,
       catid smallint,
       dateid smallint,
       eventname varchar(200),
       starttime timestamp
   )
   row format delimited
   fields terminated by '|'
   stored as textfile
   location 's3://bucket_name/tickit/spectrum/event/';

   Observação: substitua bucket-name pelo nome do bucket do S3. Para usar o AWS Glue para criar uma tabela externa, não se esqueça de adicionar definições de tabela ao seu catálogo de dados. 
   Para usar o Amazon Athena para criar uma tabela externa, adicione definições de tabela.
   Exemplos de definições de tabela com Athena:

   CREATE EXTERNAL TABLE spectrum.event (
         eventid int,
         venueid smallint,
         catid smallint,
         dateid smallint,
         eventname varchar(max),
         starttime timestamp)
       ROW FORMAT DELIMITED
         FIELDS TERMINATED BY '|'
       STORED AS INPUTFORMAT
         'org.apache.hadoop.mapred.TextInputFormat'
       OUTPUTFORMAT

7. Para visualizar todas as tabelas externas às quais seu esquema externo faz referência, execute a seguinte consulta para SVV_EXTERNAL_TABLES:

   select schemaname , tablename , location from svv_external_tables where schemaname = 'spectrum';
   
   schemaname | tablename | location
   ----------------+---------------------------------+-----------------------------------------------------------------------
   spectrum | event | s3://bucket-name/file-location

   Observação: substitua o nome do bucket pelo nome do bucket do S3 e o local do arquivo pelo local do arquivo.

8. Consulte as tabelas externas como tabelas externas do Redshift Spectrum. Use a instrução SELECIONAR:

   select top 3 spectrum.sales.eventid, sum(spectrum.sales.pricepaid) from spectrum.sales, spectrum.event
   where spectrum.sales.eventid = spectrum.event.eventid
   and spectrum.sales.pricepaid > 30
   group by spectrum.sales.eventid
   order by 2 desc;
   
    eventid |   sum
   ---------+----------
        289 | 51846.00
       7895 | 51049.00
       1602 | 50301.00

   O exemplo de consulta anterior une a tabela SALES externa com uma tabela EVENT externa.

Informações relacionadas

Amazon Redshift Spectrum

Solução de problemas de consultas no Amazon Redshift Spectrum