Hive Metastore Upgrade

# CD $HIVE _home/scripts/metastore/upgrade/mysql
[Dev root @ sd-9c1f-2eac/usr/local/src/apache-hive-2.1.1-bin/scripts/metastore/upgrade/mysql]
# ls
001-hive-972.mysql.sql 027-hive-12819.mysql.sql Hive-schema-2.0.0.mysql.sql
002-hive-1068.mysql.sql 028-hive-12821.mysql.sql Hive-schema-2.1.0.mysql.sql
003-hive-675.mysql.sql 029-hive-12822.mysql.sql Hive-txn-schema-0.13.0.mysql.sql
004-hive-1364.mysql.sql 030-hive-12823.mysql.sql Hive-txn-schema-0.14.0.mysql.sql
005-hive-417.mysql.sql 031-hive-12831.mysql.sql Hive-txn-schema-1.3.0.mysql.sql
006-hive-1823.mysql.sql 032-hive-12832.mysql.sql Hive-txn-schema-2.0.0.mysql.sql
007-hive-78.mysql.sql 034-hive-13076.mysql.sql Hive-txn-schema-2.1.0.mysql.sql
008-hive-2246.mysql.sql 035-hive-13395.mysql.sql README
009-hive-2215.mysql.sql 036-hive-13354.mysql.sql Upgrade-0.10.0-to-0.11.0.mysql.sql
010-hive-3072.mysql.sql Hive-schema-0.10.0.mysql.sql Upgrade-0.11.0-to-0.12.0.mysql.sql
011-hive-3649.mysql.sql Hive-schema-0.11.0.mysql.sql Upgrade-0.12.0-to-0.13.0.mysql.sql
012-hive-1362.mysql.sql Hive-schema-0.12.0.mysql.sql Upgrade-0.13.0-to-0.14.0.mysql.sql
013-hive-3255.mysql.sql Hive-schema-0.13.0.mysql.sql Upgrade-0.14.0-to-1.1.0.mysql.sql
014-hive-3764.mysql.sql Hive-schema-0.14.0.mysql.sql Upgrade-0.5.0-to-0.6.0.mysql.sql
016-hive-6386.mysql.sql Hive-schema-0.3.0.mysql.sql Upgrade-0.6.0-to-0.7.0.mysql.sql
017-hive-6458.mysql.sql Hive-schema-0.4.0.mysql.sql Upgrade-0.7.0-to-0.8.0.mysql.sql
018-hive-6757.mysql.sql Hive-schema-0.4.1.mysql.sql Upgrade-0.8.0-to-0.9.0.mysql.sql
019-hive-7784.mysql.sql Hive-schema-0.5.0.mysql.sql Upgrade-0.9.0-to-0.10.0.mysql.sql
020-hive-9296.mysql.sql Hive-schema-0.6.0.mysql.sql Upgrade-1.1.0-to-1.2.0.mysql.sql
021-hive-7018.mysql.sql Hive-schema-0.7.0.mysql.sql Upgrade-1.2.0-to-1.3.0.mysql.sql
022-hive-11970.mysql.sql Hive-schema-0.8.0.mysql.sql Upgrade-1.2.0-to-2.0.0.mysql.sql
023-hive-12807.mysql.sql Hive-schema-0.9.0.mysql.sql Upgrade-2.0.0-to-2.1.0.mysql.sql
024-hive-12814.mysql.sql Hive-schema-1.1.0.mysql.sql Upgrade.order.mysql
025-hive-12816.mysql.sql Hive-schema-1.2.0.mysql.sql
026-hive-12818.mysql.sql Hive-schema-1.3.0.mysql.sql

Cat README
Hive Metastore Upgrade HowTo
============================

This document describes how to upgrade the schema of a MySQL backed
Hive Metastore instance from one release version of Hive to another
Release version of Hive. For example, by following the steps listed
Below it is possible to upgrade a Hive 0.5.0 Metastore schema to a
Hive 0.7.0 metastore schema. Before attempting this project we
Strongly recommend that your read through all of the steps in this
Document and familiarize yourself with the required tools.

Metastore Upgrade Steps
=======================

1) Shutdown your Metastore instance and restrict access to the
Metastore ' s MySQL database. It's very important that no one else
Accesses or modifies the contents of the database while you are
Performing the schema upgrade.

2) Create A backup of your MySQL Metastore database. This would allow
Revert any changes made during the upgrade process if
Something goes wrong. The Mysqldump utility is the easiest-to
Create a backup of a MySQL database:

% mysqldump--opt <metastore_db_name> > Metastore_backup.sql

Note that the need also need to specify a hostname and username
Using the--host and--user command line switches.

3) Dump your Metastore database schema to a file. We Use the mysqldump
Utility again, but this time with a command line option
Specifies we is only interested in dumping the DDL statements
Required to create the schema:

% mysqldump--skip-add-drop-table--no-data <metastore_db_name> > My-schema-x.y.z.mysql.sql

4) The schema upgrade scripts assume that the schema is upgrading
closely matches the official schema for your Particular version of
Hive. The files in this directory with names like
"Hive-schema-x.y.z.mysql.sql" contain dumps of the official schemas
Co Rresponding to each of the released versions of Hive. You can
determine differences between your schemas and the official schema
by diffing the contents of the official Dump with the schema dump
your created in the previous step. Some differences is acceptable
and won't interfere with the upgrade process, but others need to
be resolved MA Nually or the upgrade scripts would fail to complete.

* Missing tables:hive ' s default configuration causes the Metastore
to create schema elements if they is n Eeded. Some tables
May is missing from your Metastore schemas if you do not created
the corresponding Hive catalog objec TS, e.g. the partitions table
would probably not exist if you had not created no table
partitions in your Metasto Re. You must create these missing tables
before running the upgrade scripts. The easiest-to-do-is
by executing the official schema DDL script against your
schema. Each of the "CREATE TABLE statements in the" schema script
include an IF not EXISTS clause, so tables which already exis T in
your schema would be ignored, and those which don ' t exist would get
created.

* Extra Tables:your Schema may include a table named Nucleus_tables
Or a table named Sequence_table. These tables is managed
By the DataNucleus ORM layer and would be created automatically if
They don ' t exist. No action on your part is required.

* Reversed Column Constraint Names in the same table:tables with
Multiple constraints may have the names of the constraints
Reversed. For example, the partitions table contains the foreign
Key constraints named PARTITIONS_FK1 and PARTITIONS_FK2 which
Reference SDS. SD_ID and TBLs. tbl_id respectively. However, in your
Schema you could find that PARTITIONS_FK1 references TBLs. TBL_ID and
PARTITIONS_FK2 references SDS. sd_id. Either version is acceptable
--The only requirement are that these constraints actually exist.

* Differences in Column/constraint names:your schema may contain
Tables with columns named "IDX" or unique keys named
"Unique<tab_name>". If you find either of these in your schema for you
Would need to change the names to "Integer_idx" and
"Unique_<tab_name>" Before running the upgrade scripts. For more
Background on the issue refer to HIVE-1435.

5) The schema upgrade scripts. If you are
Upgrading from Hive 0.5.0 to hive 0.6.0 you need to run the
Upgrade-0.5.0-to-0.6.0.mysql.sql script, but if you are upgrading
From 0.5.0 to 0.7.0 you'll need to run the 0.5.0 to 0.6.0 upgrade
Script followed by the 0.6.0 to 0.7.0 upgrade script.

% MySQL--verbose
Mysql> Use <metastore_db_name>;
Database changed
Mysql> Source Upgrade-0.5.0-to-0.6.0.mysql.sql
Mysql> Source Upgrade-0.6.0-to-0.7.0.mysql.sql

These scripts should run to completion without any errors. If You
Do encounter errors-need to analyze the cause and attempt to
Trace it back to one of the preceding steps.

6) The final step of the upgrade process is validating your freshly
Upgraded schema against the official schema for your particular
Version of Hive. This was accomplished by repeating steps (3) and
(4), but this time comparing against the official version of the
Upgraded schema, e.g. if you upgraded the schema to Hive 0.7.0 then
You'll want to compare your schema dump against the contents of
Hive-schema-0.7.0.mysql.sql

Hive Metastore Upgrade