Spark Sql :- With header footer data

HI,

 

In this blog I will going to write a program for Spark Sql

Objective: – need to create dataframe and fire some queries on it, Input data contains header and trail Also data se separated with “|”

 

Approach: – we need to remove the head and trail first and then after we will create dataframe

 

Need to add maven repository ( please find how to add maven repository in my previous Spark blog)

 

Group id: – org.apache.spark

Artifact id: – spark-sql_2.11

Version: – 1.6.0

 

Here is program:-

import org.apache.spark._

import org.apache.spark.sql.SQLContext

import org.apache.spark.sql._

 

 

object testsql extends App {

 

System.setProperty(“hadoop.home.dir”, “c://winutil//”)

val conf=new SparkConf().setMaster(“local[2]”).setAppName(“testfilter”)

val sc = new SparkContext(conf)

val sqlcontext = new SQLContext(sc);

import sqlcontext.implicits._

val rdd1 = sc.textFile(“file:///D://sparkprog//DataDrame//*”) //import data file

val rddd2=rdd1

val itemscount= rdd1.count()

val headtail= rddd2.take(itemscount.toInt).drop(1).dropRight(1) //drop header and footer

val rddht=sc.parallelize(headtail)  // convert Array to RDD

val sqlrdd=rddht.map { x => x.split(“\\|”) }   // split rdd by “|”

val schemaString=”inp_iMSi inp_RECORD_OPENiNG_TiME inp_iCR_OPERATOR_NAME inp_CALL_EVENT_DURATiON inp_CAUSE_FOR_TERMiNATiON inp_ACCESS_POiNT_NAME_Ni inp_DOWNLiNK_VOLUME inp_UPLiNK_VOLUME inp_SGSN_Address inp_GGSN_Address inp_CHARGiNG_iD inp_iMEi inp_Access_Point_Name_Oi inp_NETWORK_iD inp_PHONE_NUMBER inp_LAC inp_CDR_TYPE inp_PDP_TYPE inp_SERVED_PDP_ADDRESS inp_QOS_REQUESTED inp_QOS_NEGOTiATED inp_APN_SELECTiON_MODE inp_PREPAiD_iNDiCATOR inp_iNDiCATOR_3G_2G inp_PARTiAL_CDR inp_USAGE_TYPE inp_TiME_OF_FiRST_USAGE inp_TiME_OF_LAST_USAGE inp_RATiNG_GROUP_iD inp_LOCAL_SEQUENCE_NO inp_TiME_USAGE inp_CELL_iDENTiFiER inp_ROAMiNG_iCR_iNDiCATOR inp_TOTAL_VOLUME inp_BUFFER1 inp_BUFFER2 inp_BUFFER3 inp_Service_Containers inp_FiLE_NAME LOAD_DATE_input”

import org.apache.spark.sql.Row;

import org.apache.spark.sql.types.{StructType,StructField,StringType};

val schema=StructType(schemaString.split(” “).map(fieldName => StructField(fieldName, StringType, true)))

val row=(sqlrdd.map(p=>Row(p(0), p(1), p(2), p(3), p(4), p(5), p(6), p(7), p(8), p(9), p(10), p(11), p(12), p(13), p(14), p(15), p(16), p(17), p(18), p(19), p(20), p(21), p(22), p(23), p(24), p(25), p(26), p(27), p(28), p(29), p(30), p(31), p(32), p(32), p(34), p(35), p(36), p(37), p(38))))

val testDataFrame = sqlcontext.createDataFrame(row, schema)

testDataFrame.registerTempTable(“tabletest”)

val results = sqlcontext.sql(“SELECT inp_iMSi,inp_RECORD_OPENiNG_TiME  from tabletest”)

for(i <-results)

{

print(“inp_iMSi: ” + i.get(0) + “inp_RECORD_OPENiNG_TiME: ” + i.get(1) )

print(“\n”)

 

}

 

 

}

 

 

Input Data :-

 

HDR|20150824174542    |17     |FSS-MEDIATION   |5.0          |0         |0         |0                                         |

404490450995710      |20150824165503|          |1787      |0   |belgprs.com                                                    |1740      |1680      |223.224.66.73        |122.170.1.149        |2883078739               |                     |belgprs.com                                                    |08|8897766332     |0       |MOC|121 |10.205.14.181  |                              |0                             |0 |00|01        |300     |1002901   |20150818112502|20150824170950|105                      |92486219                 |1787    |0       |0 |3420      |                         |                         |40449                    |                                                  |10_AP_COL_SASN_20150824174542_890272.dat        ||

404490585066516      |20150824165644|          |1687      |0   |belgprs.com                                                    |17672     |10160     |223.224.66.66        |122.170.1.146        |3281337270               |                     |belgprs.com                                                    |08|9550327843     |0       |MOC|121 |10.205.14.237  |                              |0                             |0 |00|01        |4       |1002901   |20150824152644|20150824170951|105                      |92486220                 |1687    |0       |0 |27832     |                         |                         |40449                    |                                                  |10_AP_COL_SASN_20150824174542_890272.dat        ||

404490487032794      |20150824165658|          |1673      |0   |belgprs.com                                                    |1500      |1500      |223.224.66.73        |122.170.1.150        |2850570573               |                     |belgprs.com                                                    |08|9000632146     |0       |MOC|121 |10.205.14.13   |                              |0                             |0 |00|01        |319     |1002901   |20150818015657|20150824170951|105                      |92486221                 |1673    |0       |0 |3000      |                         |                         |40449                    |                                                  |10_AP_COL_SASN_20150824174542_890272.dat        ||

404490450995914      |20150824165502|          |1790      |0   |belgprs.com                                                    |1740      |1740      |223.224.66.73        |122.170.1.149        |2927160379               |                     |belgprs.com                                                    |08|8897788115     |0       |MOC|121 |10.205.14.165  |                              |0                             |0 |00|01        |97      |1002901   |20150822165501|20150824170951|105                      |92486222                 |1790    |0       |0 |3480      |                         |                         |40449                    |                                                  |10_AP_COL_SASN_20150824174542_890272.dat        ||

404490585066532      |20150824171414|          |639       |0   |belgprs.com                                                    |0         |0         |223.224.66.72        |122.170.1.148        |2934489579               |                     |belgprs.com                                                    |08|9550321494     |0       |MOC|121 |10.205.14.213  |                              |0                             |0 |00|01        |61      |1002901   |20150823111414|20150824172453|105                      |92486226                 |639     |0       |0 |0         |                         |                         |40449                    |                                                  |10_AP_COL_SASN_20150824174542_890272.dat        ||

404490487032918      |20150824165503|          |1800      |17  |belgprs.com                                                    |2348      |2348      |223.224.66.73        |122.170.1.150        |3210214286               |                     |belgprs.com                                                    |08|9000629854     |0       |MOC|121 |10.205.14.37   |                              |0                             |0 |00|01        |300     |1002901   |20150818112502|20150824165503|105                      |92486230                 |1800    |0       |0 |4696      |                         |                         |40449                    |                                                  |10_AP_COL_SASN_20150824174542_890272.dat        ||

404490457337617      |20150824165503|          |1800      |17  |belgprs.com                                                    |2272      |2272      |223.224.66.73        |122.170.1.148        |3019724196               |                     |belgprs.com                                                    |08|9000621920     |0       |MOC|121 |10.205.14.125  |                              |0                             |0 |00|01        |300     |1002901   |20150818112502|20150824165503|105                      |92486231                 |1800    |0       |0 |4544      |                         |                         |40449                    |                                                  |10_AP_COL_SASN_20150824174542_890272.dat        ||

404490487032635      |20150824165503|          |1800      |17  |belgprs.com                                                    |2196      |2196      |223.224.66.73        |122.170.1.148        |2909002186               |                     |belgprs.com                                                    |08|9000627858     |0       |MOC|121 |10.205.14.61   |                              |0                             |0 |00|01        |300     |1002901   |20150818112502|20150824165503|105                      |92486232                 |1800    |0       |0 |4392      |                         |                         |40449                    |                                                  |10_AP_COL_SASN_20150824174542_890272.dat        ||

404490487032568      |20150824165503|          |1800      |17  |belgprs.com                                                    |2196      |2196      |223.224.66.73        |122.170.1.146        |2869516217               |                     |belgprs.com                                                    |08|9000624408     |0       |MOC|121 |10.205.14.101  |                              |0                             |0 |00|01        |300     |1002901   |20150818112502|20150824165503|105                      |92486233                 |1800    |0       |0 |4392      |                         |                         |40449                    |                                                  |10_AP_COL_SASN_20150824174542_890272.dat        ||

404490450995906      |20150824165503|          |1800      |17  |belgprs.com                                                    |1820      |1764      |223.224.66.73        |122.170.1.146        |2993469592               |                     |belgprs.com                                                    |08|8897779439     |0       |MOC|121 |10.205.14.157  |                              |0                             |0 |00|01        |300     |1002901   |20150818112502|20150824165503|105                      |92486234                 |1800    |0       |0 |3584      |                         |                         |40449                    |                                                  |10_AP_COL_SASN_20150824174542_890272.dat        ||

404490485983997      |20150824165613|          |1800      |17  |belgprs.com                                                    |1620      |1620      |223.224.66.73        |122.170.1.149        |3206489614               |                     |belgprs.com                                                    |08|9000620872     |0       |MOC|121 |10.205.14.141  |                              |0                             |0 |00|01        |319     |1002901   |20150818015612|20150824165613|105                      |92486239                 |1800    |0       |0 |3240      |                         |                         |40449                    |                                                  |10_AP_COL_SASN_20150824174542_890272.dat        ||

404490457337615      |20150824165630|          |1800      |17  |belgprs.com                                                    |2016      |2016      |223.224.41.124       |122.170.1.148        |2850565933               |                     |belgprs.com                                                    |08|9000629012     |0       |MOC|121 |10.205.14.45   |                              |0                             |0 |00|01        |319     |1002901   |20150818015629|20150824165630|105                      |92486241                 |1800    |0       |0 |4032      |                         |                         |40449                    |                                                  |10_AP_COL_SASN_20150824174542_890272.dat        ||

404490487032786      |20150824172028|          |382       |0   |belgprs.com                                                    |652       |457       |223.224.66.72        |122.170.1.147        |2919947597               |                     |belgprs.com                                                    |08|9000632802     |0       |MOC|121 |10.205.14.5    |                              |0                             |0 |00|01        |2       |1002901   |20150824165028|20150824172626|105                      |92486245                 |382     |0       |0 |1109      |                         |                         |40449                    |                                                  |10_AP_COL_SASN_20150824174542_890272.dat        ||

404490457337573      |20150824165859|          |1800      |17  |belgprs.com                                                    |2440      |2188      |223.224.66.72        |122.170.1.150        |2937958323               |                     |belgprs.com                                                    |08|9000625719     |0       |MOC|121 |10.205.14.85   |                              |0                             |0 |00|01        |49      |1002901   |20150823165859|20150824165859|105                      |92486266                 |1800    |0       |0 |4628      |                         |                         |40449                    |                                                  |10_AP_COL_SASN_20150824174542_890272.dat        ||

404490457337602      |20150824165948|          |1800      |17  |belgprs.com                                                    |1200      |1200      |223.224.66.73        |122.170.1.147        |2990488424               |                     |belgprs.com                                                    |08|8897776859     |0       |MOC|121 |10.205.14.173  |                              |0                             |0 |00|01        |318     |1002901   |20150818022947|20150824165948|105                      |92486275                 |1800    |0       |0 |2400      |                         |                         |40449                    |                                                  |10_AP_COL_SASN_20150824174542_890272.dat        ||

404490485983997      |20150824172613|          |223       |0   |belgprs.com                                                    |0         |0         |223.224.66.73        |122.170.1.149        |3206489614               |                     |belgprs.com                                                    |08|9000620872     |0       |MOC|121 |10.205.14.141  |                              |0                             |0 |00|01        |320     |1002901   |20150818015612|20150824172956|105                      |92486277                 |223     |0       |0 |0         |                         |                         |40449                    |                                                  |10_AP_COL_SASN_20150824174542_890272.dat        ||

TRL|20150824174542    |17     |FSS-MEDIATION   |5.0          |0         |0         |0                                         |

 

 

Note: – please copy paste input data into notepad( uncheck wordwrap) or open into notepad++

 

Output:-

inp_iMSi: 404490487032568      inp_RECORD_OPENiNG_TiME: 20150824165503

inp_iMSi: 404490450995906      inp_RECORD_OPENiNG_TiME: 20150824165503

inp_iMSi: 404490485983997      inp_RECORD_OPENiNG_TiME: 20150824165613

inp_iMSi: 404490457337615      inp_RECORD_OPENiNG_TiME: 20150824165630

inp_iMSi: 404490487032786      inp_RECORD_OPENiNG_TiME: 20150824172028

inp_iMSi: 404490457337573      inp_RECORD_OPENiNG_TiME: 20150824165859

inp_iMSi: 404490457337602      inp_RECORD_OPENiNG_TiME: 20150824165948

inp_iMSi: 404490485983997      inp_RECORD_OPENiNG_TiME: 20150824172613

inp_iMSi: 404490450995710      inp_RECORD_OPENiNG_TiME: 20150824165503

inp_iMSi: 404490585066516      inp_RECORD_OPENiNG_TiME: 20150824165644

inp_iMSi: 404490487032794      inp_RECORD_OPENiNG_TiME: 20150824165658

inp_iMSi: 404490450995914      inp_RECORD_OPENiNG_TiME: 20150824165502

inp_iMSi: 404490585066532      inp_RECORD_OPENiNG_TiME: 20150824171414

inp_iMSi: 404490487032918      inp_RECORD_OPENiNG_TiME: 20150824165503

inp_iMSi: 404490457337617      inp_RECORD_OPENiNG_TiME: 20150824165503

inp_iMSi: 404490487032635      inp_RECORD_OPENiNG_TiME: 20150824165503

 

 

Please like if you like the post or comments

thanks and enjoy

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s