-- -- 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");