CETAS com Synapse SQL

Artigo
03/30/2023

Você pode usar CETAS (CREATE EXTERNAL TABLE AS SELECT) no pool de SQL dedicado ou no pool de SQL sem servidor para realizar as seguintes tarefas:

Criar uma tabela externa
Exportar, em paralelo, os resultados de uma instrução SELECT do Transact-SQL para:
- O Hadoop
- Blob de Armazenamento do Azure
- Azure Data Lake Storage Gen2

CETAS no pool de SQL dedicado

Para o uso e a sintaxe do CETAS do pool de SQL dedicado, veja o artigo CREATE EXTERNAL TABLE AS SELECT. Além disso, para obter diretrizes sobre o CTAS usando o pool de SQL dedicado, confira o artigo CREATE TABLE AS SELECT.

CETAS no pool de SQL sem servidor

Ao usar o pool de SQL sem servidor, o CETAS é usado para criar uma tabela externa e exportar os resultados da consulta para o Azure Storage Blob ou o Azure Data Lake Storage Gen2.

Para obter a sintaxe completa, consulte CRIAR TABELA EXTERNA COMO SELEÇÃO (Transact-SQL).

Exemplos

Esses exemplos usam CETAS para salvar a população total agregada por ano e estado em uma pasta aggregated_data que está localizada na fonte de dados population_ds.

Este exemplo conta com a credencial, a fonte de dados e o formato de arquivo externo criados anteriormente. Veja o documento tabelas externas. Para salvar os resultados da consulta em uma pasta diferente na mesma fonte de dados, altere o argumento LOCATION.

Para salvar os resultados em uma conta de armazenamento diferente, crie e use uma fonte de dados diferente para o argumento DATA_SOURCE.

Observação

Os exemplos a seguir usam uma conta de armazenamento do Open Data do Azure pública. Ela é somente leitura. Para executar essas consultas, você precisa fornecer a fonte de dados para a qual você tem permissões de gravação.

-- use CETAS to export select statement with OPENROWSET result to  storage
CREATE EXTERNAL TABLE population_by_year_state
WITH (
    LOCATION = 'aggregated_data/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
)  
AS
SELECT decennialTime, stateName, SUM(population) AS population
FROM
    OPENROWSET(BULK 'https://azureopendatastorage.dfs.core.windows.net/censusdatacontainer/release/us_population_county/year=*/*.parquet',
    FORMAT='PARQUET') AS [r]
GROUP BY decennialTime, stateName
GO

-- you can query the newly created external table
SELECT * FROM population_by_year_state

O exemplo a seguir usa uma tabela externa como fonte para o CETAS. Ele conta com a credencial, a fonte de dados, o formato de arquivo externo e a tabela externa criados anteriormente. Veja o documento tabelas externas.

-- use CETAS with select from external table
CREATE EXTERNAL TABLE population_by_year_state
WITH (
    LOCATION = 'aggregated_data/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
)  
AS
SELECT decennialTime, stateName, SUM(population) AS population
FROM census_external_table
GROUP BY decennialTime, stateName
GO

-- you can query the newly created external table
SELECT * FROM population_by_year_state

Exemplo geral

Neste exemplo, podemos ver um exemplo de código de modelo para gravar CETAS com uma Exibição como origem e usar a Identidade Gerenciada como uma autenticação.

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

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
UNIQUEIDENTIFIER

Observação

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

Próximas etapas

Tente consultar as Tabelas externas do Apache Spark para o Azure Synapse.

Share via