Pyspark.read from MariaDB fetches duplicate header rows instead of data 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 &quot;dob&quot; 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(&quot;Python Spark SQL basic example&quot;) \
   .config('spark.driver.extraClassPath', &quot;/Users/username/Downloads/mariadb-java-client-2.7.3.jar&quot;) \
   .getOrCreate()


##read table from db using spark jdbc
patients_df = spark.read \
   .format(&quot;jdbc&quot;) \
   .option(&quot;url&quot;, &quot;jdbc:mariadb:my_server:3306/SYNTH_EHR_NORM&quot;) \
   .option(&quot;dbtable&quot;, &quot;(select fname, lname from SYNTH_EHR_NORM.PATIENTS limit 20) tmp&quot;) \
   .option(&quot;user&quot;, &quot;username&quot;) \
   .option(&quot;password&quot;, &quot;not_my_real_password&quot;) \
   .option(&quot;driver&quot;, &quot;org.mariadb.jdbc.Driver&quot;) \
   .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')]
Sometimes adding an alias to subquery confuses PySpark. Try:
.option(&quot;dbtable&quot;, &quot;select fname, lname from SYNTH_EHR_NORM.PATIENTS limit 20&quot;) \