Reading Spark DataFrame from Redshift returns empty DataFrame

Question

I'm using:

python 3.6.8
spark 2.4.4

I run pyspark with an EMR cluster (emr-5.28.0) with: pyspark --packages org.apache.spark:spark-avro_2.11:2.4.4

I have the following jars in the spark classpath:

I execute this piece of code:

url = "jdbc:redshift://my.cluster:5439/my_db?user=my_user&password=my_password"
query = "select * from schema.table where trunc(timestamp)='2019-09-10'"
df = sqlContext.read.format('com.databricks.spark.redshift')\
.option("url", url)\
.option("tempdir", "s3a://bucket/tmp_folder")\
.option("query", query)\
.option("aws_iam_role", "arn_iam_role")\
.load()

And doing df.count() it returns 73 (which is the number of rows of that query) but if I do df.show(4) it returns an empty DataFrame, no error, it just prints the schema.

Did you try df.show(4,false) or just df.show(). Do you see similar behavior? — arjunj
Do you do the count and the show in the same cell? Are you rebinding the df prior to show? Have you considered df.take(1) to debug? — Oliver W.

frm frm · Accepted Answer · 2019-11-25T16:07:26

I've made it work by changing the format to 'jdbc' and only use the databricks driver to write data, not read.

Reading Spark DataFrame from Redshift returns empty DataFrame

1 Answers