0

I'm trying to connect to a postgresql database on my local machine from databricks using a JDBC connection. There are several useful posts in stackoverflow. I'm following the procedure mentioned in the documentation in spark.apache.org and databricks website.

also these posts : 1 , 2 & 3

In RStudio I can connect to postgresql database via this script :

# Define data base credential ----
psql <- DBI::dbDriver("PostgreSQL")

con <- dbConnect(
  psql,
  dbname = 'mydbname',
  host = 'hostname',
  port = 5444,
  user = 'username',
  password = 'password')

I am trying to connect postgresql via JDBC to databricks. I used two approaches:

    # method 1
    jdbcHostname = "hostname"
    jdbcDatabase = "mydbname"
    jdbcPort = 5444
    
    
    jdbcUrl = "jdbc:postgresql://{0}:{1}/{2}".format(jdbcHostname, jdbcPort, jdbcDatabase)
    connectionProperties = {
      "user" : "username",
      "password" : "password",
      "driver" : "org.postgresql.Driver"
    }
pushdown_query = "SELECT * FROM mytable"
df = spark.read.jdbc(url=jdbcUrl, table= pushdown_query , properties=connectionProperties)

and this one

#method2
jdbcDF = spark.read \
    .format("jdbc") \
    .option("url", "jdbc:postgresql://hostname:5444/mydbname") \
    .option("dbtable", "SELECT * FROM schema.mytable") \
    .option("user", "username") \
    .option("password", "password")\
    .load()

but I am getting error on both tries like this :

Connection to localhost:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections

I couldn't understand why it tries to connect to localhost:5432 when I specified localhost:5444?

DanG
  • 773
  • 14
  • 32

2 Answers2

4

I'm able to connecting to Azure Database for PostgreSQL server to Databricks via JDBC connection.

You may try the below steps:

Prerequisites:

Azure Database for PostgreSQL server => JDBC Connection String

jdbc:postgresql://{server_name}.postgres.database.azure.com:5432/{your_database}?user={Admin_username}&password={your_password}&sslmode=require

enter image description here

Step 1: Connection Information

driver = "org.postgresql.Driver"
url = "jdbc:postgresql://cheprapostgresql.postgres.database.azure.com:5432/postgres?user=chepra@cheprapostgresql&password=XXXXXXXXXXXXXX&sslmode=require"
dbname = "postgres"
dbtable = "inventory"

Step 2: Reading the data

jdbcDF = spark.read \
    .format("jdbc") \
    .option("driver", driver)\
    .option("url", url)\
    .option("dbname", dbname) \
    .option("dbtable", dbtable)\
    .load()

enter image description here

Reference: This notebook shows you how to load data from JDBC databases using Spark SQL.

CHEEKATLAPRADEEP-MSFT
  • 11,445
  • 1
  • 14
  • 35
0

Check the following file in intelij "application.properties" for example:

specifically: Username, Password, Port(this case: 5432) and make sure you actually created the database by its name(this case: 'student') before and its consist with this lines for example:

spring.datasource.url=jdbc:postgresql://localhost:5432/student
spring.datasource.username=
spring.datasource.password=
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect = `org.hibernate.dialect.PostgreSQLDialect`
spring.jpa.properties.hibernate.format_sql = true

server.error.include-message=always
ziv Daniel
  • 1
  • 1