使用Azure Databricks Excel外接程序导入和查询数据

注释

Azure Databricks Excel外接程序在Azure 政府或Azure中国区域不可用。

Azure Databricks Excel插件将您的Azure Databricks工作区连接到Microsoft Excel，将受管控的Lakehouse数据直接带入您的电子表格。

本页介绍如何使用 Azure Databricks Excel 外接程序从 Excel 中的 Azure Databricks 导入和分析数据。 可以通过直观的界面浏览和导入 Azure Databricks 表，其中不需要 SQL 知识。 虽然加载项提供了执行自定义 SQL 查询的灵活性，但它是可选的。

先决条件

• 在使用 Excel 外接程序之前，请验证是否已 设置它。
• 您拥有Azure Databricks SQL 访问权限，并且至少对SQL 仓库具有 CAN USE 权限。

选择 SQL 仓库

选择要使用的 SQL 仓库：

1. 在 Excel 中 Azure Databricks 外接程序窗格的右上角，单击下拉菜单。
2. 选择要使用的 SQL 仓库。

从Azure Databricks导入数据

通过选择表、编写 SQL 查询或导入数据透视表，从 Excel 中的 Azure Databricks 导入数据。

注释

可以使用数据透视表、SQL 查询和自定义函数导入 Unity 目录指标视图。

创建数据透视表

若要从 Excel 中的 Unity 目录表和视图创建透视表，请执行以下操作：

1. 在 Azure Databricks Excel 加载项窗格的 新建导入 选项卡下，选择 选择数据 作为 导入方法。

2. 在 “目录”下，选择要从中创建数据透视表的表，然后单击“ 选择”。

3. 选中 “数据透视表” 复选框。

4. （可选）选择 “实时查询 ”以运行查询，并在构建透视表时自动更新新工作表。 更多信息请参见 “实时查询”。

5. 通过将每个字段拖动到正确的区域来配置 行、 列和 值 。

6. （可选）添加 筛选器。 有关筛选器的详细信息，请参阅 筛选器导入的数据。

7. （可选）为导入设置行限制。

8. 导入结果。 选择下列选项之一：

   • 单击“ 保存并导入 ”以保存查询以在 Excel 工作簿中重复使用并导入结果。
   • 单击向下箭头，然后单击“ 导入结果 ”以导入结果而不保存查询。 如果要继续编辑导入，请使用此选项。

   注释

   数据透视表只能导入到新工作表。

在数据透视表中处理 Unity Catalog 指标时，结果中可能会显示 Sum(measure)。 这是预期行为，不会发生其他聚合。 Excel 要求值具有聚合函数，但由于数据包含唯一值，因此不会发生聚合。

实时查询

实时查询会运行你的枢纽查询，并在编辑枢轴时自动更新工作表，这样你在更新字段时就能看到结果，而不是点击 导入后才看到。 实时查询会在你至少更改一个行或列和一个值后重新运行查询。 要恢复标准流程，请关闭该功能。

启用 Live 查询时，透视表总是会在新工作表中创建并更新。 此外，透视生成器会显示“保存导入”按钮，而不是“导入”按钮。

如果 Excel 在你编辑实时数据透视表时关闭了，导入选项卡会显示一个恢复选项，以便你还原该导入项最后保存的配置。

选择表

数据作为 Excel 表对象导入。 可以移动表格或重命名工作表，Excel 插件会刷新新位置的数据。

若要从 Azure Databricks 表导入数据，请执行以下操作：

1. 在 Azure Databricks Excel 加载项窗格的 新建导入 选项卡下，选择 选择数据 作为 导入方法。
2. 选择要从目录资源管理器导入的表。 可以使用 按所有者、认证状态和其他属性筛选目录。
3. 单击“选择”。
4. 在 “列”下，单击向下箭头并取消选择不想导入的列，或保留选择所有列以导入整个表。
5. （可选）添加 筛选器。 有关筛选器的详细信息，请参阅 筛选器导入的数据。
6. （可选）若要查看导入示例，请单击“ 预览”。
7. （可选）设置行限制以限制导入的行数。
8. （可选）若要标识导入的数据，请输入 导入名称。
9. 在 “输出目标”下，选择将数据导入新工作表或当前工作表。 如果导入到当前工作表，数据从您输入的单元格引用开始（默认为 A1）。
10. 导入结果。 选择以下选项之一：
    • 单击“ 保存并导入 ”以保存查询以在 Excel 工作簿中重复使用并导入结果。
    • 单击向下箭头，然后单击“ 导入结果 ”以导入结果而不保存查询。 如果要继续编辑导入，请使用此选项。

编写 SQL 查询

Write SQL 导入方法支持 SQL 函数和存储过程。

若要针对 Azure Databricks 工作区运行自定义 SQL 查询，请执行以下操作：

1. 在“Azure Databricks Excel 外接程序”窗格中的“新建导入”选项卡下，选择Write SQL作为导入方法。

2. 输入查询的名称，以便稍后对其进行标识。

3. 编写新查询或使用 Azure Databricks 工作区中的现有查询。

   • 在编辑器中编写 SQL 查询。 可以查询有权访问的 Unity 目录中的任何表。

     • 单击 目录资源管理器 以查看您的架构和表。

   • 若要使用 Azure Databricks 工作区中的查询或 Excel 中的现有查询，请单击 如果使用 Azure Databricks 工作区中的现有查询，Excel 中的编辑不会反映在 Azure Databricks 上。

     注释

     查询必须使用查询编辑器右上角的 Save 按钮显式保存在Azure Databricks中，然后才能显示在Excel中。

4. （可选）若要添加查询参数，请单击“参数”旁边的“+添加”。 单击该参数并输入参数名称和参数值。

   • 对于参数值，可以输入特定值，也可以单击框和箭头按钮来指定单元格引用。 选择单元格或单元格区域，然后单击箭头自动填充参数值。

5. 在 “输出目标”下，选择将数据导入新工作表或当前工作表。 如果导入到当前工作表，数据从您输入的单元格引用开始（默认为 A1）。

6. 若要预览查询结果，请单击“ 运行”。

7. 导入结果。 选择下列选项之一：

   • 单击“ 保存并导入 ”以保存查询以在 Excel 工作簿中重复使用并导入结果。
   • 单击向下箭头，然后单击“ 导入结果 ”以导入结果而不保存查询。 如果要继续编辑导入，请使用此选项。

还可以使用自定义函数添加查询参数。 请参阅 Write SQL。

筛选导入的数据

通过选择表或创建数据透视表导入数据时，可以应用筛选器来缩小结果范围。

字符串筛选器不区分大小写且可级联。 当你应用多个滤波器时，每个滤波器的可用值取决于前一个滤波器的选择。 例如，如果你按国家筛选，然后添加城市筛选，城市筛选器只显示所选国家内的城市。

若要设置筛选器，请单击+”旁边的列，选择要向其应用筛选器的列，然后输入筛选条件。 对于需要值的筛选器，可以执行以下操作之一：

• 输入值。
• 要生成最多5,000个不同滤波器值的列表，可以使用：
  1. 单击 “值”，然后 获取筛选器值。
  2. 单击向下箭头，然后从列表中选择一个或多个值。
• 若要使用单元格引用，请执行以下操作：
  1. 单击“ 单元格”。
  2. 选择单元格或单元格区域。
  3. 单击光标

下表描述了每个可用的筛选器及其预期输入。

筛选器	预期输入	Description
IS NULL	没有	查找列值为 null 的行。
IS NOT NULL	没有	查找列值不为空的行。
EQUALS	一个数字或文本字符串	查找列值与指定值完全匹配的行。
NOT EQUALS	一个数字或文本字符串	查找列值与指定值不匹配的行。
STARTS WITH	一个文本字符串	查找列值以指定文本开头的行。
ENDS WITH	一个文本字符串	查找以指定文本结尾的列值所在的行。
CONTAINS	一个文本字符串	查找列值在字符串中任意位置包含指定文本的行。

计算字段

计算字段是从现有数据中推导出的列，例如 profit 从 revenue 和 cost计算得出的。 Excel插件不支持使用选择数据导入方法创建计算字段。 要添加计算字段，请使用以下方法之一：

• 写SQL：使用 写SQL 导入方法计算任意SQL表达式的列。 参见 编写SQL查询。
• Genie One：让Genie One返回你所需的计算列数据，然后导入结果。 参见 Microsoft Excel 中的 Use Genie One。

Databricks 建议使用 Genie One 来计算字段。

在Excel中使用Azure Databricks自定义函数

Excel 外接程序提供可在 Excel 公式中使用的自定义函数，用于从 Azure Databricks 导入数据。

选择一个表

该 DATABRICKS.Table 函数从 Unity 目录表导入数据。

语法：

=DATABRICKS.Table(catalog_name.schema_name.table_name, [column1, ...], [limit])

参数：

• catalog_name.schema_name.table_name （必需）：完整限定的表名称。
• columns （可选）：要导入的列名数组。 省略此参数以导入所有列。
• limit （可选）：要导入的最大行数。 省略此参数以导入所有行，最大限制为 10 MB。

Example:

=DATABRICKS.Table("main.default.customers", {"customer_id", "customer_name"}, 100)

此公式从customer_id表中导入customer_namemain.default.customers列，限制为 100 行。

编写 SQL

该 DATABRICKS.SQL 函数运行一个 SQL 查询，该查询使用查询参数并返回结果。

语法：

使用值指定参数。

=DATABRICKS.SQL("query_text", {parameter1_name, parameter1_value; ...})

使用单元格范围指定参数。 在同一行的单元格中定义名称和值参数。

=DATABRICKS.SQL("query_text", {param_name_cell: param_value_cell; ...})

参数：

• query_text （必需）：要运行的 SQL 查询。
• parameters（必填）：用于替换查询中的参数值的映射。

Example:

=DATABRICKS.SQL("SELECT * FROM samples.bakehouse.sales_suppliers WHERE longitude > :long_param AND latitude > :lat_param LIMIT 10", {"long_param",20; "lat_param",10})

=DATABRICKS.SQL("SELECT * FROM samples.bakehouse.sales_suppliers WHERE city = :city", M4:N4)

此公式运行一个查询，该查询使用提供的参数值通过longitude和latitude筛选销售数据。

管理查询

从“导入”页管理现有导入。

编辑现有导入

编辑现有导入：

1. 在 Excel 的 Azure Databricks 外接程序窗格中，单击“ 导入 ”选项卡。
2. 找到要编辑的导入。
3. 单击导入旁边的三点菜单。
4. 单击 “编辑” 以编辑导入。

刷新数据

Excel外接程序不会自动刷新数据。 刷新数据的方式取决于导入数据的方式。 使用导入方式（选择表、编写 SQL 查询或创建数据透视表）导入的数据可通过 导入 选项卡刷新。使用自定义函数导入的数据必须重新计算。

将导入项更新为来自 Azure Databricks 的最新值。 加载项再次运行原始查询或表选择，并使用新数据更新工作表：

• 要刷新单个导入项：
  1. 在 Excel 的 Azure Databricks 外接程序窗格中，单击“ 导入 ”选项卡。
  2. 单击要刷新导入项旁边的 刷新。
• 刷新所有导入项：
  1. 单击 Azure Databricks 插件窗格中的 全部刷新。

Important

刷新数据时，Excel 外接程序会清除指定表中的所有现有数据，并从 Azure Databricks 重新加载最新数据。 在刷新过程中，将删除添加到表的任何自定义列。

从自定义函数导入的数据，例如DATABRICKS.TableDATABRICKS.SQL，在重新打开工作簿时不会刷新。 若要刷新从自定义函数导入的数据，请登录到Azure Databricks外接程序，然后重新计算工作簿或更改自定义函数引用的值。

共享影响

共享包含 Azure Databricks 数据的 Excel 工作簿时，请考虑以下数据访问和安全影响：

对导入的数据的可见性

当收件人刷新导入时，外接程序将使用收件人的 Unity 目录权限。 如果它们无权访问基础数据，刷新将失败。

对于涉及数据隐私的工作簿，您可以使用以下变通方法：

1. 使用所有必要的公式和导入创建工作簿。
2. 从工作表中删除导入的数据。
3. 与收件人共享工作簿。
4. 让收件人刷新数据。

收件人仅看到他们有权访问的数据，具体取决于其 Unity 目录权限。

对工作区和数据资产的访问权限

• 无法访问工作簿中引用的 Unity 目录对象的用户无法刷新数据。 若要刷新数据，用户必须对 Unity 目录中的基础表和视图具有读取权限。
• 用户必须有权访问 Azure Databricks 中的基础表，才能编辑现有导入。

查询可见性

对工作簿具有编辑访问权限的用户可以查看用于通过 Azure Databricks 外接程序生成数据的查询，即使他们无权访问 Unity 目录中的基础数据。

另存为模板的替代方法

Azure Databricks Excel外接程序不支持将工作簿另存为模板，但你可以共享工作簿，以便其他用户可以看到导入的查询。 请参阅 共享对 数据访问和安全注意事项的影响。

作为将工作簿共享为模板的解决方法，请执行以下操作之一：

• 与其他用户共享本地文件。 收件人可以重命名文件并查看保存的查询。
• 在SharePoint上，与其他用户共享工作簿。 当其他用户下载该文件时，将保留保存的导入。

Limitations

• 自定义函数：对于自定义函数，由于 SQL 执行 API 的限制，查询结果限制为 25 MiB。
• 数据加载：如果工作簿中的任何单元格处于编辑模式，数据加载可能会失败。
• Excel Desktop 行限制：Excel Desktop 每个工作表最多支持 1,048,576 行。
• Excel 网页版文件大小限制：Excel for web 支持最大工作簿文件大小约 25 MB，用于查看和编辑。
• 实时查询性能：开启 实时查询 时，每次 pivot 编辑都会对你的 SQL 仓库执行一个新查询。 结果不会缓存本地，因此频繁编辑大型枢轴会增加延迟和仓库成本。