This is an automated email from the ASF dual-hosted git repository. zabetak pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/hive-test-datasets.git
commit 82efde99021c5f4513f2d744612545427ba27069 Author: Stamatis Zampetakis <[email protected]> AuthorDate: Tue May 5 13:24:48 2026 +0200 Add information about the TPC-DS metastore dumps --- README.md | 23 +++++++++++++++++++++- metastore/cleanup.sql | 54 +++++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 76 insertions(+), 1 deletion(-) diff --git a/README.md b/README.md index 5d3df7c..e204b06 100644 --- a/README.md +++ b/README.md @@ -1,2 +1,23 @@ -# hive-test-datasets +# Test datasets for Apache Hive A repository holding datasets and dumps that are used for testing Apache Hive. + +## Metastore database dumps + +A collection of database dumps obtained from various versions of Hive. +The dumps can be found in the GitHub release asserts. They are not under version control since they are in general large and not expected to change very often. +In addition, the dumps are in binary format so they cannot be easily inspected by humans. + +#### Postgres + +The database dumps were obtained from a Hive metastore running Cloudera's Hive 3.1.3000 using the following command: + + pg_dump -h localhost -p 5432 -U hive -d metastore -Fc > meta.dump.gz + +Some manual post-processing has been performed to remove redundant information from the dump using the [cleanup.sql](metastore/cleanup.sql) file. +Nevertheless, the dumps may still contain additional databases and tables but these are not actively used. + +##### TPC-DS + +Metastore dumps from various [TPC-DS](http://www.tpc.org/tpcds/) scale factors. +* [metastore_tpcds10tb_3_1_3000](https://github.com/zabetak/hive-test-datasets/releases/download/1.0/metastore_tpcds10tb_3_1_3000.dump.gz) +* [metastore_tpcds30tb_3_1_3000](https://github.com/zabetak/hive-test-datasets/releases/download/1.0/metastore_tpcds30tb_3_1_3000.dump.gz) diff --git a/metastore/cleanup.sql b/metastore/cleanup.sql new file mode 100644 index 0000000..311a937 --- /dev/null +++ b/metastore/cleanup.sql @@ -0,0 +1,54 @@ +-- Script for partially anonymizing and cleaning up the dump. +-- WARNING: The script is not guaranteed to cover any kind of metastore +-- (not even close). It is mostly suitable for cases encountered so far. + +-- Statements to remove potentially sensitive URIs and usernames. +UPDATE "CTLGS" SET "LOCATION_URI" = REGEXP_REPLACE("LOCATION_URI",'(s3a|hdfs)://[^/]+','hdfs://localhost:40889'); +UPDATE "DBS" SET "DB_LOCATION_URI" = REGEXP_REPLACE("DB_LOCATION_URI",'(s3a|hdfs)://[^/]+','hdfs://localhost:40889'); +UPDATE "DBS" SET "OWNER_NAME" = 'hive' WHERE "OWNER_NAME" IS NOT NULL AND "OWNER_NAME" <> 'public'; +UPDATE "SDS" SET "LOCATION" = REGEXP_REPLACE("LOCATION",'(s3a|hdfs)://[^/]+','hdfs://localhost:40889') WHERE "LOCATION" IS NOT NULL; +UPDATE "TBLS" SET "OWNER" = 'hive' WHERE "OWNER" IS NOT NULL AND "OWNER" <> 'public'; +-- Statements to cleanup transactions related state in metastore +TRUNCATE TABLE "HIVE_LOCKS" CASCADE ; +TRUNCATE TABLE "NOTIFICATION_LOG"; +TRUNCATE TABLE "TXNS" CASCADE; +TRUNCATE TABLE "TXN_TO_WRITE_ID"CASCADE; +TRUNCATE TABLE "TXN_COMPONENTS" CASCADE; +TRUNCATE TABLE "COMPLETED_TXN_COMPONENTS" CASCADE; +TRUNCATE TABLE "NEXT_WRITE_ID" CASCADE; +TRUNCATE TABLE "NEXT_LOCK_ID" CASCADE; +TRUNCATE TABLE "COMPACTION_QUEUE" CASCADE; +TRUNCATE TABLE "NEXT_COMPACTION_QUEUE_ID" CASCADE; +TRUNCATE TABLE "COMPLETED_COMPACTIONS" CASCADE; +TRUNCATE TABLE "AUX_TABLE" CASCADE; +TRUNCATE TABLE "WRITE_SET" CASCADE; +TRUNCATE TABLE "REPL_TXN_MAP" CASCADE; +TRUNCATE TABLE "MATERIALIZATION_REBUILD_LOCKS" CASCADE; +TRUNCATE TABLE "MIN_HISTORY_LEVEL" CASCADE; + +INSERT INTO "NEXT_LOCK_ID" VALUES(1); +INSERT INTO "NEXT_COMPACTION_QUEUE_ID" VALUES(1); + + +-- Statements to replace Hive default database with tpcds +DELETE FROM "TABLE_PARAMS" WHERE "TBL_ID" IN (SELECT "TBLS"."TBL_ID" FROM "TBLS" WHERE EXISTS (SELECT 1 FROM "DBS" WHERE "DBS"."DB_ID"="TBLS"."DB_ID" AND "NAME" = 'default')); +DELETE FROM "TAB_COL_STATS" WHERE "TBL_ID" IN (SELECT "TBLS"."TBL_ID" FROM "TBLS" WHERE EXISTS (SELECT 1 FROM "DBS" WHERE "DBS"."DB_ID"="TBLS"."DB_ID" AND "NAME" = 'default')); +DELETE FROM "MV_TABLES_USED" WHERE "TBL_ID" IN (SELECT "TBLS"."TBL_ID" FROM "TBLS" WHERE EXISTS (SELECT 1 FROM "DBS" WHERE "DBS"."DB_ID"="TBLS"."DB_ID" AND "NAME" = 'default')); +DELETE FROM "TBL_PRIVS" WHERE "TBL_ID" IN (SELECT "TBLS"."TBL_ID" FROM "TBLS" WHERE EXISTS (SELECT 1 FROM "DBS" WHERE "DBS"."DB_ID"="TBLS"."DB_ID" AND "NAME" = 'default')); +DELETE FROM "PARTITION_KEYS" WHERE "TBL_ID" IN (SELECT "TBLS"."TBL_ID" FROM "TBLS" WHERE EXISTS (SELECT 1 FROM "DBS" WHERE "DBS"."DB_ID"="TBLS"."DB_ID" AND "NAME" = 'default')); +DELETE FROM "PARTITION_KEY_VALS" WHERE "PART_ID" IN (SELECT "PART_ID" FROM "PARTITIONS" WHERE "TBL_ID" IN (SELECT "TBLS"."TBL_ID" FROM "TBLS" WHERE EXISTS (SELECT 1 FROM "DBS" WHERE "DBS"."DB_ID"="TBLS"."DB_ID" AND "NAME" = 'default'))); +DELETE FROM "PARTITION_PARAMS" WHERE "PART_ID" IN (SELECT "PART_ID" FROM "PARTITIONS" WHERE "TBL_ID" IN (SELECT "TBLS"."TBL_ID" FROM "TBLS" WHERE EXISTS (SELECT 1 FROM "DBS" WHERE "DBS"."DB_ID"="TBLS"."DB_ID" AND "NAME" = 'default'))); +DELETE FROM "PART_COL_STATS" WHERE "PART_ID" IN (SELECT "PART_ID" FROM "PARTITIONS" WHERE "TBL_ID" IN (SELECT "TBLS"."TBL_ID" FROM "TBLS" WHERE EXISTS (SELECT 1 FROM "DBS" WHERE "DBS"."DB_ID"="TBLS"."DB_ID" AND "NAME" = 'default'))); +DELETE FROM "PARTITIONS" WHERE "TBL_ID" IN (SELECT "TBLS"."TBL_ID" FROM "TBLS" WHERE EXISTS (SELECT 1 FROM "DBS" WHERE "DBS"."DB_ID"="TBLS"."DB_ID" AND "NAME" = 'default')); +DELETE FROM "KEY_CONSTRAINTS" WHERE "PARENT_TBL_ID" IN (SELECT "TBLS"."TBL_ID" FROM "TBLS" WHERE EXISTS (SELECT 1 FROM "DBS" WHERE "DBS"."DB_ID"="TBLS"."DB_ID" AND "NAME" = 'default')); +DELETE FROM "KEY_CONSTRAINTS" WHERE "CHILD_TBL_ID" IN (SELECT "TBLS"."TBL_ID" FROM "TBLS" WHERE EXISTS (SELECT 1 FROM "DBS" WHERE "DBS"."DB_ID"="TBLS"."DB_ID" AND "NAME" = 'default')); + +DELETE FROM "TBLS" WHERE "DB_ID" IN (SELECT "DB_ID" FROM "DBS" WHERE "NAME" = 'default'); + +UPDATE "TBLS" SET "DB_ID" = (SELECT "DB_ID" FROM "DBS" WHERE "NAME" = 'default') WHERE EXISTS (SELECT 1 FROM "DBS" WHERE "DBS"."DB_ID"="TBLS"."DB_ID" AND "DBS"."NAME" LIKE 'tpcds%'); + +TRUNCATE "DATABASE_PARAMS"; +DELETE FROM "DBS" WHERE "NAME" LIKE 'tpcds%'; + +UPDATE "TAB_COL_STATS" SET "DB_NAME" = 'default' WHERE "DB_NAME" LIKE 'tpcds%'; +UPDATE "PART_COL_STATS" SET "DB_NAME" = 'default' WHERE "DB_NAME" LIKE 'tpcds%';
