CREATE EXTERNAL TABLE (Transact-SQL)

概觀：SQL Server

此命令會建立 PolyBase 的外部資料表來存取儲存在 Hadoop 叢集中的資料，或是參考儲存在 Hadoop 叢集或 Azure Blob 儲存體中資料的 Azure Blob 儲存體 PolyBase 外部資料表。

適用於：SQL Server 2016 (或更新版本)

使用具備外部資料來源的外部資料表進行 PolyBase 查詢。 外部資料來源會用來建立連線能力，並支援這些主要使用案例：

• 使用 PolyBase 來執行資料虛擬化和資料載入
• 使用 SQL Server 或 SQL Database 來執行大量載入作業 (使用 BULK INSERT 或 OPENROWSET)

另請參閱 CREATE EXTERNAL DATA SOURCE 及 DROP EXTERNAL TABLE。

語法

-- Create a new external table
CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'folder_or_filepath',
        DATA_SOURCE = external_data_source_name,
        [ FILE_FORMAT = external_file_format_name ]
        [ , <reject_options> [ ,...n ] ]
    )
[;]

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage
    | REJECT_VALUE = reject_value
    | REJECT_SAMPLE_VALUE = reject_sample_value,
    | REJECTED_ROW_LOCATION = '/REJECT_Directory'
}

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

引數

{ database_name.schema_name.table_name | schema_name.table_name | table_name }

要建立之資料表名稱的第一到第三部分。 針對外部資料表，SQL 只會儲存資料表中繼資料，以及 Hadoop 或 Azure Blob 儲存體中所參考檔案或資料夾的基本統計資料。 系統不會在 SQL Server 中移動或儲存任何實際資料。

<column_definition> [ ,...n ]

CREATE EXTERNAL TABLE 支援設定資料行名稱、資料類型、可 NULL 性和定序功能。 您無法在外部資料表上使用 DEFAULT CONSTRAINT。

資料行定義 (包括資料類型及資料行數目) 必須符合外部檔案中的資料。 若有不相符的情形，系統在查詢實際資料時將會拒絕檔案資料列。

LOCATION = 'folder_or_filepath'

指定位於 Hadoop 或 Azure Blob 儲存體中之實際資料的資料夾或檔案路徑，以及檔案名稱。 此外，從 SQL Server 2022 (16.x) 開始，支援 S3 相容的物件儲存體。 位置會從根資料夾開始。 根資料夾是在外部資料來源中指定的資料位置。

在 SQL Server 中，CREATE EXTERNAL TABLE 陳述式會建立尚未存在的路徑和資料夾。 您接著可以使用 INSERT INTO 將資料從本機 SQL Server 資料表匯出至外部資料來源。 如需詳細資訊，請參閱 PolyBase 查詢。

若您將 LOCATION 指定為資料夾，會從外部資料表中選取的 PolyBase 查詢，將會從該資料夾及其所有子資料夾中擷取檔案。 PolyBase 和 Hadoop 相同，並不會傳回隱藏的資料夾。 它也不會傳回檔案名稱是以底線 (_) 或句號 (.) 開始的檔案。

在下圖範例中，若為 LOCATION='/webdata/'，PolyBase 查詢將會從 mydata.txt 和 mydata2.txt 傳回資料列。 查詢將不會傳回 mydata3.txt，因為其為隱藏子資料夾中的檔案。 此外，查詢不會傳回 _hidden.txt，因為其為隱藏的檔案。

若要變更預設設定並僅從根資料夾讀取，請在 core-site.xml 設定檔中將 <polybase.recursive.traversal> 屬性設為 'false'。 此檔案位於 SQL Server 根目錄底下<SqlBinRoot>\PolyBase\Hadoop\Confbin。 例如： C:\Program Files\Microsoft SQL Server\MSSQL13.XD14\MSSQL\Binn 。

DATA_SOURCE = external_data_source_name

可指定包含外部資料位置的外部資料來源名稱。 此位置是 Hadoop 檔案系統 (HDFS)、Azure Blob 儲存體容器或 Azure Data Lake Store。 若要建立外部資料來源，請使用 CREATE EXTERNAL DATA SOURCE。

FILE_FORMAT = external_file_format_name

可指定外部檔案格式物件的名稱，該物件中儲存了外部資料的檔案類型和壓縮方法。 若要建立外部檔案格式，請使用 CREATE EXTERNAL FILE FORMAT。

外部檔案格式可由多個類似的外部檔案重複使用。

拒絕選項

此選項只能與 TYPE = HADOOP 的外部資料源搭配使用。

您可以指定能決定 PolyBase 如何處理其從外部資料來源所擷取之已修改記錄的拒絕參數。 若資料記錄的實際資料類型或資料行數目，與外部資料表的資料行定義不相符，該資料記錄就會被系統視為「已修改」。

當您不指定或變更拒絕值時，PolyBase 就會使用預設值。 拒絕參數的相關資訊會在您搭配 CREATE EXTERNAL TABLE 陳述式建立外部資料表時，以額外中繼資料的形式儲存。 當未來有 SELECT 陳述式或 SELECT INTO SELECT 陳述式從外部資料表中選取資料時，PolyBase 將會使用拒絕選項來判斷在實際的查詢失敗之前，可以拒絕的資料列數目或百分比。 查詢將會傳回 (部分) 結果，直到超過拒絕閾值為止。 接著它便會失敗並顯示適當的錯誤訊息。

REJECT_TYPE = value | 百分比

指明是要將 REJECT_VALUE 選項指定為常值還是百分比。

value

REJECT_VALUE 是常值，而不是百分比。 拒絕資料列的數目超過 reject_value 時，查詢就會失敗。

舉例來說，假設 REJECT_VALUE = 5 且 REJECT_TYPE = value，系統拒絕五個資料列後，SELECT 查詢就失敗。

percentage

REJECT_VALUE 是百分比，而不是常值。 查詢會在被拒絕資料列的百分比超過 reject_value 時失敗。 系統會依據間隔時間計算失敗的資料列所佔百分比。

REJECT_VALUE = reject_value

指定在查詢失敗之前可以拒絕的資料列數目或百分比。

針對 REJECT_TYPE = value，reject_value 必須為介於 0 和 2,147,483,647 的整數。

針對 REJECT_TYPE = percentage，reject_value 必須為介於 0 和 100 的浮點數。

REJECT_SAMPLE_VALUE = reject_sample_value

當您指定 REJECT_TYPE = percentage 時，這是必要的屬性。 它會決定在 PolyBase 重新計算被拒絕資料列的百分比之前，應嘗試擷取的資料列數目。

reject_sample_value 參數必須是介於 0 和 2,147,483,647 的整數。

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

範例：

此範例說明三個 REJECT 選項彼此如何互動。 例如，如果 REJECT_TYPE = percentage、REJECT_VALUE = 30 且 REJECT_SAMPLE_VALUE = 100，就可能發生下列案例：

• PolyBase 會嘗試擷取前 100 個資料列；其中有 25 個失敗，75 個成功。
• 失敗資料列的百分比會計算為 25%，低於拒絕值 30%。 因此，PolyBase 將會繼續從外部資料來源擷取資料。
• PolyBase 會嘗試載入接下來的 100 個資料列；這次有 25 個資料列成功，75 個資料列失敗。
• 失敗資料列的百分比在重新計算後為 50%。 失敗資料列的百分比已超出 30% 的拒絕值。
• PolyBase 查詢在嘗試傳回前 200 個資料列後，會因被拒絕的資料列達 50% 而失敗。 請注意，相符的資料列會在 PolyBase 查詢偵測到超出拒絕閾值之前傳回。

REJECTED_ROW_LOCATION = Directory Location

適用於：SQL Server 2019 CU6 和更新版本、Azure Synapse Analytics。

指定外部資料來源中，已拒絕資料列和相應錯誤檔案應寫入的目錄。

若指定的路徑不存在，PolyBase 會為您建立一個目錄。 系統會建立名稱為 "_rejectedrows" 的子目錄。 "_" 字元可確保該目錄從其他資料處理逸出，除非已明確在位置參數中指名。 在此目錄中，會有一個根據載入提交時間建立的資料夾，格式為 YearMonthDay -HourMinuteSecond (例如 20230330-173205)。 在此資料中寫入了兩種類型的檔案，分別是 _reason 檔案與資料檔案。 這個選項只能與 TYPE = HADOOP 的外部資料源搭配使用，以及用於使用 DELIMITEDTEXT FORMAT_TYPE 的外部資料表。 如需詳細資訊，請參閱 CREATE EXTERNAL DATA SOURCE 和 CREATE EXTERNAL FILE FORMAT。

原因檔案與資料檔案均具有與 CTAS 陳述式相關的 queryID。 因為資料與原因檔案在不同的檔案中，所以對應的檔案會具有相符尾碼。

權限

需要下列使用者權限：

• CREATE TABLE
• ALTER ANY SCHEMA
• ALTER ANY EXTERNAL DATA SOURCE
• ALTER ANY EXTERNAL FILE FORMAT (僅適用於 Hadoop 和 Azure 儲存體外部資料源)
• CONTROL DATABASE (僅適用於 Hadoop 和 Azure 儲存體外部資料源)

請注意，CREATE EXTERNAL TABLE 命令中所使用 DATABASE SCOPED CREDENTIAL 中指定的遠端登入必須具有 LOCATION 參數中所指定外部資料來源上路徑/資料表/集合的讀取權限。 如果您打算使用此 EXTERNAL TABLE 將資料匯出至 Hadoop 或 Azure 儲存體外部資料來源，則指定的登入必須具有 LOCATION 中所指定路徑的寫入權限。 請注意，SQL Server 2022 (16.x) 目前不支援 Hadoop。

針對 Azure Blob 儲存體，在 Azure 入口網站、Azure Blob 儲存體或 ADLS Gen2 儲存體帳戶中設定存取金鑰和共用存取簽章 (SAS) 時，請設定 [允許的權限] 以至少授與讀取和寫入權限。 在跨資料夾搜尋時，可能也需要列出權限。 您也必須選取 [容器] 和 [物件] 作為允許的資源類型。

錯誤處理

執行 CREATE EXTERNAL TABLE 陳述式的同時，PolyBase 會嘗試連線至外部資料來源。 如果連線的嘗試失敗，陳述式就會失敗，且不會建立外部資料表。 由於 PolyBase 在查詢失敗之前會多次嘗試連線，因此可能需要一分鐘或更久的時間，命令才會失敗。

備註

在臨機操作查詢的案例 (例如 SELECT FROM EXTERNAL TABLE) 中，PolyBase 會將擷取自外部資料來源的資料列，儲存在暫存資料表中。 在查詢完成之後，PolyBase 便會移除並刪除該暫存資料表。 SQL 資料表中不會永久存放資料。

相反地，在匯入案例中 (例如 SELECT INTO FROM EXTERNAL TABLE)，PolyBase 會將擷取自外部資料來源的資料列，以永久資料的形式儲存在 SQL 資料表中。 新的資料表會在查詢執行期間，當 PolyBase 擷取外部資料時建立。

PolyBase 可以將部分的查詢計算推送至 Hadoop 以改善查詢效能。 此動作稱為述詞下推。 若要啟用它，請在 CREATE EXTERNAL DATA SOURCE 中指定 Hadoop 資源管理員位置選項。

您可以建立許多參考相同或不同外部資料來源的外部資料表。

限制事項

因為外部資料表的資料不受 SQL Server 直接管理控制，所以可隨時由外部處理序變更或移除。 因此，針對外部資料表的查詢結果並不保證具有確定性。 相同的查詢在每次針對外部資料表執行時，都有可能傳回不同的結果。 同樣地，在移動或移除外部資料的情況下，查詢也有可能會失敗。

您可以建立多個參考不同外部資料來源的外部資料表。 如果您同時針對不同的 Hadoop 資料來源執行查詢，則每個 Hadoop 來源都必須使用相同的「Hadoop 連線能力」伺服器組態設定。 例如，您不能同時針對 Cloudera Hadoop 叢集和 Hortonworks Hadoop 叢集執行查詢，因為這些叢集是使用不同的組態設定。 如需組態設定和支援的組合，請參閱 PolyBase 連線能力設定。

當外部數據表使用 DELIMITEDTEXT、 CSV、 PARQUET或 DELTA 做為資料類型時，外部數據表僅支援每個 CREATE STATISTICS 命令一個數據行的統計數據。

外部資料表上僅允許使用下列資料定義語言 (DDL) 陳述式：

• CREATE TABLE 和 DROP TABLE
• CREATE STATISTICS 和 DROP STATISTICS
• CREATE VIEW 和 DROP VIEW

不支援的建構和作業：

• 外部資料表資料行上的 DEFAULT 限制式
• 刪除、插入及更新的資料操作語言 (DML) 作業

查詢限制

執行 32 個並行的 PolyBase 查詢時，PolyBase 每個資料夾可取用的檔案數目上限為 33000 個檔案。 這個上限數同時包含了每個 HDFS 資料夾中的檔案和子資料夾。 如果並行程度小於 32，使用者就可以針對 HDFS 中內含超過 33000 個檔案的資料夾執行 PolyBase 查詢。 我們建議您使用簡短的外部檔案路徑，且所使用的每個 HDFS 資料夾檔案數目不要超過 30000 個檔案。 參考太多檔案時，可能會發生 Java 虛擬機器 (JVM) 記憶體不足的例外狀況。

資料表寬度限制

SQL Server 2016 中的 PolyBase 具有 32 KB 的資料列寬度限制，這是以依資料表定義的單一有效資料列大小上限為基礎。 若資料行結構描述的總和超過 32 KB，PolyBase 將無法查詢資料。

資料類型限制

下列資料類型不能用在 PolyBase 外部資料表中：

• geography
• geometry
• hierarchyid
• image
• text
• nText
• xml
• 任何使用者定義的類型

資料來源特定限制

Oracle

搭配使用 PolyBase 時，不支援 Oracle 同義字。

包含陣列之 MongoDB 集合的外部資料表

若要建立包含陣列之 MongoDB 集合的外部資料表，您應該使用適用於 Azure Data Studio 的資料虛擬化延伸模組，根據 PolyBase ODBC Driver for MongoDB 偵測到的結構描述來產生 CREATE EXTERNAL TABLE 陳述式。 驅動程式會自動執行壓平合併動作。 或者，您可以使用 sp_data_source_objects (Transact-SQL) 來偵測集合結構描述 (資料行)，並手動建立外部資料表。 sp_data_source_table_columns 預存程序也會透過 PolyBase ODBC Driver for MongoDB 驅動程式自動執行壓平合併。 Azure Data Studio 和 sp_data_source_table_columns 的資料虛擬化延伸模組使用相同的內部預存程序來查詢外部結構描述。

鎖定

SCHEMARESOLUTION 物件上的共用鎖定。

安全性

外部資料表的資料檔案會儲存在 Hadoop 或 Azure Blob 儲存體中。 這些資料檔案是由您自己的處理程序所建立及管理。 因此您應負責管理外部資料的安全性。

範例

A. 建立具有文字分隔格式資料的外部資料表

此範例會示範建立將資料儲存為文字分隔檔案之外部資料表的所有步驟。 它會定義外部資料來源 mydatasource，以及外部檔案格式 myfileformat。 系統接著會在 CREATE EXTERNAL TABLE 陳述式中參考這些資料庫層級的物件。 如需詳細資訊，請參閱 CREATE EXTERNAL DATA SOURCE 和 CREATE EXTERNAL FILE FORMAT。

CREATE EXTERNAL DATA SOURCE mydatasource
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (FIELD_TERMINATOR ='|')
);

CREATE EXTERNAL TABLE ClickStream (
    url varchar(50),
    event_date date,
    user_IP varchar(50)
)
WITH (
        LOCATION='/webdata/employee.tbl',
        DATA_SOURCE = mydatasource,
        FILE_FORMAT = myfileformat
    )
;

B. 建立具有 RCFile 格式資料的外部資料表

此範例會示範建立將資料格式化為 RCFile 之外部資料表的所有步驟。 它會定義外部資料來源 mydatasource_rc，以及外部檔案格式 myfileformat_rc。 系統接著會在 CREATE EXTERNAL TABLE 陳述式中參考這些資料庫層級的物件。 如需詳細資訊，請參閱 CREATE EXTERNAL DATA SOURCE 和 CREATE EXTERNAL FILE FORMAT。

CREATE EXTERNAL DATA SOURCE mydatasource_rc
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat_rc
WITH (
    FORMAT_TYPE = RCFILE,
    SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'
)
;

CREATE EXTERNAL TABLE ClickStream_rc (
    url varchar(50),
    event_date date,
    user_ip varchar(50)
)
WITH (
        LOCATION='/webdata/employee_rc.tbl',
        DATA_SOURCE = mydatasource_rc,
        FILE_FORMAT = myfileformat_rc
    )
;

C. 建立具有 ORC 格式資料的外部資料表

此範例會示範建立將資料格式化為 ORC 檔案之外部資料表的所有步驟。 其會定義外部資料來源 mydatasource_orc，以及外部檔案格式 myfileformat_orc。 系統接著會在 CREATE EXTERNAL TABLE 陳述式中參考這些資料庫層級的物件。 如需詳細資訊，請參閱 CREATE EXTERNAL DATA SOURCE 和 CREATE EXTERNAL FILE FORMAT。

CREATE EXTERNAL DATA SOURCE mydatasource_orc
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat_orc
WITH (
    FORMAT = ORC,
    COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)
;

CREATE EXTERNAL TABLE ClickStream_orc (
    url varchar(50),
    event_date date,
    user_ip varchar(50)
)
WITH (
        LOCATION='/webdata/',
        DATA_SOURCE = mydatasource_orc,
        FILE_FORMAT = myfileformat_orc
    )
;

D. 查詢 Hadoop 資料

ClickStream 為能夠連線至 Hadoop 叢集上 employee.tbl 分隔符號文字檔案的外部資料表。 下列查詢看起來像是針對標準資料表的查詢。 不過，此查詢會從 Hadoop 擷取資料，然後計算結果。

SELECT TOP 10 (url) FROM ClickStream WHERE user_ip = 'xxx.xxx.xxx.xxx';

E. 將 Hadoop 資料與 SQL 資料聯結

此查詢看起來像是針對兩個 SQL 資料表的標準 JOIN 查詢。 差異在於，PolyBase 會從 Hadoop 擷取 clickstream 資料，然後將它聯結至 UrlDescription 資料表。 其中一個資料表是外部資料表，另一個則是標準 SQL 資料表。

SELECT url.description
FROM ClickStream cs
JOIN UrlDescription url ON cs.url = url.name
WHERE cs.url = 'msdn.microsoft.com';

F. 將資料從 Hadoop 匯入至 SQL 資料表

此範例會建立新的 SQL 資料表 ms_user，其能永久儲存標準 SQL 資料表 user 及外部資料表 ClickStream 之間的聯結結果。

SELECT DISTINCT user.FirstName, user.LastName
INTO ms_user
FROM user INNER JOIN (
    SELECT * FROM ClickStream WHERE cs.url = 'www.microsoft.com'
    ) AS ms
ON user.user_ip = ms.user_ip;

G. 建立 SQL Server 的外部資料表

在您建立資料庫範圍認證之前，使用者資料庫必須具有保護認證的主要金鑰。 如需詳細資訊，請參閱 CREATE MASTER KEY 和 CREATE DATABASE SCOPED CREDENTIAL。

     -- Create a Master Key
      CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0me!nfo';
    GO
     /*  specify credentials to external data source
     *  IDENTITY: user name for external source.
     *  SECRET: password for external source.
     */
     CREATE DATABASE SCOPED CREDENTIAL SqlServerCredentials
     WITH IDENTITY = 'username', Secret = 'password';
    GO

建立名為 SQLServerInstance 的新外部資料來源，以及名為 sqlserver.customer 的外部資料表：

    /* LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE SQLServerInstance
    WITH (
    LOCATION = 'sqlserver://SqlServer',
    -- PUSHDOWN = ON | OFF,
      CREDENTIAL = SQLServerCredentials
    );
    GO

    CREATE SCHEMA sqlserver;
    GO

     /* LOCATION: sql server table/view in 'database_name.schema_name.object_name' format
     * DATA_SOURCE: the external data source, created above.
     */
     CREATE EXTERNAL TABLE sqlserver.customer(
     C_CUSTKEY INT NOT NULL,
     C_NAME VARCHAR(25) NOT NULL,
     C_ADDRESS VARCHAR(40) NOT NULL,
     C_NATIONKEY INT NOT NULL,
     C_PHONE CHAR(15) NOT NULL,
     C_ACCTBAL DECIMAL(15,2) NOT NULL,
     C_MKTSEGMENT CHAR(10) NOT NULL,
     C_COMMENT VARCHAR(117) NOT NULL
      )
      WITH (
      LOCATION='tpch_10.dbo.customer',
      DATA_SOURCE=SqlServerInstance
     );

I. 建立 Oracle 的外部資料表

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

   /*
   * LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
   * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
   * CONNECTION_OPTIONS: Specify driver location
   * CREDENTIAL: the database scoped credential, created above.
   */
   CREATE EXTERNAL DATA SOURCE external_data_source_name
   WITH (
     LOCATION = 'oracle://<server address>[:<port>]',
     -- PUSHDOWN = ON | OFF,
     CREDENTIAL = credential_name)

   /*
   * LOCATION: Oracle table/view in '<database_name>.<schema_name>.<object_name>' format. Note this may be case sensitive in the Oracle database.
   * DATA_SOURCE: the external data source, created above.
   */
   CREATE EXTERNAL TABLE customers(
   [O_ORDERKEY] DECIMAL(38) NOT NULL,
   [O_CUSTKEY] DECIMAL(38) NOT NULL,
   [O_ORDERSTATUS] CHAR COLLATE Latin1_General_BIN NOT NULL,
   [O_TOTALPRICE] DECIMAL(15,2) NOT NULL,
   [O_ORDERDATE] DATETIME2(0) NOT NULL,
   [O_ORDERPRIORITY] CHAR(15) COLLATE Latin1_General_BIN NOT NULL,
   [O_CLERK] CHAR(15) COLLATE Latin1_General_BIN NOT NULL,
   [O_SHIPPRIORITY] DECIMAL(38) NOT NULL,
   [O_COMMENT] VARCHAR(79) COLLATE Latin1_General_BIN NOT NULL
   )
   WITH (
    LOCATION='DB1.mySchema.customer',
    DATA_SOURCE= external_data_source_name
   );

J. 建立 Teradata 的外部資料表

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

    /* LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CONNECTION_OPTIONS: Specify driver location
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH (
    LOCATION = teradata://<server address>[:<port>],
   -- PUSHDOWN = ON | OFF,
    CREDENTIAL =credential_name
    );


     /* LOCATION: Teradata table/view in '<database_name>.<object_name>' format
      * DATA_SOURCE: the external data source, created above.
      */
     CREATE EXTERNAL TABLE customer(
      L_ORDERKEY INT NOT NULL,
      L_PARTKEY INT NOT NULL,
     L_SUPPKEY INT NOT NULL,
     L_LINENUMBER INT NOT NULL,
     L_QUANTITY DECIMAL(15,2) NOT NULL,
     L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
     L_DISCOUNT DECIMAL(15,2) NOT NULL,
     L_TAX DECIMAL(15,2) NOT NULL,
     L_RETURNFLAG CHAR NOT NULL,
     L_LINESTATUS CHAR NOT NULL,
     L_SHIPDATE DATE NOT NULL,
     L_COMMITDATE DATE NOT NULL,
     L_RECEIPTDATE DATE NOT NULL,
     L_SHIPINSTRUCT CHAR(25) NOT NULL,
     L_SHIPMODE CHAR(10) NOT NULL,
     L_COMMENT VARCHAR(44) NOT NULL
     )
     WITH (
     LOCATION='customer',
     DATA_SOURCE= external_data_source_name
     );

K. 建立 MongoDB 的外部資料表

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

     /* LOCATION: Location string should be of format '<type>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CONNECTION_OPTIONS: Specify driver location
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH (
    LOCATION = mongodb://<server>[:<port>],
    -- PUSHDOWN = ON | OFF,
      CREDENTIAL = credential_name
    );

     /* LOCATION: MongoDB table/view in '<database_name>.<schema_name>.<object_name>' format
     * DATA_SOURCE: the external data source, created above.
     */
     CREATE EXTERNAL TABLE customers(
     [O_ORDERKEY] DECIMAL(38) NOT NULL,
     [O_CUSTKEY] DECIMAL(38) NOT NULL,
     [O_ORDERSTATUS] CHAR COLLATE Latin1_General_BIN NOT NULL,
     [O_TOTALPRICE] DECIMAL(15,2) NOT NULL,
     [O_ORDERDATE] DATETIME2(0) NOT NULL,
     [O_COMMENT] VARCHAR(79) COLLATE Latin1_General_BIN NOT NULL
     )
     WITH (
     LOCATION='customer',
     DATA_SOURCE= external_data_source_name
     );

L. 透過外部資料表查詢 S3 相容的物件儲存體

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

下列範例示範如何使用 T-SQL，透過查詢外部資料表來查詢儲存在 S3 相容物件儲存體中的 parquet 檔案。 此範例會使用外部資料來源內的相對路徑。

CREATE EXTERNAL DATA SOURCE s3_ds
WITH
(   LOCATION = 's3://<ip_address>:<port>/'
,   CREDENTIAL = s3_dc
);
GO
CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH(FORMAT_TYPE = PARQUET);
GO
CREATE EXTERNAL TABLE Region(
r_regionkey BIGINT,
r_name CHAR(25),
r_comment VARCHAR(152) )
WITH (LOCATION = '/region/', DATA_SOURCE = 's3_ds',
FILE_FORMAT = ParquetFileFormat);
GO

後續步驟

在下列文章中深入了解相關概念：

• CREATE EXTERNAL DATA SOURCE
• CREATE EXTERNAL FILE FORMAT
• sp_data_source_objects (Transact-SQL)
• 適用於 Azure Data Studio 的資料虛擬化延伸模組

概觀：Azure SQL Database

在 Azure SQL Database 中，建立彈性查詢 (預覽階段) 的外部資料表。

另請參閱 CREATE EXTERNAL DATA SOURCE。

語法

-- Create a table for use with elastic query
CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH ( <sharded_external_table_options> )
[;]

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

<sharded_external_table_options> ::=
        DATA_SOURCE = external_data_source_name,
        SCHEMA_NAME = N'nonescaped_schema_name',
        OBJECT_NAME = N'nonescaped_object_name',
        [DISTRIBUTION  = SHARDED(sharding_column_name) | REPLICATED | ROUND_ROBIN]]
    )
[;]

引數

{ database_name.schema_name.table_name | schema_name.table_name | table_name }

要建立之資料表名稱的第一到第三部分。 針對外部資料表，SQL 只會儲存資料表中繼資料，以及 Azure SQL Database 中所參考檔案或資料夾的基本統計資料。 不會在 Azure SQL Database 中移動或儲存任何實際資料。

<column_definition> [ ,...n ]

CREATE EXTERNAL TABLE 支援設定資料行名稱、資料類型、可 NULL 性和定序功能。 您無法在外部資料表上使用 DEFAULT CONSTRAINT。

資料行定義 (包括資料類型及資料行數目) 必須符合外部檔案中的資料。 若有不相符的情形，系統在查詢實際資料時將會拒絕檔案資料列。

分區外部資料表選項

針對彈性查詢指定外部資料來源 (非 SQL Server 資料來源) 及發佈方法。

DATA_SOURCE

DATA_SOURCE 子句會定義用於外部資料表的外部資料來源 (分區對應)。 如需範例，請參閱建立外部資料表。

SCHEMA_NAME 和 OBJECT_NAME

SCHEMA_NAME 和 OBJECT_NAME 子句會將外部資料表定義對應至不同結構描述中的資料表。 如果省略，即會假設遠端物件的結構描述為 "dbo" 並假設其名稱與所定義的外部資料表名稱相同。 如果您的遠端資料表名稱已存在於您要建立外部資料表的資料庫中，這會很有用。 例如，您想要定義外部資料表以取得相應放大的資料層上目錄檢視或 DMV 的彙總檢視。 由於目錄檢視和 DMV 已經存在於本機，所以您無法將其名稱使用於外部資料表定義。 可以改為使用不同的名稱，並使用目錄檢視名稱，或 SCHEMA_NAME 和/或 OBJECT_NAME 子句中 DMV 的名稱。 如需範例，請參閱建立外部資料表。

DISTRIBUTION

選擇性。 只有類型為 SHARD_MAP_MANAGER 的資料庫才需要這個引數。 這個引數能控制資料表是否會被視為分區資料表或複寫資料表。 使用 SHARDED (column name) 資料表，來自不同資料表的資料便不會彼此重疊。 REPLICATED 指定資料表在每個分區上都有相同的資料。 ROUND_ROBIN 指出系統會使用應用程式特定的方法來散發資料。

DISTRIBUTION 子句會指定用於此資料表的資料散發。 查詢處理器會利用 DISTRIBUTION 子句中提供的資訊來建置最有效率的查詢計劃。

• SHARDED 表示跨資料庫水平分割資料。 用於資料散發的分割索引鍵是 sharding_column_name 參數。
• REPLICATED 表示資料表的相同複本存在於每個資料庫上。 您必須負責確保複本在所有資料庫上都相同。
• ROUND_ROBIN 表示使用應用程式相依的散發方法，以水平方式分割資料表。

權限

可存取外部資料表的使用者可以在外部資料來源定義中所提供的認證下，自動取得基礎遠端資料表的存取權。 避免透過外部資料來源認證提高不想提高的權限。 對外部資料表使用「授與」或「撤銷」，就像它是一般的資料表一樣。 一旦您已定義外部資料來源和外部資料表，現在您可以對外部資料表使用完整的 T-SQL。

錯誤處理

執行 CREATE EXTERNAL TABLE 陳述式時，若嘗試連線失敗，陳述式便會失敗，且不會建立外部資料表。 由於 SQL Database 在查詢失敗之前會多次嘗試連線，因此可能需要一分鐘或更久的時間，命令才會失敗。

備註

在臨機操作查詢的案例中 (例如 SELECT FROM EXTERNAL TABLE)，SQL Database 會將擷取自外部資料來源的資料列儲存在暫存資料表中。 在查詢完成之後，SQL Database 便會移除並刪除該暫存資料表。 SQL 資料表中不會永久存放資料。

相反地，在匯入案例中 (例如 SELECT INTO FROM EXTERNAL TABLE)，SQL Database 會將擷取自外部資料來源的資料列，以永久資料的形式儲存在 SQL 資料表中。 新的資料表會在查詢執行期間，於 SQL Database 擷取外部資料時建立。

您可以建立許多參考相同或不同外部資料來源的外部資料表。

您可以建立多個參考不同外部資料來源的外部資料表。

局限性

• 隔離語意：透過外部數據表存取數據不會遵守 SQL Server 內的隔離語意。 這表示查詢外部數據表不會強加任何鎖定或快照集隔離。 因此，如果外部數據源中的數據正在變更，則數據傳回可能會變更。 相同的查詢在每次針對外部資料表執行時，都有可能傳回不同的結果。 同樣地，在移動或移除外部資料的情況下，查詢也有可能會失敗。

• 不支援建構和作業：

  • 外部資料表資料行上的 DEFAULT 限制式。
  • 刪除、插入及更新的資料操作語言 (DML) 作業。
  • 外部資料表資料行上的動態資料遮罩。
  • Azure SQL Database 中的外部資料表不支援資料指標。

• 只有常值述詞：查詢中定義的常值述詞只能向下推送至外部數據源。 這不同於連結的伺服器，而且可以使用存取查詢執行期間決定的述詞，也就是在查詢計劃中搭配巢狀迴圈使用時。 這通常會導致整個外部數據表在本機複製，然後聯結。

  在下列範例中，如果 External.Orders 是外部數據表，而且 Customer 是本機數據表，則查詢會在本機複製整個外部數據表，因為編譯時期並不知道所需的述詞。

  SELECT Orders.OrderId, Orders.OrderTotal
  FROM External.Orders
  WHERE CustomerId IN (
      SELECT TOP 1 CustomerId
      FROM Customer
      WHERE CustomerName = 'MyCompany'
  );
  

• 沒有平行處理原則：使用外部數據表可防止在查詢計劃中使用平行處理原則。

• 執行為遠端查詢：外部數據表會實作為遠端查詢，因此傳回的數據列估計數目一般為 1000。 根據用來篩選外部數據表的述詞類型，還有其他規則。 它們是以規則為基礎的估計值，而不是根據外部資料表中的實際資料進行評估。 最佳化工具不會存取遠端資料源來取得更精確的估計值。

• 私人端點不支援：當與遠端數據表的連線為私人端點時，不支援外部數據表查詢。

資料類型限制

下列資料類型不能用在 PolyBase 外部資料表中：

• geography
• geometry
• hierarchyid
• image
• text
• nText
• xml
• 任何使用者定義的類型

鎖定

SCHEMARESOLUTION 物件上的共用鎖定。

範例

A. 建立 Azure SQL Database 的外部資料表

CREATE EXTERNAL TABLE [dbo].[CustomerInformation]
( [CustomerID] [int] NOT NULL,
  [CustomerName] [varchar](50) NOT NULL,
  [Company] [varchar](50) NOT NULL)
WITH
( DATA_SOURCE = MyElasticDBQueryDataSrc)

B. 針對分區資料來源建立外部資料表

此範例會使用 SCHEMA_NAME 和 OBJECT_NAME 子句將遠端 DMV 重新對應至外部資料表。

CREATE EXTERNAL TABLE [dbo].[all_dm_exec_requests]([session_id] smallint NOT NULL,
  [request_id] int NOT NULL,
  [start_time] datetime NOT NULL,
  [status] nvarchar(30) NOT NULL,
  [command] nvarchar(32) NOT NULL,
  [sql_handle] varbinary(64),
  [statement_start_offset] int,
  [statement_end_offset] int,
  [cpu_time] int NOT NULL)
WITH
(
  DATA_SOURCE = MyExtSrc,
  SCHEMA_NAME = 'sys',
  OBJECT_NAME = 'dm_exec_requests',
  DISTRIBUTION=ROUND_ROBIN
);

後續步驟

若要深入了解 Azure SQL Database 中的外部資料表，請參閱下列文章：

• Azure SQL Database 彈性查詢概觀 \(部分機器翻譯\)
• 跨相應放大的雲端資料庫報告
• 開始使用跨資料庫查詢 (垂直資料分割) \(部分機器翻譯\)

概觀：Azure Synapse Analytics

使用外部資料表來：

• 專用 SQL 集區可以從 Hadoop、Azure Blob 儲存體和 Azure Data Lake Storage Gen1 和 Gen2 查詢、匯入和儲存資料。
• 無伺服器 SQL 集區可以從 Azure Blob 儲存體、Azure Data Lake Storage Gen1 和 Gen2 查詢、匯入和儲存資料。 無伺服器不支援 TYPE=Hadoop。

另請參閱 CREATE EXTERNAL DATA SOURCE 及 DROP EXTERNAL TABLE。

如需搭配 Azure Synapse 使用外部資料表的更多指引和範例，請參閱搭配 Synapse SQL 使用外部資料表。

語法

CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'hdfs_folder_or_filepath',
        DATA_SOURCE = external_data_source_name,
        FILE_FORMAT = external_file_format_name
        [ , <reject_options> [ ,...n ] ]
    )
[;]

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage, 
    | REJECT_VALUE = reject_value,
    | REJECT_SAMPLE_VALUE = reject_sample_value,
    | REJECTED_ROW_LOCATION = '/REJECT_Directory'
}

CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'folder_or_filepath',
        DATA_SOURCE = external_data_source_name,
        FILE_FORMAT = external_file_format_name,
        [ , <reject_options> [ ,...n ] ]
        [, TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}' ]         
    )
[;]
<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]

<reject_options> ::=
{
    | REJECT_TYPE = value, 
    | REJECT_VALUE = reject_value,
    | REJECT_SAMPLE_VALUE = reject_sample_value,
    | REJECTED_ROW_LOCATION = '/REJECT_Directory'
}

引數

{ database_name.schema_name.table_name | schema_name.table_name | table_name }

要建立之資料表名稱的第一到第三部分。 針對外部資料表，只有資料表中繼資料，以及 Azure Data Lake、Hadoop 或 Azure Blob 儲存體中所參考檔案或資料夾的基本統計資料。 建立外部資料表時，不會移動或儲存實際資料。

<column_definition> [ ,...n ]

CREATE EXTERNAL TABLE 支援設定資料行名稱、資料類型、可 NULL 性和定序功能。 您無法在外部資料表上使用 DEFAULT CONSTRAINT。

• 讀取分隔檔案時，資料行定義 (包括資料類型及資料行數目) 必須符合外部檔案中的資料。 若有不相符的情形，系統在查詢實際資料時將會拒絕檔案資料列。
• 從 Parquet 檔案讀取時，您只能指定要讀取的資料行，並略過其餘部分。

LOCATION = 'folder_or_filepath'

指定位於 Azure Data Lake、Hadoop 或 Azure Blob 儲存體中之實際資料的資料夾或檔案路徑，以及檔案名稱。 位置會從根資料夾開始。 根資料夾是在外部資料來源中指定的資料位置。 CREATE EXTERNAL TABLE AS SELECT 陳述式會建立路徑及資料夾 (若不存在的話)。 CREATE EXTERNAL TABLE 則不會建立路徑和資料夾。

若您將 LOCATION 指定為資料夾，會從外部資料表中選取的 PolyBase 查詢，將會從該資料夾及其所有子資料夾中擷取檔案。 PolyBase 和 Hadoop 相同，並不會傳回隱藏的資料夾。 它也不會傳回檔案名稱是以底線 (_) 或句號 (.) 開始的檔案。

在下圖範例中，若為 LOCATION='/webdata/'，PolyBase 查詢將會從 mydata.txt 和 mydata2.txt 傳回資料列。 查詢將不會傳回 mydata3.txt，因為其為隱藏資料夾的子資料夾。 此外，查詢不會傳回 _hidden.txt，因為其為隱藏的檔案。

與 Hadoop 外部資料表不同的是，除非您在路徑結尾指定 /**，否則原生外部資料表不會傳回子資料夾。 在此範例中，若為 LOCATION='/webdata/'，無伺服器 SQL 集區查詢會傳回來自 mydata.txt 的資料列。 而不會傳回 mydata2 .txt 和 mydata3.txt，因為位於子資料夾中。 Hadoop 資料表會傳回任何子資料夾中的所有檔案。

Hadoop 和原生外部資料表都會跳過名稱開頭為底線 (_) 或句點 (.) 的檔案。

DATA_SOURCE = external_data_source_name

可指定包含外部資料位置的外部資料來源名稱。 此位置位於 Azure Data Lake 中。 若要建立外部資料來源，請使用 CREATE EXTERNAL DATA SOURCE。

FILE_FORMAT = external_file_format_name

可指定外部檔案格式物件的名稱，該物件中儲存了外部資料的檔案類型和壓縮方法。 若要建立外部檔案格式，請使用 CREATE EXTERNAL FILE FORMAT。

TABLE_OPTIONS

指定一組選項，描述如何讀取基礎檔案。 目前唯一可用的選項是，{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}指示外部資料表忽略基礎檔案更新，即使這可能導致部分不一致的讀取作業。 請在您經常附加檔案的特殊情況下，才使用此選項。 此選項適用於 CSV 格式的無伺服器 SQL 集區。

REJECT 選項

Reject 選項在 Azure Synapse Analytics 中處於無伺服器 SQL 集區的預覽狀態。

此選項只能與 TYPE = HADOOP 的外部資料源搭配使用。

您可以指定能決定 PolyBase 如何處理其從外部資料來源所擷取之已修改記錄的拒絕參數。 若資料記錄的實際資料類型或資料行數目，與外部資料表的資料行定義不相符，該資料記錄就會被系統視為「已修改」。

當您不指定或變更拒絕值時，PolyBase 就會使用預設值。 拒絕參數的相關資訊會在您搭配 CREATE EXTERNAL TABLE 陳述式建立外部資料表時，以額外中繼資料的形式儲存。 當未來有 SELECT 陳述式或 SELECT INTO SELECT 陳述式從外部資料表中選取資料時，PolyBase 將會使用拒絕選項來判斷在實際的查詢失敗之前，可以拒絕的資料列數目或百分比。 查詢將會傳回 (部分) 結果，直到超過拒絕閾值為止。 接著它便會失敗並顯示適當的錯誤訊息。

只有無伺服器 SQL 集區才支援 PARSER_VERSION 格式選項。

REJECT_TYPE = value | 百分比

指明是要將 REJECT_VALUE 選項指定為常值還是百分比。

value

REJECT_VALUE 是常值，而不是百分比。 PolyBase 查詢會在被拒絕資料列的數目超過 reject_value 時失敗。

例如，若 REJECT_VALUE = 5 且 REJECT_TYPE = value，PolyBase SELECT 查詢將會在系統拒絕五個資料列之後失敗。

percentage

REJECT_VALUE 是百分比，而不是常值。 PolyBase 查詢會在被拒絕資料列的百分比超過 reject_value 時失敗。 系統會依據間隔時間計算失敗的資料列所佔百分比。

REJECT_VALUE = reject_value

指定在查詢失敗之前可以拒絕的資料列數目或百分比。

• 針對 REJECT_TYPE = value，reject_value 必須為介於 0 和 2,147,483,647 的整數。
• 針對 REJECT_TYPE = percentage，reject_value 必須為介於 0 和 100 的浮點數。 百分比僅對於 TYPE=HADOOP 的專用 SQL 集區有效。

拒絕資料列的數目超過 reject_value 時，查詢就會失敗。 舉例來說，假設 REJECT_VALUE = 5 且 REJECT_TYPE = value，系統拒絕五個資料列後，SELECT 查詢就失敗。

REJECT_SAMPLE_VALUE = reject_sample_value

當您指定 REJECT_TYPE = percentage 時，這是必要的屬性。 它會決定在 PolyBase 重新計算被拒絕資料列的百分比之前，應嘗試擷取的資料列數目。

reject_sample_value 參數必須是介於 0 和 2,147,483,647 的整數。

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

範例：

此範例說明三個 REJECT 選項彼此如何互動。 例如，如果 REJECT_TYPE = percentage、REJECT_VALUE = 30 且 REJECT_SAMPLE_VALUE = 100，就可能發生下列案例：

• PolyBase 會嘗試擷取前 100 個資料列；其中有 25 個失敗，75 個成功。
• 失敗資料列的百分比會計算為 25%，低於拒絕值 30%。 因此，PolyBase 將會繼續從外部資料來源擷取資料。
• PolyBase 會嘗試載入接下來的 100 個資料列；這次有 25 個資料列成功，75 個資料列失敗。
• 失敗資料列的百分比在重新計算後為 50%。 失敗資料列的百分比已超出 30% 的拒絕值。
• PolyBase 查詢在嘗試傳回前 200 個資料列後，會因被拒絕的資料列達 50% 而失敗。 請注意，相符的資料列會在 PolyBase 查詢偵測到超出拒絕閾值之前傳回。

REJECTED_ROW_LOCATION = Directory Location

指定外部資料來源中，已拒絕資料列和相應錯誤檔案應寫入的目錄。

如果指定路徑不存在，系統將會加以建立。 系統會建立名稱為 _rejectedrows 的子目錄。 _ 字元可確保該目錄從其他資料處理逸出，除非已明確在位置參數中指名。

• 在無伺服器 SQL 集區中，路徑為 YearMonthDay_HourMinuteSecond_StatementID。 您可使用陳述式識別碼，讓資料夾與產生識別碼的查詢相互關聯。
• 在專用 SQL 集區中，建立的路徑是以載入提交的時間為基礎，而格式為 YearMonthDay -HourMinuteSecond，例如 20180330-173205。

在此資料中寫入了兩種類型的檔案，分別是 _reason 檔案與資料檔案。

如需詳細資訊，請參閱 CREATE EXTERNAL DATA SOURCE。

原因檔案與資料檔案均具有與 CTAS 陳述式相關的 queryID。 因為資料與原因檔案在不同的檔案中，所以對應的檔案會具有相符尾碼。

在無伺服器 SQL 集區中，error.json 檔案包含發生與拒絕資料列相關錯誤的 JSON 陣列。 代表錯誤的元素都包含下列屬性：

權限

需要下列使用者權限：

• CREATE TABLE
• ALTER ANY SCHEMA
• ALTER ANY EXTERNAL DATA SOURCE
• ALTER ANY EXTERNAL FILE FORMAT

請注意，建立外部資料來源的登入，必須具有讀取和寫入至位於 Hadoop 或 Azure Blob 儲存體上外部資料來源的權限。

錯誤處理

執行 CREATE EXTERNAL TABLE 陳述式的同時，PolyBase 會嘗試連線至外部資料來源。 如果連線的嘗試失敗，陳述式就會失敗，且不會建立外部資料表。 由於 PolyBase 在查詢失敗之前會多次嘗試連線，因此可能需要一分鐘或更久的時間，命令才會失敗。

備註

在臨機操作查詢的案例 (例如 SELECT FROM EXTERNAL TABLE) 中，PolyBase 會將擷取自外部資料來源的資料列，儲存在暫存資料表中。 在查詢完成之後，PolyBase 便會移除並刪除該暫存資料表。 SQL 資料表中不會永久存放資料。

相反地，在匯入案例中 (例如 SELECT INTO FROM EXTERNAL TABLE)，PolyBase 會將擷取自外部資料來源的資料列，以永久資料的形式儲存在 SQL 資料表中。 新的資料表會在查詢執行期間，當 PolyBase 擷取外部資料時建立。

PolyBase 可以將部分的查詢計算推送至 Hadoop 以改善查詢效能。 此動作稱為述詞下推。 若要啟用它，請在 CREATE EXTERNAL DATA SOURCE 中指定 Hadoop 資源管理員位置選項。

您可以建立許多參考相同或不同外部資料來源的外部資料表。

請注意使用 UTF-8 定序的來源資料。 針對使用 UTF-8 定序的任何來源資料，您必須在 CREATE EXTERNAL TABLE 陳述式中手動提供每個 UTF-8 資料行的非 UTF-8 定序。 這是因為 UTF-8 支援不會延伸至外部資料表。 當您嘗試使用 UTF-8 定序建立外部資料表時，便會收到 Unsupported collation 錯誤訊息。 如果外部資料表的資料庫定序是 UTF-8 定序，除非您提供明確的非 UTF-8 資料行定序 (例如 [UTF8_column] varchar(128) COLLATE LATIN1_GENERAL_100_CI_AS_KS_WS NOT NULL,)，否則外部資料表建立將會失敗。

Azure Synapse Analytics 中的無伺服器和專用 SQL 集區會使用不同的程式碼基底來進行資料虛擬化。 無伺服器 SQL 集區支援原生資料虛擬化技術。 專用 SQL 集區同時支援原生和 PolyBase 資料虛擬化。 使用 TYPE=HADOOP 建立 EXTERNAL DATA SOURCE 時，系統會使用 PolyBase 資料虛擬化。

限制事項

由於外部資料表的資料未直接受到 Azure Synapse 管理控制，其可以隨時由外部處理序變更或移除。 因此，針對外部資料表的查詢結果並不保證具有確定性。 相同的查詢在每次針對外部資料表執行時，都有可能傳回不同的結果。 同樣地，在移動或移除外部資料的情況下，查詢也有可能會失敗。

您可以建立多個參考不同外部資料來源的外部資料表。

外部資料表上僅允許使用下列資料定義語言 (DDL) 陳述式：

• CREATE TABLE 和 DROP TABLE
• CREATE STATISTICS 和 DROP STATISTICS
• CREATE VIEW 和 DROP VIEW

不支援的建構和作業：

• 外部資料表資料行上的 DEFAULT 限制式
• 刪除、插入及更新的資料操作語言 (DML) 作業
• 外部資料表資料行上的動態資料遮罩

查詢限制

建議每個資料夾包含的檔案數不要超過 3 萬個。 參考太多檔案時，可能會發生 Java 虛擬機器 (JVM) 記憶體不足的例外狀況，或效能可能會變差。

資料表寬度限制

Azure 資料倉儲中 PolyBase 具有 1 MB 的資料列寬度限制，這是以依資料表定義的單一有效資料列大小上限為基礎。 若資料行結構描述的總和超過 1 MB，PolyBase 便無法查詢資料。

資料類型限制

下列資料類型不能用在 PolyBase 外部資料表中：

• geography
• geometry
• hierarchyid
• image
• text
• nText
• xml
• 任何使用者定義的類型

鎖定

SCHEMARESOLUTION 物件上的共用鎖定。

範例

A. 將資料從 ADLS Gen 2 匯入至 Azure Synapse Analytics

以 Gen ADLS Gen 1 為例，請參閱建立外部資料來源。

-- These values come from your Azure Active Directory Application used to authenticate to ADLS Gen 2.
CREATE DATABASE SCOPED CREDENTIAL ADLUser
WITH IDENTITY = '<clientID>@\<OAuth2.0TokenEndPoint>',
SECRET = '<KEY>' ;

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH (TYPE = HADOOP,
      LOCATION = 'abfss://data@pbasetr.azuredatalakestore.net'
);

CREATE EXTERNAL FILE FORMAT TextFileFormat
WITH
(
    FORMAT_TYPE = DELIMITEDTEXT
    , FORMAT_OPTIONS ( FIELD_TERMINATOR = '|'
       , STRING_DELIMITER = ''
      , DATE_FORMAT = 'yyyy-MM-dd HH:mm:ss.fff'
      , USE_TYPE_DEFAULT = FALSE
      )
);

CREATE EXTERNAL TABLE [dbo].[DimProduct_external]
( [ProductKey] [int] NOT NULL,
  [ProductLabel] nvarchar NULL,
  [ProductName] nvarchar NULL )
WITH
(
    LOCATION='/DimProduct/' ,
    DATA_SOURCE = AzureDataLakeStore ,
    FILE_FORMAT = TextFileFormat ,
    REJECT_TYPE = VALUE ,
    REJECT_VALUE = 0
);

CREATE TABLE [dbo].[DimProduct]
WITH (DISTRIBUTION = HASH([ProductKey] ) )
AS SELECT * FROM
[dbo].[DimProduct_external] ;

B. 將資料從 Parquet 匯入至 Azure Synapse Analytics

下列範例會建立外部資料表， 接著會傳回第一個資料列：

CREATE EXTERNAL TABLE census_external_table
(
    decennialTime varchar(20),
    stateName varchar(100),
    countyName varchar(100),
    population int,
    race varchar(50),
    sex    varchar(10),
    minAge int,
    maxAge int
)  
WITH (
    LOCATION = '/parquet/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
);
GO
SELECT TOP 1 * FROM census_external_table;

後續步驟

在下列文章中深入了解外部資料表與相關概念：

• CREATE EXTERNAL DATA SOURCE
• CREATE EXTERNAL FILE FORMAT
• CREATE EXTERNAL TABLE AS SELECT
• CREATE TABLE AS SELECT (Azure Synapse Analytics)

概觀：分析平台系統

使用外部資料表來：

• 搭配 Transact-SQL 查詢 Hadoop 或 Azure Blob 儲存體資料。
• 從 Hadoop 或 Azure Blob 儲存體將資料匯入並儲存至 Analytics Platform System。

另請參閱 CREATE EXTERNAL DATA SOURCE 及 DROP EXTERNAL TABLE。

語法

CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'hdfs_folder_or_filepath',
        DATA_SOURCE = external_data_source_name,
        FILE_FORMAT = external_file_format_name
        [ , <reject_options> [ ,...n ] ]
    )
[;]

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

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

}

引數

{ database_name.schema_name.table_name | schema_name.table_name | table_name }

要建立之資料表名稱的第一到第三部分。 針對外部資料表，Analytics Platform System 只會儲存資料表中繼資料，以及 Hadoop 或 Azure Blob 儲存體中所參考檔案或資料夾的基本統計資料。 不會在 Analytics Platform System 中移動或儲存任何實際資料。

<column_definition> [ ,...n ]

CREATE EXTERNAL TABLE 支援設定資料行名稱、資料類型、可 NULL 性和定序功能。 您無法在外部資料表上使用 DEFAULT CONSTRAINT。

資料行定義 (包括資料類型及資料行數目) 必須符合外部檔案中的資料。 若有不相符的情形，系統在查詢實際資料時將會拒絕檔案資料列。

LOCATION = 'folder_or_filepath'

指定位於 Hadoop 或 Azure Blob 儲存體中之實際資料的資料夾或檔案路徑，以及檔案名稱。 位置會從根資料夾開始。 根資料夾是在外部資料來源中指定的資料位置。

在 Analytics Platform System 中，CREATE EXTERNAL TABLE AS SELECT 陳述式會建立路徑和資料夾 (若不存在的話)。 CREATE EXTERNAL TABLE 則不會建立路徑和資料夾。

若您將 LOCATION 指定為資料夾，會從外部資料表中選取的 PolyBase 查詢，將會從該資料夾及其所有子資料夾中擷取檔案。 PolyBase 和 Hadoop 相同，並不會傳回隱藏的資料夾。 它也不會傳回檔案名稱是以底線 (_) 或句號 (.) 開始的檔案。

在下圖範例中，若為 LOCATION='/webdata/'，PolyBase 查詢將會從 mydata.txt 和 mydata2.txt 傳回資料列。 查詢將不會傳回 mydata3.txt，因為其為隱藏資料夾的子資料夾。 此外，查詢不會傳回 _hidden.txt，因為其為隱藏的檔案。

若要變更預設設定並僅從根資料夾讀取，請在 <polybase.recursive.traversal> 設定檔中將 core-site.xml 屬性設為 'false'。 此檔案位於 SQL Server 根目錄底下<SqlBinRoot>\PolyBase\Hadoop\Conf\bin。 例如： C:\Program Files\Microsoft SQL Server\MSSQL13.XD14\MSSQL\Binn\ 。

DATA_SOURCE = external_data_source_name

可指定包含外部資料位置的外部資料來源名稱。 此位置可為 Hadoop 或 Azure Blob 儲存體。 若要建立外部資料來源，請使用 CREATE EXTERNAL DATA SOURCE。

FILE_FORMAT = external_file_format_name

可指定外部檔案格式物件的名稱，該物件中儲存了外部資料的檔案類型和壓縮方法。 若要建立外部檔案格式，請使用 CREATE EXTERNAL FILE FORMAT。

拒絕選項

此選項只能與 TYPE = HADOOP 的外部資料源搭配使用。

您可以指定能決定 PolyBase 如何處理其從外部資料來源所擷取之已修改記錄的拒絕參數。 若資料記錄的實際資料類型或資料行數目，與外部資料表的資料行定義不相符，該資料記錄就會被系統視為「已修改」。

當您不指定或變更拒絕值時，PolyBase 就會使用預設值。 拒絕參數的相關資訊會在您搭配 CREATE EXTERNAL TABLE 陳述式建立外部資料表時，以額外中繼資料的形式儲存。 當未來有 SELECT 陳述式或 SELECT INTO SELECT 陳述式從外部資料表中選取資料時，PolyBase 將會使用拒絕選項來判斷在實際的查詢失敗之前，可以拒絕的資料列數目或百分比。 查詢將會傳回 (部分) 結果，直到超過拒絕閾值為止。 接著它便會失敗並顯示適當的錯誤訊息。

REJECT_TYPE = value | 百分比

指明是要將 REJECT_VALUE 選項指定為常值還是百分比。

value

REJECT_VALUE 是常值，而不是百分比。 PolyBase 查詢會在被拒絕資料列的數目超過 reject_value 時失敗。

例如，若 REJECT_VALUE = 5 且 REJECT_TYPE = value，PolyBase SELECT 查詢將會在系統拒絕五個資料列之後失敗。

percentage

REJECT_VALUE 是百分比，而不是常值。 PolyBase 查詢會在被拒絕資料列的百分比超過 reject_value 時失敗。 系統會依據間隔時間計算失敗的資料列所佔百分比。

REJECT_VALUE = reject_value

指定在查詢失敗之前可以拒絕的資料列數目或百分比。

針對 REJECT_TYPE = value，reject_value 必須為介於 0 和 2,147,483,647 的整數。

針對 REJECT_TYPE = percentage，reject_value 必須為介於 0 和 100 的浮點數。

REJECT_SAMPLE_VALUE = reject_sample_value

當您指定 REJECT_TYPE = percentage 時，這是必要的屬性。 它會決定在 PolyBase 重新計算被拒絕資料列的百分比之前，應嘗試擷取的資料列數目。

reject_sample_value 參數必須是介於 0 和 2,147,483,647 的整數。

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

範例：

此範例說明三個 REJECT 選項彼此如何互動。 例如，如果 REJECT_TYPE = percentage、REJECT_VALUE = 30 且 REJECT_SAMPLE_VALUE = 100，就可能發生下列案例：

• PolyBase 會嘗試擷取前 100 個資料列；其中有 25 個失敗，75 個成功。
• 失敗資料列的百分比會計算為 25%，低於拒絕值 30%。 因此，PolyBase 將會繼續從外部資料來源擷取資料。
• PolyBase 會嘗試載入接下來的 100 個資料列；這次有 25 個資料列成功，75 個資料列失敗。
• 失敗資料列的百分比在重新計算後為 50%。 失敗資料列的百分比已超出 30% 的拒絕值。
• PolyBase 查詢在嘗試傳回前 200 個資料列後，會因被拒絕的資料列達 50% 而失敗。 請注意，相符的資料列會在 PolyBase 查詢偵測到超出拒絕閾值之前傳回。

權限

需要下列使用者權限：

• CREATE TABLE
• ALTER ANY SCHEMA
• ALTER ANY EXTERNAL DATA SOURCE
• ALTER ANY EXTERNAL FILE FORMAT
• CONTROL DATABASE

請注意，建立外部資料來源的登入，必須具有讀取和寫入至位於 Hadoop 或 Azure Blob 儲存體上外部資料來源的權限。

錯誤處理

執行 CREATE EXTERNAL TABLE 陳述式的同時，PolyBase 會嘗試連線至外部資料來源。 如果連線的嘗試失敗，陳述式就會失敗，且不會建立外部資料表。 由於 PolyBase 在查詢失敗之前會多次嘗試連線，因此可能需要一分鐘或更久的時間，命令才會失敗。

備註

在臨機操作查詢的案例 (例如 SELECT FROM EXTERNAL TABLE) 中，PolyBase 會將擷取自外部資料來源的資料列，儲存在暫存資料表中。 在查詢完成之後，PolyBase 便會移除並刪除該暫存資料表。 SQL 資料表中不會永久存放資料。

相反地，在匯入案例中 (例如 SELECT INTO FROM EXTERNAL TABLE)，PolyBase 會將擷取自外部資料來源的資料列，以永久資料的形式儲存在 SQL 資料表中。 新的資料表會在查詢執行期間，當 PolyBase 擷取外部資料時建立。

PolyBase 可以將部分的查詢計算推送至 Hadoop 以改善查詢效能。 此動作稱為述詞下推。 若要啟用它，請在 CREATE EXTERNAL DATA SOURCE 中指定 Hadoop 資源管理員位置選項。

您可以建立許多參考相同或不同外部資料來源的外部資料表。

限制事項

由於外部資料表資料未直接受到該設備的管理控制，因此其可以隨時由外部處理序變更或移除。 因此，針對外部資料表的查詢結果並不保證具有確定性。 相同的查詢在每次針對外部資料表執行時，都有可能傳回不同的結果。 同樣地，在移動或移除外部資料的情況下，查詢也有可能會失敗。

您可以建立多個參考不同外部資料來源的外部資料表。 如果您同時針對不同的 Hadoop 資料來源執行查詢，則每個 Hadoop 來源都必須使用相同的「Hadoop 連線能力」伺服器組態設定。 例如，您不能同時針對 Cloudera Hadoop 叢集和 Hortonworks Hadoop 叢集執行查詢，因為這些叢集是使用不同的組態設定。 如需組態設定和支援的組合，請參閱 PolyBase 連線能力設定。

外部資料表上僅允許使用下列資料定義語言 (DDL) 陳述式：

• CREATE TABLE 和 DROP TABLE
• CREATE STATISTICS 和 DROP STATISTICS
• CREATE VIEW 和 DROP VIEW

不支援的建構和作業：

• 外部資料表資料行上的 DEFAULT 限制式
• 刪除、插入及更新的資料操作語言 (DML) 作業
• 外部資料表資料行上的動態資料遮罩

查詢限制

執行 32 個並行的 PolyBase 查詢時，PolyBase 每個資料夾可取用的檔案數目上限為 33000 個檔案。 這個上限數同時包含了每個 HDFS 資料夾中的檔案和子資料夾。 如果並行程度小於 32，使用者就可以針對 HDFS 中內含超過 33000 個檔案的資料夾執行 PolyBase 查詢。 我們建議您使用簡短的外部檔案路徑，且所使用的每個 HDFS 資料夾檔案數目不要超過 30000 個檔案。 參考太多檔案時，可能會發生 Java 虛擬機器 (JVM) 記憶體不足的例外狀況。

資料表寬度限制

SQL Server 2016 中的 PolyBase 具有 32 KB 的資料列寬度限制，這是以依資料表定義的單一有效資料列大小上限為基礎。 若資料行結構描述的總和超過 32 KB，PolyBase 將無法查詢資料。

在 Azure Synapse Analytics 中，這項限制已提高至 1 MB。

資料類型限制

下列資料類型不能用在 PolyBase 外部資料表中：

• geography
• geometry
• hierarchyid
• image
• text
• nText
• xml
• 任何使用者定義的類型

鎖定

SCHEMARESOLUTION 物件上的共用鎖定。

安全性

外部資料表的資料檔案會儲存在 Hadoop 或 Azure Blob 儲存體中。 這些資料檔案是由您自己的處理程序所建立及管理。 因此您應負責管理外部資料的安全性。

範例

A. 聯結 HDFS 資料及 Analytics Platform System 資料

SELECT cs.user_ip FROM ClickStream cs
JOIN [User] u ON cs.user_ip = u.user_ip
WHERE cs.url = 'www.microsoft.com';

B. 從 HDFS 將資料列資料匯入至分散式 Analytics Platform System 資料表

CREATE TABLE ClickStream_PDW
WITH ( DISTRIBUTION = HASH (url) )
AS SELECT url, event_date, user_ip FROM ClickStream;

C. 從 HDFS 將資料列資料匯入至複寫 Analytics Platform System 資料表

CREATE TABLE ClickStream_PDW
WITH ( DISTRIBUTION = REPLICATE )
AS SELECT url, event_date, user_ip
FROM ClickStream;

後續步驟

在下列文章中深入了解 Analytics Platform System 中的外部資料表：

• CREATE EXTERNAL DATA SOURCE
• CREATE EXTERNAL FILE FORMAT
• CREATE EXTERNAL TABLE AS SELECT
• CREATE TABLE AS SELECT (Azure Synapse Analytics)

概觀：Azure SQL 受控執行個體

在 Azure SQL 受控執行個體中建立外部資料表。 如需完整資訊，請參閱具有 Azure SQL 受控執行個體的資料虛擬化。

Azure SQL 受控執行個體中的資料虛擬化提供 Azure Data Lake Storage Gen2 或 Azure Blob 儲存體中各種檔案格式的外部資料存取，以及使用 T-SQL 陳述式進行查詢，甚至使用聯結來合併資料與本機儲存的關聯式資料。

另請參閱 CREATE EXTERNAL DATA SOURCE 及 DROP EXTERNAL TABLE。

語法

CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'filepath',
        DATA_SOURCE = external_data_source_name,
        FILE_FORMAT = external_file_format_name
    )
[;]

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

引數

{ database_name.schema_name.table_name | schema_name.table_name | table_name }

要建立之資料表名稱的第一到第三部分。 針對外部資料表，只有資料表中繼資料，以及 Azure Data Lake 或 Azure Blob 儲存體中所參考檔案或資料夾的基本統計資料。 建立外部資料表時，不會移動或儲存實際資料。

<column_definition> [ ,...n ]

CREATE EXTERNAL TABLE 支援設定資料行名稱、資料類型、可 NULL 性和定序功能。 您無法在外部資料表上使用 DEFAULT CONSTRAINT。

資料行定義 (包括資料類型及資料行數目) 必須符合外部檔案中的資料。 若有不相符的情形，系統在查詢實際資料時將會拒絕檔案資料列。

LOCATION = 'folder_or_filepath'

指定位於 Azure Data Lake 或 Azure Blob 儲存體中實際資料的資料夾或檔案路徑，以及檔案名稱。 位置會從根資料夾開始。 根資料夾是在外部資料來源中指定的資料位置。 CREATE EXTERNAL TABLE 則不會建立路徑和資料夾。

若您將 LOCATION 指定為資料夾，Azure SQL 受控執行個體中從外部資料表選取的查詢，將會從該資料夾 (但不包含其所有子資料夾) 中擷取檔案。

Azure SQL 受控執行個體在子資料夾或隱藏資料夾中找不到檔案。 它也不會傳回檔案名稱是以底線 (_) 或句號 (.) 開始的檔案。

在下圖範例中，若為 LOCATION='/webdata/'，查詢將會從 mydata.txt 傳回資料列。 查詢將不會傳回 mydata2.txt，因為其位於子資料夾中，查詢不會傳回 mydata3.txt，因為其為隱藏的資料夾，且查詢不會傳回 _hidden.txt，因為其為隱藏的檔案。

DATA_SOURCE = external_data_source_name

可指定包含外部資料位置的外部資料來源名稱。 此位置位於 Azure Data Lake 中。 若要建立外部資料來源，請使用 CREATE EXTERNAL DATA SOURCE。

FILE_FORMAT = external_file_format_name

可指定外部檔案格式物件的名稱，該物件中儲存了外部資料的檔案類型和壓縮方法。 若要建立外部檔案格式，請使用 CREATE EXTERNAL FILE FORMAT。

權限

需要下列使用者權限：

• CREATE TABLE
• ALTER ANY SCHEMA
• ALTER ANY EXTERNAL DATA SOURCE
• ALTER ANY EXTERNAL FILE FORMAT

請注意，建立外部資料來源的登入，必須具有讀取和寫入至位於 Hadoop 或 Azure Blob 儲存體上外部資料來源的權限。

備註

在臨機操作的案例中 (例如 SELECT FROM EXTERNAL TABLE)，擷取自外部資料來源的資料列會儲存在暫存資料表中。 查詢完成之後，系統會移除資料列，並刪除暫存資料表。 SQL 資料表中不會永久存放資料。

相反地，在匯入案例中 (例如 SELECT INTO FROM EXTERNAL TABLE)，擷取自外部資料來源的資料列會以永久資料的形式儲存在 SQL 資料表中。 新的資料表會在查詢執行期間擷取外部資料時建立。

目前，具有 Azure SQL 受控執行個體的資料虛擬化是唯讀的。

您可以建立許多參考相同或不同外部資料來源的外部資料表。

限制事項

由於外部資料表的資料未直接受到 Azure SQL 受控執行個體管理控制，其可以隨時由外部處理序變更或移除。 因此，針對外部資料表的查詢結果並不保證具有確定性。 相同的查詢在每次針對外部資料表執行時，都有可能傳回不同的結果。 同樣地，在移動或移除外部資料的情況下，查詢也有可能會失敗。

您可以建立多個參考不同外部資料來源的外部資料表。

外部資料表上僅允許使用下列資料定義語言 (DDL) 陳述式：

• CREATE TABLE 和 DROP TABLE
• CREATE STATISTICS 和 DROP STATISTICS
• CREATE VIEW 和 DROP VIEW

不支援的建構和作業：

• 外部資料表資料行上的 DEFAULT 限制式
• 刪除、插入及更新的資料操作語言 (DML) 作業

資料表寬度限制

1 MB 的資料列寬度限制以依資料表定義的單一有效資料列大小上限為基礎。 如果資料行結構描述的總和大於 1 MB，資料虛擬化查詢將會失敗。

資料類型限制

下列資料類型無法用於 Azure SQL 受控執行個體中的外部資料表：

• geography
• geometry
• hierarchyid
• image
• text
• nText
• xml
• 任何使用者定義的類型

鎖定

SCHEMARESOLUTION 物件上的共用鎖定。

範例

A. 使用外部資料表從 Azure SQL 受控執行個體查詢外部資料

如需更多範例，請參閱建立外部資料來源，或參閱具有 Azure SQL 受控執行個體的資料虛擬化。

1. 如果資料庫主要金鑰不存在，請建立資料庫主要金鑰。

   -- Optional: Create MASTER KEY if it doesn't exist in the database:
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong Password>'
   GO
   

2. 使用 SAS 權杖建立資料庫範圍認證。 您也可以使用受控識別。

   CREATE DATABASE SCOPED CREDENTIAL MyCredential
   WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
   SECRET = '<KEY>' ; --Removing leading '?'
   GO
   

3. 使用認證建立外部資料來源。

   --Create external data source pointing to the file path, and referencing database-scoped credential:
   CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource
   WITH (
       LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
           CREDENTIAL = [MyCredential]
   )
   GO
   

4. 建立 EXTERNAL FILE FORMAT 和 EXTERNAL TABLE 以查詢資料，就像是本機資料表一樣。

   -- Or, create an EXTERNAL FILE FORMAT and an EXTERNAL TABLE
   
   --Create external file format
   CREATE EXTERNAL FILE FORMAT DemoFileFormat
   WITH (
    FORMAT_TYPE=PARQUET
   )
   GO
   
   --Create external table:
   CREATE EXTERNAL TABLE tbl_TaxiRides(
    vendorID VARCHAR(100) COLLATE Latin1_General_BIN2,
    tpepPickupDateTime DATETIME2,
    tpepDropoffDateTime DATETIME2,
    passengerCount INT,
    tripDistance FLOAT,
    puLocationId VARCHAR(8000),
    doLocationId VARCHAR(8000),
    startLon FLOAT,
    startLat FLOAT,
    endLon FLOAT,
    endLat FLOAT,
    rateCodeId SMALLINT,
    storeAndFwdFlag VARCHAR(8000),
    paymentType VARCHAR(8000),
    fareAmount FLOAT,
    extra FLOAT,
    mtaTax FLOAT,
    improvementSurcharge VARCHAR(8000),
    tipAmount FLOAT,
    tollsAmount FLOAT,
    totalAmount FLOAT
   )
   WITH (
    LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
    DATA_SOURCE = NYCTaxiExternalDataSource,
    FILE_FORMAT = MyFileFormat
   );
   GO
   
   --Then, query the data via an external table with T-SQL:
   SELECT TOP 10 *
   FROM tbl_TaxiRides;
   GO
   

後續步驟

在下列文章中深入了解外部資料表與相關概念：

• 使用 Azure SQL 受控執行個體的資料虛擬化
• CREATE EXTERNAL DATA SOURCE
• CREATE EXTERNAL FILE FORMAT
• CREATE EXTERNAL TABLE AS SELECT