diff --git metastore/scripts/upgrade/derby/001-HIVE-972.derby.sql metastore/scripts/upgrade/derby/001-HIVE-972.derby.sql new file mode 100644 index 0000000..ac2daba --- /dev/null +++ metastore/scripts/upgrade/derby/001-HIVE-972.derby.sql @@ -0,0 +1,3 @@ +-- HIVE-972: Support views +ALTER TABLE "TBLS" ADD "VIEW_ORIGINAL_TEXT" LONG VARCHAR DEFAULT NULL; +ALTER TABLE "TBLS" ADD "VIEW_EXPANDED_TEXT" LONG VARCHAR DEFAULT NULL; diff --git metastore/scripts/upgrade/derby/002-HIVE-1068.derby.sql metastore/scripts/upgrade/derby/002-HIVE-1068.derby.sql new file mode 100644 index 0000000..f20d79c --- /dev/null +++ metastore/scripts/upgrade/derby/002-HIVE-1068.derby.sql @@ -0,0 +1,2 @@ +-- HIVE-1068: CREATE VIEW followup: add a 'table type' enum attribute in metastore +ALTER TABLE "TBLS" ADD COLUMN "TBL_TYPE" VARCHAR(128); diff --git metastore/scripts/upgrade/derby/003-HIVE-675.derby.sql metastore/scripts/upgrade/derby/003-HIVE-675.derby.sql new file mode 100644 index 0000000..01e4a60 --- /dev/null +++ metastore/scripts/upgrade/derby/003-HIVE-675.derby.sql @@ -0,0 +1,3 @@ +-- HIVE-675: Add database/schema support for Hive QL +ALTER TABLE "DBS" ALTER "DESC" SET DATA TYPE VARCHAR(4000); +ALTER TABLE "DBS" ADD "DB_LOCATION_URI" VARCHAR(4000) NOT NULL DEFAULT ''; diff --git metastore/scripts/upgrade/derby/004-HIVE-1364.derby.sql metastore/scripts/upgrade/derby/004-HIVE-1364.derby.sql new file mode 100644 index 0000000..e635578 --- /dev/null +++ metastore/scripts/upgrade/derby/004-HIVE-1364.derby.sql @@ -0,0 +1,15 @@ +-- HIVE-1364: Increase the maximum length of various metastore fields, +-- and remove TYPE_NAME from COLUMNS primary key +ALTER TABLE "TBLS" ALTER "OWNER" SET DATA TYPE VARCHAR(767); +ALTER TABLE "COLUMNS" DROP PRIMARY KEY; +ALTER TABLE "COLUMNS" ADD PRIMARY KEY ("SD_ID", "COLUMN_NAME"); +ALTER TABLE "COLUMNS" ALTER "TYPE_NAME" SET DATA TYPE VARCHAR(4000); +ALTER TABLE "PARTITION_KEYS" ALTER "PKEY_COMMENT" SET DATA TYPE VARCHAR(4000); +ALTER TABLE "SD_PARAMS" ALTER "PARAM_VALUE" SET DATA TYPE VARCHAR(4000); +ALTER TABLE "SDS" ALTER "INPUT_FORMAT" SET DATA TYPE VARCHAR(4000); +ALTER TABLE "SDS" ALTER "LOCATION" SET DATA TYPE VARCHAR(4000); +ALTER TABLE "SDS" ALTER "OUTPUT_FORMAT" SET DATA TYPE VARCHAR(4000); +ALTER TABLE "SERDE_PARAMS" ALTER "PARAM_VALUE" SET DATA TYPE VARCHAR(4000); +ALTER TABLE "SERDES" ALTER "SLIB" SET DATA TYPE VARCHAR(4000); +ALTER TABLE "TABLE_PARAMS" ALTER "PARAM_VALUE" SET DATA TYPE VARCHAR(4000); +ALTER TABLE "PARTITION_PARAMS" ALTER "PARAM_VALUE" SET DATA TYPE VARCHAR(4000); diff --git metastore/scripts/upgrade/derby/005-HIVE-417.derby.sql metastore/scripts/upgrade/derby/005-HIVE-417.derby.sql new file mode 100644 index 0000000..01a9b59 --- /dev/null +++ metastore/scripts/upgrade/derby/005-HIVE-417.derby.sql @@ -0,0 +1,47 @@ +-- +-- HIVE-417 Implement Indexing in Hive +-- + +CREATE TABLE "IDXS" ( + "INDEX_ID" BIGINT NOT NULL, + "CREATE_TIME" INTEGER NOT NULL, + "DEFERRED_REBUILD" CHAR(1) NOT NULL, + "INDEX_HANDLER_CLASS" VARCHAR(256), + "INDEX_NAME" VARCHAR(128), + "INDEX_TBL_ID" BIGINT, + "LAST_ACCESS_TIME" INTEGER NOT NULL, + "ORIG_TBL_ID" BIGINT, + "SD_ID" BIGINT); + +ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK1" + FOREIGN KEY ("SD_ID") REFERENCES "SDS" ("SD_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK2" + FOREIGN KEY ("INDEX_TBL_ID") REFERENCES "TBLS" ("TBL_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK3" + FOREIGN KEY ("ORIG_TBL_ID") REFERENCES "TBLS" ("TBL_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_PK" + PRIMARY KEY ("INDEX_ID"); + +ALTER TABLE "IDXS" ADD CONSTRAINT "DEFERRED_REBUILD_CHECK" + CHECK (DEFERRED_REBUILD IN ('Y','N')); + + +CREATE TABLE "INDEX_PARAMS" ( + "INDEX_ID" BIGINT NOT NULL, + "PARAM_KEY" VARCHAR(256) NOT NULL, + "PARAM_VALUE" VARCHAR(767)); + +ALTER TABLE "INDEX_PARAMS" ADD CONSTRAINT "INDEX_PARAMS_FK1" + FOREIGN KEY ("INDEX_ID") REFERENCES "IDXS" ("INDEX_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "INDEX_PARAMS" ADD CONSTRAINT "INDEX_PARAMS_PK" + PRIMARY KEY ("INDEX_ID", "PARAM_KEY"); + +CREATE UNIQUE INDEX "UNIQUEINDEX" ON "IDXS" ("INDEX_NAME", "ORIG_TBL_ID"); diff --git metastore/scripts/upgrade/derby/006-HIVE-1823.derby.sql metastore/scripts/upgrade/derby/006-HIVE-1823.derby.sql new file mode 100644 index 0000000..f27324c --- /dev/null +++ metastore/scripts/upgrade/derby/006-HIVE-1823.derby.sql @@ -0,0 +1,15 @@ +-- +-- HIVE-1823 Upgrade the database thrift interface to allow parameters key-value pairs +-- +CREATE TABLE "DATABASE_PARAMS" ( + "DB_ID" BIGINT NOT NULL, + "PARAM_KEY" VARCHAR(180) NOT NULL, + "PARAM_VALUE" VARCHAR(4000)); + +ALTER TABLE "DATABASE_PARAMS" ADD CONSTRAINT "DATABASE_PARAMS_FK1" + FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "DATABASE_PARAMS" ADD CONSTRAINT "DATABASE_PARAMS_PK" + PRIMARY KEY ("DB_ID", "PARAM_KEY"); + diff --git metastore/scripts/upgrade/derby/007-HIVE-78.derby.sql metastore/scripts/upgrade/derby/007-HIVE-78.derby.sql new file mode 100644 index 0000000..8e8a586 --- /dev/null +++ metastore/scripts/upgrade/derby/007-HIVE-78.derby.sql @@ -0,0 +1,184 @@ +-- +-- HIVE-78 Authorization model for Hive +-- + +-- +-- ROLES +-- +CREATE TABLE "ROLES" ( + "ROLE_ID" BIGINT NOT NULL, + "CREATE_TIME" INTEGER NOT NULL, + "OWNER_NAME" VARCHAR(128), + "ROLE_NAME" VARCHAR(128)); + +ALTER TABLE "ROLES" ADD CONSTRAINT "ROLES_PK" + PRIMARY KEY ("ROLE_ID"); + +CREATE UNIQUE INDEX "ROLEENTITYINDEX" ON "ROLES" ("ROLE_NAME"); + +CREATE TABLE "ROLE_MAP" ( + "ROLE_GRANT_ID" BIGINT NOT NULL, + "ADD_TIME" INTEGER NOT NULL, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "ROLE_ID" BIGINT); + +ALTER TABLE "ROLE_MAP" ADD CONSTRAINT "ROLE_MAP_FK1" + FOREIGN KEY ("ROLE_ID") REFERENCES "ROLES" ("ROLE_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "ROLE_MAP" ADD CONSTRAINT "ROLE_MAP_PK" + PRIMARY KEY ("ROLE_GRANT_ID"); + +CREATE UNIQUE INDEX "USERROLEMAPINDEX" ON "ROLE_MAP" ( + "PRINCIPAL_NAME", "ROLE_ID", "GRANTOR", "GRANTOR_TYPE"); + +-- +-- GLOBAL_PRIVS +-- +CREATE TABLE "GLOBAL_PRIVS" ( + "USER_GRANT_ID" BIGINT NOT NULL, + "CREATE_TIME" INTEGER NOT NULL, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "USER_PRIV" VARCHAR(128)); + +ALTER TABLE "GLOBAL_PRIVS" ADD CONSTRAINT "GLOBAL_PRIVS_PK" + PRIMARY KEY ("USER_GRANT_ID"); + +CREATE UNIQUE INDEX "GLOBALPRIVILEGEINDEX" ON "GLOBAL_PRIVS" ( + "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "USER_PRIV", + "GRANTOR", "GRANTOR_TYPE"); + +-- +-- DB_PRIVS +-- +CREATE TABLE "DB_PRIVS" ( + "DB_GRANT_ID" BIGINT NOT NULL, + "CREATE_TIME" INTEGER NOT NULL, + "DB_ID" BIGINT, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "DB_PRIV" VARCHAR(128)); + +ALTER TABLE "DB_PRIVS" ADD CONSTRAINT "DB_PRIVS_FK1" + FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "DB_PRIVS" ADD CONSTRAINT "DB_PRIVS_PK" + PRIMARY KEY ("DB_GRANT_ID"); + +CREATE UNIQUE INDEX "DBPRIVILEGEINDEX" ON "DB_PRIVS" ( + "DB_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", + "DB_PRIV", "GRANTOR", "GRANTOR_TYPE"); + +-- +-- TBL_PRIVS +-- +CREATE TABLE "TBL_PRIVS" ( + "TBL_GRANT_ID" BIGINT NOT NULL, + "CREATE_TIME" INTEGER NOT NULL, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "TBL_PRIV" VARCHAR(128), + "TBL_ID" BIGINT); + +ALTER TABLE "TBL_PRIVS" ADD CONSTRAINT "TBL_PRIVS_FK1" + FOREIGN KEY ("TBL_ID") REFERENCES "TBLS" ("TBL_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "TBL_PRIVS" ADD CONSTRAINT "TBL_PRIVS_PK" + PRIMARY KEY ("TBL_GRANT_ID"); + +CREATE INDEX "TABLEPRIVILEGEINDEX" ON "TBL_PRIVS" ( + "TBL_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", + "TBL_PRIV", "GRANTOR", "GRANTOR_TYPE"); + +-- +-- TBL_COL_PRIVS +-- +CREATE TABLE "TBL_COL_PRIVS" ( + "TBL_COLUMN_GRANT_ID" BIGINT NOT NULL, + "COLUMN_NAME" VARCHAR(128), + "CREATE_TIME" INTEGER NOT NULL, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "TBL_COL_PRIV" VARCHAR(128), + "TBL_ID" BIGINT); + +ALTER TABLE "TBL_COL_PRIVS" ADD CONSTRAINT "TBL_COL_PRIVS_FK1" + FOREIGN KEY ("TBL_ID") REFERENCES "TBLS" ("TBL_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "TBL_COL_PRIVS" ADD CONSTRAINT "TBL_COL_PRIVS_PK" + PRIMARY KEY ("TBL_COLUMN_GRANT_ID"); + +CREATE INDEX "TABLECOLUMNPRIVILEGEINDEX" ON "TBL_COL_PRIVS" ( + "TBL_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", + "TBL_COL_PRIV", "GRANTOR", "GRANTOR_TYPE"); + +-- +-- PART_PRIVS +-- +CREATE TABLE "PART_PRIVS" ( + "PART_GRANT_ID" BIGINT NOT NULL, + "CREATE_TIME" INTEGER NOT NULL, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PART_ID" BIGINT, + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "PART_PRIV" VARCHAR(128)); + +ALTER TABLE "PART_PRIVS" ADD CONSTRAINT "PART_PRIVS_FK1" + FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "PART_PRIVS" ADD CONSTRAINT "PART_PRIVS_PK" + PRIMARY KEY ("PART_GRANT_ID"); + +CREATE INDEX "PARTPRIVILEGEINDEX" ON "PART_PRIVS" ( + "PART_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", + "PART_PRIV", "GRANTOR", "GRANTOR_TYPE"); + +-- +-- PART_COL_PRIVS +-- +CREATE TABLE "PART_COL_PRIVS" ( + "PART_COLUMN_GRANT_ID" BIGINT NOT NULL, + "COLUMN_NAME" VARCHAR(128), + "CREATE_TIME" INTEGER NOT NULL, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PART_ID" BIGINT, + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "PART_COL_PRIV" VARCHAR(128)); + +ALTER TABLE "PART_COL_PRIVS" ADD CONSTRAINT "PART_COL_PRIVS_FK1" + FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "PART_COL_PRIVS" ADD CONSTRAINT "PART_COL_PRIVS_PK" + PRIMARY KEY ("PART_COLUMN_GRANT_ID"); + +CREATE INDEX "PARTITIONCOLUMNPRIVILEGEINDEX" ON "PART_COL_PRIVS" ( + "PART_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", + "PART_COL_PRIV", "GRANTOR", "GRANTOR_TYPE"); diff --git metastore/scripts/upgrade/derby/README metastore/scripts/upgrade/derby/README index 506eb54..e42ff7d 100644 --- metastore/scripts/upgrade/derby/README +++ metastore/scripts/upgrade/derby/README @@ -1,17 +1,109 @@ +Hive MetaStore Upgrade HowTo +============================ -1) Shutdown your metastore instance. +This document describes how to upgrade the schema of a Derby 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 you read through all of the steps in this +document and familiarize yourself with the required tools. -2) Perform a backup of your Derby metastore database. Probably - the easiest way of doing this is to just create a copy of the - Derby database "metastore_db" directory. +MetaStore Upgrade Steps +======================= -3) Execute the upgrade script: +1) Shutdown your MetaStore instance and restrict access to the + MetaStore's Derby database. It is very important that no one else + accesses or modifies the contents of database while you are + performing the schema upgrade. + +2) Create a backup of your Derby metastore database. This will allow + you to revert any changes made during the upgrade process if + something goes wrong. The easiest way of accomplishing this task is + by creating a copy of the directory containing your Derby database. + +3) Dump your MetaStore database schema to a file using Derby's dblook utility: + + % dblook -d -z "APP" > my-schema-x.y.z.derby.sql + + Note that "APP" is Derby's default schema for user-created catalog + objects. + + +4) The schema upgrade scripts assume that the schema you are 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.derby.sql" contain dumps of the official schemas + corresponding to each of the released versions of Hive. You can + determine differences between your schema and the official schema + by comparing the contents of the official dump with the schema dump + you created in the previous step. Note that due to a bug in Derby + the order in which the DDL statements appear is non-deterministic, + so simply diffing the two dumps is unlikely to result in useable + results. A simple workaround for this problem is to compare sorted + versions of the two schema dumps, e.g: + + % sort hive-schema-x.y.z.derby.sql > hive-schema-x.y.z.derby.sql.sorted + % sort my-schema-x.y.z.derby.sql > my-schema-x.y.z.derby.sql.sorted + % diff hive-schema-x.y.z.derby.sql.sorted my-schema-x.y.z.derby.sql.sorted + + Some differences are acceptable and will not interfere + with the upgrade process, but others need to be resolved manually + or the upgrade scripts will fail to complete. + + * Missing Tables: Hive's default configuration causes the MetaStore + to create schema elements only when they are needed. Some tables + may be missing from your MetaStore schema if you have not created + the corresponding Hive catalog objects, e.g. the PARTITIONS table + will probably not exist if you have not created any table + partitions in your MetaStore. You MUST create these missing tables + before running the upgrade scripts. The easiest way to do this is + by executing the official schema DDL script against your + schema. You should expect most of the DDL statements to fail since + the table/constraint/index already exist. + + * 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 two foreign + key constraints named PARTITIONS_FK1 and PARTITIONS_FK2 which + reference SDS.SD_ID and TBLS.TBL_ID respectively. However, in your + schema you may find that PARTITIONS_FK1 references TBLS.TBL_ID and + PARTITIONS_FK2 references SDS.SD_ID. Either version is acceptable + -- the only requirement is that these constraints actually exist. + + * Differences in Column/Constraint Names: Your schema may contain + tables with columns named "IDX" or unique keys named + "UNIQUE". If you find either of these in your schema you + will need to change the names to "INTEGER_IDX" and + "UNIQUE_" before running the upgrade scripts. For more + background on this issue please refer to HIVE-1435. + +5) You are now ready to run 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.derby.sql script, but if you are upgrading + from 0.5.0 to 0.7.0 you will 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. + +NOTE: You may need to install the Derby 'ij' utility. + Look here for installation instructions: + http://db.apache.org/derby/docs/10.4/getstart/ + % ij ij version 10.4 ij> CONNECT 'jdbc:derby:/Users/bob/hive/metastore_db;databaseName=metastore_db'; - ij> RUN 'upgrade-0.6.0.derby.sql'; + ij> RUN 'upgrade-0.5.0-to-0.6.0.derby.sql'; + ij> RUN 'upgrade-0.6.0-to-0.7.0.derby.sql'; ij> quit; -NOTE: You may need to install the Derby 'ij' utility. - Look here for installation instructions: - http://db.apache.org/derby/docs/10.4/getstart/ + These scripts should run to completion without any errors. If you + do encounter errors you 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 is 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 will want to compare your schema dump against the contents of + hive-schema-0.7.0.derby.sql + diff --git metastore/scripts/upgrade/derby/hive-schema-0.3.0.derby.sql metastore/scripts/upgrade/derby/hive-schema-0.3.0.derby.sql new file mode 100644 index 0000000..828e181 --- /dev/null +++ metastore/scripts/upgrade/derby/hive-schema-0.3.0.derby.sql @@ -0,0 +1,135 @@ +-- Timestamp: 2011-03-11 18:04:26.586 +-- Source database is: /Users/carl/metastore/derby/mdb-0.3.0 +-- Connection URL is: jdbc:derby:/Users/carl/metastore/derby/mdb-0.3.0 +-- appendLogs: false + +-- ---------------------------------------------- +-- DDL Statements for functions +-- ---------------------------------------------- + +CREATE FUNCTION "APP"."NUCLEUS_ASCII" (C CHAR(1)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.ascii' ; + +CREATE FUNCTION "APP"."NUCLEUS_MATCHES" (TEXT VARCHAR(8000),PATTERN VARCHAR(8000)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.matches' ; + +-- ---------------------------------------------- +-- DDL Statements for tables +-- ---------------------------------------------- + +CREATE TABLE "APP"."TBLS" ("TBL_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "DB_ID" BIGINT, "LAST_ACCESS_TIME" INTEGER NOT NULL, "OWNER" VARCHAR(128), "RETENTION" INTEGER NOT NULL, "SD_ID" BIGINT, "TBL_NAME" VARCHAR(128)); + +CREATE TABLE "APP"."TABLE_PARAMS" ("TBL_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767)); + +CREATE TABLE "APP"."PARTITION_PARAMS" ("PART_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767)); + +CREATE TABLE "APP"."PARTITIONS" ("PART_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "LAST_ACCESS_TIME" INTEGER NOT NULL, "PART_NAME" VARCHAR(767), "SD_ID" BIGINT, "TBL_ID" BIGINT); + +CREATE TABLE "APP"."TYPES" ("TYPES_ID" BIGINT NOT NULL, "TYPE_NAME" VARCHAR(128), "TYPE1" VARCHAR(128), "TYPE2" VARCHAR(128)); + +CREATE TABLE "APP"."PARTITION_KEY_VALS" ("PART_ID" BIGINT NOT NULL, "PART_KEY_VAL" VARCHAR(256), "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."PARTITION_KEYS" ("TBL_ID" BIGINT NOT NULL, "PKEY_COMMENT" VARCHAR(767), "PKEY_NAME" VARCHAR(128) NOT NULL, "PKEY_TYPE" VARCHAR(767) NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."TYPE_FIELDS" ("TYPE_NAME" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "FIELD_NAME" VARCHAR(128) NOT NULL, "FIELD_TYPE" VARCHAR(128) NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."SERDE_PARAMS" ("SERDE_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767)); + +CREATE TABLE "APP"."DBS" ("DB_ID" BIGINT NOT NULL, "DESC" VARCHAR(767), "NAME" VARCHAR(128)); + +CREATE TABLE "APP"."COLUMNS" ("SD_ID" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "COLUMN_NAME" VARCHAR(128) NOT NULL, "TYPE_NAME" VARCHAR(128) NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."BUCKETING_COLS" ("SD_ID" BIGINT NOT NULL, "BUCKET_COL_NAME" VARCHAR(256), "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."SDS" ("SD_ID" BIGINT NOT NULL, "INPUT_FORMAT" VARCHAR(256), "ISCOMPRESSED" CHAR(1) NOT NULL, "LOCATION" VARCHAR(767), "NUM_BUCKETS" INTEGER NOT NULL, "OUTPUT_FORMAT" VARCHAR(256), "SERDE_ID" BIGINT); + +CREATE TABLE "APP"."SORT_COLS" ("SD_ID" BIGINT NOT NULL, "COLUMN_NAME" VARCHAR(128), "ORDER" INTEGER NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."SD_PARAMS" ("SD_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767)); + +CREATE TABLE "APP"."SERDES" ("SERDE_ID" BIGINT NOT NULL, "NAME" VARCHAR(128), "SLIB" VARCHAR(767)); + +-- ---------------------------------------------- +-- DDL Statements for indexes +-- ---------------------------------------------- + +CREATE UNIQUE INDEX "APP"."UNIQUEDATABASE" ON "APP"."DBS" ("NAME"); + +CREATE UNIQUE INDEX "APP"."UNIQUETYPE" ON "APP"."TYPES" ("TYPE_NAME"); + +CREATE UNIQUE INDEX "APP"."UNIQUEPARTITION" ON "APP"."PARTITIONS" ("PART_NAME", "TBL_ID"); + +CREATE UNIQUE INDEX "APP"."UNIQUETABLE" ON "APP"."TBLS" ("TBL_NAME", "DB_ID"); + +-- ---------------------------------------------- +-- DDL Statements for keys +-- ---------------------------------------------- + +-- primary/unique +ALTER TABLE "APP"."DBS" ADD CONSTRAINT "DBS_PK" PRIMARY KEY ("DB_ID"); + +ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_PK" PRIMARY KEY ("TYPE_NAME", "FIELD_NAME"); + +ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_PK" PRIMARY KEY ("TBL_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."TYPES" ADD CONSTRAINT "TYPES_PK" PRIMARY KEY ("TYPES_ID"); + +ALTER TABLE "APP"."SERDES" ADD CONSTRAINT "SERDES_PK" PRIMARY KEY ("SERDE_ID"); + +ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEY_PK" PRIMARY KEY ("TBL_ID", "PKEY_NAME"); + +ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_PK" PRIMARY KEY ("SD_ID", "IDX"); + +ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_PK" PRIMARY KEY ("SD_ID", "COLUMN_NAME", "TYPE_NAME"); + +ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_PK" PRIMARY KEY ("PART_ID"); + +ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_PK" PRIMARY KEY ("TBL_ID"); + +ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_PK" PRIMARY KEY ("SD_ID", "IDX"); + +ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_PK" PRIMARY KEY ("PART_ID", "IDX"); + +ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_PK" PRIMARY KEY ("SERDE_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_PK" PRIMARY KEY ("SD_ID"); + +ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_PK" PRIMARY KEY ("PART_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_PK" PRIMARY KEY ("SD_ID", "PARAM_KEY"); + +-- foreign +ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_FK1" FOREIGN KEY ("TYPE_NAME") REFERENCES "APP"."TYPES" ("TYPES_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEYS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK2" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK2" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK1" FOREIGN KEY ("DB_ID") REFERENCES "APP"."DBS" ("DB_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +-- ---------------------------------------------- +-- DDL Statements for checks +-- ---------------------------------------------- + +ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SQL110311180425340" CHECK (ISCOMPRESSED IN ('Y','N')); + diff --git metastore/scripts/upgrade/derby/hive-schema-0.4.0.derby.sql metastore/scripts/upgrade/derby/hive-schema-0.4.0.derby.sql new file mode 100644 index 0000000..3093560 --- /dev/null +++ metastore/scripts/upgrade/derby/hive-schema-0.4.0.derby.sql @@ -0,0 +1,135 @@ +-- Timestamp: 2011-03-11 18:08:03.119 +-- Source database is: /Users/carl/metastore/derby/mdb-0.4.0 +-- Connection URL is: jdbc:derby:/Users/carl/metastore/derby/mdb-0.4.0 +-- appendLogs: false + +-- ---------------------------------------------- +-- DDL Statements for functions +-- ---------------------------------------------- + +CREATE FUNCTION "APP"."NUCLEUS_ASCII" (C CHAR(1)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.ascii' ; + +CREATE FUNCTION "APP"."NUCLEUS_MATCHES" (TEXT VARCHAR(8000),PATTERN VARCHAR(8000)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.matches' ; + +-- ---------------------------------------------- +-- DDL Statements for tables +-- ---------------------------------------------- + +CREATE TABLE "APP"."TBLS" ("TBL_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "DB_ID" BIGINT, "LAST_ACCESS_TIME" INTEGER NOT NULL, "OWNER" VARCHAR(128), "RETENTION" INTEGER NOT NULL, "SD_ID" BIGINT, "TBL_NAME" VARCHAR(128)); + +CREATE TABLE "APP"."PARTITION_KEY_VALS" ("PART_ID" BIGINT NOT NULL, "PART_KEY_VAL" VARCHAR(256), "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."SDS" ("SD_ID" BIGINT NOT NULL, "INPUT_FORMAT" VARCHAR(256), "ISCOMPRESSED" CHAR(1) NOT NULL, "LOCATION" VARCHAR(767), "NUM_BUCKETS" INTEGER NOT NULL, "OUTPUT_FORMAT" VARCHAR(256), "SERDE_ID" BIGINT); + +CREATE TABLE "APP"."SERDE_PARAMS" ("SERDE_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767)); + +CREATE TABLE "APP"."TYPES" ("TYPES_ID" BIGINT NOT NULL, "TYPE_NAME" VARCHAR(128), "TYPE1" VARCHAR(128), "TYPE2" VARCHAR(128)); + +CREATE TABLE "APP"."SORT_COLS" ("SD_ID" BIGINT NOT NULL, "COLUMN_NAME" VARCHAR(128), "ORDER" INTEGER NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."COLUMNS" ("SD_ID" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "COLUMN_NAME" VARCHAR(128) NOT NULL, "TYPE_NAME" VARCHAR(128) NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."SERDES" ("SERDE_ID" BIGINT NOT NULL, "NAME" VARCHAR(128), "SLIB" VARCHAR(767)); + +CREATE TABLE "APP"."TABLE_PARAMS" ("TBL_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767)); + +CREATE TABLE "APP"."SD_PARAMS" ("SD_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767)); + +CREATE TABLE "APP"."PARTITIONS" ("PART_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "LAST_ACCESS_TIME" INTEGER NOT NULL, "PART_NAME" VARCHAR(767), "SD_ID" BIGINT, "TBL_ID" BIGINT); + +CREATE TABLE "APP"."PARTITION_KEYS" ("TBL_ID" BIGINT NOT NULL, "PKEY_COMMENT" VARCHAR(767), "PKEY_NAME" VARCHAR(128) NOT NULL, "PKEY_TYPE" VARCHAR(767) NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."BUCKETING_COLS" ("SD_ID" BIGINT NOT NULL, "BUCKET_COL_NAME" VARCHAR(256), "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."DBS" ("DB_ID" BIGINT NOT NULL, "DESC" VARCHAR(767), "NAME" VARCHAR(128)); + +CREATE TABLE "APP"."PARTITION_PARAMS" ("PART_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767)); + +CREATE TABLE "APP"."TYPE_FIELDS" ("TYPE_NAME" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "FIELD_NAME" VARCHAR(128) NOT NULL, "FIELD_TYPE" VARCHAR(128) NOT NULL, "IDX" INTEGER NOT NULL); + +-- ---------------------------------------------- +-- DDL Statements for indexes +-- ---------------------------------------------- + +CREATE UNIQUE INDEX "APP"."UNIQUEPARTITION" ON "APP"."PARTITIONS" ("PART_NAME", "TBL_ID"); + +CREATE UNIQUE INDEX "APP"."UNIQUETABLE" ON "APP"."TBLS" ("TBL_NAME", "DB_ID"); + +CREATE UNIQUE INDEX "APP"."UNIQUETYPE" ON "APP"."TYPES" ("TYPE_NAME"); + +CREATE UNIQUE INDEX "APP"."UNIQUEDATABASE" ON "APP"."DBS" ("NAME"); + +-- ---------------------------------------------- +-- DDL Statements for keys +-- ---------------------------------------------- + +-- primary/unique +ALTER TABLE "APP"."SERDES" ADD CONSTRAINT "SERDES_PK" PRIMARY KEY ("SERDE_ID"); + +ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_PK" PRIMARY KEY ("TYPE_NAME", "FIELD_NAME"); + +ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_PK" PRIMARY KEY ("TBL_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEY_PK" PRIMARY KEY ("TBL_ID", "PKEY_NAME"); + +ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_PK" PRIMARY KEY ("PART_ID"); + +ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_PK" PRIMARY KEY ("SD_ID", "COLUMN_NAME", "TYPE_NAME"); + +ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_PK" PRIMARY KEY ("PART_ID", "IDX"); + +ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_PK" PRIMARY KEY ("TBL_ID"); + +ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_PK" PRIMARY KEY ("SD_ID", "IDX"); + +ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_PK" PRIMARY KEY ("PART_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."TYPES" ADD CONSTRAINT "TYPES_PK" PRIMARY KEY ("TYPES_ID"); + +ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_PK" PRIMARY KEY ("SD_ID"); + +ALTER TABLE "APP"."DBS" ADD CONSTRAINT "DBS_PK" PRIMARY KEY ("DB_ID"); + +ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_PK" PRIMARY KEY ("SD_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_PK" PRIMARY KEY ("SERDE_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_PK" PRIMARY KEY ("SD_ID", "IDX"); + +-- foreign +ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_FK1" FOREIGN KEY ("TYPE_NAME") REFERENCES "APP"."TYPES" ("TYPES_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEYS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK2" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK2" FOREIGN KEY ("DB_ID") REFERENCES "APP"."DBS" ("DB_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +-- ---------------------------------------------- +-- DDL Statements for checks +-- ---------------------------------------------- + +ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SQL110311180801910" CHECK (ISCOMPRESSED IN ('Y','N')); + diff --git metastore/scripts/upgrade/derby/hive-schema-0.4.1.derby.sql metastore/scripts/upgrade/derby/hive-schema-0.4.1.derby.sql new file mode 100644 index 0000000..19d4cdc --- /dev/null +++ metastore/scripts/upgrade/derby/hive-schema-0.4.1.derby.sql @@ -0,0 +1,135 @@ +-- Timestamp: 2011-03-11 18:08:11.747 +-- Source database is: /Users/carl/metastore/derby/mdb-0.4.1 +-- Connection URL is: jdbc:derby:/Users/carl/metastore/derby/mdb-0.4.1 +-- appendLogs: false + +-- ---------------------------------------------- +-- DDL Statements for functions +-- ---------------------------------------------- + +CREATE FUNCTION "APP"."NUCLEUS_ASCII" (C CHAR(1)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.ascii' ; + +CREATE FUNCTION "APP"."NUCLEUS_MATCHES" (TEXT VARCHAR(8000),PATTERN VARCHAR(8000)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.matches' ; + +-- ---------------------------------------------- +-- DDL Statements for tables +-- ---------------------------------------------- + +CREATE TABLE "APP"."TBLS" ("TBL_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "DB_ID" BIGINT, "LAST_ACCESS_TIME" INTEGER NOT NULL, "OWNER" VARCHAR(128), "RETENTION" INTEGER NOT NULL, "SD_ID" BIGINT, "TBL_NAME" VARCHAR(128)); + +CREATE TABLE "APP"."SERDE_PARAMS" ("SERDE_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767)); + +CREATE TABLE "APP"."PARTITION_KEYS" ("TBL_ID" BIGINT NOT NULL, "PKEY_COMMENT" VARCHAR(767), "PKEY_NAME" VARCHAR(128) NOT NULL, "PKEY_TYPE" VARCHAR(767) NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."COLUMNS" ("SD_ID" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "COLUMN_NAME" VARCHAR(128) NOT NULL, "TYPE_NAME" VARCHAR(128) NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."PARTITION_KEY_VALS" ("PART_ID" BIGINT NOT NULL, "PART_KEY_VAL" VARCHAR(256), "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."SORT_COLS" ("SD_ID" BIGINT NOT NULL, "COLUMN_NAME" VARCHAR(128), "ORDER" INTEGER NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."DBS" ("DB_ID" BIGINT NOT NULL, "DESC" VARCHAR(767), "NAME" VARCHAR(128)); + +CREATE TABLE "APP"."TYPES" ("TYPES_ID" BIGINT NOT NULL, "TYPE_NAME" VARCHAR(128), "TYPE1" VARCHAR(128), "TYPE2" VARCHAR(128)); + +CREATE TABLE "APP"."TABLE_PARAMS" ("TBL_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767)); + +CREATE TABLE "APP"."BUCKETING_COLS" ("SD_ID" BIGINT NOT NULL, "BUCKET_COL_NAME" VARCHAR(256), "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."PARTITION_PARAMS" ("PART_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767)); + +CREATE TABLE "APP"."SERDES" ("SERDE_ID" BIGINT NOT NULL, "NAME" VARCHAR(128), "SLIB" VARCHAR(767)); + +CREATE TABLE "APP"."SDS" ("SD_ID" BIGINT NOT NULL, "INPUT_FORMAT" VARCHAR(256), "ISCOMPRESSED" CHAR(1) NOT NULL, "LOCATION" VARCHAR(767), "NUM_BUCKETS" INTEGER NOT NULL, "OUTPUT_FORMAT" VARCHAR(256), "SERDE_ID" BIGINT); + +CREATE TABLE "APP"."PARTITIONS" ("PART_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "LAST_ACCESS_TIME" INTEGER NOT NULL, "PART_NAME" VARCHAR(767), "SD_ID" BIGINT, "TBL_ID" BIGINT); + +CREATE TABLE "APP"."TYPE_FIELDS" ("TYPE_NAME" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "FIELD_NAME" VARCHAR(128) NOT NULL, "FIELD_TYPE" VARCHAR(128) NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."SD_PARAMS" ("SD_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767)); + +-- ---------------------------------------------- +-- DDL Statements for indexes +-- ---------------------------------------------- + +CREATE UNIQUE INDEX "APP"."UNIQUEPARTITION" ON "APP"."PARTITIONS" ("PART_NAME", "TBL_ID"); + +CREATE UNIQUE INDEX "APP"."UNIQUETABLE" ON "APP"."TBLS" ("TBL_NAME", "DB_ID"); + +CREATE UNIQUE INDEX "APP"."UNIQUETYPE" ON "APP"."TYPES" ("TYPE_NAME"); + +CREATE UNIQUE INDEX "APP"."UNIQUEDATABASE" ON "APP"."DBS" ("NAME"); + +-- ---------------------------------------------- +-- DDL Statements for keys +-- ---------------------------------------------- + +-- primary/unique +ALTER TABLE "APP"."SERDES" ADD CONSTRAINT "SERDES_PK" PRIMARY KEY ("SERDE_ID"); + +ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_PK" PRIMARY KEY ("PART_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEY_PK" PRIMARY KEY ("TBL_ID", "PKEY_NAME"); + +ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_PK" PRIMARY KEY ("SERDE_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_PK" PRIMARY KEY ("PART_ID"); + +ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_PK" PRIMARY KEY ("TYPE_NAME", "FIELD_NAME"); + +ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_PK" PRIMARY KEY ("SD_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_PK" PRIMARY KEY ("TBL_ID"); + +ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_PK" PRIMARY KEY ("SD_ID", "COLUMN_NAME", "TYPE_NAME"); + +ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_PK" PRIMARY KEY ("TBL_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."TYPES" ADD CONSTRAINT "TYPES_PK" PRIMARY KEY ("TYPES_ID"); + +ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_PK" PRIMARY KEY ("SD_ID"); + +ALTER TABLE "APP"."DBS" ADD CONSTRAINT "DBS_PK" PRIMARY KEY ("DB_ID"); + +ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_PK" PRIMARY KEY ("SD_ID", "IDX"); + +ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_PK" PRIMARY KEY ("SD_ID", "IDX"); + +ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_PK" PRIMARY KEY ("PART_ID", "IDX"); + +-- foreign +ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEYS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK2" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_FK1" FOREIGN KEY ("TYPE_NAME") REFERENCES "APP"."TYPES" ("TYPES_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK2" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK1" FOREIGN KEY ("DB_ID") REFERENCES "APP"."DBS" ("DB_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +-- ---------------------------------------------- +-- DDL Statements for checks +-- ---------------------------------------------- + +ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SQL110311180810520" CHECK (ISCOMPRESSED IN ('Y','N')); + diff --git metastore/scripts/upgrade/derby/hive-schema-0.5.0.derby.sql metastore/scripts/upgrade/derby/hive-schema-0.5.0.derby.sql new file mode 100644 index 0000000..e1cc456 --- /dev/null +++ metastore/scripts/upgrade/derby/hive-schema-0.5.0.derby.sql @@ -0,0 +1,135 @@ +-- Timestamp: 2011-03-11 18:08:20.291 +-- Source database is: /Users/carl/metastore/derby/mdb-0.5.0 +-- Connection URL is: jdbc:derby:/Users/carl/metastore/derby/mdb-0.5.0 +-- appendLogs: false + +-- ---------------------------------------------- +-- DDL Statements for functions +-- ---------------------------------------------- + +CREATE FUNCTION "APP"."NUCLEUS_ASCII" (C CHAR(1)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.ascii' ; + +CREATE FUNCTION "APP"."NUCLEUS_MATCHES" (TEXT VARCHAR(8000),PATTERN VARCHAR(8000)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.matches' ; + +-- ---------------------------------------------- +-- DDL Statements for tables +-- ---------------------------------------------- + +CREATE TABLE "APP"."SDS" ("SD_ID" BIGINT NOT NULL, "INPUT_FORMAT" VARCHAR(256), "ISCOMPRESSED" CHAR(1) NOT NULL, "LOCATION" VARCHAR(767), "NUM_BUCKETS" INTEGER NOT NULL, "OUTPUT_FORMAT" VARCHAR(256), "SERDE_ID" BIGINT); + +CREATE TABLE "APP"."TABLE_PARAMS" ("TBL_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767)); + +CREATE TABLE "APP"."SERDES" ("SERDE_ID" BIGINT NOT NULL, "NAME" VARCHAR(128), "SLIB" VARCHAR(767)); + +CREATE TABLE "APP"."DBS" ("DB_ID" BIGINT NOT NULL, "DESC" VARCHAR(767), "NAME" VARCHAR(128)); + +CREATE TABLE "APP"."SORT_COLS" ("SD_ID" BIGINT NOT NULL, "COLUMN_NAME" VARCHAR(128), "ORDER" INTEGER NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."BUCKETING_COLS" ("SD_ID" BIGINT NOT NULL, "BUCKET_COL_NAME" VARCHAR(256), "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."PARTITION_KEY_VALS" ("PART_ID" BIGINT NOT NULL, "PART_KEY_VAL" VARCHAR(256), "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."COLUMNS" ("SD_ID" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "COLUMN_NAME" VARCHAR(128) NOT NULL, "TYPE_NAME" VARCHAR(128) NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."TYPE_FIELDS" ("TYPE_NAME" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "FIELD_NAME" VARCHAR(128) NOT NULL, "FIELD_TYPE" VARCHAR(128) NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."TBLS" ("TBL_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "DB_ID" BIGINT, "LAST_ACCESS_TIME" INTEGER NOT NULL, "OWNER" VARCHAR(128), "RETENTION" INTEGER NOT NULL, "SD_ID" BIGINT, "TBL_NAME" VARCHAR(128)); + +CREATE TABLE "APP"."PARTITIONS" ("PART_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "LAST_ACCESS_TIME" INTEGER NOT NULL, "PART_NAME" VARCHAR(767), "SD_ID" BIGINT, "TBL_ID" BIGINT); + +CREATE TABLE "APP"."PARTITION_PARAMS" ("PART_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767)); + +CREATE TABLE "APP"."SERDE_PARAMS" ("SERDE_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767)); + +CREATE TABLE "APP"."PARTITION_KEYS" ("TBL_ID" BIGINT NOT NULL, "PKEY_COMMENT" VARCHAR(767), "PKEY_NAME" VARCHAR(128) NOT NULL, "PKEY_TYPE" VARCHAR(767) NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."TYPES" ("TYPES_ID" BIGINT NOT NULL, "TYPE_NAME" VARCHAR(128), "TYPE1" VARCHAR(128), "TYPE2" VARCHAR(128)); + +CREATE TABLE "APP"."SD_PARAMS" ("SD_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767)); + +-- ---------------------------------------------- +-- DDL Statements for indexes +-- ---------------------------------------------- + +CREATE UNIQUE INDEX "APP"."UNIQUEPARTITION" ON "APP"."PARTITIONS" ("PART_NAME", "TBL_ID"); + +CREATE UNIQUE INDEX "APP"."UNIQUETABLE" ON "APP"."TBLS" ("TBL_NAME", "DB_ID"); + +CREATE UNIQUE INDEX "APP"."UNIQUETYPE" ON "APP"."TYPES" ("TYPE_NAME"); + +CREATE UNIQUE INDEX "APP"."UNIQUEDATABASE" ON "APP"."DBS" ("NAME"); + +-- ---------------------------------------------- +-- DDL Statements for keys +-- ---------------------------------------------- + +-- primary/unique +ALTER TABLE "APP"."SERDES" ADD CONSTRAINT "SERDES_PK" PRIMARY KEY ("SERDE_ID"); + +ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_PK" PRIMARY KEY ("SD_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_PK" PRIMARY KEY ("PART_ID", "IDX"); + +ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_PK" PRIMARY KEY ("SD_ID", "COLUMN_NAME", "TYPE_NAME"); + +ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_PK" PRIMARY KEY ("PART_ID"); + +ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_PK" PRIMARY KEY ("PART_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_PK" PRIMARY KEY ("SD_ID", "IDX"); + +ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_PK" PRIMARY KEY ("TBL_ID"); + +ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEY_PK" PRIMARY KEY ("TBL_ID", "PKEY_NAME"); + +ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_PK" PRIMARY KEY ("SERDE_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."TYPES" ADD CONSTRAINT "TYPES_PK" PRIMARY KEY ("TYPES_ID"); + +ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_PK" PRIMARY KEY ("SD_ID"); + +ALTER TABLE "APP"."DBS" ADD CONSTRAINT "DBS_PK" PRIMARY KEY ("DB_ID"); + +ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_PK" PRIMARY KEY ("TBL_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_PK" PRIMARY KEY ("SD_ID", "IDX"); + +ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_PK" PRIMARY KEY ("TYPE_NAME", "FIELD_NAME"); + +-- foreign +ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK2" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK2" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK1" FOREIGN KEY ("DB_ID") REFERENCES "APP"."DBS" ("DB_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEYS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_FK1" FOREIGN KEY ("TYPE_NAME") REFERENCES "APP"."TYPES" ("TYPES_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +-- ---------------------------------------------- +-- DDL Statements for checks +-- ---------------------------------------------- + +ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SQL110311180819080" CHECK (ISCOMPRESSED IN ('Y','N')); + diff --git metastore/scripts/upgrade/derby/hive-schema-0.6.0.derby.sql metastore/scripts/upgrade/derby/hive-schema-0.6.0.derby.sql new file mode 100644 index 0000000..a9ea4a7 --- /dev/null +++ metastore/scripts/upgrade/derby/hive-schema-0.6.0.derby.sql @@ -0,0 +1,135 @@ +-- Timestamp: 2011-03-11 18:08:28.86 +-- Source database is: /Users/carl/metastore/derby/mdb-0.6.0 +-- Connection URL is: jdbc:derby:/Users/carl/metastore/derby/mdb-0.6.0 +-- appendLogs: false + +-- ---------------------------------------------- +-- DDL Statements for functions +-- ---------------------------------------------- + +CREATE FUNCTION "APP"."NUCLEUS_ASCII" (C CHAR(1)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.ascii' ; + +CREATE FUNCTION "APP"."NUCLEUS_MATCHES" (TEXT VARCHAR(8000),PATTERN VARCHAR(8000)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.matches' ; + +-- ---------------------------------------------- +-- DDL Statements for tables +-- ---------------------------------------------- + +CREATE TABLE "APP"."TBLS" ("TBL_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "DB_ID" BIGINT, "LAST_ACCESS_TIME" INTEGER NOT NULL, "OWNER" VARCHAR(767), "RETENTION" INTEGER NOT NULL, "SD_ID" BIGINT, "TBL_NAME" VARCHAR(128), "TBL_TYPE" VARCHAR(128), "VIEW_EXPANDED_TEXT" LONG VARCHAR, "VIEW_ORIGINAL_TEXT" LONG VARCHAR); + +CREATE TABLE "APP"."SORT_COLS" ("SD_ID" BIGINT NOT NULL, "COLUMN_NAME" VARCHAR(128), "ORDER" INTEGER NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."PARTITIONS" ("PART_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "LAST_ACCESS_TIME" INTEGER NOT NULL, "PART_NAME" VARCHAR(767), "SD_ID" BIGINT, "TBL_ID" BIGINT); + +CREATE TABLE "APP"."TYPES" ("TYPES_ID" BIGINT NOT NULL, "TYPE_NAME" VARCHAR(128), "TYPE1" VARCHAR(767), "TYPE2" VARCHAR(767)); + +CREATE TABLE "APP"."COLUMNS" ("SD_ID" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "COLUMN_NAME" VARCHAR(128) NOT NULL, "TYPE_NAME" VARCHAR(4000) NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."PARTITION_PARAMS" ("PART_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(4000)); + +CREATE TABLE "APP"."SD_PARAMS" ("SD_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(4000)); + +CREATE TABLE "APP"."SERDE_PARAMS" ("SERDE_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(4000)); + +CREATE TABLE "APP"."SDS" ("SD_ID" BIGINT NOT NULL, "INPUT_FORMAT" VARCHAR(4000), "ISCOMPRESSED" CHAR(1) NOT NULL, "LOCATION" VARCHAR(4000), "NUM_BUCKETS" INTEGER NOT NULL, "OUTPUT_FORMAT" VARCHAR(4000), "SERDE_ID" BIGINT); + +CREATE TABLE "APP"."TYPE_FIELDS" ("TYPE_NAME" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "FIELD_NAME" VARCHAR(128) NOT NULL, "FIELD_TYPE" VARCHAR(767) NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."DBS" ("DB_ID" BIGINT NOT NULL, "DESC" VARCHAR(4000), "DB_LOCATION_URI" VARCHAR(4000) NOT NULL, "NAME" VARCHAR(128)); + +CREATE TABLE "APP"."PARTITION_KEYS" ("TBL_ID" BIGINT NOT NULL, "PKEY_COMMENT" VARCHAR(4000), "PKEY_NAME" VARCHAR(128) NOT NULL, "PKEY_TYPE" VARCHAR(767) NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."BUCKETING_COLS" ("SD_ID" BIGINT NOT NULL, "BUCKET_COL_NAME" VARCHAR(256), "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."SERDES" ("SERDE_ID" BIGINT NOT NULL, "NAME" VARCHAR(128), "SLIB" VARCHAR(4000)); + +CREATE TABLE "APP"."PARTITION_KEY_VALS" ("PART_ID" BIGINT NOT NULL, "PART_KEY_VAL" VARCHAR(256), "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."TABLE_PARAMS" ("TBL_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(4000)); + +-- ---------------------------------------------- +-- DDL Statements for indexes +-- ---------------------------------------------- + +CREATE UNIQUE INDEX "APP"."UNIQUEPARTITION" ON "APP"."PARTITIONS" ("PART_NAME", "TBL_ID"); + +CREATE UNIQUE INDEX "APP"."UNIQUETABLE" ON "APP"."TBLS" ("TBL_NAME", "DB_ID"); + +CREATE UNIQUE INDEX "APP"."UNIQUETYPE" ON "APP"."TYPES" ("TYPE_NAME"); + +CREATE UNIQUE INDEX "APP"."UNIQUEDATABASE" ON "APP"."DBS" ("NAME"); + +-- ---------------------------------------------- +-- DDL Statements for keys +-- ---------------------------------------------- + +-- primary/unique +ALTER TABLE "APP"."SERDES" ADD CONSTRAINT "SERDES_PK" PRIMARY KEY ("SERDE_ID"); + +ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_PK" PRIMARY KEY ("SD_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_PK" PRIMARY KEY ("SD_ID", "COLUMN_NAME"); + +ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_PK" PRIMARY KEY ("SERDE_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_PK" PRIMARY KEY ("PART_ID"); + +ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_PK" PRIMARY KEY ("SD_ID", "IDX"); + +ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEY_PK" PRIMARY KEY ("TBL_ID", "PKEY_NAME"); + +ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_PK" PRIMARY KEY ("TBL_ID"); + +ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_PK" PRIMARY KEY ("PART_ID", "IDX"); + +ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_PK" PRIMARY KEY ("PART_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."TYPES" ADD CONSTRAINT "TYPES_PK" PRIMARY KEY ("TYPES_ID"); + +ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_PK" PRIMARY KEY ("SD_ID"); + +ALTER TABLE "APP"."DBS" ADD CONSTRAINT "DBS_PK" PRIMARY KEY ("DB_ID"); + +ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_PK" PRIMARY KEY ("TBL_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_PK" PRIMARY KEY ("TYPE_NAME", "FIELD_NAME"); + +ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_PK" PRIMARY KEY ("SD_ID", "IDX"); + +-- foreign +ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK2" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEYS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK2" FOREIGN KEY ("DB_ID") REFERENCES "APP"."DBS" ("DB_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_FK1" FOREIGN KEY ("TYPE_NAME") REFERENCES "APP"."TYPES" ("TYPES_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +-- ---------------------------------------------- +-- DDL Statements for checks +-- ---------------------------------------------- + +ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SQL110311180827610" CHECK (ISCOMPRESSED IN ('Y','N')); + diff --git metastore/scripts/upgrade/derby/hive-schema-0.7.0.derby.sql metastore/scripts/upgrade/derby/hive-schema-0.7.0.derby.sql new file mode 100644 index 0000000..be20b82 --- /dev/null +++ metastore/scripts/upgrade/derby/hive-schema-0.7.0.derby.sql @@ -0,0 +1,221 @@ +-- Timestamp: 2011-03-11 18:08:38.513 +-- Source database is: /Users/carl/metastore/derby/mdb-0.7.0-rc0 +-- Connection URL is: jdbc:derby:/Users/carl/metastore/derby/mdb-0.7.0-rc0 +-- appendLogs: false + +-- ---------------------------------------------- +-- DDL Statements for functions +-- ---------------------------------------------- + +CREATE FUNCTION "APP"."NUCLEUS_ASCII" (C CHAR(1)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.ascii' ; + +CREATE FUNCTION "APP"."NUCLEUS_MATCHES" (TEXT VARCHAR(8000),PATTERN VARCHAR(8000)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.matches' ; + +-- ---------------------------------------------- +-- DDL Statements for tables +-- ---------------------------------------------- + +CREATE TABLE "APP"."PARTITION_KEYS" ("TBL_ID" BIGINT NOT NULL, "PKEY_COMMENT" VARCHAR(4000), "PKEY_NAME" VARCHAR(128) NOT NULL, "PKEY_TYPE" VARCHAR(767) NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."COLUMNS" ("SD_ID" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "COLUMN_NAME" VARCHAR(128) NOT NULL, "TYPE_NAME" VARCHAR(4000) NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."SERDE_PARAMS" ("SERDE_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(4000)); + +CREATE TABLE "APP"."IDXS" ("INDEX_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "DEFERRED_REBUILD" CHAR(1) NOT NULL, "INDEX_HANDLER_CLASS" VARCHAR(4000), "INDEX_NAME" VARCHAR(128), "INDEX_TBL_ID" BIGINT, "LAST_ACCESS_TIME" INTEGER NOT NULL, "ORIG_TBL_ID" BIGINT, "SD_ID" BIGINT); + +CREATE TABLE "APP"."SDS" ("SD_ID" BIGINT NOT NULL, "INPUT_FORMAT" VARCHAR(4000), "ISCOMPRESSED" CHAR(1) NOT NULL, "LOCATION" VARCHAR(4000), "NUM_BUCKETS" INTEGER NOT NULL, "OUTPUT_FORMAT" VARCHAR(4000), "SERDE_ID" BIGINT); + +CREATE TABLE "APP"."PARTITION_KEY_VALS" ("PART_ID" BIGINT NOT NULL, "PART_KEY_VAL" VARCHAR(256), "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."PART_PRIVS" ("PART_GRANT_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "GRANT_OPTION" SMALLINT NOT NULL, "GRANTOR" VARCHAR(128), "GRANTOR_TYPE" VARCHAR(128), "PART_ID" BIGINT, "PRINCIPAL_NAME" VARCHAR(128), "PRINCIPAL_TYPE" VARCHAR(128), "PART_PRIV" VARCHAR(128)); + +CREATE TABLE "APP"."INDEX_PARAMS" ("INDEX_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(4000)); + +CREATE TABLE "APP"."ROLES" ("ROLE_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "OWNER_NAME" VARCHAR(128), "ROLE_NAME" VARCHAR(128)); + +CREATE TABLE "APP"."SORT_COLS" ("SD_ID" BIGINT NOT NULL, "COLUMN_NAME" VARCHAR(128), "ORDER" INTEGER NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."SD_PARAMS" ("SD_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(4000)); + +CREATE TABLE "APP"."DB_PRIVS" ("DB_GRANT_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "DB_ID" BIGINT, "GRANT_OPTION" SMALLINT NOT NULL, "GRANTOR" VARCHAR(128), "GRANTOR_TYPE" VARCHAR(128), "PRINCIPAL_NAME" VARCHAR(128), "PRINCIPAL_TYPE" VARCHAR(128), "DB_PRIV" VARCHAR(128)); + +CREATE TABLE "APP"."PARTITIONS" ("PART_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "LAST_ACCESS_TIME" INTEGER NOT NULL, "PART_NAME" VARCHAR(767), "SD_ID" BIGINT, "TBL_ID" BIGINT); + +CREATE TABLE "APP"."TBLS" ("TBL_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "DB_ID" BIGINT, "LAST_ACCESS_TIME" INTEGER NOT NULL, "OWNER" VARCHAR(767), "RETENTION" INTEGER NOT NULL, "SD_ID" BIGINT, "TBL_NAME" VARCHAR(128), "TBL_TYPE" VARCHAR(128), "VIEW_EXPANDED_TEXT" LONG VARCHAR, "VIEW_ORIGINAL_TEXT" LONG VARCHAR); + +CREATE TABLE "APP"."TYPES" ("TYPES_ID" BIGINT NOT NULL, "TYPE_NAME" VARCHAR(128), "TYPE1" VARCHAR(767), "TYPE2" VARCHAR(767)); + +CREATE TABLE "APP"."TBL_PRIVS" ("TBL_GRANT_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "GRANT_OPTION" SMALLINT NOT NULL, "GRANTOR" VARCHAR(128), "GRANTOR_TYPE" VARCHAR(128), "PRINCIPAL_NAME" VARCHAR(128), "PRINCIPAL_TYPE" VARCHAR(128), "TBL_PRIV" VARCHAR(128), "TBL_ID" BIGINT); + +CREATE TABLE "APP"."PARTITION_PARAMS" ("PART_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(4000)); + +CREATE TABLE "APP"."TBL_COL_PRIVS" ("TBL_COLUMN_GRANT_ID" BIGINT NOT NULL, "COLUMN_NAME" VARCHAR(128), "CREATE_TIME" INTEGER NOT NULL, "GRANT_OPTION" SMALLINT NOT NULL, "GRANTOR" VARCHAR(128), "GRANTOR_TYPE" VARCHAR(128), "PRINCIPAL_NAME" VARCHAR(128), "PRINCIPAL_TYPE" VARCHAR(128), "TBL_COL_PRIV" VARCHAR(128), "TBL_ID" BIGINT); + +CREATE TABLE "APP"."SERDES" ("SERDE_ID" BIGINT NOT NULL, "NAME" VARCHAR(128), "SLIB" VARCHAR(4000)); + +CREATE TABLE "APP"."DBS" ("DB_ID" BIGINT NOT NULL, "DESC" VARCHAR(4000), "DB_LOCATION_URI" VARCHAR(4000) NOT NULL, "NAME" VARCHAR(128)); + +CREATE TABLE "APP"."ROLE_MAP" ("ROLE_GRANT_ID" BIGINT NOT NULL, "ADD_TIME" INTEGER NOT NULL, "GRANT_OPTION" SMALLINT NOT NULL, "GRANTOR" VARCHAR(128), "GRANTOR_TYPE" VARCHAR(128), "PRINCIPAL_NAME" VARCHAR(128), "PRINCIPAL_TYPE" VARCHAR(128), "ROLE_ID" BIGINT); + +CREATE TABLE "APP"."GLOBAL_PRIVS" ("USER_GRANT_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "GRANT_OPTION" SMALLINT NOT NULL, "GRANTOR" VARCHAR(128), "GRANTOR_TYPE" VARCHAR(128), "PRINCIPAL_NAME" VARCHAR(128), "PRINCIPAL_TYPE" VARCHAR(128), "USER_PRIV" VARCHAR(128)); + +CREATE TABLE "APP"."PART_COL_PRIVS" ("PART_COLUMN_GRANT_ID" BIGINT NOT NULL, "COLUMN_NAME" VARCHAR(128), "CREATE_TIME" INTEGER NOT NULL, "GRANT_OPTION" SMALLINT NOT NULL, "GRANTOR" VARCHAR(128), "GRANTOR_TYPE" VARCHAR(128), "PART_ID" BIGINT, "PRINCIPAL_NAME" VARCHAR(128), "PRINCIPAL_TYPE" VARCHAR(128), "PART_COL_PRIV" VARCHAR(128)); + +CREATE TABLE "APP"."TYPE_FIELDS" ("TYPE_NAME" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "FIELD_NAME" VARCHAR(128) NOT NULL, "FIELD_TYPE" VARCHAR(767) NOT NULL, "IDX" INTEGER NOT NULL); + +CREATE TABLE "APP"."TABLE_PARAMS" ("TBL_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(4000)); + +CREATE TABLE "APP"."DATABASE_PARAMS" ("DB_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(180) NOT NULL, "PARAM_VALUE" VARCHAR(4000)); + +CREATE TABLE "APP"."BUCKETING_COLS" ("SD_ID" BIGINT NOT NULL, "BUCKET_COL_NAME" VARCHAR(256), "IDX" INTEGER NOT NULL); + +-- ---------------------------------------------- +-- DDL Statements for indexes +-- ---------------------------------------------- + +CREATE UNIQUE INDEX "APP"."UNIQUETYPE" ON "APP"."TYPES" ("TYPE_NAME"); + +CREATE INDEX "APP"."PARTITIONCOLUMNPRIVILEGEINDEX" ON "APP"."PART_COL_PRIVS" ("PART_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "PART_COL_PRIV", "GRANTOR", "GRANTOR_TYPE"); + +CREATE UNIQUE INDEX "APP"."USERROLEMAPINDEX" ON "APP"."ROLE_MAP" ("PRINCIPAL_NAME", "ROLE_ID", "GRANTOR", "GRANTOR_TYPE"); + +CREATE INDEX "APP"."TABLEPRIVILEGEINDEX" ON "APP"."TBL_PRIVS" ("TBL_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "TBL_PRIV", "GRANTOR", "GRANTOR_TYPE"); + +CREATE UNIQUE INDEX "APP"."UNIQUEPARTITION" ON "APP"."PARTITIONS" ("PART_NAME", "TBL_ID"); + +CREATE UNIQUE INDEX "APP"."UNIQUETABLE" ON "APP"."TBLS" ("TBL_NAME", "DB_ID"); + +CREATE INDEX "APP"."PARTPRIVILEGEINDEX" ON "APP"."PART_PRIVS" ("PART_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "PART_PRIV", "GRANTOR", "GRANTOR_TYPE"); + +CREATE UNIQUE INDEX "APP"."UNIQUEDATABASE" ON "APP"."DBS" ("NAME"); + +CREATE UNIQUE INDEX "APP"."ROLEENTITYINDEX" ON "APP"."ROLES" ("ROLE_NAME"); + +CREATE UNIQUE INDEX "APP"."UNIQUEINDEX" ON "APP"."IDXS" ("INDEX_NAME", "ORIG_TBL_ID"); + +CREATE UNIQUE INDEX "APP"."DBPRIVILEGEINDEX" ON "APP"."DB_PRIVS" ("DB_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "DB_PRIV", "GRANTOR", "GRANTOR_TYPE"); + +CREATE INDEX "APP"."TABLECOLUMNPRIVILEGEINDEX" ON "APP"."TBL_COL_PRIVS" ("TBL_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "TBL_COL_PRIV", "GRANTOR", "GRANTOR_TYPE"); + +CREATE UNIQUE INDEX "APP"."GLOBALPRIVILEGEINDEX" ON "APP"."GLOBAL_PRIVS" ("PRINCIPAL_NAME", "PRINCIPAL_TYPE", "USER_PRIV", "GRANTOR", "GRANTOR_TYPE"); + +-- ---------------------------------------------- +-- DDL Statements for keys +-- ---------------------------------------------- + +-- primary/unique +ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_PK" PRIMARY KEY ("PART_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."TYPES" ADD CONSTRAINT "TYPES_PK" PRIMARY KEY ("TYPES_ID"); + +ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_PK" PRIMARY KEY ("TYPE_NAME", "FIELD_NAME"); + +ALTER TABLE "APP"."PART_COL_PRIVS" ADD CONSTRAINT "PART_COL_PRIVS_PK" PRIMARY KEY ("PART_COLUMN_GRANT_ID"); + +ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_PK" PRIMARY KEY ("SD_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEY_PK" PRIMARY KEY ("TBL_ID", "PKEY_NAME"); + +ALTER TABLE "APP"."ROLE_MAP" ADD CONSTRAINT "ROLE_MAP_PK" PRIMARY KEY ("ROLE_GRANT_ID"); + +ALTER TABLE "APP"."TBL_PRIVS" ADD CONSTRAINT "TBL_PRIVS_PK" PRIMARY KEY ("TBL_GRANT_ID"); + +ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_PK" PRIMARY KEY ("TBL_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_PK" PRIMARY KEY ("PART_ID"); + +ALTER TABLE "APP"."SERDES" ADD CONSTRAINT "SERDES_PK" PRIMARY KEY ("SERDE_ID"); + +ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_PK" PRIMARY KEY ("SD_ID", "IDX"); + +ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_PK" PRIMARY KEY ("SD_ID", "COLUMN_NAME"); + +ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_PK" PRIMARY KEY ("TBL_ID"); + +ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_PK" PRIMARY KEY ("SD_ID", "IDX"); + +ALTER TABLE "APP"."PART_PRIVS" ADD CONSTRAINT "PART_PRIVS_PK" PRIMARY KEY ("PART_GRANT_ID"); + +ALTER TABLE "APP"."DBS" ADD CONSTRAINT "DBS_PK" PRIMARY KEY ("DB_ID"); + +ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_PK" PRIMARY KEY ("SERDE_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."ROLES" ADD CONSTRAINT "ROLES_PK" PRIMARY KEY ("ROLE_ID"); + +ALTER TABLE "APP"."IDXS" ADD CONSTRAINT "IDXS_PK" PRIMARY KEY ("INDEX_ID"); + +ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_PK" PRIMARY KEY ("PART_ID", "IDX"); + +ALTER TABLE "APP"."DB_PRIVS" ADD CONSTRAINT "DB_PRIVS_PK" PRIMARY KEY ("DB_GRANT_ID"); + +ALTER TABLE "APP"."INDEX_PARAMS" ADD CONSTRAINT "INDEX_PARAMS_PK" PRIMARY KEY ("INDEX_ID", "PARAM_KEY"); + +ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_PK" PRIMARY KEY ("SD_ID"); + +ALTER TABLE "APP"."TBL_COL_PRIVS" ADD CONSTRAINT "TBL_COL_PRIVS_PK" PRIMARY KEY ("TBL_COLUMN_GRANT_ID"); + +ALTER TABLE "APP"."GLOBAL_PRIVS" ADD CONSTRAINT "GLOBAL_PRIVS_PK" PRIMARY KEY ("USER_GRANT_ID"); + +ALTER TABLE "APP"."DATABASE_PARAMS" ADD CONSTRAINT "DATABASE_PARAMS_PK" PRIMARY KEY ("DB_ID", "PARAM_KEY"); + +-- foreign +ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_FK1" FOREIGN KEY ("TYPE_NAME") REFERENCES "APP"."TYPES" ("TYPES_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PART_COL_PRIVS" ADD CONSTRAINT "PART_COL_PRIVS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEYS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."ROLE_MAP" ADD CONSTRAINT "ROLE_MAP_FK1" FOREIGN KEY ("ROLE_ID") REFERENCES "APP"."ROLES" ("ROLE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TBL_PRIVS" ADD CONSTRAINT "TBL_PRIVS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK2" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK2" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK1" FOREIGN KEY ("DB_ID") REFERENCES "APP"."DBS" ("DB_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PART_PRIVS" ADD CONSTRAINT "PART_PRIVS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."IDXS" ADD CONSTRAINT "IDXS_FK1" FOREIGN KEY ("INDEX_TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."IDXS" ADD CONSTRAINT "IDXS_FK2" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."IDXS" ADD CONSTRAINT "IDXS_FK3" FOREIGN KEY ("ORIG_TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."DB_PRIVS" ADD CONSTRAINT "DB_PRIVS_FK1" FOREIGN KEY ("DB_ID") REFERENCES "APP"."DBS" ("DB_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."INDEX_PARAMS" ADD CONSTRAINT "INDEX_PARAMS_FK1" FOREIGN KEY ("INDEX_ID") REFERENCES "APP"."IDXS" ("INDEX_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."TBL_COL_PRIVS" ADD CONSTRAINT "TBL_COL_PRIVS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "APP"."DATABASE_PARAMS" ADD CONSTRAINT "DATABASE_PARAMS_FK1" FOREIGN KEY ("DB_ID") REFERENCES "APP"."DBS" ("DB_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; + +-- ---------------------------------------------- +-- DDL Statements for checks +-- ---------------------------------------------- + +ALTER TABLE "APP"."IDXS" ADD CONSTRAINT "SQL110311180836420" CHECK (DEFERRED_REBUILD IN ('Y','N')); + +ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SQL110311180837030" CHECK (ISCOMPRESSED IN ('Y','N')); + diff --git metastore/scripts/upgrade/derby/upgrade-0.5.0-to-0.6.0.derby.sql metastore/scripts/upgrade/derby/upgrade-0.5.0-to-0.6.0.derby.sql new file mode 100644 index 0000000..493069a --- /dev/null +++ metastore/scripts/upgrade/derby/upgrade-0.5.0-to-0.6.0.derby.sql @@ -0,0 +1,5 @@ +-- Upgrade MetaStore schema from 0.5.0 to 0.6.0 +RUN '001-HIVE-972.derby.sql'; +RUN '002-HIVE-1068.derby.sql'; +RUN '003-HIVE-675.derby.sql'; +RUN '004-HIVE-1364.derby.sql'; diff --git metastore/scripts/upgrade/derby/upgrade-0.6.0-to-0.7.0.derby.sql metastore/scripts/upgrade/derby/upgrade-0.6.0-to-0.7.0.derby.sql new file mode 100644 index 0000000..9c5c6f3 --- /dev/null +++ metastore/scripts/upgrade/derby/upgrade-0.6.0-to-0.7.0.derby.sql @@ -0,0 +1,4 @@ +-- Upgrade MetaStore schema from 0.6.0 to 0.7.0 +RUN '005-HIVE-417.derby.sql'; +RUN '006-HIVE-1823.derby.sql'; +RUN '007-HIVE-78.derby.sql'; diff --git metastore/scripts/upgrade/derby/upgrade-0.6.0.derby.sql metastore/scripts/upgrade/derby/upgrade-0.6.0.derby.sql deleted file mode 100644 index 77a0f39..0000000 --- metastore/scripts/upgrade/derby/upgrade-0.6.0.derby.sql +++ /dev/null @@ -1,27 +0,0 @@ --- HIVE-972: Support views -ALTER TABLE "TBLS" ADD "VIEW_ORIGINAL_TEXT" LONG VARCHAR DEFAULT NULL; -ALTER TABLE "TBLS" ADD "VIEW_EXPANDED_TEXT" LONG VARCHAR DEFAULT NULL; - --- HIVE-1068: CREATE VIEW followup: add a 'table type' enum --- attribute in metastore -ALTER TABLE "TBLS" ADD COLUMN "TBL_TYPE" VARCHAR(128); - --- HIVE-675: Add database/schema support for Hive QL -ALTER TABLE "DBS" ALTER "DESC" SET DATA TYPE VARCHAR(4000); -ALTER TABLE "DBS" ADD "DB_LOCATION_URI" VARCHAR(4000) NOT NULL DEFAULT ''; - --- HIVE-1364: Increase the maximum length of various metastore fields, --- and remove TYPE_NAME from COLUMNS primary key -ALTER TABLE "TBLS" ALTER "OWNER" SET DATA TYPE VARCHAR(767); -ALTER TABLE "COLUMNS" ALTER "TYPE_NAME" SET DATA TYPE VARCHAR(4000); -ALTER TABLE "PARTITION_KEYS" ALTER "PKEY_COMMENT" SET DATA TYPE VARCHAR(4000); -ALTER TABLE "SD_PARAMS" ALTER "PARAM_VALUE" SET DATA TYPE VARCHAR(4000); -ALTER TABLE "SDS" ALTER "INPUT_FORMAT" SET DATA TYPE VARCHAR(4000); -ALTER TABLE "SDS" ALTER "LOCATION" SET DATA TYPE VARCHAR(4000); -ALTER TABLE "SDS" ALTER "OUTPUT_FORMAT" SET DATA TYPE VARCHAR(4000); -ALTER TABLE "SERDE_PARAMS" ALTER "PARAM_VALUE" SET DATA TYPE VARCHAR(4000); -ALTER TABLE "SERDES" ALTER "SLIB" SET DATA TYPE VARCHAR(4000); -ALTER TABLE "TABLE_PARAMS" ALTER "PARAM_VALUE" SET DATA TYPE VARCHAR(4000); -ALTER TABLE "COLUMNS" DROP PRIMARY KEY; -ALTER TABLE "COLUMNS" ADD PRIMARY KEY ("SD_ID", "COLUMN_NAME"); -ALTER TABLE "PARTITION_PARAMS" ALTER "PARAM_VALUE" SET DATA TYPE VARCHAR(4000); diff --git metastore/scripts/upgrade/derby/upgrade-0.7.0.derby.sql metastore/scripts/upgrade/derby/upgrade-0.7.0.derby.sql deleted file mode 100644 index ccad4e7..0000000 --- metastore/scripts/upgrade/derby/upgrade-0.7.0.derby.sql +++ /dev/null @@ -1,235 +0,0 @@ --- --- HIVE-417 Implement Indexing in Hive --- - -CREATE TABLE "IDXS" ( - "INDEX_ID" BIGINT NOT NULL, - "CREATE_TIME" INTEGER NOT NULL, - "DEFERRED_REBUILD" CHAR(1) NOT NULL, - "INDEX_HANDLER_CLASS" VARCHAR(256), - "INDEX_NAME" VARCHAR(128), - "INDEX_TBL_ID" BIGINT, - "LAST_ACCESS_TIME" INTEGER NOT NULL, - "ORIG_TBL_ID" BIGINT, - "SD_ID" BIGINT); - -ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK1" - FOREIGN KEY ("SD_ID") REFERENCES "SDS" ("SD_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK2" - FOREIGN KEY ("INDEX_TBL_ID") REFERENCES "TBLS" ("TBL_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK3" - FOREIGN KEY ("ORIG_TBL_ID") REFERENCES "TBLS" ("TBL_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_PK" - PRIMARY KEY ("INDEX_ID"); - -ALTER TABLE "IDXS" ADD CONSTRAINT "DEFERRED_REBUILD_CHECK" - CHECK (DEFERRED_REBUILD IN ('Y','N')); - - -CREATE TABLE "INDEX_PARAMS" ( - "INDEX_ID" BIGINT NOT NULL, - "PARAM_KEY" VARCHAR(256) NOT NULL, - "PARAM_VALUE" VARCHAR(767)); - -ALTER TABLE "INDEX_PARAMS" ADD CONSTRAINT "INDEX_PARAMS_FK1" - FOREIGN KEY ("INDEX_ID") REFERENCES "IDXS" ("INDEX_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "INDEX_PARAMS" ADD CONSTRAINT "INDEX_PARAMS_PK" - PRIMARY KEY ("INDEX_ID", "PARAM_KEY"); - -CREATE UNIQUE INDEX "UNIQUEINDEX" ON "IDXS" ("INDEX_NAME", "ORIG_TBL_ID"); - - --- --- HIVE-1823 Upgrade the database thrift interface to allow parameters key-value pairs --- -CREATE TABLE "DATABASE_PARAMS" ( - "DB_ID" BIGINT NOT NULL, - "PARAM_KEY" VARCHAR(180) NOT NULL, - "PARAM_VALUE" VARCHAR(4000)); - -ALTER TABLE "DATABASE_PARAMS" ADD CONSTRAINT "DATABASE_PARAMS_FK1" - FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "DATABASE_PARAMS" ADD CONSTRAINT "DATABASE_PARAMS_PK" - PRIMARY KEY ("DB_ID", "PARAM_KEY"); - -ALTER TABLE "DBS" DROP COLUMN "PARAMETERS"; - --- --- HIVE-78 Authorization model for Hive --- - -CREATE TABLE "DB_PRIVS" ( - "DB_GRANT_ID" BIGINT NOT NULL, - "CREATE_TIME" INTEGER NOT NULL, - "DB_ID" BIGINT, - "GRANT_OPTION" SMALLINT NOT NULL, - "GRANTOR" VARCHAR(128), - "GRANTOR_TYPE" VARCHAR(128), - "PRINCIPAL_NAME" VARCHAR(128), - "PRINCIPAL_TYPE" VARCHAR(128), - "DB_PRIV" VARCHAR(128)); - -ALTER TABLE "DB_PRIVS" ADD CONSTRAINT "DB_PRIVS_FK1" - FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "DB_PRIVS" ADD CONSTRAINT "DB_PRIVS_PK" - PRIMARY KEY ("DB_GRANT_ID"); - -CREATE UNIQUE INDEX "DBPRIVILEGEINDEX" ON "DB_PRIVS" ( - "DB_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", - "DB_PRIV", "GRANTOR", "GRANTOR_TYPE"); - - -CREATE TABLE "PART_COL_PRIVS" ( - "PART_COLUMN_GRANT_ID" BIGINT NOT NULL, - "COLUMN_NAME" VARCHAR(128), - "CREATE_TIME" INTEGER NOT NULL, - "GRANT_OPTION" SMALLINT NOT NULL, - "GRANTOR" VARCHAR(128), - "GRANTOR_TYPE" VARCHAR(128), - "PART_ID" BIGINT, - "PRINCIPAL_NAME" VARCHAR(128), - "PRINCIPAL_TYPE" VARCHAR(128), - "PART_COL_PRIV" VARCHAR(128)); - -ALTER TABLE "PART_COL_PRIVS" ADD CONSTRAINT "PART_COL_PRIVS_FK1" - FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "PART_COL_PRIVS" ADD CONSTRAINT "PART_COL_PRIVS_PK" - PRIMARY KEY ("PART_COLUMN_GRANT_ID"); - -CREATE INDEX "PARTITIONCOLUMNPRIVILEGEINDEX" ON "PART_COL_PRIVS" ( - "PART_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", - "PART_COL_PRIV", "GRANTOR", "GRANTOR_TYPE"); - -CREATE TABLE "PART_PRIVS" ( - "PART_GRANT_ID" BIGINT NOT NULL, - "CREATE_TIME" INTEGER NOT NULL, - "GRANT_OPTION" SMALLINT NOT NULL, - "GRANTOR" VARCHAR(128), - "GRANTOR_TYPE" VARCHAR(128), - "PART_ID" BIGINT, - "PRINCIPAL_NAME" VARCHAR(128), - "PRINCIPAL_TYPE" VARCHAR(128), - "PART_PRIV" VARCHAR(128)); - -ALTER TABLE "PART_PRIVS" ADD CONSTRAINT "PART_PRIVS_FK1" - FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "PART_PRIVS" ADD CONSTRAINT "PART_PRIVS_PK" - PRIMARY KEY ("PART_GRANT_ID"); - -CREATE INDEX "PARTPRIVILEGEINDEX" ON "PART_PRIVS" ( - "PART_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", - "PART_PRIV", "GRANTOR", "GRANTOR_TYPE"); - - -CREATE TABLE "ROLES" ( - "ROLE_ID" BIGINT NOT NULL, - "CREATE_TIME" INTEGER NOT NULL, - "OWNER_NAME" VARCHAR(128), - "ROLE_NAME" VARCHAR(128)); - -ALTER TABLE "ROLES" ADD CONSTRAINT "ROLES_PK" - PRIMARY KEY ("ROLE_ID"); - -CREATE UNIQUE INDEX "ROLEENTITYINDEX" ON "ROLES" ("ROLE_NAME"); - - -CREATE TABLE "ROLE_MAP" ( - "ROLE_GRANT_ID" BIGINT NOT NULL, - "ADD_TIME" INTEGER NOT NULL, - "GRANT_OPTION" SMALLINT NOT NULL, - "GRANTOR" VARCHAR(128), - "GRANTOR_TYPE" VARCHAR(128), - "PRINCIPAL_NAME" VARCHAR(128), - "PRINCIPAL_TYPE" VARCHAR(128), - "ROLE_ID" BIGINT); - -ALTER TABLE "ROLE_MAP" ADD CONSTRAINT "ROLE_MAP_FK1" - FOREIGN KEY ("ROLE_ID") REFERENCES "ROLES" ("ROLE_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "ROLE_MAP" ADD CONSTRAINT "ROLE_MAP_PK" - PRIMARY KEY ("ROLE_GRANT_ID"); - -CREATE UNIQUE INDEX "USERROLEMAPINDEX" ON "ROLE_MAP" ( - "PRINCIPAL_NAME", "ROLE_ID", "GRANTOR", "GRANTOR_TYPE"); - - -CREATE TABLE "TBL_COL_PRIVS" ( - "TBL_COLUMN_GRANT_ID" BIGINT NOT NULL, - "COLUMN_NAME" VARCHAR(128), - "CREATE_TIME" INTEGER NOT NULL, - "GRANT_OPTION" SMALLINT NOT NULL, - "GRANTOR" VARCHAR(128), - "GRANTOR_TYPE" VARCHAR(128), - "PRINCIPAL_NAME" VARCHAR(128), - "PRINCIPAL_TYPE" VARCHAR(128), - "TBL_COL_PRIV" VARCHAR(128), - "TBL_ID" BIGINT); - -ALTER TABLE "TBL_COL_PRIVS" ADD CONSTRAINT "TBL_COL_PRIVS_FK1" - FOREIGN KEY ("TBL_ID") REFERENCES "TBLS" ("TBL_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "TBL_COL_PRIVS" ADD CONSTRAINT "TBL_COL_PRIVS_PK" - PRIMARY KEY ("TBL_COLUMN_GRANT_ID"); - -CREATE INDEX "TABLECOLUMNPRIVILEGEINDEX" ON "TBL_COL_PRIVS" ( - "TBL_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", - "TBL_COL_PRIV", "GRANTOR", "GRANTOR_TYPE"); - - -CREATE TABLE "TBL_PRIVS" ( - "TBL_GRANT_ID" BIGINT NOT NULL, - "CREATE_TIME" INTEGER NOT NULL, - "GRANT_OPTION" SMALLINT NOT NULL, - "GRANTOR" VARCHAR(128), - "GRANTOR_TYPE" VARCHAR(128), - "PRINCIPAL_NAME" VARCHAR(128), - "PRINCIPAL_TYPE" VARCHAR(128), - "TBL_PRIV" VARCHAR(128), - "TBL_ID" BIGINT); - -ALTER TABLE "TBL_PRIVS" ADD CONSTRAINT "TBL_PRIVS_FK1" - FOREIGN KEY ("TBL_ID") REFERENCES "TBLS" ("TBL_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "TBL_PRIVS" ADD CONSTRAINT "TBL_PRIVS_PK" - PRIMARY KEY ("TBL_GRANT_ID"); - -CREATE INDEX "TABLEPRIVILEGEINDEX" ON "TBL_PRIVS" ( - "TBL_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", - "TBL_PRIV", "GRANTOR", "GRANTOR_TYPE"); - - -CREATE TABLE "GLOBAL_PRIVS" ( - "USER_GRANT_ID" BIGINT NOT NULL, - "CREATE_TIME" INTEGER NOT NULL, - "GRANT_OPTION" SMALLINT NOT NULL, - "GRANTOR" VARCHAR(128), - "GRANTOR_TYPE" VARCHAR(128), - "PRINCIPAL_NAME" VARCHAR(128), - "PRINCIPAL_TYPE" VARCHAR(128), - "USER_PRIV" VARCHAR(128)); - -ALTER TABLE "GLOBAL_PRIVS" ADD CONSTRAINT "GLOBAL_PRIVS_PK" - PRIMARY KEY ("USER_GRANT_ID"); - -CREATE UNIQUE INDEX "GLOBALPRIVILEGEINDEX" ON "GLOBAL_PRIVS" ( - "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "USER_PRIV", - "GRANTOR", "GRANTOR_TYPE"); diff --git metastore/scripts/upgrade/mysql/001-HIVE-972.mysql.sql metastore/scripts/upgrade/mysql/001-HIVE-972.mysql.sql new file mode 100644 index 0000000..d58c824 --- /dev/null +++ metastore/scripts/upgrade/mysql/001-HIVE-972.mysql.sql @@ -0,0 +1,3 @@ +SELECT '< HIVE-972: Support views >' AS ' '; +ALTER TABLE `TBLS` ADD `VIEW_EXPANDED_TEXT` mediumtext; +ALTER TABLE `TBLS` ADD `VIEW_ORIGINAL_TEXT` mediumtext; diff --git metastore/scripts/upgrade/mysql/002-HIVE-1068.mysql.sql metastore/scripts/upgrade/mysql/002-HIVE-1068.mysql.sql new file mode 100644 index 0000000..5765961 --- /dev/null +++ metastore/scripts/upgrade/mysql/002-HIVE-1068.mysql.sql @@ -0,0 +1,2 @@ +SELECT '< HIVE-1068: CREATE VIEW followup: add a "table type" enum attribute in metastore >' AS ' '; +ALTER TABLE `TBLS` ADD `TBL_TYPE` VARCHAR(128); diff --git metastore/scripts/upgrade/mysql/003-HIVE-675.mysql.sql metastore/scripts/upgrade/mysql/003-HIVE-675.mysql.sql new file mode 100644 index 0000000..d2dd8f0 --- /dev/null +++ metastore/scripts/upgrade/mysql/003-HIVE-675.mysql.sql @@ -0,0 +1,3 @@ +SELECT '< HIVE-675: Add database/schema support for Hive QL >' AS ' '; +ALTER TABLE `DBS` MODIFY `DESC` VARCHAR(4000); +ALTER TABLE `DBS` ADD `DB_LOCATION_URI` VARCHAR(4000) NOT NULL DEFAULT ''; diff --git metastore/scripts/upgrade/mysql/004-HIVE-1364.mysql.sql metastore/scripts/upgrade/mysql/004-HIVE-1364.mysql.sql new file mode 100644 index 0000000..fbd11d1 --- /dev/null +++ metastore/scripts/upgrade/mysql/004-HIVE-1364.mysql.sql @@ -0,0 +1,14 @@ +SELECT '< HIVE-1364: Increase the maximum length of various metastore fields >' AS ' '; +ALTER TABLE `TBLS` MODIFY `OWNER` VARCHAR(767); +ALTER TABLE `COLUMNS` DROP PRIMARY KEY; +ALTER TABLE `COLUMNS` ADD PRIMARY KEY (`SD_ID`, `COLUMN_NAME`); +ALTER TABLE `COLUMNS` MODIFY `TYPE_NAME` VARCHAR(4000); +ALTER TABLE `PARTITION_KEYS` MODIFY `PKEY_COMMENT` VARCHAR(4000); +ALTER TABLE `SD_PARAMS` MODIFY `PARAM_VALUE` VARCHAR(4000); +ALTER TABLE `SDS` MODIFY `INPUT_FORMAT` VARCHAR(4000); +ALTER TABLE `SDS` MODIFY `LOCATION` VARCHAR(4000); +ALTER TABLE `SDS` MODIFY `OUTPUT_FORMAT` VARCHAR(4000); +ALTER TABLE `SERDE_PARAMS` MODIFY `PARAM_VALUE` VARCHAR(4000); +ALTER TABLE `SERDES` MODIFY `SLIB` VARCHAR(4000); +ALTER TABLE `TABLE_PARAMS` MODIFY `PARAM_VALUE` VARCHAR(4000); +ALTER TABLE `PARTITION_PARAMS` MODIFY `PARAM_VALUE` VARCHAR(4000); diff --git metastore/scripts/upgrade/mysql/005-HIVE-417.mysql.sql metastore/scripts/upgrade/mysql/005-HIVE-417.mysql.sql new file mode 100644 index 0000000..3ccf454 --- /dev/null +++ metastore/scripts/upgrade/mysql/005-HIVE-417.mysql.sql @@ -0,0 +1,28 @@ +SELECT '< HIVE-417 Implement Indexing in Hive >' AS ' '; +CREATE TABLE IF NOT EXISTS `IDXS` ( + `INDEX_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `DEFERRED_REBUILD` bit(1) NOT NULL, + `INDEX_HANDLER_CLASS` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `INDEX_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `INDEX_TBL_ID` bigint(20) DEFAULT NULL, + `LAST_ACCESS_TIME` int(11) NOT NULL, + `ORIG_TBL_ID` bigint(20) DEFAULT NULL, + `SD_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`INDEX_ID`), + UNIQUE KEY `UNIQUEINDEX` (`INDEX_NAME`,`ORIG_TBL_ID`), + KEY `IDXS_FK1` (`SD_ID`), + KEY `IDXS_FK2` (`INDEX_TBL_ID`), + KEY `IDXS_FK3` (`ORIG_TBL_ID`), + CONSTRAINT `IDXS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`), + CONSTRAINT `IDXS_FK2` FOREIGN KEY (`INDEX_TBL_ID`) REFERENCES `TBLS` (`TBL_ID`), + CONSTRAINT `IDXS_FK3` FOREIGN KEY (`ORIG_TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `INDEX_PARAMS` ( + `INDEX_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`INDEX_ID`,`PARAM_KEY`), + CONSTRAINT `INDEX_PARAMS_FK1` FOREIGN KEY (`INDEX_ID`) REFERENCES `IDXS` (`INDEX_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; diff --git metastore/scripts/upgrade/mysql/006-HIVE-1823.mysql.sql metastore/scripts/upgrade/mysql/006-HIVE-1823.mysql.sql new file mode 100644 index 0000000..3e725f8 --- /dev/null +++ metastore/scripts/upgrade/mysql/006-HIVE-1823.mysql.sql @@ -0,0 +1,8 @@ +SELECT '< HIVE-1823 Upgrade the database thrift interface to allow parameters key-value pairs >' AS ' '; +CREATE TABLE IF NOT EXISTS `DATABASE_PARAMS` ( + `DB_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(180) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`DB_ID`,`PARAM_KEY`), + CONSTRAINT `DATABASE_PARAMS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; diff --git metastore/scripts/upgrade/mysql/007-HIVE-78.mysql.sql metastore/scripts/upgrade/mysql/007-HIVE-78.mysql.sql new file mode 100644 index 0000000..d62ae74 --- /dev/null +++ metastore/scripts/upgrade/mysql/007-HIVE-78.mysql.sql @@ -0,0 +1,114 @@ +SELECT '< HIVE-78 Authorization model for Hive >' AS ' '; + +CREATE TABLE IF NOT EXISTS `ROLES` ( + `ROLE_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `OWNER_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `ROLE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`ROLE_ID`), + UNIQUE KEY `ROLEENTITYINDEX` (`ROLE_NAME`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `ROLE_MAP` ( + `ROLE_GRANT_ID` bigint(20) NOT NULL, + `ADD_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `ROLE_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`ROLE_GRANT_ID`), + UNIQUE KEY `USERROLEMAPINDEX` (`PRINCIPAL_NAME`,`ROLE_ID`,`GRANTOR`,`GRANTOR_TYPE`), + CONSTRAINT `ROLE_MAP_FK1` FOREIGN KEY (`ROLE_ID`) REFERENCES `ROLES` (`ROLE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `GLOBAL_PRIVS` ( + `USER_GRANT_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `USER_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`USER_GRANT_ID`), + UNIQUE KEY `GLOBALPRIVILEGEINDEX` (`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`USER_PRIV`,`GRANTOR`,`GRANTOR_TYPE`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `DB_PRIVS` ( + `DB_GRANT_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `DB_ID` bigint(20) DEFAULT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `DB_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`DB_GRANT_ID`), + UNIQUE KEY `DBPRIVILEGEINDEX` (`DB_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`DB_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), + CONSTRAINT `DB_PRIVS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `TBL_PRIVS` ( + `TBL_GRANT_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TBL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TBL_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`TBL_GRANT_ID`), + KEY `TABLEPRIVILEGEINDEX` (`TBL_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`TBL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), + CONSTRAINT `TBL_PRIVS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `TBL_COL_PRIVS` ( + `TBL_COLUMN_GRANT_ID` bigint(20) NOT NULL, + `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `CREATE_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TBL_COL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TBL_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`TBL_COLUMN_GRANT_ID`), + KEY `TABLECOLUMNPRIVILEGEINDEX` (`TBL_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`TBL_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), + CONSTRAINT `TBL_COL_PRIVS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `PART_PRIVS` ( + `PART_GRANT_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PART_ID` bigint(20) DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PART_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`PART_GRANT_ID`), + KEY `PARTPRIVILEGEINDEX` (`PART_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), + CONSTRAINT `PART_PRIVS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `PART_COL_PRIVS` ( + `PART_COLUMN_GRANT_ID` bigint(20) NOT NULL, + `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `CREATE_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PART_ID` bigint(20) DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PART_COL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`PART_COLUMN_GRANT_ID`), + KEY `PARTITIONCOLUMNPRIVILEGEINDEX` (`PART_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), + CONSTRAINT `PART_COL_PRIVS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; diff --git metastore/scripts/upgrade/mysql/README metastore/scripts/upgrade/mysql/README index 3d4360f..243da12 100644 --- metastore/scripts/upgrade/mysql/README +++ metastore/scripts/upgrade/mysql/README @@ -1,9 +1,101 @@ +Hive MetaStore Upgrade HowTo +============================ -1) Shutdown your metastore instance. +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 you 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 is very important that no one else + accesses or modifies the contents of database while you are + performing the schema upgrade. + +2) Create a backup of your MySQL metastore database. This will allow + you to revert any changes made during the upgrade process if + something goes wrong. The mysqldump utility is the easiest way to + create a backup of a MySQL database: -2) Perform a backup of your MySQL metastore database: % mysqldump --opt > metastore_backup.sql -3) Execute the upgrade script: - % mysql --user= --password= \ - < upgrade-0.6.0.mysql.sql +3) Dump your metastore database schema to a file. We use the mysqldump + utility again, but this time with a command line option that + specifies we are only interested in dumping the DDL statements + required to create the schema: + + % mysqldump --no-data > my-schema-x.y.z.mysql.sql + +4) The schema upgrade scripts assume that the schema you are 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 + corresponding to each of the released versions of Hive. You can + determine differences between your schema and the official schema + by diffing the contents of the official dump with the schema dump + you created in the previous step. Some differences are acceptable + and will not interfere with the upgrade process, but others need to + be resolved manually or the upgrade scripts will fail to complete. + + * Missing Tables: Hive's default configuration causes the MetaStore + to create schema elements only when they are needed. Some tables + may be missing from your MetaStore schema if you have not created + the corresponding Hive catalog objects, e.g. the PARTITIONS table + will probably not exist if you have not created any table + partitions in your MetaStore. You MUST create these missing tables + before running the upgrade scripts. The easiest way to do this 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 exist in + your schema will be ignored, and those which don't exist will get + created. + + * Extra Tables: Your schema may include a table named NUCLEUS_TABLES + or a table named SEQUENCE_TABLE. These tables are managed + by the DataNucleus ORM layer and will 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 two foreign + key constraints named PARTITIONS_FK1 and PARTITIONS_FK2 which + reference SDS.SD_ID and TBLS.TBL_ID respectively. However, in your + schema you may find that PARTITIONS_FK1 references TBLS.TBL_ID and + PARTITIONS_FK2 references SDS.SD_ID. Either version is acceptable + -- the only requirement is that these constraints actually exist. + + * Differences in Column/Constraint Names: Your schema may contain + tables with columns named "IDX" or unique keys named + "UNIQUE". If you find either of these in your schema you + will need to change the names to "INTEGER_IDX" and + "UNIQUE_" before running the upgrade scripts. For more + background on this issue please refer to HIVE-1435. + +5) You are now ready to run 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 will 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 ; + 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 you 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 is 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 will want to compare your schema dump against the contents of + hive-schema-0.7.0.mysql.sql diff --git metastore/scripts/upgrade/mysql/hive-schema-0.3.0.mysql.sql metastore/scripts/upgrade/mysql/hive-schema-0.3.0.mysql.sql new file mode 100644 index 0000000..3cbee90 --- /dev/null +++ metastore/scripts/upgrade/mysql/hive-schema-0.3.0.mysql.sql @@ -0,0 +1,305 @@ +-- MySQL dump 10.13 Distrib 5.1.48, for apple-darwin10.3.0 (i386) +-- +-- Host: localhost Database: mdb +-- ------------------------------------------------------ +-- Server version 5.1.48 + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +-- +-- Table structure for table `BUCKETING_COLS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `BUCKETING_COLS` ( + `SD_ID` bigint(20) NOT NULL, + `BUCKET_COL_NAME` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`SD_ID`,`INTEGER_IDX`), + KEY `BUCKETING_COLS_N49` (`SD_ID`), + CONSTRAINT `BUCKETING_COLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `COLUMNS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `COLUMNS` ( + `SD_ID` bigint(20) NOT NULL, + `COMMENT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `TYPE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`SD_ID`,`COLUMN_NAME`,`TYPE_NAME`), + KEY `COLUMNS_N49` (`SD_ID`), + CONSTRAINT `COLUMNS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITIONS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITIONS` ( + `PART_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `LAST_ACCESS_TIME` int(11) NOT NULL, + `PART_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `SD_ID` bigint(20) DEFAULT NULL, + `TBL_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`PART_ID`), + UNIQUE KEY `UNIQUEPARTITION` (`PART_NAME`,`TBL_ID`), + KEY `PARTITIONS_N49` (`SD_ID`), + KEY `PARTITIONS_N50` (`TBL_ID`), + CONSTRAINT `PARTITIONS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`), + CONSTRAINT `PARTITIONS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITION_KEYS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITION_KEYS` ( + `TBL_ID` bigint(20) NOT NULL, + `PKEY_COMMENT` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PKEY_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PKEY_TYPE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`TBL_ID`,`PKEY_NAME`), + KEY `PARTITION_KEYS_N49` (`TBL_ID`), + CONSTRAINT `PARTITION_KEYS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITION_KEY_VALS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITION_KEY_VALS` ( + `PART_ID` bigint(20) NOT NULL, + `PART_KEY_VAL` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`PART_ID`,`INTEGER_IDX`), + KEY `PARTITION_KEY_VALS_N49` (`PART_ID`), + CONSTRAINT `PARTITION_KEY_VALS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `partitions` (`PART_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITION_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITION_PARAMS` ( + `PART_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`PART_ID`,`PARAM_KEY`), + KEY `PARTITION_PARAMS_N49` (`PART_ID`), + CONSTRAINT `PARTITION_PARAMS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `partitions` (`PART_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SDS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SDS` ( + `SD_ID` bigint(20) NOT NULL, + `INPUT_FORMAT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `IS_COMPRESSED` bit(1) NOT NULL, + `LOCATION` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `NUM_BUCKETS` int(11) NOT NULL, + `OUTPUT_FORMAT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `SERDE_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`SD_ID`), + KEY `SDS_N49` (`SERDE_ID`), + CONSTRAINT `SDS_FK1` FOREIGN KEY (`SERDE_ID`) REFERENCES `serdes` (`SERDE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SD_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SD_PARAMS` ( + `SD_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`SD_ID`,`PARAM_KEY`), + KEY `SD_PARAMS_N49` (`SD_ID`), + CONSTRAINT `SD_PARAMS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SERDES` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SERDES` ( + `SERDE_ID` bigint(20) NOT NULL, + `NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `SLIB` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`SERDE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SERDE_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SERDE_PARAMS` ( + `SERDE_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`SERDE_ID`,`PARAM_KEY`), + KEY `SERDE_PARAMS_N49` (`SERDE_ID`), + CONSTRAINT `SERDE_PARAMS_FK1` FOREIGN KEY (`SERDE_ID`) REFERENCES `serdes` (`SERDE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SORT_COLS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SORT_COLS` ( + `SD_ID` bigint(20) NOT NULL, + `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `ORDER` int(11) NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`SD_ID`,`INTEGER_IDX`), + KEY `SORT_COLS_N49` (`SD_ID`), + CONSTRAINT `SORT_COLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `TABLE_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `TABLE_PARAMS` ( + `TBL_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`TBL_ID`,`PARAM_KEY`), + KEY `TABLE_PARAMS_N49` (`TBL_ID`), + CONSTRAINT `TABLE_PARAMS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `TBLS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `TBLS` ( + `TBL_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `DB_ID` bigint(20) DEFAULT NULL, + `LAST_ACCESS_TIME` int(11) NOT NULL, + `OWNER` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `RETENTION` int(11) NOT NULL, + `SD_ID` bigint(20) DEFAULT NULL, + `TBL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`TBL_ID`), + UNIQUE KEY `UNIQUETABLE` (`TBL_NAME`,`DB_ID`), + KEY `TBLS_N50` (`SD_ID`), + KEY `TBLS_N49` (`DB_ID`), + CONSTRAINT `TBLS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `dbs` (`DB_ID`), + CONSTRAINT `TBLS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `TYPE_FIELDS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `TYPE_FIELDS` ( + `TYPE_NAME` bigint(20) NOT NULL, + `COMMENT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `FIELD_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `FIELD_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`TYPE_NAME`,`FIELD_NAME`), + KEY `TYPE_FIELDS_N49` (`TYPE_NAME`), + CONSTRAINT `TYPE_FIELDS_FK1` FOREIGN KEY (`TYPE_NAME`) REFERENCES `types` (`TYPES_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `dbs` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `dbs` ( + `DB_ID` bigint(20) NOT NULL, + `DESC` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`DB_ID`), + UNIQUE KEY `UNIQUE_DATABASE` (`NAME`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `types` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `types` ( + `TYPES_ID` bigint(20) NOT NULL, + `TYPE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TYPE1` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TYPE2` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`TYPES_ID`), + UNIQUE KEY `UNIQUE_TYPE` (`TYPE_NAME`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +-- Dump completed on 2011-03-14 15:29:14 diff --git metastore/scripts/upgrade/mysql/hive-schema-0.4.0.mysql.sql metastore/scripts/upgrade/mysql/hive-schema-0.4.0.mysql.sql new file mode 100644 index 0000000..22decb3 --- /dev/null +++ metastore/scripts/upgrade/mysql/hive-schema-0.4.0.mysql.sql @@ -0,0 +1,305 @@ +-- MySQL dump 10.13 Distrib 5.1.48, for apple-darwin10.3.0 (i386) +-- +-- Host: localhost Database: mdb +-- ------------------------------------------------------ +-- Server version 5.1.48 + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +-- +-- Table structure for table `BUCKETING_COLS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `BUCKETING_COLS` ( + `SD_ID` bigint(20) NOT NULL, + `BUCKET_COL_NAME` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`SD_ID`,`INTEGER_IDX`), + KEY `BUCKETING_COLS_N49` (`SD_ID`), + CONSTRAINT `BUCKETING_COLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `COLUMNS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `COLUMNS` ( + `SD_ID` bigint(20) NOT NULL, + `COMMENT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `TYPE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`SD_ID`,`COLUMN_NAME`,`TYPE_NAME`), + KEY `COLUMNS_N49` (`SD_ID`), + CONSTRAINT `COLUMNS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITIONS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITIONS` ( + `PART_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `LAST_ACCESS_TIME` int(11) NOT NULL, + `PART_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `SD_ID` bigint(20) DEFAULT NULL, + `TBL_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`PART_ID`), + UNIQUE KEY `UNIQUEPARTITION` (`PART_NAME`,`TBL_ID`), + KEY `PARTITIONS_N49` (`SD_ID`), + KEY `PARTITIONS_N50` (`TBL_ID`), + CONSTRAINT `PARTITIONS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`), + CONSTRAINT `PARTITIONS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITION_KEYS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITION_KEYS` ( + `TBL_ID` bigint(20) NOT NULL, + `PKEY_COMMENT` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PKEY_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PKEY_TYPE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`TBL_ID`,`PKEY_NAME`), + KEY `PARTITION_KEYS_N49` (`TBL_ID`), + CONSTRAINT `PARTITION_KEYS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITION_KEY_VALS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITION_KEY_VALS` ( + `PART_ID` bigint(20) NOT NULL, + `PART_KEY_VAL` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`PART_ID`,`INTEGER_IDX`), + KEY `PARTITION_KEY_VALS_N49` (`PART_ID`), + CONSTRAINT `PARTITION_KEY_VALS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `partitions` (`PART_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITION_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITION_PARAMS` ( + `PART_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`PART_ID`,`PARAM_KEY`), + KEY `PARTITION_PARAMS_N49` (`PART_ID`), + CONSTRAINT `PARTITION_PARAMS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `partitions` (`PART_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SDS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SDS` ( + `SD_ID` bigint(20) NOT NULL, + `INPUT_FORMAT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `IS_COMPRESSED` bit(1) NOT NULL, + `LOCATION` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `NUM_BUCKETS` int(11) NOT NULL, + `OUTPUT_FORMAT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `SERDE_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`SD_ID`), + KEY `SDS_N49` (`SERDE_ID`), + CONSTRAINT `SDS_FK1` FOREIGN KEY (`SERDE_ID`) REFERENCES `serdes` (`SERDE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SD_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SD_PARAMS` ( + `SD_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`SD_ID`,`PARAM_KEY`), + KEY `SD_PARAMS_N49` (`SD_ID`), + CONSTRAINT `SD_PARAMS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SERDES` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SERDES` ( + `SERDE_ID` bigint(20) NOT NULL, + `NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `SLIB` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`SERDE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SERDE_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SERDE_PARAMS` ( + `SERDE_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`SERDE_ID`,`PARAM_KEY`), + KEY `SERDE_PARAMS_N49` (`SERDE_ID`), + CONSTRAINT `SERDE_PARAMS_FK1` FOREIGN KEY (`SERDE_ID`) REFERENCES `serdes` (`SERDE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SORT_COLS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SORT_COLS` ( + `SD_ID` bigint(20) NOT NULL, + `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `ORDER` int(11) NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`SD_ID`,`INTEGER_IDX`), + KEY `SORT_COLS_N49` (`SD_ID`), + CONSTRAINT `SORT_COLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `TABLE_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `TABLE_PARAMS` ( + `TBL_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`TBL_ID`,`PARAM_KEY`), + KEY `TABLE_PARAMS_N49` (`TBL_ID`), + CONSTRAINT `TABLE_PARAMS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `TBLS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `TBLS` ( + `TBL_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `DB_ID` bigint(20) DEFAULT NULL, + `LAST_ACCESS_TIME` int(11) NOT NULL, + `OWNER` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `RETENTION` int(11) NOT NULL, + `SD_ID` bigint(20) DEFAULT NULL, + `TBL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`TBL_ID`), + UNIQUE KEY `UNIQUETABLE` (`TBL_NAME`,`DB_ID`), + KEY `TBLS_N50` (`SD_ID`), + KEY `TBLS_N49` (`DB_ID`), + CONSTRAINT `TBLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_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 */; + +-- +-- Table structure for table `TYPE_FIELDS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `TYPE_FIELDS` ( + `TYPE_NAME` bigint(20) NOT NULL, + `COMMENT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `FIELD_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `FIELD_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`TYPE_NAME`,`FIELD_NAME`), + KEY `TYPE_FIELDS_N49` (`TYPE_NAME`), + CONSTRAINT `TYPE_FIELDS_FK1` FOREIGN KEY (`TYPE_NAME`) REFERENCES `types` (`TYPES_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `dbs` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `dbs` ( + `DB_ID` bigint(20) NOT NULL, + `DESC` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`DB_ID`), + UNIQUE KEY `UNIQUE_DATABASE` (`NAME`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `types` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `types` ( + `TYPES_ID` bigint(20) NOT NULL, + `TYPE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TYPE1` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TYPE2` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`TYPES_ID`), + UNIQUE KEY `UNIQUE_TYPE` (`TYPE_NAME`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +-- Dump completed on 2011-03-14 15:29:31 diff --git metastore/scripts/upgrade/mysql/hive-schema-0.4.1.mysql.sql metastore/scripts/upgrade/mysql/hive-schema-0.4.1.mysql.sql new file mode 100644 index 0000000..c8cf218 --- /dev/null +++ metastore/scripts/upgrade/mysql/hive-schema-0.4.1.mysql.sql @@ -0,0 +1,305 @@ +-- MySQL dump 10.13 Distrib 5.1.48, for apple-darwin10.3.0 (i386) +-- +-- Host: localhost Database: mdb +-- ------------------------------------------------------ +-- Server version 5.1.48 + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +-- +-- Table structure for table `BUCKETING_COLS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `BUCKETING_COLS` ( + `SD_ID` bigint(20) NOT NULL, + `BUCKET_COL_NAME` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`SD_ID`,`INTEGER_IDX`), + KEY `BUCKETING_COLS_N49` (`SD_ID`), + CONSTRAINT `BUCKETING_COLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `COLUMNS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `COLUMNS` ( + `SD_ID` bigint(20) NOT NULL, + `COMMENT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `TYPE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`SD_ID`,`COLUMN_NAME`,`TYPE_NAME`), + KEY `COLUMNS_N49` (`SD_ID`), + CONSTRAINT `COLUMNS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITIONS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITIONS` ( + `PART_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `LAST_ACCESS_TIME` int(11) NOT NULL, + `PART_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `SD_ID` bigint(20) DEFAULT NULL, + `TBL_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`), + CONSTRAINT `PARTITIONS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`), + CONSTRAINT `PARTITIONS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITION_KEYS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITION_KEYS` ( + `TBL_ID` bigint(20) NOT NULL, + `PKEY_COMMENT` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PKEY_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PKEY_TYPE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`TBL_ID`,`PKEY_NAME`), + KEY `PARTITION_KEYS_N49` (`TBL_ID`), + CONSTRAINT `PARTITION_KEYS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITION_KEY_VALS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITION_KEY_VALS` ( + `PART_ID` bigint(20) NOT NULL, + `PART_KEY_VAL` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`PART_ID`,`INTEGER_IDX`), + KEY `PARTITION_KEY_VALS_N49` (`PART_ID`), + CONSTRAINT `PARTITION_KEY_VALS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `partitions` (`PART_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITION_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITION_PARAMS` ( + `PART_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`PART_ID`,`PARAM_KEY`), + KEY `PARTITION_PARAMS_N49` (`PART_ID`), + CONSTRAINT `PARTITION_PARAMS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `partitions` (`PART_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SDS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SDS` ( + `SD_ID` bigint(20) NOT NULL, + `INPUT_FORMAT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `IS_COMPRESSED` bit(1) NOT NULL, + `LOCATION` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `NUM_BUCKETS` int(11) NOT NULL, + `OUTPUT_FORMAT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `SERDE_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`SD_ID`), + KEY `SDS_N49` (`SERDE_ID`), + CONSTRAINT `SDS_FK1` FOREIGN KEY (`SERDE_ID`) REFERENCES `serdes` (`SERDE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SD_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SD_PARAMS` ( + `SD_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`SD_ID`,`PARAM_KEY`), + KEY `SD_PARAMS_N49` (`SD_ID`), + CONSTRAINT `SD_PARAMS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SERDES` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SERDES` ( + `SERDE_ID` bigint(20) NOT NULL, + `NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `SLIB` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`SERDE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SERDE_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SERDE_PARAMS` ( + `SERDE_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`SERDE_ID`,`PARAM_KEY`), + KEY `SERDE_PARAMS_N49` (`SERDE_ID`), + CONSTRAINT `SERDE_PARAMS_FK1` FOREIGN KEY (`SERDE_ID`) REFERENCES `serdes` (`SERDE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SORT_COLS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SORT_COLS` ( + `SD_ID` bigint(20) NOT NULL, + `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `ORDER` int(11) NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`SD_ID`,`INTEGER_IDX`), + KEY `SORT_COLS_N49` (`SD_ID`), + CONSTRAINT `SORT_COLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `TABLE_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `TABLE_PARAMS` ( + `TBL_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`TBL_ID`,`PARAM_KEY`), + KEY `TABLE_PARAMS_N49` (`TBL_ID`), + CONSTRAINT `TABLE_PARAMS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `TBLS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `TBLS` ( + `TBL_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `DB_ID` bigint(20) DEFAULT NULL, + `LAST_ACCESS_TIME` int(11) NOT NULL, + `OWNER` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `RETENTION` int(11) NOT NULL, + `SD_ID` bigint(20) DEFAULT NULL, + `TBL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`TBL_ID`), + UNIQUE KEY `UNIQUETABLE` (`TBL_NAME`,`DB_ID`), + KEY `TBLS_N50` (`SD_ID`), + KEY `TBLS_N49` (`DB_ID`), + CONSTRAINT `TBLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_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 */; + +-- +-- Table structure for table `TYPE_FIELDS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `TYPE_FIELDS` ( + `TYPE_NAME` bigint(20) NOT NULL, + `COMMENT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `FIELD_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `FIELD_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`TYPE_NAME`,`FIELD_NAME`), + KEY `TYPE_FIELDS_N49` (`TYPE_NAME`), + CONSTRAINT `TYPE_FIELDS_FK1` FOREIGN KEY (`TYPE_NAME`) REFERENCES `types` (`TYPES_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `dbs` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `dbs` ( + `DB_ID` bigint(20) NOT NULL, + `DESC` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`DB_ID`), + UNIQUE KEY `UNIQUE_DATABASE` (`NAME`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `types` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `types` ( + `TYPES_ID` bigint(20) NOT NULL, + `TYPE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TYPE1` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TYPE2` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`TYPES_ID`), + UNIQUE KEY `UNIQUE_TYPE` (`TYPE_NAME`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +-- Dump completed on 2011-03-14 15:29:49 diff --git metastore/scripts/upgrade/mysql/hive-schema-0.5.0.mysql.sql metastore/scripts/upgrade/mysql/hive-schema-0.5.0.mysql.sql new file mode 100644 index 0000000..712deb9 --- /dev/null +++ metastore/scripts/upgrade/mysql/hive-schema-0.5.0.mysql.sql @@ -0,0 +1,305 @@ +-- MySQL dump 10.13 Distrib 5.1.48, for apple-darwin10.3.0 (i386) +-- +-- Host: localhost Database: mdb +-- ------------------------------------------------------ +-- Server version 5.1.48 + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +-- +-- Table structure for table `BUCKETING_COLS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `BUCKETING_COLS` ( + `SD_ID` bigint(20) NOT NULL, + `BUCKET_COL_NAME` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`SD_ID`,`INTEGER_IDX`), + KEY `BUCKETING_COLS_N49` (`SD_ID`), + CONSTRAINT `BUCKETING_COLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `COLUMNS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `COLUMNS` ( + `SD_ID` bigint(20) NOT NULL, + `COMMENT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `TYPE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`SD_ID`,`COLUMN_NAME`,`TYPE_NAME`), + KEY `COLUMNS_N49` (`SD_ID`), + CONSTRAINT `COLUMNS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITIONS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITIONS` ( + `PART_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `LAST_ACCESS_TIME` int(11) NOT NULL, + `PART_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `SD_ID` bigint(20) DEFAULT NULL, + `TBL_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`PART_ID`), + UNIQUE KEY `UNIQUEPARTITION` (`PART_NAME`,`TBL_ID`), + KEY `PARTITIONS_N49` (`SD_ID`), + KEY `PARTITIONS_N50` (`TBL_ID`), + CONSTRAINT `PARTITIONS_FK2` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TBL_ID`), + CONSTRAINT `PARTITIONS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITION_KEYS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITION_KEYS` ( + `TBL_ID` bigint(20) NOT NULL, + `PKEY_COMMENT` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PKEY_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PKEY_TYPE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`TBL_ID`,`PKEY_NAME`), + KEY `PARTITION_KEYS_N49` (`TBL_ID`), + CONSTRAINT `PARTITION_KEYS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITION_KEY_VALS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITION_KEY_VALS` ( + `PART_ID` bigint(20) NOT NULL, + `PART_KEY_VAL` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`PART_ID`,`INTEGER_IDX`), + KEY `PARTITION_KEY_VALS_N49` (`PART_ID`), + CONSTRAINT `PARTITION_KEY_VALS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `partitions` (`PART_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITION_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITION_PARAMS` ( + `PART_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`PART_ID`,`PARAM_KEY`), + KEY `PARTITION_PARAMS_N49` (`PART_ID`), + CONSTRAINT `PARTITION_PARAMS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `partitions` (`PART_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SDS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SDS` ( + `SD_ID` bigint(20) NOT NULL, + `INPUT_FORMAT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `IS_COMPRESSED` bit(1) NOT NULL, + `LOCATION` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `NUM_BUCKETS` int(11) NOT NULL, + `OUTPUT_FORMAT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `SERDE_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`SD_ID`), + KEY `SDS_N49` (`SERDE_ID`), + CONSTRAINT `SDS_FK1` FOREIGN KEY (`SERDE_ID`) REFERENCES `serdes` (`SERDE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SD_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SD_PARAMS` ( + `SD_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`SD_ID`,`PARAM_KEY`), + KEY `SD_PARAMS_N49` (`SD_ID`), + CONSTRAINT `SD_PARAMS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SERDES` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SERDES` ( + `SERDE_ID` bigint(20) NOT NULL, + `NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `SLIB` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`SERDE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SERDE_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SERDE_PARAMS` ( + `SERDE_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`SERDE_ID`,`PARAM_KEY`), + KEY `SERDE_PARAMS_N49` (`SERDE_ID`), + CONSTRAINT `SERDE_PARAMS_FK1` FOREIGN KEY (`SERDE_ID`) REFERENCES `serdes` (`SERDE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SORT_COLS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SORT_COLS` ( + `SD_ID` bigint(20) NOT NULL, + `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `ORDER` int(11) NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`SD_ID`,`INTEGER_IDX`), + KEY `SORT_COLS_N49` (`SD_ID`), + CONSTRAINT `SORT_COLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `TABLE_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `TABLE_PARAMS` ( + `TBL_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`TBL_ID`,`PARAM_KEY`), + KEY `TABLE_PARAMS_N49` (`TBL_ID`), + CONSTRAINT `TABLE_PARAMS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `TBLS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `TBLS` ( + `TBL_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `DB_ID` bigint(20) DEFAULT NULL, + `LAST_ACCESS_TIME` int(11) NOT NULL, + `OWNER` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `RETENTION` int(11) NOT NULL, + `SD_ID` bigint(20) DEFAULT NULL, + `TBL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`TBL_ID`), + UNIQUE KEY `UNIQUETABLE` (`TBL_NAME`,`DB_ID`), + KEY `TBLS_N50` (`SD_ID`), + KEY `TBLS_N49` (`DB_ID`), + CONSTRAINT `TBLS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `dbs` (`DB_ID`), + CONSTRAINT `TBLS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `TYPE_FIELDS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `TYPE_FIELDS` ( + `TYPE_NAME` bigint(20) NOT NULL, + `COMMENT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `FIELD_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `FIELD_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`TYPE_NAME`,`FIELD_NAME`), + KEY `TYPE_FIELDS_N49` (`TYPE_NAME`), + CONSTRAINT `TYPE_FIELDS_FK1` FOREIGN KEY (`TYPE_NAME`) REFERENCES `types` (`TYPES_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `dbs` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `dbs` ( + `DB_ID` bigint(20) NOT NULL, + `DESC` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`DB_ID`), + UNIQUE KEY `UNIQUE_DATABASE` (`NAME`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `types` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `types` ( + `TYPES_ID` bigint(20) NOT NULL, + `TYPE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TYPE1` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TYPE2` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`TYPES_ID`), + UNIQUE KEY `UNIQUE_TYPE` (`TYPE_NAME`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +-- Dump completed on 2011-03-14 15:30:07 diff --git metastore/scripts/upgrade/mysql/hive-schema-0.6.0.mysql.sql metastore/scripts/upgrade/mysql/hive-schema-0.6.0.mysql.sql new file mode 100644 index 0000000..377e345 --- /dev/null +++ metastore/scripts/upgrade/mysql/hive-schema-0.6.0.mysql.sql @@ -0,0 +1,309 @@ +-- MySQL dump 10.13 Distrib 5.1.48, for apple-darwin10.3.0 (i386) +-- +-- Host: localhost Database: mdb +-- ------------------------------------------------------ +-- Server version 5.1.48 + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +-- +-- Table structure for table `BUCKETING_COLS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `BUCKETING_COLS` ( + `SD_ID` bigint(20) NOT NULL, + `BUCKET_COL_NAME` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`SD_ID`,`INTEGER_IDX`), + KEY `BUCKETING_COLS_N49` (`SD_ID`), + CONSTRAINT `BUCKETING_COLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `COLUMNS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `COLUMNS` ( + `SD_ID` bigint(20) NOT NULL, + `COMMENT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `TYPE_NAME` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`SD_ID`,`COLUMN_NAME`), + KEY `COLUMNS_N49` (`SD_ID`), + CONSTRAINT `COLUMNS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITIONS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITIONS` ( + `PART_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `LAST_ACCESS_TIME` int(11) NOT NULL, + `PART_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `SD_ID` bigint(20) DEFAULT NULL, + `TBL_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`PART_ID`), + UNIQUE KEY `UNIQUEPARTITION` (`PART_NAME`,`TBL_ID`), + KEY `PARTITIONS_N49` (`SD_ID`), + KEY `PARTITIONS_N50` (`TBL_ID`), + CONSTRAINT `PARTITIONS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`), + CONSTRAINT `PARTITIONS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITION_KEYS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITION_KEYS` ( + `TBL_ID` bigint(20) NOT NULL, + `PKEY_COMMENT` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PKEY_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PKEY_TYPE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`TBL_ID`,`PKEY_NAME`), + KEY `PARTITION_KEYS_N49` (`TBL_ID`), + CONSTRAINT `PARTITION_KEYS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITION_KEY_VALS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITION_KEY_VALS` ( + `PART_ID` bigint(20) NOT NULL, + `PART_KEY_VAL` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`PART_ID`,`INTEGER_IDX`), + KEY `PARTITION_KEY_VALS_N49` (`PART_ID`), + CONSTRAINT `PARTITION_KEY_VALS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `partitions` (`PART_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITION_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITION_PARAMS` ( + `PART_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`PART_ID`,`PARAM_KEY`), + KEY `PARTITION_PARAMS_N49` (`PART_ID`), + CONSTRAINT `PARTITION_PARAMS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `partitions` (`PART_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SDS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SDS` ( + `SD_ID` bigint(20) NOT NULL, + `INPUT_FORMAT` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `IS_COMPRESSED` bit(1) NOT NULL, + `LOCATION` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `NUM_BUCKETS` int(11) NOT NULL, + `OUTPUT_FORMAT` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `SERDE_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`SD_ID`), + KEY `SDS_N49` (`SERDE_ID`), + CONSTRAINT `SDS_FK1` FOREIGN KEY (`SERDE_ID`) REFERENCES `serdes` (`SERDE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SD_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SD_PARAMS` ( + `SD_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`SD_ID`,`PARAM_KEY`), + KEY `SD_PARAMS_N49` (`SD_ID`), + CONSTRAINT `SD_PARAMS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SERDES` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SERDES` ( + `SERDE_ID` bigint(20) NOT NULL, + `NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `SLIB` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`SERDE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SERDE_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SERDE_PARAMS` ( + `SERDE_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`SERDE_ID`,`PARAM_KEY`), + KEY `SERDE_PARAMS_N49` (`SERDE_ID`), + CONSTRAINT `SERDE_PARAMS_FK1` FOREIGN KEY (`SERDE_ID`) REFERENCES `serdes` (`SERDE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SORT_COLS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SORT_COLS` ( + `SD_ID` bigint(20) NOT NULL, + `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `ORDER` int(11) NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`SD_ID`,`INTEGER_IDX`), + KEY `SORT_COLS_N49` (`SD_ID`), + CONSTRAINT `SORT_COLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `TABLE_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `TABLE_PARAMS` ( + `TBL_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`TBL_ID`,`PARAM_KEY`), + KEY `TABLE_PARAMS_N49` (`TBL_ID`), + CONSTRAINT `TABLE_PARAMS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `TBLS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `TBLS` ( + `TBL_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `DB_ID` bigint(20) DEFAULT NULL, + `LAST_ACCESS_TIME` int(11) NOT NULL, + `OWNER` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `RETENTION` int(11) NOT NULL, + `SD_ID` bigint(20) DEFAULT NULL, + `TBL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TBL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `VIEW_EXPANDED_TEXT` mediumtext, + `VIEW_ORIGINAL_TEXT` mediumtext, + PRIMARY KEY (`TBL_ID`), + UNIQUE KEY `UNIQUETABLE` (`TBL_NAME`,`DB_ID`), + KEY `TBLS_N50` (`DB_ID`), + KEY `TBLS_N49` (`SD_ID`), + CONSTRAINT `TBLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_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 */; + +-- +-- Table structure for table `TYPE_FIELDS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `TYPE_FIELDS` ( + `TYPE_NAME` bigint(20) NOT NULL, + `COMMENT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `FIELD_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `FIELD_TYPE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`TYPE_NAME`,`FIELD_NAME`), + KEY `TYPE_FIELDS_N49` (`TYPE_NAME`), + CONSTRAINT `TYPE_FIELDS_FK1` FOREIGN KEY (`TYPE_NAME`) REFERENCES `types` (`TYPES_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `dbs` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `dbs` ( + `DB_ID` bigint(20) NOT NULL, + `DESC` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `DB_LOCATION_URI` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`DB_ID`), + UNIQUE KEY `UNIQUE_DATABASE` (`NAME`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `types` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `types` ( + `TYPES_ID` bigint(20) NOT NULL, + `TYPE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TYPE1` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TYPE2` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`TYPES_ID`), + UNIQUE KEY `UNIQUE_TYPE` (`TYPE_NAME`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +-- Dump completed on 2011-03-14 15:30:25 diff --git metastore/scripts/upgrade/mysql/hive-schema-0.7.0.mysql.sql metastore/scripts/upgrade/mysql/hive-schema-0.7.0.mysql.sql new file mode 100644 index 0000000..894f740 --- /dev/null +++ metastore/scripts/upgrade/mysql/hive-schema-0.7.0.mysql.sql @@ -0,0 +1,543 @@ +-- MySQL dump 10.13 Distrib 5.1.48, for apple-darwin10.3.0 (i386) +-- +-- Host: localhost Database: mdb +-- ------------------------------------------------------ +-- Server version 5.1.48 + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +-- +-- Table structure for table `BUCKETING_COLS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `BUCKETING_COLS` ( + `SD_ID` bigint(20) NOT NULL, + `BUCKET_COL_NAME` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`SD_ID`,`INTEGER_IDX`), + KEY `BUCKETING_COLS_N49` (`SD_ID`), + CONSTRAINT `BUCKETING_COLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `COLUMNS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `COLUMNS` ( + `SD_ID` bigint(20) NOT NULL, + `COMMENT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `TYPE_NAME` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`SD_ID`,`COLUMN_NAME`), + KEY `COLUMNS_N49` (`SD_ID`), + CONSTRAINT `COLUMNS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `DATABASE_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `DATABASE_PARAMS` ( + `DB_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(180) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`DB_ID`,`PARAM_KEY`), + KEY `DATABASE_PARAMS_N49` (`DB_ID`), + CONSTRAINT `DATABASE_PARAMS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `dbs` (`DB_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `DB_PRIVS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `DB_PRIVS` ( + `DB_GRANT_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `DB_ID` bigint(20) DEFAULT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `DB_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`DB_GRANT_ID`), + UNIQUE KEY `DBPRIVILEGEINDEX` (`DB_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`DB_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), + KEY `DB_PRIVS_N49` (`DB_ID`), + CONSTRAINT `DB_PRIVS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `dbs` (`DB_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `IDXS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `IDXS` ( + `INDEX_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `DEFERRED_REBUILD` bit(1) NOT NULL, + `INDEX_HANDLER_CLASS` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `INDEX_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `INDEX_TBL_ID` bigint(20) DEFAULT NULL, + `LAST_ACCESS_TIME` int(11) NOT NULL, + `ORIG_TBL_ID` bigint(20) DEFAULT NULL, + `SD_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`INDEX_ID`), + UNIQUE KEY `UNIQUEINDEX` (`INDEX_NAME`,`ORIG_TBL_ID`), + KEY `IDXS_N51` (`SD_ID`), + KEY `IDXS_N50` (`INDEX_TBL_ID`), + KEY `IDXS_N49` (`ORIG_TBL_ID`), + CONSTRAINT `IDXS_FK3` FOREIGN KEY (`INDEX_TBL_ID`) REFERENCES `tbls` (`TBL_ID`), + CONSTRAINT `IDXS_FK1` FOREIGN KEY (`ORIG_TBL_ID`) REFERENCES `tbls` (`TBL_ID`), + CONSTRAINT `IDXS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `INDEX_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `INDEX_PARAMS` ( + `INDEX_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`INDEX_ID`,`PARAM_KEY`), + KEY `INDEX_PARAMS_N49` (`INDEX_ID`), + CONSTRAINT `INDEX_PARAMS_FK1` FOREIGN KEY (`INDEX_ID`) REFERENCES `idxs` (`INDEX_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITIONS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITIONS` ( + `PART_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `LAST_ACCESS_TIME` int(11) NOT NULL, + `PART_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `SD_ID` bigint(20) DEFAULT NULL, + `TBL_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`), + CONSTRAINT `PARTITIONS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`), + CONSTRAINT `PARTITIONS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITION_KEYS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITION_KEYS` ( + `TBL_ID` bigint(20) NOT NULL, + `PKEY_COMMENT` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PKEY_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PKEY_TYPE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`TBL_ID`,`PKEY_NAME`), + KEY `PARTITION_KEYS_N49` (`TBL_ID`), + CONSTRAINT `PARTITION_KEYS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITION_KEY_VALS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITION_KEY_VALS` ( + `PART_ID` bigint(20) NOT NULL, + `PART_KEY_VAL` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`PART_ID`,`INTEGER_IDX`), + KEY `PARTITION_KEY_VALS_N49` (`PART_ID`), + CONSTRAINT `PARTITION_KEY_VALS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `partitions` (`PART_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PARTITION_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PARTITION_PARAMS` ( + `PART_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`PART_ID`,`PARAM_KEY`), + KEY `PARTITION_PARAMS_N49` (`PART_ID`), + CONSTRAINT `PARTITION_PARAMS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `partitions` (`PART_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PART_COL_PRIVS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PART_COL_PRIVS` ( + `PART_COLUMN_GRANT_ID` bigint(20) NOT NULL, + `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `CREATE_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PART_ID` bigint(20) DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PART_COL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`PART_COLUMN_GRANT_ID`), + KEY `PART_COL_PRIVS_N49` (`PART_ID`), + KEY `PARTITIONCOLUMNPRIVILEGEINDEX` (`PART_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), + CONSTRAINT `PART_COL_PRIVS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `partitions` (`PART_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `PART_PRIVS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `PART_PRIVS` ( + `PART_GRANT_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PART_ID` bigint(20) DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PART_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`PART_GRANT_ID`), + KEY `PARTPRIVILEGEINDEX` (`PART_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), + KEY `PART_PRIVS_N49` (`PART_ID`), + CONSTRAINT `PART_PRIVS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `partitions` (`PART_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `ROLE_MAP` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `ROLE_MAP` ( + `ROLE_GRANT_ID` bigint(20) NOT NULL, + `ADD_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `ROLE_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`ROLE_GRANT_ID`), + UNIQUE KEY `USERROLEMAPINDEX` (`PRINCIPAL_NAME`,`ROLE_ID`,`GRANTOR`,`GRANTOR_TYPE`), + KEY `ROLE_MAP_N49` (`ROLE_ID`), + CONSTRAINT `ROLE_MAP_FK1` FOREIGN KEY (`ROLE_ID`) REFERENCES `roles` (`ROLE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SDS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SDS` ( + `SD_ID` bigint(20) NOT NULL, + `INPUT_FORMAT` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `IS_COMPRESSED` bit(1) NOT NULL, + `LOCATION` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `NUM_BUCKETS` int(11) NOT NULL, + `OUTPUT_FORMAT` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `SERDE_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`SD_ID`), + KEY `SDS_N49` (`SERDE_ID`), + CONSTRAINT `SDS_FK1` FOREIGN KEY (`SERDE_ID`) REFERENCES `serdes` (`SERDE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SD_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SD_PARAMS` ( + `SD_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`SD_ID`,`PARAM_KEY`), + KEY `SD_PARAMS_N49` (`SD_ID`), + CONSTRAINT `SD_PARAMS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SERDES` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SERDES` ( + `SERDE_ID` bigint(20) NOT NULL, + `NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `SLIB` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`SERDE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SERDE_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SERDE_PARAMS` ( + `SERDE_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`SERDE_ID`,`PARAM_KEY`), + KEY `SERDE_PARAMS_N49` (`SERDE_ID`), + CONSTRAINT `SERDE_PARAMS_FK1` FOREIGN KEY (`SERDE_ID`) REFERENCES `serdes` (`SERDE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `SORT_COLS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `SORT_COLS` ( + `SD_ID` bigint(20) NOT NULL, + `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `ORDER` int(11) NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`SD_ID`,`INTEGER_IDX`), + KEY `SORT_COLS_N49` (`SD_ID`), + CONSTRAINT `SORT_COLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `TABLE_PARAMS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `TABLE_PARAMS` ( + `TBL_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`TBL_ID`,`PARAM_KEY`), + KEY `TABLE_PARAMS_N49` (`TBL_ID`), + CONSTRAINT `TABLE_PARAMS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `TBLS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `TBLS` ( + `TBL_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `DB_ID` bigint(20) DEFAULT NULL, + `LAST_ACCESS_TIME` int(11) NOT NULL, + `OWNER` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `RETENTION` int(11) NOT NULL, + `SD_ID` bigint(20) DEFAULT NULL, + `TBL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TBL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `VIEW_EXPANDED_TEXT` mediumtext, + `VIEW_ORIGINAL_TEXT` mediumtext, + PRIMARY KEY (`TBL_ID`), + UNIQUE KEY `UNIQUETABLE` (`TBL_NAME`,`DB_ID`), + KEY `TBLS_N50` (`SD_ID`), + KEY `TBLS_N49` (`DB_ID`), + CONSTRAINT `TBLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `sds` (`SD_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 */; + +-- +-- Table structure for table `TBL_COL_PRIVS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `TBL_COL_PRIVS` ( + `TBL_COLUMN_GRANT_ID` bigint(20) NOT NULL, + `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `CREATE_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TBL_COL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TBL_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`TBL_COLUMN_GRANT_ID`), + KEY `TABLECOLUMNPRIVILEGEINDEX` (`TBL_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`TBL_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), + KEY `TBL_COL_PRIVS_N49` (`TBL_ID`), + CONSTRAINT `TBL_COL_PRIVS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `TBL_PRIVS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `TBL_PRIVS` ( + `TBL_GRANT_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TBL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TBL_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`TBL_GRANT_ID`), + KEY `TBL_PRIVS_N49` (`TBL_ID`), + KEY `TABLEPRIVILEGEINDEX` (`TBL_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`TBL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), + CONSTRAINT `TBL_PRIVS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `TYPE_FIELDS` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `TYPE_FIELDS` ( + `TYPE_NAME` bigint(20) NOT NULL, + `COMMENT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `FIELD_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `FIELD_TYPE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `INTEGER_IDX` int(11) NOT NULL, + PRIMARY KEY (`TYPE_NAME`,`FIELD_NAME`), + KEY `TYPE_FIELDS_N49` (`TYPE_NAME`), + CONSTRAINT `TYPE_FIELDS_FK1` FOREIGN KEY (`TYPE_NAME`) REFERENCES `types` (`TYPES_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `dbs` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `dbs` ( + `DB_ID` bigint(20) NOT NULL, + `DESC` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `DB_LOCATION_URI` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`DB_ID`), + UNIQUE KEY `UNIQUE_DATABASE` (`NAME`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `global_privs` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `global_privs` ( + `USER_GRANT_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `USER_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`USER_GRANT_ID`), + UNIQUE KEY `GLOBALPRIVILEGEINDEX` (`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`USER_PRIV`,`GRANTOR`,`GRANTOR_TYPE`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `roles` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `roles` ( + `ROLE_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `OWNER_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `ROLE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`ROLE_ID`), + UNIQUE KEY `ROLEENTITYINDEX` (`ROLE_NAME`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `types` +-- + +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `types` ( + `TYPES_ID` bigint(20) NOT NULL, + `TYPE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TYPE1` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TYPE2` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`TYPES_ID`), + UNIQUE KEY `UNIQUE_TYPE` (`TYPE_NAME`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +-- Dump completed on 2011-03-14 15:30:49 diff --git metastore/scripts/upgrade/mysql/upgrade-0.5.0-to-0.6.0.mysql.sql metastore/scripts/upgrade/mysql/upgrade-0.5.0-to-0.6.0.mysql.sql new file mode 100644 index 0000000..20341b7 --- /dev/null +++ metastore/scripts/upgrade/mysql/upgrade-0.5.0-to-0.6.0.mysql.sql @@ -0,0 +1,6 @@ +SELECT '< Upgrading MetaStore schema from 0.5.0 to 0.6.0 >' AS ' '; +SOURCE 001-HIVE-972.mysql.sql; +SOURCE 002-HIVE-1068.mysql.sql; +SOURCE 003-HIVE-675.mysql.sql; +SOURCE 004-HIVE-1364.mysql.sql; +SELECT '< Finished upgrading MetaStore schema from 0.5.0 to 0.6.0 >' AS ' '; diff --git metastore/scripts/upgrade/mysql/upgrade-0.6.0-to-0.7.0.mysql.sql metastore/scripts/upgrade/mysql/upgrade-0.6.0-to-0.7.0.mysql.sql new file mode 100644 index 0000000..0ea656e --- /dev/null +++ metastore/scripts/upgrade/mysql/upgrade-0.6.0-to-0.7.0.mysql.sql @@ -0,0 +1,5 @@ +SELECT 'Upgrading MetaStore schema from 0.6.0 to 0.7.0' AS ' '; +SOURCE 005-HIVE-417.mysql.sql; +SOURCE 006-HIVE-1823.mysql.sql; +SOURCE 007-HIVE-78.mysql.sql; +SELECT 'Finished upgrading MetaStore schema from 0.6.0 to 0.7.0' AS ' '; diff --git metastore/scripts/upgrade/mysql/upgrade-0.6.0.mysql.sql metastore/scripts/upgrade/mysql/upgrade-0.6.0.mysql.sql deleted file mode 100644 index 8cbd501..0000000 --- metastore/scripts/upgrade/mysql/upgrade-0.6.0.mysql.sql +++ /dev/null @@ -1,27 +0,0 @@ --- HIVE-972: Support views -ALTER TABLE `TBLS` ADD `VIEW_EXPANDED_TEXT` mediumtext; -ALTER TABLE `TBLS` ADD `VIEW_ORIGINAL_TEXT` mediumtext; - --- HIVE-1068: CREATE VIEW followup: add a 'table type' enum --- attribute in metastore -ALTER TABLE `TBLS` ADD `TBL_TYPE` VARCHAR(128); - --- HIVE-675: Add database/schema support for Hive QL -ALTER TABLE `DBS` MODIFY `DESC` VARCHAR(4000); -ALTER TABLE `DBS` ADD `DB_LOCATION_URI` VARCHAR(4000) NOT NULL DEFAULT ''; - --- HIVE-1364: Increase the maximum length of various metastore fields, --- and remove TYPE_NAME from COLUMNS primary key -ALTER TABLE `TBLS` MODIFY `OWNER` VARCHAR(767); -ALTER TABLE `COLUMNS` MODIFY `TYPE_NAME` VARCHAR(4000); -ALTER TABLE `PARTITION_KEYS` MODIFY `PKEY_COMMENT` VARCHAR(4000); -ALTER TABLE `SD_PARAMS` MODIFY `PARAM_VALUE` VARCHAR(4000); -ALTER TABLE `SDS` MODIFY `INPUT_FORMAT` VARCHAR(4000); -ALTER TABLE `SDS` MODIFY `LOCATION` VARCHAR(4000); -ALTER TABLE `SDS` MODIFY `OUTPUT_FORMAT` VARCHAR(4000); -ALTER TABLE `SERDE_PARAMS` MODIFY `PARAM_VALUE` VARCHAR(4000); -ALTER TABLE `SERDES` MODIFY `SLIB` VARCHAR(4000); -ALTER TABLE `TABLE_PARAMS` MODIFY `PARAM_VALUE` VARCHAR(4000); -ALTER TABLE `COLUMNS` DROP PRIMARY KEY; -ALTER TABLE `COLUMNS` ADD PRIMARY KEY (`SD_ID`, `COLUMN_NAME`); -ALTER TABLE `PARTITION_PARAMS` MODIFY `PARAM_VALUE` VARCHAR(4000); diff --git metastore/scripts/upgrade/mysql/upgrade-0.7.0.mysql.sql metastore/scripts/upgrade/mysql/upgrade-0.7.0.mysql.sql deleted file mode 100644 index aab664e..0000000 --- metastore/scripts/upgrade/mysql/upgrade-0.7.0.mysql.sql +++ /dev/null @@ -1,160 +0,0 @@ --- --- HIVE-417 Implement Indexing in Hive --- -CREATE TABLE IF NOT EXISTS `IDXS` ( - `INDEX_ID` bigint(20) NOT NULL, - `CREATE_TIME` int(11) NOT NULL, - `DEFERRED_REBUILD` bit(1) NOT NULL, - `INDEX_HANDLER_CLASS` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `INDEX_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `INDEX_TBL_ID` bigint(20) DEFAULT NULL, - `LAST_ACCESS_TIME` int(11) NOT NULL, - `ORIG_TBL_ID` bigint(20) DEFAULT NULL, - `SD_ID` bigint(20) DEFAULT NULL, - PRIMARY KEY (`INDEX_ID`), - UNIQUE KEY `UNIQUEINDEX` (`INDEX_NAME`,`ORIG_TBL_ID`), - KEY `IDXS_FK1` (`SD_ID`), - KEY `IDXS_FK2` (`INDEX_TBL_ID`), - KEY `IDXS_FK3` (`ORIG_TBL_ID`), - CONSTRAINT `IDXS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`), - CONSTRAINT `IDXS_FK2` FOREIGN KEY (`INDEX_TBL_ID`) REFERENCES `TBLS` (`TBL_ID`), - CONSTRAINT `IDXS_FK3` FOREIGN KEY (`ORIG_TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -CREATE TABLE IF NOT EXISTS `INDEX_PARAMS` ( - `INDEX_ID` bigint(20) NOT NULL, - `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - PRIMARY KEY (`INDEX_ID`,`PARAM_KEY`), - CONSTRAINT `INDEX_PARAMS_FK1` FOREIGN KEY (`INDEX_ID`) REFERENCES `IDXS` (`INDEX_ID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - - --- --- HIVE-1823 Upgrade the database thrift interface to allow parameters key-value pairs --- -CREATE TABLE IF NOT EXISTS `DATABASE_PARAMS` ( - `DB_ID` bigint(20) NOT NULL, - `PARAM_KEY` varchar(180) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - PRIMARY KEY (`DB_ID`,`PARAM_KEY`), - CONSTRAINT `DATABASE_PARAMS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -ALTER TABLE `DBS` DROP COLUMN `PARAMETERS`; - --- --- HIVE-78 Authorization model for Hive --- -CREATE TABLE IF NOT EXISTS `DB_PRIVS` ( - `DB_GRANT_ID` bigint(20) NOT NULL, - `CREATE_TIME` int(11) NOT NULL, - `DB_ID` bigint(20) DEFAULT NULL, - `GRANT_OPTION` smallint(6) NOT NULL, - `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `DB_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - PRIMARY KEY (`DB_GRANT_ID`), - UNIQUE KEY `DBPRIVILEGEINDEX` (`DB_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`DB_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), - CONSTRAINT `DB_PRIVS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -CREATE TABLE IF NOT EXISTS `PART_COL_PRIVS` ( - `PART_COLUMN_GRANT_ID` bigint(20) NOT NULL, - `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `CREATE_TIME` int(11) NOT NULL, - `GRANT_OPTION` smallint(6) NOT NULL, - `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PART_ID` bigint(20) DEFAULT NULL, - `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PART_COL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - PRIMARY KEY (`PART_COLUMN_GRANT_ID`), - KEY `PARTITIONCOLUMNPRIVILEGEINDEX` (`PART_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), - CONSTRAINT `PART_COL_PRIVS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -CREATE TABLE IF NOT EXISTS `PART_PRIVS` ( - `PART_GRANT_ID` bigint(20) NOT NULL, - `CREATE_TIME` int(11) NOT NULL, - `GRANT_OPTION` smallint(6) NOT NULL, - `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PART_ID` bigint(20) DEFAULT NULL, - `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PART_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - PRIMARY KEY (`PART_GRANT_ID`), - KEY `PARTPRIVILEGEINDEX` (`PART_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), - CONSTRAINT `PART_PRIVS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -CREATE TABLE IF NOT EXISTS `ROLES` ( - `ROLE_ID` bigint(20) NOT NULL, - `CREATE_TIME` int(11) NOT NULL, - `OWNER_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `ROLE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - PRIMARY KEY (`ROLE_ID`), - UNIQUE KEY `ROLEENTITYINDEX` (`ROLE_NAME`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -CREATE TABLE IF NOT EXISTS `ROLE_MAP` ( - `ROLE_GRANT_ID` bigint(20) NOT NULL, - `ADD_TIME` int(11) NOT NULL, - `GRANT_OPTION` smallint(6) NOT NULL, - `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `ROLE_ID` bigint(20) DEFAULT NULL, - PRIMARY KEY (`ROLE_GRANT_ID`), - UNIQUE KEY `USERROLEMAPINDEX` (`PRINCIPAL_NAME`,`ROLE_ID`,`GRANTOR`,`GRANTOR_TYPE`), - CONSTRAINT `ROLE_MAP_FK1` FOREIGN KEY (`ROLE_ID`) REFERENCES `ROLES` (`ROLE_ID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -CREATE TABLE IF NOT EXISTS `TBL_COL_PRIVS` ( - `TBL_COLUMN_GRANT_ID` bigint(20) NOT NULL, - `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `CREATE_TIME` int(11) NOT NULL, - `GRANT_OPTION` smallint(6) NOT NULL, - `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `TBL_COL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `TBL_ID` bigint(20) DEFAULT NULL, - PRIMARY KEY (`TBL_COLUMN_GRANT_ID`), - KEY `TABLECOLUMNPRIVILEGEINDEX` (`TBL_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`TBL_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), - CONSTRAINT `TBL_COL_PRIVS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -CREATE TABLE IF NOT EXISTS `TBL_PRIVS` ( - `TBL_GRANT_ID` bigint(20) NOT NULL, - `CREATE_TIME` int(11) NOT NULL, - `GRANT_OPTION` smallint(6) NOT NULL, - `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `TBL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `TBL_ID` bigint(20) DEFAULT NULL, - PRIMARY KEY (`TBL_GRANT_ID`), - KEY `TABLEPRIVILEGEINDEX` (`TBL_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`TBL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), - CONSTRAINT `TBL_PRIVS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -CREATE TABLE IF NOT EXISTS `GLOBAL_PRIVS` ( - `USER_GRANT_ID` bigint(20) NOT NULL, - `CREATE_TIME` int(11) NOT NULL, - `GRANT_OPTION` smallint(6) NOT NULL, - `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `USER_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - PRIMARY KEY (`USER_GRANT_ID`), - UNIQUE KEY `GLOBALPRIVILEGEINDEX` (`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`USER_PRIV`,`GRANTOR`,`GRANTOR_TYPE`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; diff --git metastore/src/model/package.jdo metastore/src/model/package.jdo index 88ac402..d9df583 100644 --- metastore/src/model/package.jdo +++ metastore/src/model/package.jdo @@ -377,7 +377,7 @@ - + @@ -416,7 +416,7 @@ - + @@ -424,7 +424,7 @@ - + @@ -453,7 +453,7 @@ - + @@ -494,7 +494,7 @@ - + @@ -535,7 +535,7 @@ - + @@ -576,7 +576,7 @@ - + @@ -621,7 +621,7 @@ - +