创建和更改 Azure 存储外部表

本文中的命令可用于在执行命令的数据库中创建或更改 Azure 存储外部表。 Azure 存储外部表引用位于 Azure Blob 存储、Azure Data Lake Store Gen1 或 Azure Data Lake Store Gen2 中的数据。

权限

.create 至少需要数据库用户权限，.alter 至少需要表管理员权限。

.create-or-alter 使用托管标识身份验证的外部表需要 AllDatabasesAdmin 权限。

语法

(.create | .alter | .create-or-alter) externaltableTableName(Schema)kind=storage [partitionby(Partitions) [pathformat=(PathFormat)]] dataformat=DataFormat(StorageConnectionString [, ...] ) [with(Property [, ...])]

详细了解语法约定。

参数

客户	类型​​	必需	说明
TableName	string	✔	遵循实体名称规则的外部表名称。 外部表不能与同一数据库中的常规表具有相同的名称。
架构	string	✔	外部数据架构是包含一个或多个列名称和数据类型的逗号分隔列表，其中的每个项遵循以下格式：ColumnName:ColumnType。 如果架构未知，请使用 infer_storage_schema 根据外部文件内容推理架构。
分区	string		对外部表进行分区时所依据的列的逗号分隔列表。 分区列可以位于数据文件本身中，或者作为文件路径的一部分存在。 请参阅分区格式了解此值的外观。
PathFormat	string		用于分区的外部数据文件夹 URI 路径格式。 请参阅路径格式。
DataFormat	string	✔	数据格式，可以是任何引入格式。 建议对外部表使用 Parquet 格式以提高查询和导出性能，除非你使用 JSON 路径映射。 将外部表用于导出方案时，只能使用以下格式：CSV、TSV、JSON 和 Parquet。
StorageConnectionString	string	✔	Azure Blob 存储 Blob 容器、Azure Data Lake Gen 2 文件系统或 Azure Data Lake Gen 1 容器（包括凭据）的一个或多个逗号分隔路径。 外部表存储类型取决于提供的连接字符串。 请参阅存储连接字符串。
属性	string		采用 PropertyName=PropertyValue 格式的键值属性对。 请参阅可选属性。

身份验证和授权

访问外部表的身份验证方法基于在创建外部表期间提供的连接字符串，访问该表所需的权限因身份验证方法而异。

下表列出了 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:long=hash(ColumnName,Number)	哈希是模数。
已截断的日期/时间列（值）	PartitionName:datetime= (startofyear | startofmonth | startofweek | startofday) (ColumnName)	请参阅有关 startofyear、startofmonth、startofweek 或 startofday 函数的文档。
已截断的日期/时间列 (bin)	PartitionName:datetime=bin(ColumnName,TimeSpan)	详细了解 bin 函数。

路径格式

使用 PathFormat 参数可以指定除分区之外的外部数据文件夹 URI 路径的格式。 它由一系列分区元素和文本分隔符组成。 分区元素引用分区 by 子句中声明的分区，文本分隔符是用引号括住的任何文本。 必须使用文本分隔符分隔连续的分区元素。

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

若要构造原始文件路径前缀，请使用呈现为字符串并以相应文本分隔符分隔的分区元素。 可以使用 datetime_pattern 宏 (datetime_pattern(DateTimeFormat,PartitionName)) 指定用于呈现日期/时间分区值的格式。 该宏遵循 .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

虚拟列

从 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.chinacloudapi.cn/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 文件（仅用在.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 位置不存在，或者没有足够的访问它的权限，则该命令会失败。
dryRun	bool	如果设置了此属性，则不会保留外部表定义。 此选项对于验证外部表定义很有用，特别是在与 filesPreview 或 sampleUris 参数一起使用的情况下。

文件筛选逻辑

查询外部表时，通过筛选掉无关的外部存储文件来提高性能。 循环访问文件并确定是否应处理文件的过程如下：

1. 生成一个 URI 模式，用于表示找到文件的位置。 最初，URI 模式等于作为外部表定义的一部分提供的连接字符串。 如果定义了任何分区，则它们将使用 PathFormat 呈现，然后追加到 URI 模式。

2. 对于在创建的 URI 模式下找到的所有文件，请检查：

   • 分区值是否与查询中使用的谓词匹配。
   • 如果定义了这样的属性，则 Blob 名称以 NamePrefix 开头。
   • 如果定义了这样的属性，则 Blob 名称以 FileExtension 结尾。

满足所有条件时，会提取并处理该文件。

示例

未分区的外部表

在以下未分区的外部表中，文件应直接放在定义的容器下：

.create external table ExternalTable (x:long, s:string)  
kind=storage 
dataformat=csv 
( 
   h@'https://storageaccount.blob.core.chinacloudapi.cn/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.chinacloudapi.cn/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.chinacloudapi.cn/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.chinacloudapi.cn/container1;secretKey' 
)

按哈希和日期分区

以下外部表首先按客户名称哈希（模数为 10）分区，然后按日期分区。 预期的目录结构类似于 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.chinacloudapi.cn/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.chinacloudapi.cn/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)

相关内容

• 查询外部表。
• 将数据导出到外部表。
• 连续数据导出到外部表。