Import data from SAP HANA to Huawei Cloud big data service: MRS

This document focuses on the process of exporting data from SAP to an MRS cluster.

Environmental information:

1. The MRS cluster version uses MRS_2.0.0 as an example (it is recommended to use a normal cluster) and install HADOOP and HIVE services.

2. The operation location is the master node of the cluster;
   

3. The SAP server can communicate with the MRS cluster;

4. 192.168.0.100 (example) is the SAP server IP.
   

Install Sqoop

1) Download open source Sqoop

http://www.apache.org/dyn/closer.lua/sqoop/1.4.7

2) Put the downloaded sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz package into the /opt/sqoop directory of the MRS cluster master node and extract it.

3) Enter the directory where the decompression is completed, modify the permissions and configuration

chmod 755 -R /opt/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0
chown omm:wheel -R /opt/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0
cd /opt/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/conf
cp sqoop-env-template.sh sqoop-env.sh
vi sqoop-env.sh

Add configuration:

export HADOOP_COMMON_HOME=/opt/client/HDFS/hadoop
export HADOOP_MAPRED_HOME=/opt/client/HDFS/hadoop
export HIVE_HOME=/opt/Bigdata/MRS_2.0.0/install/FusionInsight-Hive-3.1.0/hive-3.1.0
export HIVE_CONF_DIR=/opt/client/Hive/config
export HCAT_HOME=/opt/client/Hive/HCatalog

4) Add system variables and add SQOOP_HOME to your PATH

vi /etc/profile

Add some information:

export SQOOP_HOME=/opt/sqoop-1.4.7.bin__hadoop-2.6.0
export PATH=$PATH:$SQOOP_HOME/bin

After loading the environment

source /etc/profile
source /opt/client/bigdata_env

Import data from SAP HANA into HDFS

Obtain ngdbc.jar from the hdbclient folder of the SAP node (the general address is C:\Program Files\SAP\hdbclient). This is the driver that connects to the SAP HANA system. Put this Jar package into the Sqoop lib folder. Inside and change permissions

sqoop import \
--username SYSTEM \
--password **** \
--connect jdbc:sap://192.168.0.100:30215/?currentschema=SYSTEM \
--driver com.sap.db.jdbc.Driver \
--table test02 \
--m 1 \
--target-dir /tmp/test06

--username      SAP HANA username
--password      SAP HANA password
--connect       SAP IP and port
--driver        driver type
--table         table name
--m 1           process number
--target-dir    HDFS path for storage

After the operation is complete, go to view the data on HDFS:

Get data from SAP HANA and import it into HIVE

Obtain ngdbc.jar from the hdbclient folder of the SAP node (the general address is C:\Program Files\SAP\hdbclient). This is the driver that connects to the SAP HANA system. Put this Jar package into the Sqoop lib folder. Inside and change permissions

sqoop import \
--username SYSTEM \
--password **** \
--connect jdbc:sap://192.168.0.100:30215/?currentschema=SYSTEM \
--driver com.sap.db.jdbc.Driver \
--table TEST01 \
--m 1 \
--hive-import \
--direct

--username      SAP HANA username
--password      SAP HANA password
--connect       SAP IP and port
--driver        driver type
--table         table name
--m 1           process number
--hive-import
--direct

Enter beeline

Check whether the hive table has been imported

select * from test01;

Common error message:

1, if the report can not find the class: jline.console.completer.ArgumentCompleter

Need to add the missing jar package to Sqoop's lib folder

cp /opt/share/jline-2.12/jline-2.12.jar /opt/sqoop/sqoop-1.4.7.bin__hadoop-2.6.0/lib/

2. If you report the error: TEST01 table already exists, you need to delete or rename the TEST01 table storage address first (change the table name)

other problems:

1, the table name can not have "." (can not use the table name such as default.test01), if you need to specify the library, add the parameter currentschema=SYSTEM after the connection address

--connect jdbc:sap://192.168.0.100:30215/?currentschema=SYSTEM \

2. If there is no suitable type for the SAP data type to be converted to the HDFS data type, you need to change the type to a simpler data type.

3, the current test is a normal cluster, whether the security cluster has a certification problem has not been tested