FAQ
Repository: hive
Updated Branches:
   refs/heads/branch-1 f06e5171e -> 494ab8db5


HIVE-7018: Table and Partition tables have column LINK_TARGET_ID in Mysql scripts but not others (Yongzhi Chen via Chaoyu Tang)


Project: http://git-wip-us.apache.org/repos/asf/hive/repo
Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/494ab8db
Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/494ab8db
Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/494ab8db

Branch: refs/heads/branch-1
Commit: 494ab8db5b71c5fc0f4635dbe2e49e3dc8bb45fe
Parents: f06e517
Author: ctang <ctang.ma@gmail.com>
Authored: Sat Jun 13 09:01:52 2015 -0400
Committer: ctang <ctang.ma@gmail.com>
Committed: Sat Jun 13 09:01:52 2015 -0400

----------------------------------------------------------------------
  .../upgrade/mysql/021-HIVE-7018.mysql.sql | 53 ++++++++++++++++++++
  .../upgrade/mysql/hive-schema-1.3.0.mysql.sql | 10 +---
  .../mysql/upgrade-1.2.0-to-1.3.0.mysql.sql | 2 +-
  3 files changed, 56 insertions(+), 9 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/hive/blob/494ab8db/metastore/scripts/upgrade/mysql/021-HIVE-7018.mysql.sql
----------------------------------------------------------------------
diff --git a/metastore/scripts/upgrade/mysql/021-HIVE-7018.mysql.sql b/metastore/scripts/upgrade/mysql/021-HIVE-7018.mysql.sql
new file mode 100644
index 0000000..4f89892
--- /dev/null
+++ b/metastore/scripts/upgrade/mysql/021-HIVE-7018.mysql.sql
@@ -0,0 +1,53 @@
+SELECT '< HIVE-7018 Remove Table and Partition tables column LINK_TARGET_ID from Mysql for other DBs do not have it >' AS ' ';
+
+DELIMITER $$
+DROP PROCEDURE IF EXISTS RM_TLBS_LINKID $$
+DROP PROCEDURE IF EXISTS RM_PARTITIONS_LINKID $$
+DROP PROCEDURE IF EXISTS RM_LINKID $$
+
+/* Call this procedure to drop column LINK_TARGET_ID for TBLS */
+CREATE PROCEDURE RM_TLBS_LINKID()
+ BEGIN
+ IF EXISTS (SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_NAME` = 'TBLS' AND `COLUMN_NAME` = 'LINK_TARGET_ID') THEN
+ ALTER TABLE `TBLS`
+ DROP FOREIGN KEY `TBLS_FK3`
+ ;
+ ALTER TABLE `TBLS`
+ DROP KEY `TBLS_N51`
+ ;
+ ALTER TABLE `TBLS`
+ DROP COLUMN `LINK_TARGET_ID`
+ ;
+ END IF;
+ END $$
+
+/* Call this procedure to drop column LINK_TARGET_ID for PARTITIONS */
+CREATE PROCEDURE RM_PARTITIONS_LINKID()
+ BEGIN
+ IF EXISTS (SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_NAME` = 'PARTITIONS' AND `COLUMN_NAME` = 'LINK_TARGET_ID') THEN
+ ALTER TABLE `PARTITIONS`
+ DROP FOREIGN KEY `PARTITIONS_FK3`
+ ;
+ ALTER TABLE `PARTITIONS`
+ DROP KEY `PARTITIONS_N51`
+ ;
+ ALTER TABLE `PARTITIONS`
+ DROP COLUMN `LINK_TARGET_ID`
+ ;
+ END IF;
+ END $$
+
+/*
+ * Check and drop column LINK_TARGET_ID
+ */
+CREATE PROCEDURE RM_LINKID()
+ BEGIN
+ call RM_PARTITIONS_LINKID();
+ call RM_TLBS_LINKID();
+ SELECT 'Completed remove LINK_TARGET_ID';
+ END $$
+
+
+DELIMITER ;
+
+CALL RM_LINKID();

http://git-wip-us.apache.org/repos/asf/hive/blob/494ab8db/metastore/scripts/upgrade/mysql/hive-schema-1.3.0.mysql.sql
----------------------------------------------------------------------
diff --git a/metastore/scripts/upgrade/mysql/hive-schema-1.3.0.mysql.sql b/metastore/scripts/upgrade/mysql/hive-schema-1.3.0.mysql.sql
index bc63f0d..71de138 100644
--- a/metastore/scripts/upgrade/mysql/hive-schema-1.3.0.mysql.sql
+++ b/metastore/scripts/upgrade/mysql/hive-schema-1.3.0.mysql.sql
@@ -211,15 +211,12 @@ CREATE TABLE IF NOT EXISTS `PARTITIONS` (
    `PART_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    `SD_ID` bigint(20) DEFAULT NULL,
    `TBL_ID` bigint(20) DEFAULT NULL,
- `LINK_TARGET_ID` bigint(20) DEFAULT NULL,
    PRIMARY KEY (`PART_ID`),
    UNIQUE KEY `UNIQUEPARTITION` (`PART_NAME`,`TBL_ID`),
    KEY `PARTITIONS_N49` (`TBL_ID`),
    KEY `PARTITIONS_N50` (`SD_ID`),
- KEY `PARTITIONS_N51` (`LINK_TARGET_ID`),
    CONSTRAINT `PARTITIONS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`),
- CONSTRAINT `PARTITIONS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`),
- CONSTRAINT `PARTITIONS_FK3` FOREIGN KEY (`LINK_TARGET_ID`) REFERENCES `PARTITIONS` (`PART_ID`)
+ CONSTRAINT `PARTITIONS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  /*!40101 SET character_set_client = @saved_cs_client */;

@@ -590,15 +587,12 @@ CREATE TABLE IF NOT EXISTS `TBLS` (
    `TBL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    `VIEW_EXPANDED_TEXT` mediumtext,
    `VIEW_ORIGINAL_TEXT` mediumtext,
- `LINK_TARGET_ID` bigint(20) DEFAULT NULL,
    PRIMARY KEY (`TBL_ID`),
    UNIQUE KEY `UNIQUETABLE` (`TBL_NAME`,`DB_ID`),
    KEY `TBLS_N50` (`SD_ID`),
    KEY `TBLS_N49` (`DB_ID`),
- KEY `TBLS_N51` (`LINK_TARGET_ID`),
    CONSTRAINT `TBLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`),
- CONSTRAINT `TBLS_FK2` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`),
- CONSTRAINT `TBLS_FK3` FOREIGN KEY (`LINK_TARGET_ID`) REFERENCES `TBLS` (`TBL_ID`)
+ CONSTRAINT `TBLS_FK2` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  /*!40101 SET character_set_client = @saved_cs_client */;


http://git-wip-us.apache.org/repos/asf/hive/blob/494ab8db/metastore/scripts/upgrade/mysql/upgrade-1.2.0-to-1.3.0.mysql.sql
----------------------------------------------------------------------
diff --git a/metastore/scripts/upgrade/mysql/upgrade-1.2.0-to-1.3.0.mysql.sql b/metastore/scripts/upgrade/mysql/upgrade-1.2.0-to-1.3.0.mysql.sql
index d5a31f7..44a9946 100644
--- a/metastore/scripts/upgrade/mysql/upgrade-1.2.0-to-1.3.0.mysql.sql
+++ b/metastore/scripts/upgrade/mysql/upgrade-1.2.0-to-1.3.0.mysql.sql
@@ -1,4 +1,4 @@
  SELECT 'Upgrading MetaStore schema from 1.2.0 to 1.3.0' AS ' ';
+SOURCE 021-HIVE-7018.mysql.sql;
  UPDATE VERSION SET SCHEMA_VERSION='1.3.0', VERSION_COMMENT='Hive release version 1.3.0' where VER_ID=1;
  SELECT 'Finished upgrading MetaStore schema from 1.2.0 to 1.3.0' AS ' ';
-

Search Discussions

  • Ctang at Jun 18, 2015 at 8:19 pm
    Repository: hive
    Updated Branches:
       refs/heads/master 49d8414d0 -> ef6f313c6


    HIVE-7018: Table and Partition tables have column LINK_TARGET_ID in Mysql scripts but not others (Yongzhi Chen via Chaoyu Tang)


    Project: http://git-wip-us.apache.org/repos/asf/hive/repo
    Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/ef6f313c
    Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/ef6f313c
    Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/ef6f313c

    Branch: refs/heads/master
    Commit: ef6f313c6258c56c0b7e93a0981c6d643db6159a
    Parents: 49d8414
    Author: ctang <ctang.ma@gmail.com>
    Authored: Thu Jun 18 16:17:55 2015 -0400
    Committer: ctang <ctang.ma@gmail.com>
    Committed: Thu Jun 18 16:17:55 2015 -0400

    ----------------------------------------------------------------------
      .../upgrade/mysql/021-HIVE-7018.mysql.sql | 53 ++++++++++++++++++++
      .../upgrade/mysql/hive-schema-1.3.0.mysql.sql | 10 +---
      .../upgrade/mysql/hive-schema-2.0.0.mysql.sql | 10 +---
      .../mysql/upgrade-1.2.0-to-1.3.0.mysql.sql | 2 +-
      4 files changed, 58 insertions(+), 17 deletions(-)
    ----------------------------------------------------------------------


    http://git-wip-us.apache.org/repos/asf/hive/blob/ef6f313c/metastore/scripts/upgrade/mysql/021-HIVE-7018.mysql.sql
    ----------------------------------------------------------------------
    diff --git a/metastore/scripts/upgrade/mysql/021-HIVE-7018.mysql.sql b/metastore/scripts/upgrade/mysql/021-HIVE-7018.mysql.sql
    new file mode 100644
    index 0000000..4f89892
    --- /dev/null
    +++ b/metastore/scripts/upgrade/mysql/021-HIVE-7018.mysql.sql
    @@ -0,0 +1,53 @@
    +SELECT '< HIVE-7018 Remove Table and Partition tables column LINK_TARGET_ID from Mysql for other DBs do not have it >' AS ' ';
    +
    +DELIMITER $$
    +DROP PROCEDURE IF EXISTS RM_TLBS_LINKID $$
    +DROP PROCEDURE IF EXISTS RM_PARTITIONS_LINKID $$
    +DROP PROCEDURE IF EXISTS RM_LINKID $$
    +
    +/* Call this procedure to drop column LINK_TARGET_ID for TBLS */
    +CREATE PROCEDURE RM_TLBS_LINKID()
    + BEGIN
    + IF EXISTS (SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_NAME` = 'TBLS' AND `COLUMN_NAME` = 'LINK_TARGET_ID') THEN
    + ALTER TABLE `TBLS`
    + DROP FOREIGN KEY `TBLS_FK3`
    + ;
    + ALTER TABLE `TBLS`
    + DROP KEY `TBLS_N51`
    + ;
    + ALTER TABLE `TBLS`
    + DROP COLUMN `LINK_TARGET_ID`
    + ;
    + END IF;
    + END $$
    +
    +/* Call this procedure to drop column LINK_TARGET_ID for PARTITIONS */
    +CREATE PROCEDURE RM_PARTITIONS_LINKID()
    + BEGIN
    + IF EXISTS (SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_NAME` = 'PARTITIONS' AND `COLUMN_NAME` = 'LINK_TARGET_ID') THEN
    + ALTER TABLE `PARTITIONS`
    + DROP FOREIGN KEY `PARTITIONS_FK3`
    + ;
    + ALTER TABLE `PARTITIONS`
    + DROP KEY `PARTITIONS_N51`
    + ;
    + ALTER TABLE `PARTITIONS`
    + DROP COLUMN `LINK_TARGET_ID`
    + ;
    + END IF;
    + END $$
    +
    +/*
    + * Check and drop column LINK_TARGET_ID
    + */
    +CREATE PROCEDURE RM_LINKID()
    + BEGIN
    + call RM_PARTITIONS_LINKID();
    + call RM_TLBS_LINKID();
    + SELECT 'Completed remove LINK_TARGET_ID';
    + END $$
    +
    +
    +DELIMITER ;
    +
    +CALL RM_LINKID();

    http://git-wip-us.apache.org/repos/asf/hive/blob/ef6f313c/metastore/scripts/upgrade/mysql/hive-schema-1.3.0.mysql.sql
    ----------------------------------------------------------------------
    diff --git a/metastore/scripts/upgrade/mysql/hive-schema-1.3.0.mysql.sql b/metastore/scripts/upgrade/mysql/hive-schema-1.3.0.mysql.sql
    index bc63f0d..71de138 100644
    --- a/metastore/scripts/upgrade/mysql/hive-schema-1.3.0.mysql.sql
    +++ b/metastore/scripts/upgrade/mysql/hive-schema-1.3.0.mysql.sql
    @@ -211,15 +211,12 @@ CREATE TABLE IF NOT EXISTS `PARTITIONS` (
        `PART_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
        `SD_ID` bigint(20) DEFAULT NULL,
        `TBL_ID` bigint(20) DEFAULT NULL,
    - `LINK_TARGET_ID` bigint(20) DEFAULT NULL,
        PRIMARY KEY (`PART_ID`),
        UNIQUE KEY `UNIQUEPARTITION` (`PART_NAME`,`TBL_ID`),
        KEY `PARTITIONS_N49` (`TBL_ID`),
        KEY `PARTITIONS_N50` (`SD_ID`),
    - KEY `PARTITIONS_N51` (`LINK_TARGET_ID`),
        CONSTRAINT `PARTITIONS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`),
    - CONSTRAINT `PARTITIONS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`),
    - CONSTRAINT `PARTITIONS_FK3` FOREIGN KEY (`LINK_TARGET_ID`) REFERENCES `PARTITIONS` (`PART_ID`)
    + CONSTRAINT `PARTITIONS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      /*!40101 SET character_set_client = @saved_cs_client */;

    @@ -590,15 +587,12 @@ CREATE TABLE IF NOT EXISTS `TBLS` (
        `TBL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
        `VIEW_EXPANDED_TEXT` mediumtext,
        `VIEW_ORIGINAL_TEXT` mediumtext,
    - `LINK_TARGET_ID` bigint(20) DEFAULT NULL,
        PRIMARY KEY (`TBL_ID`),
        UNIQUE KEY `UNIQUETABLE` (`TBL_NAME`,`DB_ID`),
        KEY `TBLS_N50` (`SD_ID`),
        KEY `TBLS_N49` (`DB_ID`),
    - KEY `TBLS_N51` (`LINK_TARGET_ID`),
        CONSTRAINT `TBLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`),
    - CONSTRAINT `TBLS_FK2` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`),
    - CONSTRAINT `TBLS_FK3` FOREIGN KEY (`LINK_TARGET_ID`) REFERENCES `TBLS` (`TBL_ID`)
    + CONSTRAINT `TBLS_FK2` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      /*!40101 SET character_set_client = @saved_cs_client */;


    http://git-wip-us.apache.org/repos/asf/hive/blob/ef6f313c/metastore/scripts/upgrade/mysql/hive-schema-2.0.0.mysql.sql
    ----------------------------------------------------------------------
    diff --git a/metastore/scripts/upgrade/mysql/hive-schema-2.0.0.mysql.sql b/metastore/scripts/upgrade/mysql/hive-schema-2.0.0.mysql.sql
    index 3a595dd..6547cf1 100644
    --- a/metastore/scripts/upgrade/mysql/hive-schema-2.0.0.mysql.sql
    +++ b/metastore/scripts/upgrade/mysql/hive-schema-2.0.0.mysql.sql
    @@ -211,15 +211,12 @@ CREATE TABLE IF NOT EXISTS `PARTITIONS` (
        `PART_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
        `SD_ID` bigint(20) DEFAULT NULL,
        `TBL_ID` bigint(20) DEFAULT NULL,
    - `LINK_TARGET_ID` bigint(20) DEFAULT NULL,
        PRIMARY KEY (`PART_ID`),
        UNIQUE KEY `UNIQUEPARTITION` (`PART_NAME`,`TBL_ID`),
        KEY `PARTITIONS_N49` (`TBL_ID`),
        KEY `PARTITIONS_N50` (`SD_ID`),
    - KEY `PARTITIONS_N51` (`LINK_TARGET_ID`),
        CONSTRAINT `PARTITIONS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`),
    - CONSTRAINT `PARTITIONS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`),
    - CONSTRAINT `PARTITIONS_FK3` FOREIGN KEY (`LINK_TARGET_ID`) REFERENCES `PARTITIONS` (`PART_ID`)
    + CONSTRAINT `PARTITIONS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      /*!40101 SET character_set_client = @saved_cs_client */;

    @@ -590,15 +587,12 @@ CREATE TABLE IF NOT EXISTS `TBLS` (
        `TBL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
        `VIEW_EXPANDED_TEXT` mediumtext,
        `VIEW_ORIGINAL_TEXT` mediumtext,
    - `LINK_TARGET_ID` bigint(20) DEFAULT NULL,
        PRIMARY KEY (`TBL_ID`),
        UNIQUE KEY `UNIQUETABLE` (`TBL_NAME`,`DB_ID`),
        KEY `TBLS_N50` (`SD_ID`),
        KEY `TBLS_N49` (`DB_ID`),
    - KEY `TBLS_N51` (`LINK_TARGET_ID`),
        CONSTRAINT `TBLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`),
    - CONSTRAINT `TBLS_FK2` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`),
    - CONSTRAINT `TBLS_FK3` FOREIGN KEY (`LINK_TARGET_ID`) REFERENCES `TBLS` (`TBL_ID`)
    + CONSTRAINT `TBLS_FK2` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      /*!40101 SET character_set_client = @saved_cs_client */;


    http://git-wip-us.apache.org/repos/asf/hive/blob/ef6f313c/metastore/scripts/upgrade/mysql/upgrade-1.2.0-to-1.3.0.mysql.sql
    ----------------------------------------------------------------------
    diff --git a/metastore/scripts/upgrade/mysql/upgrade-1.2.0-to-1.3.0.mysql.sql b/metastore/scripts/upgrade/mysql/upgrade-1.2.0-to-1.3.0.mysql.sql
    index d5a31f7..44a9946 100644
    --- a/metastore/scripts/upgrade/mysql/upgrade-1.2.0-to-1.3.0.mysql.sql
    +++ b/metastore/scripts/upgrade/mysql/upgrade-1.2.0-to-1.3.0.mysql.sql
    @@ -1,4 +1,4 @@
      SELECT 'Upgrading MetaStore schema from 1.2.0 to 1.3.0' AS ' ';
    +SOURCE 021-HIVE-7018.mysql.sql;
      UPDATE VERSION SET SCHEMA_VERSION='1.3.0', VERSION_COMMENT='Hive release version 1.3.0' where VER_ID=1;
      SELECT 'Finished upgrading MetaStore schema from 1.2.0 to 1.3.0' AS ' ';
    -

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupcommits @
categorieshive, hadoop
postedJun 13, '15 at 1:02p
activeJun 18, '15 at 8:19p
posts2
users1
websitehive.apache.org

1 user in discussion

Ctang: 2 posts

People

Translate

site design / logo © 2021 Grokbase