Sqoop Import All Tables – A Complete Guide

Keeping you updated with latest technology trends, Join DataFlair on Telegram

1. Objective

A tool which imports a set of tables from an RDBMS to HDFS is what we call the Sqoop import all tables. Although there are many more insights on this tool. So, this document aims the whole concept of Sqoop import all tables in Sqoop. After its introduction, we will cover its Syntax also. Moreover, we will also cover its examples to understand it better.

So, let’s start the Sqoop Import All Tables tutorial.

Sqoop Import All Tables

Sqoop Import All Tables – A Complete Guide

2. Introduction to Sqoop Import All Tables

A tool which imports a set of tables from an RDBMS to HDFS is what we call the import-all-tables. Basically, here in HDFS, data from each table is stored in a separate directory.
However, there are various conditions that must meet for the import-all-tables tool to be useful. Such as:

  • Basically, it is important that each table must have a single-column primary key. Also,  –autoreset-to-one-mapper option must be used.
  • Moreover, we must intend to import all columns of each table.
  • However, via a WHERE clause, we must not intend to use non-default splitting column. Also, we must not impose any conditions.

3. Syntax of Sqoop import All Tables

$ sqoop import-all-tables (generic-args) (import-args)
$ sqoop-import-all-tables (generic-args) (import-args)
However,  the import arguments can be entered in any order with respect to one another, But the Hadoop generic arguments must precede any import arguments only.
Table 1. Common arguments

Argument Description
–connect <jdbc-uri> Specify JDBC connect string
–connection-manager <class-name> Specify connection manager class to use
–driver <class-name> Manually specify JDBC driver class to use
–hadoop-mapred-home <dir> Override $HADOOP_MAPRED_HOME
–help Print usage instructions
–password-file Set path for a file containing the authentication password
-P Read password from console
–password <password> Set authentication password
–username <username> Set authentication username
–verbose Print more information while working
–connection-param-file <filename> Optional properties file that provides connection parameters
–relaxed-isolation Set connection transaction isolation to read uncommitted for the mappers.

Table 2. Import control arguments

Argument Description
–as-avrodatafile Imports data to Avro Data Files
–as-sequencefile Imports data to SequenceFiles
–as-textfile Imports data as plain text (default)
–as-parquetfile Imports data to Parquet Files
–direct Use direct import fast path
–inline-lob-limit <n> Set the maximum size for an inline LOB
-m,–num-mappers <n> Use n map tasks to import in parallel
–warehouse-dir <dir> HDFS parent for table destination
-z,–compress Enable compression
–compression-codec <c> Use Hadoop codec (default gzip)
–exclude-tables <tables> Comma separated list of tables to exclude from import process
–autoreset-to-one-mapper Import should use one mapper if a table with no primary key is encountered

While we use the Sqoop-import tool, these arguments behave in the same manner. However, for Sqoop import all tables the –table, –split-by, –columns, and –where arguments are invalid. Although, the argument –exclude-tables is only for +sqoop-import-all-tables.
Table 3. Output line formatting arguments

Argument Description
–enclosed-by <char> Sets a required field enclosing character
–escaped-by <char> Sets the escape character
–fields-terminated-by <char> Sets the field separator character
–lines-terminated-by <char> Sets the end-of-line character
–mysql-delimiters Uses MySQL’s default delimiter set: fields: , lines: \n escaped-by: \ optionally-enclosed-by: ‘
–optionally-enclosed-by <char> Sets a field enclosing character

Table 4. Input parsing arguments

Argument Description
–input-enclosed-by <char> Sets a required field encloser
–input-escaped-by <char> Sets the input escape character
–input-fields-terminated-by <char> Sets the input field separator
–input-lines-terminated-by <char> Sets the input end-of-line character
–input-optionally-enclosed-by <char> Sets a field enclosing character

Table 5. Hive arguments

Argument Description
–hive-home <dir> Override $HIVE_HOME
–hive-import Import tables into Hive (Uses Hive’s default delimiters if none are set.)
–hive-overwrite Overwrite existing data in the Hive table.
–create-hive-table If set, then the job will fail if the target hive table exits. By default this property is false.
–hive-table <table-name> Sets the table name to use when importing to Hive.
–hive-drop-import-delims Drops \n, \r, and \01 from string fields when importing to Hive.
–hive-delims-replacement Replace \n, \r, and \01 from string fields with user defined string when importing to Hive.
–hive-partition-key Name of a hive field to partition are sharded on
–hive-partition-value <v> String-value that serves as partition key for this imported into hive in this job.
–map-column-hive <map> Override default mapping from SQL type to Hive type for configured columns.

Table 6. Code generation arguments

Argument Description
–bindir <dir> Output directory for compiled objects
–jar-file <file> Disable code generation; use specified jar
–outdir <dir> Output directory for generated code
–package-name <name> Put auto-generated classes in this package

Basically, the import-all-tables tool does not support the –class-name argument. Although, with –package-name in which all generated classes will be placed we can specify a package.

4. Sqoop Import Example

Basically, Import all tables from the corp database:
$ sqoop import-all-tables –connect jdbc:mysql://db.foo.com/corp
Moreover, here we are verifying that it worked:
$ hadoop fs -ls
Found 4 items
drwxr-xr-x   – someuser somegrp       0 2010-04-27 17:15 /user/someuser/EMPLOYEES
drwxr-xr-x   – someuser somegrp       0 2010-04-27 17:15 /user/someuser/PAYCHECKS
drwxr-xr-x   – someuser somegrp       0 2010-04-27 17:15 /user/someuser/DEPARTMENTS
drwxr-xr-x   – someuser somegrp       0 2010-04-27 17:15 /user/someuser/OFFICE_SUPPLIES

5. Conclusion

As a result, we have seen all the concepts of Sqoop- import-all-tables. Also, we have seen its purpose as well as its syntax. Moreover, we have seen all arguments. Afterwards, we have also covered its Example Invocations to understand it well. However, still, if you want to ask any query regarding, please ask through the comment section. Likewise, we ensure we will definitely get back to you.
Learn about Sqoop Export & Sqoop List Databases
For reference

1 Response

  1. Senthilkumar says:

    Could you please explain/share the commands for SQOOP import all operation commands arguments for ORACLE database.
    How to pass oracle DB details etc..
    Thanks in Advance

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.