1
$\begingroup$

It's a very strange issue- instead of fetching data like expected, it returns every row as a duplicate of the header. Every cell is a literal string of the column name.

If I don't use the query like below and instead just put the table, it errors out trying to cast the literal column name as its datatype, e.g. it will try to cast literally "dob" as a date. I'm posting because I don't see anything about similar issues elsewhere online.I've tried multiple versions of the JDBC driver, so I'm not sure where to go from here.

Code to reproduce (network and user info removed ofc):

##import required libraries
import pyspark

##create spark session
spark = pyspark.sql.SparkSession \
   .builder \
   .appName("Python Spark SQL basic example") \
   .config('spark.driver.extraClassPath', "/Users/username/Downloads/mariadb-java-client-2.7.3.jar") \
   .getOrCreate()


##read table from db using spark jdbc
patients_df = spark.read \
   .format("jdbc") \
   .option("url", "jdbc:mariadb:my_server:3306/SYNTH_EHR_NORM") \
   .option("dbtable", "(select fname, lname from SYNTH_EHR_NORM.PATIENTS limit 20) tmp") \
   .option("user", "username") \
   .option("password", "not_my_real_password") \
   .option("driver", "org.mariadb.jdbc.Driver") \
   .load()

##print the users dataframe
print(patients_df.collect())

which returns:

[Row(fname='fname', lname='lname'), Row(fname='fname', lname='lname'), Row(fname='fname', lname='lname'), Row(fname='fname', lname='lname'), Row(fname='fname', lname='lname'), Row(fname='fname', lname='lname'), Row(fname='fname', lname='lname'), Row(fname='fname', lname='lname'), Row(fname='fname', lname='lname'), Row(fname='fname', lname='lname'), Row(fname='fname', lname='lname'), Row(fname='fname', lname='lname'), Row(fname='fname', lname='lname'), Row(fname='fname', lname='lname'), Row(fname='fname', lname='lname'), Row(fname='fname', lname='lname'), Row(fname='fname', lname='lname'), Row(fname='fname', lname='lname'), Row(fname='fname', lname='lname'), Row(fname='fname', lname='lname')]
$\endgroup$
1
$\begingroup$

Sometimes adding an alias to subquery confuses PySpark. Try:

.option("dbtable", "select fname, lname from SYNTH_EHR_NORM.PATIENTS limit 20") \
$\endgroup$
1
  • $\begingroup$ That was a good read, ty. It looks like people were using the alias thing as a workaround for not knowing that you need to change "dbtable" to "query" in the first parameter. However, even after correcting that, I'm still getting the same behavior. Is this a bug in the jdbc or something? $\endgroup$ May 20 at 3:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.