Submitting a Sqoop Job

Updated on 2024-11-29 GMT+08:00

View PDF

Scenario

This section outlines the procedure for submitting a Sqoop job using the Oozie client.

NOTE:

Parameters that facilitate the automatic creation and deletion of HCatalog tables, such as Sqoop's options --create-hcatalog-table and --drop-and-create-hcatalog-table, are not applicable for data imports using Sqoop. It is necessary to pre-create the required tables in Oozie. To leverage the automatic table creation feature, perform data import using the --hive-import method.
If Kerberos authentication is enabled for the cluster (the cluster is in security mode), it is not possible to use the Oozie client for direct Sqoop task submissions to HBase. Instead, execute the Sqoop command for data imports or conduct operations on a normal cluster.

Prerequisites

The Sqoop and Oozie components and clients have been installed and are running properly.
Obtain the URL of the Oozie service and the IP address and port number of the active Yarn ResourceManager node.
- The URL of the Oozie service is https://Host IP address of the Oozie instance:Port number/oozie. For example, you can use https://10.1.130.11:21003/oozie to specify the Oozie server that runs the Sqoop task.
  Log in to FusionInsight Manager, choose Cluster > Services > Oozie and click Instances to check the IP address of any Oozie instance. Click Configurations and search for OOZIE_HTTPS_PORT in the search box to check the port number in use.
- The IP address and port number of the active Yarn ResourceManager node are used to modify the resourceManager parameter in the job.properties file. The format is Active ResourceManager IP address:Port number, for example, 10.1.130.11:8032.
  Log in to FusionInsight Manager, choose Cluster > Services > Yarn and click Instances to check the IP address of the active ResourceManager instance. Click Configurations and search for yarn.resourcemanager.port in the search box to check the port number in use.
Upload the JDBC driver JAR package (for example, MySQL driver package mysql-connector-java-5.1.47.jar) of the corresponding relational database version to the /user/oozie/share/lib/sqoopclient/ directory of HDFS, and change the permission and user group to be the same as those of other JAR packages in the directory, perform either of the following operations to update the JAR package dependency:
- Refresh the dependency on the Oozie client.
  oozie admin -oozie https://Host IP address of the Oozie instance:Port number/oozie -sharelibupdate
- Restart the Oozie service to refresh the dependency.
  Log in to FusionInsight Manager and choose Cluster > Services > Oozie. On the page that is displayed, choose More > Restart Service to restart the Oozie service.
You have obtained the JDBC driver JAR package of the corresponding relational database version, for example, the MySQL driver package mysql-connector-java-5.1.47.jar.
The corresponding Sqoop command has been prepared. For details, see .

Procedure

Create a human-machine user for accessing the Oozie service. (Skip this step if a user with related permissions already exists.)
1. Log in to FusionInsight Manager and choose System > Permission.
2. Click Role and click Create Role.
  - Role Name: Enter a role name, for example, oozieadmin.
  - Configure Resource Permission: Click the name of the desired cluster, click Oozie, and select Admin.
3. Choose User in the navigation pane and click Create on the displayed page. Create a human-machine user.
  - Username: Enter the username, for example, admin123.
  - User Type: Select Human-Machine.
  - Password and Confirm Password: Enter a password and enter it again for confirmation.
  - User Group: Add the user to the hadoop, supergroup, and hive user groups.
  - Role: Click Add and bind manager_viewer and the role (for example, oozieadmin) that has the Oozie administrator permissions added in 1.b.
4. Log in to FusionInsight Manager as the new user and change the initial password.
Log in to the node where the client is installed as the client installation user.
Configure environment variables. /opt/client is an example client installation path.

source /opt/client/bigdata_env
Check the cluster authentication mode.
- Kerberos authentication has been enabled for the cluster. Run the kinit command to authenticate users.
  For example, the oozieuser user is authenticated using the following command:
  
  kinit oozieuser
- If Kerberos authentication is not enabled for the cluster, go to 5.
Go to the Sqoop sample directory.

cd /opt/client/Oozie/oozie-client-*/examples/apps/sqoopclient/
NOTE:
- Sqoop supports two workflow compilation methods: sqoopclient and sqoopclient-freeform. The distinction between them is confined to the format of the Sqoop command parameters within the workflow.xml file.
- The sqoopclient-freeform method can be utilized identically to the sqoopclient method. Here are some guidelines for using sqoopclient.
- If the actual parameter in the Sqoop command contains spaces, use /opt/client/Oozie/oozie-client-*/examples/apps/sqoopclient-freeform as an example. For details, see workflow.xml in the path.
  For example, the Sqoop command contains the parameter --query'select TT.I, TT.S from TT where $CONDITIONS' , and the select statement of the command contains spaces.
Modify the job.properties file.

vim job.properties

Perform the following modifications:
- Change the value of resourceManager to the IP address and port number of the active ResourceManager node of Yarn, for example, resourceManager=10.1.130.11:8032.
- Change the value of userName to the name of the human-machine user who submits the task, that is, the user name created in 1, for example, userName=oozieuser. This parameter is used to combine the HDFS user path used by Oozie Job.
- Change the value of user.name to the name of the human-machine user who submits the task, that is, the user name created in 1, for example, user.name=oozieuser. This parameter specifies the user who submits the task.
- In a multi-cluster environment, change the value of nameNode to the value of fs.defaultFS of the Oozie service in the corresponding cluster.
In the following example, the resourceManager, userName, and user.name parameters are modified. Retain the default values for other parameters.
```
nameNode=hdfs://hacluster
resourceManager=10.1.130.11:8032
queueName=default
examplesRoot=examples
userName=oozieuser
user.name=oozieuser
oozie.use.system.libpath=true
oozie.wf.application.path=${nameNode}/user/${userName}/${examplesRoot}/apps/sqoop
```

Modify the workflow.xml file:

Kerberos authentication is disabled for the cluster (the cluster is in normal mode)

vim workflow.xml

Below is an example of importing data from a MySQL database to Hive. The bolded content should be adjusted accordingly.

<workflow-app xmlns="uri:oozie:workflow:1.0" name="sqoopclient-wf">
    <start to="sqoopclient-node"/>

    <action name="sqoopclient-node">
        <sqoopclient xmlns="uri:oozie:sqoopclient-action:1.0">
            <resource-manager>${resourceManager}</resource-manager>
            <name-node>${nameNode}</name-node>
            <prepare>
                <delete path="${nameNode}/user/${userName}/${examplesRoot}/output-data/sqoopclient"/>
                <mkdir path="${nameNode}/user/${userName}/${examplesRoot}/output-data"/>
            </prepare>
            <configuration>
                <property>
                    <name>mapred.job.queue.name</name>
                    <value>${queueName}</value>
                </property>
            </configuration>
            <!--   Specify a specific Sqoop command. Do not add sqoop at the beginning of the command. Do not write sqoop import. Do not use single or double quotation marks to quote the database password. -->
            <command>import --connect jdbc:mysql://mysql_host_ip:3306/database --username xxx --password xxx --table xxx --hive-import --hive-table xxx --delete-target-dir --fields-terminated-by "," -m 1 --as-textfile</command>
            <!--   Specify the HDFS configuration file to be used. This parameter should only be specified if explicitly required.-->
            <file>/user/oozie/share/lib/sqoopclient/hive-site.xml#hive-site.xml</file> 
        </sqoopclient>
        <ok to="end"/>
        <error to="fail"/>
    </action>

    <kill name="fail">
        <message>Sqoop client failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
    </kill>
    <end name="end"/>
</workflow-app>

Kerberos authentication is enabled for the cluster (the cluster is in security mode)

cp workflow.xml.security workflow.xml

vim workflow.xml

Below is an example of importing data from a MySQL database to Hive. The bolded content should be adjusted accordingly.

<workflow-app xmlns="uri:oozie:workflow:1.0" name="sqoopclient-wf">
    <credentials>
      <credential name='hcat_auth' type='hcat'>
         <property>
            <name>hcat.metastore.uri</name>
            <!--  The value can be obtained from hive.metastore.uris in hive-site.xml. For example, you can obtain the value from Client installation directory/Hive/config/hive-site.xml.
            <value>thrift://172.19.xxx.xxx:9083,thrift://172.xxx.xxx.xxx:9083</value>
         </property>
         <property>
            <name>hcat.metastore.principal</name>
            <!--  The value can be obtained from hive.metastore.kerberos.principal in hive-site.xml. For example, you can obtain the value from Client installation directory/Hive/config/hive-site.xml.
            <value>hive/hadoop.xxx.com@XXX_XXX_XXX_XXX_XXX.COM</value>
         </property>
      </credential>
    </credentials>

    <start to="sqoopclient-node"/>

    <action name="sqoopclient-node" cred='hcat_auth'>
        <sqoopclient xmlns="uri:oozie:sqoopclient-action:1.0">
            <resource-manager>${resourceManager}</resource-manager>
            <name-node>${nameNode}</name-node>
            <prepare>
                <delete path="${nameNode}/user/${userName}/${examplesRoot}/output-data/sqoopclient"/>
                <mkdir path="${nameNode}/user/${userName}/${examplesRoot}/output-data"/>
            </prepare>
            <configuration>
                <property>
                    <name>mapred.job.queue.name</name>
                    <value>${queueName}</value>
                </property>
            </configuration>
            <!--   Specify a specific Sqoop command. Do not add sqoop at the beginning of the command. Do not write sqoop import. Do not use single or double quotation marks to quote the database password. -->
            <command>import --connect jdbc:mysql://mysql_host_ip:3306/database --username xxx --password xxx --table xxx --hive-import --hive-table xxx --delete-target-dir --fields-terminated-by "," -m 1 --as-textfile</command>
            <!--   Specify the HDFS configuration file to be used. This parameter should only be specified if explicitly required.-->
            <file>/user/oozie/share/lib/sqoopclient/hive-site.xml#hive-site.xml</file> 
        </sqoopclient>
        <ok to="end"/>
        <error to="fail"/>
    </action>

    <kill name="fail">
        <message>Sqoop client failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
    </kill>
    <end name="end"/>
</workflow-app>

Upload the workflow.xml file to the HDFS path specified by oozie.wf.application.path in the job.properties file.

hadoop fs -put -f workflow.xml /user/oozieuser/examples/apps/sqoopclient
NOTE:
- If the path does not exist, run the following command to create one:
  hadoop fs -mkdir -p /user/oozieuser/examples/apps/sqoopclient
- Each modification to the workflow.xml file on the client requires re-uploading the file to HDFS prior to executing the Oozie job command.
Run the oozie job command to run the workflow file.

oozie job -oozie https://Host IP address of the Oozie instance:Port number/oozie/ -config job.properties -run
NOTE:
- The command parameters are described as follows:
  - -oozie: URL of the Oozie server that actually executes the Sqoop task
  - -config: Workflow property file
  - -run: Executing a workflow
- If the job ID is displayed after the workflow file is executed, the submission is successful. The following is an example.
  job: 0000021-140222101051722-oozie-omm-W
  You can view the execution results on the Oozie web UI.
  
  Log in to the Oozie web UI at https://IP address of the Oozie role:21003/oozie as user oozieuser.
  
  On the Oozie web UI, you can view the submitted workflow information based on the job ID in the table on the page.