Run federated queries on PostgreSQL
This article describes how to set up Lakehouse Federation to run federated queries on Run queries on PostgreSQL data that is not managed by Azure Databricks. To learn more about Lakehouse Federation, see What is Lakehouse Federation?.
To connect to your Run queries on PostgreSQL database using Lakehouse Federation, you must create the following in your Azure Databricks Unity Catalog metastore:
- A connection to your Run queries on PostgreSQL database.
- A foreign catalog that mirrors your Run queries on PostgreSQL database in Unity Catalog so that you can use Unity Catalog query syntax and data governance tools to manage Azure Databricks user access to the database.
Before you begin
Workspace requirements:
- Workspace enabled for Unity Catalog.
Compute requirements:
Network connectivity from your compute resource to the target database systems. See Networking recommendations for Lakehouse Federation.
Azure Databricks compute must use Databricks Runtime 13.3 LTS or above and Shared or Single user access mode.
SQL warehouses must be pro and must use 2023.40 or above.
Permissions required:
- To create a connection, you must be a metastore admin or a user with the
CREATE CONNECTION
privilege on the Unity Catalog metastore attached to the workspace. - To create a foreign catalog, you must have the
CREATE CATALOG
permission on the metastore and be either the owner of the connection or have theCREATE FOREIGN CATALOG
privilege on the connection.
Additional permission requirements are specified in each task-based section that follows.
Create a connection
A connection specifies a path and credentials for accessing an external database system. To create a connection, you can use Catalog Explorer or the CREATE CONNECTION
SQL command in an Azure Databricks notebook or the Databricks SQL query editor.
Note
You can also use the Databricks REST API or the Databricks CLI to create a connection. See POST /api/2.1/unity-catalog/connections and Unity Catalog commands.
Permissions required: Metastore admin or user with the CREATE CONNECTION
privilege.
Catalog Explorer
In your Azure Databricks workspace, click Catalog.
At the top of the Catalog pane, click the Add icon and select Add a connection from the menu.
Alternatively, from the Quick access page, click the External data > button, go to the Connections tab, and click Create connection.
Enter a user-friendly Connection name.
Select a Connection type of PostgreSQL.
Enter the following connection properties for your PostgreSQL instance.
- Host: For example,
postgres-demo.lb123.cn-north-2.rds.amazonaws.com
- Port: For example,
5432
- User: For example,
postgres_user
- Password: For example,
password123
- Host: For example,
(Optional) Click Test connection to confirm that it works.
(Optional) Add a comment.
Click Create.
SQL
Run the following command in a notebook or the Databricks SQL query editor.
CREATE CONNECTION <connection-name> TYPE postgresql
OPTIONS (
host '<hostname>',
port '<port>',
user '<user>',
password '<password>'
);
We recommend that you use Azure Databricks secrets instead of plaintext strings for sensitive values like credentials. For example:
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>')
)
For information about setting up secrets, see Secret management.
Create a foreign catalog
A foreign catalog mirrors a database in an external data system so that you can query and manage access to data in that database using Azure Databricks and Unity Catalog. To create a foreign catalog, you use a connection to the data source that has already been defined.
To create a foreign catalog, you can use Catalog Explorer or the CREATE FOREIGN CATALOG
SQL command in an Azure Databricks notebook or the SQL query editor.
Note
You can also use the Databricks REST API or the Databricks CLI to create a catalog. See POST /api/2.1/unity-catalog/catalogs and Unity Catalog commands.
Permissions required: CREATE CATALOG
permission on the metastore and either ownership of the connection or the CREATE FOREIGN CATALOG
privilege on the connection.
Catalog Explorer
In your Azure Databricks workspace, click Catalog to open Catalog Explorer.
At the top of the Catalog pane, click the Add icon and select Add a catalog from the menu.
Alternatively, from the Quick access page, click the Catalogs button, and then click the Create catalog button.
Follow the instructions for creating foreign catalogs in Create catalogs.
SQL
Run the following SQL command in a notebook or SQL query editor. Items in brackets are optional. Replace the placeholder values:
<catalog-name>
: Name for the catalog in Azure Databricks.<connection-name>
: The connection object that specifies the data source, path, and access credentials.<database-name>
: Name of the database you want to mirror as a catalog in Azure Databricks.
CREATE FOREIGN CATALOG [IF NOT EXISTS] <catalog-name> USING CONNECTION <connection-name>
OPTIONS (database '<database-name>');
Supported pushdowns
The following pushdowns are supported on all compute:
- Filters
- Projections
- Limit
- Functions: partial, only for filter expressions. (String functions, Mathematical functions, and other miscellaneous functions, such as Alias, Cast, SortOrder)
The following pushdowns are supported on Databricks Runtime 13.3 LTS and above, and on SQL warehouses:
- The following aggregation functions: 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
- The following Boolean functions: =, <, <, =>, >=, <=>
- The following mathematical functions (not supported if ANSI is disabled): +, -, *, %, /
- Miscellaneous operators | and ~
- Sorting, when used with limit
The following pushdowns are not supported:
- Joins
- Windows functions
Data type mappings
When you read from PostgreSQL to Spark, data types map as follows:
PostgreSQL type | Spark type |
---|---|
numeric | DecimalType |
int2 | ShortType |
int4 (if not signed) | IntegerType |
int8, oid, xid, int4 (if signed) | LongType |
float4 | FloatType |
double precision, float8 | 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, time, time with time zone, timetz, time without time zone, timestamp with time zone, timestamp, timestamptz, timestamp without time zone* | TimestampType/TimestampNTZType |
Postgresql array type** | ArrayType |
*When you read from Postgresql, Postgresql Timestamp
is mapped to Spark TimestampType
if preferTimestampNTZ = false
(default). Postgresql Timestamp
is mapped to TimestampNTZType
if preferTimestampNTZ = true
.
**Limited array types are supported.