在 PostgreSQL 上运行联合查询
本文介绍如何设置 Lakehouse Federation,以对非 Azure Databricks 管理的 PostgreSQL 数据运行联合查询。 若要详细了解 Lakehouse Federation,请参阅“什么是 Lakehouse Federation?”。
要使用 Lakehouse Federation 连接到 PostgreSQL 数据库上的运行查询,必须在 Azure Databricks Unity Catalog 元存储中创建以下内容:
- 与 PostgreSQL 数据库上的运行查询的连接。
- 镜像 Unity Catalog 中 PostgreSQL 数据库上运行查询的外部目录,以便你可使用 Unity Catalog 查询语法和数据治理工具来管理 Azure Databricks 用户对数据库的访问。
开始之前
工作区要求:
- 已为 Unity Catalog 启用工作区。
计算要求:
从 Databricks Runtime 群集或 SQL 仓库到目标数据库系统的网络连接。 请参阅 Lakehouse Federation 网络建议。
Azure Databricks 群集必须使用 Databricks Runtime 13.3 LTS(或更高版本)和共享或单用户访问模式。
SQL 仓库必须是专业版,并且必须使用 2023.40 或更高版本。
所需的权限:
- 若要创建连接,你必须是元存储管理员或对附加到工作区的 Unity Catalog 元存储具有
CREATE CONNECTION
权限的用户。 - 若要创建外部目录,必须对元存储具有
CREATE CATALOG
权限,并且是连接的所有者或对连接具有CREATE FOREIGN CATALOG
特权。
后面的每个基于任务的部分中都指定了其他权限要求。
创建连接
连接指定用于访问外部数据库系统的路径和凭据。 若要创建连接,可以使用目录资源管理器,或者使用 Azure Databricks 笔记本或 Databricks SQL 查询编辑器中的 CREATE CONNECTION
SQL 命令。
注意
你还可以使用 Databricks REST API 或 Databricks CLI 来创建连接。 请参阅 POST /api/2.1/unity-catalog/connections 和 Unity Catalog 命令。
所需的权限:具有 CREATE CONNECTION
特权的元存储管理员或用户。
目录资源管理器
在 Azure Databricks 工作区中,单击 “目录”。
在“目录”窗格顶部,单击 “添加”图标,然后从菜单中选择“添加连接”。
也可在“快速访问”页中单击“外部数据 >”按钮,转到“连接”选项卡,然后单击“创建连接”。
输入用户友好的连接名称。
选择 PostgreSQL 的“连接类型”。
输入你的 PostgreSQL 实例的以下连接属性。
- 主机:例如
postgres-demo.lb123.cn-north-2.rds.amazonaws.com
- 端口:例如
5432
- 用户:例如
postgres_user
- 密码:例如
password123
- 主机:例如
(可选)单击“测试连接”以确认它是否正常工作。
(可选)添加注释。
单击“创建”。
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>')
)
有关设置机密的详细信息,请参阅机密管理。
创建外部目录
外部目录镜像外部数据系统中的数据库,以便可以使用 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
权限。
目录资源管理器
在 Azure Databricks 工作区中,单击 “目录”以打开目录资源管理器。
在“目录”窗格顶部,单击 “添加”图标,然后从菜单中选择“添加目录”。
也可在“快速访问”页中单击“目录”按钮,然后单击“创建目录”按钮。
按照创建目录中的说明创建外部目录。
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>');
支持的下推
所有计算均支持以下下推:
- 筛选器
- 投影
- 限制
- 函数:部分,仅适用于筛选器表达式。 (字符串函数、数学函数、数据、时间和时间戳函数以及其他杂项函数,例如 Alias、Cast、SortOrder)
Databricks Runtime 13.3 LTS 及更高版本和 SQL 仓库支持下列下推:
- 以下聚合函数:MIN、MAX、COUNT、SUM、AVG、VAR_POP、VAR_SAMP、STDDEV_POP、STDDEV_SAMP、GREATEST、LEAST、COVAR_POP、COVAR_SAMP、CORR、REGR_INTERCEPT、REGR_R2、REGR_SLOPE、REGR_SXY
- 以下布尔函数:=、<、<、=>、>=、<=>
- 以下数学函数(在禁用了 ANSI 时不受支持):+、-、*、%、/
- 其他运算符 | 和 ~
- 排序,与限制一起使用时
不支持以下下推:
- 联接
- Windows 函数
数据类型映射
从 PostgreSQL 读取到 Spark 时,数据类型映射如下所示:
PostgreSQL 类型 | Spark 类型 |
---|---|
数字 | DecimalType |
int2 | ShortType |
int4(如果未签名) | IntegerType |
int8、oid、xid、int4(如果已签名) | LongType |
float4 | FloatType |
双精度,浮点数8 | DoubleType |
char | CharType |
name, varchar, tid | VarcharType |
bpchar, character varying, json, money, point, super, text | StringType |
bytea, geometry, varbyte | BinaryType |
bit, bool | BooleanType |
date | DateType |
tabstime,时间,含时区的时间,timetz,不含时区的时间,含时区的时间戳,时间戳,timestamptz、,含时区的时间戳* | TimestampType/TimestampNTZType |
Postgresql 数组类型** | ArrayType |
*从 PostgreSQL 读取时,如果 preferTimestampNTZ = false
(默认),PostgreSQL Timestamp
会映射到 Spark TimestampType
。 如果 preferTimestampNTZ = true
,PostgreSQL Timestamp
会映射到 TimestampNTZType
。
**支持有限的数组类型。