CETAS (CREATE EXTERNAL TABLE AS SELECT) (Transact-SQL)

  • Artigo

Aplica-se a: SQL Server 2022 (16.x) e posterior Azure Synapse Analytics Analytics Platform System (PDW)

Cria uma tabela externa e exporta, em paralelo, os resultados de uma instrução SELECT do Transact-SQL.

  • Azure Synapse Analytics e Analytics Platform System oferecem suporte para o Hadoop ou o Armazenamento de Blobs do Azure.
  • O SQL Server 2022 (16.x) e versões anteriores oferecem suporte CETAS (CREATE EXTERNAL TABLE AS SELECT) para criar uma tabela externa e exportar, em paralelo, o resultado de uma instrução SELECT do Transact-SQL para ADLS (Azure Data Lake Storage) Gen2, Conta de Armazenamento do Azure V2 e armazenamento de objetos compatíveis com S3.

Observação

Os recursos e a segurança do CETAS para Instância Gerenciada de SQL do Azure são diferentes do SQL Server ou Azure Synapse Analytics. Para obter mais informações, confira a versão da Instância Gerenciada de SQL do Azure de CREATE EXTERNAL TABLE AS SELECT.

Observação

Os recursos e a segurança do CETAS para pools sem servidor no Azure Synapse Analytics são diferentes dos SQL Server. Para obter mais informações, confira CETAS com SQL do Synapse.

Convenções de sintaxe de Transact-SQL

Sintaxe

CREATE EXTERNAL TABLE { [ [ database_name . [ schema_name ] . ] | schema_name . ] table_name }
    [ (column_name [ , ...n ] ) ]
    WITH (
        LOCATION = 'hdfs_folder' | '<prefix>://<path>[:<port>]' ,
        DATA_SOURCE = external_data_source_name ,
        FILE_FORMAT = external_file_format_name
        [ , <reject_options> [ , ...n ] ]
    )
    AS <select_statement>
[;]

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage
    | REJECT_VALUE = reject_value
    | REJECT_SAMPLE_VALUE = reject_sample_value
}

<select_statement> ::=
    [ WITH <common_table_expression> [ , ...n ] ]
    SELECT <select_criteria>

Argumentos

[ [ database_name. [ schema_name ]. ] | schema_name . ] table_name

O nome de uma a três partes da tabela a ser criado no banco de dados. Em uma tabela externa, o banco de dados relacional armazena apenas os metadados da tabela.

[ ( column_name [ ,...n ] ) ]

O nome de uma coluna da tabela.

LOCALIZAÇÃO

Aplica-se a: Azure Synapse Analytics e Analytics Platform System

'hdfs_folder'**
Especifica o local em que gravar os resultados da instrução SELECT na fonte de dados externa. O local é um nome de pasta e pode incluir um caminho relativo à pasta raiz do cluster Hadoop ou Armazenamento de Blobs. O PolyBase criará o caminho e a pasta se ainda não existirem.

Os arquivos externos são gravados em hdfs_folder e nomeados QueryID_date_time_ID.format, em que ID é um identificador incremental e format é o formato de dados exportados. Um exemplo é QID776_20160130_182739_0.orc.

LOCATION deve apontar para uma pasta e ter / à direita, por exemplo: aggregated_data/.

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores

prefix://path[:port] fornece o protocolo de conectividade (prefixo), o caminho e, opcionalmente, a porta, para a fonte de dados externa na qual o resultado da instrução SELECT será gravado.

Se o destino for um armazenamento de objeto compatível com S3, um bucket deverá existir primeiro, mas o PolyBase poderá criar subpastas, se necessário. O SQL Server 2022 (16.x) dá suporte ao Azure Data Lake Storage Gen2, à conta de Armazenamento do Azure V2 e ao armazenamento de objetos compatível com S3. No momento, não há suporte para arquivos ORC.

DATA_SOURCE = external_data_source_name

Especifica o nome do objeto de fonte de dados externa que contém o local em que os dados externos são ou serão armazenados. O local é um cluster Hadoop ou um armazenamento de Blob do Azure. Para criar uma fonte de dados externa, use CREATE EXTERNAL DATA SOURCE (Transact-SQL).

FILE_FORMAT = external_file_format_name

Especifica o nome do objeto de formato de arquivo externo que contém o formato do arquivo de dados externo. Para criar um formato de arquivo externo, use CREATE EXTERNAL FILE FORMAT (Transact-SQL).

opções REJECT

As opções REJECT não se aplicam no momento em que a instrução CREATE EXTERNAL TABLE AS SELECT é executada. Em vez disso, elas são especificadas aqui, de modo que o banco de dados possa usá-las mais tarde ao importar dados da tabela externa. Posteriormente, quando a instrução CREATE TABLE AS SELECT selecionar dados da tabela externa, o banco de dados usará as opções de rejeição para determinar o número ou percentual de linhas que podem falhar ao serem importadas antes que a importação seja interrompida.

  • REJECT_VALUE = reject_value

    Especifica o valor ou percentual de linhas que podem falhar ao serem importadas antes que o banco de dados interrompa a importação.

  • REJECT_TYPE = value | percentage

    Esclarece se a opção REJECT_VALUE é um valor literal ou uma porcentagem.

    • value

      Usado se REJECT_VALUE for um valor literal, não um percentual. O banco de dados interrompe a importação de linhas do arquivo de dados externo quando o número de linhas com falha exceder reject_value.

      Por exemplo, se REJECT_VALUE = 5 e REJECT_TYPE = value, o banco de dados para de importar linhas depois que a importação de cinco linhas falhar.

    • percentage

      Usado se REJECT_VALUE for um percentual, não um valor literal. O banco de dados interrompe a importação de linhas do arquivo de dados externo quando o percentual de linhas com falha exceder reject_value. O percentual de linhas com falha é calculado em intervalos. Válido somente em pools de SQL dedicados quando TYPE=HADOOP.

  • REJECT_SAMPLE_VALUE = reject_sample_value

    Obrigatório quando REJECT_TYPE = percentage. Isso especifica o número de linhas na tentativa de importação antes que o banco de dados calcule novamente o percentual de linhas com falha.

    Por exemplo, se REJECT_SAMPLE_VALUE = 1000, o banco de dados calculará o percentual de linhas com falha depois de tentar importar 1.000 linhas do arquivo de dados externo. Se o percentual de linhas com falha for menor que reject_value, o banco de dados tentará carregar outras 1000 linhas. O banco de dados continuará calculando novamente o percentual de linhas com falha depois de tentar importar cada 1.000 linhas adicionais.

    Observação

    Como o banco de dados calcula o percentual de linhas com falha em intervalos, o percentual real de linhas com falha pode exceder reject_value.

    Exemplo:

    Este exemplo mostra como as três opções REJECT interagem. Por exemplo, se REJECT_TYPE = percentage, REJECT_VALUE = 30, REJECT_SAMPLE_VALUE = 100, o seguinte cenário pode ocorrer:

    • O banco de dados tenta carregar as primeiras 100 linhas, das quais 25 falham e 75 são bem-sucedidas.
    • O percentual de linhas com falha é calculado como 25%, que é menor do que o valor de rejeição de 30%. Portanto, não há necessidade de interromper o carregamento.
    • O banco de dados tenta carregar as próximas 100 linhas. Desta vez, 25 foram bem-sucedidos e 75 falharam.
    • O percentual de linhas com falha é recalculado como 50%. O percentual de linhas com falha excedeu o valor de rejeição de 30%.
    • A carga falha com 50% de linhas com falha após a tentativa de carregar 200 linhas, que é maior que o limite de 30% especificado.

WITH common_table_expression

Especifica um conjunto de resultados nomeado temporário, conhecido como uma CTE (expressão de tabela comum). Para obter mais informações, confira WITH common_table_expression (Transact-SQL)

SELECT <select_criteria>

Popula a nova tabela com os resultados de uma instrução SELECT. select_criteria é o corpo da instrução SELECT que determina quais dados serão copiados para a nova tabela. Para obter informações sobre as instruções SELECT, confira SELECT (Transact-SQL).

Observação

A cláusula ORDER BY em SELECT não tem efeito sobre CETAS.

Opções de coluna

  • column_name [ ,...n ]

    Os nomes de coluna não permitem as opções de coluna mencionadas em CREATE TABLE. Nesse caso, você pode fornecer uma lista opcional de um ou mais nomes de coluna para a nova tabela. As colunas na nova tabela usam os nomes que você especificar. Quando você especificar nomes de coluna, o número de colunas na lista de colunas deverá corresponder ao número de colunas nos resultados de select. Se você não especificar nenhum nome de coluna, a nova tabela de destino usará os nomes de coluna nos resultados da instrução select.

    Não é possível especificar nenhuma outra opção de coluna, como tipos de dados, ordenação ou nulidade. Cada um desses atributos é derivado dos resultados da instrução SELECT. No entanto, você pode usar a instrução SELECT para alterar os atributos. Como exemplo, confira Usar CETAS para alterar atributos de coluna.

Permissões

Para executar esse comando, o usuário de banco de dados precisa de todas estas permissões ou associações:

  • Permissão ALTER SCHEMA no esquema local que conterá a nova tabela ou associação à função de banco de dados fixa db_ddladmin.
  • Permissão CREATE TABLE ou associação à função de banco de dados fixa db_ddladmin.
  • Permissão SELECT em todos os objetos referenciados no select_criteria.

O logon precisa de todas estas permissões:

  • ADMINISTER BULK OPERATIONS
  • ALTER ANY EXTERNAL DATA SOURCE
  • ALTER ANY EXTERNAL FILE FORMAT
  • Em geral, você precisa de permissões para Listar o conteúdo da pasta e Gravar o CETAS na pasta LOCATION.
  • No Azure Synapse Analytics e no Analytics Platform System, a permissão de Gravação para ler e gravar na pasta externa no cluster do Hadoop ou no armazenamento de Blob do Azure.
  • No SQL Server 2022 (16.x), também é necessário definir as permissões adequadas no local externo. Permissão de gravação para gerar os dados no local e permissão de leitura para acessá-los.
  • No caso do Armazenamento de Blobs do Azure e do Azure Data Lake Gen2, o token SHARED ACCESS SIGNATURE precisa receber os seguintes privilégios no contêiner: LeituraGravação, Listagem e Criação.
  • Para o armazenamento de Blobs do Azure, as caixas de seleção Allowed Services: Blob precisam estar marcadas para que o token SAS seja gerado.
  • Para o Azure Data Lake Gen2, as caixas de seleção Allowed Services: Container e Object precisam estar marcadas para que o token SAS seja gerado.

Importante

A permissão ALTER ANY EXTERNAL DATA SOURCE concede a qualquer entidade de segurança a capacidade de criar e modificar qualquer objeto de fonte de dados externa. Portanto, também concede a capacidade de acessar todas as credenciais com escopo de banco de dados no banco de dados. Essa permissão precisa ser considerada altamente privilegiada e deve ser concedida apenas a entidades confiáveis no sistema.

Tratamento de erros

Quando CREATE EXTERNAL TABLE AS SELECT exporta dados para um arquivo delimitado por texto, não há nenhum arquivo de rejeição para linhas com falha na exportação.

Quando você cria a tabela externa, o banco de dados tenta se conectar ao local externo. Se a conexão falhar, o comando falhará e a tabela externa não é criada. Pode levar alguns minutos ou mais para o comando falhar porque o banco de dados tenta estabelecer a conexão novamente pelo menos três vezes.

Se a instrução CREATE EXTERNAL TABLE AS SELECT for cancelada ou falhar, o banco de dados fará uma tentativa única de remover novos arquivos e pastas já criados na fonte de dados externa.

No Azure Synapse Analytics e no Analytics Platform System, o banco de dados relatará todos os erros de Java que ocorrerem na fonte de dados externa durante a exportação de dados.

Comentários

Após a conclusão da instrução CREATE EXTERNAL TABLE AS SELECT, você pode executar consultas Transact-SQL na tabela externa. Essas operações importam dados para o banco de dados durante a consulta, a menos que você faça a importação usando a instrução CREATE TABLE AS SELECT.

O nome da tabela externa e a definição são armazenados nos metadados do banco de dados. Os dados são armazenados na fonte de dados externa.

A instrução CREATE EXTERNAL TABLE AS SELECT sempre cria uma tabela não particionada, mesmo que a tabela de origem seja particionada.

Para o SQL Server 2022 (16.x), a opção allow polybase export precisa ser habilitada por meio de sp_configure. Para obter mais informações, confira Definir a opção de configuração allow polybase export.

Para planos de consulta no Azure Synapse Analytics e no Analytics Platform System criados com EXPLAIN, o banco de dados usa essas operações de plano de consulta para tabelas externas: Movimentação de ordem aleatória externa, Movimentação de transmissão externa, Movimentação de partição externa.

No Analytics Platform System, como um pré-requisito para a criação de uma tabela externa, o administrador do dispositivo precisa configurar a conectividade do Hadoop. Para saber mais, confira "Configurar conectividade para dados externos (Analytics Platform System)" na documentação do Analytics Platform System, que pode ser baixada do Centro de Download da Microsoft.

Limitações e restrições

Como os dados da tabela externa residem fora do banco de dados, as operações de backup e restauração apenas funcionam em dados armazenados no banco de dados. Consequentemente, apenas os metadados são copiados em backup e restaurados.

O banco de dados não verifica a conexão com a fonte de dados externa ao restaurar um backup de banco de dados que contém uma tabela externa. Se a fonte original não estiver acessível, a restauração de metadados da tabela externa ainda terá êxito, mas as operações SELECT na tabela externa falharão.

O banco de dados não assegura a consistência dos dados entre o banco de dados e os dados externos. Você, o cliente, é o único responsável por manter a consistência entre os dados externos e o banco de dados.

Não há compatibilidade com as operações DML (linguagem de manipulação de dados) em tabelas externas. Por exemplo, não é possível usar as instruções Transact-SQL atualizar, inserir ou excluir para modificar os dados externos.

CREATE TABLE, DROP TABLE, CREATE STATISTICS, DROP STATISTICS, CREATE VIEW e DROP VIEW são as únicas operações DDL (linguagem de definição de dados) permitidas em tabelas externas.

Limitações e restrições do Azure Synapse Analytics

  • Nos pools de SQL dedicados do Azure Synapse Analytics e no Analytics Platform System, o PolyBase pode consumir no máximo 33.000 arquivos por pasta ao executar 32 consultas simultâneas do PolyBase. O número máximo inclui arquivos e subpastas em cada pasta do HDFS. Se o grau de simultaneidade é menor que 32, um usuário pode executar consultas do PolyBase em pastas do HDFS que contêm mais de 33 mil arquivos. Recomendamos que os usuários do Hadoop e do PolyBase mantenham os caminhos de arquivo curtos e não usem mais do que 30 mil arquivos por pasta do HDFS. Quando há muitos arquivos referenciados, ocorre uma exceção de memória insuficiente da JVM.

  • Em pools de SQL sem servidor, as tabelas externas não podem ser criadas em um local no qual você tem dados no momento. Para reutilizar um local que foi usado para armazenar dados, o local deve ser excluído manualmente em ADLS. Para obter mais limitações e as melhores práticas, confira Melhores práticas de otimização de filtro.

Nos pools de SQL dedicado do Azure Synapse Analytics e no Analytics Platform System, quando CREATE EXTERNAL TABLE AS SELECT seleciona um RCFile, os valores da coluna no RCFile não deverão conter o caractere de barra vertical (|).

SET ROWCOUNT (Transact-SQL) não tem nenhum efeito na instrução CREATE EXTERNAL TABLE AS SELECT. Para obter um comportamento semelhante, use TOP (Transact-SQL).

Examine a Nomenclatura e referência de contêineres, blobs e metadados para obter limitações em nomes de arquivo.

Erros de caractere

Os caracteres a seguir presentes nos dados podem causar erros, incluindo registros rejeitados com CREATE EXTERNAL TABLE AS SELECT para arquivos Parquet.

No Azure Synapse Analytics e no Analytics Platform System, isso também se aplica a arquivos ORC.

  • |
  • " (caractere de aspas)
  • \r\n
  • \r
  • \n

Para usar a instrução CREATE EXTERNAL TABLE AS SELECT que contenha esses caracteres, primeiro você precisa executar essa instrução para exportar os dados de arquivos de texto delimitados, que você pode converter em Parquet ou ORC usando uma ferramenta externa.

Trabalhar com parquet

Ao trabalhar com arquivos parquet, CREATE EXTERNAL TABLE AS SELECT vai gerar um arquivo parquet por CPU disponível, até o grau máximo de paralelismo (MAXDOP) configurado. Cada arquivo pode chegar a até 190 GB, depois disso o SQL Server gerará mais arquivos parquet conforme necessário.

A dica de consulta OPTION (MAXDOP n) afetará apenas a parte SELECT de CREATE EXTERNAL TABLE AS SELECT, ela não tem influência sobre a quantidade de arquivos parquet. Somente o MAXDOP em nível de banco de dados e o MAXDOP em nível de instância são considerados.

Bloqueio

Usa um bloqueio compartilhado no objeto SCHEMARESOLUTION.

Tipos de dados com suporte

O CETAS pode ser usado para armazenar conjuntos de resultados com os seguintes tipos de dados SQL:

  • binary
  • varbinary
  • char
  • varchar
  • nchar
  • NVARCHAR
  • smalldate
  • date
  • datetime
  • datetime2
  • datetimeoffset
  • time
  • decimal
  • numeric
  • FLOAT
  • real
  • BIGINT
  • TINYINT
  • smallint
  • INT
  • BIGINT
  • bit
  • money
  • smallmoney

Exemplos

a. Criar uma tabela do Hadoop usando CREATE EXTERNAL TABLE AS SELECT

Aplica-se a: Azure Synapse Analytics e Analytics Platform System

O exemplo a seguir cria uma tabela externa chamada hdfsCustomer, que usa as definições de coluna e os dados da tabela de origem dimCustomer.

A definição de tabela é armazenada no banco de dados e os resultados da instrução SELECT são exportados para o arquivo /pdwdata/customer.tbl na fonte de dados externa do Hadoop customer_ds. O arquivo é formatado de acordo com o formato de arquivo externo customer_ff.

O nome do arquivo é gerado pelo banco de dados e contém a ID de consulta para facilitar o alinhamento do arquivo com a consulta que o gerou.

O caminho hdfs://xxx.xxx.xxx.xxx:5000/files/ que precede o diretório Customer já deve existir. Se o diretório Customer não existir, o banco de dados o criará.

Observação

Este exemplo especifica 5000. Se a porta não for especificada, o banco de dados usará 8020 como a porta padrão.

O local e o nome de arquivo do Hadoop resultantes serão hdfs:// xxx.xxx.xxx.xxx:5000/files/Customer/ QueryID_YearMonthDay_HourMinutesSeconds_FileIndex.txt..

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE hdfsCustomer
    WITH (
            LOCATION = '/pdwdata/customer.tbl',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT *
FROM dimCustomer;
GO

B. Usar uma dica de consulta com CREATE EXTERNAL TABLE AS SELECT

Aplica-se a: Azure Synapse Analytics e Analytics Platform System

Essa consulta mostra a sintaxe básica para o uso de uma dica de junção de consulta com a instrução CREATE EXTERNAL TABLE AS SELECT. Depois que a consulta é enviada, o banco de dados usa a estratégia de junção hash para gerar o plano de consulta. Para obter mais informações sobre dicas de junção e como usar a cláusula OPTION, confira Cláusula OPTION (Transact-SQL&).

Observação

Este exemplo especifica 5000. Se a porta não for especificada, o banco de dados usará 8020 como a porta padrão.

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
    WITH (
            LOCATION = '/files/Customer',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT T1.*
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
    ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO

C. Usar CETAS para alterar atributos de coluna

Aplica-se a: Azure Synapse Analytics e Analytics Platform System

Este exemplo usa CETAS para alterar os tipos de dados, a nulidade e a ordenação de várias colunas da tabela FactInternetSales.

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
    WITH (
            LOCATION = '/files/Customer',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT T1.ProductKey AS ProductKeyNoChange,
    T1.OrderDateKey AS OrderDate,
    T1.ShipDateKey AS ShipDate,
    T1.CustomerKey AS CustomerKeyNoChange,
    T1.OrderQuantity AS Quantity,
    T1.SalesAmount AS MONEY
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
    ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO

D. Usar CREATE EXTERNAL TABLE AS SELECT exportando dados como parquet

Aplica-se a: SQL Server 2022 (16.x)

O exemplo a seguir cria uma nova tabela externa chamada ext_sales, que usa os dados da tabela SalesOrderDetail de AdventureWorks2022. A opção de configuração allow polybase export precisa ser habilitada.

O resultado da instrução SELECT será salvo em um armazenamento de objetos compatível com S3 anteriormente configurado e chamado s3_eds e uma credencial adequada será criada como s3_dsc. O local do arquivo parquet será <ip>:<port>/cetas/sales.parquet, em que cetas é o bucket de armazenamento criado anteriormente.

Observação

Atualmente, o formato Delta só tem suporte como somente leitura.

-- Credential to access the S3-compatible object storage
CREATE DATABASE SCOPED CREDENTIAL s3_dsc
    WITH IDENTITY = 'S3 Access Key',
        SECRET = '<accesskeyid>:<secretkeyid>'
GO

-- S3-compatible object storage data source
CREATE EXTERNAL DATA SOURCE s3_eds
    WITH (
            LOCATION = 's3://<ip>:<port>',
            CREDENTIAL = s3_dsc
            )

-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
    WITH (FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE ext_sales
    WITH (
            LOCATION = '/cetas/sales.parquet',
            DATA_SOURCE = s3_eds,
            FILE_FORMAT = ParquetFileFormat
            ) AS

SELECT *
FROM AdventureWorks2022.[Sales].[SalesOrderDetail];
GO

E. Usar CREATE EXTERNAL TABLE AS SELECT da tabela delta para parquet

Aplica-se a: SQL Server 2022 (16.x)

O exemplo a seguir cria uma tabela externa chamada Delta_to_Parquet, que usa o tipo de tabela delta de dados em um armazenamento de objetos compatível com S3 chamado s3_delta e grava o resultado em outra fonte de dados chamada s3_parquet como um arquivo Parquet. Para isso, o exemplo usa o comando OPENROWSET. A opção de configuração allow polybase export precisa ser habilitada.

-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
    WITH (FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE Delta_to_Parquet
    WITH (
            LOCATION = '/backup/sales.parquet',
            DATA_SOURCE = s3_parquet,
            FILE_FORMAT = ParquetFileFormat
            ) AS

SELECT *
FROM OPENROWSET(BULK '/delta/sales_fy22/', FORMAT = 'DELTA', DATA_SOURCE = 's3_delta') AS [r];
GO

F. Usar o CREATE EXTERNAL TABLE AS SELECT com uma exibição como a origem

Aplica-se a: pools de SQL sem servidor do Azure Synapse Analytics e pools de SQL dedicados.

Neste exemplo, podemos ver um exemplo de um código de modelo para escrever CETAS com uma exibição definida pelo usuário como origem, usando a identidade gerenciada como uma autenticação e wasbs:.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
    LOCATION = 'wasbs://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SynapseSQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

G. Usar o CREATE EXTERNAL TABLE AS SELECT com uma exibição como a origem

Aplica-se a: pools de SQL sem servidor do Azure Synapse Analytics e pools de SQL dedicados.

Neste exemplo, podemos ver um exemplo de um código de modelo para escrever CETAS com uma exibição definida pelo usuário como origem, usando a identidade gerenciada como uma autenticação e https:.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
    LOCATION = 'https://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SynapseSQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

Próximas etapas

Aplica-se a: Instância Gerenciada de SQL do Azure

Cria uma tabela externa e exporta, em paralelo, os resultados de uma instrução SELECT do Transact-SQL.

Você pode usar CETAS (CREATE EXTERNAL TABLE AS SELECT) para realizar as seguintes tarefas:

  • Crie uma tabela externa sobre arquivos Parquet ou CSV no Armazenamento de Blobs do Azure ou ADLS (Azure Data Lake Storage) Gen2.
  • Exporte, em paralelo, os resultados de uma instrução T-SQL SELECT para a tabela externa criada.
  • Para obter mais recursos de virtualização de dados da Instância Gerenciada de SQL do Azure, confira Virtualização de dados com Instância Gerenciada de SQL do Azure.

Observação

Este conteúdo aplica-se apenas a Instância Gerenciada de SQL do Azure. Para outras plataformas, escolha a versão apropriada de CREATE EXTERNAL TABLE AS SELECT no seletor suspenso.

Convenções de sintaxe de Transact-SQL

Sintaxe

CREATE EXTERNAL TABLE [ [database_name  . [ schema_name ] . ] | schema_name . ] table_name
    WITH (
        LOCATION = 'path_to_folder/',  
        DATA_SOURCE = external_data_source_name,  
        FILE_FORMAT = external_file_format_name
        [, PARTITION ( column_name [ , ...n ] ) ]
)
    AS <select_statement>  
[;]

<select_statement> ::=  
    [ WITH <common_table_expression> [ ,...n ] ]  
    SELECT <select_criteria>

Argumentos

[ [ database_name. [ schema_name ]. ] | schema_name . ] table_name

O nome de uma a três partes da tabela a ser criada. Para uma tabela externa, somente os metadados da tabela são armazenados. Nenhum dado real é movido ou armazenado.

LOCATION = 'path_to_folder'

Especifica o local em que gravar os resultados da instrução SELECT na fonte de dados externa. A pasta raiz é o local de dados especificado na fonte de dados externa. LOCATION deve apontar para uma pasta e ter um / à direita. Exemplo: aggregated_data/.

A pasta de destino para o CETAS deve estar vazia. Se o caminho e a pasta ainda não existirem, eles serão criados automaticamente.

DATA_SOURCE = external_data_source_name

Especifica o nome do objeto de fonte de dados externa que contém a localização em que os dados externos serão armazenados. Para criar uma fonte de dados externa, use CREATE EXTERNAL DATA SOURCE (Transact-SQL).

FILE_FORMAT = external_file_format_name

Especifica o nome do objeto de formato de arquivo externo que contém o formato do arquivo de dados externo. Para criar um formato de arquivo externo, use CREATE EXTERNAL FILE FORMAT (Transact-SQL). No momento, só há suporte para formatos de arquivo externo com FORMAT_TYPE=PARQUET e FORMAT_TYPE=DELIMITEDTEXT. A compactação GZip para o formato DELIMITEDTEXT não é compatível.

[, PARTITION ( column name [ , ...n ] ) ]

Particiona os dados de saída em vários caminhos de arquivo parquet. O particionamento ocorre por determinadas colunas (column_name), correspondendo os caracteres curinga (*) no LOCATION à respectiva coluna de particionamento. O número de colunas na parte PARTITION deve corresponder ao número de caracteres curinga no LOCATION. Deve haver pelo menos uma coluna que não seja usada para particionamento.

WITH <common_table_expression>

Especifica um conjunto de resultados nomeado temporário, conhecido como uma CTE (expressão de tabela comum). Para obter mais informações, confira WITH common_table_expression (Transact-SQL).

SELECT <select_criteria>

Popula a nova tabela com os resultados de uma instrução SELECT. select_criteria é o corpo da instrução SELECT que determina quais dados serão copiados para a nova tabela. Para obter informações sobre as instruções SELECT, confira SELECT (Transact-SQL).

Observação

A cláusula ORDER BY em SELECT não tem suporte para CETAS.

Permissões

Permissões no armazenamento

Você precisa de permissões para listar o conteúdo da pasta e gravar no caminho LOCATION para que o CETAS funcione.

Os métodos de autenticação com suporte são identidade gerenciada ou um token SAS (Assinatura de Acesso Compartilhado).

  • Se você estiver usando a identidade gerenciada para autenticação, verifique se a entidade de serviço da instância gerenciada de SQL tem uma função de Colaborador de Dados do Blob de Armazenamento no contêiner de destino.
  • Se você estiver usando um token SAS, permissões de Leitura, Gravação e Lista serão necessárias.
  • Para o armazenamento de Blobs do Azure, as caixas de seleção Allowed Services: Blob precisam estar marcadas para que o token SAS seja gerado.
  • Para o Azure Data Lake Gen2, as caixas de seleção Allowed Services: Container e Object precisam estar marcadas para que o token SAS seja gerado.

Não há suporte para uma identidade gerenciada atribuída pelo usuário. Não há suporte para a autenticação de passagem do Microsoft Entra. A ID do Microsoft Entra é (anteriormente Azure Active Directory).

Permissões na instância gerenciada de SQL

Para executar esse comando, o usuário de banco de dados precisa de todas estas permissões ou associações:

  • Permissão ALTER SCHEMA no esquema local que conterá a nova tabela ou associação à função de banco de dados fixa db_ddladmin.
  • Permissão CREATE TABLE ou associação à função de banco de dados fixa db_ddladmin.
  • Permissão SELECT em todos os objetos referenciados no select_criteria.

O logon precisa de todas estas permissões:

  • ADMINISTER BULK OPERATIONS
  • ALTER ANY EXTERNAL DATA SOURCE
  • ALTER ANY EXTERNAL FILE FORMAT

Importante

A permissão ALTER ANY EXTERNAL DATA SOURCE concede a qualquer entidade de segurança a capacidade de criar e modificar qualquer objeto de fonte de dados externa. Portanto, também concede a capacidade de acessar todas as credenciais com escopo de banco de dados no banco de dados. Essa permissão precisa ser considerada altamente privilegiada e deve ser concedida apenas a entidades confiáveis no sistema.

Tipos de dados com suporte

O CETAS armazena conjuntos de resultados com os seguintes tipos de dados SQL:

  • binary
  • varbinary
  • char
  • varchar
  • nchar
  • NVARCHAR
  • smalldatetime
  • date
  • datetime
  • datetime2
  • datetimeoffset
  • time
  • decimal
  • numeric
  • FLOAT
  • real
  • BIGINT
  • TINYINT
  • smallint
  • INT
  • BIGINT
  • bit
  • money
  • smallmoney

Observação

LOBs maiores que 1 MB não podem ser usados com CETAS.

Limitações e restrições

  • CETAS (CREATE EXTERNAL TABLE AS SELECT) para Instância Gerenciada de SQL do Azure está desabilitado por padrão. Para obter mais informações, confira a próxima seção, Desabilitado por padrão.
  • Para obter mais informações sobre limitações ou problemas conhecidos com a virtualização de dados em Instância Gerenciada de SQL do Azure, confira Limitações e problemas conhecidos.

Como os dados da tabela externa residem fora do banco de dados, as operações de backup e restauração apenas funcionam em dados armazenados no banco de dados. Consequentemente, apenas os metadados são copiados em backup e restaurados.

O banco de dados não verifica a conexão com a fonte de dados externa ao restaurar um backup de banco de dados que contém uma tabela externa. Se a fonte original não estiver acessível, a restauração de metadados da tabela externa ainda terá êxito, mas as operações SELECT na tabela externa falharão.

O banco de dados não assegura a consistência dos dados entre o banco de dados e os dados externos. Você, o cliente, é o único responsável por manter a consistência entre os dados externos e o banco de dados.

Não há compatibilidade com as operações DML (linguagem de manipulação de dados) em tabelas externas. Por exemplo, não é possível usar as instruções TransactSQL atualizar, inserir ou excluir para modificar os dados externos.

CREATE TABLE, DROP TABLE, CREATE STATISTICS, DROP STATISTICS, CREATE VIEW e DROP VIEW são as únicas operações DDL (linguagem de definição de dados) permitidas em tabelas externas.

As tabelas externas não podem ser criadas em um local no qual você tem dados no momento. Para reutilizar um local que foi usado para armazenar dados, o local deve ser excluído manualmente em ADLS.

SET ROWCOUNT (Transact-SQL) não tem nenhum efeito na instrução CREATE EXTERNAL TABLE AS SELECT. Para obter um comportamento semelhante, use TOP (Transact-SQL).

Examine a Nomenclatura e referência de contêineres, blobs e metadados para obter limitações em nomes de arquivo.

Tipos de armazenamento

Os arquivos podem ser armazenados no Azure Data Lake Storage Gen2 ou no Armazenamento de Blobs do Azure. Para consultar os arquivos, você precisa fornecer o local em um formato específico e usar o prefixo de tipo de local correspondente ao tipo da origem externa e o ponto de extremidade/protocolo, como os seguintes exemplos:

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet

--Data Lake endpoint
adls://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet

Importante

O prefixo de tipo de Local fornecido é usado para escolher o protocolo ideal para comunicação e aproveitar todos os recursos avançados oferecidos pelo tipo de armazenamento específico. O uso do prefixo genérico https:// está desabilitado. Sempre use prefixos específicos do ponto de extremidade.

Desabilitadas por padrão

CETAS (CREATE EXTERNAL TABLE AS SELECT) permite exportar dados da sua instância gerenciada de SQL para uma conta de armazenamento externa, portanto, há potencial para risco de exfiltração de dados com esses recursos. Portanto, o CETAS está desabilitado por padrão para a Instância Gerenciada de SQL do Azure.

Habilitar CETAS

O CETAS para Instância Gerenciada de SQL do Azure só pode ser habilitado por meio de um método que requer permissões elevadas do Azure e não pode ser habilitado por meio do T-SQL. Devido ao risco de exfiltração de dados não autorizados, o CETAS não pode ser habilitado por meio do procedimento armazenado T-SQL sp_configure, mas exige que a ação do usuário fora da instância gerenciada do SQL.

Permissões para habilitar o CETAS

Para habilitar por meio do Azure PowerShell, o usuário que executa o comando deve ter funções RBAC do Azure do Colaborador ou do SQL Security Manager para sua instância gerenciada de SQL.

Uma função personalizada também pode ser criada para isso, exigindo a ação de leitura e gravação para a açãoMicrosoft.Sql/managedInstances/serverConfigurationOptions.

Métodos para habilitar o CETAS

Para invocar os comandos do PowerShell em um computador, o pacote Az versão 9.7.0 ou mais recente deve ser instalado localmente. Ou considere usar o Azure Cloud Shell para executar Azure PowerShell em shell.azure.com.

Primeiro, faça logon no Azure e defina o contexto adequado para sua assinatura:

Login-AzAccount
$SubscriptionID = "<YourSubscriptionIDHERE>"
Select-AzSubscription -SubscriptionName $SubscriptionID

Para gerenciar a opção de configuração do servidor "allowPolybaseExport", ajuste os scripts do PowerShell a seguir para sua assinatura e o nome da instância gerenciada de SQL e execute os comandos. Para obter mais informações, confira Set-AzSqlServerConfigurationOption e Get-AzSqlServerConfigurationOption.

# Enable ServerConfigurationOption with name "allowPolybaseExport"
Set-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport" -Value 1

Para desabilitar a opção de configuração do servidor "allowPolybaseExport":

# Disable ServerConfigurationOption with name "allowPolybaseExport"
Set-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport" -Value 0

Para obter o valor atual da opção de configuração do servidor "allowPolybaseExport":

# Get ServerConfigurationOptions with name "allowPolybaseExport"
Get-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport"

Verificar status de CETAS

A qualquer momento, você pode marcar o status atual da opção de configuração CETAS.

Conectar-se à sua instância gerenciada do SQL. Execute o T-SQL a seguir e observe a coluna value da resposta. Depois que a alteração de configuração do servidor for concluída, os resultados dessa consulta deverão corresponder à configuração desejada.

SELECT [name], [value] FROM sys.configurations WHERE name ='allow polybase export';

Solucionar problemas

Para obter mais etapas para solucionar problemas de virtualização de dados na Instância Gerenciada de SQL do Azure, confira Solucionar problemas. O tratamento de erros e mensagens de erro comuns para CETAS na Instância Gerenciada de SQL do Azure segue.

Tratamento de erros

Quando CREATE EXTERNAL TABLE AS SELECT exporta dados para um arquivo delimitado por texto, não há nenhum arquivo de rejeição para linhas com falha na exportação.

Quando você cria a tabela externa, o banco de dados tenta se conectar ao local externo. Se a conexão falhar, o comando falhará e a tabela externa não será criada. Pode levar alguns minutos ou mais para o comando falhar porque o banco de dados tenta estabelecer a conexão novamente pelo menos três vezes.

Mensagens de erro comuns

Essas mensagens de erro comuns têm explicações rápidas para o CETAS para Instância Gerenciada de SQL do Azure.

  1. Especificando um local já existente no armazenamento.

    Solução: desmarque o local de armazenamento (incluindo instantâneo) ou altere o parâmetro de localização na consulta.

    Mensagem de erro de exemplo: Msg 15842: Cannot create external table. External table location already exists.

  2. Valores de coluna formatados usando objetos JSON.

    Solução: converta a coluna de valor em uma única coluna VARCHAR ou NVARCHAR ou um conjunto de colunas com tipos explicitamente definidos.

    Mensagem de erro de exemplo: Msg 16549: Values in column value are formatted as JSON objects and cannot be written using CREATE EXTERNAL TABLE AS SELECT.

  3. Parâmetro de localização inválido (por exemplo, vários //).

    Solução: corrigir o parâmetro de localização.

    Mensagem de erro de exemplo: Msg 46504: External option 'LOCATION' is not valid. Ensure that the length and range are appropriate.

  4. Faltando uma das opções necessárias (DATA_SOURCE, FILE_FORMAT, LOCATION).

    Solução: adicione o parâmetro ausente à consulta CETAS.

    Mensagem de erro de exemplo: Msg 46505: Missing required external DDL option 'FILE_FORMAT'

  5. Problemas de acesso (credencial inválida, credencial expirada ou credencial com permissões insuficientes). A possibilidade alternativa é um caminho inválido, em que a instância gerenciada de SQL recebeu um Erro 404 do armazenamento.

    Solução: verifique a validade e as permissões de credencial. Como alternativa, valide se o caminho é válido e o armazenamento existe. Use o caminho de URL adls://<container>@<storage_account>.blob.core.windows.net/<path>/.

    Mensagem de erro de exemplo: Msg 15883: Access check for '<Read/Write>' operation against '<Storage>' failed with HRESULT = '0x...'

  6. A parte de localização do DATA_SOURCE contém curingas.

    Solução: remova curingas do local.

    Mensagem de erro de exemplo: Msg 16576: Location provided by DATA_SOURCE '<data source name>' cannot contain any wildcards.

  7. O número de caracteres curinga no parâmetro LOCATION e o número de colunas particionadas não correspondem.

    Solução: verifique o mesmo número de caracteres curinga em LOCATION que as colunas de partição.

    Mensagem de erro de exemplo: Msg 16577: Number of wildcards in LOCATION must match the number of columns in PARTITION clause.

  8. O nome da coluna na cláusula PARTITION não corresponde a nenhuma coluna na lista.

    Solução: verifique se as colunas em PARTITION são válidas.

    Mensagem de erro de exemplo: Msg 16578: The column name '<column name>' specified in the PARTITION option does not match any column specified in the column list

  9. Coluna especificada mais de uma vez na lista PARTITION.

    Solução: verifique se as colunas na cláusula PARTITION são exclusivas.

    Mensagem de erro de exemplo: Msg 16579: A column has been specified more than once in the PARTITION list. Column '<column name>' is specified more than once.

  10. A coluna foi especificada mais de uma vez na lista PARTITION ou não corresponde a nenhuma coluna da lista SELECT.

    Solução: verifique se não há duplicatas na lista de partições e se as colunas de partição existem na parte SELECT.

    Mensagem de Erro de Exemplo: Msg 11569: Column <column name> has been specified more than once in the partition columns list. Please try again with a valid parameter. or Msg 11570: Column <column name> specified in the partition columns list does not match any columns in SELECT clause. Please try again with a valid parameter.

  11. Usando todas as colunas na lista PARTITION.

    Solução: pelo menos uma das colunas da parte SELECT não deve estar na parte PARTITION da consulta.

    Mensagem de erro de exemplo: Msg 11571: All output columns in DATA_EXPORT query are declared as PARTITION columns. DATA_EXPORT query requires at least one column to export.

  12. O recurso está desabilitado.

    Solução: habilite o recurso usando a seção Desabilitado por padrão neste artigo.

    Mensagem de erro de exemplo: Msg 46552: Writing into an external table is disabled. See 'https://go.microsoft.com/fwlink/?linkid=2201073' for more information

Bloqueio

Usa um bloqueio compartilhado no objeto SCHEMARESOLUTION.

Exemplos

a. Usar o CETAS com uma exibição para criar uma tabela externa usando a identidade gerenciada

Este exemplo fornece código para escrever o CETAS com uma exibição como origem, usando uma autenticação de identidade gerenciada pelo sistema.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SQLwriteable] WITH (
    LOCATION = 'adls://container@mystorageaccount.blob.core.windows.net/mybaseoutputfolderpath',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

B. Usar o CETAS com uma exibição para criar uma tabela externa com autenticação SAS

Este exemplo fornece código para escrever CETAS com uma exibição como origem, usando um token SAS como autenticação.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL SAS_token
WITH
  IDENTITY = 'SHARED ACCESS SIGNATURE',
  -- Remove ? from the beginning of the SAS token
  SECRET = '<azure_shared_access_signature>' ;
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SQLwriteable] WITH (
    LOCATION = 'adls://container@mystorageaccount.blob.core.windows.net/mybaseoutputfolderpath',
    CREDENTIAL = [SAS_token]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

C. Criar uma tabela externa em um único arquivo parquet no armazenamento

Os dois exemplos a seguir mostram como descarregar alguns dos dados de uma tabela local em uma tabela externa armazenada como arquivos parquet no contêiner de armazenamento de Blobs do Azure. Eles foram projetados para trabalhar com o banco de dados AdventureWorks2022. Este exemplo mostra a criação de uma tabela externa como um único arquivo parquet, em que o próximo exemplo mostra como criar uma tabela externa e particioná-la em várias pastas com arquivos parquet.

O exemplo abaixo funciona usando identidade gerenciada para autenticação. Dessa forma, verifique se a entidade de serviço Instância Gerenciada de SQL do Azure tem a função Colaborador de Dados do Blob de Armazenamento no contêiner Armazenamento de Blobs do Azure. Como alternativa, você pode modificar o exemplo e usar tokens SAS (Segredo de Acesso Compartilhado) para autenticação.

No exemplo a seguir, você cria uma tabela externa em um único arquivo parquet em Armazenamento de Blobs do Azure, selecionando na tabela SalesOrderHeader para pedidos anteriores a 1º de janeiro de 2014:

--Example 1: Creating an external table into a single parquet file on the storage, selecting from SalesOrderHeader table for orders older than 1-Jan-2014:
USE [AdventureWorks2022]
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong Password';
GO

CREATE DATABASE SCOPED CREDENTIAL [CETASCredential]
    WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL DATA SOURCE [CETASExternalDataSource]
WITH (
    LOCATION = 'abs://container@storageaccount.blob.core.windows.net',
    CREDENTIAL = [CETASCredential] );
GO

CREATE EXTERNAL FILE FORMAT [CETASFileFormat]
WITH(
    FORMAT_TYPE=PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
    );
GO

-- Count how many rows we plan to offload
SELECT COUNT(*) FROM [AdventureWorks2022].[Sales].[SalesOrderHeader] WHERE
        OrderDate < '2013-12-31';

-- CETAS write to a single file, archive all data older than 1-Jan-2014:
CREATE EXTERNAL TABLE SalesOrdersExternal
WITH (
    LOCATION = 'SalesOrders/',
    DATA_SOURCE = [CETASExternalDataSource],
    FILE_FORMAT = [CETASFileFormat])
AS 
    SELECT 
        *
    FROM 
        [AdventureWorks2022].[Sales].[SalesOrderHeader]
    WHERE
        OrderDate < '2013-12-31';

-- you can query the newly created external table
SELECT COUNT (*) FROM SalesOrdersExternal;

D. Criar uma tabela externa particionada em vários arquivos parquet armazenados em uma árvore de pastas

Este exemplo baseia-se no exemplo anterior para mostrar como criar uma tabela externa e particioná-la em várias pastas com arquivos parquet. Você pode usar tabelas particionadas para obter benefícios de desempenho se o conjunto de dados for grande.

Crie uma tabela externa com base em dados SalesOrderHeader, usando as etapas do Exemplo B, mas particione a tabela externa por OrderDate ano e mês. Ao consultar tabelas externas particionadas, podemos nos beneficiar da eliminação de partição para o desempenho.

--CETAS write to a folder hierarchy (partitioned table):
CREATE EXTERNAL TABLE SalesOrdersExternalPartitioned
WITH (
    LOCATION = 'PartitionedOrders/year=*/month=*/', 
    DATA_SOURCE = CETASExternalDataSource,
    FILE_FORMAT = CETASFileFormat,
    --year and month will correspond to the two respective wildcards in folder path    
    PARTITION (
        [Year],
        [Month]
        ) 
    )
AS
    SELECT
        *,
        YEAR(OrderDate) AS [Year],
        MONTH(OrderDate) AS [Month]
    FROM [AdventureWorks2022].[Sales].[SalesOrderHeader]
    WHERE
        OrderDate < '2013-12-31';
GO

-- you can query the newly created partitioned external table
SELECT COUNT (*) FROM SalesOrdersExternalPartitioned;

Próximas etapas