CREATE EXTERNAL TABLE （Transact-SQL）

適用於：SQL Server 2016 （13.x）及以後版本 Azure SQL DatabaseAzure SQL 受管執行個體Azure Synapse 分析Analytics Platform System （PDW）Microsoft Fabric 中的倉儲Microsoft Fabric 中的 SQL 資料庫

建立外部資料表。

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

選取產品

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

* SQL 伺服器 *  

SQL 資料庫

Azure SQL 受控執行個體

網狀架構數據倉儲

Fabric SQL 資料庫

Azure Synapse
分析

分析平臺
系統 （PDW）

 

概觀：SQL Server

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

適用於：SQL Server 2016 （13.x） 和更新版本。

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

• 使用數據虛擬化搭配 SQL Server 中的 PolyBase 進行數據虛擬化和數據載入
• 使用 SQL Server 或 SQL Database 來執行大量載入作業 (使用 BULK INSERT 或 OPENROWSET)

外部數據表是以 外部數據源為基礎。

Transact-SQL 語法慣例

語法

-- 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 外部資料表上使用 。

資料行定義 (包括資料類型及資料行數目) 必須符合外部檔案中的資料。 如果發生不相符的情況，查詢實際數據時會拒絕檔案數據列。

位置 = 'folder_or_filepath'

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

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。

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

REJECT 選項

此選項僅適用於外部資料來源，且 。TYPE = HADOOP

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

當您不指定或變更拒絕值時，PolyBase 就會使用預設值。 當你建立帶有 CREATE EXTERNAL TABLE 陳述的外部資料表時，這些拒絕參數的資訊會作為額外的中繼資料儲存。 當未來 SELECT 的語句 SELECT INTO SELECT 或語句從外部資料表中選擇資料時，PolyBase 會利用拒絕選項來決定在實際查詢失敗前可被拒絕的列數或百分比。 查詢會（部分回傳）結果，直到被超過拒絕閾值為止。 接著它便會失敗並顯示適當的錯誤訊息。

REJECT_TYPE = { 價值 | 百分比 }

釐清選項是否 REJECT_VALUE 指定為常值或百分比。

• 價值

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

  例如，若 REJECT_VALUE = 5 且 REJECT_TYPE = value， SELECT 則在拒絕五列後查詢即告失敗。

• 百分比

  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 會計算在嘗試從外部資料檔匯入 1,000 列後，失敗列的百分比。 如果失敗的數據列百分比小於 reject_value，PolyBase 會嘗試擷取另一個 1,000 個數據列。 它會在嘗試匯入每個額外的 1,000 個數據列之後，繼續重新計算失敗數據列的百分比。

注意

由於 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 = 目錄位置

適用於：SQL Server 2019（15.x）CU 6 及以上版本，以及 Azure Synapse Analytics。

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

如果指定的路徑不存在，PolyBase 會代表您建立一個路徑。 系統會建立名稱為 _rejectedrows 的子目錄。 _ 字元可確保該目錄從其他資料處理逸出，除非已明確在位置參數中指名。 在此目錄中，有一個資料夾是根據載入提交 YearMonthDay -HourMinuteSecond 格式建立的資料夾（例如，20230330-173205）。 在此資料中寫入了兩種類型的檔案，分別是 _reason 檔案與資料檔案。 此選項只能與外部數據源搭配使用，其中 TYPE = HADOOP 和使用 DELIMITEDTEXTFORMAT_TYPE的外部數據表。 欲了解更多資訊，請參閱 建立外部資料來源 及 建立外部檔案格式。

原因檔案和數據檔都有與 CTAS 語句相關聯的 queryID。 因為資料與原因檔案在不同的檔案中，所以對應的檔案會具有相符尾碼。

權限

需要下列使用者權限：

• CREATE TABLE
• ALTER ANY SCHEMA
• ALTER ANY EXTERNAL DATA SOURCE
• ALTER ANY EXTERNAL FILE FORMAT （僅適用於 Hadoop 和 Azure Storage 的外部資料來源）
• CONTROL DATABASE （僅適用於 Hadoop 和 Azure Storage 的外部資料來源）

注意，指令中使用的DATABASE SCOPED CREDENTIALCREATE EXTERNAL TABLE遠端登入必須擁有參數中指定的LOCATION外部資料來源路徑/資料表/集合的讀取權限。 如果你打算用它 EXTERNAL TABLE 把資料匯出到 Hadoop 或 Azure Storage 的外部資料來源，那麼指定的登入必須有寫入權限，路徑在 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 擷取外部資料時建立。

Hadoop 格式僅支援於 SQL Server 2016（13.x）、SQL Server 2017（14.x）及 SQL Server 2019（15.x）。

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（13.x）中，PolyBase 的列寬度限制為 32 KB，這是根據資料表定義單一有效資料列的最大大小。 若資料行結構描述的總和超過 32 KB，PolyBase 將無法查詢資料。

資料類型限制

以下資料型別無法用於 PolyBase 外部資料表：

• 地理位置
• 幾何
• 層次識別碼（hierarchyid）
• 映像
• 文字
• 內容
• xml
• 任何使用者定義的類型

資料來源特定限制

Oracle公司

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

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

使用 sp_data_source_objects 偵測包含陣列的 MongoDB 集合的集合結構（欄位），並手動建立外部資料表。 sp_data_source_table_columns 預存程序也會透過 PolyBase ODBC Driver for MongoDB 驅動程式自動執行壓平合併。

鎖定

共用鎖定物件 SCHEMARESOLUTION 。

安全性

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

範例

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

此範例會示範建立將資料儲存為文字分隔檔案之外部資料表的所有步驟。 它會定義外部資料來源 mydatasource 和外部檔案格式 myfileformat。 這些資料庫層級物件接著會在 CREATE EXTERNAL TABLE 語句中參考。 欲了解更多資訊，請參閱 建立外部資料來源 及 建立外部檔案格式。

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

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

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

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

此範例會示範建立將資料格式化為 RCFile 之外部資料表的所有步驟。 它會定義外部資料來源 mydatasource_rc 和外部檔案格式 myfileformat_rc。 這些資料庫層級物件接著會在 CREATE EXTERNAL TABLE 語句中參考。 欲了解更多資訊，請參閱 建立外部資料來源 及 建立外部檔案格式。

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

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

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

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

此範例會示範建立將資料格式化為 ORC 檔案之外部資料表的所有步驟。 其會定義外部資料來源 mydatasource_orc，以及外部檔案格式 myfileformat_orc。 這些資料庫層級物件接著會在 CREATE EXTERNAL TABLE 語句中參考。 欲了解更多資訊，請參閱 建立外部資料來源 及 建立外部檔案格式。

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

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

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 AS cs
     INNER JOIN UrlDescription AS 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 a Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
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>';

建立名為 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;

/* 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
);

一. 建立 Oracle 的外部資料表

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

/*
* 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>';
GO

/*
* 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. 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 (
    DATA_SOURCE = external_data_source_name,
    LOCATION = 'DB1.mySchema.customer'
);

J. 建立 Teradata 的外部資料表

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

/*
* 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>';
GO

/* 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
);
GO

/* 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>';
GO

/*
* 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>';
GO

/* 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
);

相關內容

• CREATE EXTERNAL DATA SOURCE （Transact-SQL）
• CREATE EXTERNAL FILE FORMAT （Transact-SQL）
• sp_data_source_objects （Transact-SQL）