Overview on Sqoop

Sqoop is open source s/w from Apache used for transfer data between RDBMS(Oracle, SQL Server, MySQL) and HDFS.

MySQL Database
Connecting to MySQL Database in cloudera VM:

root user: root/cloudera
other user: cloudera/cloudera

[cloudera@quickstart ~]$ mysql -u root -p




Create new user in MySQL
mysql> create user cloudera1 identified by 'cloudera1';
mysql> grant all privileges on *.* to 'cloudera1';

Listing databases in MySQL
mysql> show databases;

Switching to db
mysql> USE retail_db;

Listing tables in retail_db database
mysql> show tables;

Connecting to Sqoop

Version of Sqoop
[cloudera@quickstart hivedata]$ sqoop version

[cloudera@quickstart hivedata]$ sqoop help

Available commands:
  codegen            Generate code to interact with database records
  create-hive-table  Import a table definition into Hive
  eval               Evaluate a SQL statement and display the results
  export             Export an HDFS directory to a database table
  help               List available commands
  import             Import a table from a database to HDFS
  import-all-tables  Import tables from a database to HDFS
  import-mainframe   Import datasets from a mainframe server to HDFS
  job                Work with saved jobs
  list-databases     List available databases on a server
  list-tables        List available tables in a database
  merge              Merge results of incremental imports
  metastore          Run a standalone Sqoop metastore
  version            Display version information




import command

import --> importing data from RDBMS to HDFS

1) import data from a table to HDFS(all the rows and columns)
2) import data from a table to HDFS (all the rows but specific columns)
3) import data from a table to HDFS (all the columns, filter the rows by usign where)
4) import data from a table to HDFS  (Specific columns,filter the rows by usign where)


Table:  trails_db.customers

1) import data from a table to HDFS(all the rows and columns)

[cloudera@quickstart hivedata]$ sqoop import --connect jdbc:mysql://192.168.13.135/retail_db --username cloudera --password cloudera --table customers --m 1;

imported data will be stored in /user/cloudera   (default location)

view the data
[cloudera@quickstart ~]$ hdfs dfs -cat /user/cloudera/customers/part-m-00000


2) import data from a table to HDFS (all the rows but specific columns)

[cloudera@quickstart hivedata]$ sqoop import --connect jdbc:mysql://192.168.13.135/retail_db --username cloudera --password cloudera --table customers --columns "customer_id","customer_fname","customer_email" --m 1;

imported data will be stored in /user/cloudera   (default location)

view the data
[cloudera@quickstart ~]$ hdfs dfs -cat /user/cloudera/customers/part-m-00000


3) import data from a table to HDFS (all the columns, filter the rows by usign where)

[cloudera@quickstart hivedata]$ sqoop import --connect jdbc:mysql://192.168.13.135/retail_db --username cloudera --password cloudera --table customers --where "customer_state='IN'" --m 1 --target-dir /user/hive/warehouse/customers

view the data
[cloudera@quickstart ~]$ hdfs dfs -cat /user/hive/warehouse/customers/part-m-00000

4) import data from a table to HDFS  (Specific columns,filter the rows by usign where)

[cloudera@quickstart ~]$ sqoop import --connect jdbc:mysql://192.168.13.135/retail_db --username cloudera --password cloudera --table customers --columns "customer_id","customer_fname" --where "customer_state='IN'" --m 1 --target-dir /user/hive/warehouse/customers

view the data
[cloudera@quickstart ~]$ hdfs dfs -cat /user/hive/warehouse/customers/part-m-00000