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