Solution :

Step 1 : Import Single table .

sqoop import --connect jdbc:mysql://quickstart:3306/retail_db -username=retail_dba -password=cloudera -table=order_items --target -dir=p90 ordeMtems --m 1

Note : Please check you dont have space between before or after ' = ' sign. Sqoop uses the MapReduce framework to copy data from RDBMS to hdfs

Step 2 : Read the data from one of the partition, created using above command. hadoop fs -cat p90_order_items/part-m-00000

Step 3 : In pyspark, get the total revenue across all days and orders. entire TableRDD = sc.textFile( " p90_order_items " )

#Cast string to float

extractedRevenueColumn = entireTableRDD.map(lambda line: float(line.split( " , " )[4]))

Step 4 : Verify extracted data

for revenue in extractedRevenueColumn.collect():

print revenue

#use reduce ' function to sum a single column vale

totalRevenue = extractedRevenueColumn.reduce(lambda a, b: a + b)

Step 5 : Calculate the maximum revenue

maximumRevenue = extractedRevenueColumn.reduce(lambda a, b: (a if a > =b else b))

Step 6 : Calculate the minimum revenue

minimumRevenue = extractedRevenueColumn.reduce(lambda a, b: (a if a < =b else b))

Step 7 : Caclculate average revenue

count=extractedRevenueColumn.count()

averageRev=totalRevenue/count

Solution :

b.subtractByKey(d).collect

subtractByKey [Pair] : Very similar to subtract, but instead of supplying a function, the key-component of each pair will be automatically used as criterion for removing items from the first RDD.

Solution :

Step 1 : Connect to mysql database.

mysql --user=retail_dba -password=cloudera

show databases; use retail_db; show tables;

Insert record

Insert into departments values(9999, ' " Data Science " ' );

select " from departments;

Step 2 : Import data as per requirement.

sqoop import \

-connect jdbc:mysql;//quickstart:3306/retail_db \

~username=retail_dba \

--password=cloudera \

-table departments \

-target-dir /user/cloudera/departments_enclosedby \

-enclosed-by V -escaped-by \\ -fields-terminated-by-- ' -lines-terminated-by :

Step 3 : Check the result.

hdfs dfs -cat/user/cloudera/departments_enclosedby/part "

Solution :

Step 1 : Create directory

hdfs dfs -mkdir hdfs_commands

Step 2 : Create a file in hdfs named data.txt in hdfs_commands. hdfs dfs -touchz hdfs_commands/data.txt

Step 3 : Now copy this data.txt file on local filesystem, however while copying file please make sure file properties are not changed e.g. file permissions.

hdfs dfs -copyToLocal -p hdfs_commands/data.txt/home/cloudera/Desktop/HadoopExam

Step 4 : Now create a file in local directory named data_local.txt and move this file to hdfs in hdfs_commands directory.

touch data_local.txt

hdfs dfs -moveFromLocal /home/cloudera/Desktop/HadoopExam/dataJocal.txt hdfs_commands/

Step 5 : Create a file data_hdfs.txt in hdfs_commands directory and copy it to local file system.

hdfs dfs -touchz hdfscommands/data hdfs.txt

hdfs dfs -getfrdfs_commands/data_hdfs.txt /home/cloudera/Desktop/HadoopExam/

Step 6 : Create a file in local filesystem named filel .txt and put it to hdfs

touch filel.txt

hdfs dfs -put/home/cloudera/Desktop/HadoopExam/file1.txt hdfs_commands/

Solution :

Step 1 : Select all the records with quantity > = 5000 and name starts with ' Pen '

val results = sqlContext.sql(......SELECT * FROM products WHERE quantity > = 5000 AND name LIKE ' Pen %.......)

results.show()

Step 2 : Select all the records with quantity > = 5000 , price is less than 1.24 and name starts with ' Pen '

val results = sqlContext.sql(......SELECT * FROM products WHERE quantity > = 5000 AND price < 1.24 AND name LIKE ' Pen %.......)

results. showQ

Step 3 : Select all the records witch does not have quantity > = 5000 and name does not starts with ' Pen '

val results = sqlContext.sql( ' .....SELECT * FROM products WHERE NOT (quantity > = 5000 AND name LIKE ' Pen % ' )......)

results. showQ

Step 4 : Select all the products wchich name is ' Pen Red ' , ' Pen Black '

val results = sqlContext.sql( ' .....SELECT ' FROM products WHERE name IN ( ' Pen Red ' , ' Pen Black ' )......)

results. showQ

Step 5 : Select all the products which has price BETWEEN 1.0 AND 2.0 AND quantity BETWEEN 1000 AND 2000.

val results = sqlContext.sql(......SELECT * FROM products WHERE (price BETWEEN 1.0 AND 2.0) AND (quantity BETWEEN 1000 AND 2000)......)

results. show()

Solution :

Step 1 : Create all three tiles in hdfs (We will do using Hue}. However, you can first create in local filesystem and then upload it to hdfs.

Step 2 : The first problem is that values in each partition of our initial RDD describe lines from the file rather than sentences. Sentences may be split over multiple lines.

The glom() RDD method is used to create a single entry for each document containing the list of all lines, we can then join the lines up, then resplit them into sentences using " . " as the separator, using flatMap so that every object in our RDD is now a sentence.

sentences = sc.textFile( " spark75/file1.txt " ) \ .glom() \

map(lambda x: " " .join(x)) \ .flatMap(lambda x: x.spllt( " . " ))

Step 3 : Now we have isolated each sentence we can split it into a list of words and extract the word bigrams from it. Our new RDD contains tuples

containing the word bigram (itself a tuple containing the first and second word) as the first value and the number 1 as the second value. bigrams = sentences.map(lambda x:x.split()) \ .flatMap(lambda x: [((x[i],x[i+1] ),1)for i in range(0,len(x)-1)])

Step 4 : Finally we can apply the same reduceByKey and sort steps that we used in the wordcount example, to count up the bigrams and sort them in order of descending frequency. In reduceByKey the key is not an individual word but a bigram.

freq_bigrams = bigrams.reduceByKey(lambda x,y:x+y)\

map(lambda x:(x[1],x[0] )) \

sortByKey(False)

freq_bigrams.take(10)

Solution :

b.mapValuesf ' x " + _ + " x " ).collect

mapValues [Pair] : Takes the values of a RDD that consists of two-component tuples, and applies the provided function to transform each value . Tlien,.it.forms newtwo-componend tuples using the key and the transformed value and stores them in a new RDD.

Solution :

Step 1 : Create all three files in hdfs (We will do using Hue). However, you can first create in local filesystem and then upload it to hdfs.

Step 2 : Load EmployeeName.csv file from hdfs and create PairRDDs

val name = sc.textFile( " spark5/EmployeeName.csv " )

val namePairRDD = name.map(x= > (x.split( " , " )(0),x.split( ' V ' )(1)))

Step 3 : Load EmployeeSalary.csv file from hdfs and create PairRDDs

val salary = sc.textFile( " spark5/EmployeeSalary.csv " )

val salaryPairRDD = salary.map(x= > (x.split( " , " )(0),x.split( " , " )(1)))

Step 4 : Join all pairRDDS

val joined = namePairRDD.join(salaryPairRDD}

Step 5 : Remove key from RDD and Salary as a Key. val keyRemoved = joined.values

Step 6 : Now swap filtered RDD.

val swapped = keyRemoved.map(item = > item.swap)

Step 7 : Now groupBy keys (It will generate key and value array) val grpByKey = swapped.groupByKey().collect()

Step 8 : Now create RDD for values collection

val rddByKey = grpByKey.map{case (k,v) = > k- > sc.makeRDD(v.toSeq)}

Step 9 : Save the output as a Text file.

rddByKey.foreach{ case (k,rdd) = > rdd.saveAsTextFile( " spark5/Employee " +k)}