Observações de uso

Este tópico contém notas de uso do CREATE EXTERNAL TABLE. Não é possível exibir os detalhes das tabelas do Amazon Redshift Spectrum usando os mesmos recursos das tabelas padrão do Amazon Redshift, como PG_TABLE_DEF, STV_TBL_PERM, PG_CLASS, ou information_schema. Caso sua ferramenta de business intelligence ou análise não reconheça as tabelas externas do Redshift Spectrum, configure sua aplicação para consultar SVV_EXTERNAL_TABLES e SVV_EXTERNAL_COLUMNS.

CREATE EXTERNAL TABLE AS

Em alguns casos, é possível executar o comando CREATE EXTERNAL TABLE AS em um catálogo de dados do AWS Glue, em um catálogo externo do AWS Lake Formation ou em um metastore do Apache Hive. Nesses casos, use uma função do AWS Identity and Access Management (IAM) para criar o esquema externo. Essa função do IAM deve conter as permissões de leitura e gravação no Amazon S3.

Se você usar um catálogo do Lake Formation, a função do IAM deve ter a permissão para criar tabelas no catálogo. Nesse caso, ela também deve ter a permissão do local do data lake no caminho de destino do Amazon S3. Essa função do IAM se torna proprietária da nova tabela do AWS Lake Formation.

Para garantir que os nomes dos arquivos sejam únicos, o Amazon Redshift usa o seguinte formato para o nome de cada arquivo carregado no Amazon S3 por padrão.

<date>_<time>_<microseconds>_<query_id>_<slice-number>_part_<part-number>.<format>.

Um exemplo é 20200303_004509_810669_1007_0001_part_00.parquet.

Considere o seguinte ao executar o comando CREATE EXTERNAL TABLE AS:

• O local do Amazon S3 deve estar vazio.

• O Amazon Redshift oferece suporte somente aos formatos PARQUET e TEXTFILE ao usar a cláusula STORED AS.

• Não é necessário criar uma lista de definições de coluna. Os nomes de colunas e os tipos de dados de coluna da nova tabela externa são derivados diretamente da consulta SELECT.

• Não é necessário definir o tipo de dados da coluna de partição na cláusula PARTITIONED BY. Se você especificar uma chave de partição, o nome dessa coluna deverá existir no resultado da consulta SELECT. Ao ter várias colunas de partição, a ordem na consulta SELECT não importa. O Amazon Redshift usa a ordem definida na cláusula PARTITIONED BY para criar a tabela externa.

• O Amazon Redshift automaticamente particiona arquivos de saída em pastas de partição com base nas chaves-valor de partição. Por padrão, o Amazon Redshift remove as colunas de partição dos arquivos de saída.

• Não há suporte para a cláusula LINES TERMINATED BY 'delimiter'.

• Não há suporte para a cláusula ROW FORMAT SERDE 'serde_name'.

• Não há suporte para o uso de arquivos de manifesto. Dessa forma, não é possível definir a cláusula LOCATION em um arquivo manifesto no Amazon S3.

• O Amazon Redshift automaticamente atualiza a propriedade da tabela “numRows” no final do comando.

• A propriedade da tabela 'compression_type' aceita somente 'none' ou 'snappy' para o formato de arquivo PARQUET.

• O Amazon Redshift não permite a cláusula LIMIT na consulta SELECT externa. Em vez disso, você pode usar uma cláusula LIMIT aninhada.

• É possível usar STL_UNLOAD_LOG para rastrear os arquivos que são gravados no Amazon S3 por cada operação CREATE EXTERNAL TABLE AS.

Permissões para criar e consultar tabelas externas

Para criar tabelas externas, verifique se você é o proprietário do esquema externo ou um superusuário. Para transferir a propriedade de um esquema externo, use ALTER SCHEMA. O exemplo a seguir altera o proprietário do esquema spectrum_schema para newowner.

alter schema spectrum_schema owner to newowner;

Para executar uma consulta do Redshift Spectrum, você precisa das seguintes permissões:

• Permissão de uso no esquema

• Permissão para criar tabelas temporárias no banco de dados atual

O exemplo a seguir concede permissão de uso no esquema spectrum_schema para o grupo de usuários spectrumusers.

grant usage on schema spectrum_schema to group spectrumusers;

O exemplo a seguir concede permissão temporária no banco de dados spectrumdb para o grupo de usuários spectrumusers.

grant temp on database spectrumdb to group spectrumusers;

Pseudocolunas

Por padrão, o Amazon Redshift cria tabelas externas com as pseudocolunas $path e $size. Selecione essas colunas para exibir o caminho que levará aos arquivos de dados no Amazon S3 e o tamanho dos arquivos de dados em cada linha retornada por uma consulta. Os nomes de coluna $path e $size devem ser delimitados por aspas duplas. A cláusula SELECT * não retornará as pseudocolunas. Você deve incluir explicitamente os nomes de coluna $path e $size na consulta, como mostra o exemplo a seguir.

select "$path", "$size"
from spectrum.sales_part
where saledate = '2008-12-01';

Você pode desabilitar a criação de pseudocolunas em uma sessão. Basta definir o parâmetro de configuração como false.

Importante

A seleção de $size ou $path gera cobranças porque o Redshift Spectrum verifica os arquivos de dados no Amazon S3 para determinar o tamanho do conjunto de resultados. Para obter mais informações, consulte Preço do Amazon Redshift.

Definir opções de tratamento de dados

Você pode definir parâmetros de tabela para especificar o tratamento de entrada para dados que estão sendo consultados em tabelas externas, incluindo:

• Caracteres excedentes em colunas contendo VARCHAR, CHAR e dados em string. Para obter mais informações, consulte a propriedade de tabela externa surplus_char_handling.

• Caracteres inválidos em colunas contendo VARCHAR, CHAR e dados em string. Para obter mais informações, consulte a propriedade de tabela externa invalid_char_handling.

• Caractere de substituição a ser usado quando você especificar REPLACE como a propriedade de tabela externa invalid_char_handling.

• Tratamento de transbordamento de conversão em colunas contendo dados inteiros e decimais. Para obter mais informações, consulte a propriedade de tabela externa numeric_overflow_handling.

• Surplus_bytes_handling para especificar o tratamento de entradas para bytes excedentes em colunas que contêm dados varbyte. Para obter mais informações, consulte a propriedade de tabela externa surplus_bytes_handling.