How to connect Synapse SparkPool notebook with external databases

lucas.pontes 11 Reputation points
2022-04-28T14:46:27.733+00:00

I'm trying to connect my Synapse SparkPool notebook to a external Postgres DataBase. I set the conecction as described here access-postgresql-from-azure-synapse-notebook however it returning an error related to IP not in pg_hba.confg.

I think it is because the VPN.
My question is: what is the correct way to access external postgres database, that is covered by VNP, in Synapse SparkPool notebooks ?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,668 questions
Azure Database for PostgreSQL
0 comments No comments
{count} vote

1 answer

Sort by: Most helpful
  1. Vedant Desai 651 Reputation points
    2023-05-16T04:57:14.21+00:00

    Hey @lucas.pontes

    here are the step-by-step instructions on how to access an external Postgres database that is covered by a VPN in Synapse SparkPool notebooks:

    1. Open the Synapse SparkPool notebook.
    2. In the notebook, click on the "File" menu and select "Settings".
    3. In the "Settings" dialog, select the "Connections" tab.
    4. In the "Connections" tab, click on the "Add" button.
    5. In the "Add Connection" dialog, select the "Proxy" connection type.
    6. In the "Proxy" connection type, enter the following information: Host: The hostname or IP address of the proxy server. Port: The port number of the proxy server. Username: The username for the proxy server. Password: The password for the proxy server.
    7. Click on the "Test" button to test the connection to the proxy server.
    8. If the connection is successful, click on the "Save" button.
    9. Open the connection string for the external Postgres database.
    10. In the connection string, find the following section:
    [connection]
    host=localhost
    port=5432
    database=postgres
    username=postgres
    password=postgres
    
    1. Replace the value of the "host" property with the hostname or IP address of the proxy server.
    2. Replace the value of the "port" property with the port number of the proxy server.
    3. Add the following section to the connection string:
    [proxy]
    host=localhost
    port=8080
    username=username
    password=password
    
    
    1. Save the connection string.
    2. In the Synapse SparkPool notebook, create a new cell.
    3. In the new cell, import the following libraries:
    import pyodbc
    
    1. In the new cell, create a connection object to the external Postgres database using the connection string you updated in step 13.
    conn = pyodbc.connect(connection_string)
    
    
    1. In the new cell, execute a query against the external Postgres database.
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM table")
    rows = cursor.fetchall()
    
    
    1. In the new cell, print the rows returned by the query.
    for row in rows:
    print(row)
    
    1. Run the cell.

    If you have followed these steps correctly, you should be able to connect to the external Postgres database from your Synapse SparkPool notebook and execute queries against it.

    If you find this helpful please Accept the answer by clicking Accept Answer Button and Upvote.

    0 comments No comments