Importing and Exporting Hive Databases

Scenario

In big data application scenarios, Hive databases and all tables in these databases are usually migrated to another cluster. You can run the Hive database export and import commands to migrate a complete database.

The Hive database import and export function does not support encrypted tables, HBase external tables, Hudi tables, view tables, or materialized view tables.

Prerequisites

• If Hive databases are imported or exported across clusters and Kerberos authentication is enabled for both the source and destination clusters, configure cross-cluster mutual trust.
• If you want to run the dump or load command to import or export databases created by other users, grant the corresponding database permission to the users.
  • If Ranger authentication is not enabled for the cluster, log in to FusionInsight Manager to grant the administrator rights of the role to which the user belongs. For details, see section Creating a Hive Role.
  • If Ranger authentication is enabled for the cluster, grant users the permission to dump and load databases. For details, see Adding a Ranger Access Permission Policy for Hive.
• Enable the inter-cluster copy function in the source cluster and destination cluster.
• Configure the HDFS service address parameter for the source cluster to access the destination cluster.

  Log in to FusionInsight Manager of the source cluster, choose Cluster > Services > Hive, and click Configurations. On the page that is displayed, search for hdfs.site.customized.configs, add custom parameter dfs.namenode.rpc-address.haclusterX, and set it to Service IP address of the active NameNode node in the target cluster:RPC port. Add custom parameter dfs.namenode.rpc-address.haclusterX1 and set it to Service IP address of the standby NameNode node in the target cluster:RPC port. The RPC port of NameNode is 25000 by default. After saving the configuration, roll-restart the Hive service.

Procedure

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

   cd /opt/client

3. Run the following command to configure environment variables:

   source bigdata_env

4. If Kerberos authentication is enabled for the cluster, run the following command to authenticate the user. Otherwise, skip this step.

   kinit Hive service user

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

   beeline

6. Run the following command to create the dump_db database:

   create database dump_db;

7. Run the following command to switch to the dump_db database:

   use dump_db;

8. Run the following command to create the test table in the dump_db database:

   create table test(id int);

9. Run the following command to insert data to the test table:

   insert into test values(123);

10. Run the following command to set the dump_db database as the source of the replication policy:

    alter database dump_db set dbproperties ('repl.source.for'='replpolicy1');

    

    • You must have related permissions on the database when running the alter command to modify database attributes. To configure permissions, perform the following operations:
      • If Ranger authentication is not enabled for the cluster, log in to FusionInsight Manager to grant the administrator rights of the role to which the user belongs. For details, see section Creating a Hive Role.
      • If Ranger authentication is enabled for the cluster, grant users the permission to dump and load databases. For details, see Adding a Ranger Access Permission Policy for Hive.
    • Databases with replication policy sources configured can be deleted only after their replication policy sources are set to null. To do so, run the following command:

      alter database dump_db set dbproperties ('repl.source.for'='');

11. Run the following command to export the dump_db database to the /user/hive/test directory of the destination cluster:

    repl dump dump_db with ('hive.repl.rootdir'='hdfs://haclusterX/user/hive/test');

    The following is an example.

    

    

    • hacluster X is the value of haclusterX in new custom parameterdfs.namenode.rpc-address.haclusterX.
    • Ensure that the current user has the read and write permissions on the export directory to be specified.

12. Log in to the node where the client is installed in the destination cluster as the Hive client installation user, and perform 2 to 5.
13. Run the following command to import data from the dump_db database in the /user/hive/test directory to the load_db database:

    repl load load_db from 'hdfs://haclusterX/user/hive/test/XXX';

    

    • Replace hdfs://haclusterX/user/hive/test/XXX with the specific path in the dump_dir column in 11.
    • When the repl load command is used to import a database, pay attention to the following points when specifying the database name:
      • If the specified database does not exist, the database will be created during the import.
      • If the specified database exists and the value of hive.repl.ckpt.key of the database is the same as the imported path, skip the import operation.
      • If the specified database already exists and no table or function exists in this database, only the tables in the source database are imported to the current database during the import. Otherwise, the import fails.