pyspark-create-dataframe-jdbc.py

from pyspark.sql import SparkSession

spark = SparkSession. \
    builder. \
    master('local'). \
    appName('Create Dataframe over JDBC'). \
    getOrCreate()

orders = spark.read. \
  format('jdbc'). \
  option('url', 'jdbc:mysql://ms.itversity.com'). \
  option('dbtable', 'retail_db.orders'). \
  option('user', 'retail_user'). \
  option('password', 'itversity'). \
  load()

orders.show()

orderItems = spark.read. \
    jdbc("jdbc:mysql://ms.itversity.com", "retail_db.order_items",
          properties={"user": "retail_user",
                      "password": "itversity",
                      "numPartitions": "4",
                      "partitionColumn": "order_item_order_id",
                      "lowerBound": "10000",
                      "upperBound": "20000"})

orderItems.write.json('/user/training/bootcamp/pyspark/orderItemsJDBC')

query = "(select order_status, count(1) from retail_db.orders group by order_status) t"
queryData = spark.read. \
    jdbc("jdbc:mysql://ms.itversity.com", query,
         properties={"user": "retail_user",
                     "password": "itversity"})

queryData.show()