Teach you how to use FusionInsight SqoopShell

Abstract: Sqoop-shell is a Loader shell tool, all of its functions are implemented by executing the script "sqoop2-shell".

This article is shared from the HUAWEI cloud community " FusionInsight SqoopShell Use Case ", author: Jia installs the boss.

1 Introduction to the use of SqoopShell

sqoop-shell is a Loader shell tool, all of its functions are implemented by executing the script "sqoop2-shell".

The sqoop-shell tool provides the following functions:

• Support for creating and updating connectors
• Support for creating and updating jobs
• Support for deleting connectors and jobs
• Support for starting jobs synchronously or asynchronously
• Support stop operation
• Support query job status
• Support for querying job history execution records
• Supports duplication of connectors and jobs
• Support for creating and updating conversion steps
• Support specifying row and column separators

The sqoop-shell tool supports the following modes:

• Interactive mode

By executing the "sqoop2-shell" script without parameters, enter the specific interactive window of Loader. After the user enters the script, the tool will return the corresponding information to the interactive window.

• Batch mode

By executing the "sqoop2-shell" script with a file name as a parameter, multiple commands are stored in the file line by line, and the sqoop-shell tool will execute all the commands in the file in order; or after the "sqoop2-shell" script Attach a command with the "-c" parameter, and only execute one command at a time.

2 SqoopShell configuration

2.1 Configure Loader client

1. Use the "PuTTY" tool to log in to the node where the client is located as the user who installed the client.
2. Execute the following command to prevent the timeout from exiting.

TMOUT=0

3. Execute the following command to enter the Loader client installation directory. For example, the Loader client installation directory is "/opt/hadoopclient/Loader".

cd /opt/hadoopclient/Loader

4. Execute the following commands to configure environment variables.

source /opt/hadoopclient/bigdata_env

5. Execute the following command to decompress "loader-tools-1.99.3.tar".

tar -xvf loader-tools-1.99.3.tar

The new file after decompression is saved in the "loader-tools-1.99.3" directory.

6. Run the following command to modify the tool authorization configuration file "login-info.xml", save and exit.

vi loader-tools-1.99.3/loader-tool/job-config/login-info.xml

2.2 Configure sqoopshell configuration file

Use the "PuTTY" tool to log in to the node where the Loader client is located as the user who installed the client.
Execute the following command to enter the "conf" directory of the sqoop-shell tool. For example, the Loader client installation directory is "/opt/hadoopclient/Loader".
cd /opt/hadoopclient/Loader/loader-tools-1.99.3/sqoop-shell/conf

Execute the following commands to configure authentication information.
vi client.properties

3 Sqoopshell usage example

3.1 Interactive mode

1. Execute the following command to enter the interactive mode (the client uses /opt/hadoopclient as an example).

source /opt/hadoopclient/bigdata_env

cd /opt/hadoopclient/Loader/loader-tools-1.99.3/sqoop-shell

./sqoop2-shell

2. Get help information (\h get help information, \cr get help information for create, \up get updated help information, and so on)
   
3. View connector
   

By analogy, you can view information such as framework, job, connection, etc.

4. Create connector

Select the corresponding connector according to the show connector information, create the connection according to the create help information, and then enter the corresponding information according to the command line prompts. If the prompted parameters do not need to be set, press enter to skip directly

5. Create an assignment

Select the corresponding link according to the information from show connection, create the Job according to the create help information, and then enter the corresponding information according to the command line prompts. If the prompted parameters do not need to be set, press enter to skip directly

You can also use --help to view the option information for creating a job

Json acquisition method

1) Log in to the loader native page and create a job of the same type (such as TaiPingTab).

2) Export the configuration json file of the job, copy the content of hops (including braces) in the json file, and save it as a new json file

3) Adjust the json file according to business needs.

3.2 Batch mode

There are two ways to batch mode, one is the way of ./sqoop2-shell+script, the command to be executed is configured in the script

other is the ./sqoop2-shell -c "command to be executed" method

Get help:

./sqoop2-shell -c "create connection -cn generic-jdbc-connector --help" Get help information for creating connection

./sqoop2-shell -c "create job -xn mysql -t import --help" View help information for creating a job

1. Script mode

cd /opt/hadoopclient/Loader/loader-tools-1.99.3/sqoop-shell

vi batchCommand.sh

Update assignment

2. -C mode (use the -c parameter to attach a command, sqoop-shell can execute only the attached command at a time)

Create an import task whose link is mysql and the name is TaiPingTab

./sqoop2-shell -c "create job -xn mysql -t import -name TaiPingTab --connector-table-schemaName keshangbank --connector-table-tableName Tab --connector-table-needPartition false --connector-table-columns id,name,value --framework-output-storageType HIVE --framework-output-outputDirectory /user/loader/Tab --framework-throttling-partitionHandlers 10 --framework-throttling-extractors 10 --trans /opt/client/Loader/loader-tools-1.99.3/loader-tool/job-config/TaiPing.json --queue root.default"

Start: ./sqoop2-shell -c "start job -n TaiPingTab -s"

Delete: ./sqoop2-shell -c "delete job -n TaiPingTab"

Update: ./sqoop2-shell -c'update job -j 37 -name update-test --connector-table-sql "select * from keshangbank.update_test where time <"2020-2-2" and ${CONDITIONS}" '

Note: 1. The meaning of each parameter is described in detail in createConnection createJob helpCmd startCmd in the sqoop-shell directory

2. Json files are obtained and referenced in the same way. For how to obtain Json files in batch mode, please refer to the way of obtaining json in interactive mode.
3. The parameters are not described in detail in this document. For the parameters, please refer to the sqoop-shell chapter of the product documentation.
4. The updated parameters are consistent with the created parameters

• Attachment: SqoopShell use case.docx743.88KB
• Attachment: Json.rar

Click to follow, and learn about Huawei Cloud's fresh technology for the first time~