Comment utiliser une base de données PostgreSQL comme métastore externe pour Hive sur Amazon EMR ?

Résolution

Avant de commencer, notez les points suivants :

• Cette solution part du principe que vous disposez déjà d’une base de données PostgreSQL active.
• Si vous utilisez la version 5.7 ou une version antérieure d'Amazon EMR, téléchargez d'abord le pilote JDBC PostgreSQL depuis le site Web de pgJDBC. Puis, ajoutez le pilote au chemin de la bibliothèque Hive (/usr/lib/hive/lib). Les versions 5.8.0 et ultérieures d'Amazon EMR sont fournies avec le pilote JDBC PostgreSQL dans le chemin de la bibliothèque Hive.

Configurer une instance de base de données PostgreSQL en tant que métastore externe pour Hive

Procédez comme suit :

1. Créez une instance de base de données Amazon RDS pour PostgreSQL et la base de données.
   Remarque : Vous pouvez créer la base de données pendant que vous créez l'instance de base de données dans la console Amazon Aurora et RDS. Vous pouvez spécifier le nom de la base de données dans le champ Nom initial de la base de données sous Configuration supplémentaire. Vous pouvez également connecter l'instance de base de données PostgreSQL, puis créer la base de données.

2. Pour autoriser les connexions sur le port 5432 entre votre base de données et le groupe de sécurité ElasticMapReduce-Master, modifiez le groupe de sécurité de l'instance de base de données. Pour plus d'informations, consultez la section Groupes de sécurité VPC.

3. Lancez un cluster Amazon EMR sans métastore externe. Amazon EMR utilise la base de données MySQL par défaut dans ce cas.

4. Utilisez SSH pour vous connecter au nœud primaire.

5. Mettez à jour la configuration de Hive.
   Un exemple de configuration de Hive est fourni ci-dessous. Remplacez les valeurs suivantes par les vôtres :
   mypostgresql.testabcd1111.us-west-2.rds.amazonaws.com par le point de terminaison de votre instance de base de données
   mypgdb par le nom de votre base de données PostgreSQL
   database_username par le nom d'utilisateur de l'instance de base de données
   database_password par le mot de passe de l'instance de base de données

   [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. Pour créer le schéma PostgreSQL, exécutez les commandes suivantes :

   [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. Pour appliquer les mises à jour et les modifications, arrêtez et démarrez les services Hive.

   [hadoop@ip-X-X-X-X bin]$ sudo initctl list |grep -i hivehive-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

   Remarque : Pour automatiser les étapes 5 à 7, exécutez le script bash suivant (hive_postgres_emr_step.sh) en tant que tâche par étapes dans le cluster EMR.

   ## 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--------------------"

   Veillez à remplacer les valeurs suivantes dans le script :

   • rds_db_instance_endpoint par le point de terminaison de votre instance de base de données
   • rds_db_instance_port par le port de votre instance de base de données
   • rds_db_name par le nom de votre base de données PostgreSQL
   • rds_db_instance_username par le nom d'utilisateur de l'instance de base de données
   • rds_db_instance_password par le mot de passe de l'instance de base de données

Télécharger le script sur Amazon S3

Vous pouvez exécuter le script en tant que tâche étape par étape via la console Amazon EMR, l'interface de la ligne de commande AWS (AWS CLI) ou l'API. Pour utiliser la console Amazon EMR afin d'exécuter le script, procédez comme suit :

1. Ouvrez la console Amazon EMR.

2. Sur la page Liste des clusters, sélectionnez le lien correspondant à votre cluster.

3. Sur la page Détails du cluster, choisissez l'onglet Étapes.

4. Dans l'onglet Étapes, choisissez Ajouter une étape.

5. Dans la boîte de dialogue Ajouter une étape, conservez les valeurs par défaut pour Type d’étape et Nom.

6. Pour Emplacement JAR, entrez les informations suivantes :

   command-runner.jar

7. Pour Arguments, entrez les informations suivantes :

   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"

   Remplacez l'emplacement S3 de la commande par l'emplacement où vous avez stocké le script.

8. Choisissez Ajouter pour exécuter la tâche par étapes.

Vérifier les mises à jour de la configuration de Hive

Procédez comme suit :

1. Connectez-vous au shell Hive et créez une table Hive.
   Remarque : Remplacez test_postgres par le nom de votre table 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. Pour installer PostgreSQL, exécutez la commande suivante :

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

3. Connectez-vous à l'instance de base de données PostgreSQL à l'aide de la ligne de commande.
   Remplacez les valeurs suivantes dans la commande :
   mypostgresql.testabcd1111.us-west-2.rds.amazonaws.com par le point de terminaison de votre instance de base de données
   mypgdb par le nom de votre base de données PostgreSQL
   database_username par le nom d'utilisateur de l'instance de base de données

   [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. Lorsque vous y êtes invité, entrez le mot de passe de l'instance de base de données.

5. Pour confirmer que vous pouvez accéder à la table Hive que vous avez créée précédemment, exécutez la commande suivante :

   
   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)

Informations connexes

Configuration d'une métastore externe pour Hive

Connexion à une instance de base de données exécutant le moteur de base de données PostgreSQL