在 PostgreSQL 上运行联合查询

本页介绍如何设置 Lakehouse 联邦系统，以对未由 Azure Databricks 管理的 PostgreSQL 数据运行联合查询。 要了解有关 Lakehouse Federation 的更多信息，请参阅连接到外部数据库和目录

若要使用 Lakehouse 联盟连接到 PostgreSQL 数据库并运行查询，必须在 Azure Databricks Unity Catalog 元存储中创建以下内容（2023 年 11 月 9 日之后创建的工作区已自动预配 Unity Catalog 元存储）：

  • 与 PostgreSQL 数据库上的运行查询的连接。
  • 镜像 Unity Catalog 中 PostgreSQL 数据库上运行查询的外部目录，以便你可使用 Unity Catalog 查询语法和数据治理工具来管理 Azure Databricks 用户对数据库的访问。

开始之前

工作区要求：

  • 已为 Unity Catalog 启用工作区。 2023 年 11 月 9 日之后创建的工作区会自动启用 Unity Catalog，包括自动配置元数据存储。 除非你的工作区创建于自动启用之前且尚未启用 Unity Catalog，否则无需手动创建元数据存储。 请参阅 Unity 目录入门。

计算要求：

  • 从计算资源到目标数据库系统的网络连接。 请参阅 Lakehouse Federation 的网络建议。
  • Azure Databricks 计算必须使用 Databricks Runtime 13.3 LTS 或更高版本以及 标准 或 专用 访问模式。
  • SQL 仓库必须是专业或无服务器，并且必须使用 2023.40 或更高版本。

所需的权限：

  • 若要创建连接，必须是元存储管理员或对附加到工作区的 Unity Catalog 元存储具有 CREATE CONNECTION 特权的用户。 在自动为 Unity 目录启用的工作区中，工作区管理员默认具有 CREATE CONNECTION 权限。
  • 若要创建外部目录，必须对元存储具有 CREATE CATALOG 权限，并且是连接的所有者或对连接具有 CREATE FOREIGN CATALOG 特权。 在自动为 Unity 目录启用的工作区中，工作区管理员默认具有 CREATE CATALOG 权限。

后面每个基于任务的部分都指定了其他权限要求。

创建连接

连接指定用于访问外部数据库系统的路径和凭据。 若要创建连接，可以使用目录资源管理器，或者使用 Azure Databricks 笔记本或 Databricks SQL 查询编辑器中的 CREATE CONNECTION SQL 命令。

Note

你还可以使用 Databricks REST API 或 Databricks CLI 来创建连接。 请参阅 POST /api/2.1/unity-catalog/connections 和 Unity Catalog 命令。

所需的权限：具有 CREATE CONNECTION 特权的元存储管理员或用户。

目录资源管理器

  1. 在 Azure Databricks 工作区中，单击 “数据”图标。目录。
  2. 在“目录”窗格顶部，单击Add or plus icon“添加”或“加号”图标，然后从菜单中选择“创建连接”。
  3. 在“设置连接”向导的“连接基本信息”页上，输入一个用户友好的“连接名称”。
  4. 选择 PostgreSQL 的“连接类型”。
  5. （可选）添加注释。
  6. 单击 “下一步” 。
  7. 在 身份验证 页上，输入 PostgreSQL 实例的以下连接属性。
    • 主机：例如 postgres-demo.lb123.us-west-2.rds.amazonaws.com
    • 端口：例如 5432
    • 用户：例如 postgres_user
    • 密码：例如 password123
    • userProvidedServerCertificate：可选。 你PostgreSQL实例的PEM编码公开证书。 连接始终采用SSL加密，该证书在TLS握手时验证服务器身份。 当你的服务器提供来自私有或内部证书授权机构的证书，且不在默认信任存储中时，提供该证书。 它是设置 trustServerCertificate 为 true的替代方案，后者跳过了该验证。 如果你们都提供证书并将 设置为 trustServerCertificatetrue，则该证书优先。 主机名验证作为TLS握手sslmode=verify-full（）的一部分进行：如果证书上的主机名与请求的主机名不匹配，连接将失败。 将 sslmode 设置为 verify-ca，以在不进行主机名验证的情况下验证证书链。
  8. 单击 创建连接。
  9. 在“目录基本信息”页上，输入外部目录的名称。 外部目录镜像外部数据系统中的数据库，以便可以使用 Azure Databricks 和 Unity Catalog 查询和管理对该数据库中数据的访问。
  10. （可选）单击“测试连接”以确认它是否正常工作。
  11. 单击 创建目录。
  12. 在“访问权限”页上，选择用户可以在其中访问你所创建的目录的工作区。 可以选择“所有工作区均有访问权限”，也可以单击“分配给工作区”，选择工作区，然后单击“分配”。
  13. 更改 所有者，以便其能够管理目录中所有对象的访问权限。 开始在文本框中键入主体，然后单击返回的结果中的主体。
  14. 授予对目录的“特权”。 单击“授予”
    1. 指定 主体 谁有权访问目录中的对象。 开始在文本框中键入主体，然后单击返回的结果中的主体。
    2. 选择要授予每个主体的“特权预设”。 默认情况下，向所有帐户用户授予 BROWSE。
      • 从下拉菜单中选择 数据读取器，以授予对目录中对象的 read 特权。
      • 从下拉菜单中选择 数据编辑器，以授予对目录中对象的 read 和 modify 权限。
      • 手动选择要授予的权限。
    3. 单击授权。
  15. 单击 “下一步” 。
  16. 在“元数据”页上，指定标记键值对。 有关详细信息，请参阅 将标记应用于 Unity 目录安全对象。
  17. （可选）添加注释。
  18. 单击“ 保存”。

SQL

在笔记本或 Databricks SQL 查询编辑器中运行以下命令。

CREATE CONNECTION <connection-name> TYPE postgresql
OPTIONS (
  host '<hostname>',
  port '<port>',
  user '<user>',
  password '<password>'
);

建议对凭据等敏感值使用 Azure Databricks 机密而不是纯文本字符串。 例如：

CREATE CONNECTION <connection-name> TYPE postgresql
OPTIONS (
  host '<hostname>',
  port '<port>',
  user secret ('<secret-scope>','<secret-key-user>'),
  password secret ('<secret-scope>','<secret-key-password>')
)

在TLS握手期间验证服务器身份（例如，当你的PostgreSQL服务器呈现来自私有或内部证书授权机构的证书，但该证书不在默认信任存储中时），在选项 userProvidedServerCertificate 中传递服务器的PEM编码证书。 连接始终使用 SSL 进行加密。 此选项可替代 trustServerCertificate，且优先级高于该选项。

CREATE CONNECTION <connection-name> TYPE postgresql
OPTIONS (
  host '<hostname>',
  port '<port>',
  user secret ('<secret-scope>','<secret-key-user>'),
  password secret ('<secret-scope>','<secret-key-password>'),
  userProvidedServerCertificate '<pem-encoded-certificate>'
)

有关设置机密的详细信息，请参阅机密管理。

创建外部目录

Note

如果使用 UI 创建与数据源的连接，则包含外部目录创建，你可以跳过此步骤。

外部目录镜像外部数据系统中的数据库，以便可以使用 Azure Databricks 和 Unity Catalog 查询和管理对该数据库中数据的访问。 若要创建外部目录，请使用与已定义的数据源的连接。

要创建外部目录，可以使用目录资源管理器，或在 Azure Databricks 笔记本或 SQL 查询编辑器中使用 CREATE FOREIGN CATALOG SQL 命令。 你还可以使用 Databricks REST API 或 Databricks CLI 来创建目录。 请参阅 POST /api/2.1/unity-catalog/catalogs 和 Unity Catalog 命令。

所需的权限：对元存储的 CREATE CATALOG 权限以及连接的所有权或对连接的 CREATE FOREIGN CATALOG 特权。

目录资源管理器

  1. 在 Azure Databricks 工作区中，单击 “数据”图标以打开目录资源管理器。

  2. 在“目录”窗格顶部，单击 “添加”图标，然后从菜单中选择“添加目录”。Add or plus icon

    也可在“快速访问”页中单击“目录”按钮，然后单击“创建目录”按钮。

  3. 按照创建目录中的说明创建外部目录。

SQL

在笔记本或 SQL 查询编辑器中运行以下 SQL 命令。 括号中的项是可选的。 替换占位符值：

  • <catalog-name>：Azure Databricks 中目录的名称。
  • <connection-name>：指定数据源、路径和访问凭据的连接对象。
  • <database-name>：要在 Azure Databricks 中镜像为目录的数据库的名称。
CREATE FOREIGN CATALOG [IF NOT EXISTS] <catalog-name> USING CONNECTION <connection-name>
OPTIONS (database '<database-name>');

支持的下推

下表列出了 PostgreSQL 支持的下推操作以及每个操作所需的计算。

下推 支持的计算
日期、时间和时间戳函数
（部分，仅筛选表达式）		 支持 所有计算
Filters 支持 所有计算
Limit 支持 所有计算
数学函数
（部分，仅筛选表达式）		 支持 所有计算
其他功能
（如 Alias、Cast、SortOrder;部分筛选器表达式）		 支持 所有计算
Offset 支持 所有计算
Projections 支持 所有计算
字符串函数
（部分，仅筛选表达式）		 支持 所有计算
TABLESAMPLE
（无更换）		 支持 所有计算
聚合 支持 所有计算
算数运算符
（如 +、-、*、%、/;如果禁用 ANSI，则不支持）		 支持 所有计算
布尔运算符
（例如 =、<=>、<、<=、>、>=）		 支持 所有计算
位运算符
（&、| 和 ~）		 支持 所有计算
排序（与限制一起使用时） 支持 所有计算
Joins 支持 Databricks Runtime 17.2 及更高版本和 SQL 仓库计算。 此下推功能处于公开预览阶段；请在预览页面上启用联合查询的连接下推开关。
Windows 函数 不支持 不支持

数据类型映射

从 PostgreSQL 读取到 Spark 时，数据类型映射如下所示：

PostgreSQL 类型 Spark 类型
numeric DecimalType
int2 ShortType
int4 （如果未签名） IntegerType
int8、、oidxid、 int4 （如果已签名） LongType
float4 FloatType
double precision、float8 DoubleType
char CharType
name、varchar、tid VarcharType
bpchar、character varying、json、jsonb、money、point、super、text StringType
bytea、geometry、varbyte BinaryType
bit、bool BooleanType
date DateType
tabstime、time、带时区的 time、timetz、不带时区的 time、带时区的 timestamp、timestamp、timestamptz、不带时区的 timestamp* TimestampType/TimestampNTZType
Postgresql 数组类型** ArrayType

*从 PostgreSQL 读取时，如果 Timestamp（默认），PostgreSQL TimestampType 会映射到 Spark preferTimestampNTZ = false。 如果 Timestamp，PostgreSQL TimestampNTZType 会映射到 preferTimestampNTZ = true。

**支持有限的数组类型。

其他资源

  • Last updated on