Cloudera Cloudera Certified Associate CCA CCA175 Questions & Answers

• Question 91:

  Problem Scenario 82 : You have been given table in Hive with following structure (Which you have created

  in previous exercise).

  productid int code string name string quantity int price float

  Using SparkSQL accomplish following activities.

  1.

  Select all the products name and quantity having quantity <= 2000

  2.

  Select name and price of the product having code as 'PEN'

  3.

  Select all the products, which name starts with PENCIL

  4.

  Select all products which "name" begins with 'P\ followed by any two characters, followed by space, followed by zero or more characters

  Correct Answer: See the explanation for Step by Step Solution and configuration.

  Solution :

  Step 1 : Copy following tile (Mandatory Step in Cloudera QuickVM) if you have not done it.

  sudo su root

  cp /usr/lib/hive/conf/hive-site.xml /usr/lib/sparkVconf/

  Step 2 : Now start spark-shell

  Step 3 ; Select all the products name and quantity having quantity <= 2000

  val results = sqlContext.sql(......SELECT name, quantity FROM products WHERE quantity

  <= 2000......)

  results.showQ

  Step 4 : Select name and price of the product having code as 'PEN'

  val results = sqlContext.sql(......SELECT name, price FROM products WHERE code =

  'PEN.......)

  results. showQ

  Step 5 : Select all the products , which name starts with PENCIL

  val results = sqlContext.sql(......SELECT name, price FROM products WHERE

  upper(name) LIKE 'PENCIL%.......}

  results. showQ

  Step 6 : select all products which "name" begins with 'P', followed by any two characters,

  followed by space, followed byzero or more characters

  -- "name" begins with 'P', followed by any two characters,

  -followed by space, followed by zero or more characters

  val results = sqlContext.sql(......SELECT name, price FROM products WHERE name LIKE

  'P_ %.......)

  results. show()

• Question 92:

  Problem Scenario 67 : You have been given below code snippet.

  lines = sc.parallelize(['lts fun to have fun,','but you have to know how.'])

  M = lines.map( lambda x: x.replace(',7 ').replace('.',' 'J.replaceC-V ').lower())

  r2 = r1.flatMap(lambda x: x.split())

  r3 = r2.map(lambda x: (x, 1))

  operation1

  r5 = r4.map(lambda x:(x[1],x[0]))

  r6 = r5.sortByKey(ascending=False)

  r6.take(20)

  Write a correct code snippet for operationl which will produce desired output, shown below.

  [(2, 'fun'), (2, 'to'), (2, 'have'), (1, its'), (1, 'know1), (1, 'how1), (1, 'you'), (1, 'but')]

  Correct Answer: See the explanation for Step by Step Solution and configuration.

  Solution : r4 = r3.reduceByKey(lambda x,y:x+y)

• Question 93:

  Problem Scenario 5 : You have been given following mysql database details.

  user=retail_dba password=cloudera database=retail_db jdbc URL = jdbc:mysql://quickstart:3306/retail_db Please accomplish following activities.

  1.

  List all the tables using sqoop command from retail_db

  2.

  Write simple sqoop eval command to check whether you have permission to read database tables or not.

  3.

  Import all the tables as avro files in /user/hive/warehouse/retail cca174.db

  4.

  Import departments table as a text file in /user/cloudera/departments.

  Correct Answer: See the explanation for Step by Step Solution and configuration.

  Solution:

  Step 1 : List tables using sqoop

  sqoop list-tables --connect jdbc:mysql://quickstart:330G/retail_db --username retail dba password cloudera

  Step 2 : Eval command, just run a count query on one of the table.

  sqoop eval \

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

  -username retail_dba \

  -password cloudera \

  --query "select count(1) from ordeMtems"

  Step 3 : Import all the tables as avro file.

  sqoop import-all-tables \

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

  -username=retail_dba \

  -password=cloudera \

  -as-avrodatafile \

  -warehouse-dir=/user/hive/warehouse/retail stage.db \

  -ml

  Step 4 : Import departments table as a text file in /user/cloudera/departments

  sqoop import \

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

  -username=retail_dba \

  -password=cloudera \

  -table departments \

  -as-textfile \

  -target-dir=/user/cloudera/departments

  Step 5 : Verify the imported data.

  hdfs dfs -Is /user/cloudera/departments

  hdfs dfs -Is /user/hive/warehouse/retailstage.db

  hdfs dfs -Is /user/hive/warehouse/retail_stage.db/products

• Question 94:

  Problem Scenario 51 : You have been given below code snippet.

  val a = sc.parallelize(List(1, 2,1, 3), 1)

  val b = a.map((_, "b"))

  val c = a.map((_, "c"))

  Operation_xyz

  Write a correct code snippet for Operationxyz which will produce below output.

  Output:

  Array[(lnt, (lterable[String], lterable[String]))] = Array(

  (2,(ArrayBuffer(b),ArrayBuffer(c))),

  (3,(ArrayBuffer(b),ArrayBuffer(c))),

  (1,(ArrayBuffer(b, b),ArrayBuffer(c, c))) )

  Correct Answer: See the explanation for Step by Step Solution and configuration.

  Solution :

  b.cogroup(c).collect

  cogroup [Pair], groupWith [Pair]

  A very powerful set of functions that allow grouping up to 3 key-value RDDs together using

  their keys.

  Another example

  val x = sc.parallelize(List((1, "apple"), (2, "banana"), (3, "orange"), (4, "kiwi")), 2)

  val y = sc.parallelize(List((5, "computer"), (1, "laptop"), (1, "desktop"), (4, "iPad")), 2)

  x.cogroup(y).collect

  Array[(lnt, (lterable[String], lterable[String]))] = Array(

  (4,(ArrayBuffer(kiwi),ArrayBuffer(iPad))),

  (2,(ArrayBuffer(banana),ArrayBuffer())),

  (3,(ArrayBuffer(orange),ArrayBuffer())),

  (1 ,(ArrayBuffer(apple),ArrayBuffer(laptop, desktop))),

  (5,{ArrayBuffer(),ArrayBuffer(computer))))

• Question 95:

  Problem Scenario 55 : You have been given below code snippet.

  val pairRDDI = sc.parallelize(List( ("cat",2), ("cat", 5), ("book", 4),("cat", 12))) val

  pairRDD2 = sc.parallelize(List( ("cat",2), ("cup", 5), ("mouse", 4),("cat", 12)))

  operation1

  Write a correct code snippet for operationl which will produce desired output, shown below.

  Array[(String, (Option[lnt], Option[lnt]))] = Array((book,(Some(4},None)),

  (mouse,(None,Some(4))), (cup,(None,Some(5))), (cat,(Some(2),Some(2)),

  (cat,(Some(2),Some(12))), (cat,(Some(5),Some(2))), (cat,(Some(5),Some(12))),

  (cat,(Some(12),Some(2))), (cat,(Some(12),Some(12)))J

  Correct Answer: See the explanation for Step by Step Solution and configuration.

  Solution : pairRDD1.fullOuterJoin(pairRDD2).collect

  fullOuterJoin [Pair]

  Performs the full outer join between two paired RDDs.

  Listing Variants

  def fullOuterJoin[W](other: RDD[(K, W)], numPartitions: Int): RDD[(K, (Option[V],

  OptionfW]))]

  def fullOuterJoin[W](other: RDD[(K, W}]}: RDD[(K, (Option[V], OptionfW]))]

  def fullOuterJoin[W](other: RDD[(K, W)], partitioner: Partitioner): RDD[(K, (Option[V],

  Option[W]))]