建立和改變 Azure 儲存體外部資料表
適用於： ✅Microsoft網狀架構✅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 | ） external table TableName (架構kind= storage) [ 數據分割) [pathformat(by(=partition PathFormat]] 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 = : string ColumnName |
|
| 字串資料行值 哈希（） | PartitionName ColumnName=: long hash(, Number) |
哈希為模數。 |
| 截斷日期時間資料行 （value） | PartitionName datetime : = （startofyearstartofweek | | | startofdaystartofmonth） ( 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 屬性，請參閱 檔案篩選邏輯 一節。
檔案篩選邏輯
查詢外部數據表時，藉由篩選掉無關的外部記憶體檔案來改善效能。 反覆運算檔案並決定是否應該處理檔案的程式如下：
建置 URI 模式，代表找到檔案的位置。 一開始，URI 模式等於在外部數據表定義中提供的 連接字串。 如果已定義任何分割區，則會使用 PathFormat轉譯，然後附加至URI模式。
針對在 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 | Folder | 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） |