Hadoop Sqoop commands Interview Questions

Our HadoopTpoint App is now available in google play store,please rate and comment it in play store : W3Schools

Hadoop Sqoop commands Interview Questions

Write a command to Import RDBMS data into HDFS ?

root@ubuntu:/home/mahesh/sqoop-related# sqoop import –connect jdbc:mysql://localhost/mahesh –table emp -m 1;

Where the table created for the above command ?

In HDFS, hadoop fs -ls /

How to read the RDBMS table data In HDFS ?

hadoop fs -cat /emp/part-m-00000
111,mahesh,28000
112,neelesh,30000
113,rupesh,26000
114,vijay,28000

What is the Default delimiter between RDBMS table columns ?

coma (,)

How to set target Directory to sqoop ?

root@ubuntu:/home/mahesh/sqoop-related# sqoop import –connect jdbc:mysql://localhost/mahesh –table emp -m 1
–target-dir /ImportData

O/P:-
root@ubuntu:/home/mahesh/sqoop-related# hadoop fs -cat /ImportData/part-m-00000
111 mahesh 28000
112 neelesh 30000
113 rupesh 26000
114 vijay 28000

How to set target directory and delimiter to sqoop ?

root@ubuntu:/home/mahesh/sqoop-related# sqoop import –connect jdbc:mysql://localhost/mahesh –table emp -m 1
–target-dir /ImportData –fields-terminated-by ‘t';

O/P:-
root@ubuntu:/home/mahesh/sqoop-related# hadoop fs -cat /ImportData/part-m-00000
111 mahesh 28000
112 neelesh 30000
113 rupesh 26000
114 vijay 28000

what Indicates -m 1 in above sqoop commands ?

-m 1 indicates output file divided into only 1 file,suppose we write -m 2 that means the output devided into 2 parts of files like part-r-00000 and part-r-00001

What is the use of –append command in sqoop ?

Append command used to add the extra output records for the old directory, there is no need to over right or create a new directory it will appended to old directory

How to select only specific columns In a table using sqoop ?

By using columns command
root@ubuntu:/home/mahesh/sqoop-related# sqoop import –connect jdbc:mysql://localhost/mahesh –table emp -m 1 –columns ‘empId, eName’ –target-dir /ImportData –fields-terminated-by ‘t’

Example for append command in sqoop ?

root@ubuntu:/home/mahesh/sqoop-related# sqoop import –connect jdbc:mysql://localhost/mahesh –table emp -m 1 –columns ‘empId, eName’ –target-dir /ImportData –fields-terminated-by ‘t’ –append;

O/P:-
root@ubuntu:/home/mahesh/sqoop-related# hadoop fs -ls /ImportData
Found 5 items
-rw-r–r– 1 root supergroup 0 2013-11-07 19:05 /ImportData/_SUCCESS
drwxr-xr-x – root supergroup 0 2013-11-07 19:04 /ImportData/_logs
drwxr-xr-x – root supergroup 0 2013-11-07 19:11 /ImportData/_logs-00000
-rw-r–r– 1 root supergroup 67 2013-11-07 19:04 /ImportData/part-m-00000
-rw-r–r– 1 root supergroup 43 2013-11-07 19:11 /ImportData/part-m-00001
root@ubuntu:/home/mahesh/sqoop-related# hadoop fs -cat /ImportData/part-m-00001
111 mahesh
112 neelesh
113 rupesh
114 vijay

How to write queries on a RDBMS table ?

root@ubuntu:/home/mahesh/sqoop-related# sqoop import –connect jdbc:mysql://localhost/mahesh –table emp -m 1 –columns ‘empId, eName’ –target-dir /ImportData –fields-terminated-by ‘t’ –where ‘eSal>26000′ –append;

O/P:-
root@ubuntu:/home/mahesh/sqoop-related# hadoop fs -cat /ImportData/part-m-00002
111 mahesh
112 neelesh
114 vijay

What is the advantage with Eval command in sqoop ?

We can see the output directly from the terminal there is no need to go and check the output on top of HDFS

Eval Query Examples In sqoop ?

root@ubuntu:/home/mahesh/sqoop-related# sqoop eval –connect jdbc:mysql://localhost/mahesh –query “select * from emp”;
13/11/07 19:24:24 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
—————————————————-
| empId | eName | eSal |
—————————————————-
| 111 | mahesh | 28000 |
| 112 | neelesh | 30000 |
| 113 | rupesh | 26000 |
| 114 | vijay | 28000 |
—————————————————-

root@ubuntu:/home/mahesh/sqoop-related# sqoop eval –connect jdbc:mysql://localhost/mahesh –query “select * from emp limit 2″;

13/11/07 19:28:44 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
—————————————————-
| empId | eName | eSal |
—————————————————-
| 111 | mahesh | 28000 |
| 112 | neelesh | 30000 |
—————————————————-

root@ubuntu:/home/mahesh/sqoop-related# sqoop eval –connect jdbc:mysql://localhost/mahesh –query “select * from emp where empId = 111″;

13/11/07 19:29:02 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
—————————————————-
| empId | eName | eSal |
—————————————————-
| 111 | mahesh | 28000 |
—————————————————-

Create Table In sqoop by using Eval ?

root@ubuntu:/home/mahesh/sqoop-related# sqoop eval –connect jdbc:mysql://localhost/mahesh –query “create table evalTab(evalId int, evalName varchar(30), evalScope varchar(30))”;

13/11/07 19:35:39 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
13/11/07 19:35:39 INFO tool.EvalSqlTool: 0 row(s) updated.

root@ubuntu:/home/mahesh/sqoop-related# sqoop eval –connect jdbc:mysql://localhost/mahesh –query “select * from evalTab”;

13/11/07 19:36:02 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
————————————————————-
| evalId | evalName | evalScope |
————————————————————-

Insert Values Into Eval Table ?

root@ubuntu:/home/mahesh/sqoop-related# sqoop eval –connect jdbc:mysql://localhost/mahesh –query “insert into evalTab values(111,’aaa’, ‘app’)”;

13/11/07 19:37:37 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
13/11/07 19:37:37 INFO tool.EvalSqlTool: 1 row(s) updated.

root@ubuntu:/home/mahesh/sqoop-related# sqoop eval –connect jdbc:mysql://localhost/mahesh –query “insert into evalTab values(112,’bbb’, ‘prgrm’)”;

13/11/07 19:37:51 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
13/11/07 19:37:52 INFO tool.EvalSqlTool: 1 row(s) updated.

root@ubuntu:/home/mahesh/sqoop-related# sqoop eval –connect jdbc:mysql://localhost/mahesh –query “insert into evalTab values(113,’ccc’, ‘project’)”;

13/11/07 19:38:09 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
13/11/07 19:38:10 INFO tool.EvalSqlTool: 1 row(s) updated.

root@ubuntu:/home/mahesh/sqoop-related# sqoop eval –connect jdbc:mysql://localhost/mahesh –query “select * from evalTab”;

13/11/07 19:38:27 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
————————————————————-
| evalId | evalName | evalScope |
————————————————————-
| 111 | aaa | app |
| 112 | bbb | prgrm |
| 113 | ccc | project |
————————————————————-

How to show tables in Database by using Eval ?

root@ubuntu:/home/mahesh/sqoop-related# sqoop eval –connect jdbc:mysql://localhost/mahesh –query “show tables”;

13/11/07 19:40:22 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
————————
| TABLE_NAME |
————————
| emp |
| evalTab |
————————

How to describe Eval Table In Query ?

root@ubuntu:/home/mahesh/sqoop-related# sqoop eval –connect jdbc:mysql://localhost/mahesh –query “desc evalTab”;

13/11/07 19:40:39 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
———————————————————————————————————
| COLUMN_NAME | COLUMN_TYPE | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA |
———————————————————————————————————
| evalId | int(11) | YES | | (null) | |
| evalName | varchar(30) | YES | | (null) | |
| evalScope | varchar(30) | YES | | (null) | |
———————————————————————————————————
Import Command In sqoop ?

sqoop import –conncet jdbc:mysql://localhost/mahesh –table emp -m 1;

Export Command In sqoop ?

sqoop export –connect jdbc:mysql://localhost/mahesh -m 1 –table emp –export-dir/emptab/part-m-00000;

Job command In sqoop ?

root@ubuntu:/home/mahesh/sqoop-related# sqoop job –create DeptData — import –connect jdbc:mysql://localhost/mahesh –table dept -m 1 –target-dir /JobImport –append;

Speak Your Mind

*