Support Questions
Find answers, ask questions, and share your expertise

Re: Migrate Hive Metastore from MySQL to Postgres

Contributor

Hello @Sindhu

I did the following.

a. Created a new MySQL instance and migrated the tables into this new database

b. Pointed the hive metastore to the new MySQL instance. As expected, the hive metastore service didn't come up.

c. I ran the upgrade script command to upgrade the MySQL database schema.

d. I restarted the hive metastore service and it came up.

e. Now as the schema versions are same, I migrated the tables from the new MySQL database to Postgres database.

f. I am now trying to start the hiveserver2 but it is not starting but I am able to login into hive command prompt.

g. I also ran the following command

[rsingh01@ip-172-31-31-251 bin]$ ./schematool -info -dbType postgres -userName root -passWord password -verbose WARNING: Use "yarn jar" to launch YARN applications. Metastore connection URL: jdbc:postgresql://xxxxxxxxxxxxxxxxxxxxxxxxx/cirrus3 Metastore Connection Driver : org.postgresql.Driver Metastore connection User: root Hive distribution version: 1.2.1000 Metastore schema version: 1.2.1000 schemaTool completed

h. I tried querying the tables but now it is showing the following error:

hive> select * from rms_customer; FAILED: SemanticException Unable to determine if hdfs://xxxxxxxxxxxxxxxxxxxxxxxxxxxx:8020/user/hive/warehouse/rms.db/rms_customer is encrypted: java.lang.IllegalArgumentException: Wrong FS: hdfs://xxxxxxxxxxxxxxxxxxxxxxxxxxxx:8020/user/hive/warehouse/rms.db/rms_customer, expected: hdfs://xxxxxxxxxxxxxxxxxxxxxxxxxxxx:8020

h. I followed the link https://issues.apache.org/jira/browse/HIVE-11116 and tried to list the FSRoot by running the command metatool -listFSRoot but this time it is throwing the error:

===========================

[rsingh01@ip-172-31-31-251 bin]$ ./metatool -listFSRoot WARNING: Use "yarn jar" to launch YARN applications. Initializing HiveMetaTool.. 16/07/14 10:05:43 INFO metastore.ObjectStore: ObjectStore, initialize called 16/07/14 10:05:43 INFO DataNucleus.Persistence: Property hive.metastore.integral.jdo.pushdown unknown - will be ignored 16/07/14 10:05:43 INFO DataNucleus.Persistence: Property datanucleus.cache.level2 unknown - will be ignored 16/07/14 10:05:44 ERROR Datastore.Schema: Failed initialising database. Unable to open a test connection to the given database. JDBC url = jdbc:postgresql://xxxxxxxxxxxxxxxxxxxxxxxx/cirrus1, username = root. Terminating connection pool (set lazyInit to true if you expect to start your database after your app). Original Exception: ------ org.postgresql.util.PSQLException: FATAL: password authentication failed for user "root"

===========================

I am totally stuck now

Best Regards,

Rinku Singh.

Re: Migrate Hive Metastore from MySQL to Postgres

@Rinku Singh

Error:

ERROR Datastore.Schema: Failed initialising database. Unable to open a test connection to the given database. JDBC url = jdbc:postgresql://xxxxxxxxxxxxxxxxxxxxxxxx/cirrus1, username = root.

Seems like issue with test connectivity. Can you please try testing connectivity using a java program?

https://community.hortonworks.com/content/kbentry/25410/simple-steps-to-test-hive-jdbc-connect.html

Thanks and Regards,

Sindhu

Re: Migrate Hive Metastore from MySQL to Postgres

Contributor

Hi @Sindhu

I finally had to add the parameter javax.jdo.option.ConnectionPassword in the hive-site.xml and re-run it. Fixed all the issues and everything is working now.

Thank you so much for your help and pointers.

Best Regards,

Rinku Singh.

Re: Migrate Hive Metastore from MySQL to Postgres

@Rinku Singh

Glad to be of some help. Please mark best answer to end the discussion.

Re: Migrate Hive Metastore from MySQL to Postgres

New Contributor

Hi @Rinku Singh, @Sindhu,

We're migrating our cluster to new hardware. TBs of HDFS (/Data, /user & /apps/hive/warehouse) data transferred well to destination. Now, we need to move the Hive tables Metastore. There are 10s of DBs and each would have 100s of tables. The specifications are as follows:

Ambari: 2.5.0.3 (Old), 2.6.1.5 (New)

HDP: 2.6.0.3 (Old), 2.6.4.0 (New)

HIVE DB: MySQL (Old), Postgres (New)

Issue: We don't have the DBs schema so that we can run again on new cluster. It would be very tedious to Create Table from one to another. Is there a way to import the metadata dump from one to another?

Please advise!

Thanks allot in advance!

Regards.

Re: Migrate Hive Metastore from MySQL to Postgres

New Contributor

Hi @Rinku Singh, @Sindhu,

We're migrating our cluster to new hardware. TBs of HDFS (/Data, /user & /apps/hive/warehouse) data transferred well to destination. Now, we need to move the Hive tables Metastore. There are 10s of DBs and each would have 100s of tables. The specifications are as follows:

Ambari: 2.5.0.3 (Old), 2.6.1.5 (New)

HDP: 2.6.0.3 (Old), 2.6.4.0 (New)

HIVE DB: MySQL (Old), Postgres (New)

Issue: We don't have the DBs schema so that we can run again on new cluster. It would be very tedious to Create Table from one to another. Is there a way to import the metadata dump from one to another?

Please advise!

Thanks allot in advance!

Regards.