Sqoop import with where clause

If you are following from previous sqoop import tutorial http://jugnu-life.blogspot.in/2012/03/sqoop-import-tutorial.html then , lets try to do conditional import from RDBMS in sqoop

$ sqoop import --connect jdbc:mysql://localhost/CompanyDatabase --table Customer --username root --password mysecret -m 1

The sqoop command above would import all the rows present in the table Customer.

Let say that customer table is something like this

CustomerName
DateOfJoining
Adam
2012-12-12
John
2002-1-3
Emma
2011-1-3
Tina
2009-3-8

Now lets say we want to import only those customers which are joining after 2005-1-1

We can modify the sqoop import as

$ sqoop import --connect jdbc:mysql://localhost/CompanyDatabase --table Customer --username root --password mysecret --where "DateOfJoining > '2005-1-1' "

This would import only 3 records from above table.

Happy sqooping :)

6 comments:

  1. Can we import only the tables we needed from mysql using sqoop with some script?

    -Gopi Krishna

    ReplyDelete
  2. Hello Gopi,

    Yes this is what Sqoop is made for , import what you need . As much you need. You just need to specify Table name which you want to import.


    See Sqoop user guide example

    $ sqoop import --connect jdbc:mysql://db.foo.com/corp --table EMPLOYEES \
    --username SomeUser -P

    The above code imports Table EMPLOYEES from the MySQL

    Are you having any issues?

    ReplyDelete
    Replies
    1. Got it.

      But if i had 100 tables then i have to write 100 import statements.

      Is there anyway to import few tables at a time from total tables present.

      Delete
    2. Hello Gopi,

      If you want to import all tables then you can use import-all command.

      If you have to import few tables from set of tables then you can write some script to generate code for you. Or Use excel to replace table names , it is straight forward.

      Delete
    3. Jagat can you explain me how a social CRM process look like?

      Delete

Please share your views and comments below.

Thank You.