![\"Sqoop](\"https:\/\/techvidvan.com\/tutorials\/wp-content\/uploads\/sites\/2\/2020\/08\/Sqoop-import-mainframe-TV.jpg\")
<\/a><\/p>\nWhat is Sqoop Import Mainframe?<\/h3>\n\n- Sqoop Import Mainframe is a tool that imports all the sequential datasets in a partitioned dataset (PDS) on the mainframe to the HDFS.<\/li>\n
- A partitioned dataset (PDS) is similar to a directory in the open systems.<\/li>\n
- The records in the dataset contain only the character data.<\/li>\n
- The records are stored with the whole record as the single text field.<\/li>\n<\/ul>\n
Syntax of Sqoop Import Mainframe Tool<\/h4>\n
The Syntax for the Sqoop Mainframe tool is:<\/p>\n
$ sqoop import-mainframe (generic-args) (import-args)\n$ sqoop-import-mainframe (generic-args) (import-args)\n<\/pre>\nWe can enter the import arguments in any order corresponding to each other, but we have to enter the Hadoop generic arguments before any import arguments.<\/p>\n
The common arguments are:<\/strong><\/p>\n\n\n\nArgument<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\n–connect <hostname><\/span><\/td>\n This argument specifies the mainframe host to connect to.\u00a0<\/span><\/td>\n<\/tr>\n\n–connection-manager <class-name><\/span><\/td>\n This argument specify the connection manager class which is to use<\/span><\/td>\n<\/tr>\n\n–hadoop-mapred-home <dir><\/span><\/td>\n Override $HADOOP_MAPRED_HOME<\/span><\/td>\n<\/tr>\n\n–help<\/span><\/td>\n It will print the usage instructions<\/span><\/td>\n<\/tr>\n\n–password-file<\/span><\/td>\n It will set the path for a file which contains the authentication password<\/span><\/td>\n<\/tr>\n\n-P<\/span><\/td>\n It will read the password from a console<\/span><\/td>\n<\/tr>\n\n–password <password><\/span><\/td>\n Set authentication password<\/span><\/td>\n<\/tr>\n\n–username <username><\/span><\/td>\n Set authentication username<\/span><\/td>\n<\/tr>\n\n–verbose<\/span><\/td>\n It will print more information when working<\/span><\/td>\n<\/tr>\n\n–connection-param-file <filename><\/span><\/td>\n Specify the optional properties file which provides the connection parameters<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nLet us now explore different steps that one can go through while using the Sqoop Import Mainframe tool.<\/p>\n
Connecting to a Mainframe<\/h3>\n
We can use Sqoop for importing mainframe datasets into the HDFS. For doing so, we have to specify the mainframe hostname in a Sqoop –connect argument.<\/p>\n
$ sqoop import-mainframe --connect z390<\/pre>\nOn entering the above command, we will get connected to the mainframe host z390 via ftp.
\nSometimes, we have to authenticate against the mainframe host for accessing it. For doing so, we have to supply the username via the –username option.<\/p>\n
There are several different ways available in Sqoop for supplying a password to the mainframe. Some of the Secure ways of supplying password to the mainframe are:<\/p>\n
1.<\/strong> We can save the password in a file stored on the user’s home directory with the 400 permissions. After storing the file, we have to specify the path to that file by using the argument –password-file<\/strong>. It is the most preferred method for entering credentials.<\/p>\nApache Sqoop then reads the password from this file and then passes it to the MapReduce cluster. It does so by using secure means without exposing the password in the job configuration.<\/p>\n
This file, which contains the password, can be stored either on the Local FileSystem or on the HDFS.
\nExample:<\/strong><\/p>\n$ sqoop import-mainframe --connect z390 \\\n --username dataflair --password-file ${user.home}\/.password\n<\/pre>\n2.<\/strong> We can also supply the password by using the -P argument that will read the password from the console prompt.
\nExample:<\/strong><\/p>\n$ sqoop import-mainframe --connect z390 --username dataflair --password 123\n<\/pre>\nSelecting the Files to Import<\/h3>\n
The Import control arguments are:<\/strong><\/p>\n\n\n\nArgument<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\n–as-avrodatafile<\/span><\/td>\n Imports data to the Avro Data Files<\/span><\/td>\n<\/tr>\n\n–as-sequencefile<\/span><\/td>\n Imports data to the SequenceFiles<\/span><\/td>\n<\/tr>\n\n–as-textfile<\/span><\/td>\n Imports data as a plain text (default)<\/span><\/td>\n<\/tr>\n\n–as-parquetfile<\/span><\/td>\n Imports data to the Parquet Files<\/span><\/td>\n<\/tr>\n\n–delete-target-dir<\/span><\/td>\n It will delete an import target directory if the directory exists<\/span><\/td>\n<\/tr>\n\n-m,–num-mappers <n><\/span><\/td>\n Use <\/span>n<\/span><\/i> map tasks for importing in parallel<\/span><\/td>\n<\/tr>\n\n–target-dir <dir><\/span><\/td>\n HDFS destination dir<\/span><\/td>\n<\/tr>\n\n–warehouse-dir <dir><\/span><\/td>\n HDFS parent for the table destination<\/span><\/td>\n<\/tr>\n\n-z,–compress<\/span><\/td>\n Enable compression<\/span><\/td>\n<\/tr>\n\n–compression-codec <c><\/span><\/td>\n Use the Hadoop codec (default gzip)<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nWe can use –dataset<\/strong> argument for specifying the partitioned dataset name. All the sequential datasets present in the partitioned dataset will get imported.<\/p>\nControlling Parallelism<\/h3>\n
Apache Sqoop imports data in parallel by creating multiple ftp connections to the mainframe for transferring multiple files simultaneously.
\nBy using -m<\/strong> or –num-mappers<\/strong> argument, we can specify the number of mappers (parallel processes) used for performing the import.<\/p>\nBoth of these arguments take the integer value corresponding to the degree of parallelism.
\nSqoop, by default, uses four map tasks. We can adjust this value for maximizing the data transfer rate from the mainframe.<\/p>\n
Controlling Distributed Cache<\/h3>\n
Apache Sqoop copies the jars in the $SQOOP_HOME\/lib<\/strong> folder to the job cache every time while starting a Sqoop job. When we launch it by Oozie, then it is unnecessary because Oozie uses its own Sqoop share lib, which keeps the Sqoop dependencies in a distributed cache.<\/p>\nBy using the argument –skip-dist-cache<\/strong> in Sqoop command when launched by the Oozie, it will skip the step by which the Sqoop copies all its dependencies to the job cache and save the massive I\/O.<\/p>\nControlling the Import Process<\/h3>\n
Sqoop, by default, imports all the sequential files present in the partitioned dataset pds to a directory named pds inside the home directory in the HDFS.
\nFor example, if our username is someuser, then the Sqoop import tool will write to the \/user\/someuser\/pds\/(files).
\nWe can adjust the parent directory of import with the argument –warehouse-dir.<\/strong><\/p>\nFor example:<\/strong><\/p>\n$ sqoop import-mainframe --connnect <host> --dataset foo --warehouse-dir \/shared \\\n ...\n<\/pre>\nThe above command will write to a set of files present in the \/shared\/pds\/ directory.<\/p>\n
Also, we can explicitly choose the target directory like:<\/p>\n
$ sqoop import-mainframe --connnect <host> --dataset foo --target-dir \/dest \\\n ...\n<\/pre>\nThis above command will import the files into a \/dest directory. The argument –target-dir<\/strong> is incompatible with the —warehouse-dir<\/strong> argument.
\nThe import, by default, goes to a new target location. If in case the destination directory already exists in the HDFS, then Sqoop will refuse to import and overwrite the content of that directory.<\/p>\nSqoop File Formats<\/h3>\n
Each record in the dataset is stored as a text record with the newline at the end. It is assumed that each record contains the single text field with the name DEFAULT_COLUMN. When Apache Sqoop imports the data to the HDFS, it generates a Java class that reinterprets the text files that it creates.<\/p>\n
We can also import the mainframe records to the Sequence, Parquet, or Avro files. By default, while importing the data is not compressed.<\/p>\n
We can compress our data either by using the deflate (gzip) algorithm with the –compress<\/strong> or -z argument, or we can specify the Hadoop compression codec by using the –compression-codec<\/strong> argument.<\/p>\nThe Output line formatting arguments:<\/strong><\/p>\n\n\n\nArgument<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\n–enclosed-by <char><\/span><\/td>\n It will set the required field enclosing character<\/span><\/td>\n<\/tr>\n\n–escaped-by <char><\/span><\/td>\n It will set the escape character<\/span><\/td>\n<\/tr>\n\n–fields-terminated-by <char><\/span><\/td>\n It will set the field separator character<\/span><\/td>\n<\/tr>\n\n–lines-terminated-by <char><\/span><\/td>\n It will set the end-of-line character<\/span><\/td>\n<\/tr>\n\n–mysql-delimiters<\/span><\/td>\n Uses the MySQL\u2019s default delimiter set: fields: , lines: \\n escaped-by: \\ optionally-enclosed-by: ‘<\/span><\/td>\n<\/tr>\n\n–optionally-enclosed-by <char><\/span><\/td>\n It will set the field enclosing character<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nThe Input parsing arguments are:<\/strong><\/p>\n\n\n\nArgument<\/span><\/td>\n Description<\/span><\/td>\n<\/tr>\n\n–input-enclosed-by <char><\/span><\/td>\n Sets a required field encloser<\/span><\/td>\n<\/tr>\n\n–input-escaped-by <char><\/span><\/td>\n Sets the input escape character<\/span><\/td>\n<\/tr>\n\n–input-fields-terminated-by <char><\/span><\/td>\n Sets the input field separator<\/span><\/td>\n<\/tr>\n\n–input-lines-terminated-by <char><\/span><\/td>\n Sets the input end-of-line character<\/span><\/td>\n<\/tr>\n\n–input-optionally-enclosed-by <char><\/span><\/td>\n Sets a field enclosing character<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nImporting Data Into Hive<\/h3>\n
We use Sqoop mainly for uploading table data into HDFS. But if we have a Hive metastore associated with our HDFS cluster, then also we can use Apache Sqoop.<\/p>\n
Sqoop imports the data into the Hive by generating and executing the CREATE TABLE<\/strong> statement for defining data\u2019s layout in the Hive. We can import data into Hive just by adding the option –hive-import in our Sqoop command line.<\/p>\nIf in case the Hive table already exists, then we can specify –hive-overwrite<\/strong> option that indicates the replacement of the already existing table in hive.<\/p>\nWhen the Sqoop job wants to import data into HDFS, then Sqoop will generate the Hive script that contains the CREATE TABLE operation that defines our columns using the Hive\u2019s types, and the LOAD DATA INPATH<\/strong> statement for moving data files into the Hive\u2019s warehouse directory.<\/p>\nThis script is executed by calling an installed copy of Hive on the machine where Sqoop is running. If we have multiple Hive installations, or the hive is not in our $PATH, then we use the option –hive-home<\/strong> for identifying Hive installation directory. Apache Sqoop will use $HIVE_HOME\/bin\/hive<\/strong> from here.<\/p>\nWe can import data for Hive into the particular partition by specifying the arguments, –hive-partition-key and –hive-partition-value arguments.<\/p>\n
By using the –compress<\/strong> and –compression-codec<\/strong> options, we can import compressed tables into the Hive.<\/p>\nThe Hive arguments are:<\/strong><\/p>\n\n\n\nArgument<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\n–hive-home <dir><\/span><\/td>\n Override $HIVE_HOME<\/span><\/td>\n<\/tr>\n\n–hive-import<\/span><\/td>\n Import tables into Hive. It uses the Hive\u2019s default delimiters if none are set.<\/span><\/td>\n<\/tr>\n\n–hive-overwrite<\/span><\/td>\n Overwrite the existing data in the Hive table.<\/span><\/td>\n<\/tr>\n\n–create-hive-table<\/span><\/td>\n If we set this option, then the Sqoop job will fail if a target hive table exits. This property, by default, is set to false.<\/span><\/td>\n<\/tr>\n\n–hive-table <table-name><\/span><\/td>\n It will set a table name to use while importing to Hive.<\/span><\/td>\n<\/tr>\n\n–hive-drop-import-delims<\/span><\/td>\n It will drop the <\/span>\\n<\/span><\/i>, <\/span>\\r<\/span><\/i>, and <\/span>\\01<\/span><\/i> from the string fields while importing to Hive.<\/span><\/td>\n<\/tr>\n\n–hive-delims-replacement<\/span><\/td>\n It will replace the <\/span>\\n<\/span><\/i>, <\/span>\\r<\/span><\/i>, and <\/span>\\01<\/span><\/i> from the string fields with the user defined string while importing to Hive.<\/span><\/td>\n<\/tr>\n\n–hive-partition-key<\/span><\/td>\n It will specify the name of the hive field to which the partition are sharded on<\/span><\/td>\n<\/tr>\n\n–hive-partition-value <v><\/span><\/td>\n It will specify the String-value which serves as a partition key for this imported into the hive in this job.<\/span><\/td>\n<\/tr>\n\n–map-column-hive <map><\/span><\/td>\n Override the default mapping from the SQL type to the Hive type for the configured columns.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nImporting Data Into HBase<\/h3>\n
Apache Sqoop can import the records into the table in HBase as well. For importing a table to HBase instead of any directory in HDFS, we have to specify the –hbase-table<\/strong> option in the Sqoop command.<\/p>\nApache Sqoop will import the data to a table specified as an argument to the –hbase-table option. Each row of an input table is transformed into the HBase Put operation to the row of the output table. For each row, it takes the key from the column of the input.<\/p>\n
Sqoop, by default, uses a split-by column as a row key column.<\/p>\n
If the split-by column is not specified, then it will try to find the primary key column. We can also manually specify the row key column with the –hbase-row-key<\/strong>. Every output column is placed in the same column family specified with –column-family<\/strong>.<\/p>\nWhile importing data into HBase, if the target table and the column family don\u2019t exist, then the Sqoop job exits with an error.<\/p>\n
So if you are importing using the –hbase-table option, then you have to create the target table and the column family before running the import.
\nIf we specify the option –hbase-create-table<\/strong>, then the Sqoop will itself create the target table and the column family if they don\u2019t exist.<\/p>\nThe HBase arguments are:<\/strong><\/p>\n\n\n\nArgument<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\n–column-family <family><\/span><\/td>\n It will set a target column family for the import<\/span><\/td>\n<\/tr>\n\n–hbase-create-table<\/span><\/td>\n If we specify this option then it will create missing HBase tables.\u00a0<\/span><\/td>\n<\/tr>\n\n–hbase-row-key <col><\/span><\/td>\n It will specify which input column to be used as a row key. If the input table contains a composite key, then in such as case the<col> must be in the form of the comma-separated list of the composite key attributes<\/span><\/td>\n<\/tr>\n\n
Syntax of Sqoop Import Mainframe Tool<\/h4>\n
The Syntax for the Sqoop Mainframe tool is:<\/p>\n
$ sqoop import-mainframe (generic-args) (import-args)\n$ sqoop-import-mainframe (generic-args) (import-args)\n<\/pre>\nWe can enter the import arguments in any order corresponding to each other, but we have to enter the Hadoop generic arguments before any import arguments.<\/p>\n
The common arguments are:<\/strong><\/p>\n
\n\n
\n Argument<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n \n –connect <hostname><\/span><\/td>\n This argument specifies the mainframe host to connect to.\u00a0<\/span><\/td>\n<\/tr>\n \n –connection-manager <class-name><\/span><\/td>\n This argument specify the connection manager class which is to use<\/span><\/td>\n<\/tr>\n \n –hadoop-mapred-home <dir><\/span><\/td>\n Override $HADOOP_MAPRED_HOME<\/span><\/td>\n<\/tr>\n \n –help<\/span><\/td>\n It will print the usage instructions<\/span><\/td>\n<\/tr>\n \n –password-file<\/span><\/td>\n It will set the path for a file which contains the authentication password<\/span><\/td>\n<\/tr>\n \n -P<\/span><\/td>\n It will read the password from a console<\/span><\/td>\n<\/tr>\n \n –password <password><\/span><\/td>\n Set authentication password<\/span><\/td>\n<\/tr>\n \n –username <username><\/span><\/td>\n Set authentication username<\/span><\/td>\n<\/tr>\n \n –verbose<\/span><\/td>\n It will print more information when working<\/span><\/td>\n<\/tr>\n \n –connection-param-file <filename><\/span><\/td>\n Specify the optional properties file which provides the connection parameters<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Let us now explore different steps that one can go through while using the Sqoop Import Mainframe tool.<\/p>\n
Connecting to a Mainframe<\/h3>\n
We can use Sqoop for importing mainframe datasets into the HDFS. For doing so, we have to specify the mainframe hostname in a Sqoop –connect argument.<\/p>\n
$ sqoop import-mainframe --connect z390<\/pre>\nOn entering the above command, we will get connected to the mainframe host z390 via ftp.
\nSometimes, we have to authenticate against the mainframe host for accessing it. For doing so, we have to supply the username via the –username option.<\/p>\nThere are several different ways available in Sqoop for supplying a password to the mainframe. Some of the Secure ways of supplying password to the mainframe are:<\/p>\n
1.<\/strong> We can save the password in a file stored on the user’s home directory with the 400 permissions. After storing the file, we have to specify the path to that file by using the argument –password-file<\/strong>. It is the most preferred method for entering credentials.<\/p>\n
Apache Sqoop then reads the password from this file and then passes it to the MapReduce cluster. It does so by using secure means without exposing the password in the job configuration.<\/p>\n
This file, which contains the password, can be stored either on the Local FileSystem or on the HDFS.
\nExample:<\/strong><\/p>\n$ sqoop import-mainframe --connect z390 \\\n --username dataflair --password-file ${user.home}\/.password\n<\/pre>\n2.<\/strong> We can also supply the password by using the -P argument that will read the password from the console prompt.
\nExample:<\/strong><\/p>\n$ sqoop import-mainframe --connect z390 --username dataflair --password 123\n<\/pre>\nSelecting the Files to Import<\/h3>\n
The Import control arguments are:<\/strong><\/p>\n
\n\n
\n Argument<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n \n –as-avrodatafile<\/span><\/td>\n Imports data to the Avro Data Files<\/span><\/td>\n<\/tr>\n \n –as-sequencefile<\/span><\/td>\n Imports data to the SequenceFiles<\/span><\/td>\n<\/tr>\n \n –as-textfile<\/span><\/td>\n Imports data as a plain text (default)<\/span><\/td>\n<\/tr>\n \n –as-parquetfile<\/span><\/td>\n Imports data to the Parquet Files<\/span><\/td>\n<\/tr>\n \n –delete-target-dir<\/span><\/td>\n It will delete an import target directory if the directory exists<\/span><\/td>\n<\/tr>\n \n -m,–num-mappers <n><\/span><\/td>\n Use <\/span>n<\/span><\/i> map tasks for importing in parallel<\/span><\/td>\n<\/tr>\n \n –target-dir <dir><\/span><\/td>\n HDFS destination dir<\/span><\/td>\n<\/tr>\n \n –warehouse-dir <dir><\/span><\/td>\n HDFS parent for the table destination<\/span><\/td>\n<\/tr>\n \n -z,–compress<\/span><\/td>\n Enable compression<\/span><\/td>\n<\/tr>\n \n –compression-codec <c><\/span><\/td>\n Use the Hadoop codec (default gzip)<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n We can use –dataset<\/strong> argument for specifying the partitioned dataset name. All the sequential datasets present in the partitioned dataset will get imported.<\/p>\n
Controlling Parallelism<\/h3>\n
Apache Sqoop imports data in parallel by creating multiple ftp connections to the mainframe for transferring multiple files simultaneously.
\nBy using -m<\/strong> or –num-mappers<\/strong> argument, we can specify the number of mappers (parallel processes) used for performing the import.<\/p>\nBoth of these arguments take the integer value corresponding to the degree of parallelism.
\nSqoop, by default, uses four map tasks. We can adjust this value for maximizing the data transfer rate from the mainframe.<\/p>\nControlling Distributed Cache<\/h3>\n
Apache Sqoop copies the jars in the $SQOOP_HOME\/lib<\/strong> folder to the job cache every time while starting a Sqoop job. When we launch it by Oozie, then it is unnecessary because Oozie uses its own Sqoop share lib, which keeps the Sqoop dependencies in a distributed cache.<\/p>\n
By using the argument –skip-dist-cache<\/strong> in Sqoop command when launched by the Oozie, it will skip the step by which the Sqoop copies all its dependencies to the job cache and save the massive I\/O.<\/p>\n
Controlling the Import Process<\/h3>\n
Sqoop, by default, imports all the sequential files present in the partitioned dataset pds to a directory named pds inside the home directory in the HDFS.
\nFor example, if our username is someuser, then the Sqoop import tool will write to the \/user\/someuser\/pds\/(files).
\nWe can adjust the parent directory of import with the argument –warehouse-dir.<\/strong><\/p>\nFor example:<\/strong><\/p>\n
$ sqoop import-mainframe --connnect <host> --dataset foo --warehouse-dir \/shared \\\n ...\n<\/pre>\nThe above command will write to a set of files present in the \/shared\/pds\/ directory.<\/p>\n
Also, we can explicitly choose the target directory like:<\/p>\n
$ sqoop import-mainframe --connnect <host> --dataset foo --target-dir \/dest \\\n ...\n<\/pre>\nThis above command will import the files into a \/dest directory. The argument –target-dir<\/strong> is incompatible with the —warehouse-dir<\/strong> argument.
\nThe import, by default, goes to a new target location. If in case the destination directory already exists in the HDFS, then Sqoop will refuse to import and overwrite the content of that directory.<\/p>\nSqoop File Formats<\/h3>\n
Each record in the dataset is stored as a text record with the newline at the end. It is assumed that each record contains the single text field with the name DEFAULT_COLUMN. When Apache Sqoop imports the data to the HDFS, it generates a Java class that reinterprets the text files that it creates.<\/p>\n
We can also import the mainframe records to the Sequence, Parquet, or Avro files. By default, while importing the data is not compressed.<\/p>\n
We can compress our data either by using the deflate (gzip) algorithm with the –compress<\/strong> or -z argument, or we can specify the Hadoop compression codec by using the –compression-codec<\/strong> argument.<\/p>\n
The Output line formatting arguments:<\/strong><\/p>\n
\n\n
\n Argument<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n \n –enclosed-by <char><\/span><\/td>\n It will set the required field enclosing character<\/span><\/td>\n<\/tr>\n \n –escaped-by <char><\/span><\/td>\n It will set the escape character<\/span><\/td>\n<\/tr>\n \n –fields-terminated-by <char><\/span><\/td>\n It will set the field separator character<\/span><\/td>\n<\/tr>\n \n –lines-terminated-by <char><\/span><\/td>\n It will set the end-of-line character<\/span><\/td>\n<\/tr>\n \n –mysql-delimiters<\/span><\/td>\n Uses the MySQL\u2019s default delimiter set: fields: , lines: \\n escaped-by: \\ optionally-enclosed-by: ‘<\/span><\/td>\n<\/tr>\n \n –optionally-enclosed-by <char><\/span><\/td>\n It will set the field enclosing character<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n The Input parsing arguments are:<\/strong><\/p>\n
\n\n
\n Argument<\/span><\/td>\n Description<\/span><\/td>\n<\/tr>\n \n –input-enclosed-by <char><\/span><\/td>\n Sets a required field encloser<\/span><\/td>\n<\/tr>\n \n –input-escaped-by <char><\/span><\/td>\n Sets the input escape character<\/span><\/td>\n<\/tr>\n \n –input-fields-terminated-by <char><\/span><\/td>\n Sets the input field separator<\/span><\/td>\n<\/tr>\n \n –input-lines-terminated-by <char><\/span><\/td>\n Sets the input end-of-line character<\/span><\/td>\n<\/tr>\n \n –input-optionally-enclosed-by <char><\/span><\/td>\n Sets a field enclosing character<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Importing Data Into Hive<\/h3>\n
We use Sqoop mainly for uploading table data into HDFS. But if we have a Hive metastore associated with our HDFS cluster, then also we can use Apache Sqoop.<\/p>\n
Sqoop imports the data into the Hive by generating and executing the CREATE TABLE<\/strong> statement for defining data\u2019s layout in the Hive. We can import data into Hive just by adding the option –hive-import in our Sqoop command line.<\/p>\n
If in case the Hive table already exists, then we can specify –hive-overwrite<\/strong> option that indicates the replacement of the already existing table in hive.<\/p>\n
When the Sqoop job wants to import data into HDFS, then Sqoop will generate the Hive script that contains the CREATE TABLE operation that defines our columns using the Hive\u2019s types, and the LOAD DATA INPATH<\/strong> statement for moving data files into the Hive\u2019s warehouse directory.<\/p>\n
This script is executed by calling an installed copy of Hive on the machine where Sqoop is running. If we have multiple Hive installations, or the hive is not in our $PATH, then we use the option –hive-home<\/strong> for identifying Hive installation directory. Apache Sqoop will use $HIVE_HOME\/bin\/hive<\/strong> from here.<\/p>\n
We can import data for Hive into the particular partition by specifying the arguments, –hive-partition-key and –hive-partition-value arguments.<\/p>\n
By using the –compress<\/strong> and –compression-codec<\/strong> options, we can import compressed tables into the Hive.<\/p>\n
The Hive arguments are:<\/strong><\/p>\n
\n\n
\n Argument<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n \n –hive-home <dir><\/span><\/td>\n Override $HIVE_HOME<\/span><\/td>\n<\/tr>\n \n –hive-import<\/span><\/td>\n Import tables into Hive. It uses the Hive\u2019s default delimiters if none are set.<\/span><\/td>\n<\/tr>\n \n –hive-overwrite<\/span><\/td>\n Overwrite the existing data in the Hive table.<\/span><\/td>\n<\/tr>\n \n –create-hive-table<\/span><\/td>\n If we set this option, then the Sqoop job will fail if a target hive table exits. This property, by default, is set to false.<\/span><\/td>\n<\/tr>\n \n –hive-table <table-name><\/span><\/td>\n It will set a table name to use while importing to Hive.<\/span><\/td>\n<\/tr>\n \n –hive-drop-import-delims<\/span><\/td>\n It will drop the <\/span>\\n<\/span><\/i>, <\/span>\\r<\/span><\/i>, and <\/span>\\01<\/span><\/i> from the string fields while importing to Hive.<\/span><\/td>\n<\/tr>\n \n –hive-delims-replacement<\/span><\/td>\n It will replace the <\/span>\\n<\/span><\/i>, <\/span>\\r<\/span><\/i>, and <\/span>\\01<\/span><\/i> from the string fields with the user defined string while importing to Hive.<\/span><\/td>\n<\/tr>\n \n –hive-partition-key<\/span><\/td>\n It will specify the name of the hive field to which the partition are sharded on<\/span><\/td>\n<\/tr>\n \n –hive-partition-value <v><\/span><\/td>\n It will specify the String-value which serves as a partition key for this imported into the hive in this job.<\/span><\/td>\n<\/tr>\n \n –map-column-hive <map><\/span><\/td>\n Override the default mapping from the SQL type to the Hive type for the configured columns.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Importing Data Into HBase<\/h3>\n
Apache Sqoop can import the records into the table in HBase as well. For importing a table to HBase instead of any directory in HDFS, we have to specify the –hbase-table<\/strong> option in the Sqoop command.<\/p>\n
Apache Sqoop will import the data to a table specified as an argument to the –hbase-table option. Each row of an input table is transformed into the HBase Put operation to the row of the output table. For each row, it takes the key from the column of the input.<\/p>\n
Sqoop, by default, uses a split-by column as a row key column.<\/p>\n
If the split-by column is not specified, then it will try to find the primary key column. We can also manually specify the row key column with the –hbase-row-key<\/strong>. Every output column is placed in the same column family specified with –column-family<\/strong>.<\/p>\n
While importing data into HBase, if the target table and the column family don\u2019t exist, then the Sqoop job exits with an error.<\/p>\n
So if you are importing using the –hbase-table option, then you have to create the target table and the column family before running the import.
\nIf we specify the option –hbase-create-table<\/strong>, then the Sqoop will itself create the target table and the column family if they don\u2019t exist.<\/p>\nThe HBase arguments are:<\/strong><\/p>\n
\n\n
\n Argument<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n \n –column-family <family><\/span><\/td>\n It will set a target column family for the import<\/span><\/td>\n<\/tr>\n \n –hbase-create-table<\/span><\/td>\n If we specify this option then it will create missing HBase tables.\u00a0<\/span><\/td>\n<\/tr>\n \n –hbase-row-key <col><\/span><\/td>\n It will specify which input column to be used as a row key. If the input table contains a composite key, then in such as case the<col> must be in the form of the comma-separated list of the composite key attributes<\/span><\/td>\n<\/tr>\n \n