This tutorial will explain how to read data from various types of databases(such as Mysql, SingleStore, Teradata) using JDBC Connection into Spark dataframe.

PySpark: DB To Dataframe

This tutorial will explain how to read data from various types of databases(such as Mysql, SingleStore, Teradata) using JDBC Connection into Spark dataframe.

• DataFrameReader "spark.read" can be used to import data into Spark dataframe from different databases.
• repartition() function can be used to increase the number of partition in dataframe while reading data.
• options() or option() function can be used to alter the behavior of read operation. Click here to learn more about JDBC options available for reading data from various databases.
  
  
• Following topics will be covered on this page:
  • Read From Mysql
  • Read From SingleStore(MemSQL)
  • Read From Teradata
  • Read From Hive

➠ Read From Mysql: These are examples to read from Mysql database using JDBC connection. "mysql-connector-java-8.0.11.jar" jar should be present in Spark library to read data from Mysql database using JDBC connection. This jar can be downloaded from Mysql website. Full table data is being fetched using "dbtable" in first example. Custom query using "query" is used in the the second example.

Replace below attributes in the examples:

• Host/port in the URL from Mysql.dbmstutorials.com:3306 to your server.
• Username in the user from tutorial_user to your username.
• Password value in the password from user_password to your password.
• Tablename in the dbtable from tutorial_db.department to your table name.

• Read Full table:

  
  Example 1: Using options()
  
  df=spark.read.format("jdbc").options(driver="com.mysql.cj.jdbc.Driver",
                                          user="tutorial_user",
                                          password="user_password",
                                          url="jdbc:mysql://Mysql.dbmstutorials.com:3306?serverTimezone=UTC&useSSL=false",
                                          dbtable="tutorial_db.department"
                                          ).load()
  
  df.show()
  +-------+--------------------+-----------+
  |dept_no|     department_name|   loc_name|
  +-------+--------------------+-----------+
  |    100|            ACCOUNTS|     JAIPUR|
  |    200|               R & D|  NEW DELHI|
  |    300|               SALES|  BENGALURU|
  |    400|INFORMATION TECHN...|BHUBANESWAR|
  +-------+--------------------+-----------+
  
  
  
  Example 2: Using option()
  
  df=spark.read.format("jdbc") \
               .option("driver","com.mysql.cj.jdbc.Driver") \
               .option("user","tutorial_user") \
               .option("password","user_password") \
               .option("url","jdbc:mysql://Mysql.dbmstutorials.com:3306?serverTimezone=UTC&useSSL=false") \
               .option("dbtable","tutorial_db.department") \
               .load()
  
  df.show()
  +-------+--------------------+-----------+
  |dept_no|     department_name|   loc_name|
  +-------+--------------------+-----------+
  |    100|            ACCOUNTS|     JAIPUR|
  |    200|               R & D|  NEW DELHI|
  |    300|               SALES|  BENGALURU|
  |    400|INFORMATION TECHN...|BHUBANESWAR|
  +-------+--------------------+-----------+
  

  
  
• Read with custom query:

  
  df=spark.read.format("jdbc").options(driver="com.mysql.cj.jdbc.Driver",
                                       user="tutorial_user",
                                       password="user_password",
                                       url="jdbc:mysql://Mysql.dbmstutorials.com:3306?serverTimezone=UTC&useSSL=false",
                                       query="SELECT * FROM tutorial_db.department WHERE dept_no IN (100,200)"
                                       ).load()
  
  df.show()
  +-------+---------------+---------+
  |dept_no|department_name| loc_name|
  +-------+---------------+---------+
  |    100|       ACCOUNTS|   JAIPUR|
  |    200|          R & D|NEW DELHI|
  +-------+---------------+---------+
  

➠ Read From SingleStore: These are examples to read from SingleStore / MemSQL database using JDBC connection. Full table data is being fetched using "dbtable" in first example. Custom query using "query" is used in the the second example.

Replace below attributes in the examples:

• Host/port in the URL from singlestore.dbmstutorials.com:3306 to your server.
• Username in the user from tutorial_user to your username.
• Password value in the password from user_password to your password.
• Database in the URL from tutorial_db database to your database name.
• Tablename in the dbtable from tutorial_db.department to your table name.

• Read Full table:

  
  df=spark.read.format("jdbc").options(driver="org.mariadb.jdbc.Driver", 
                                       user="tutorial_user", 
                                       password="user_password",
                                       url= "jdbc:mysql://singlestore.dbmstutorials.com:3306/tutorial_db",
                                       dbtable="department",
                                       partitionColumn="dept_no",
                                       lowerBound=10,
                                       upperBound=30,
                                       numPartitions=5
                                       ).load()
  
  df.show()
  +-------+--------------------+-----------+
  |dept_no|     department_name|   loc_name|
  +-------+--------------------+-----------+
  |    100|            ACCOUNTS|     JAIPUR|
  |    200|               R & D|  NEW DELHI|
  |    300|               SALES|  BENGALURU|
  |    400|INFORMATION TECHN...|BHUBANESWAR|
  +-------+--------------------+-----------+
  

  
  
• Read with custom query:

  
  df=spark.read.format("jdbc").options(driver="org.mariadb.jdbc.Driver",
                                       user="tutorial_user", 
                                       password="user_password",
                                       url= "jdbc:mysql://singlestore.dbmstutorials.com:3306/tutorial_db",
                                       query="SELECT * FROM tutorial_db.department WHERE dept_no=300"
                                       ).load()
  
  df.show()
  +-------+---------------+---------+
  |dept_no|department_name| loc_name|
  +-------+---------------+---------+
  |    300|          SALES|BENGALURU|
  +-------+---------------+---------+
  

➠ Read From Teradata: These are examples to read from Teradata database using JDBC connection. "terajdbc4.jar" jar should be present in Spark library to read data from Teradata using JDBC connection. This jar can be downloaded from Teradata website. Full table data is being fetched using "dbtable" in first example. Custom query using "query" is used in the the second example.

Replace below attributes in the examples:

• Host in the URL from Teradata.dbmstutorials.com to your server.
• Username in the user from tutorial_user to your username.
• Password value in the password from user_password to your password.
• Tablename in the dbtable from tutorial_db.department to your table name.

• Read Full table:

  
  teradata_df = spark.read.format("jdbc") \
                           .option("url", "jdbc:teradata://Teradata.dbmstutorials.com/COLUMN_NAME=ON") \
                           .option("dbtable", "tutorial_db.department") \
                           .option("user", "tutorial_user") \
                           .option("password", "user_password") \
                           .option("driver","com.teradata.jdbc.TeraDriver") \
                           .load()
  
  teradata_df.show()
  +-------+--------------------+-----------+                                      
  |dept_no|     department_name|   loc_name|
  +-------+--------------------+-----------+
  |    200|               R & D|  NEW DELHI|
  |    100|            ACCOUNTS|     JAIPUR|
  |    400|INFORMATION TECHN...|BHUBANESWAR|
  |    300|               SALES|  BENGALURU|
  +-------+--------------------+-----------+
  

  
  
• Read with custom query:

  
  teradata_df = spark.read.format("jdbc") \
                          .option("url","jdbc:teradata://Teradata.dbmstutorials.com/COLUMN_NAME=ON") \
                          .option("query","SELECT * FROM tutorial_db.department WHERE dept_no=400") \
                          .option("user","tutorial_user") \
                          .option("password","user_password") \
                          .option("driver","com.teradata.jdbc.TeraDriver") \
                          .load()
  
  teradata_df.show()
  +-------+--------------------+-----------+
  |dept_no|     department_name|   loc_name|
  +-------+--------------------+-----------+
  |    400|INFORMATION TECHN...|BHUBANESWAR|
  +-------+--------------------+-----------+
  

➠ Read From Hive: This section will explain how to read data from hive table. If hive setup is present for spark then table() function can be used to read data from Hive. Full table data is being fetched using "dbtable" in first example. Custom query using "sql" is used in the the second example.

• Read Full table:

  
  hive_df = spark.read.table("retail.orders")
  
  hive_df.show(5)
  +--------+--------------------+-----------------+------------+
  |order_id|          order_date|order_customer_id|order_status|
  +--------+--------------------+-----------------+------------+
  |   68817|2014-03-27 00:00:...|             6704|    COMPLETE|
  |   68818|2014-03-31 00:00:...|            12393|  PROCESSING|
  |   68819|2014-04-03 00:00:...|             1212|    COMPLETE|
  |   68820|2014-04-04 00:00:...|             6358|    COMPLETE|
  |   68821|2014-04-05 00:00:...|             2564|    COMPLETE|
  +--------+--------------------+-----------------+------------+
  

  
  
• Read with custom query:

  
  hive_df = spark.sql("SELECT * FROM retail.orders WHERE order_id IN (68817, 68819)")
  
  hive_df.show()
  +--------+--------------------+-----------------+------------+
  |order_id|          order_date|order_customer_id|order_status|
  +--------+--------------------+-----------------+------------+
  |   68817|2014-03-27 00:00:...|             6704|    COMPLETE|
  |   68819|2014-04-03 00:00:...|             1212|    COMPLETE|
  +--------+--------------------+-----------------+------------+