java.lang.IllegalArgumentException: Unsupported JDBC protocol: 'postgresql'

[hafizmujadid]

How to use postgresql jdbc driver with spark-redshift?
following code give me exception
java.lang.IllegalArgumentException: Unsupported JDBC protocol: 'postgresql'

val df1: DataFrame = sqlContext.read
            .format("com.databricks.spark.redshift")
            .option("url", "jdbc:postgresql://host:5439/db?user=test&password=test")
            .option("dbtable", "wdata")
            .option("tempdir", "s3n://accessKEy:SecretKEy@redshift/dir/")
            .load()
        df1.show()

Full stacktrace is as follow

Exception in thread "main" java.lang.IllegalArgumentException: Unsupported JDBC protocol: 'postgresql'
    at com.databricks.spark.redshift.JDBCWrapper$$anonfun$getDriverClass$2.apply(RedshiftJDBCWrapper.scala:68)
    at com.databricks.spark.redshift.JDBCWrapper$$anonfun$getDriverClass$2.apply(RedshiftJDBCWrapper.scala:52)
    at scala.Option.getOrElse(Option.scala:120)
    at com.databricks.spark.redshift.JDBCWrapper.getDriverClass(RedshiftJDBCWrapper.scala:51)
    at com.databricks.spark.redshift.JDBCWrapper.getConnector(RedshiftJDBCWrapper.scala:138)
    at com.databricks.spark.redshift.RedshiftRelation$$anonfun$schema$1.apply(RedshiftRelation.scala:59)
    at com.databricks.spark.redshift.RedshiftRelation$$anonfun$schema$1.apply(RedshiftRelation.scala:56)
    at scala.Option.getOrElse(Option.scala:120)
    at com.databricks.spark.redshift.RedshiftRelation.schema$lzycompute(RedshiftRelation.scala:56)
    at com.databricks.spark.redshift.RedshiftRelation.schema(RedshiftRelation.scala:55)
    at org.apache.spark.sql.execution.datasources.LogicalRelation.<init>(LogicalRelation.scala:31)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:120)

[JoshRosen]

This is both a bug and documentation issue introduced by https://github.com/databricks/spark-redshift/pull/90/files#diff-69806564231efb590460b162532ba683R44, which expects the JDBC subprotocol to be postgres instead of the more-standard postgresql.

As a user, there are two ways that you can work around this:

1. Change the subprotocol in your URI, e.g. use "jdbc:postgres://host:5439/db?user=test&password=test"
2. Leave the URI unchanged and use the jdbcdriver configuration option to explicitly specify that the PostgreSQL driver should be used: .option("jdbcdriver", "org.postgresql.Driver").

Since this behavior is confusing, I should probably fix it and add regression tests, even though Postgres driver support is a lower-priority feature. I'll look into writing a patch for this when I have some time. When I do patch this, I'll make sure that the non-standard postgres URIs continue to work so as not to break backwards compatibility.

[hafizmujadid]

thank you so much josh :)

On Wed, Nov 18, 2015 at 8:54 PM, Josh Rosen [email protected]
wrote:

This is both a bug and documentation issue introduced by
https://github.com/databricks/spark-redshift/pull/90/files#diff-69806564231efb590460b162532ba683R44,
which expects the JDBC subprotocol to be postgres instead of the
more-standard postgresql.

As a user, there are two ways that you can work around this:

1. Change the subprotocol in your URI, e.g. use
   "jdbc:postgres://host:5439/db?user=test&password=test"
2. Leave the URI unchanged and use the jdbcdriver configuration option
   to explicitly specify that the PostgreSQL driver should be used: .option("jdbcdriver",
   "org.postgresql.Driver").

Since this behavior is confusing, I should probably fix it and add
regression tests, even though Postgres driver support is a lower-priority
feature. I'll look into writing a patch for this when I have some time.
When I do patch this, I'll make sure that the non-standard postgres URIs
continue to work so as not to break backwards compatibility.

—
Reply to this email directly or view it on GitHub
#126 (comment)
.

Regards: HAFIZ MUJADID

[JoshRosen]

Digging into this in a little bit more detail, it looks like there's no need to have a backwards-compatibility branch for the incorrect postgres subprotocol since the official Postgres driver won't understand that prefix anyways. I'm about to submit a patch to fix this and clean up some slight code comment and documentation inaccuracies related to JDBC driver precedence.