Using Hive to Load OBS Data and Analyze Enterprise Employee Information

Scenario

Hive is a data warehouse built on Hadoop. It provides batch computing capability for the big data platform and is able to batch analyze and summarize structured and semi-structured data for data calculation. Hive operates structured data using HQL, a SQL-like language. HQL is automatically converted into MapReduce tasks for the query and analysis of massive data in the Hadoop cluster.

Hive is able to:

• Analyze massive structured data and summarizes analysis results.
• Allow complex MapReduce jobs to be compiled in SQL languages.
• Support flexible data storage formats, including JSON, CSV, TextFile, RCFile, SequenceFile, and ORC.

This practice describes how to develop a Hive data analysis application and how to run HQL statements to access Hive data stored in OBS after you connect to Hive through the client. For example, manage and query enterprise employee information. If you need to develop and build your application based on the sample code project provided by MRS, see Application Development Overview.

In this practice, the raw data of employee information includes the following two tables:

You can perform the following analysis through a data application:

• Query contact information of employees whose salaries are paid in USD.
• Query the IDs and names of employees who were hired in 2014, and load the query results to a new table.
• Collect the number of employee information records.
• Query information about employees whose email addresses end with "cn".

Creating an MRS Offline Query Cluster

1. Go to the Buy Cluster page.
2. Click the Quick Config tab and set configuration parameters.

   Table 3 Software parameters (for reference only)

   Parameter	Value
   Region	EU-Dublin
   Billing Mode	Pay-per-use
   Cluster Name	MRS_demo
   Version Type	Normal
   Cluster Version	MRS 3.1.0
   Component	Hadoop Analysis Cluster
   AZ	AZ1
   VPC	vpc-01
   Subnet	subnet-01
   Enterprise Project	default
   Kerberos Authentication	Disabled
   Username	root/admin
   Password	Set the password for logging in to the cluster management page and ECS node, for example, Test!@12345.
   Confirm Password	Enter the password again.
   Secure Communications	Select Enable.

   Figure 1 Buying a Hadoop analysis cluster
   
3. Click Buy Now and wait until the MRS cluster is created.
   Figure 2 Cluster created
   

Creating an OBS Agency and Binding It to an MRS Cluster

• MRS presets MRS_ECS_DEFAULT_AGENCY in the agency list of IAM so that you can select this agency when creating a custom cluster. This agency has the OBSOperateAccess permissions and the CESFullAccess (only available for users who have enabled fine-grained policies), CES Administrator, and KMS Administrator permissions in the region where the cluster resides.
• If you want to use a custom agency, perform the following steps to create an agency. (To create or modify an agency, you must have the Security Administrator permission.)

1. Log in to the HUAWEI CLOUD management console.
2. Choose Service List > Management & Deployment > Identity and Access Management.
3. In the navigation pane on the left, choose Agencies. On the displayed page, click Create Agency.
4. Set Agency Name, select Cloud service for Agency Type, and select ECS BMS for Cloud Service to authorize ECS or BMS to invoke OBS.
5. Set Validity Period to Unlimited and click Next.
   Figure 3 Creating an agency
   
6. On the displayed page, search for the OBS OperateAccess policy and select it.
   Figure 4 Assigning permissions
   
7. Click Next. On the displayed page, select the desired scope for permissions you selected. By default, All resources is selected. Click Show More and select Global resources.
8. In the dialog box that is displayed, click OK to start authorization. After the message "Authorization successful." is displayed, click Finish. The agency is successfully created.
9. Switch back to the MRS console and click the name of the created MRS cluster. On the Dashboard page, click Manage Agency, select the created OBS agency, and click OK.
   Figure 5 Dashboard tab page of the MRS cluster
   
   Figure 6 Binding an agency to an MRS cluster
   

Creating a Hive Table and Loading Data from OBS

1. Choose Service List > Object Storage Service. In the navigation pane on the left, choose Parallel File Systems and click Create Parallel File System, set the following parameters, and click Create Now.

   Table 4 Parallel file system parameters

   Parameter	Value
   Region	EU-Dublin
   File System Name	hiveobs
   Policy	Private
   Direct Reading	Disable
   Enterprise Project	default
   Tags	-

2. Download the MRS cluster client, and install it, for example, in the /opt/client directory of the active master node.

   You can also use the cluster client provided in the /opt/Bigdata/client directory of the master node.

3. Bind an EIP to the active master node and enable port 22 in the security group. Then, log in to the active master node as user root, go to the directory where the client is located, and load variables.

   cd /opt/client

   source bigdata_env

4. Run the beeline command to go to the Hive Beeline page.

   Run the following command to create an employee information data table employees_info that matches the raw data fields:

   create external table if not exists employees_info

   (

   id INT,

   name STRING,

   usd_flag STRING,

   salary DOUBLE,

   deductions MAP<STRING, DOUBLE>,

   address STRING,

   entrytime STRING

   )

   row format delimited fields terminated by ',' map keys terminated by '&'

   stored as textfile

   location 'obs://hiveobs/employees_info';

   Run the following command to create an employee contact information table employees_contact that matches the raw data fields:

   create external table if not exists employees_contact

   (

   id INT,

   phone STRING,

   email STRING

   )

   row format delimited fields terminated by ','

   stored as textfile

   location 'obs://hiveobs/employees_contact';

5. Run the following command to check whether the table is successfully created:

   show tables;

   +--------------------------+
   |         tab_name         |
   +--------------------------+
   | employees_contact        |
   | employees_info           |
   +--------------------------+

6. Import data to the corresponding OBS table directory.

   By default, a folder is created in the specified storage space for a Hive internal table. The table can read data that matches the table structure as long as the file is stored in the folder.

   Log in to the OBS Console. On the Files page of the created file system, upload the local raw data to the employees_info and employees_contact folders.

   Figure 7 Uploading data
   

   The following is an example of the raw data format:

   info.txt:

   1,Wang,R,8000.01,personal income tax&0.05,China:Shenzhen,2014
   3,Tom,D,12000.02,personal income tax&0.09,America:NewYork,2014
   4,Jack,D,24000.03,personal income tax&0.09,America:Manhattan,2015
   6,Linda,D,36000.04,personal income tax&0.09,America:NewYork,2014
   8,Zhang,R,9000.05,personal income tax&0.05,China:Shanghai,2014

   contact.txt:

   1,135 XXXX XXXX,xxxx@xx.com
   3,159 XXXX XXXX,xxxx@xx.com.cn
   4,189 XXXX XXXX,xxxx@xx.org
   6,189 XXXX XXXX,xxxx@xx.cn
   8,134 XXXX XXXX,xxxx@xxx.cn

7. Run the following command on the Hive Beeline client to check whether the source data is correctly loaded:
   select * from employees_info;

   +--------------------+----------------------+--------------------------+------------------------+-------------------------------+-------------------------+---------------------------+
   | employees_info.id  | employees_info.name  | employees_info.usd_flag  | employees_info.salary  |   employees_info.deductions   | employees_info.address  | employees_info.entrytime  |
   +--------------------+----------------------+--------------------------+------------------------+-------------------------------+-------------------------+---------------------------+
   | 1                  | Wang                 | R                        | 8000.01                | {"personal income tax":0.05}  | China:Shenzhen          | 2014                      |
   | 3                  | Tom                  | D                        | 12000.02               | {"personal income tax":0.09}  | America:NewYork         | 2014                      |
   | 4                  | Jack                 | D                        | 24000.03               | {"personal income tax":0.09}  | America:Manhattan       | 2015                      |
   | 6                  | Linda                | D                        | 36000.04               | {"personal income tax":0.09}  | America:NewYork         | 2014                      |
   | 8                  | Zhang                | R                        | 9000.05                | {"personal income tax":0.05}  | China:Shanghai          | 2014                      |
   +--------------------+----------------------+--------------------------+------------------------+-------------------------------+-------------------------+---------------------------+

   select * from employees_contact;

   +-----------------------+--------------------------+--------------------------+
   | employees_contact.id  | employees_contact.phone  | employees_contact.email  |
   +-----------------------+--------------------------+--------------------------+
   | 1                     | 135 XXXX XXXX            | xxxx@xx.com              |
   | 3                     | 159 XXXX XXXX            | xxxx@xx.com.cn           |
   | 4                     | 186 XXXX XXXX            | xxxx@xx.org              |
   | 6                     | 189 XXXX XXXX            | xxxx@xx.cn               |
   | 8                     | 134 XXXX XXXX            | xxxx@xxx.cn              |
   +-----------------------+--------------------------+--------------------------+

Analyzing Data Based on HQL