创建和更改 Azure 存储外部表

适用于：✅Azure 数据资源管理器

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

权限

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

语法

（.create | .alter | .create-or-alter） externaltableTableName(架构)kind=storage [partitionby [()pathformat=()dataformat]] = DataFormat (StorageConnectionString [, ...] )[with(属性 [, ...])]

详细了解语法约定。

参数

身份验证和授权

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

下表列出了 Azure 存储外部表支持的身份验证方法，以及对表进行读取或写入操作所需的权限。

分区格式设置

分区列表是分区列的任意组合，使用下表中显示的格式之一指定。

路径格式

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

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

若要构造原始文件路径前缀，请使用呈现为字符串并以相应文本分隔符分隔的分区元素。 可以使用 datetime_pattern 宏 (datetime_pattern(DateTimeFormat PartitionName,) 指定用于呈现日期/时间分区值的格式)。 该宏遵循 .NET 格式规范，允许将格式说明符括在大括号中。 例如，以下两种格式是等效的：

• 'year='yyyy'/month='MM
• year={yyyy}/month={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")

可选属性

文件筛选逻辑

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

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")

示例输出

相关内容