Search code examples
pythonscalapysparkgreenplum

Errors when trying to access greenplum table using pyspark

I am trying to sign onto a server and pull back data to locally build a model in python. I'm using the pyspark library - but I keep getting the same error. Here is the code I am running, without error, so I know that I've installed the .jar files correctly:

import pyspark
print(pyspark.__file__)
from pyspark.sql import DataFrameReader
from pyspark.sql import SQLContext
from pyspark import SparkContext

sc = SparkContext(appName="PythonStreamingQueueStream") 
sqlContext = SQLContext(sc)
sqlContext.read.format('io.pivotal.greenplum.spark.GreenplumRelationProvider')

sc.stop()

It provides this as the output indicating the jar is correctly installed:

<pyspark.sql.readwriter.DataFrameReader at 0x10819cd10>

I can run this in scala without error, it pulls back the table that I am requesting:

val gscReadOptionMap = Map(
  "url" -> "jdbc:postgresql://12.3.45.678:9101/code",
  "user" -> "my_name",
  "password" -> "password",
  "dbschema" -> "schema",
  "dbtable" -> "table",
  "partitionColumn" -> "max"
)
val gpdf = spark.read.format("greenplum").options(gscReadOptionMap).load()

And it provides the following output:

gpdf: org.apache.spark.sql.DataFrame = [output_code: string, input_code: string ... 11 more fields]

But when I try to put the database sign-on into python I keep getting the same error:

import pyspark
print(pyspark.__file__)
from pyspark.sql import DataFrameReader
from pyspark.sql import SQLContext
from pyspark import SparkContext

sc = SparkContext(appName="PythonStreamingQueueStream") 
sqlContext = SQLContext(sc)
sqlContext.read.format('io.pivotal.greenplum.spark.GreenplumRelationProvider')

source_df = sqlContext.read.format('io.pivotal.greenplum.spark.GreenplumRelationProvider').options(
          url='jdbc:postgresql://12.3.45.678:9101/code',
          dbschema='schema',
          dbtable = 'table',
          user='my_name',
          password='password',
          driver='org.postgresql.Driver',
          partitionColumn='max').load()

sc.stop()

Here is the error, in usual python fashion the error is super long:

Traceback (most recent call last):
  File "<stdin>", line 7, in <module>
  File "/usr/local/Cellar/apache-spark/2.2.0/libexec/python/pyspark/sql/readwriter.py", line 165, in load
    return self._df(self._jreader.load())
  File "/usr/local/Cellar/apache-spark/2.2.0/libexec/python/lib/py4j-0.10.4-src.zip/py4j/java_gateway.py", line 1133, in __call__
  File "/usr/local/Cellar/apache-spark/2.2.0/libexec/python/pyspark/sql/utils.py", line 63, in deco
    return f(*a, **kw)
  File "/usr/local/Cellar/apache-spark/2.2.0/libexec/python/lib/py4j-0.10.4-src.zip/py4j/protocol.py", line 319, in get_return_value
py4j.protocol.Py4JJavaError: An error occurred while calling o111.load.
: java.lang.ClassNotFoundException: Failed to find data source: io.pivotal.greenplum.spark.GreenplumRelationProvider. Please find packages at http://spark.apache.org/third-party-projects.html
    at org.apache.spark.sql.execution.datasources.DataSource$.lookupDataSource(DataSource.scala:549)
    at org.apache.spark.sql.execution.datasources.DataSource.providingClass$lzycompute(DataSource.scala:86)
    at org.apache.spark.sql.execution.datasources.DataSource.providingClass(DataSource.scala:86)
    at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:301)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:178)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:146)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)
    at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357)
    at py4j.Gateway.invoke(Gateway.java:280)
    at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)
    at py4j.commands.CallCommand.execute(CallCommand.java:79)
    at py4j.GatewayConnection.run(GatewayConnection.java:214)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.ClassNotFoundException: io.pivotal.greenplum.spark.GreenplumRelationProvider.DefaultSource
    at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    at org.apache.spark.sql.execution.datasources.DataSource$$anonfun$21$$anonfun$apply$12.apply(DataSource.scala:533)
    at org.apache.spark.sql.execution.datasources.DataSource$$anonfun$21$$anonfun$apply$12.apply(DataSource.scala:533)
    at scala.util.Try$.apply(Try.scala:192)
    at org.apache.spark.sql.execution.datasources.DataSource$$anonfun$21.apply(DataSource.scala:533)
    at org.apache.spark.sql.execution.datasources.DataSource$$anonfun$21.apply(DataSource.scala:533)
    at scala.util.Try.orElse(Try.scala:84)
    at org.apache.spark.sql.execution.datasources.DataSource$.lookupDataSource(DataSource.scala:533)
    ... 16 more

I have the 'greenplum-spark_2.11-1.4.0.jar' and 'postgresql-42.2.4.jre7.jar' being brought in... I'm not sure what I am doing wrong?

edit: I am running the jars in python 2 ways and it has no bearing on the error. 

import os 
os.getcwd() 
os.environ['PYSPARK_SUBMIT_ARGS'] = '--master local[*] pyspark-shell --jars /Users/greenplum-spark_2.11-1.4.0.jar, /Users/postgresql-42.2.4.jre7.jar'

I've also run: 

%%bash 
export GSC_JAR=/Users/greenplum-spark_2.11-1.4.0.jar export POSTGRES_JAR=/Users/postgresql-42.2.4.jre7.jar 
spark-shell --jars ${GSC_JAR}, ${POSTGRES_JAR}
Solution

  • Thank you everyone for the help and suggestions. The pyspark connector / loader does not work in any interface except the terminal. i.e. Jupyter Notebook.