Connecting Hive with External RDS¶
Note
RDS indicates the relational database in this section. This section describes how to connect Hive with the open-source MySQL and Postgres databases.
After an external metadata database is deployed in a cluster with Hive data, the original metadata table will not be automatically synchronized. Therefore, before installing Hive, you need to check whether the metadata is external RDS or built-in DBService. If the metadata is external RDS, you need to configure the metadata to external RDS when installing Hive or when there is no Hive data. After the installation, the metadata cannot be modified. Otherwise, the original metadata will be lost.
Hive supports access to open source MySQL and Postgres metabases.
Install the open source MySQL or Postgres database.
Note
The node where the database is installed must be in the same network segment as the cluster, so that they can access each other.
Upload the driver package.
Postgres: Use the open source Postgres driver package to replace the existing one of the cluster. Upload the Postgres driver package postgresql-42.2.5.jar to the ${BIGDATA_HOME}/third_lib/Hive directory on all MetaStore instance nodes. To download the open-source driver package, visit https://repo1.maven.org/maven2/org/postgresql/postgresql/42.2.5/.
MySQL: Go to the MySQL official website (https://www.mysql.com/), select Downloads > Community > MySQL Connectors > Connector/J to download the driver package of the corresponding version, and upload the driver package to the ${BIGDATA_HOME}/third_lib/Hive directory on all RDSMetastore nodes.
Create a database in RDS as the specified database of Hive metadata.
Run the following command in Postgres or MySQL:
create database databasename;
Note
In the preceding command, databasename indicates the database name.
Import the SQL statements for creating metadata tables.
Path of the Postgres SQL file: ${BIGDATA_HOME}/FusionInsight_HD_8.1.2.2/install/FusionInsight-Hive-3.1.0/hive-3.1.0/scripts/metastore/upgrade/postgres/hive-schema-3.1.0.postgres.sql
Run the following command to import the SQL file to Postgres:
./bin/psql -U username -d databasename -f hive-schema-3.1.0.postgres.sql
Specifically:
./bin/psql is in the Postgres installation directory.
username indicates the username for logging in to Postgres.
databasename indicates the database name.
Path of the MySQL file: ${BIGDATA_HOME}/FusionInsight_HD_8.1.2.2/install/FusionInsight-Hive-3.1.0/hive-3.1.0/scripts/metastore/upgrade/mysql/hive-schema-3.1.0.mysql.sql
Run the following command to import the SQL file to the MySQL database:
./bin/mysql -u username -ppassword -Ddatabasename<hive-schema-3.1.0.mysql.sql
Specifically:
./bin/mysql is in the MySQL installation directory.
username indicates the user name for logging in to MySQL.
databasename indicates the database name.
To connect Hive to the open-source database, log in to FusionInsight Manager. For details, see Accessing FusionInsight Manager. Choose Cluster > Name of the desired cluster > Services > Hive > Configurations > All Configurations > Hive(Service) > MetaDB, modify the following parameters, and click Save.
Table 1 Parameter description¶ Parameter
Default Value
Description
javax.jdo.option.ConnectionDriverName
org.postgresql.Driver
Driver class for connecting metadata on MetaStore
If an external MySQL database is used, the value is:
com.mysql.jdbc.Driver
If an external Postgres database is used, the value is:
org.postgresql.Driver
javax.jdo.option.ConnectionURL
jdbc:postgresql://%{DBSERVICE_FLOAT_IP}%{DBServer}:%{DBSERVICE_CPORT}/hivemeta?socketTimeout=60
URL of the JDBC link of the MetaStore metadata
If an external MySQL database is used, the value is:
jdbc:mysql://MySQL IP address:MySQL port number/test? useSSL=false
If an external Postgres database is used, the value is:
jdbc:postgresql://Postgres IP address:Postgres port number/test
javax.jdo.option.ConnectionUserName
hive${SERVICE_INDEX}${SERVICE_INDEX}
Username for connecting to the metadata database on Metastore
Change the Postgres database password in MetaStore. Choose Cluster > Name of the desired cluster > Services > Hive > Configurations > All Configurations > MetaStore(Role) > MetaDB, modify the following parameters, and click Save.
Table 2 Parameter description¶ Parameter
Default Value
Description
javax.jdo.option.extend.ConnectionPassword
******
User password for connecting to the external metadata database on Metastore. The password is encrypted in the background.
Log in to each MetaStore background node and check whether the local directory /opt/Bigdata/tmp exists.
If yes, go to 8.
If no, run the following commands to create one:
mkdir -p /opt/Bigdata/tmp
chmod 755 /opt/Bigdata/tmp
Save the configuration. Choose Dashboard > More > Restart Service, and enter the password to restart the Hive service.