CETAS 搭配 Synapse SQL

發行項
03/30/2023

您可以在專用 SQL 集區或無伺服器 SQL 集區中使用 CREATE EXTERNAL TABLE AS SELECT (CETAS) 完成下列工作：

建立外部資料表
將 Transact-SQL SELECT 陳述式的結果以平行方式匯出至：
- Hadoop
- Azure 儲存體 Blob
- Azure Data Lake Storage Gen2

專用 SQL 集區中的 CETAS

如需專用 SQL 集區的 CETAS 用法和語法，請查看 CREATE EXTERNAL TABLE AS SELECT 一文。此外，如需使用專用 SQL 集區進行 CTAS 的指引，請參閱 CREATE TABLE AS SELECT 一文。

無伺服器 SQL 集區中的 CETAS

使用無伺服器 SQL 集區時，CETAS 會用來建立外部資料表，並將查詢結果匯出至 Azure 儲存體 Blob 或 Azure Data Lake Storage Gen2。

如需完整的語法，請參閱 CREATE EXTERNAL TABLE AS SELECT (Transact-SQL) 。

範例

這些範例會使用 CETAS，將依據年和州彙總的總人口數儲存至位於 population_ds 資料來源中的 aggregated_data 資料夾。

此範例會依賴先前建立的認證、資料來源和外部檔案格式。請參閱外部資料表文件。若要將查詢結果儲存到相同資料來源中的不同資料夾，請變更 LOCATION 引數。

若要將結果儲存到不同的儲存體帳戶，請為 DATA_SOURCE 引數建立並使用不同的資料來源。

注意

接下來的範例會使用公用 Azure 開放資料儲存體帳戶。此範例僅供讀取。若要執行這些查詢，您需要提供您具有寫入權限的資料來源。

-- 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
date
Datetime
datetime2
datetimeoffset
time
decimal
NUMERIC
FLOAT
real
BIGINT
TINYINT
SMALLINT
int
BIGINT
bit
money
SMALLMONEY
UNIQUEIDENTIFIER

注意

大於 1MB 的 LOB 無法與 CETAS 搭配使用。

後續步驟

試著查詢 Apache Spark for Azure Synapse 外部資料表。

共用方式為