Apache Sqoop Interview Questions and Answers

The most prominent open-source Apache Sqoop tool is specially designed for transferring a huge amount of data between structured datastores like mainframes or relational databases and Apache Hadoop. The key use of the Sqoop tool is to import bulk amounts of data from RDBMS into HDFS and it transforms the data in MapReduce then finally it exports data back to RDBMS. Hadoop MapReduce is used by Sqoop to import and export data and it also provides fault tolerance as well as parallel operations.

The key purpose of the Sqoop Import tool is to import large amounts of data from a Mainframe or Relational Database to HDFS. In Sqoop, the process of import is performed in parallel and the output result will be in multiple files. Using import you can import individual tables from the Relational database to HDFS. In a table, each row is represented as a separate or individual record in HDFS. In order to import tables to HDFS, you need to specify a connection string to describe the process of database connectivity. Typically, data is imported in table-centric fashion.

$ sqoop import (generic-args) (import-args)

Usually, The data is imported by the Sqoop tool in table-centric fashion. The –table argument is used to select the table in a relational database to import data. Typically, this argument is used to identify a table-like entity or view in a database. 

The few import control arguments are listed below:

• –append – This argument is used to append the data to the dataset in HDFS.
• –direct – Helps in making use of a direct connector if it exists for the database.
• –as-textfile – This argument is used to import data as plain text by default.
• –as-avrodatafile – This argument is used to import data to Avro Data Files.
• –table <table-name> – Allows table to read the data.
• –fetch-size <n> – Used to read number of entries from database at once.
• –target-dir <dir> – Shows HDFS destination Directory.
• –as-sequencefile – This argument is used to import data to Sequence Files.
• -z,–compress – This argument enables compression.

By default, the Sqoop import process will make use of JDBC (Java Database Connectivity) because it provides a specific and accurate cross-vendor import channel. Many relational databases can perform effective import operations in a more secure fashion by using data movement tools. In Apache Sqoop, the imports go to the new target location by default. The –append argument is used by Sqoop to import data to a temporary directory and then rename the files into the original directory. 

 Basically there are two different file formats available in Apache Sqoop to import the data and they are:

• SequenceFiles

• Delimited text

Delimited text: The default format for import in Apache Sqoop is the Delimited text. This file format can be specified in a direct way by using –as-textfile argument. The Delimited text file format is best suitable for non-binary data types. The delimited text also supports manipulations by some other tools like Hive. 

SequenceFiles: This file format supports binary format because it is used to store individual or various records in the custom related data types. Binary data types are provided automatically by Sqoop. The SequenceFiles provides very high performance than data read from the text files.

The large objects like CLOB and BLOB are handled by Apache Sqoop in a particular way. If you feel that the data is very large then you need to handle the data in streaming fashion In Sqoop, the large objects size less than 16MB are stored by default inline within the available data space. At a very large extent of size, the files are actually stored in subdirectory _lobs of the sqoop import main directory. In Sqoop if suppose, you will set the inline limit of the LOB to 0 value then all the larger objects will be saved in the external storage. 

The main function of the Apache Sqoop import tool is to import the bi amount of data into files in HDFS. To import data into Hive by using Sqoop import you need to have, Hive metastore associated with the HDFS cluster. In such a case, Sqoop can import data to Hive by executing and generating a CREATE TABLE statement in order to define the data’s layout on the Hive. In a simple way, you can import data into Hive by adding –hive-import option to the Apache Sqoop command line. 

In Apache Sqoop, there are few additional import configuration properties that can be configured by modifying conf/sqoop-site.xml. Some additional configuration properties are listed below:

• sqoop.hbase.add.row.key – By default when it is set to true, the column acts as a row key, and then it will be added to row data present in HBase. When it is set to false by default, Apache Sqoop does not add the column that acts as a row key in the row data present in HBase.

• sqoop.bigdecimal.format.string – This argument is used to control the ways in which the BigDecimal columns will be formatted when stored as a String. A value of false (default) will use toString that may include an exponent (1E-7). When the value is set to true it will use toPlainString to store them excluding the exponent component (0.0000001); 

The main purpose of Sqoop-import-all-tables is to import a set of tables from a Relational Database Management Systems to HDFS. Eventually, the data from each and every table is stored in a different directory present in HDFS. In order to make use of Sqoop-import-all-tables tools the following conditions must be met:

• You must be intended to import all the columns of each and every table. 
• You must not specifically use a non-default splitting column.
• Each and every table must have a primary key.

Syntax:

$ sqoop import-all-tables (generic-args) (import-args)