如何在 Amazon EMR 上将 PostgreSQL 数据库用作 Hive 外部元数据仓？

解决方法

在开始之前，请注意以下几点：

• 此解决方案假定您已经有一个活跃的 PostgreSQL 数据库。
• 如果您使用的是 Amazon EMR 5.7 版或更低版本，请下载 PostgreSQL JDBC 驱动程序。然后，将该驱动程序添加到 Hive 库路径 (/usr/lib/hive/lib)。Amazon EMR 版本 5.8.0 及更高版本会在 Hive 库路径中附带 PostgreSQL JDBC 驱动程序。

要将 PostgreSQL 数据库实例配置为 Hive 的外部元数据仓，请执行以下操作：

1.    创建 Amazon RDS for PostgreSQL DB 数据库实例并创建数据库。请注意，您可以在 AWS 控制台中从 Amazon RDS 创建数据库实例时执行此操作。您可以在 Additional configuration（其他配置）项下的Initial database name（初始数据库名称）字段中指定数据库名称。或者，您可以连接 PostgreSQL 数据库实例，然后创建数据库。

2.    修改数据库实例安全组，以允许在端口 5432 上连接数据库和 ElasticMapReduce-master 安全组。有关更多信息，请参阅 VPC 安全组。

3.    在没有外部元数据仓的情况下启动 Amazon EMR 集群。在此情况下，Amazon EMR 使用默认的 MySQL 数据库。

4.    使用 SSH 连接到主节点。

5.    替换 Hive 配置的以下属性。
替换示例中的以下值：
mypostgresql.testabcd1111.us-west-2.rds.amazonaws.com 替换为数据库实例的端点
mypgdb 替换为 PostgreSQL 数据库的名称
database_username 替换为数据库实例用户名
database_password 替换为数据库实例密码

[hadoop@ip-X-X-X-X ~]$ sudo vi /etc/hive/conf/hive-site.xml

<property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:postgresql://mypostgresql.testabcd1111.us-west-2.rds.amazonaws.com:5432/mypgdb</value>
    <description>PostgreSQL JDBC driver connection URL</description>
  </property>

  <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>org.postgresql.Driver</value>
    <description>PostgreSQL metastore driver class name</description>
  </property>

  <property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>database_username</value>
    <description>the username for the DB instance</description>
  </property>

  <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>database_password</value>
    <description>the password for the DB instance</description>
  </property>

6.    运行以下命令，创建 PostgreSQL 架构：

[hadoop@ip-X-X-X-X ~]$ cd /usr/lib/hive/bin/
[hadoop@ip-X-X-X-X bin]$ ./schematool -dbType postgres -initSchema  
Metastore connection URL:     jdbc:postgresql://mypostgresql.testabcd1111.us-west-2.rds.amazonaws.com:5432/mypgdb
Metastore Connection Driver :     org.postgresql.Driver
Metastore connection User:     test
Starting metastore schema initialization to 2.3.0
Initialization script hive-schema-2.3.0.postgres.sql
Initialization script completed
schemaTool completed

7.    关闭并启动 Hive 服务，以使新设置生效：

[hadoop@ip-X-X-X-X bin]$ sudo initctl list |grep -i hive
hive-server2 start/running, process 11818
hive-hcatalog-server start/running, process 12708
[hadoop@ip-X-X-X-X9 bin]$ sudo stop hive-server2
hive-server2 stop/waiting
[hadoop@ip-X-X-X-X bin]$ sudo stop hive-hcatalog-server
hive-hcatalog-server stop/waiting
[hadoop@ip-X-X-X-X bin]$ sudo start hive-server2
hive-server2 start/running, process 18798
[hadoop@ip-X-X-X-X bin]$ sudo start hive-hcatalog-server
hive-hcatalog-server start/running, process 19614

您可以选择通过在 EMR 集群中将以下 bash 脚本 (hive_postgres_emr_step.sh) 作为步骤任务运行来自动执行上述过程中的步骤 5 到 7。

## Automated Bash script to update the hive-site.xml and restart Hive

## Parameters
rds_db_instance_endpoint='<rds_db_instance_endpoint>'
rds_db_instance_port='<rds_db_instance_port>'
rds_db_name='<rds_db_name>'
rds_db_instance_username='<rds_db_instance_username>'
rds_db_instance_password='<rds_db_instance_username>'

############################# Copying the original hive-site.xml
sudo cp /etc/hive/conf/hive-site.xml /tmp/hive-site.xml

############################# Changing the JDBC URL
old_jdbc=`grep "javax.jdo.option.ConnectionURL" -A +3 -B 1 /tmp/hive-site.xml | grep "<value>" | xargs`
sudo sed -i "s|$old_jdbc|<value>jdbc:postgresql://$rds_db_instance_endpoint:$rds_db_instance_port/$rds_db_name</value>|g" /tmp/hive-site.xml

############################# Changing the Driver name
old_driver_name=`grep "javax.jdo.option.ConnectionDriverName" -A +3 -B 1 /tmp/hive-site.xml | grep "<value>" | xargs`
sudo sed -i "s|$old_driver_name|<value>org.postgresql.Driver</value>|g" /tmp/hive-site.xml

############################# Changing the database user
old_db_username=`grep "javax.jdo.option.ConnectionUserName"  -A +3 -B 1 /tmp/hive-site.xml | grep "<value>" | xargs`
sudo sed -i "s|$old_db_username|<value>$rds_db_instance_username</value>|g" /tmp/hive-site.xml

############################# Changing the database password and description
connection_password=`grep "javax.jdo.option.ConnectionPassword" -A +3 -B 1 /tmp/hive-site.xml | grep "<value>" | xargs`
sudo sed -i "s|$connection_password|<value>$rds_db_instance_password</value>|g" /tmp/hive-site.xml
old_password_description=`grep "javax.jdo.option.ConnectionPassword" -A +3 -B 1 /tmp/hive-site.xml | grep "<description>" | xargs`
new_password_description='<description>the password for the DB instance</description>'
sudo sed -i "s|$password_description|$new_password_description|g" /tmp/hive-site.xml

############################# Moving hive-site to backup
sudo mv /etc/hive/conf/hive-site.xml /etc/hive/conf/hive-site.xml_bkup
sudo mv /tmp/hive-site.xml /etc/hive/conf/hive-site.xml

############################# Init Schema for Postgres
/usr/lib/hive/bin/schematool -dbType postgres -initSchema

############################# Restart Hive
## Check Amazon Linux version and restart Hive
OS_version=`uname -r`
if [[ "$OS_version" == *"amzn2"* ]]; then
    echo "Amazon Linux 2 instance, restarting Hive..."
    sudo systemctl stop hive-server2
    sudo systemctl stop hive-hcatalog-server
    sudo systemctl start hive-server2
    sudo systemctl start hive-hcatalog-server
elif [[ "$OS_version" == *"amzn1"* ]]; then
    echo "Amazon Linux 1 instance, restarting Hive"
    sudo stop hive-server2
    sudo stop hive-hcatalog-server
    sudo start hive-server2
    sudo start hive-hcatalog-server
else
    echo "ERROR: OS version different from AL1 or AL2."
fi
echo "--------------------COMPLETED--------------------"

请务必在脚本中替换以下值：

• rds_db_instance_endpoint 替换为数据库实例的端点
• rds_db_instance_port 替换为数据库实例的端口
• rds_db_name 替换为 PostgreSQL 数据库的名称
• rds_db_instance_username 替换为数据库实例用户名
• rds_db_instance_password 替换为数据库实例密码

将脚本上载到 Amazon S3。您可以使用 Amazon EMR 控制台、AWS Command Line Interface（AWS CLI）或 API 将脚本作为步骤任务运行。要使用 Amazon EMR 控制台运行脚本，请执行以下操作：

1.    打开 Amazon EMR 控制台。

2.    在 Cluster List（集群列表）页面上，选择集群的链接。

3.    在Cluster Details（集群详细信息）页面上选择 Steps（步骤）选项卡。

4.    在 Steps（步骤）选项卡上，选择 Add step（添加步骤）。

5.    在 Add step（添加步骤）对话框中，保留 Step type（步骤类型）和 Name（名称）的默认值。

6.    对于 JAR location（JAR 位置），请输入以下内容：

command-runner.jar

7.    对于 Arguments（参数），请输入以下内容：

bash -c "aws s3 cp s3://example_bucket/script/hive_postgres_emr_step.sh .; chmod +x hive_postgres_emr_step.sh; ./hive_postgres_emr_step.sh"

将命令中的 S3 位置替换为您存储脚本的位置。

8.    选择 Add（添加）运行步骤任务。

步骤任务完成后，执行以下操作以验证 Hive 配置更新：

1.    登录 Hive shell 并创建 Hive 表。

注意：请务必将示例中的 test_postgres 替换为 Hive 表的名称。

[hadoop@ip-X-X-X-X bin]$ hive

Logging initialized using configuration in file:/etc/hive/conf.dist/hive-log4j2.properties Async: true
hive> show databases;
OK
default
Time taken: 0.569 seconds, Fetched: 1 row(s)
hive> create table test_postgres(a int,b int);
OK
Time taken: 0.708 seconds

2.    安装 PostgreSQL：

[hadoop@ip-X-X-X-X bin]$ sudo yum install postgresql

3.    使用命令行连接到 PostgreSQL 数据库实例。

替换命令中的以下值：
mypostgresql.testabcd1111.us-west-2.rds.amazonaws.com 替换为数据库实例的终端节点
mypgdb 替换为 PostegreSQL 数据库的名称
database_username 替换为数据库实例用户名

[hadoop@ip-X-X-X-X bin]$ psql --host=mypostgresql.testabcd1111.us-west-2.rds.amazonaws.com --port=5432 --username=database_username --password --dbname=mypgdb

4.    弹出提示时，输入数据库实例密码。

5.    运行以下命令，确认您可以访问之前创建的 Hive 表：

mypgdb=>  select * from "TBLS";

 TBL_ID | CREATE_TIME | DB_ID | LAST_ACCESS_TIME | OWNER  | RETENTION | SD_ID |   TBL_NAME    |   TBL_TYPE    | VIEW_EXPANDED_TEXT | VIEW_ORIGINAL_TEXT | IS_REWRITE_ENABLED 
--------+-------------+-------+------------------+--------+-----------+-------+---------------+---------------+--------------------+--------------------+--------------------
      1 |  1555014961 |     1 |                0 | hadoop |         0 |     1 | test_postgres | MANAGED_TABLE |                    |                    | f
(1 row)

现在，您的 Amazon EMR 集群正在将 PostgreSQL 数据库用作 Hive 的外部元数据仓。

---

相关信息

为 Hive 配置外部元数据仓

连接到运行 PostgreSQL 数据库引擎的数据库实例