Hive Interview Questions : Hive Lateral View Keyword Use

Question:- Consider a scenario , we have table in hive containing one column as INT and one column as ARRAY . Display all values as one on one mapping/ horizontally.

Solution:

1. Below is the data set to explain the example. '\t' is the field delimiter and Control+B is the collection items delimiter.

2.  Create a managed table in the database using below query on the hive shell:-

hive > CREATE TABLE arrays(emp_id INT, dep_id ARRAY<STRING>, address_id ARRAY<STRING>) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY  '\002'; 

3.  Load data in to table using the below query:-

hive > LOAD DATA LOCAL INPATH '/<pathtofile>/array.txt' overwrite INTO TABLE arrays;

4.  To check the data has been loaded successfully run below query:-

hive > select * from arrays;

Below output will be produced-

So far we have successfully created our data. Now before moving forward explanation of Lateral View keyword is required.

Lateral view is used with user-defined table generating functions such as explode(). explode() is built in table generate function that produces multiple rows for one row as input.

Use Case 1: Query to print department id horizontally with employee id

hive > select emp_id,dep_id from arrays LATERAL VIEW EXPLODE(dep_id) arrays as dep_id;

output:-

Use Case 2: Query to print multiple columns horizontally with employee id

hive > select emp_id,dep_id,address_id from arrays LATERAL VIEW EXPLODE(dep_id) arrays as dep_id LATERAL VIEW EXPLODE(address_id) arrays as address_id;

output:-

Use Case 3: As you can see that row with emp_id 4 is eliminated in output produced this is because that was having null in address_id. To get that row as well we need to use LATERAL View with OUTER.

hive > select emp_id,dep_id,address_id from arrays LATERAL VIEW EXPLODE(dep_id) arrays as dep_id LATERAL VIEW OUTER EXPLODE(address_id) arrays as address_id;

output:- As you can see that employee id with 4 is coming as well. 

Input file is attached with the post.

array.txt

I hope you like my explanation. Please leave a comment if you like it.

Hadoop Errors

List of Hadoop Errors

A :

 Incompatible clusterIDs in Hadoop :  Some time you get below error in your logs , in this case you will not find datanode as running instance.

Error Stack - Incompatible clusterIDs in /usr/local/hadoop_store/hdfs/datanode: namenode clusterID = CID-52c3b330-d913-461d-8a5a-03c1659390bd; datanode clusterID = CID-bfea0588-2680-43cb-aa69-d5464168ca88
    at org.apache.hadoop.hdfs.server.datanode.DataStorage.doTransition(DataStorage.java:646)
    at org.apache.hadoop.hdfs.server.datanode.DataStorage.addStorageLocations(DataStorage.java:320)

                                                           Fix : 

ClusterIDs of datanodes and namenodes should match, then only can datanodes communicate with namenode. 

When first time you run your hadoop instance (using start-all.sh or other way) you can see datanode is in running state, but after that if you format your name node then  new ClusterID will be assigned for namenodes  and ClusterIDs in datanodes won't match.

You can locate a VERSION files in your /usr/local/hadoop_store/hdfs/datanode/current/ (datanode directory ).

You can fix above issue by two way

1)-  If you dont want to delete data of datanode then go to  /usr/local/hadoop_store/hdfs/datanode/current/  open version file and update clusterID value with updated one (as mention in error trace).

2)- If you are not ok with above apporoach you can execute below commands to generate new clusterId.

   a)- Stop all HDFS services.

   b)- rm -rf /usr/local/hadoop_store/hdfs/datanode/* 

   c)- rm -rf /usr/local/hadoop_store/hdfs/namenode/*

   d)-format hdfs again (hadoop namenode -format )

Hive Errors

List Of Errors

A : Exception in using UDF
 Some time may you face below issue while using customizes UDF in hive
java.io.FileNotFoundException: File does not exist: hdfs:

 

here is complete stack trace

java.io.FileNotFoundException: File does not exist: hdfs://localhost:54310/usr/local/hivetmp/amit.pathak/9381feb3-6c5f-469b-b6b1-9af55abbdabd/udf.jar
at org.apache.hadoop.hdfs.DistributedFileSystem$18.doCall(DistributedFileSystem.java:1122)
at org.apache.hadoop.hdfs.DistributedFileSystem$18.doCall(DistributedFileSystem.java:1114)
at org.apache.hadoop.fs.FileSystemLinkResolver.resolve(FileSystemLinkResolver.java:81)
at org.apache.hadoop.hdfs.DistributedFileSystem.getFileStatus(DistributedFileSystem.java:1114)
at org.apache.hadoop.mapreduce.filecache.ClientDistributedCacheManager.getFileStatus(ClientDistributedCacheManager.java:288)

If you want to view exact problem you can check this issueLink

This issue comes mainly when you  use UDF in join or Create table tablename as function.

To fix above issue I have two ways.

1) - Use add file command instead of add jar (As using file it make sure your data exist in distributed cache )

Before Changes ::

add jar '/user/hive/udf.jar';

create temporary function convertToJulian as 'com.convertToJulian';

After Changes ::

add file '/user/hive/udf.jar';

create temporary function convertToJulian as 'com.convertToJulian';

2)- Have same file structure on local as well as on hadoop.

Like if you stored your UDF in below local file system

/user/hive/amit/udf.jar

So you also need to create same directory structure in hadoop filesystem and then put your udf jar in that directory.

B :   

- Exception in thread "main" java.lang.RuntimeException: java.lang.IllegalArgumentException: java.net.URISyntaxException: Relative path in absolute URI: ${system:java.io.tmpdir%7D/$%7Bsystem:user.name%7D

                                        Fix :

1)- execute command -> sudo nano hive-site.xml

2)- update below highlighted 3 property value with your created directory

<property>

<name>hive.exec.scratchdir</name>

    <value>/usr/local/hive_data</value>

    <description>HDFS root scratch dir for Hive jobs which gets created with write all (733) permission. For each connecting user, an HDFS scratch dir: ${hive.exec.scratchdir}/&lt;username&gt; is created,$

</property>

<property>

    <name>hive.exec.local.scratchdir</name>

    <value>/usr/local/hive_data</value>

    <description>Local scratch space for Hive jobs</description>

</property>

<property>

    <name>hive.downloaded.resources.dir</name>

    <value>/usr/local/hive_data/${hive.session.id}_resources</value>

    <description>Temporary local directory for added resources in the remote file system.</description>

</property> 

C :

 java.lang.IncompatibleClassChangeError: Found class jline.Terminal, but interface was expected .

                                          Fix : 

  a)- Open profile file(or any file where you stored path info of hive) -> sudo nano /etc/profile

  b)- add line at bottom of file -> export HADOOP_USER_CLASSPATH_FIRST=true

  c)- execute command -> source /etc/profile

  d)- type hive and hit Enter.

Now it should working fine and you able to see hive pointer. like

hive>

Sqoop Errors

In this post I will try to make collection of common errors which we face in Sqoop.


Error 1 : if you are facing any issue like com.mysql.jdbc.RowDataDynamic@692b53b4

Full Trace :
ERROR manager.SqlManager: Error reading from database: java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@692b53b4 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.

Solution :

Add below line in your import sqoop query
--driver com.mysql.jdbc.Driver \

Like :

sqoop import \
--connect jdbc:mysql://localhost/userdb \
--username hiveuser \
--password hiveuser \
--driver com.mysql.jdbc.Driver \
--table emp --m 1

Error 2:  Import fails due to ClassNotFoundException

Full Trace :

04/12/14 11:25:18 INFO mapred.LocalJobRunner: map task executor complete.
04/12/14 11:25:18 WARN mapred.LocalJobRunner: job_local194018022_0001.
java.lang.Exception: java.lang.RuntimeException:  java.lang.ClassNotFoundException: Class emp not found

Solution :

Add line in your import command
--bindir /usr/local/sqoop \

Like :
sqoop import \
--connect jdbc:mysql://localhost/userdb \
--username hiveuser \
--password hiveuser \
--driver com.mysql.jdbc.Driver \
--bindir /usr/local/sqoop \
--table emp --m 1