Background

Suppose a user develops an application to manage users who use service A in an enterprise. The procedure of operating service A on the Hive client is as follows:

Operations on common tables:

• Create the user_info table.
• Add users' educational backgrounds and professional titles to the table.
• Query user names and addresses by user ID.
• Delete the user information table after service A ends.

Procedure

1. Log in to the node where the client is installed as the client installation user and run the following command to switch to the client installation directory, for example, /opt/hadoopclient.

   cd /opt/hadoopclient

2. Run the following command to configure environment variables:

   source bigdata_env

3. If Kerberos authentication is enabled for the current cluster, run the following command to authenticate the current user. The current user must have the permission to create Hive tables. If Kerberos authentication is disabled for the current cluster, skip this step.

   kinit MRS cluster user

   For example, kinit hiveuser

4. Run the Hive client command to implement service A.
   • Operations on internal tables:
     1. Run the following command to log in to the Hive client CLI:

        beeline

        Hive allows you to add extension identifiers to JDBC connection strings. These extension identifiers are printed in HiveServer audit logs to distinguish SQL sources. You can concatenate the following to a URL:

        auditAddition=xxx

        xxx is the custom identifier. The identifier can contain a maximum of 256 bytes and only letters, digits, underscores (_), commas (,), and colons (:) are allowed.

        For details about how to set the extension identifier using code, see the Hive Development Guide. The client connection can be set in either of the following ways:

        • Modify the Client installation directory/Hive/component_env file, add \;auditAddition=xxx to the end of the CLIENT_HIVE_URI parameter, and run the source bigdata_env command again to apply the changes.
        • When using a specified JDBC URL to connect to the Hive client, add ;auditAddition=xxx at the end of the URL. The following is an example:

     2. Create the user_info user information table according to Table 2-801 and add data to it.

        create table user_info(id string,name string,gender string,age int,addr string);

        insert into table user_info(id,name,gender,age,addr) values("12005000201","A","Male",19,"City A");

     3. Add users' education backgrounds and occupation to the user_info table.

        For example, to add educational background and title information about user 12005000201, run the following command:

        alter table user_info add columns(education string,technical string);

     4. Query user names and addresses by user ID.

        For example, to query the name and address of user 12005000201, run the following command:

        select name,addr from user_info where id='12005000201';

     5. Delete the user information table.

        drop table user_info;

     6. Run the following command to exit:

        !q

   • Operations on external partitioned tables:
     1. Run the insert statement to inset data into an external table or run the load data command to import file data from HDFS to an external table. To run the load data command, perform the following operations:
        1. Create a file based on the data in Table 2-801. For example, the file name is txt.log. Fields are separated by space, and the line feed characters are used as the line breaks.
        2. Run the following command to upload the file to the HDFS directory, for example, /tmp:

           hdfs dfs -put txt.log /tmp

     2. Create a path for storing external table data.

        hdfs dfs -mkdir /hive/

        hdfs dfs -mkdir /hive/user_info

     3. Run the following command to log in to the Hive client CLI:

        beeline

        Hive allows you to add extension identifiers to JDBC connection strings. These extension identifiers are printed in HiveServer audit logs to distinguish SQL sources. You can concatenate the following to a URL:

        auditAddition=xxx

        xxx is the custom identifier. The identifier can contain a maximum of 256 bytes and only letters, digits, underscores (_), commas (,), and colons (:) are allowed.

        For details about how to set the extension identifier using code, see the Hive Development Guide. The client connection can be set in either of the following ways:

        • Modify the Client installation directory/Hive/component_env file, add \;auditAddition=xxx to the end of the CLIENT_HIVE_URI parameter, and run the source bigdata_env command again to apply the changes.
        • When using a specified JDBC URL to connect to the Hive client, add ;auditAddition=xxx at the end of the URL. The following is an example:

     4. Run the following statement to create a table:

        create external table user_info(id string,name string,gender string,age int,addr string) partitioned by(year string) row format delimited fields terminated by ' ' lines terminated by '\n' stored as textfile location '/hive/user_info';

        fields terminated indicates delimiters, for example, spaces.

        lines terminated indicates line breaks, for example, \n.

        /hive/user_info indicates the path of the data file.

     5. Import data.
        • Run the insert statement to insert data. The following describes how to insert information about the user 12005000201. The procedure for inserting information about other users is similar.

          insert into user_info partition(year="2018") values ("12005000201","A","Male",19,"City A");

        • Run the load data command to import file data.

          load data inpath '/tmp/txt.log' into table user_info partition (year='2011');

          In the preceding command, /tmp/txt.log indicates the data file uploaded to the HDFS in 4.a.

     6. Query the imported data.

        select * from user_info;

     7. Delete the user information table.

        drop table user_info;

     8. Run the following command to exit:

        !q

Navigation Path

Go to the Hive configurations page by referring to Modifying Cluster Service Configuration Parameters.

Parameter Description

Scenario

This section guides users to use a Hive client in an O&M or service scenario. For details about shell commands, see MapReduce Service (MRS) 3.3.1-LTS Shell O&M Commands (for Huawei Cloud Stack 8.3.1).

Prerequisites

• The client has been installed. For details, see Installing a Client. For example, the client is installed in the /opt/hadoopclient directory. The client directory in the following operations is only an example. Change it to the actual installation directory.
• Service component users have been created by the MRS cluster administrator by referring to Creating a User. In security mode, machine-machine users need to download the keytab file. For details, see Exporting an Authentication Credential File. A human-machine user must change the password upon the first login.

Using the Hive Client

1. Log in to the node where the client is installed as the client installation user.
2. Run the following command to go to the client installation directory:

   cd /opt/hadoopclient

3. Run the following command to configure environment variables:

   source bigdata_env

4. If you use the client to connect to a specific Hive instance in a scenario when multiple Hive instances are installed, run the following command to load the environment variables of the instance. Otherwise, skip this step. For example, load the environment variables of the Hive2 instance.

   source Hive2/component_env

5. Log in to the Hive client based on the cluster authentication mode.
   • In security mode, run the following command to complete user authentication and log in to the Hive client:

     kinit Component service user

     beeline

   • In common mode, run the following command to log in to the Hive client. If no component service user is specified, the current OS user is used to log in to the Hive client.

     beeline -n component service user

6. Run the following command to execute the HCatalog client command:

   hcat -e "cmd"

   cmd must be a Hive DDL statement, for example, hcat -e "show tables".

   • To use the HCatalog client, choose More > Download Client on the service page to download the clients of all services. This restriction does not apply to the beeline client.
   • Due to permission model incompatibility, tables created using the HCatalog client cannot be accessed on the HiveServer client. However, the tables can be accessed on the WebHCat client.
   • If you use the HCatalog client in Normal mode, the system performs DDL commands using the current user who has logged in to the operating system.
   • Exit the beeline client by running the !q command instead of by pressing Ctrl + C. Otherwise, the temporary files generated by the connection cannot be deleted and a large number of junk files will be generated as a result.
   • If multiple statements need to be entered during the use of beeline clients, separate the statements from each other using semicolons (;) and set the value of entireLineAsCommand to false.

     Setting method: If beeline has not been started, run the beeline --entireLineAsCommand=false command. If the beeline has been started, run the !set entireLineAsCommand false command.

     After the setting, if a statement contains semicolons (;) that do not indicate the end of the statement, escape characters must be added, for example, select concat_ws('\;', collect_set(col1)) from tbl.

Common Hive Client Commands

The following table lists common Hive Beeline commands.

For more commands, see https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients#HiveServer2Clients-BeelineCommands.

Scenario

HDFS Colocation is the data location control function provided by HDFS. The HDFS Colocation API stores associated data or data on which associated operations are performed on the same storage node. Hive supports the HDFS Colocation function. When Hive tables are created, after the locator information is set for table files, data files of related tables are stored on the same storage node when data is inserted into tables using the insert statement (other data import modes are not supported). This ensures convenient and efficient data computing among associated tables. The supported table formats are only TextFile and RCFile.

Procedure

1. Log in to the node where the client is installed as a client installation user.
2. Run the following command to switch to the client installation directory, for example, /opt/hadoopclient:

   cd /opt/hadoopclient

3. Run the following command to configure environment variables:

   source bigdata_env

4. If the cluster is in security mode, run the following command to authenticate the user:

   kinit MRS username

5. Create the groupid through the HDFS API.

   hdfs colocationadmin -createGroup -groupId <groupid> -locatorIds <locatorid1>,<locatorid2>,<locatorid3>

   In the preceding command, <groupid> indicates the name of the created group. The group created in this example contains three locators. You can define the number of locators as required.

   For details about group ID creation and HDFS Colocation, see HDFS description.

6. Run the following command to log in to the Hive client:

   beeline

7. Enable Hive to use colocation.

   Assume that table_name1 and table_name2 are associated with each other. Run the following statements to create them:

   CREATE TABLE <[db_name.]table_name1>[(col_name data_type , ...)] [ROW FORMAT <row_format>] [STORED AS <file_format>] TBLPROPERTIES("groupId"=" <group> ","locatorId"="<locator1>");

   CREATE TABLE <[db_name.]table_name2> [(col_name data_type , ...)] [ROW FORMAT <row_format>] [STORED AS <file_format>] TBLPROPERTIES("groupId"=" <group> ","locatorId"="<locator1>");

   After data is inserted into table_name1 and table_name2 using the insert statement, data files of table_name1 and table_name2 are distributed to the same storage position in the HDFS, facilitating associated operations among the two tables.

Scenario

Hive supports encryption of one or multiple columns in a table. When creating a Hive table, you can specify the column to be encrypted and encryption algorithm. When data is inserted into the table using the insert statement, the related columns are encrypted. Column encryption can be performed in HDFS tables of only the TextFile and SequenceFile file formats. The Hive column encryption does not support views and the Hive over HBase scenario.

Hive supports two column encryption algorithms, which can be specified during table creation:

• AES (the encryption class is org.apache.hadoop.hive.serde2.AESRewriter)
• SMS4 (the encryption class is org.apache.hadoop.hive.serde2.SMS4Rewriter)

• In national cryptographic cluster scenarios, Hive column encryption supports only table creation using the SMS4 algorithm.
• When you import data from a common Hive table into a Hive column encryption table, you are advised to delete the original data from the common Hive table as long as doing this does not affect other services. Retaining an unencrypted table poses security risks.

Procedure

1. Specify the column to be encrypted and encryption algorithm when creating a table.

   create table<[db_name.]table_name> (<col_name1> <data_type> ,<col_name2> <data_type>,<col_name3> <data_type>,<col_name4> <data_type>) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH SERDEPROPERTIES ('column.encode.columns'='<col_name2>,<col_name3>', 'column.encode.classname'='org.apache.hadoop.hive.serde2.AESRewriter')STORED AS TEXTFILE;

   Alternatively, use the following statement:

   create table <[db_name.]table_name> (<col_name1> <data_type> ,<col_name2> <data_type>,<col_name3> <data_type>,<col_name4> <data_type>) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH SERDEPROPERTIES ('column.encode.indices'='1,2', 'column.encode.classname'='org.apache.hadoop.hive.serde2.SMS4Rewriter') STORED AS TEXTFILE;

   • The numbers used to specify encryption columns start from 0. 0 indicates column 1, 1 indicates column 2, and so on.
   • When creating a table with encrypted columns, ensure that the directory where the table resides is empty.

2. Insert data into the table using the insert statement.

   Assume that the test table exists and contains data.

   insert into table <table_name> select <col_list> from test;

Scenario

In most cases, a carriage return character is used as the row delimiter in Hive tables stored in text files, that is, the carriage return character is used as the terminator of a row during queries. However, some data files are delimited by special characters, and not a carriage return character.

MRS Hive allows you to use different characters or character combinations to delimit rows of Hive text data. When creating a table, set inputformat to SpecifiedDelimiterInputFormat, and set the following parameter before search each time. Then the table data is queried by the specified delimiter.

set hive.textinput.record.delimiter='';

The Hue component of the current version does not support the configuration of multiple separators when files are imported to a Hive table.

Procedure

1. Specify inputFormat and outputFormat when creating a table.

   CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name [(col_name data_type [COMMENT col_comment], ...)] [ROW FORMAT row_format] STORED AS inputformat 'org.apache.hadoop.hive.contrib.fileformat.SpecifiedDelimiterInputFormat' outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';

2. Specify the delimiter before search.

   set hive.textinput.record.delimiter='!@!';

   Hive will use '!@!' as the row delimiter.

Scenario

Due to the limitations of underlying storage systems, Hive does not support the ability to delete a single piece of table data. In Hive on HBase, MRS Hive supports the ability to delete a single piece of HBase table data. Using a specific syntax, Hive can delete one or more pieces of data from an HBase table.

Procedure

1. To delete some data from an HBase table, run the following HQL statement:

   remove table <table_name> where <expression>;

   In the preceding information, <expression> specifies the filter condition of the data to be deleted. <table_name> indicates the Hive on HBase table from which data is to be deleted.

Scenario

WebHCat provides external REST APIs for Hive. By default, the open-source community version uses the HTTP protocol.

MRS Hive supports the HTTPS protocol that is more secure, and enables switchover between the HTTP protocol and the HTTPS protocol.

The security mode supports HTTPS and HTTP, and the common mode supports only HTTP.

Procedure

1. Log in to FusionInsight Manager. For details, see Accessing FusionInsight Manager of an MRS Cluster. Click Cluster, choose Services > Hive, click Configurations, and then All Configurations.
2. Choose WebHCat(Role) > Security. On the page that is displayed, select HTTPS or HTTP. After the modification, restart the Hive service to use the corresponding protocol.

Scenario

The Transform function is not allowed by Hive of the open source version.

MRS Hive supports the configuration of the Transform function. The function is disabled by default, which is the same as that of the open-source community version.

Users can modify configurations of the Transform function to enable the function. However, security risks exist when the Transform function is enabled.

The Transform function can be disabled only in security mode.

Procedure

1. Log in to FusionInsight Manager. For details, see Accessing FusionInsight Manager of an MRS Cluster. Click Cluster, choose Services > Hive, click Configurations, and then All Configurations.
2. Enter the parameter name in the search box, search for hive.security.transform.disallow, change the parameter value to true or false, and restart all HiveServer instances.
   • If this parameter is set to true, the Transform function is disabled, which is the same as that in the open-source community version.
   • If this parameter is set to false, the Transform function is enabled, which poses security risks.

Scenario

This section describes how to create a view on Hive when MRS is configured in security mode, authorize access permissions to different users, and specify that different users access different data.

Example

• If the current_user() function is not used, different views need to be created for different users to access different data.
  • Authorize the view v1 permission to user hiveuser1. The user hiveuser1 can access data with type set to hiveuser1 in table1.

    create view v1 as select * from table1 where type='hiveuser1';

  • Authorize the view v2 permission to user hiveuser2. The user hiveuser2 can access data with type set to hiveuser2 in table1.

    create view v2 as select * from table1 where type='hiveuser2';

• If the current_user function is used, only one view needs to be created.

  Authorize the view v permission to users hiveuser1 and hiveuser2. When user hiveuser1 queries view v, the current_user() function is automatically converted to hiveuser1. When user hiveuser2 queries view v, the current_user() function is automatically converted to hiveuser2.

  create view v as select * from table1 where type=current_user();

Scenario

You must have ADMIN permission when creating temporary functions on Hive of the open source community version.

MRS Hive supports the configuration of the function for creating temporary functions with ADMIN permission. The function is disabled by default, which is the same as that of the open-source community version.

You can modify configurations of this function. After the function is enabled, you can create temporary functions without ADMIN permission. If this parameter is set to false, security risks exist.

The security mode supports the configuration of whether the ADMIN permission is required for creating temporary functions, but the common mode does not support this function.

Procedure

1. Log in to FusionInsight Manager. For details, see Accessing FusionInsight Manager of an MRS Cluster. Click Cluster, choose Services > Hive, click Configurations, and then All Configurations.
2. Enter the parameter name in the search box, search for hive.security.temporary.function.need.admin, change the parameter value to true or false, and restart all HiveServer instances.
   • If this parameter is set to true, the ADMIN permission is required for creating temporary functions, which is the same as that in the open source community.
   • If this parameter is set to false, the ADMIN permission is not required for creating temporary functions.

Scenario

Hive allows users to create external tables to associate with other relational databases. External tables read data from associated relational databases and support Join operations with other tables in Hive.

Prerequisites

The Hive client has been installed. For details, see Installing a Client.

Procedure

1. Log in to the node where the Hive client is installed as the Hive client installation user .
2. Run the following command to go to the client installation directory:

   cd Client installation directory

   For example, if the client installation directory is /opt/hadoopclient, run the following command:

   cd /opt/hadoopclient

3. Run the following command to configure environment variables:

   source bigdata_env

4. Check whether the cluster authentication mode is Security.
   • If yes, run the following command to authenticate the user:

     kinit Hive service user

   • If no, go to Step 5.

5. Run the following command to upload the driver JAR package of the relational database to be associated to an HDFS directory.

   hdfs dfs -put directory where the JAR package is located HDFS directory to which the JAR is uploaded

   For example, to upload the Oracle driver JAR package in /opt to the /tmp directory in HDFS, run the following command:

   hdfs dfs -put /opt/ojdbc6.jar /tmp

6. Create an external table on the Hive client to associate with the relational database, as shown in the following example.
   If the security mode is used, the user who creates the table must have the ADMIN permission. The ADD JAR path is subject to the actual path.

   -- Example of associating with an Oracle Linux 6 database
   -- In security mode, set the admin permission.
   set role admin;
   -- Upload the driver JAR package of the relational database to be associated. The driver JAR packages vary according to databases.
   ADD JAR hdfs:///tmp/ojdbc6.jar;
   
   CREATE EXTERNAL TABLE ora_test
   -- The Hive table must have one more column than the database return result. This column is used for paging query.
   (id STRING,rownum string)
   STORED BY 'com.qubitproducts.hive.storage.jdbc.JdbcStorageHandler'
   TBLPROPERTIES (
   -- Relational database table type
   "qubit.sql.database.type" = "ORACLE",
   -- Connect to the URL of the relational database through JDBC. (The URL formats vary according to databases.)
   "qubit.sql.jdbc.url" = "jdbc:oracle:thin:@//10.163.0.1:1521/mydb",
   -- Relational database driver class type
   "qubit.sql.jdbc.driver" = "oracle.jdbc.OracleDriver",
   -- SQL statement queried in the relational database. The result is returned to the Hive table.
   "qubit.sql.query" = "select name from aaa",
   -- (Optional) Match the Hive table columns to the relational database table columns.
   "qubit.sql.column.mapping" = "id=name",
   -- Relational database user
   "qubit.sql.dbcp.username" = "test",
   -- Relational database password. Commands carrying authentication passwords pose security risks. Disable historical command recording before running such commands to prevent information leakage.
   "qubit.sql.dbcp.password" = "xxx");

Overview

Hive supports the following types of traditional relational database syntax:

• Grouping
• EXCEPT and INTERSECT

Grouping

Syntax description:

• Grouping takes effect only when the Group by statement contains ROLLUP or CUBE.
• The result set generated by CUBE contains all the combinations of values in the selected columns.
• The result set generated by ROLLUP contains the combinations of a certain layer structure in the selected columns.
• Grouping: If a row is added by using the CUBE or ROLLUP operator, the output value of the added row is 1. If the row is not added by using the CUBE or ROLLUP operator, the output value of the added row is 0.

For example, the table_test table exists in Hive and the table structure is as follows:

+----------------+-------------------+--+
| table_test.id  | table_test.value  |
+----------------+-------------------+--+
| 1              | 10                |
| 1              | 15                |
| 2              | 20                |
| 2              | 5                 |
| 2              | 13                |
+----------------+-------------------+--+

Run the following statement:

select id,grouping(id),sum(value) from table_test group by id with rollup;

The result is as follows:

+-------+-----------------+------+--+
|  id   | groupingresult  | sum  |
+-------+-----------------+------+--+
| 1     | 0               | 25   |
| NULL  | 1               | 63   |
| 2     | 0               | 38   |
+-------+-----------------+------+--+

EXCEPT and INTERSECT

Syntax description:

• EXCEPT returns the difference of two result sets (that is, non-duplicated values return only one query).
• INTERSECT returns the intersection of two result sets (that is, non-duplicated values return by both queries).

For example, two tables test_table1 and test_table2 exist in Hive.

The table structure of test_table1 is as follows:

+-----------------+--+
| test_table1.id  |
+-----------------+--+
| 1               |
| 2               |
| 3               |
| 4               |
+-----------------+--+

The table structure of test_table2 is as follows:

+-----------------+--+
| test_table2.id  |
+-----------------+--+
| 2               |
| 3               |
| 4               |
| 5               |
+-----------------+--+

• Run the following EXCEPT statement:

  select id from test_table1 except select id from test_table2;

  The result is as follows:

  +--------------+--+
  | _alias_0.id  |
  +--------------+--+
  | 1            |
  +--------------+--+

• Run the following INTERSECT statement:

  select id from test_table1 intersect select id from test_table2;

  The result is as follows:

  +--------------+--+
  | _alias_0.id  |
  +--------------+--+
  | 2            |
  | 3            |
  | 4            |
  +--------------+--+

Function

AddDoublesUDF is used to add two or more floating point numbers. In this example, you can learn how to write and use UDFs.

• A common UDF must be inherited from org.apache.hadoop.hive.ql.exec.UDF.
• A common UDF must implement at least one evaluate(). The evaluate function supports overloading.
• To develop a UDF, add the hive-exec-*.jar dependency package to the project. You can obtain the package from the Hive service installation directory, for example, ${BIGDATA_HOME}/components/FusionInsight_HD_*/Hive/disaster/plugin/lib/.

Sample Code

The following is a UDF code example:

xxx indicates the name of the organization that develops the program.

package com.xxx.bigdata.hive.example.udf;
import org.apache.hadoop.hive.ql.exec.UDF;

public class AddDoublesUDF extends UDF { 
 public Double evaluate(Double... a) { 
    Double total = 0.0; 
    // Processing logic
    for (int i = 0; i < a.length; i++) 
      if (a[i] != null) 
        total += a[i]; 
    return total; 
  } 
} 

How to Use

1. Packing programs as AddDoublesUDF.jar on the client node, and upload the package to a specified directory in HDFS, for example, /user/hive_examples_jars.

   Both the user who creates the function and the user who uses the function must have the read permission on the file.

   The following are example statements:

   hdfs dfs -put ./hive_examples_jars /user/hive_examples_jars

   hdfs dfs -chmod 777 /user/hive_examples_jars

2. Check the cluster authentication mode.
   • In security mode, log in to the beeline client as a user with the Hive management permission and run the following commands:

     kinit Hive service user

     beeline

     set role admin;

   • In common mode, run the following command:

     beeline -n Hive service user

3. Define the function in HiveServer. Run the following SQL statement to create a permanent function:

   CREATE FUNCTION addDoubles AS 'com.xxx.bigdata.hive.example.udf.AddDoublesUDF' using jar 'hdfs://hacluster/user/hive_examples_jars/AddDoublesUDF.jar';

   addDoubles is the alias of the function, which is used in SELECT queries. xxx is typically the name of the organization that develops the program.

   Run the following statement to create a temporary function:

   CREATE TEMPORARY FUNCTION addDoubles AS 'com.xxx.bigdata.hive.example.udf.AddDoublesUDF' using jar 'hdfs://hacluster/user/hive_examples_jars/AddDoublesUDF.jar';

   • addDoubles indicates the function alias that is used for SELECT query.
   • TEMPORARY indicates that the function is used only in the current session with the HiveServer.

4. Run the following SQL statement to use the function on the HiveServer:

   SELECT addDoubles(1,2,3);

   If an [Error 10011] error is displayed when you log in to the client again, run the reload function; command and then use this function.

5. Run the following SQL statement to delete the function from the HiveServer:

   DROP FUNCTION addDoubles;

Extended Applications

None

Scenario

• When the beeline client is disconnected due to network exceptions during the execution of a batch processing task, tasks submitted before beeline is disconnected can be properly executed in Hive. When you start the batch processing task again, the submitted tasks are not executed and tasks that are not executed are executed in sequence.
• When the HiveServer service breaks down due to some reasons during the execution of a batch processing task, Hive enables that the tasks that have been successfully executed are not executed again when the same batch processing task is started again. The execution starts from the task that has not been executed from the time when HiveServer2 breaks down.

Example

1. Beeline is reconnected after being disconnection.

   Example:

   beeline -e "${SQL}" --hivevar batchid=xxxxx

2. Beeline kills the running tasks.

   Example:

   beeline -e "" --hivevar batchid=xxxxx --hivevar kill=true

3. Log in to the beeline client and start the mechanism of reconnection after disconnection.

   Log in to the beeline client and run the set hivevar:batchid=xxxx command.

   Instructions:

   • xxxx indicates the batch ID of tasks submitted in the same batch using the beeline client. Batch IDs can be used to identify the task submission batch. If the batch ID is not contained when a task is submitted, this feature is not enabled. The value of xxxx is specified during task execution. In the following example, the value of xxxx is 012345678901.

     beeline -f hdfs://hacluster/user/hive/table.sql --hivevar batchid=012345678901

   • If the running SQL script depends on the data timeliness, you are advised not to enable the breakpoint reconnection mechanism. You can use a new batch ID to submit tasks. During reexecution of the scripts, some SQL statements have been executed and are not executed again. As a result, expired data is obtained.
   • If some built-in time functions are used in the SQL script, it is recommended that you do not enable the breakpoint reconnection mechanism or the use of a new batch ID for each execution. The reason is the same as above.
   • A SQL script contains one or more subtasks. If the logic for deleting and creating temporary tables exist in the SQL script, it is recommended that the logic for deleting temporary tables be placed at the end of the script. If the subtasks executed after the temporary table deletion task fail to be executed and the temporary table is used in the subtasks before the temporary table deletion task, when the SQL script is executed using the same batch ID for the next time, the compilation of the subtasks (excluding the task for creating the temporary table because the creation has been completed and is not executed again, and only compilation is allowed) executed before the temporary table deletion task fails because the temporary has been deleted. In this case, you are advised to use a new batch ID to execute the script.

   Parameter description:

   • zk.cleanup.finished.job.interval: indicates the interval for executing the cleanup task. The default interval is 60 seconds.
   • zk.cleanup.finished.job.outdated.threshold: indicates the threshold of the node validity period. A node is generated for tasks in the same batch. The threshold is calculated from the end time of the execution of the current batch task. If the time exceeds 60 minutes, the node is deleted.
   • batch.job.max.retry.count: indicates the maximum number of retry times of a batch task. If the number of retry times of a batch task exceeds the value of this parameter, the task execution record is deleted. The task will be executed from the first task when the task is started next time. The default value is 10.
   • beeline.reconnect.zk.path: indicates the root node for storing task execution progress. The default value for the Hive service is /beeline.

Scenario

This function is supported on Hive and Spark.

With this function enabled, if the select permission is granted to a user during Hive table creation, the user can run the show create table command to view the table structure.

Procedure

1. Log in to FusionInsight Manager. For details, see Accessing FusionInsight Manager of an MRS Cluster. Click Cluster, choose Services > Hive, click Configurations, and then All Configurations.
2. Choose HiveServer(Role) > Customization, add a customized parameter to the hive-site.xml parameter file, set Name to hive.allow.show.create.table.in.select.nogrant, and set Value to true. Restart all Hive instances after the modification.
3. Determine whether to enable this function on the Spark client.
   • If yes, download and install the Spark client again.
   • If no, no further action is required.

Scenario

This function applies to Hive.

After this function is enabled, run the following command to write a directory into Hive: insert overwrite directory "/path1" .... After the operation is successfully performed, the old data is removed to the recycle bin, and the directory cannot be an existing database path in the Hive metastore.

1. Log in to FusionInsight Manager. For details, see Accessing FusionInsight Manager of an MRS Cluster. Click Cluster, choose Services > Hive, click Configurations, and then All Configurations.
2. Choose HiveServer(Role) > Customization, add a customized parameter to the hive-site.xml parameter file, set Name to hive.overwrite.directory.move.trash, and set Value to true. Restart all Hive instances after the modification.

Scenario

This function applies to Hive.

With this function enabled, run the insert overwrite directory /path1/path2/path3... command to write a subdirectory. The permission of the /path1/path2 directory is 700, and the owner is the current user. If the /path3 directory does not exist, it is automatically created and data is written successfully.

This function is supported when hive.server2.enable.doAs is set to true in earlier versions. This version supports the function when hive.server2.enable.doAs is set to false.

The parameter adjustment of this function is the same as that of the custom parameters added in Writing a Directory into Hive with the Old Data Removed to the Recycle Bin.

Procedure

1. Log in to FusionInsight Manager. For details, see Accessing FusionInsight Manager of an MRS Cluster. Click Cluster, choose Services > Hive, click Configurations, and then All Configurations.
2. Choose HiveServer(Role) > Customization, add a customized parameter to the hive-site.xml parameter file, set Name to hive.overwrite.directory.move.trash, and set Value to true. Restart all Hive instances after the modification.

Scenario

This function is supported on Hive and Spark.

After this function is enabled, only the Hive administrator can create databases and tables in the default database. Other users can use the databases only after being authorized by the Hive administrator.

• After this function is enabled, common users are not allowed to create a database or create a table in the default database. Based on the actual application scenario, determine whether to enable this function.
• Permissions of common users are restricted. In the scenario where common users have been used to perform operations, such as database creation, table script migration, and metadata recreation in an earlier version of database, the users can perform such operations on the database in the condition that this function is disabled temporarily after the database is migrated or after the cluster is upgraded.

Procedure

1. Log in to FusionInsight Manager. For details, see Accessing FusionInsight Manager of an MRS Cluster. Click Cluster, choose Services > Hive, click Configurations, and then All Configurations.
2. Choose HiveServer(Role) > Customization, add a customized parameter to the hive-site.xml parameter file, set Name to hive.allow.only.admin.create, and set Value to true. Restart all Hive instances after the modification.
3. Determine whether to enable this function on the Spark client.
   • If yes, go to Step 4.
   • If no, no further action is required.

4. Choose SparkResource > Customization, add a customized parameter to the hive-site.xml parameter file, and set Name to hive.allow.only.admin.create and Value to true. Then, choose JDBCServer > Customization and repeat the preceding operations to add the customized parameter. Restart all Spark instances after the modification.
5. Download and install the Spark client again.

Scenario

This function applies to Hive and Spark.

After this function is enabled, the location keyword cannot be specified when a Hive internal table is created. Specifically, after a table is created, the table path following the location keyword is created in the default \warehouse directory and cannot be specified to another directory. If the location is specified when the internal table is created, the creation fails.

After this function is enabled, the location keyword cannot be specified during the creation of a Hive internal table. The table creation statement is restricted. If a table that has been created in the database is not stored in the default directory /warehouse, the location keyword can still be specified when the database creation, table script migration, or metadata recreation operation is performed by disabling this function temporarily.

Procedure

1. Log in to FusionInsight Manager. For details, see Accessing FusionInsight Manager of an MRS Cluster. Click Cluster, choose Services > Hive, click Configurations, and then All Configurations.
2. Choose HiveServer(Role) > Customization, add a customized parameter to the hive-site.xml parameter file, set Name to hive.internaltable.notallowlocation, and set Value to true. Restart all Hive instances after the modification.
3. Determine whether to enable this function on the Spark client.
   • If yes, download and install the Spark client again.
   • If no, no further action is required.

Scenario

This function applies to Hive and Spark.

After this function is enabled, the user or user group that has the read and execute permissions on a directory can create foreign tables in the directory without checking whether the current user is the owner of the directory. In addition, the directory of a foreign table cannot be stored in the default directory \warehouse. In addition, do not change the permission of the directory during foreign table authorization.

After this function is enabled, the function of the foreign table changes greatly. Based on the actual application scenario, determine whether to enable this function.

Procedure

1. Log in to FusionInsight Manager. For details, see Accessing FusionInsight Manager of an MRS Cluster. Click Cluster, choose Services > Hive, click Configurations, and then All Configurations.
2. Choose HiveServer(Role) > Customization, add a customized parameter to the hive-site.xml parameter file, set Name to hive.restrict.create.grant.external.table, and set Value to true.
3. Choose MetaStore(Role) > Customization, add a customized parameter to the hivemetastore-site.xml parameter file, set Name to hive.restrict.create.grant.external.table, and set Value to true. Restart all Hive instances after the modification.
4. Determine whether to enable this function on the Spark client.
   • If yes, download and install the Spark client again.
   • If no, no further action is required.

Scenario

This function applies to Hive.

The number of OS user groups is limited, and the number of roles that can be created in Hive cannot exceed 32. After this function is enabled, more than 32 roles can be created in Hive.

• After this function is enabled and the table or database is authorized, roles that have the same permission on the table or database will be combined using vertical bars (|). When the ACL permission is queried, the combined result is displayed, which is different from that before the function is enabled. This operation is irreversible. Determine whether to make adjustment based on the actual application scenario.
• If the current component uses Ranger for permission control, you need to configure related policies based on Ranger for permission management. For details, see Adding a Ranger Access Permission Policy for Hive.
• After this function is enabled, a maximum of 512 roles (including owner) are supported by default. The number is controlled by the user-defined parameter hive.supports.roles.max of MetaStore. You can change the value based on the actual application scenario.

Procedure

1. Log in to FusionInsight Manager. For details, see Accessing FusionInsight Manager of an MRS Cluster. Click Cluster, choose Services > Hive, click Configurations, and then All Configurations.
2. Choose MetaStore(Role) > Customization, add a custom parameter to the hivemetastore-site.xml parameter file, and set Name to hive.supports.over.32.roles and Value to true. Restart all MetaStore instances after the modification.

Scenario

• This function applies to Hive.
• This function is used to limit the maximum number of maps for Hive tasks on the server to avoid performance deterioration caused by overload of the HiveSever service.

Procedure

1. Log in to FusionInsight Manager. For details, see Accessing FusionInsight Manager of an MRS Cluster. Click Cluster, choose Services > Hive, click Configurations, and then All Configurations.
2. Choose MetaStore(Role) > Customization, add a customized parameter to the hivemetastore-site.xml parameter file, set Name to hive.mapreduce.per.task.max.splits, and set the parameter to a large value. Restart all Hive instances after the modification.

Scenario

• This function applies to Hive.
• This function can be enabled to specify specific users to access HiveServer services on specific nodes, achieving HiveServer resource isolation.

Procedure

This section describes how to set lease isolation for user hiveuser for existing HiveServer instances.

1. Log in to FusionInsight Manager.
2. Choose Cluster > Services > Hive and click HiveServer.
3. In the HiveServer list, select the HiveServer for which lease isolation is configured and choose HiveServer > Instance Configurations > All Configurations.
4. In the upper right corner of the All Configurations page, search for hive.server2.zookeeper.namespace and specify its value, for example, hiveserver2_zk.
5. Click Save. In the dialog box that is displayed, click OK.
6. Choose Cluster > Services > Hive. Click More and select Restart Service. In the dialog box displayed, enter the password to restart the service.
7. Run the beeline -u command to log in to the client and run the following command:

   beeline -u "jdbc:hive2://10.5.159.13:24002/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2_zk;sasl.qop=auth-conf;auth=KERBEROS;principal=hive/hadoop.<System domain name>@<System domain name>"

   Replace 10.5.159.13 with the IP address of any ZooKeeper instance. To query the IP address, choose Cluster > Services > ZooKeeper and click Instance.

   hiveserver2_zk following zooKeeperNamespace= is set to the value of hive.server2.zookeeper.namespace in Step 4.

   As a result, only the HiveServer whose lease isolation is configured can be logged in.

   • After this function is enabled, you must run the preceding command during login to access the HiveServer for which lease isolation is configured. If you run the beeline command to log in to the client, only the HiveServer that is not isolated by the lease is accessed.
   • You can log in to FusionInsight Manager, choose System > Permission > Domain and Mutual Trust, and view the value of Local Domain, which is the current system domain name. hive/hadoop.<system domain name> is the username. All letters in the system domain name contained in the username are lowercase letters.

Scenario

This function restricts components in a cluster to connect to specified Hive Metastore instances. By default, components can connect to all Metastore instances.

Currently, only HetuEngine, Hive, Loader, Spark, and Flink can connect to Metastore in a cluster. The Metastore instances can be allocated in a unified manner.

• This function only limits the Metastore instances accessed by component servers. Metadata is not isolated.
• Currently, Flink tasks can only connect to Metastore instances through the client.
• When spark-sql is used to execute tasks, the client is directly connected to Metastore. The client needs to be updated for the isolation to take effect.
• This function supports only isolation in the same cluster. If HetuEngine is deployed in different clusters, unified isolation configuration is not supported. You need to modify the HetuEngine configuration to connect to the specified Metastore instance.
• You are advised to configure at least two Metastore instances for each component to ensure availability during isolation configuration.

Prerequisites

The Hive service has been installed in the cluster and is running properly.

Procedure

1. Log in to FusionInsight Manager and choose Cluster > Services > Hive. On the displayed page, click the Configurations tab and then All Configurations, and search for the HIVE_METASTORE_URI parameter.
2. Set the value of HIVE_METASTORE_URI_DEFAULT to the URI connection string of all Metastore instances.

3. Connect a component to a specified Metastore instance. Copy the value in Step 2, modify the configuration items based on the component name, save the modification, and restart the component.
   The following example shows how Spark connects only to two Metastore instances of Hive.

   1. Log in to FusionInsight Manager and choose Cluster > Services > Hive. On the displayed page, click the Configurations tab and then All Configurations, and search for the HIVE_METASTORE_URI parameter.
   2. Copy the default configuration of HIVE_METASTORE_URI_DEFAULT to the URI configuration item of Spark. If Spark needs to connect only to two Metastore instances, retain two nodes as required. Click Save.

   3. Choose Cluster > Services > Spark. Click Instance, select the instances whose configuration has expired, click More, and select Restart Instance. In the dialog box that is displayed, enter the password and click OK to restart the instances.

Scenario

Hive can use the Tez engine to process data computing tasks. Before executing a task, you can manually switch the execution engine to Tez.

Prerequisites

The TimelineServer role of the Yarn service has been installed in the cluster and is running properly.

Switching the Execution Engine on the Client to Tez

1. Install and log in to the Hive client. For details, see Using a Hive Client.
2. Run the following command to switch the engine:
   set hive.execution.engine=tez;

   • To specify a running Yarn queue, run the set tez.queue.name=default command on the client.

3. Submit and execute the Tez tasks.
4. Log in to FusionInsight Manager. Choose Cluster > Name of the desired cluster > Services > Tez > TezUI(host name) to view the task execution status on the TezUI page.

Switching the Default Execution Engine of Hive to Tez

1. Log in to FusionInsight Manager. Choose Cluster > Name of the desired cluster > Services > Hive > Configurations > All Configurations > HiveServer(Role), and search for hive.execution.engine.
2. Set hive.execution.engine to tez.
3. Click Save. In the displayed confirmation dialog box, click OK.
4. Choose Dashboard > More > Restart Service to restart the Hive service. Enter the password to restart the service.
5. Install and log in to the Hive client. For details, see Using a Hive Client.
6. Submit and execute the Tez tasks.
7. Log in to FusionInsight Manager and choose Cluster > Name of the desired cluster > Services > Tez > TezUI(host name). On the displayed TezUI page, view the task execution status.

Scenario

The MetaStore service of Hive can cache the metadata of some tables in Redis.

• The same external RDS and Redis cache cluster can be configured for multiple Hive clusters to implement metadata sharing among multiple clusters. In multi-cluster scenarios, only Redis clusters in normal mode can be configured.
• If the Redis cache is abnormal, MetaStore automatically delivers requests to the metabase, which does not affect SQL execution.

Prerequisites

The Redis service has been installed in a cluster.

Configure Parameters Related to Metastore

1. Log in to FusionInsight Manager and choose Cluster > Services > Hive. Click Configurations, search for hive.metastore.rawstore.impl, and change its value to org.apache.hadoop.hive.metastore.cache.redis.RedisCachedStore (Redis cache implementation class) to enable the Redis cache function of MetaStore.
2. Click All Configurations, click MetaStore(Role), select Custom, and modify the following parameters to connect the MetaStore cache to the Redis service:
   Table 2-812 Parameters

   Parameter	Value	Description
   redis.cluster.host.and.port	xxx.xxx.xxx.xxx:22400;xxx.xxx.xxx.xxx.xxx:22401	Mandatory. Service IP address and port number of any node in the Redis cluster, in the format of IP address:Port number;IP address:Port number. The value cannot end with a semicolon (;).
   metastore.cached.rawstore.cached.object.whitelist	catalog.database.table,catalog.database.table	Optional. Cache table list. Tables that are listed will be cached to Redis. You are advised to list tables that contain over 100,000 partitions. Multiple tables are separated by commas (,). The default separator is .*, that is, all tables are cached. NOTE: The table name consists of catalog.database.table. The default catalog is hive. If there are excessive tables, default separator .* will cause large volumes of data cannot be uploaded to Redis within the prewarm period.
   redis.cache.prewarm.cron	cronTab expression, for example, 0 0 16 * *?	(Optional) Periodically execute the corn expression of prewarm to update the data cached in the metabase to redisCache for synchronization. NOTE: You are adivsed to configure expression execution once a day during off-peak hours. Partitioned tables are synchronized only when the number of partitions in the metabase is inconsistent with that in the Redis cache.
   metastore.cached.rawstore.catalogs	hive	(Optional) Catalog to be cached. The default value is hive.
   jedis.pool.max.wait.mills	30,000	(Optional) Obtain the Redis connection timeout interval. In security mode, the timeout interval can be longer. The unit is ms. The default value is 30,000 ms.
   jedis.pool.max.idle	200	(Optional) Maximum number of idle connections in the Jedis connection pool. You are advised to set this parameter to the value of max.total. The default value is 200.
   jedis.pool.max.total	200	(Optional) Maximum number of connections in the Jedis connection pool. The default value is 200.
   redis.security.enabled	true or false	(Optional) Whether to enable the Redis cache security mode. The default value is true, indicating that the security mode is enabled.

3. Save the configuration and choose Dashboard > More > Restart Service to restart the Hive service.

Precautions

If Redis is switched back to the native non-cache mode and then switched back after a period of time, the added, deleted, or modified metadata cannot be synchronized to the Redis when the database is used. Therefore, before switching back, you must clear the cache table in the Redis and synchronize the metadata again in either of the following two clearing modes:

• Log in to the Redis client and run the flushall command on all Redis nodes.
• Log in to the Redis client and run the following commands to change the two Redis identifiers. {hiveServiceName} is the value of HIVE_DEFAULT_GROUP in the Metastore configuration file ENV_VARS. The default value is hive.

  set {hiveServiceName}-hive-isRedisAvailable false

  del {hiveServiceName}-hive-isCanPrewarm

Hive External Tables Corresponding to Hudi Tables

A Hudi source table corresponds to a copy of HDFS data. The Hudi table data can be mapped to a Hive external table through the Spark component, Flink component, or Hudi client. Based on the external table, Hive can easily perform real-time query, read-optimized view query, and incremental view query.

• Different view queries are provided for different types of Hudi source tables:
  • When the Hudi source table is a Copy-On-Write (COW) table, it can be mapped to a Hive external table. The table supports real-time query and incremental view query.
  • When the Hudi source table is a Merge-On-Read (MOR) table, it can be mapped to two Hive external tables (RO table and RT table). The RO table supports read-optimized view query, and the RT table supports real-time view query and incremental view query.

• Hive external tables cannot be added, deleted, or modified (including insert, update, delete, load, merge, alter and msck). Only the query operation (select) is supported.
• Granting table permissions: The update, alter, write, and all permissions cannot be modified.
• Backup and restoration: The RO and RT tables are mapped from the same Hudi source table. When one table is backed up, the other table is also backed up. The same applies to restoration. Therefore, only one table needs to be backed up.
• Component versions:
  • Hive: FusionInsight_HD_8.3.1; Hive kernel version 3.1.0
  • Spark: FusionInsight_Spark_8.3.1; Hudi kernel version 0.11.0

Creating Hive External Tables Corresponding to Hudi Tables

Generally, Hudi table data is synchronized to Hive external tables when the data is imported to the lake. In this case, you can directly query the corresponding Hive external tables in Beeline. If the data is not synchronized to the Hive external tables, you can use the Hudi client tool run_hive_sync_tool.sh to synchronize data manually.

Querying Hive External Tables Corresponding to Hudi Tables

Prerequisites

Before using Hive to perform incremental query on Hudi tables, you need to set another three parameters in Table 2-813. The three parameters are table-level parameters. Each Hudi source table corresponds to three parameters, where hudisourcetablename indicates the name of the Hudi source table (not the name of the Hive external table).

Querying a Hudi COW Table

For example, the name of a Hudi source table of the COW type is hudicow, and the name of the mapped Hive external table is hudicow.

• Real-time view query on the COW table:

  Select * from hudicow;

• Incremental query on the COW table: Set three incremental query parameters based on the name of the Hudi source table. The where clause of the incremental query statements must contain `_hoodie_commit_time`>'xxx', where xxx indicates the value of hoodie.hudisourcetablename.consume.start.timestamp.

  set hoodie.hudicow.consume.mode= INCREMENTAL;

  set hoodie.hudicow.consume.max.commits=3;

  set hoodie.hudicow.consume.start.timestamp= 20200427114546;

  select count(*) from hudicow where `_hoodie_commit_time`>'20200427114546';

Querying a Hudi MOR Table

For example, the name of a Hudi source table of the MOR type is hudimor, and the two mapped Hive external tables are hudimor_ro (RO table) and hudimor_rt (RT table).

• Read-optimized view query on the RO table:

  Select * from hudicow_ro;

• Real-time view query on the RT table:

  Select * from hudicow_rt;

• Incremental query on the RT table: Set three incremental query parameters based on the name of the Hudi source table. The where clause of the incremental query statements must contain `_hoodie_commit_time`>'xxx', where xxx indicates the value of hoodie.hudisourcetablename.consume.start.timestamp.

  set hoodie.hudimor.consume.mode=INCREMENTAL;

  set hoodie.hudimor.consume.max.commits=-1;

  set hoodie.hudimor.consume.start.timestamp=20210207144611;

  select * from hudimor_rt where `_hoodie_commit_time`>'20210207144611';

  set hoodie.hudisourcetablename.consume.mode=INCREMENTAL; is used only for the incremental query on the table. To switch to another query mode, run set hoodie.hudisourcetablename.consume.mode=SNAPSHOT;.

Querying Hive External Tables Corresponding to Hudi Schema Evolution Tables

If the Hudi table is a schema evolution table (some fields in the table have been modified), you need to add set hive.exec.schema.evolution=true; when Hive queries the table.

The following uses the query of the real-time view of a COW table as an example. To query other views, you need to add this parameter.

• Real-time view query on the COW table:

  set hive.exec.schema.evolution=true;

  select * from hudicow;

Cold and Hot Storage of Partitioned Metadata

• The metadata that have not been used for a long time is moved to a backup table to reduce the pressure on metadata databases. This process is called partitioned data freezing. The partitions in which data is moved are cold partitions, partitions that are not frozen are hot partitions. A table with a cold partition is a frozen table. Moving the frozen data back to the original metadata table is called partitioned data unfreezing.
• When a partition is changed from a hot partition to a cold partition, only metadata is identified. The partition path and data file content on the HDFS service side do not change.

Freezing a Partition

The user who creates the table can freeze one or more partitions based on filter criteria. The format is freeze partitions Database name Table name where Filter criteria.

Example:

freeze partitions testdb.test where year <= 2021;
freeze partitions testdb.test where year<=2021 and month <= 5;
freeze partitions testdb.test where year<=2021 and month <= 5 and day <= 27;

Unfreezing a Partition

The user who creates the table can unfreeze one or more partitions based on filter criteria. The format is unfreeze partitions Database name Table name where Filter criteria. Example:

unfreeze partitions testdb.test where year <= 2021;
unfreeze partitions testdb.test where year<=2021 and month <= 5;
unfreeze partitions testdb.test where year<=2021 and month <= 5 and day <= 27;

Querying Tables with Frozen Data

• Querying all frozen tables in the current database

  show frozen tables;

• Querying all frozen tables in the dbname database

  show frozen tables in dbname;

Querying Frozen Partitions of a Frozen Table

Querying frozen partitions

show frozen partitions table;

• By default, only partitions of the int, string, varchar, date, or timestamp type can be frozen in the metadata database.
• For external metadata databases, only the Postgres database is supported, and only partitions of the int, string, varchar, or timestamp type can be frozen.
• You need to unfreeze data to restore the metadata of a frozen table using MSCK. If a frozen table has been backed up, you can run msck repair to restore the table, and you can only run this command to unfreeze the table.
• You need to unfreeze data before renaming a frozen partition. Otherwise, a message indicating that the partition does not exist is displayed.
• When a table that contains frozen data is deleted, the frozen data is also deleted.
• When a partition that contains frozen data is deleted, information about the frozen partition and HDFS service data is not deleted.
• When you run the select command to query data, the criteria for filtering the data in cold partitions is automatically added. The query result does not contain the data in cold partitions.
• When you run the show partitions table command to query the partitioned data in the table, the query result does not contain the data in cold partitions. You can run the show frozen partitions table command to query frozen partitions.
• If Redis-based CacheStore is enabled, cold and hot storage of metadata is not supported.

Scenario

In the scenario where storage and compute resources are separated, you can specify different storage sources, for example, OBS or HDFS, for partitions in a Hive partitioned table.

Prerequisites

The Hive client has been installed. For details, see Installing a Client.

Example

1. Log in to the node where the Hive client is installed as the Hive client installation users.
2. Run the following command to go to the client installation directory:

   cd Client installation directory

   For example, if the client installation directory is /opt/hadoopclient, run the following command:

   cd /opt/hadoopclient

3. Run the following command to configure environment variables:

   source bigdata_env

4. Check whether the cluster authentication mode is in security mode.
   • If yes, run the following command to authenticate the user:

     kinit Hive service user

   • If no, go to 5.
5. Run the following command to log in to the Hive client:

   beeline

6. Run the following commands to create a Hive partitioned table named table_1, and set the path of partitions pt=?2021-12-12 and pt='2021-12-18 to hdfs//xxx and obs://xxx respectively:

   create table table_1(id string) partitioned by(pt string) [stored as [orc|textfile|parquet|...]];

   alter table table_1 add partition(pt='2021-12-12') location 'hdfs://xxx';

   alter table table_1 add partition(pt='2021-12-18') location 'obs://xxx';

7. After data is inserted into table_1, it is stored in the corresponding storage source. You can run the desc command to view the location of each partition.

   desc formatted table_1 partition(pt='2021-12-18');

Scenario

• Data files stored in Hive are abnormal due to misoperations or disk damage, thereby causing task execution failures or incorrect data results.
• Common non-text data files can be located using the specified tool.

Procedure

1. Log in to the node where the Hive service is installed as user omm and run the following command to go to the Hive installation directory:

   cd ${BIGDATA_HOME}/FusionInsight_HD_*/install/FusionInsight-Hive-*/hive-*/bin

2. Run the following tool to locate abnormal Hive files:

   sh hive_parser_file.sh [--help] <filetype> <command> <input-file|input-directory>

   Table 2-814 describes the related parameters.

   Note: You can run only one command at a time.

   Table 2-814 Parameter description

   Parameter	Description	Remarks
   filetype	Specifies the format of the data file to be parsed. Currently, only the ORC, RC (RCFile), and Parquet formats are supported.	Currently, data files in the RC format can only be viewed.
   -c	Prints the column information in the current metadata.	The column information includes the class name, file format, and sequence number.
   -d	Prints data in a data file. You can limit the data volume using the limit parameter.	The data is the content of the specified data file. Note that only one value can be specified for the limit parameter at a time.
   -t	Prints the time zone to which the data is written.	The time zone is the zone to which the file is written.
   -h	Prints the help information.	Help information.
   -m	Prints information about various storage formats.	The information varies based on the storage format. For example, if the file format is ORC, information such as strip and block size will be printed.
   -a	Prints detailed information.	The detailed information, including the preceding parameters, is displayed.
   input-file	Specifies the data files to be input.	If the input directory contains a file of the supported formats, the file will be parsed. Otherwise, this operation is omitted. You can specify a local file or an HDFS/OBS file or directory.
   input-directory	Specifies the directory where the input data file is located. This parameter is used when there are multiple subfiles.

3. Example:

   sh hive_parser_file.sh orc -d limit=100 hdfs://hacluster/user/hive/warehouse/orc_test

   If the file name does not contain a prefix similar to hdfs://hacluster, the local file is read by default.

Scenario

ZSTD_JNI is a native implementation of the ZSTD compression algorithm. Compared with ZSTD, ZSTD_JNI has higher compression read/write efficiency and compression ratio, and allows you to specify the compression level as well as the compression mode for data columns in a specific format.

Currently, only ORC tables can be compressed using ZSTD_JNI. By contrast, ZSTD enables you to compress tables in the full storage format. Therefore, you are advised to use this feature only when you have high requirements on data compression.

Example

1. Log in to the node where the client is installed as the Hive client installation user.
2. Run the following command to switch to the client installation directory, for example, /opt/hadoopclient:

   cd /opt/hadoopclient

3. Run the following command to configure environment variables:

   source bigdata_env

4. Check whether the cluster authentication mode is in security mode.
   • If yes, run the following command to perform user authentication and then go to Step 5.

     kinit Hive service user

   • If no, go to Step 5.

5. Run the following command to log in to the Hive client:

   beeline

6. Create a table in ZSTD_JNI compression format as follows:
   • Run the following example command to set the orc.compress parameter to ZSTD_JNI when using this compression algorithm to create an ORC table:

     create table tab_1(...) stored as orc TBLPROPERTIES("orc.compress"="ZSTD_JNI");

   • The compression level of ZSTD_JNI ranges from 1 to 19. A larger value indicates a higher compression ratio but a slower read/write speed. A smaller value indicates a lower compression ratio but a faster compression speed compared with read/write speed and the other way around. The default value is 6. You can set the compression level through the orc.global.compress.level parameter, as shown in the follows.

     create table tab_1(...) stored as orc TBLPROPERTIES("orc.compress"="ZSTD_JNI", 'orc.global.compress.level'='3');

   • This compression algorithm allows you to compress service data and columns in a specific data format. Currently, data in the following formats is supported: JSON data columns, Base64 data columns, timestamp data columns, and UUID data columns. You can achieve this function by setting the orc.column.compress parameter during table creation.

     The following example code shows how to use ZSTD_JNI to compress data in the JSON, Base64, timestamp, and UUID formats.

     create table test_orc_zstd_jni(f1 int, f2 string, f3 string, f4 string, f5 string) stored as orc

     TBLPROPERTIES('orc.compress'='ZSTD_JNI', 'orc.column.compress'='[{"type":"cjson","columns":"f2"},{"type":"base64","columns":"f3"},{"type ":"gorilla","columns":{"format": "yyyy-MM-dd HH:mm:ss.SSS", "columns": "f4"}},{"type":"uuid","columns":"f5"}]');

     You can insert data in the corresponding format based on the site requirements to further compress the data.

Scenario

There is high read and write pressure on a single underlying database when excessive number (even 10s of millions) of metadata tables or partitions are planned for services. This HiveMetaStore access pressure reduction feature is introduced to reduce read/write pressure by creating multiple relational databases to read and write metadata.

Procedure

1. Create a secondary relational database in HiveMetaStore. If the secondary relational database is DBService, go to Step 2. If the secondary relational database is MySQL, go to Step 3.
   • The type and version of the secondary relational database must be the same as those of the current storage database. Currently, a maximum of five secondary relational databases can be created.
   • The secondary relational database can be built-in DBService provided by MRS or external MySQL.

2. If the secondary relational database is DBService, perform the following operations:
   1. Log in to FusionInsight Manager and choose Cluster > Services. On the Services page that is displayed, click Add Service. In the Add Service dialog box, add a DBService, for example, named DBService-1. For details about how to add a service, see "Adding a Service" in MapReduce Service (MRS) 3.3.1-LTS User Guide (for Huawei Cloud Stack 8.3.1) in the MapReduce Service (MRS) 3.3.1-LTS Usage Guide (for Huawei Cloud Stack 8.3.1).
   2. Copy the ${BIGDATA_HOME}/FusionInsight_HD_*/install/FusionInsight-Hive-3.1.0/hive-3.1.0/scripts/multipalstore-migrationtool/ctlgs_dbs_export_import/postgresql directory on any node where Hive is installed to any temporary directory on the node where the primary database resides as user omm, and go to the directory as this user.
      1. Configure database parameters in the dbProperties.template file.

         EXPORT_DB_PORT=20051 #Port for the primary database to connect to other databases
         EXPORT_DB_NAME=hivemeta #Name of the Hive database in the primary database. The default value is hivemeta.
         IMPORT_DB_HOST= #IP address of the database connected to the secondary database. Enter the floating IP address if the primary database is a DBService database.
         IMPORT_DB_PORT=20051  #Port for the primary database to connect to other databases.
         IMPORT_DB_NAME=hivemeta #Name of the Hive database in the secondary database. The default value is hivemeta.
         IMPORT_DB_HIVE_USER= testuser #Owner created for the secondary database.
         IMPORT_TMP_PATH=/home/omm/testinit  #Specified empty path name of the node where the secondary database resides. Operations on this path require the read, write, and execute permissions of system user omm. This directory temporarily stores data exported from the primary database and is automatically deleted after the data is imported to the secondary database.

      2. Run the ctlgs_dbs_operator.sh script, enter the password of user IMPORT_DB_HIVE_USER that is configured in the dbProperties.template file. Run the following script to initialize data:

         sh ctlgs_dbs_operator.sh dbProperties.template

3. If the secondary relational database is a MySQL database, perform the following steps:
   1. Install and configure the MySQL database by referring to Interconnecting Hive with External Self-Built Relational Databases.
   2. Initialize the metadata in the default secondary relational database stored in HiveMetaStore and the table mapping information of the primary database.
      Copy the ${BIGDATA_HOME}/FusionInsight_HD_*/install/FusionInsight-Hive-3.1.0/hive-3.1.0/scripts/multipalstore-migrationtool/ctlgs_dbs_export_import/mysql directory on any node where Hive is installed to any temporary directory on the node where the primary database resides, and go to the directory.

      1. Configure database parameters in the dbProperties.template file.

         EXPORT_DB_USER= # User of the primary database
         EXPORT_DB_PORT=3306 #Port for the primary database to connect to other databases
         EXPORT_DB_NAME=hivemeta #Name of the Hive database in the primary database. The default value is hivemeta.
         IMPORT_DB_USER= #Username of the secondary database
         IMPORT_DB_HOST=  #IP address of the secondary database
         IMPORT_DB_PORT=  #Port number of the secondary database
         IMPORT_DB_NAME=hivemeta #Name of the Hive database in the secondary database. The default value is hivemeta.
         IMPORT_TMP_PATH= #Temporary path of the secondary database. The user who executes the ctlgs_dbs_operator.sh script must have the read, write, and execute permissions on the path.

      2. Run the ctlgs_dbs_operator.sh script, enter the passwords of users EXPORT_DB_USER and IMPORT_DB_USER that are configured in the dbProperties.template file. Run the following script to initialize data:

         sh ctlgs_dbs_operator.sh dbProperties.template

4. Perform the following steps to partially migrate metadata in the primary database. Otherwise, go to Step 5. Fully evaluate the impact on services before performing this operation and exercise caution when performing it.
   • Currently, only the metadata of the built-in DBService database can be migrated.
   • The Hive service must be stopped during data migration.

     Log in to FusionInsight Manager and choose Cluster > Services > Hive, and click Stop.

   • If the metabase is a DBService database, copy the initialization script directory ${BIGDATA_HOME}/FusionInsight_HD_*/install/FusionInsight-Hive-3.1.0/hive-3.1.0/scripts/multipalstore-migrationtool/migration_export_import/ on any node where the Hive service is installed to any temporary directory on the primary database node.
   • Set database parameters in the dbProperties.template file.

     EXPORT_DB_PORT=20051 #Port for the primary database to connect to other databases
     EXPORT_DB_NAME=hivemeta #Name of the Hive database in the primary database. The default value is hivemeta.
     IMPORT_DB_HOST= #IP address of the database interconnecting with the secondary database. Enter the floating IP address if the primary database is a DBService database.
     IMPORT_DB_PORT=20051 #Port number of the secondary database.
     IMPORT_DB_NAME=hivemeta #Name of the Hive database in the secondary database. The default value is hivemeta.
     IMPORT_DB_HIVE_USER= testuser #User of the new secondary database.
     IMPORT_TMP_PATH=/home/omm/testmigration  #Specified empty path name of the node where the secondary database resides. Operations on this path require the read, write, and execute permissions of system user omm. This directory temporarily stores data exported from the primary database and is automatically deleted after the data is imported to the secondary database.
     IMPORT_RDS_RDB_KEY= javax.jdo.option.ConnectionURL1 #JDBC link URL of the secondary database.

   • Execute the queryMaxPartitionTable.sh script to view partition table information in descending order of the number of partitions. For example, to view the 20 tables with the largest number of partitions, run the following command:

     sh queryMaxPartitionTable.sh dbProperties.template 20

   • Create a text file in the temporary migration directory on the primary database node. The file content is the name of the primary database to be migrated. The format is Table name Table name, and the following is an example:

     vim tbl_propertie

     default.testtable1
     default.testtable2
     default.testtable1
     testdb1.test1
     testdb2.test2

   • Run the migrationtool.sh script, enter the password of user IMPORT_DB_HIVE_USER that is configured in the IMPORT_DB_HIVE_USER file. Run the following script to migrate data:

     sh migrationtool.sh tbl_propertie dbProperties.template

   • After the migrationtool.sh script is successfully executed, run the delete.sh script to delete the migrated data from the primary database.

     sh delete.sh tbl_propertie dbProperties.template

5. Log in to FusionInsight Manager and choose Cluster > Services > Hive. On the page that is displayed, click the Configurations tab then the All Configurations sub-tab. In the navigation pane on the left, choose MetaStore(Role) and change the value of hive.metastore.rawstore.impl to org.apache.hadoop.hive.metastore.multi.MultiObjectStore.

   Choose MetaStore(Role) > MultiMetaDB and modify the following parameters:

   Table 2-815 Parameters

   Parameter	Description
   javax.jdo.option.ConnectionURLx	JDBC URL of the metadata in MetaStore If the secondary database is MySQL, the value is as follows: jdbc:mysql://MySQL IP address:MySQL port number/test?characterEncoding=utf-8 If the secondary database is DBService or PostgreSQL, the value is as follows: jdbc:postgresql://PostgreSQL IP address:PostgreSQL port number/hivemeta
   javax.jdo.option.ConnectionUserNamex	Username for connecting to the secondary relational database on MetaStore
   javax.jdo.option.ConnectionPasswordx	Password for connecting to the secondary relational database on MetaStore NOTE: The system automatically encrypts user passwords.

   Each secondary relational database has three configuration items: JDBC URL, username, and password. The numbers at the end of these three parameters of a secondary relational database are the same (1, 2, 3, 4, or 5). That is, a maximum of five secondary relational databases can be created.

   For example, if a secondary relational database is created, you need to configure javax.jdo.option.ConnectionURL1, javax.jdo.option.ConnectionUserName1, and javax.jdo.option.ConnectionPassword1.

6. (Optional) Add the metastore-ext.write.rdb.custom.address parameter so that new tables can be written in the specified RDB.

   Choose Cluster > Services > Hive, click Configurations then All Configurations, click MetaStore(Role), select Customization, and add the following configuration to the hive.metastore.customized.configs parameter: Name: metastore-ext.write.rdb.custom.address; Value: URL of the Metastore metadata JDBC, that is, javax.jdo.option.ConnectionURLx. Separate multiple values with commas (,) or semicolons (;).

   For example, the metadata of a new table is written to the RDB specified by javax.jdo.option.ConnectionURL1 and javax.jdo.option.ConnectionURL2.

7. Click Save. In the dialog box that is displayed, click OK to save the configuration.
8. Click the Dashboard tab. On this tab page, choose More > Restart Service, and enter the password to restart the Hive service.

Scenario

The client connection of Hive MetaStore supports load balancing. That is, heavy load of a single MetaStore node during heavy service traffic can be avoided by connecting to the node with the least connections based on the connection number recorded in ZooKeeper. Enabling this function does not affect the original connection mode.

Procedure

1. Log in to FusionInsight Manager, click Cluster, choose Services > Hive, click Configurations, and then All Configurations.
2. Search for the hive.metastore-ext.balance.connection.enable parameter and set its value to true.
3. Click Save.
4. Click Instance, select all instances, choose More > Restart Instance, enter the password, and click OK to restart all Hive instances.
5. For other components that connect to MetaStore, add the hive.metastore-ext.balance.connection.enable parameter and set its value to true.

   The following uses Spark as an example:

   1. Log in to FusionInsight Manager, choose Cluster > Services > Spark, and click Configurations.
   2. Click Customization, add a custom parameter hive.metastore-ext.balance.connection.enable to all hive-site.xml parameter files, set its value to true, and click Save.
   3. Click Instance, select all configuration-expired instances, choose More > Restart Instance, enter the password, and click OK to restart them.

   In the Hive multi-service or multi-instance scenario, add custom configuration parameter metastore-ext.balance.zookeeper.namespace to the parameter files (such as hive-site.xml and hdfs-site.xml) of other components that connect to MetaStore. Set this parameter to metastore for Hive, metastore-1 for Hive-1, and this rule applies. The value is the ZooKeeper directory of the MetaStore to be connected. You can log in to the ZooKeeper client and run the ls / command to check the directory.

   For example:

   • To enable this function, add custom parameter metastore-ext.balance.zookeeper.namespace and set it to metastore-1 if Spark uses Hive-1.
   • To enable this function, add custom parameter metastore-ext.balance.zookeeper.namespace and set it to metastore-2 if Spark uses Hive-2.

Scenario

INSERT OVERWRITE of table data is available in Hive. Historical data of tables or partitions is cleared before final data is written. If you perform INSERT OVERWRITE concurrently on the same table or partition, two tasks may delete the same historical data at the same time, or some data files written by a task are deleted by other tasks. This causes data disorder.

To avoid this problem, you can enable interception of concurrent INSERT OVERWRITE.

When the interception function will access ZooKeeper, which lowers query performance. If you do not perform concurrent INSERT OVERWRITE on tables and partitions, or use ALTER TABLE CONCATENATE for small file merging, you do not need to enable this function.

Procedure

1. Log in to FusionInsight Manager and choose Cluster > Services > Hive > Configurations.
2. Search for hive-ext.concurrency.overwrite.reject in and set its value to true.
3. Click Save, and then click OK to save the configuration.
4. Click Instance, select all instances, choose More > Restart Instance, enter the password, and click OK to restart all Hive instances.

After this function is enabled, the ZooKeeper service is used by default to record information about tables and partitions where INSERT OVERWRITE is executed. If other ZooKeeper services are needed for better performance, you need to configure the following Hive parameters:

• Change the value of hive.zookeeper.quorum.for.overwrite to the host name of the ZooKeeper you want to use.
• Change the value of hive.zookeeper.client.port.for.overwrite to the ZooKeeper port.

Log Description

Log path: The default save path of Hive logs is /var/log/Bigdata/hive/role name, the default save path of Hive1 logs is /var/log/Bigdata/hive1/role name, and the others follow the same rule.

• HiveServer: /var/log/Bigdata/hive/hiveserver (run log) and var/log/Bigdata/audit/hive/hiveserver (audit log)
• MetaStore: /var/log/Bigdata/hive/metastore (run log) and /var/log/Bigdata/audit/hive/metastore (audit log)
• WebHCat: /var/log/Bigdata/hive/webhcat (run log) and /var/log/Bigdata/audit/hive/webhcat (audit log)

Log archive rule: The automatic compression and archiving function of Hive is enabled. By default, when the size of a log file exceeds 40 MB (which is adjustable), the log file is automatically compressed. The naming rule of a compressed log file is as follows: <Original log name>-<yyyy-mm-dd_hh-mm-ss>.[ID].log.zip A maximum of 20 latest compressed files are reserved. The number of compressed files and compression threshold can be configured.

Log Levels

Table 2-817 describes the log levels supported by Hive.

Levels of run logs are ERROR, WARN, INFO, and DEBUG from the highest to the lowest priority. Run logs of equal or higher levels are recorded. The higher the specified log level, the fewer the logs recorded.

To modify log levels, perform the following operations:

1. Go to the All Configurations page of the Yarn service by referring to Modifying Cluster Service Configuration Parameters.
2. On the menu bar on the left, select the log menu of the target role.
3. Select a desired log level and save the configuration.

   The Hive log level takes effect immediately after being configured. You do not need to restart the service.

Log Formats

The following table lists the Hive log formats: