Sqoop UI

The Sqoop UI enables transferring data between structured and unstructured data sources. Examples of structured data source are the relational databases such as the MySQL and Postgres, and unstructured, semi-structured data sources include Hbase, HDFS, Cassandra. The UI lives uses Apache Sqoop to do this. See the Sqoop Documentation for more details on Sqoop.

Hue, the open source Big Data UI, has an application that enables transferring data between data sources such as relational databases and Hadoop. This new application is driven by Sqoop 2 and has several user experience improvements to boot.

Sqoop is a batch data migration tool for transferring data between traditional databases and Hadoop. The first version of Sqoop is a heavy client that drives and oversees data transfer via MapReduce.

In Sqoop 2, the majority of the work was moved to a server that a thin client communicates with. Also, any client can communicate with the Sqoop 2 server over its JSON-REST API Sqoop 2 was chosen instead of its predecessors because of its client-server design.

Environment

Importing FROM MySQL TO HDFS

The following is the canonical FROM job example sourced from Sqoop5MinutesDemo.html.

In Hue, this can be done in 3 easy steps:

If the new job button is not appearing, Sqoop2 is probably not starting. Make sure the MySql or other DB connectors are in the /usr/lib/sqoop/lib directory of Sqoop2. Make sure you have these properties in the Sqoop2 Server configuration:

org.apache.sqoop.repository.schema.immutable=false
org.apache.sqoop.connector.autoupgrade=true
org.apache.sqoop.driver.autoupgrade=true

1. Create Links for the From and To

In the Sqoop app, the data source link manager is available from the “New Job” wizard. To get to the new job wizard, click on “New Job”. There may be a list of links available if a few have been created before. For the purposes of this demo, we’ll go through the process of creating a new link. Click “Add a new link” and fill in the blanks with the data below. Then click save to return to the “New Job” wizard!

Add a link for the FROM data source such as the MySQL 

Link Config Input                     Value

Name                                  mysql-link-demo

JDBC Driver Class                     com.mysql.jdbc.Driver

JDBC Connection String                jdbc:mysql://hue-demo/demo

Username                              demo

Password                              demo

Add a link for the TO data source such as the HDFS 

Link Config Input                     Value

Name                                  hdfs-link-demo 

HDFS URI                              hdfs://hue-demo:8020/demo

2. Create a Job

After creating the FROM and TO links, follow the wizard and fill in the blanks with the information below. We can use the two links created above to associate the From and To for the job.

Job configuration for the FROM data source

From Job Config Input                                 Value

Schema name(Optional)

Table name                                             test

Table SQL statement:(Optional)

Table column names:(Optional)

Partition column name:(Optional)                        id

Null value allowed for the partition column:(Optional)

Boundary query:(Optional)

Job configuration for the TO data source

To Job Config Input                           Value

Output format                                 TEXT_FILE

Compression format(Optional)

Output directory                              /tmp/mysql-import-job-demo

Job configuration for the Job Execution Drivere

DriverConfig Input                     Value

Extractors(Optional)                     2

Loaders(Optional)                        2

3. Save and Submit the Job

At the end of the Job wizard, click “Save and Run”! The job should automagically start after that and the job dashboard will be displayed. As the job is running, a progress bar below the job listing will be dynamically updated. Links to the HDFS output via the File Browser and Map Reduce logs via Job Browser will be available on the left hand side of the job edit page.

Sum Up

The new Sqoop application enables batch data migration from a more traditional databases to Hadoop and vice versa through Hue. Using Hue, a user can move data between storage systems in a distributed fashion with the click of a button.

I’d like to send out a big thank you to the Sqoop community for the new client-server design!

Both projects are undergoing heavy development and are welcoming external contributions! Have any suggestions? Feel free to tell us what you think through hue-user or @gethue​!

Installation and Configuration

The Sqoop UI is one of the applications installed as part of Hue. For information about installing and configuring Hue, see the Hue Installation manual.

Starting

Click the Sqoop icon (image) in the navigation bar at the top of the Hue browser page.

Sqoop Jobs

Sqoop UI is oriented around jobs in Apache Sqoop.

Creating a New Job

  1. Click the New job button at the top right.
  2. In the Name field, enter a name.
  3. Choose the FROM and TO. The corresponding job configuration inputs will change depending on FROM or TO chosen.
  4. Select a link for the FROM or TO data source, or create one if it does not exist.
  5. Fill in the rest of the fields for the job. For FROM, the "Schema/Table name" and "Input directory" are necessary at a minimum for MySQL and HDFS data sources respectively
    6. For MySQL "TO" data source, the "Schema/Table name" is necessary and for HDFS TO data source, the Output directory" are necessary at a minimum.
  6. Click save to finish.

Editing a Job

  1. In the list of jobs, click on the name of the job.
  2. Edit the desired configuration fields in the job.

Copying a Job

  1. In the list of jobs, click on the name of the job.
  2. On the left hand side of the job editor, there should be a panel containing actions. Click Copy.

Removing a Job

  1. In the list of jobs, click on the name of the job.
  2. On the left hand side of the job editor, there should be a panel containing actions. Click Delete.

Running a Job

There's a status on each of the items in the job list indicating the last time a job was ran. The progress of the job should dynamically update. There's a progress bar at the bottom of each item on the job list as well.

  1. In the list of jobs, click on the name of the job.
  2. On the left hand side of the job editor, there should be a panel containing actions. Click Run.

Creating a New Link

  1. Click the New job button at the top right.
  2. At the link field, click the hyperlink titled Add a new link.
  3. Fill in the displayed fields.
  4. Click save to finish.

Editing a Link

  1. Click the New job button at the top right.
  2. At the link field, select the link by name that should be edited.
  3. Click Edit.
  4. Edit the any of the fields.
  5. Click save to finish.

Removing a Link

  1. Click the New job button at the top right.
  2. At the link field, select the link by name that should be deleted.
  3. Click Delete.

NOTE: If this does not work, it's like because a job is using that link. Make sure not jobs are using the link that will be deleted.

Filtering Sqoop Jobs

The text field in the top, left corner of the Sqoop Jobs page enables fast filtering of sqoop jobs by name.