Cloudera Exam CCA175 Topic 4 Question 58 Discussion

Actual exam question for Cloudera's CCA175 exam

Question #: 58
Topic #: 4

[All CCA175 Questions]

Problem Scenario 90 : You have been given below two files

course.txt

id,course

1,Hadoop

2,Spark

3,HBase

fee.txt

id,fee

2,3900

3,4200

4,2900

Accomplish the following activities.

1. Select all the courses and their fees , whether fee is listed or not.

2. Select all the available fees and respective course. If course does not exists still list the fee

3. Select all the courses and their fees , whether fee is listed or not. However, ignore records having fee as null.

ASolution :
Step 1:
hdfs dfs -mkdir sparksql4
hdfs dfs -put course.txt sparksql4/
hdfs dfs -put fee.txt sparksql4/
Step 2 : Now in spark shell
// load the data into a new RDD
val course = sc.textFile('sparksql4/course.txt')
val fee = sc.textFile('sparksql4/fee.txt')
// Return the first element in this RDD
course.fi rst()
fee.fi rst()
//define the schema using a case class case class Course(id: Integer, name: String) case class Fee(id: Integer, fee: Integer)
// create an RDD of Product objects
val courseRDD = course.map(_.split(',')).map(c => Course(c(0).tolnt,c(1)))
val feeRDD =fee.map(_.split(',')).map(c => Fee(c(0}.tolnt,c(1}.tolnt))
courseRDD.first()
courseRDD.count(}
feeRDD.first()
feeRDD.countQ
// change RDD of Product objects to a DataFrame val courseDF = courseRDD.toDF(} val feeDF = feeRDD.toDF{)
// register the DataFrame as a temp table courseDF. registerTempTable('course') feeDF. registerTempTablef'fee')
// Select data from table
val results = sqlContext.sql(......SELECT' FROM course ''' )
results. showQ
val results = sqlContext.sql(......SELECT' FROM fee......)
results. showQ
val results = sqlContext.sql(......SELECT * FROM course LEFT JOIN fee ON course.id = fee.id......)
results-showQ
val results ='sqlContext.sql(......SELECT * FROM course RIGHT JOIN fee ON course.id = fee.id 'MM )
results. showQ
val results = sqlContext.sql(......SELECT' FROM course LEFT JOIN fee ON course.id = fee.id where fee.id IS NULL'
results. show()

BSolution :
Step 1:
hdfs dfs -mkdir sparksql4
hdfs dfs -put course.txt sparksql4/
hdfs dfs -put fee.txt sparksql4/
Step 2 : Now in spark shell
// load the data into a new RDD
val course = sc.textFile('sparksql4/course.txt')
val fee = sc.textFile('sparksql4/fee.txt')
// Return the first element in this RDD
course.fi rst()
fee.fi rst()
//define the schema using a case class case class Course(id: Integer, name: String) case class Fee(id: Integer, fee: Integer)
// create an RDD of Product objects
val courseRDD = course.map(_.split(',')).map(c => Course(c(0).tolnt,c(1)))
val feeRDD =fee.map(_.split(',')).map(c => Fee(c(0}.tolnt,c(1}.tolnt))
courseRDD.first()
courseRDD.count(}
feeRDD.first()
results-showQ
val results ='sqlContext.sql(......SELECT * FROM course RIGHT JOIN fee ON course.id = fee.id 'MM )
results. showQ
val results = sqlContext.sql(......SELECT' FROM course LEFT JOIN fee ON course.id = fee.id where fee.id IS NULL'
results. show()

Show Suggested Answer

Suggested Answer: A

by Natalie at Apr 25, 2024, 09:49 AM

Limited Time Offer

25%

7 days ago

I have completed Step 1.

upvoted 0 times

...