共用方式為


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

適用於: SQL Server 2022 (16.x) 和更新版本的 Azure Synapse Analytics Analytics Platform System (PDW)

建立外部資料表,然後平行匯出 Transact-SQL SELECT 陳述式的結果。

  • Azure Synapse Analytics 和分析平台系統支援 Hadoop 或 Azure Blob 記憶體。
  • SQL Server 2022 (16.x) 和更新版本支援 CREATE EXTERNAL TABLE AS SELECT (CETAS) 來建立外部數據表,然後平行導出 Transact-SQL SELECT 語句的結果至 Azure Data Lake Storage (ADLS) Gen2、Azure 儲存體 Account V2 和 S3 兼容的物件記憶體。

注意

Azure SQL 受控執行個體 CETAS 的功能和安全性與 SQL Server 或 Azure Synapse Analytics 不同。 如需詳細資訊,請參閱 CREATE EXTERNAL TABLE AS SELECT Azure SQL 受控執行個體 版本。

注意

Azure Synapse Analytics 中無伺服器集區的 CETAS 功能和安全性與 SQL Server 不同。 如需詳細資訊,請參閱 使用 Synapse SQL 的 CETAS。

Transact-SQL 語法慣例

語法

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>

引數

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

要在資料庫中建立之資料表的第一到第三部分名稱。 就外部資料表而言,關聯式資料庫只會儲存資料表中繼資料。

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

資料表資料行的名稱。

LOCATION

適用於:Azure Synapse Analytics 和 Analytics Platform System

'hdfs_folder'**
指定要將 SELECT 陳述式的結果寫入至外部資料來源上的哪個位置。 此位置是一個資料夾名稱,而且可以選擇性地包含 Hadoop 叢集或 Blob 儲存體之根資料夾的相對路徑。 如果此位置尚未存在,PolyBase 會建立路徑與資料夾。

外部檔案會寫入至 hdfs_folder 並命名為 QueryID_date_time_ID.format,其中 ID 是一個累加識別碼,而 format 則是所匯出資料的格式。 例如 QID776_20160130_182739_0.orc

LOCATION 必須指向資料夾,並以 / 結尾,例如:aggregated_data/

適用於:SQL Server 2022 (16.x) 和更新版本

prefix://path[:port] 會提供連線通訊協定(前置詞)、路徑,以及選擇性地將埠傳送至外部數據源,其中會寫入 SELECT 語句的結果。

如果目的地是 S3 相容的物件儲存體,則貯體必須先存在,但 PolyBase 可以視需要建立子資料夾。 SQL Server 2022 (16.x) 支援 Azure Data Lake Storage Gen2、Azure 儲存體帳戶 V2 和 S3 相容的物件儲存體。 目前不支援 ORC 檔案。

DATA_SOURCE = external_data_source_name

指定包含儲存或將儲存外部資料之位置的外部資料來源 名稱。 此位置為 Hadoop 叢集或 Azure Blob 儲存體。 若要建立外部資料來源,請使用 CREATE EXTERNAL DATA SOURCE (Transact-SQL)

FILE_FORMAT = external_file_format_name

指定包含外部資料檔案格式之外部檔案格式物件的名稱。 若要建立外部檔案格式,請使用 CREATE EXTERNAL FILE FORMAT (Transact-SQL)

REJECT 選項

執行這個 CREATE EXTERNAL TABLE AS SELECT 陳述式時,不適用 REJECT 選項。 而是在這裡指定這類選項,以便讓資料庫能夠在稍後從外部資料表匯入資料時加以使用。 稍後,當 CREATE TABLE AS SELECT 陳述式從外部資料表選取資料時,資料庫將會使用拒絕選項來決定在停止匯入之前,可允許發生匯入失敗的資料列數目或百分比。

  • REJECT_VALUE = reject_value

    指定在資料庫停止匯入之前,可允許發生匯入失敗的資料列值或百分比。

  • REJECT_TYPE = value | percentage

    釐清 REJECT_VALUE 選項為常值或百分比。

    • value

      若 REJECT_VALUE 為常值而非百分比則會使用。 當失敗的資料列數目超出 reject_value 時,資料庫會停止從外部資料檔案匯入資料列。

      例如,若為 REJECT_VALUE = 5REJECT_TYPE = value,資料庫在匯入 5 個資料列失敗之後會停止匯入資料列。

    • percentage

      若 REJECT_VALUE 為百分比而非常值則會使用。 當失敗的資料列「百分比」超出 reject_value 時,資料庫會停止從外部資料檔案匯入資料列。 系統會依據間隔時間計算失敗的資料列所佔百分比。 當 TYPE=HADOOP 時,只有在專用 SQL 集區中才有效。

  • REJECT_SAMPLE_VALUE = reject_sample_value

    REJECT_TYPE = percentage 時為必要。 指定資料庫重新計算失敗的資料列百分比之前,會嘗試匯入的資料列數目。

    例如,如果 REJECT_SAMPLE_VALUE = 1000,資料庫將會在已嘗試從外部資料檔案匯入 1000 個資料列之後,計算失敗的資料列百分比。 如果失敗的資料列百分比小於 reject_value,資料庫會嘗試載入另外 1000 個資料列。 資料庫會在嘗試匯入每個額外的 1000 個資料列之後,持續重新計算失敗的資料列百分比。

    注意

    由於資料庫每隔一段時間就會計算失敗的資料列百分比,因此,實際的失敗資料列百分比可能會超過 reject_value

    範例:

    此範例說明三個 REJECT 選項彼此如何互動。 例如,若為 REJECT_TYPE = percentage, REJECT_VALUE = 30, REJECT_SAMPLE_VALUE = 100,則可能會發生下列情節:

    • 資料庫會嘗試載入前 100 個資料列,其中 25 個失敗,75 個成功。
    • 失敗的資料列百分比會計算為 25%,低於拒絕值 30%。 因此,不需停止載入。
    • 資料庫會嘗試載入接下來的 100 個資料列。 這次 25 個成功,75 個失敗。
    • 失敗的資料列百分比在重新計算後為 50%。 失敗資料列的百分比已超出 30% 的拒絕值。
    • 在嘗試載入 200 個資料列之後,因為失敗的資料列達 50%,已超出指定的 30% 限制,所以載入失敗。

WITH common_table_expression

指定稱為通用資料表運算式 (CTE) 的暫存具名結果集。 如需詳細資訊,請參閱 WITH common_table_expression (Transact-SQL)

SELECT <select_criteria>

將 SELECT 陳述式產生的結果填入新資料表。 select_criteria 是 SELECT 陳述式的主體,可決定要複製到新資料表的資料。 如需 SELECT 陳述式的相關資訊,請參閱 SELECT (Transact-SQL)

注意

SELECT 中的 ORDER BY 子句不會影響 CETAS。

資料行選項

  • column_name [ ,...n ]

    資料行名稱不允許 CREATE TABLE 中提到的資料行選項。 您反而應該為新資料表提供一個由一或多個資料行名稱構成的選擇性清單。 新數據表中的數據行會使用您指定的名稱。 當您指定資料行名稱時,資料行清單中的資料行數目必須與選取結果中的資料行數目相符。 如果您未指定任何資料行名稱,新目標資料表會使用選取陳述式結果中的資料行名稱。

    您無法指定任何其他資料行選項,例如資料類型、定序或可 Null 性。 這些屬性每個都是從 SELECT 陳述式的結果衍生而來的。 不過,您可以使用 SELECT 陳述式來變更屬性。 如需範例,請參閱使用 CETAS 來變更資料行的屬性

權限

若要執行此命令,「資料庫使用者」需要具備下列所有權限或成員資格:

  • 將包含新資料表之本機結構描述的 ALTER SCHEMA 權限,或 db_ddladmin 固定資料庫角色的成員資格。
  • CREATE TABLE 權限或 db_ddladmin 固定資料庫角色的成員資格。
  • select_criteria中所參考之任何物件上的 SELECT 權限。

登入需要下列所有權限:

  • ADMINISTER BULK OPERATIONS
  • ALTER ANY EXTERNAL DATA SOURCE
  • ALTER ANY EXTERNAL FILE FORMAT
  • 一般而言,您針對 CETAS 必須擁有列出資料夾內容和寫入 LOCATION 資料夾的權限。
  • 在 Azure Synapse Analytics 和 Analytics Platform System 中,讀取和寫入 Hadoop 叢集或 Azure Blob 儲存體上外部資料夾的寫入許可權。
  • 在 SQL Server 2022 (16.x) 中,您也必須在外部位置上設定正確的權限。 將資料輸出至位置的寫入權限,以及存取資料的讀取權限。
  • 針對 Azure Blob 儲存體和 Azure Data Lake Gen2,SHARED ACCESS SIGNATURE 權杖必須授與容器的下列權限:讀取寫入列出建立
  • 針對 Azure 部落格記憶體,必須選取 [ Allowed ServicesBlob ] 複選框來產生 SAS 令牌。
  • 針對 Azure Data Lake Gen2, Allowed Services必須選取 : ContainerObject 複選框來產生 SAS 令牌。

重要

ALTER ANY EXTERNAL DATA SOURCE 權限可授與任何主體建立及修改任何外部資料來源物件的能力,因此也能為其授與存取資料庫上所有資料庫範圍認證的能力。 您必須將此權限視為具高度權限,而且只能將其授與系統中受信任的主體。

錯誤處理

當 CREATE EXTERNAL TABLE AS SELECT 將資料匯出至文字分隔檔案時,對於匯出失敗的資料列,不會有任何拒絕檔案。

當您建立外部資料表時,資料庫會嘗試連線至外部位置。 如果連線失敗,命令會失敗,而且不會建立外部數據表。 由於資料庫至少會重試連線三次,因此可能需要一分鐘或更久的時間,命令才會失敗。

如果 CREATE EXTERNAL TABLE AS SELECT 取消或失敗,資料庫將會進行一次嘗試,以移除已經在外部資料來源上建立的任何新檔案與資料夾。

在 Azure Synapse Analytics 和 Analytics Platform System 中,資料庫會報告資料匯出期間在外部資料來源上發生的任何 JAVA 錯誤。

備註

當 CREATE EXTERNAL TABLE AS SELECT 陳述式完成之後,您就能在外部資料表上執行 Transact-SQL 查詢。 除非您使用 CREATE TABLE AS SELECT 陳述式來進行匯入,否則這些作業會在查詢的持續期間將資料匯入至資料庫。

外部資料表名稱和定義會儲存在資料庫中繼資料中。 資料會儲存在外部資料來源中。

CREATE EXTERNAL TABLE AS SELECT 陳述式一律會建立一個沒有分割的資料表,即使來源資料表已分割也一樣。

針對 SQL Server 2022 (16.x),必須使用 sp_configure 來啟用選項 allow polybase export。 如需詳細資訊,請參閱設定 allow polybase export 設定選項

針對使用 EXPLAIN 建立的 Azure Synapse Analytics 和 Analytics Platform System 中的查詢計劃,資料庫會針對外部資料表使用這些查詢計劃作業:外部隨機移動、外部廣播移動、外部資料分割移動。

在 Analytics Platform System 中,作為建立外部資料表的先決條件,應用裝置系統管理員必須設定 Hadoop 管連線能力。 如需詳細資訊,請參閱 Analytics Platform System 文件 (可從 Microsoft 下載中心 \(英文\) 下載) 中的<設定對外部資料的連線能力 (Analytics Platform System)>。

限制事項

由於外部數據表數據位於資料庫外部,因此備份和還原作業只會對儲存在資料庫中的數據運作。 因此,只會備份和還原元數據。

還原包含外部資料表的資料庫備份時,資料庫不會驗證對外部資料來源的連線。 如果無法存取原始來源,外部數據表的元數據還原仍會成功,但外部數據表上的SELECT作業會失敗。

資料庫不保證資料庫與外部資料之間的資料一致性。 如果您是客戶,就必須全權負責維護外部資料與資料庫之間的一致性。

外部資料表不支援資料操作語言 (DML) 作業。 例如,您無法使用 Transact-SQL 更新、插入或刪除 Transact-SQL 語句來修改外部數據。

外部資料表上允許的資料定義語言 (Data Definition Language, DDL) 作業僅限 CREATE TABLE、DROP TABLE、CREATE STATISTICS、DROP STATISTICS、CREATE VIEW 及 DROP VIEW。

Azure Synapse Analytics 的限制

  • 在 Azure Synapse Analytics 專用 SQL 集區和 Analytics Platform System 系統中,PolyBase 在執行 32 個並行 PolyBase 查詢時,針對每個資料夾最多可以取用 33,000 個檔案。 這個上限數同時包含了每個 HDFS 資料夾中的檔案和子資料夾。 如果並行程度小於 32,使用者就可以針對 HDFS 中內含超過 33,000 個檔案的資料夾執行 PolyBase 查詢。 我們建議 Hadoop 與 PolyBase 的使用者使用簡短的檔案路徑,且所使用的每個 HDFS 資料夾檔案數目不要超過 30,000 個。 參考太多檔案時,就會發生 JVM 記憶體不足的例外狀況。

  • 在無伺服器 SQL 集區中,您無法在目前有資料的位置中建立外部資料表。 若要重複使用已用來儲存資料的位置,則必須在 ADLS 上手動刪除該位置。 如需更多限制和最佳做法,請參閱篩選最佳化最佳做法

在 Azure Synapse Analytics 專用 SQL 集區和 Analytics Platform System 系統中,當 CREATE EXTERNAL TABLE AS SELECT 從 RCFile 中選取時,RCFile 中的資料行值不得包含管道 (|) 字元。

SET ROWCOUNT (Transact-SQL) 對 CREATE EXTERNAL TABLE AS SELECT 沒有任何作用。 若要達到類似的行為,請使用 TOP (Transact-SQL)

如需檔名的限制,請檢閱 命名和參考容器、Blob 和元數據

字元錯誤

資料中存在的下列字元可能會導致錯誤,包括使用 CREATE EXTERNAL TABLE AS SELECT 對 Parquet 檔案的拒絕記錄。

在 Azure Synapse Analytics 和 Analytics Platform System 中,這也適用於 ORC 檔案。

  • |
  • " (引號字元)
  • \r\n
  • \r
  • \n

若要使用包含這些字元的 CREATE EXTERNAL TABLE AS SELECT,則必須先執行 CREATE EXTERNAL TABLE AS SELECT 陳述式將資料匯出到分隔符號文字檔,然後即可使用外部工具將其轉換成 Parquet 或 ORC。

使用 parquet

使用 parquet 檔案時, CREATE EXTERNAL TABLE AS SELECT 會為每個可用的 CPU 產生一個 parquet 檔案,最多設定的最大平行處理原則程度 (MAXDOP)。 每個檔案可以成長到 190 GB,之後 SQL Server 會視需要產生更多 Parquet 檔案。

查詢提示 OPTION (MAXDOP n) 只會影響 的 CREATE EXTERNAL TABLE AS SELECTSELECT 部分,不會影響 parquet 檔案的數量。 只會考慮資料庫層級 MAXDOP 和實例層級 MAXDOP。

鎖定

在 SCHEMARESOLUTION 上採取共用鎖定。

支援的資料類型

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

範例

A. 使用 CREATE EXTERNAL TABLE AS SELECT 來建立 Hadoop 資料表

適用於:Azure Synapse Analytics 和 Analytics Platform System

下列範例會使用來自來源資料表 dimCustomer 的資料行定義與資料,來建立名為 hdfsCustomer 的新外部資料表。

資料表定義會儲存在資料庫中,而 SELECT 陳述式的結果則會匯出至位於 Hadoop 外部資料來源 customer_ds 上的 /pdwdata/customer.tbl 檔案。 此檔案會根據外部檔案格式 customer_ff來格式化。

檔案名稱會由資料庫產生並包含查詢識別碼,以方便根據產生檔案的查詢來調整該檔案。

客戶目錄前的路徑 hdfs://xxx.xxx.xxx.xxx:5000/files/ 必須是已經存在的路徑。 如果客戶目錄不存在,資料庫就會建立該目錄。

注意

這個範例指定的是 5000。 如果未指定連接埠,資料庫就會使用 8020 作為預設連接埠。

產生的 Hadoop 位置和檔案名稱將會是 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. 搭配 CREATE EXTERNAL TABLE AS SELECT 使用查詢提示

適用於:Azure Synapse Analytics 和 Analytics Platform System

此查詢示範如何將查詢聯結提示與 CREATE EXTERNAL TABLE AS SELECT 陳述式搭配使用的基本語法。 提交查詢之後,資料庫會使用雜湊聯結策略來產生查詢計劃。 如需有關聯結提示及如何使用 OPTION 子句的詳細資訊,請參閱 OPTION 子句 (Transact-SQL)

注意

這個範例指定的是 5000。 如果未指定連接埠,資料庫就會使用 8020 作為預設連接埠。

-- 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. 使用 CTAS 來變更資料行屬性

適用於:Azure Synapse Analytics 和 Analytics Platform System

這個範例會使用 CTAS 來為 FactInternetSales 資料表的數個資料行變更資料類型、可 Null 性和定序。

-- 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. 使用 CREATE EXTERNAL TABLE AS SELECT 將資料匯出為 parquet

適用於:SQL Server 2022 (16.x)

下列範例會建立名為 ext_sales 的新外部數據表,該數據表會使用 數據表SalesOrderDetailAdventureWorks2022中的數據。 必須啟用允許 PolyBase 匯出組態選項

SELECT 陳述式的結果將會儲存在先前設定並命名為 s3_eds 的 S3 相容物件儲存體上,以及建立為 s3_dsc 的適當認證。 parquet 檔案位置將會是 <ip>:<port>/cetas/sales.parquet,也就是先前建立儲存體貯體的 cetas

注意

差異格式目前僅支援為唯讀。

-- 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. 使用 CREATE EXTERNAL TABLE AS SELECT 從差異資料表到 parquet

適用於:SQL Server 2022 (16.x)

下列範例會建立名為 Delta_to_Parquet 的新外部資料表,其使用位於名為 s3_delta 的 S3 相容物件儲存體的差異資料表資料類型,並將結果寫入另一個名為 s3_parquet parquet 檔案的資料來源中。 因此,範例會使用 OPENROWSET 命令。 必須啟用允許 PolyBase 匯出組態選項

-- 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. 使用 CREATE EXTERNAL TABLE AS SELECT 搭配檢視作為來源

適用於:Azure Synapse Analytics 無伺服器 SQL 集區和專用 SQL 集區。

在這裡範例中,我們可以看到範本程式碼範例,以使用者定義檢視作為來源撰寫 CETAS,並使用受控識別作為驗證,以及 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. 使用 CREATE EXTERNAL TABLE AS SELECT 搭配檢視作為來源

適用於:Azure Synapse Analytics 無伺服器 SQL 集區和專用 SQL 集區。

在這裡範例中,我們可以看到範本程式碼範例,以使用者定義檢視作為來源撰寫 CETAS,並使用受控識別作為驗證,以及 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

下一步

適用於:Azure SQL 受控執行個體

建立外部資料表,然後平行匯出 Transact-SQL SELECT 陳述式的結果。

您可以使用 CREATE EXTERNAL TABLE AS SELECT (CETAS) 來完成下列工作:

  • 在 Azure Blob 記憶體或 Azure Data Lake Storage (ADLS) Gen2 中的 Parquet 或 CSV 檔案之上建立外部數據表。
  • 平行匯出 T-SQL SELECT 語句的結果至所建立的外部數據表。
  • 如需 Azure SQL 受控執行個體 的數據虛擬化功能,請參閱使用 Azure SQL 受控執行個體 進行數據虛擬化。

注意

此內容僅適用於 Azure SQL 受控執行個體。 對於其他平臺,請從 dropdrown 選取器中選擇適當的 CREATE EXTERNAL TABLE AS SELECT 版本

Transact-SQL 語法慣例

語法

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>

引數

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

要建立之資料表名稱的第一到第三部分。 針對外部數據表,只會儲存數據表元數據。 不會移動或儲存實際數據。

LOCATION = 'path_to_folder'

指定要將 SELECT 陳述式的結果寫入至外部資料來源上的哪個位置。 根資料夾是在外部資料來源中指定的資料位置。 LOCATION 必須指向資料夾,並具有尾 /端 。 範例:aggregated_data/

CETAS 的目的地資料夾必須是空的。 如果路徑和資料夾不存在,系統就會自動建立它們。

DATA_SOURCE = external_data_source_name

指定外部數據來源物件的名稱,其中包含儲存外部資料的位置。 若要建立外部資料來源,請使用 CREATE EXTERNAL DATA SOURCE (Transact-SQL)

FILE_FORMAT = external_file_format_name

指定包含外部資料檔案格式之外部檔案格式物件的名稱。 若要建立外部檔案格式,請使用 CREATE EXTERNAL FILE FORMAT (Transact-SQL)。 目前僅支援具有 FORMAT_TYPE=PARQUET 和 FORMAT_TYPE=DELIMITEDTEXT 的外部檔格式。 不支援 DELIMITEDTEXT 格式的 GZip 壓縮。

[, PARTITION (資料行名稱 [ , ...n ] ) ]

將輸出數據分割成多個 parquet 檔案路徑。 每個指定的資料列都會進行資料分割,column_name比對 LOCATION 與個別資料分割資料列中的通配符 \ 。 PARTITION 元件中的數據行數目必須符合 LOCATION 中的通配符數目。 至少必須有一個數據行未用於數據分割。

WITH <common_table_expression>

指定稱為通用資料表運算式 (CTE) 的暫存具名結果集。 如需詳細資訊,請參閱 WITH common_table_expression (Transact-SQL)

SELECT <select_criteria>

將 SELECT 陳述式產生的結果填入新資料表。 select_criteria 是 SELECT 陳述式的主體,可決定要複製到新資料表的資料。 如需 SELECT 陳述式的相關資訊,請參閱 SELECT (Transact-SQL)

注意

CETAS 不支援 SELECT 中的 ORDER BY 子句。

權限

記憶體中的許可權

您必須具有列出資料夾內容的許可權,並寫入LOCATION路徑,CETAS才能運作。

支持的驗證方法是受控識別或共用存取簽章 (SAS) 令牌。

  • 如果您使用受控識別進行驗證,請確定 SQL 受控實例的服務主體在目的地容器上具有記憶體 Blob 數據參與者的角色
  • 如果您使用 SAS 令牌, 則需要 [讀取]、 [寫入] 和 [列表 ] 許可權。
  • 針對 Azure 部落格記憶體,必須選取 [ Allowed ServicesBlob ] 複選框來產生 SAS 令牌。
  • 針對 Azure Data Lake Gen2, Allowed Services必須選取 : ContainerObject 複選框來產生 SAS 令牌。

不支援使用者指派的受控識別。 Microsoft不支援 Entra 傳遞驗證。 Microsoft Entra 識別符是 (先前稱為 Azure Active Directory)。

SQL 受控實例中的許可權

若要執行此命令,「資料庫使用者」需要具備下列所有權限或成員資格:

  • 將包含新資料表之本機結構描述的 ALTER SCHEMA 權限,或 db_ddladmin 固定資料庫角色的成員資格。
  • CREATE TABLE 權限或 db_ddladmin 固定資料庫角色的成員資格。
  • select_criteria中所參考之任何物件上的 SELECT 權限。

登入需要下列所有權限:

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

重要

ALTER ANY EXTERNAL DATA SOURCE 權限可授與任何主體建立及修改任何外部資料來源物件的能力,因此也能為其授與存取資料庫上所有資料庫範圍認證的能力。 您必須將此權限視為具高度權限,而且只能將其授與系統中受信任的主體。

支援的資料類型

CETAS 會儲存具有下列 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

注意

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

限制事項

  • 默認會停用 Azure SQL 受控執行個體 的 CREATE EXTERNAL TABLE AS SELECT (CETAS)。 如需詳細資訊,請參閱下一節, 預設為停用。
  • 如需 Azure SQL 受控執行個體 中數據虛擬化的限制或已知問題的詳細資訊,請參閱限制和已知問題

由於外部數據表數據位於資料庫外部,因此備份和還原作業只會對儲存在資料庫中的數據運作。 因此,只會備份和還原元數據。

還原包含外部資料表的資料庫備份時,資料庫不會驗證對外部資料來源的連線。 如果無法存取原始來源,外部資料表的中繼資料還原仍會成功,但外部資料表上的 SELECT 作業會失敗。

資料庫不保證資料庫與外部資料之間的資料一致性。 如果您是客戶,就必須全權負責維護外部資料與資料庫之間的一致性。

外部資料表不支援資料操作語言 (DML) 作業。 例如,您無法使用 Transact-SQL Update、Insert 或 Delete Transact-SQL 陳述式來修改外部資料。

外部資料表上允許的資料定義語言 (Data Definition Language, DDL) 作業僅限 CREATE TABLE、DROP TABLE、CREATE STATISTICS、DROP STATISTICS、CREATE VIEW 及 DROP VIEW。

您無法在目前擁有資料的位置建立外部資料表。 若要重複使用已用來儲存資料的位置,則必須在 ADLS 上手動刪除該位置。

SET ROWCOUNT (Transact-SQL) 對 CREATE EXTERNAL TABLE AS SELECT 沒有任何作用。 若要達到類似的行為,請使用 TOP (Transact-SQL)

如需檔名的限制,請檢閱 命名和參考容器、Blob 和元數據

儲存體類型

檔案可以儲存在 Azure Data Lake Storage Gen2 或 Azure Blob Storage。 若要查詢檔案,您必須以特定格式提供位置,並使用對應外部來源和端點/通訊協定類型的位置類型前置詞,如下列範例所示:

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

重要

提供的位置類型前置詞可用來選擇最佳的通訊協定,並利用特定儲存體類型所提供的任何進階功能。 已停用使用一般 https:// 前置詞。 一律使用端點特定的前置詞。

預設為停用

CREATE EXTERNAL TABLE AS SELECT (CETAS) 可讓您將數據從 SQL 受控實例導出至外部記憶體帳戶,因此可能會有這些功能的數據外泄風險。 因此,預設會針對 Azure SQL 受控執行個體 停用 CETAS。

啟用 CETAS

Azure SQL 受控執行個體 的 CETAS 只能透過需要提高 Azure 許可權的方法啟用,而且無法透過 T-SQL 啟用。 由於未經授權的數據外泄風險,因此無法透過 sp_configure T-SQL 預存程式啟用 CETAS,但需要 SQL 受控實例以外的用戶動作。

啟用 CETAS 的許可權

若要透過 Azure PowerShell 啟用,執行命令的用戶必須具有 SQL 受控實例的參與者SQL 安全性管理員 Azure RBAC 角色。

您也可以為此建立自定義角色,需要動作的Microsoft.Sql/managedInstances/serverConfigurationOptions讀取寫入動作。

啟用 CETAS 的方法

若要在計算機上叫用PowerShell命令, 必須在本機安裝 Az 套件 9.7.0 版或更新版本。 或者,考慮使用 Azure Cloud Shell,在 shell.azure.com 執行 Azure PowerShell。

首先,登入 Azure 並設定訂用帳戶的適當內容:

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

若要管理伺服器組態選項 「allowPolybaseExport」,請將下列 PowerShell 腳本調整為您的訂用帳戶和 SQL 受控實例名稱,然後執行命令。 如需詳細資訊,請參閱 Set-AzSqlServerConfigurationOptionGet-AzSqlServerConfigurationOption

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

若要停用伺服器組態選項 「allowPolybaseExport」:

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

若要取得伺服器組態選項 「allowPolybaseExport」 的目前值:

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

確認 CETAS 的狀態

您可以隨時檢查 CETAS 組態選項的目前狀態。

線上到您的 SQL 受控實例。 執行下列 T-SQL 並觀察 value 響應的數據行。 伺服器組態變更完成後,此查詢的結果應該符合您所需的設定。

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

疑難排解

如需針對 Azure SQL 受控執行個體 中的數據虛擬化進行疑難解答的詳細資訊,請參閱疑難解答。 下列 Azure SQL 受控執行個體 中 CETAS 的錯誤處理和常見錯誤訊息。

錯誤處理

當 CREATE EXTERNAL TABLE AS SELECT 將資料匯出至文字分隔檔案時,對於匯出失敗的資料列,不會有任何拒絕檔案。

當您建立外部資料表時,資料庫會嘗試連線至外部位置。 如果連線失敗,命令將會失敗,而且將不會建立外部資料表。 由於資料庫至少會重試連線三次,因此可能需要一分鐘或更久的時間,命令才會失敗。

常見的錯誤訊息

這些常見的錯誤訊息有適用於 Azure SQL 受控執行個體 CETAS 的快速說明。

  1. 指定記憶體中已經存在的位置。

    解決方案:清除記憶體位置(包括快照集),或變更查詢中的位置參數。

    範例錯誤訊息: Msg 15842: Cannot create external table. External table location already exists.

  2. 使用 JSON 物件格式化的數據行值。

    解決方案:將值數據行轉換成單一 VARCHAR 或 NVARCHAR 數據行,或具有明確定義類型的一組數據行。

    範例錯誤訊息: Msg 16549: Values in column value are formatted as JSON objects and cannot be written using CREATE EXTERNAL TABLE AS SELECT.

  3. 位置參數無效(例如,多個 //)。

    解決方案:修正位置參數。

    範例錯誤訊息: Msg 46504: External option 'LOCATION' is not valid. Ensure that the length and range are appropriate.

  4. 缺少其中一個必要選項(DATA_SOURCE、FILE_FORMAT、LOCATION)。

    解決方案:將遺漏的參數新增至 CETAS 查詢。

    範例錯誤訊息: Msg 46505: Missing required external DDL option 'FILE_FORMAT'

  5. 存取問題(無效的認證、過期的認證或許可權不足的認證)。 替代的可能性是無效的路徑,其中 SQL 受控實例從記憶體收到錯誤 404。

    解決方案:驗證認證有效性和許可權。 或者,驗證路徑有效且記憶體存在。 使用網址路徑 adls://<container>@<storage_account>.blob.core.windows.net/<path>/

    範例錯誤訊息: Msg 15883: Access check for '<Read/Write>' operation against '<Storage>' failed with HRESULT = '0x...'

  6. DATA_SOURCE的位置部分包含通配符。

    解決方案:從位置移除通配符。

    範例錯誤訊息: Msg 16576: Location provided by DATA_SOURCE '<data source name>' cannot contain any wildcards.

  7. LOCATION 參數中的通配符數目和數據分割數據行數目不符。

    解決方案:確定 LOCATION 中的通配符數目與數據分割數據行相同。

    範例錯誤訊息: Msg 16577: Number of wildcards in LOCATION must match the number of columns in PARTITION clause.

  8. PARTITION 子句中的數據行名稱與清單中的任何數據行不符。

    解決方案:請確定 PARTITION 中的數據行有效。

    範例錯誤訊息: Msg 16578: The column name '<column name>' specified in the PARTITION option does not match any column specified in the column list

  9. 數據行在 PARTITION 清單中指定多次。

    解決方案:請確定 PARTITION 子句中的數據行是唯一的。

    範例錯誤訊息: Msg 16579: A column has been specified more than once in the PARTITION list. Column '<column name>' is specified more than once.

  10. 數據行在 PARTITION 清單中指定了一次以上,或者它與 SELECT 清單中的數據行不相符。

    解決方案:請確定資料分割清單中沒有重複專案,且數據分割數據行存在於SELECT元件中。

    範例錯誤訊息: Msg 11569: Column <column name> has been specified more than once in the partition columns list. Please try again with a valid parameter.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. 在 PARTITION 清單中使用所有資料行。

    解決方案:SELECT 元件中的至少一個數據行不得位於查詢的 PARTITION 部分。

    範例錯誤訊息: 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. 功能已停用。

    解決方案:使用 本文中的預設 停用一節來啟用此功能。

    範例錯誤訊息: Msg 46552: Writing into an external table is disabled. See 'https://go.microsoft.com/fwlink/?linkid=2201073' for more information

鎖定

在 SCHEMARESOLUTION 上採取共用鎖定。

範例

A. 使用 CETAS 搭配檢視,以使用受控識別建立外部數據表

此範例提供使用系統受控識別驗證,以檢視作為來源撰寫 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 [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. 使用 CETAS 搭配檢視建立具有 SAS 驗證的外部數據表

此範例提供使用SAS令牌作為驗證,以檢視作為來源撰寫 CETAS 的程式代碼。

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. 在記憶體上的單一 parquet 檔案中建立外部數據表

接下來的兩個範例示範如何將部分數據從本機數據表卸除到 Azure Blob 記憶體容器上儲存為 parquet 檔案的外部數據表。 其設計目的是要與資料庫搭配使用 AdventureWorks2022 。 此範例示範如何將外部數據表建立為單一 parquet 檔案,下一個範例示範如何建立外部數據表,並將它分割成具有 parquet 檔案的多個資料夾。

下列範例使用受控識別進行驗證。 因此,請確定您的 Azure SQL 受控執行個體 服務主體在 Azure Blob 儲存體 容器上具有記憶體 Blob 數據參與者角色。 或者,您可以修改範例,並使用共用存取密碼 (SAS) 令牌進行驗證。

下列範例會在 Azure Blob 儲存體 中建立外部數據表到單一 parquet 檔案,並從數據表中SalesOrderHeader選取 2014 年 1 月 1 日之前的訂單:

--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. 將分割的外部數據表建立至儲存在資料夾樹狀結構中的多個 parquet 檔案

此範例以上一個範例為基礎,示範如何建立外部數據表,並將它分割成具有 parquet 檔案的多個資料夾。 如果您的數據集很大,您可以使用資料分割數據表來提升效能優勢。

使用範例 B 中的步驟,從 SalesOrderHeader 數據建立外部數據表,但依 OrderDate 年份和月份分割外部數據表。 查詢數據分割外部數據表時,我們可以從數據分割消除中獲益,以達到效能。

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

下一步