共用方式為

建立和改變 Azure 儲存體外部資料表

  • 發行項

本文中的命令可用來建立或改變執行命令之資料庫中 Azure 儲存體 外部數據表。 Azure 儲存體 外部數據表會參考位於 Azure Blob 儲存體、Azure Data Lake Store Gen1 或 Azure Data Lake Store Gen2 中的數據。

注意

如果數據表存在， .create 命令將會失敗併發生錯誤。 使用 .create-or-alter 或修改 .alter 現有的數據表。

權限

若要 .create 至少 需要資料庫用戶 許可權，而且 .alter 至少 需要數據表管理員 許可權。

若要 .create-or-alter 使用受控識別驗證的外部數據表， 需要 AllDatabasesAdmin 許可權。

語法

（.create.create-or-alter.alter | | ） externaltableTableName(架構=kindstorage) [Partitions) [pathformat(by(=partitionPathFormat]] dataformat=DataFormat() StorageConnectionString [, ...] )[with(屬性 [, ...])]

注意

kind適用於所有 storage Azure 儲存體 外部資料存放區類型。 blob 和 adl 已被取代。

深入瞭解 語法慣例。

參數

姓名 類型​​ 必要 描述
TableName string ✔️ 遵守 實體名稱規則的外部數據表名稱 。 外部數據表不能與相同資料庫中的一般數據表同名。
結構描述 string ✔️ 外部數據架構是一個或多個數據行名稱和數據類型的逗號分隔清單，其中每個專案都遵循下列格式：ColumnName:ColumnType。 如果架構未知，請使用 infer_storage_schema 根據外部檔案內容推斷架構。
資料分割 string 數據行的逗號分隔清單，外部數據表會依此清單進行分割。 數據分割數據行可以存在於數據檔本身，或作為檔案路徑的一部分。 請參閱 分割區格式 設定，以瞭解此值的外觀。
PathFormat string 要與分割區搭配使用的外部資料資料夾 URI 路徑格式。 請參閱 路徑格式。
DataFormat string ✔️ 數據格式，可以是任何 擷取格式。 除非您使用JSON路徑對應，否則建議您使用Parquet外部數據表的格式來改善查詢和導出效能。 使用外部資料表進行匯出案例時，您受限於下列格式：CSV、 TSVJSON 和 Parquet。
StorageConnectionString string ✔️ Azure Blob 儲存體 Blob 容器、Azure Data Lake Gen 2 檔系統或 Azure Data Lake Gen 1 容器的一或多個逗號分隔路徑，包括認證。 外部數據表儲存類型是由提供的 連接字串 所決定。 請參閱記憶體 連接字串。
屬性 string PropertyName=PropertyValue 格式的索引鍵/值屬性組。 請參閱 選擇性屬性。

注意

具有非相同架構的 CSV 檔案可能會導致數據出現移位或遺失。 我們建議將具有不同架構的 CSV 檔案區隔，以分隔記憶體容器，並使用適當的架構來定義每個記憶體容器的外部數據表。

提示

提供多個記憶體帳戶，以避免記憶體節流，同時 將大量數據匯出 至外部數據表。 匯出會在提供的所有帳戶之間散發寫入。

驗證與授權

存取外部數據表的驗證方法是以建立期間所提供的 連接字串 為基礎，而存取數據表所需的許可權會根據驗證方法而有所不同。

下表列出 Azure 儲存體 外部資料表支持的驗證方法，以及讀取或寫入數據表所需的許可權。

驗證方法 Azure Blob 儲存體 / Data Lake Storage Gen2 Data Lake Storage Gen1
類比 讀取許可權： 記憶體 Blob 資料讀取器
寫入許可權： 記憶體 Blob 資料參與者		 讀取許可權： 讀取者
寫入許可權： 參與者
受控識別 讀取許可權： 記憶體 Blob 資料讀取器
寫入許可權： 記憶體 Blob 資料參與者		 讀取許可權： 讀取者
寫入許可權： 參與者
共用存取權 （SAS） 令牌 讀取權限： 清單 + 讀取
寫入許可權： 寫入		 Gen1 不支援此驗證方法。
Microsoft Entra 存取令牌 不需要其他許可權。 不需要其他許可權。
儲存體帳戶存取金鑰 不需要其他許可權。 Gen1 不支援此驗證方法。

數據分割格式設定

數據分割清單是資料分割資料行的任何組合，使用下表所示的其中一個表單來指定。

數據分割類型 語法 備註
虛擬數據行 PartitionName: （datetime | string） 深入瞭解 虛擬數據行。
字串數據行值 PartitionName=:stringColumnName
字串資料行值 哈希（） PartitionName ColumnName=:longhash(,Number) 哈希為模數。
截斷日期時間資料行 （value） PartitionNamedatetime=: （startofyear | startofweekstartofdaystartofmonth | | ） (ColumnName) 請參閱 startofyear、startofmonth、startofweek 或 startofday 函式的檔。
截斷的 Datetime 資料行值 =bin(ColumnName,TimeSpan) 深入瞭解 bin 函式。

路徑格式

PathFormat 參數可讓您除了分割區之外，還要指定外部資料資料夾 URI 路徑的格式。 它包含一連串的數據分割專案和文字分隔符。 數據分割專案是指在 partition by 子句中宣告的數據分割，而文字分隔符則是以引號括住的任何文字。 連續分割區元素必須使用文字分隔符分開。

[ StringSeparator ] Partition [ StringSeparator ] [Partition [ StringSeparator ] ...]

若要建構源文件路徑前置詞，分割區元素會轉譯為字串，並以對應的文字分隔符分隔。 您可以使用 datetime_pattern 宏 （datetime_pattern(DateTimeFormat,PartitionName)） 來指定用來轉譯 datetime 數據分割值的格式。 宏遵守 .NET 格式規格，並允許以大括弧括住格式規範。 例如，下列兩種格式相等：

  • 'year='yyyy'/month='MM
  • year={yyyy}/month={MM}

根據預設，日期時間值會使用下列格式來轉譯：

分割區函數 默認格式
startofyear yyyy
startofmonth yyyy/MM
startofweek yyyy/MM/dd
startofday yyyy/MM/dd
bin(資料行, 1d) yyyy/MM/dd
bin(資料行, 1h) yyyy/MM/dd/HH
bin(資料行, 1m) yyyy/MM/dd/HH/mm

提示

若要檢查 Partitions 和 PathFormat 定義正確性，請使用 屬性 sampleUris ，或在 filesPreview 建立外部資料表時使用 。

虛擬數據行

從 Spark 匯出資料時，資料分割數據行（提供給數據框架寫入器 partitionBy 的方法）不會寫入數據檔。 此程式可避免資料重複，因為數據已經存在於資料夾名稱中（例如 column1=<value>/column2=<value>/），Spark 可以在讀取時辨識數據。

外部數據表支援以的形式 virtual colums讀取此數據。 虛擬資料列可以是 類型 string 或 datetime，並使用下列語法指定：

.create external table ExternalTable (EventName:string, Revenue:double)  
kind=storage  
partition by (CustomerName:string, Date:datetime)  
pathformat=("customer=" CustomerName "/date=" datetime_pattern("yyyyMMdd", Date))  
dataformat=parquet
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)

若要依查詢中的虛擬資料列進行篩選，請在查詢述詞中指定資料分割名稱：

external_table("ExternalTable")
 | where Date between (datetime(2020-01-01) .. datetime(2020-02-01))
 | where CustomerName in ("John.Doe", "Ivan.Ivanov")

選擇性屬性

屬性 類型​ 描述
folder string 數據表的資料夾
docString string 記錄數據表的字串
compressed bool 如果設定，表示檔案是否壓縮為 .gz 檔案（ 僅適用於匯出案例 ）
includeHeaders string 針對分隔文字格式 （CSV、TSV、...），指出檔案是否包含標頭。 可能的值為： All （所有檔案都包含標頭）、 FirstFile （資料夾中的第一個檔案包含標頭）、 None （沒有檔案包含標頭）。
namePrefix string 如果設定，表示檔案的前置詞。 在寫入作業上，所有檔案都會以這個前置詞寫入。 在讀取作業上，只會讀取具有此前置詞的檔案。
fileExtension string 如果設定，表示檔案的擴展名。 寫入時，檔名會以這個後綴結尾。 在讀取時，只會讀取具有此擴展名的檔案。
encoding string 顯示文字的編碼方式： UTF8NoBOM （預設值） 或 UTF8BOM。
sampleUris bool 如果設定，命令結果會提供數個模擬外部數據檔 URI 的範例，如同外部數據表定義所預期。 此選項有助於驗證 是否已正確定義 Partitions 和 PathFormat 參數。
filesPreview bool 如果設定，其中一個命令結果數據表包含 .show 外部數據表成品命令的預覽。 如同 sampleUri，此選項有助於驗證 外部數據表定義的 Partitions 和 PathFormat 參數。
validateNotEmpty bool 如果已設定，則會驗證 連接字串 中是否有內容。 如果指定的 URI 位置不存在，或沒有足夠的許可權存取它，命令將會失敗。
dryRun bool 如果設定，則不會保存外部資料表定義。 這個選項適用於驗證外部數據表定義，特別是與 或 sampleUris 參數搭配filesPreview使用。

注意

只有在查詢和匯出期間，才能在建立期間存取外部數據表。 在建立期間使用 validateNotEmpty 選擇性屬性，以確定數據表定義有效且可存取記憶體。

提示

若要深入瞭解查詢期間在數據文件篩選中扮演的角色 namePrefix 和 fileExtension 屬性，請參閱 檔案篩選邏輯 一節。

檔案篩選邏輯

查詢外部數據表時，藉由篩選掉無關的外部記憶體檔案來改善效能。 反覆運算檔案並決定是否應該處理檔案的程式如下：

  1. 建置 URI 模式，代表找到檔案的位置。 一開始，URI 模式等於在外部數據表定義中提供的 連接字串。 如果已定義任何分割區，則會使用 PathFormat轉譯，然後附加至URI模式。

  2. 針對在 URI 模式下建立的所有檔案，請檢查：

    • 數據分割值符合查詢中使用的述詞。
    • 如果已定義這類屬性，Blob 名稱會以 開頭 NamePrefix。
    • 如果定義這類屬性，Blob 名稱會以 FileExtension結尾。

一旦符合所有條件，就會擷取並處理檔案。

注意

初始 URI 模式是使用查詢述詞值所建置。 這最適合一組有限的字串值，以及封閉時間範圍。

範例

非分割外部數據表

在下列非分割外部數據表中，檔案應該直接放在定義的容器之下：

.create external table ExternalTable (x:long, s:string)  
kind=storage 
dataformat=csv 
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey' 
)

依日期分割

在依日期分割的下列外部數據表中，檔案應該放在預設 datetime 格式 yyyy/MM/dd的目錄之下：

.create external table ExternalTable (Timestamp:datetime, x:long, s:string) 
kind=storage
partition by (Date:datetime = bin(Timestamp, 1d)) 
dataformat=csv 
( 
   h@'abfss://filesystem@storageaccount.dfs.core.windows.net/path;secretKey'
)

依月份分割

在依月份分割的下列外部資料表中，目錄格式為 year=yyyy/month=MM：

.create external table ExternalTable (Timestamp:datetime, x:long, s:string) 
kind=storage 
partition by (Month:datetime = startofmonth(Timestamp)) 
pathformat=(datetime_pattern("'year='yyyy'/month='MM", Month)) 
dataformat=csv 
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey' 
)

依名稱和日期分割

在下列外部資料表中，數據會先依客戶名稱分割，然後依日期分割，這表示預期的目錄結構為 ，例如 customer_name=Softworks/2019/02/01：

.create external table ExternalTable (Timestamp:datetime, CustomerName:string) 
kind=storage 
partition by (CustomerNamePart:string = CustomerName, Date:datetime = startofday(Timestamp)) 
pathformat=("customer_name=" CustomerNamePart "/" Date)
dataformat=csv 
(  
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey' 
)

依哈希和日期分割

下列外部資料表會先依客戶名稱哈希 （modulo ten）， 然後依日期分割。 預期的目錄結構是 ，例如， customer_id=5/dt=20190201和資料檔名稱結尾加上 .txt 擴展名：

.create external table ExternalTable (Timestamp:datetime, CustomerName:string) 
kind=storage 
partition by (CustomerId:long = hash(CustomerName, 10), Date:datetime = startofday(Timestamp)) 
pathformat=("customer_id=" CustomerId "/dt=" datetime_pattern("yyyyMMdd", Date)) 
dataformat=csv 
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)
with (fileExtension = ".txt")

依查詢中的數據分割數據行進行篩選

若要依查詢中的數據分割數據行進行篩選，請在查詢述詞中指定原始資料行名稱：

external_table("ExternalTable")
 | where Timestamp between (datetime(2020-01-01) .. datetime(2020-02-01))
 | where CustomerName in ("John.Doe", "Ivan.Ivanov")

範例輸出

TableName TableType 資料夾 DocString 屬性 ConnectionStrings 資料分割 PathFormat
ExternalTable Blob ExternalTables 文件 {“Format”：“Csv”，“Compressed”：false，“CompressionType”：null，“FileExtension”：null，“IncludeHeaders”：“None”，“Encoding”：null，“NamePrefix”：null} [“https://storageaccount.blob.core.windows.net/container1;*******”] [{“Mod”：10，“Name”：“CustomerId”，“ColumnName”：“CustomerName”，“Ordinal”：0}，{“Function”：“StartOfDay”，“Name”：“Date”，“ColumnName”：“Timestamp”，“Ordinal”：1}] “customer_id=” CustomerId “/dt=” datetime_pattern（“yyyyMMdd”，Date）

相關內容