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
  • 689
  • 1
  • 16
  • 39

1 Answers1

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