This topic contains 0 replies, has 1 voice, and was last updated by  Avikumar 2 years, 3 months ago.

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #1282 Reply

    Avikumar
    Participant

    Sqoop – Import:-
    This chapter describes how to import data from MySQL database to Hadoop HDFS. The ‘Import tool’ imports individual tables from RDBMS to HDFS. Each row in a table is treated as a record in HDFS. All records are stored as text data in the text files or as binary data in Avro and Sequence files.

    Syntax
    The following syntax is used to import data into HDFS.

    $ sqoop import (generic-args) (import-args)
    $ sqoop-import (generic-args) (import-args)

    Importing a Table:-
    The following command is used to import the emp table from MySQL database server to HDFS.
    $ sqoop import \
    –connect jdbc:mysql://localhost/userdb \
    –username root \
    –table emp –m 1
    If it is executed successfully, then you get the following output.
    To verify the imported data in HDFS, use the following command.
    $ $HADOOP_HOME/bin/hadoop fs -cat /emp/part-m-*

    Importing into Target Directory:-
    We can specify the target directory while importing table data into HDFS using the Sqoop import tool.
    Following is the syntax to specify the target directory as option to the Sqoop import command.
    –target-dir <new or exist directory in HDFS>
    The following command is used to import emp_add table data into ‘/queryresult’ directory.

    $ sqoop import \
    –connect jdbc:mysql://localhost/userdb \
    –username root \
    –table emp_add \
    –m 1 \
    –target-dir /queryresult
    The following command is used to verify the imported data in /queryresult directory form emp_add table.

    $ $HADOOP_HOME/bin/hadoop fs -cat /queryresult/part-m-*

    Import Subset of Table Data:-
    We can import a subset of a table using the ‘where’ clause in Sqoop import tool. It executes the corresponding SQL query in the respective database server and stores the result in a target directory in HDFS.

    The syntax for where clause is as follows.

    –where <condition>
    The following command is used to import a subset of emp_add table data. The subset query is to retrieve the employee id and address, who lives in Secunderabad city.

    $ sqoop import \
    –connect jdbc:mysql://localhost/userdb \
    –username root \
    –table emp_add \
    –m 1 \
    –where “city =’sec-bad’” \
    –target-dir /wherequery
    The following command is used to verify the imported data in /wherequery directory from the emp_add table.
    $ $HADOOP_HOME/bin/hadoop fs -cat /wherequery/part-m-*
    $ $HADOOP_HOME/bin/hadoop fs -cat /emp/part-m-*1

    Sqoop – Import All Tables:-
    Syntax
    The following syntax is used to import all tables.
    $ sqoop import-all-tables (generic-args) (import-args)
    $ sqoop-import-all-tables (generic-args) (import-args)
    The following command is used to import all the tables from the userdb database.
    $ sqoop import \
    –connect jdbc:mysql://localhost/userdb \
    –username root
    The following command is used to verify all the table data to the userdb database in HDFS

    $ $HADOOP_HOME/bin/hadoop fs -ls

    Sqoop – Export:-
    The default operation is to insert all the record from the input files to the database table using the INSERT statement. In update mode, Sqoop generates the UPDATE statement that replaces the existing record into the database.

    Syntax
    The following is the syntax for the export command.

    $ sqoop export (generic-args) (export-args)
    $ sqoop-export (generic-args) (export-args)
    The following query is used to create the table ‘employee’ in mysql command line.

    $ mysql
    mysql> USE db;
    mysql> CREATE TABLE employee (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20),
    deg VARCHAR(20),
    salary INT,
    dept VARCHAR(10));
    The following command is used to export the table data (which is in emp_data file on HDFS) to the employee table in db database of Mysql database server.

    $ sqoop export \
    –connect jdbc:mysql://localhost/db \
    –username root \
    –table employee \
    –export-dir /emp/emp_data
    The following command is used to verify the table in mysql command line.

    mysql>select * from employee;

Viewing 1 post (of 1 total)
Reply To: Sqoop
Your information:




cf22

Your Name (required)

Your Email (required)

Subject

Phone No

Your Message

Cart

  • No products in the cart.