Extract Data From IBM DB2 Data base with Pyspark in HDFS Shell Environment
Hi All, In this article I have demonstrated how to extract data from IBM Db2 database and store the data in hdfs locations .
I assume that you are familiar to the pyspark . If not , you can visit https://sparkbyexamples.com/pyspark-tutorial/
It helped me alot.
Problem Statement : Extract data from IBM DB2 and store it in the HDFS location.
Solution :
Pre-requisites :
1. IBM DB2 Driver (Download it from official Website)
Role of Driver in Pyspark : Db2 Connection : Pyspark needs a jar file in order to connect to any database .In our case , it is db2 jar file which will help pyspark to build a connection to the db2 database. If you are not providing the jar file, the connection will fail even if you provide the host and port details and the code will throw an error.
2. Pyspark : If you are in hdfs environment, pyspark will be there . If not, you can visit the below website to set up the environment . In this problem statement, I am running pyspark in hdfs environment
https://www.datacamp.com/community/tutorials/installation-of-pyspark
Solution :
Step 1 : Open your hdfs terminal with below command :
pyspark — jars /your/location//ForJar/db2jcc4.jar — num-executors 20 — executor-memory 1G — executor-cores 05
Note that I have specified the jar file , number of executors and cores.
Get Priya Chauhan’s stories in your inbox
Join Medium for free to get updates from this writer.
Step 2 : Now pyspark knows where to find the jar when its asked to read data from db2 database.
Creating the SparkSession :
spark = SparkSession.builder \
.master(“yarn”) \
.appName(“ETL_Pyspark”) \
.getOrCreate()
I know the Spark families will think that I can provide the jar location in config option while creating the SparkSession, but trust me it didn’t work with me:(
So I have to help Pyspark to not struggle (Now I got to know, why it has a lazy Evaluation tagline:D)
Step 3 : Create the dataframe (You have to provide a structured home to load the extract data from db2)
test_df=spark.read.format(“jdbc”). \
.option(“url”,”jdbc:db2://your_db_hostname:Port/DbName:user=username;password=yourPassword;”) \
.option(“dbtable”,”your_table_name”) \
.option(“driver”,”com.ibm.db2.jcc.DB2Driver”) \
.load()
Step 3 : Yes! you can read the data now(Make sure you have permission to see the data from its original owner (DB2 Admin))
— test_df.show()
— test_df.count()
Step 4 : Store your dataframe in csv format(your choice, it can be parquet or any allowed format in pyspark)
test_df.write.csv(“/your/hdfs/location”)
Now, you have extracted and loaded the data in hdfs shell using pyspark.
Now you can do your transformations and load the data in your target db table, in my case it is postgres.
Thank You! Hope it helped you.
Please click on the clap icon and follow for more such content!
