Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
In this tutorial step, you connect a local Python application to the PostgreSQL endpoint. You query one view that reads warehouse signals through federation and another that reads signals loaded into PostgreSQL by the Copy job.
Important
Fabric HorizonDB is in public preview. Only the Microsoft Entra identity that created the database can connect. Run this local exercise as the creator; service principals and managed identities can't connect in the current preview.
Prerequisites
- Complete Analyze mirrored data in a notebook.
- Keep
fabric_sales.customer_signalsfrom the federation step andanalytics_ops.customer_signalsfrom the Copy job step. - Install Python 3.10 or later.
- Configure the connection values described in Connect with Python, including the tenant and preview token scope.
Use the PostgreSQL host from the contoso-orders-db item's connection pane and database name postgres. Don't use the Fabric item display name or the SQL analytics endpoint.
Create application views
Run this SQL in the HorizonDB PostgreSQL editor:
CREATE OR REPLACE VIEW horizon_sample.customer_analytics AS
SELECT
customer.customer_id,
customer.customer_name,
signal.risk_score,
signal.recommended_action,
signal.calculated_at
FROM horizon_sample.customers AS customer
JOIN fabric_sales.customer_signals AS signal
ON signal.customer_id = customer.customer_id;
CREATE OR REPLACE VIEW horizon_sample.customer_operational_insights AS
SELECT
customer.customer_id,
customer.customer_name,
signal.risk_score,
signal.recommended_action,
signal.calculated_at
FROM horizon_sample.customers AS customer
JOIN analytics_ops.customer_signals AS signal
ON signal.customer_id = customer.customer_id;
The first view has a remote warehouse dependency at query time. The second reads only local PostgreSQL tables; its freshness depends on the last successful load and publish step. These views organize application queries; they don't enable additional PostgreSQL users.
Create the application
Create a folder named contoso-orders-app, create and activate a virtual environment, and install:
python -m pip install "psycopg[binary]" azure-identity
Create app.py:
import os
import sys
import psycopg
from azure.identity import InteractiveBrowserCredential
customer_id = int(sys.argv[1])
with InteractiveBrowserCredential(
tenant_id=os.environ["HORIZONDB_TENANT_ID"]
) as credential:
token = credential.get_token(
os.environ["HORIZONDB_TOKEN_SCOPE"]
).token
with psycopg.connect(
host=os.environ["HORIZONDB_HOST"],
port=int(os.environ["HORIZONDB_PORT"]),
dbname=os.environ["HORIZONDB_DATABASE"],
user=os.environ["HORIZONDB_USER"],
password=token,
sslmode=os.environ["HORIZONDB_SSLMODE"],
connect_timeout=30,
application_name="contoso-orders-app",
) as connection:
with connection.cursor() as cursor:
cursor.execute("SET statement_timeout = '30s'")
cursor.execute(
"""
SELECT customer_name, risk_score,
recommended_action, calculated_at
FROM horizon_sample.customer_analytics
WHERE customer_id = %s
""",
(customer_id,),
)
print("Federated result:", cursor.fetchall())
cursor.execute(
"""
SELECT customer_name, risk_score,
recommended_action, calculated_at
FROM horizon_sample.customer_operational_insights
WHERE customer_id = %s
""",
(customer_id,),
)
print("Loaded local result:", cursor.fetchall())
The driver binds the customer ID as a parameter. Don't concatenate user input into SQL, print the token, or store credentials in source code.
Run and validate
Load the connection environment values, then run the application with an actual customer ID from horizon_sample.customers. For example:
python app.py 1
Sign in as the database creator in the browser. Confirm that both results match the selected customer's source data. If a federated query fails, investigate source permissions, capacity, and metadata separately from local PostgreSQL health. The example surfaces errors rather than silently falling back to older local data.
Use bounded retries only for safe, transient connection failures. Don't retry authentication or permission errors without correcting them, and don't replay an uncertain write transaction.
Expected outcome
The application uses one PostgreSQL connection to query federated analytical signals and loaded operational signals. Their values can differ if the warehouse changed after the Copy job.