Spark to Connect with Azure SQL DB and read Table

Introduction

In this article we are trying to connect spark with Azure SQL DB and just simply read a table.

Hope it will be interesting.

Scala Code

	import com.microsoft.azure.sqldb.spark.bulkcopy.BulkCopyMetadata
	import com.microsoft.azure.sqldb.spark.config.Config
	import com.microsoft.azure.sqldb.spark.connect._
	val url = "[Enter your url here]"
	val databaseName = "[Enter your database name here]"
	val dbTable = "[Enter your database table here]"
	val user = "[Enter your username here]"
	val password = "[Enter your password here]"
	// READ FROM CONFIG
	val readConfig = Config(Map(
	"url"            -> url,
	"databaseName"   -> databaseName,
	"user"           -> user,
	"password"       -> password,
	"connectTimeout" -> "5",
	"queryTimeout"   -> "5",
	"dbTable"        -> dbTable
	))
	val df = sqlContext.read.sqlDB(readConfig)
	println("Total rows: " + df.count)
	df.show()
	// TRADITIONAL SYNTAX
	import java.util.Properties
	val properties = new Properties()
	properties.put("databaseName", databaseName)
	properties.put("user", user)
	properties.put("password", password)
	properties.put("connectTimeout", "5")
	properties.put("queryTimeout", "5")
	val df = sqlContext.read.sqlDB(url, dbTable, properties)
	println("Total rows: " + df.count)
	df.show()

Hope you like it.

Posted by: MR. JOYDEEP DAS

SSIS Folder Traversing in SPARK SQL

Introduction

Here in this article, we are trying to demonstrate Folder Traversing of SSIS ForEach loop container for searching a specified file.

Hope it will be interesting

Scenario

We have a folder named “Sample”. Under this folder, we have three other folder named “Sample-1”, “Sample-2” and “Sample-3”. For each folder there is a flat file named “Student-1.txt”,”Student-2.txt” and “Student-3.txt”.

We need to read the entire file from different folder location

The folder and file structure is displayed by DOS TREE command

Scala Code

//---------------------------------------

// Scala for SPARK to Read Flat File form Different Folder

// Implementation Folder Traversing of SSIS in Spark

// Creation Date: 06/18/2018

//-----------------------------------------

import org.apache.spark.sql.SparkSession

import org.apache.spark.sql.catalyst.encoders.ExpressionEncoder

import org.apache.spark.sql.Encoder

import spark.implicits._

case class Student(roll: Long, name: String)

val employeeDF = spark.sparkContext.textFile("d:/spark/bin/examples/src/main/resources/sample/*/student-*.txt").map(_.split(",")).map(attributes=>Student(attributes(0).trim.toInt, attributes(1).trim)).toDF()

employeeDF.createOrReplaceTempView("student")

val youngstersDF = spark.sql("SELECT roll, name FROM student")

youngstersDF.show

Output

Hope you like it.

Posted By: MR. JOYDEEP DAS

SSIS Conditional Split with SPARK SQL

Introduction

Here in this article we are trying to make SSIS conditional Split Transform by using SPARK SQL. It is called Split Data Frame using Filter Transform.

Hope it will be interesting.

What We Want to Do

We have a Flat File Named Student Marks Details mentioned bellow.

101,Joydeep Das,Math,10

102,Deepasree Das,Math,89

103,Shipra Roy Chowdhury,Math,100

104,Rajesh Roy,Math,45

105,Sunita Tendon,Math,98

106,Amit Basu,Math,20

107,Debalina Bhattacharya,Math,99

108,Pritam Das,Math,40

109,Partha Deb Das,Math,89

110,Onkita Gupta,Math,100

The file contains Student Roll, Student Name, Subject and Marks. Based on Marks we need to display records

If [ Marks ] > 50	Display Records
If [ Marks ] < 50	Display Records

Scala Code

//---------------------------------------

// Scala for SPARK to Read Flat File

// Make Conditional Split Depends on Makes (Marks>50)

// Creation Date: 06/172018

//-----------------------------------------

import org.apache.spark.sql.SparkSession

import org.apache.spark.sql.catalyst.encoders.ExpressionEncoder

import org.apache.spark.sql.Encoder

import spark.implicits._

case class Student(roll: Long, name: String, subject: String, marks: Long)

//Read FLAT File

val studentDF = spark.sparkContext.textFile("d:/spark/bin/examples/src/main/resources/studentmarksdetails.txt").map(_.split(",")).map(attributes => Student(attributes(0).trim.toInt, attributes(1).trim, attributes(2).trim, attributes(3).trim.toInt)).toDF()

studentDF.cache() // recommended to prevent repeating the calculation

val condition = col("marks") > 50 // Condition

val studentDF1 = studentDF.filter(condition)

val studentDF2 = studentDF.filter(not(condition))

//Making View for FLAT file

studentDF1.createOrReplaceTempView("studentrecord1")

studentDF2.createOrReplaceTempView("studentrecord2")

val youngstersDF1 = spark.sql("SELECT roll, name, subject, marks FROM studentrecord1")

youngstersDF1.show

val youngstersDF2 = spark.sql("SELECT roll, name, subject, marks FROM studentrecord2")

youngstersDF2.show

Output

Hope you like it.

Posted by: MR. JOYDEEP DAS

Spark SQL to join Flat File and JSON File

Introduction

In this article we are trying to join a Flat File with a JSON file by using SPARK SQL. So were going to join a structured file with a Semi Structured file.

Hope it will be interesting.

Flat File and JSON file Meta Data

JSON file structure:

{"empid":101, "name":"Michael", "salary":3000}

{"empid":102, "name":"Andy", "salary":4500}

{"empid":103, "name":"Justin", "salary":3500}

{"empid":104, "name":"Berta", "salary":4000}

Flat File Structure:

101,Tripura

102,West Bengal

103,Bihar

Scala Code

//---------------------------------------

// Scala for SPARK to Read JSON File

// Join with FLAT file

// Creation Date: 05/31/2018

//-----------------------------------------

import org.apache.spark.sql.SparkSession

import org.apache.spark.sql.catalyst.encoders.ExpressionEncoder

import org.apache.spark.sql.Encoder

import spark.implicits._

case class Employeestate(empid: Long, state: String)

//Read JSON File

val spark = SparkSession.builder().appName("Spark SQL basic example").config("spark.some.config.option", "some-value").getOrCreate()

import spark.implicits._

val df = spark.read.json("examples/src/main/resources/empsalarydetails.json")

//Making View for JSON file

df.createOrReplaceTempView("employee")

//Read FLAT File

val employeestaeDF = spark.sparkContext.textFile("d:/spark/bin/examples/src/main/resources/employeestate.txt").map(_.split(",")).map(attributes => Employeestate(attributes(0).trim.toInt, attributes(1).trim)).toDF()

//Making View for FLAT file

employeeDF.createOrReplaceTempView("employeestate")

val employeeDF = spark.sql("SELECT employee.empid, employee.name, employee.salary, employeestate.state FROM employee, employeestate WHERE employee.empid=employeestate.empid;")

employeeDF.show

Output

Hope you like it.

Posted By: MR. JOYDEEP DAS