This is an automated email from the ASF dual-hosted git repository.
dataroaring pushed a commit to branch branch-3.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-3.0 by this push:
new c7ded7a55fe branch-3.0: [test](mtmv)Test and check the differences
between MTMV and OLAP (#43432)
c7ded7a55fe is described below
commit c7ded7a55fec974a6480380e951a3c7f6a22f5fa
Author: github-actions[bot]
<41898282+github-actions[bot]@users.noreply.github.com>
AuthorDate: Thu Nov 14 12:32:33 2024 +0800
branch-3.0: [test](mtmv)Test and check the differences between MTMV and
OLAP (#43432)
Cherry-picked from #36520
Co-authored-by: zfr95 <[email protected]>
---
.../suites/mtmv_p0/test_mtmv_outfile.groovy | 93 +++++++++
.../suites/mtmv_p0/test_mtmv_property.groovy | 216 +++++++++++++++++++++
.../mtmv_p0/test_mtmv_sql_cache_and_profile.groovy | 87 +++++++++
3 files changed, 396 insertions(+)
diff --git a/regression-test/suites/mtmv_p0/test_mtmv_outfile.groovy
b/regression-test/suites/mtmv_p0/test_mtmv_outfile.groovy
new file mode 100644
index 00000000000..693df86e014
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/test_mtmv_outfile.groovy
@@ -0,0 +1,93 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+import org.junit.Assert
+
+suite("test_mtmv_outfile","mtmv") {
+
+ String dbName = context.config.getDbNameByFile(context.file)
+ String suiteName = "test_mtmv_outfile"
+ String tableName = "${suiteName}_table"
+ String mvName = "${suiteName}_mv"
+
+ sql """drop table if exists `${tableName}`"""
+ sql """drop materialized view if exists ${mvName};"""
+
+ sql """
+ CREATE TABLE ${tableName}
+ (
+ k2 INT,
+ k3 varchar(32)
+ )
+ DISTRIBUTED BY HASH(k2) BUCKETS 2
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+
+ sql """
+ insert into ${tableName} values (1,1),(1,2);
+ """
+
+ sql """
+ CREATE MATERIALIZED VIEW ${mvName}
+ BUILD DEFERRED REFRESH AUTO ON MANUAL
+ DISTRIBUTED BY hash(k2) BUCKETS 2
+ PROPERTIES (
+ 'replication_num' = '1'
+ )
+ AS
+ SELECT * from ${tableName};
+ """
+ sql """
+ REFRESH MATERIALIZED VIEW ${mvName} AUTO;
+ """
+
+ def jobName = getJobName(dbName, mvName)
+ waitingMTMVTaskFinished(jobName)
+
+ // use to outfile to s3
+ String ak = getS3AK()
+ String sk = getS3SK()
+ String s3_endpoint = getS3Endpoint()
+ String region = region = getS3Region()
+ String bucket = context.config.otherConfigs.get("s3BucketName");
+
+ def outfile_to_S3_directly = {
+ // select ... into outfile ...
+ def s3_outfile_path = "${bucket}/outfile/csv/test-mtmv-outfile"
+ def uri = "s3://${s3_outfile_path}/exp_"
+
+ def res = sql """
+ SELECT * FROM ${mvName} t
+ INTO OUTFILE "${uri}"
+ FORMAT AS csv
+ PROPERTIES (
+ "s3.endpoint" = "${s3_endpoint}",
+ "s3.region" = "${region}",
+ "s3.secret_key"="${sk}",
+ "s3.access_key" = "${ak}"
+ );
+ """
+ logger.info("outfile to s3 success path: " + res[0][3]);
+ }
+
+ outfile_to_S3_directly()
+
+ sql """drop table if exists `${tableName}`"""
+ sql """drop materialized view if exists ${mvName};"""
+}
diff --git a/regression-test/suites/mtmv_p0/test_mtmv_property.groovy
b/regression-test/suites/mtmv_p0/test_mtmv_property.groovy
new file mode 100644
index 00000000000..e0ffbca8dbf
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/test_mtmv_property.groovy
@@ -0,0 +1,216 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+import org.junit.Assert;
+
+suite("test_mtmv_property","mtmv") {
+ if (isCloudMode()) {
+ return
+ }
+ String dbName = context.config.getDbNameByFile(context.file)
+ String suiteName = "test_mtmv_property"
+ String tableName = "${suiteName}_table"
+ String mvName = "${suiteName}_mv"
+
+ sql """drop table if exists `${tableName}`"""
+ sql """drop materialized view if exists ${mvName};"""
+
+ sql """
+ CREATE TABLE ${tableName}
+ (
+ k2 INT,
+ k3 varchar(32)
+ )
+ DISTRIBUTED BY HASH(k2) BUCKETS 2
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+ sql """
+ CREATE MATERIALIZED VIEW ${mvName}
+ BUILD DEFERRED REFRESH AUTO ON MANUAL
+ DISTRIBUTED BY hash(k2) BUCKETS 2
+ PROPERTIES (
+ 'replication_num' = '1',
+ 'store_row_column' = 'true',
+ 'skip_write_index_on_load' = 'false',
+ "binlog.enable" = "false",
+ "binlog.ttl_seconds" = "86400",
+ "binlog.max_bytes" = "9223372036854775807",
+ "binlog.max_history_nums" = "9223372036854775807",
+ "enable_duplicate_without_keys_by_default" = "true"
+ )
+ AS
+ SELECT * from ${tableName};
+ """
+
+ def showCreateTableResult = sql """show create materialized view
${mvName}"""
+ logger.info("showCreateTableResult: " + showCreateTableResult.toString())
+
assertTrue(showCreateTableResult.toString().contains('tag.location.default: 1'))
+
assertTrue(showCreateTableResult.toString().contains('"min_load_replica_num" =
"-1"'))
+ assertTrue(showCreateTableResult.toString().contains('"storage_medium" =
"hdd"'))
+ assertTrue(showCreateTableResult.toString().contains('"store_row_column" =
"true"'))
+
assertTrue(showCreateTableResult.toString().contains('"enable_duplicate_without_keys_by_default"
= "true"'))
+
+ sql """
+ insert into ${tableName} values (2,1),(2,2);
+ """
+ sql """
+ REFRESH MATERIALIZED VIEW ${mvName} AUTO;
+ """
+ def jobName = getJobName(dbName, mvName)
+ waitingMTMVTaskFinished(jobName)
+ sql """select * from ${mvName}"""
+
+ // replication_num
+ sql """
+ ALTER TABLE ${mvName} set ("replication_num" = "1");
+ """
+ showCreateTableResult = sql """show create materialized view ${mvName}"""
+ logger.info("showCreateTableResult: " + showCreateTableResult.toString())
+
assertTrue(showCreateTableResult.toString().contains('tag.location.default: 1'))
+
+ // replication_allocation
+ sql """
+ ALTER TABLE ${mvName} set ("replication_allocation" =
"tag.location.default: 1");
+ """
+ showCreateTableResult = sql """show create materialized view ${mvName}"""
+ logger.info("showCreateTableResult: " + showCreateTableResult.toString())
+
assertTrue(showCreateTableResult.toString().contains('tag.location.default: 1'))
+
+ // min_load_replica_num
+ sql """
+ ALTER TABLE ${mvName} set ("min_load_replica_num" = "1");
+ """
+ showCreateTableResult = sql """show create materialized view ${mvName}"""
+ logger.info("showCreateTableResult: " + showCreateTableResult.toString())
+
assertTrue(showCreateTableResult.toString().contains('"min_load_replica_num" =
"1"'))
+
+ // type
+ sql """
+ insert into ${tableName} values (3,1),(3,2);
+ """
+ sql """
+ REFRESH MATERIALIZED VIEW ${mvName} AUTO;
+ """
+ jobName = getJobName(dbName, mvName)
+ waitingMTMVTaskFinished(jobName)
+ def tablets_res = sql """show tablets from ${mvName}"""
+ assertTrue(tablets_res.size() > 0)
+ sql """
+ admin check tablet (${tablets_res[0][0].toString()})
properties("type"="consistency");
+ """
+
+ // storage_policy
+ def resource_name = "test_mtmv_remote_s3_resource"
+ def policy_name= "test_mtmv_storage_policy"
+ sql """
+ CREATE RESOURCE IF NOT EXISTS "${resource_name}"
+ PROPERTIES(
+ "type"="s3",
+ "AWS_ENDPOINT" = "${getS3Endpoint()}",
+ "AWS_REGION" = "${getS3Region()}",
+ "AWS_ROOT_PATH" = "regression/cooldown",
+ "AWS_ACCESS_KEY" = "${getS3AK()}",
+ "AWS_SECRET_KEY" = "${getS3SK()}",
+ "AWS_MAX_CONNECTIONS" = "50",
+ "AWS_REQUEST_TIMEOUT_MS" = "3000",
+ "AWS_CONNECTION_TIMEOUT_MS" = "1000",
+ "AWS_BUCKET" = "${getS3BucketName()}",
+ "s3_validity_check" = "true"
+ );
+ """
+
+ sql """
+ CREATE STORAGE POLICY IF NOT EXISTS ${policy_name}
+ PROPERTIES(
+ "storage_resource" = "${resource_name}",
+ "cooldown_ttl" = "300"
+ )
+ """
+
+ sql """ALTER TABLE ${mvName} SET ("storage_policy" = "${policy_name}");"""
+ showCreateTableResult = sql """show create materialized view ${mvName}"""
+ logger.info("showCreateTableResult: " + showCreateTableResult.toString())
+ assertTrue(showCreateTableResult.toString().contains('"storage_policy" =
"test_mtmv_storage_policy"'))
+
+ // store_row_column
+ try {
+ sql """ALTER TABLE ${mvName} SET ("store_row_column" = "false");"""
+ } catch (Exception e) {
+ log.info(e.getMessage())
+ assertTrue(e.getMessage().contains("Can not alter store_row_column"))
+ }
+
+ // is_being_synced
+ sql """
+ ALTER TABLE ${mvName} set ("is_being_synced" = "true");
+ """
+ showCreateTableResult = sql """show create materialized view ${mvName}"""
+ logger.info("showCreateTableResult: " + showCreateTableResult.toString())
+ assertTrue(showCreateTableResult.toString().contains('"is_being_synced" =
"true"'))
+
+ // skip_write_index_on_load
+ def desc_res = sql """desc ${mvName}"""
+ assertTrue(desc_res.size() == 2)
+ assertTrue(desc_res[0][3] == "false")
+ assertTrue(desc_res[1][3] == "false")
+
+ // enable_unique_key_merge_on_write
+ try {
+ sql """
+ CREATE MATERIALIZED VIEW mv_unique_key_merge_on_write
+ BUILD DEFERRED REFRESH AUTO ON MANUAL
+ DISTRIBUTED BY hash(k2) BUCKETS 2
+ PROPERTIES (
+ 'replication_num' = '1',
+ "enable_unique_key_merge_on_write" = "true"
+ )
+ AS
+ SELECT * from ${tableName};
+ """
+ } catch (Exception e) {
+ log.info(e.getMessage())
+ assertTrue(e.getMessage().contains("Unknown properties"))
+ }
+
+ // group_commit_interval_ms
+ sql """ALTER TABLE ${mvName} SET ("group_commit_interval_ms" = "2000");"""
+ showCreateTableResult = sql """show create materialized view ${mvName}"""
+ logger.info("showCreateTableResult: " + showCreateTableResult.toString())
+
assertTrue(showCreateTableResult.toString().contains('"group_commit_interval_ms"
= "2000"'))
+
+ // group_commit_data_bytes
+ sql """ALTER TABLE ${mvName} SET ("group_commit_data_bytes" =
"234217728");"""
+ showCreateTableResult = sql """show create materialized view ${mvName}"""
+ logger.info("showCreateTableResult: " + showCreateTableResult.toString())
+
assertTrue(showCreateTableResult.toString().contains('"group_commit_data_bytes"
= "234217728"'))
+
+ // check mv can update normally
+ sql """
+ insert into ${tableName} values (1,1),(1,2);
+ """
+ sql """
+ REFRESH MATERIALIZED VIEW ${mvName} AUTO;
+ """
+ jobName = getJobName(dbName, mvName)
+ waitingMTMVTaskFinished(jobName)
+ sql """select * from ${mvName}"""
+
+ sql """drop table if exists `${tableName}`"""
+ sql """drop materialized view if exists ${mvName};"""
+}
diff --git
a/regression-test/suites/mtmv_p0/test_mtmv_sql_cache_and_profile.groovy
b/regression-test/suites/mtmv_p0/test_mtmv_sql_cache_and_profile.groovy
new file mode 100644
index 00000000000..02191f0d4cd
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/test_mtmv_sql_cache_and_profile.groovy
@@ -0,0 +1,87 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+import org.junit.Assert;
+
+suite("test_mtmv_sql_cache_and_profile", "mtmv") {
+
+ sql """ADMIN SET FRONTEND CONFIG ('cache_enable_sql_mode' = 'true')"""
+
+ String dbName = context.config.getDbNameByFile(context.file)
+ String suiteName = "test_mtmv_sql_cache_and_profile"
+ String tableName = "${suiteName}_table"
+ String mvName = "${suiteName}_mv"
+ sql """use ${dbName};"""
+ sql """drop table if exists `${tableName}`"""
+ sql """drop materialized view if exists ${mvName};"""
+
+ sql """
+ CREATE TABLE ${tableName}
+ (
+ k2 INT,
+ k3 varchar(32)
+ )
+ DISTRIBUTED BY HASH(k2) BUCKETS 2
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+
+ sql """
+ insert into ${tableName} values (1,1),(1,2);
+ """
+
+ sql """
+ CREATE MATERIALIZED VIEW ${mvName}
+ BUILD DEFERRED REFRESH AUTO ON MANUAL
+ DISTRIBUTED BY hash(k2) BUCKETS 2
+ PROPERTIES (
+ 'replication_num' = '1'
+ )
+ AS
+ SELECT * from ${tableName};
+ """
+ sql """
+ REFRESH MATERIALIZED VIEW ${mvName} AUTO;
+ """
+
+ def jobName = getJobName(dbName, mvName)
+ waitingMTMVTaskFinished(jobName)
+
+ sql """set enable_sql_cache=true;"""
+
+ long startTime = System.currentTimeMillis()
+ long timeoutTimestamp = startTime + 5 * 60 * 1000
+ def explain_res = ""
+ while (System.currentTimeMillis() < timeoutTimestamp) {
+ sleep(5 * 1000)
+ sql """select k2 from ${mvName} group by k2;"""
+ try {
+ explain_res = sql """explain plan select k2 from ${mvName} group
by k2;"""
+ } catch (Exception e) {
+ logger.info(e.getMessage())
+ }
+ logger.info("explain_res: " + explain_res)
+ if (explain_res.toString().indexOf("LogicalSqlCache") != -1 ||
explain_res.toString().indexOf("PhysicalSqlCache") != -1) {
+ break
+ }
+ }
+ assertTrue(explain_res.toString().indexOf("LogicalSqlCache") != -1 ||
explain_res.toString().indexOf("PhysicalSqlCache") != -1)
+
+ sql """drop table if exists `${tableName}`"""
+ sql """drop materialized view if exists ${mvName};"""
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
