sqoop - Sqoop Import All Tables - apache sqoop - sqoop tutorial - sqoop hadoop



How to import all tables from the RDBMS database server to the HDFS?

  • Each table data is stored in a separate directory and the directory name is same as the table name.
  • The import-all-tables tool imports a set of tables from an RDBMS to HDFS. Data from each table is stored in a separate directory in HDFS.
  • For the import-all-tables tool to be useful, the following conditions must be met:
    • Each table must have a single-column primary key or --autoreset-to-one-mapper option must be used.
    • You must intend to import all columns of each table.
    • You must not intend to use non-default splitting column, nor impose any conditions via a WHERE clause.
learn sqoop - sqoop tutorial - sqoop2 tutorial - sqoop mapreduce - sqoop job - sqoop code - sqoop programming - sqoop download - sqoop examples
learn sqoop - sqoop tutorial - sqoop2 tutorial - sqoop data transfer - sqoop job - sqoop code - sqoop programming - sqoop download - sqoop examples
learn sqoop - sqoop tutorial - sqoop2 tutorial - sqoop data transfer - sqoop job - sqoop code - sqoop programming - sqoop download - sqoop examples
learn sqoop - sqoop tutorial - sqoop2 tutorial - sqoop data transfer - sqoop job - sqoop code - sqoop programming - sqoop download - sqoop examples
learn sqoop - sqoop tutorial - sqoop2 tutorial - sqoop data transfer - sqoop job - sqoop code - sqoop programming - sqoop download - sqoop examples

Syntax

$ sqoop import-all-tables (generic-args) (import-args) 
$ sqoop-import-all-tables (generic-args) (import-args)
Click "Copy code" button to copy into clipboard - By wikitechy - sqoop tutorial - team

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)
--direct Use direct import fast path
--direct-split-size <n> Split the input stream every n bytes when importing in direct mode
--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)
  • These arguments behave in the same manner as they do when used for the sqoop-import tool, but the --table, --split-by, --columns, and --where arguments are invalid for sqoop-import-all-tables. The --exclude-tables argument is for +sqoop-import-all-tables only.

Example

  • Let us take an example of importing all tables from the userdb database.
  • The list of tables that the database userdb contains is as follows.

 +--------------------+
 |      Tables        |
 +--------------------+
 |      emp           |
 |      emp_add       |
 |      emp_contact   |
 +--------------------+
 
Click "Copy code" button to copy into clipboard - By wikitechy - sqoop tutorial - team
  • The following command is used to import all the tables from the userdb database.
$ sqoop import-all-tables \--connect jdbc:mysql://localhost/userdb \--username root
Click "Copy code" button to copy into clipboard - By wikitechy - sqoop tutorial - team

Note: If you are using the import-all-tables, it is mandatory that every table in that database must have a primary key field.

  • The following command is used to verify all the table data to the userdb database in HDFS
$ $HADOOP_HOME/bin/hadoop fs -ls
Click "Copy code" button to copy into clipboard - By wikitechy - sqoop tutorial - team
  • It will show you the list of table names in userdb database as directories.

Output

drwxr-xr-x - hadoop supergroup 0 2014-12-22 22:50 _sqoop
drwxr-xr-x - hadoop supergroup 0 2014-12-23 01:46 emp
drwxr-xr-x - hadoop supergroup 0 2014-12-23 01:50 emp_add
drwxr-xr-x - hadoop supergroup 0 2014-12-23 01:52 emp_contact

Import all tables from the corp database:

$ sqoop import-all-tables --connect jdbc:mysql://db.foo.com/corp
Click "Copy code" button to copy into clipboard - By wikitechy - sqoop tutorial - team

Validating:

$ 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

Related Searches to Sqoop Import All Tables