topic Re: How to connect PostgreSQL from Databricks in Data Engineering

How to connect PostgreSQL from Databricks

akj2784 — Thu, 19 Sep 2019 07:11:21 GMT

I am trying to connect PostgreSQL from Azure Databricks.

I am using the below code to connect.

jdbcHostname = "Test"

jdbcPort = 1234

jdbcDatabase = "Test1"

jdbcUrl = "jdbc:postgresql://{0}:{1}/{2}".format(jdbcHostname, jdbcPort, jdbcDatabase)

Connection was successful

connectionProps = { "user": "userid", "password": "pass!" }

Now, when I run the below command

accountDF = spark.read.jdbc(url=jdbcUrl, table="POC_TABLE", properties=connectionProps) display(accountDF)

I get the below error.

org.postgresql.util.PSQLException: FATAL: SSL connection is required. Please specify SSL options and retry.

How do I fix this error. I have SSL enabled in the postgreSQL at Azure portal.

Regards,

Akash

Re: How to connect PostgreSQL from Databricks

shyam_9 — Thu, 19 Sep 2019 07:39:34 GMT

It seems that you didn’t specify SSL options correctly. Please enable SSL and make sure to save it.

Please go through the steps in Documentation and follow them,

https://docs.microsoft.com/en-us/azure/postgresql/concepts-ssl-connection-security

Re: How to connect PostgreSQL from Databricks

akj2784 — Thu, 19 Sep 2019 07:46:48 GMT

SSL enforce status :ENABLED

I see it in Azure portal for this database. What else is missing ?

Re: How to connect PostgreSQL from Databricks

shyam_9 — Thu, 19 Sep 2019 08:05:42 GMT

Can you please follow the steps in documentation and still if you are facing issue, reply here.

Re: How to connect PostgreSQL from Databricks

charlesgreen — Fri, 25 Oct 2019 05:32:19 GMT

Thank you Akash for posting the JDBC connection config for Postgres.

Really wish it was part of the docs.

Re: How to connect PostgreSQL from Databricks

JavierDe_La_Tor — Tue, 28 Jan 2020 09:56:54 GMT

Hi,

I am trying the same. What I am stuck is figuring out how to upload the Key and Certificate files for the JDBC driver to use when using SSL. The connection should look like:

remote_table = spark.read.format("jdbc")\
  .option("driver", driver)\
  .option("url", url)\
  .option("dbtable", table)\
  .option("user", user)\
  .option("password", password)\
  .option("ssl", True) \
  .options("sslmode", "verify-full" ) \
  .options("sslcert", "path_to_cert" ) \
  .options("sslkey", "path_to_key" ) \
  .load()

But I dont know how or where to upload the certificate and and key, so when I ran it I get the following error:

org.postgresql.util.PSQLException: SSL error: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

Any idea how to upload the cert and key to the cluster so that JDBC can find it?

Re: How to connect PostgreSQL from Databricks

Anonymous — Thu, 13 Feb 2020 09:38:15 GMT

@Javier De La Torre do you really need two-way SSL (verify-full)? In most cases one way SSL (sslmode=require) should be enough.

@akj2784 When you say "Connection was successful", where do you mean you established a successful connection? You might be missing the

.option("ssl", True) \ .options("sslmode", "require")

Re: How to connect PostgreSQL from Databricks

TheodoreVadpey — Sun, 18 Oct 2020 21:05:44 GMT

Did you resolve this? I'm having the same problem

Re: How to connect PostgreSQL from Databricks

BorjaMuñoz — Mon, 19 Oct 2020 08:11:57 GMT

Hi,

There are different possibilities:

- You can upload them to the cluster using wget as explained in this article:

https://carto.com/help/tutorials/sql-direct-databricks/

- You can upload the files to an S3 bucket / Azure Blob and then read them from there

- You can import them using the "Import and Explore Data" functionality in the Databricks Welcome page

Re: How to connect PostgreSQL from Databricks

TheodoreVadpey — Fri, 23 Oct 2020 15:10:32 GMT

Hi, thanks for your reply! I couldn't get it to work from dbfs location or driver-- just need to point to ssl-ca... I made a question for it here: https://forums.databricks.com/questions/48860/connect-to-azure-mysql-with-ssl-through-jdbc.html. Am I missing some option?

Re: How to connect PostgreSQL from Databricks

BorjaMuñoz — Mon, 26 Oct 2020 11:37:07 GMT

You are getting a SSL error. It seems you are using SSL mode "verify-full" that requires you to include certificate files to verify your identity, not only the server identity. I'm not familiar with MySQL but it seems "verify-full" is not a supported option.

Re: How to connect PostgreSQL from Databricks

RashidQuamar — Mon, 26 Jul 2021 09:51:02 GMT

https://docs.microsoft.com/en-us/azure/databricks/data/data-sources/sql-databases

The above link shows how to generate and add the required certificates