sqoop import multiple tables

hadoop hive hdfs sqoop
Danny Westfall picture Danny Westfall · Jun 19, 2013 · Viewed 29.2k times · Source

We are using Cloudera CDH 4 and we are able to import tables from our Oracle databases into our HDFS warehouse as expected. The problem is we have 10's of thousands of tables inside our databases and sqoop only supports importing one table at a time.

What options are available for importing multiple tables into HDFS or Hive? For example what would be the best way of importing 200 tables from oracle into HDFS or Hive at a time?

The only solution i have seen so far is to create a sqoop job for each table import and then run them all individually. Since Hadoop is designed to work with large dataset it seems like there should be a better way though.

Answer

Kumar Reddy Basapuram picture Kumar Reddy Basapuram · May 21, 2014

U can use " import-all-tables " option to load all tables into HDFS at one time .

sqoop import-all-tables --connect jdbc:mysql://localhost/sqoop --username root --password hadoop  --target-dir '/Sqoop21/AllTables'

if we want to exclude some tables to load into hdfs we can use " --exclude-tables " option

Ex:

sqoop import-all-tables --connect jdbc:mysql://localhost/sqoop --username root --password hadoop  --target-dir '/Sqoop21/AllTables'  --exclude-tables <table1>,<tables2>

If we want to store in a specified directory then u can use " --warehouse-dir " option

Ex:

sqoop import-all-tables --connect jdbc:mysql://localhost/sqoop --username root --password hadoop --warehouse-dir '/Sqoop'

Related questions

Sqoop Hive table import, Table dataType doesn't match with database

Using Sqoop to import data from oracle to hive, its working fine but it create table in hive with only 2 dataTypes String and Double. I want to use timeStamp as datatype for some columns. How can I do it. bin/…

Read More
Where does Hive store files in HDFS?

I'd like to know how to find the mapping between Hive tables and the actual HDFS files (or rather, directories) that they represent. I need to access the table files directly. Where does Hive store its files in HDFS?

Read More
Hive load CSV with commas in quoted fields

I am trying to load a CSV file into a Hive table like so: CREATE TABLE mytable ( num1 INT, text1 STRING, num2 INT, text2 STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ","; LOAD DATA LOCAL INPATH '/data.csv' OVERWRITE INTO …

Read More