建立和改變 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 許可權。

Syntax

.create | .create-or-alter.alter | () tableexternal TableName( 架構)storagekind= [Partitions) [partition=pathformatby((PathFormat]]=dataformat DataFormat() StorageConnectionString [ ...] ) [(withProperty [,, ...]])

注意

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 不支援此驗證方法。

數據分割格式設定

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

數據分割類型 Syntax 備註
虛擬資料行 PartitionName: (datetime | string) 深入瞭解 虛擬數據行。
字串數據行值 PartitionName:string=ColumnName
字串數據行值 哈希 PartitionName:long=hash(ColumnName,Number) 哈希為模數。
截斷日期時間數據行 (值) PartitionName:datetime= (startofyear | startofmonth | startofweek | startofday) (ColumnName) 請參閱 startofyear、 startofmonth、 startofweek 或 startofday 函式的檔。
截斷的 Datetime 資料行值 (bin) PartitionName: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(Column, 1d) yyyy/MM/dd
bin(Column, 1h) yyyy/MM/dd/HH
bin(Column, 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 external table artifacts 命令的預覽。 如同 sampleUri，該選項可協助驗證外部資料表定義的 Partitions 和 PathFormat 參數。
validateNotEmpty bool 若有設定，系統會驗證連接字串中是否有內容。 如果指定的 URI 位置不存在，或者沒有足夠的權限可存取 URI，此命令將會失敗。
dryRun bool 如果設定，則不會保存外部資料表定義。 此選項適用於驗證外部資料表定義，尤其是搭配 filesPreview 或 sampleUris 參數使用。

注意

只有在查詢和導出期間，才會在建立期間存取外部數據表。 在建立期間使用 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' 
)

依日期分割

在依日期分割的下列外部資料表中，檔案應該放在預設日期時間格式 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' 
)

依哈希和日期分割

下列外部數據表會先依客戶名稱哈希分割， (模數十) ，然後依日期分割。 預期的目錄結構為 ，例如 ， 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 Docs {"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)

相關內容