CETAS в SQL Synapse

Статья
03/30/2023

Вы можете использовать CREATE EXTERNAL TABLE AS SELECT (CETAS) в выделенных пулах SQL или бессерверных пулах SQL для выполнения следующих задач:

Создание внешней таблицы
Параллельный экспорт результатов инструкции Transact-SQL SELECT в:
- Hadoop
- Большой двоичный объект хранилища Azure
- Azure Data Lake Storage 2-го поколения

Использование CETAS в выделенном пуле SQL

Дополнительные сведения о выделенном пуле SQL, а также использовании и синтаксисе CETAS см. в разделе CREATE EXTERNAL TABLE AS SELECT. Инструкции по использованию CTAS в выделенным пуле SQL см. в разделе CREATE TABLE AS SELECT.

Использование CETAS в бессерверном пуле SQL

При использовании бессерверного пула SQL с помощью CETAS можно создать внешнюю таблицу и экспортировать результаты запроса в Azure Storage Blob или Azure Data Lake Storage 2-го поколения.

Полный синтаксис см. в статье CREATE EXTERNAL TABLE AS SELECT (Transact-SQL).

Примеры

В этих примерах CETAS используется для сохранения данных о всем населении, агрегированных по годам и штатам, в папку aggregated_data, расположенную в источнике данных population_ds.

Этот пример основан на учетных данных, источнике данных и формате внешнего файла, которые созданы ранее. Дополнительные сведения см. в статье о внешних таблицах. Чтобы сохранить результаты запроса в другую папку в том же источнике данных, измените аргумент LOCATION.

Чтобы сохранить результаты в другую учетную запись хранения, создайте и используйте другой источник данных для аргумента DATA_SOURCE.

Примечание

В приведенных ниже примерах используется открытая учетная запись хранения Azure Open Data. Она доступна только в режиме для чтения. Для выполнения этих запросов необходимо указать источник данных, для которого у вас есть разрешения на запись.

-- 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

В следующем примере внешняя таблица используется в качестве источника для CETAS. Этот пример основан на учетных данных, источнике данных, формате внешнего файла и внешней таблице, которые созданы ранее. Дополнительные сведения см. в статье о внешних таблицах.

-- 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

Общий пример

В этом примере мы можем увидеть пример кода шаблона для написания CETAS с представлением в качестве источника и использования управляемого удостоверения в качестве проверки подлинности.

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

Поддерживаемые типы данных

CETAS можно использовать для хранения результирующих наборов со следующими типами данных SQL:

binary
varbinary
char
varchar
nchar
nvarchar
smalldate
Дата
DATETIME
datetime2
datetimeoffset
time
Decimal
NUMERIC
FLOAT
real
BIGINT
tinyint
smallint
INT
BIGINT
bit
money
smallmoney
UNIQUEIDENTIFIER

Примечание

Большие объекты (LOB) размером более 1 МБ нельзя использовать с CETAS.

Дальнейшие действия

Попробуйте выполнить запросы к внешним таблицам Apache Spark для Azure Synapse.

Поделиться через