zabetak commented on code in PR #6825:
URL: https://github.com/apache/hive/pull/6825#discussion_r4413752033
##########
standalone-metastore/metastore-server/src/test/resources/sql/postgres/migrate-metastore-dump-to-4.3.0.postgres.sql:
##########
@@ -0,0 +1,56 @@
+-- The file has some overlapping with
upgrade-3.2.0-to-4.0.0-alpha-1.postgres.sql
+SELECT 'Upgrading MetaStore schema from 3.1.3000 to 4.3.0';
+
+-- The dump was created from a metastore where some columns had the wrong
types.
+-- Fix those column types that some tests verify.
+UPDATE "COLUMNS_V2" c
+SET "TYPE_NAME" = 'char(16)'
+WHERE "COLUMN_NAME" in ('c_customer_id', 'ca_address_id')
+AND EXISTS (SELECT * FROM "SDS" s WHERE s."CD_ID" = c."CD_ID");
+
+-- HIVE-20793
+ALTER TABLE "WM_RESOURCEPLAN" ADD "NS" character varying(128);
+UPDATE "WM_RESOURCEPLAN" SET "NS" = 'default' WHERE "NS" IS NULL;
+ALTER TABLE "WM_RESOURCEPLAN" DROP CONSTRAINT "UNIQUE_WM_RESOURCEPLAN";
+ALTER TABLE ONLY "WM_RESOURCEPLAN" ADD CONSTRAINT "UNIQUE_WM_RESOURCEPLAN"
UNIQUE ("NS", "NAME");
+
+-- HIVE-22046 (DEFAULT HIVE)
+UPDATE "TAB_COL_STATS" SET "ENGINE" = 'hive' WHERE "ENGINE" IS NULL;
+UPDATE "PART_COL_STATS" SET "ENGINE" = 'hive' WHERE "ENGINE" IS NULL;
+
+-- HIVE-22728
+ALTER TABLE "KEY_CONSTRAINTS" DROP CONSTRAINT "KEY_CONSTRAINTS_pkey";
+ALTER TABLE "KEY_CONSTRAINTS" ADD CONSTRAINT "CONSTRAINTS_PK" PRIMARY KEY
("PARENT_TBL_ID", "CONSTRAINT_NAME", "POSITION");
+
+-- HIVE-23048
+INSERT INTO "TXNS" ("TXN_ID", "TXN_STATE", "TXN_STARTED",
"TXN_LAST_HEARTBEAT", "TXN_USER", "TXN_HOST")
+SELECT COALESCE(MAX("CTC_TXNID"),0), 'c', 0, 0, '', '' FROM
"COMPLETED_TXN_COMPONENTS";
+CREATE SEQUENCE "TXNS_TXN_ID_SEQ" MINVALUE 0 OWNED BY "TXNS"."TXN_ID";
+select setval('"TXNS_TXN_ID_SEQ"', (SELECT MAX("TXN_ID") FROM "TXNS"));
+ALTER TABLE "TXNS" ALTER "TXN_ID" SET DEFAULT nextval('"TXNS_TXN_ID_SEQ"');
+
+ALTER TABLE "NEXT_TXN_ID" RENAME TO "TXN_LOCK_TBL";
+ALTER TABLE "TXN_LOCK_TBL" RENAME COLUMN "NTXN_NEXT" TO "TXN_LOCK";
+
+--Increase the size of RM_PROGRESS to accomodate the replication statistics
+ALTER TABLE "REPLICATION_METRICS" ALTER "RM_PROGRESS" TYPE varchar(24000);
+
+ALTER TABLE "REPLICATION_METRICS" ALTER "RM_PROGRESS" TYPE varchar(10000);
+
+-- HIVE-24770
+UPDATE "SERDES" SET "SLIB"='org.apache.hadoop.hive.serde2.MultiDelimitSerDe'
where "SLIB"='org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe';
+UPDATE "DBS" SET "TYPE"= 'NATIVE' WHERE "TYPE" IS NULL;
+
+-- HIVE-29178
+-- Table structure for CATALOG_PARAMS
+CREATE TABLE "CATALOG_PARAMS" (
+ "CTLG_ID" BIGINT NOT NULL,
+ "PARAM_KEY" VARCHAR(180) NOT NULL,
+ "PARAM_VALUE" VARCHAR(4000) DEFAULT NULL,
+ PRIMARY KEY ("CTLG_ID", "PARAM_KEY"),
+ CONSTRAINT "CATALOG_PARAMS_FK1" FOREIGN KEY ("CTLG_ID") REFERENCES "CTLGS"
("CTLG_ID") ON DELETE CASCADE
+);
+
Review Comment:
For `DESCRIBE` commands to work it seems that we have to include this in the
upgrade script.
```suggestion
-- HIVE-29244
ALTER TABLE "HIVE_LOCKS" ADD COLUMN "HL_CATALOG" varchar(128) NOT NULL
DEFAULT 'hive';
ALTER TABLE "MATERIALIZATION_REBUILD_LOCKS" ADD COLUMN "MRL_CAT_NAME"
varchar(128) NOT NULL DEFAULT 'hive';
```
Again this is a finding after testing with HIVE-29476.
##########
standalone-metastore/metastore-server/src/test/resources/sql/postgres/migrate-metastore-dump-to-4.3.0.postgres.sql:
##########
@@ -0,0 +1,56 @@
+-- The file has some overlapping with
upgrade-3.2.0-to-4.0.0-alpha-1.postgres.sql
+SELECT 'Upgrading MetaStore schema from 3.1.3000 to 4.3.0';
+
+-- The dump was created from a metastore where some columns had the wrong
types.
+-- Fix those column types that some tests verify.
+UPDATE "COLUMNS_V2" c
+SET "TYPE_NAME" = 'char(16)'
+WHERE "COLUMN_NAME" in ('c_customer_id', 'ca_address_id')
+AND EXISTS (SELECT * FROM "SDS" s WHERE s."CD_ID" = c."CD_ID");
Review Comment:
I was doing some sanity checks on the content of the metastore using
DESCRIBE commands (i.e., HIVE-29476) and noticed that for these two columns
mentioned here the statistics disappear from the output.
https://github.com/zabetak/hive/blob/28e32f6ce91f3051c045030aecd502d23d248ee3/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/desc_customer.q.out#L97
```
col_name c_customer_id
data_type char(16)
min
max
num_nulls
distinct_count
avg_col_len
max_col_len
num_trues
num_falses
bit_vector
comment from deserializer
```
https://github.com/zabetak/hive/blob/28e32f6ce91f3051c045030aecd502d23d248ee3/ql/src/test/results/clientpositive/perf/tpcds30tb/tez/desc_customer_address.q.out#L68-L79
```
col_name ca_address_id
data_type char(16)
min
max
num_nulls
distinct_count
avg_col_len
max_col_len
num_trues
num_falses
bit_vector
comment from deserializer
```
I am not sure if it affects query planning but let's check why it happens
and if it is something that needs to be fixed as part of this PR or a follow-up.
##########
standalone-metastore/metastore-server/docker/hive-postgres-tpcds-metastore/Dockerfile:
##########
@@ -14,9 +14,19 @@
# See the License for the specific language governing permissions and
# limitations under the License.
#
-FROM postgres:12.3
+FROM postgres:18-alpine
+
+RUN wget -O /tmp/metastore_db.zstd
https://github.com/thomasrebele/hive-postgres-metastore/releases/download/tpcds-30tb-histogram-1.1/metastore_tpcds30tb_with_histograms.raw_db.zstd
\
+ && echo 'bc9e206d5f963a14451dad6f499ab069800be9ce /tmp/metastore_db.zstd' >
/tmp/metastore_db.sha1 \
+ && sha1sum -c /tmp/metastore_db.sha1 \
+ && chown postgres:postgres /tmp/metastore_db.zstd
+
+COPY entrypoint.sh /usr/local/bin/entrypoint.sh
+RUN chmod u+x /usr/local/bin/entrypoint.sh
+COPY postgresql.conf /etc/postgresql/postgresql.conf
+RUN chown postgres:postgres /etc/postgresql/postgresql.conf
+
+ENTRYPOINT ["/usr/local/bin/entrypoint.sh"]
+
+CMD ["postgres", "-c", "config_file=/etc/postgresql/postgresql.conf"]
Review Comment:
The principal use-case of the image is via `PostgresTPCDS.java` and that
will not use the custom `postgresql.conf`. Even if someone uses the image
directly most of the time the expectation will be to run with the same
configuration that CI/tests run and very few people will come and read the
`Dockerfile` itself.
Since the custom `postgresql.conf` is not exercised by CI and tests, I would
suggest dropping it and removing it from the image to avoid any potential
confusion and maintenance overhead.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
