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

上次更新时间:2022 年 1 月 31 日

我想在 Amazon EMR 上,使用 Amazon Relational Database Service(Amazon RDS)将 PostgreSQL 数据库实例用作 Apache 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 替换为 PostegreSQL 数据库的名称
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 的外部元数据仓。


这篇文章对您有帮助吗?


您是否需要账单或技术支持?