CREATE EXTERNAL TABLE (Transact-SQL)

建立外部資料表。

本文提供適用於您所選擇之 SQL 產品的語法、引數、備註、權限和範例。

如需語法慣例的詳細資訊，請參閱 Transact-SQL 語法慣例。

選取產品

在以下資料列中，選取您感興趣的產品名稱，隨即只會顯示該產品的資訊。

* SQL Server *  

SQL Database

Azure SQL 受控執行個體

Azure Synapse
分析

Analytics Platform
System (PDW)

 

概觀：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 個資料列之後，持續重新計算失敗的資料列百分比。

注意

由於 PolyBase 會不時計算失敗的資料列百分比，因此實際的失敗資料列百分比可能超出 reject_value。

範例：

此範例說明三個 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) 時，請設定 [允許的權限] 以至少授與讀取和寫入權限。 在跨資料夾搜尋時，可能也需要列出權限。 您也必須選取 [容器] 和 [物件] 作為允許的資源類型。

重要

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

錯誤處理

執行 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 的資料虛擬化延伸模組