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
commit 5527c544ce77dee0c77d1b2275bcdb90917c8216 Author: Vallish Pai <[email protected]> AuthorDate: Tue May 28 08:11:50 2024 +0530 [Enhancement] add information_schema.table_options(#32572) (#34384) ## Proposed changes Issue Number: close #32572 Support table_options in information_schema. UT result: mysql> select * from information_schema.table_options where table_schema="test_table_options_db"\G *************************** 1. row *************************** TABLE_NAME: rangetable TABLE_CATALOG: internal TABLE_SCHEMA: test_table_options_db DISTRIBUTE_KEY: user_id DISTRIBUTE_TYPE: HASH BUCKETS_NUM: 8 PARTITION_NUM: 3 PROPERTIES: min_load_replica_num = -1, data_sort.col_num = 6, group_commit_interval_ms = 10000, data_sort.sort_type = LEXICAL, is_being_synced = false, binlog.enable = false, binlog.ttl_seconds = 86400, inverted_index_storage_format = V1, time_series_compaction_empty_rowsets_threshold = 5, default.replication_allocation = tag.location.default: 1, time_series_compaction_level_threshold = 1, time_series_compaction_time_threshold_seconds = 3600, storage_format = V2, store_row_column = false, light_schema_change = true, enable_unique_key_merge_on_write = false, in_memory = false, file_cache_ttl_seconds = 0, group_commit_data_bytes = 134217728, compaction_policy = size_based, _auto_bucket = false, binlog.max_history_nums = 9223372036854775807, time_series_compaction_file_count_threshold = 2000, skip_write_index_on_load = false, disable_auto_compaction = false, time_series_compaction_goal_size_mbytes = 1024, storage_medium = HDD, enable_single_replica_compaction = false, compression = LZ4F, binlog.max_bytes = 9223372036854775807 *************************** 2. row *************************** TABLE_NAME: listtable TABLE_CATALOG: internal TABLE_SCHEMA: test_table_options_db DISTRIBUTE_KEY: user_id DISTRIBUTE_TYPE: HASH BUCKETS_NUM: 16 PARTITION_NUM: 3 PROPERTIES: min_load_replica_num = -1, data_sort.col_num = 6, group_commit_interval_ms = 10000, data_sort.sort_type = LEXICAL, is_being_synced = false, binlog.enable = false, binlog.ttl_seconds = 86400, inverted_index_storage_format = V1, time_series_compaction_empty_rowsets_threshold = 5, default.replication_allocation = tag.location.default: 1, time_series_compaction_level_threshold = 1, time_series_compaction_time_threshold_seconds = 3600, storage_format = V2, store_row_column = false, light_schema_change = true, enable_unique_key_merge_on_write = false, in_memory = false, file_cache_ttl_seconds = 0, group_commit_data_bytes = 134217728, compaction_policy = size_based, _auto_bucket = false, binlog.max_history_nums = 9223372036854775807, time_series_compaction_file_count_threshold = 2000, skip_write_index_on_load = false, disable_auto_compaction = false, time_series_compaction_goal_size_mbytes = 1024, storage_medium = HDD, enable_single_replica_compaction = false, compression = LZ4F, binlog.max_bytes = 9223372036854775807 *************************** 3. row *************************** TABLE_NAME: randomtable TABLE_CATALOG: internal TABLE_SCHEMA: test_table_options_db DISTRIBUTE_KEY: RANDOM DISTRIBUTE_TYPE: RANDOM BUCKETS_NUM: 16 PARTITION_NUM: 1 PROPERTIES: min_load_replica_num = -1, data_sort.col_num = 3, group_commit_interval_ms = 10000, data_sort.sort_type = LEXICAL, is_being_synced = false, binlog.enable = false, binlog.ttl_seconds = 86400, inverted_index_storage_format = V1, time_series_compaction_empty_rowsets_threshold = 5, default.replication_allocation = tag.location.default: 1, time_series_compaction_level_threshold = 1, time_series_compaction_time_threshold_seconds = 3600, storage_format = V2, store_row_column = false, light_schema_change = true, enable_unique_key_merge_on_write = false, in_memory = false, file_cache_ttl_seconds = 0, group_commit_data_bytes = 134217728, compaction_policy = size_based, _auto_bucket = false, binlog.max_history_nums = 9223372036854775807, time_series_compaction_file_count_threshold = 2000, skip_write_index_on_load = false, disable_auto_compaction = false, time_series_compaction_goal_size_mbytes = 1024, storage_medium = HDD, enable_single_replica_compaction = false, compression = LZ4F, binlog.max_bytes = 9223372036854775807 3 rows in set (0.03 sec) ## Further comments If this is a relatively large or complex change, kick off the discussion at [[email protected]](mailto:[email protected]) by explaining why you chose the solution you did and what alternatives you considered, etc... --------- Co-authored-by: Gavin Chou <[email protected]> Co-authored-by: Xinyi Zou <[email protected]> --- be/src/exec/schema_scanner.cpp | 3 + .../schema_table_options_scanner.cpp | 134 +++++++++++++++++ .../schema_scanner/schema_table_options_scanner.h | 52 +++++++ .../org/apache/doris/analysis/SchemaTableType.java | 5 +- .../java/org/apache/doris/catalog/OlapTable.java | 14 ++ .../java/org/apache/doris/catalog/SchemaTable.java | 13 ++ .../org/apache/doris/catalog/TableProperty.java | 11 ++ .../doris/tablefunction/MetadataGenerator.java | 91 ++++++++++++ .../doris/datasource/RefreshCatalogTest.java | 5 +- gensrc/thrift/Descriptors.thrift | 3 +- gensrc/thrift/FrontendService.thrift | 1 + .../jdbc/test_mariadb_jdbc_catalog.out | 1 + .../jdbc/test_mysql_jdbc_catalog.out | 1 + .../jdbc/test_mysql_jdbc_catalog_nereids.out | 1 + .../jdbc/test_mysql_jdbc_driver5_catalog.out | 1 + .../data/query_p0/system/test_table_options.out | 9 ++ .../query_p0/system/test_table_options.groovy | 158 +++++++++++++++++++++ 17 files changed, 498 insertions(+), 5 deletions(-) diff --git a/be/src/exec/schema_scanner.cpp b/be/src/exec/schema_scanner.cpp index c233c6f83fd..5f39ff6657d 100644 --- a/be/src/exec/schema_scanner.cpp +++ b/be/src/exec/schema_scanner.cpp @@ -41,6 +41,7 @@ #include "exec/schema_scanner/schema_rowsets_scanner.h" #include "exec/schema_scanner/schema_schema_privileges_scanner.h" #include "exec/schema_scanner/schema_schemata_scanner.h" +#include "exec/schema_scanner/schema_table_options_scanner.h" #include "exec/schema_scanner/schema_table_privileges_scanner.h" #include "exec/schema_scanner/schema_tables_scanner.h" #include "exec/schema_scanner/schema_user_privileges_scanner.h" @@ -170,6 +171,8 @@ std::unique_ptr<SchemaScanner> SchemaScanner::create(TSchemaTableType::type type return SchemaUserScanner::create_unique(); case TSchemaTableType::SCH_WORKLOAD_POLICY: return SchemaWorkloadSchedulePolicyScanner::create_unique(); + case TSchemaTableType::SCH_TABLE_OPTIONS: + return SchemaTableOptionsScanner::create_unique(); default: return SchemaDummyScanner::create_unique(); break; diff --git a/be/src/exec/schema_scanner/schema_table_options_scanner.cpp b/be/src/exec/schema_scanner/schema_table_options_scanner.cpp new file mode 100644 index 00000000000..604da59b637 --- /dev/null +++ b/be/src/exec/schema_scanner/schema_table_options_scanner.cpp @@ -0,0 +1,134 @@ +// 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. + +#include "exec/schema_scanner/schema_table_options_scanner.h" + +#include "runtime/client_cache.h" +#include "runtime/exec_env.h" +#include "runtime/runtime_state.h" +#include "util/thrift_rpc_helper.h" +#include "vec/common/string_ref.h" +#include "vec/core/block.h" +#include "vec/data_types/data_type_factory.hpp" + +namespace doris { +std::vector<SchemaScanner::ColumnDesc> SchemaTableOptionsScanner::_s_tbls_columns = { + {"TABLE_NAME", TYPE_VARCHAR, sizeof(StringRef), true}, + {"TABLE_CATALOG", TYPE_VARCHAR, sizeof(StringRef), true}, + {"TABLE_SCHEMA", TYPE_VARCHAR, sizeof(StringRef), true}, + {"TABLE_MODEL", TYPE_STRING, sizeof(StringRef), true}, + {"TABLE_MODEL_KEY", TYPE_STRING, sizeof(StringRef), true}, + {"DISTRIBUTE_KEY", TYPE_STRING, sizeof(StringRef), true}, + {"DISTRIBUTE_TYPE", TYPE_STRING, sizeof(StringRef), true}, + {"BUCKETS_NUM", TYPE_INT, sizeof(int32_t), true}, + {"PARTITION_NUM", TYPE_INT, sizeof(int32_t), true}, + {"PROPERTIES", TYPE_STRING, sizeof(StringRef), true}, +}; + +SchemaTableOptionsScanner::SchemaTableOptionsScanner() + : SchemaScanner(_s_tbls_columns, TSchemaTableType::SCH_TABLE_OPTIONS) {} + +Status SchemaTableOptionsScanner::start(RuntimeState* state) { + _block_rows_limit = state->batch_size(); + _rpc_timeout_ms = state->execution_timeout() * 1000; + return Status::OK(); +} + +Status SchemaTableOptionsScanner::get_block_from_fe() { + TNetworkAddress master_addr = ExecEnv::GetInstance()->master_info()->network_address; + + TSchemaTableRequestParams schema_table_request_params; + for (int i = 0; i < _s_tbls_columns.size(); i++) { + schema_table_request_params.__isset.columns_name = true; + schema_table_request_params.columns_name.emplace_back(_s_tbls_columns[i].name); + } + schema_table_request_params.__set_current_user_ident(*_param->common_param->current_user_ident); + + TFetchSchemaTableDataRequest request; + request.__set_schema_table_name(TSchemaTableName::TABLE_OPTIONS); + request.__set_schema_table_params(schema_table_request_params); + + TFetchSchemaTableDataResult result; + + RETURN_IF_ERROR(ThriftRpcHelper::rpc<FrontendServiceClient>( + master_addr.hostname, master_addr.port, + [&request, &result](FrontendServiceConnection& client) { + client->fetchSchemaTableData(result, request); + }, + _rpc_timeout_ms)); + + Status status(Status::create(result.status)); + if (!status.ok()) { + LOG(WARNING) << "fetch table options from FE failed, errmsg=" << status; + return status; + } + std::vector<TRow> result_data = result.data_batch; + + _tableoptions_block = vectorized::Block::create_unique(); + for (int i = 0; i < _s_tbls_columns.size(); ++i) { + TypeDescriptor descriptor(_s_tbls_columns[i].type); + auto data_type = vectorized::DataTypeFactory::instance().create_data_type(descriptor, true); + _tableoptions_block->insert(vectorized::ColumnWithTypeAndName( + data_type->create_column(), data_type, _s_tbls_columns[i].name)); + } + _tableoptions_block->reserve(_block_rows_limit); + if (result_data.size() > 0) { + int col_size = result_data[0].column_value.size(); + if (col_size != _s_tbls_columns.size()) { + return Status::InternalError<false>("table options schema is not match for FE and BE"); + } + } + + for (int i = 0; i < result_data.size(); i++) { + TRow row = result_data[i]; + for (int j = 0; j < _s_tbls_columns.size(); j++) { + RETURN_IF_ERROR(insert_block_column(row.column_value[j], j, _tableoptions_block.get(), + _s_tbls_columns[j].type)); + } + } + return Status::OK(); +} + +Status SchemaTableOptionsScanner::get_next_block(vectorized::Block* block, bool* eos) { + if (!_is_init) { + return Status::InternalError("Used before initialized."); + } + + if (nullptr == block || nullptr == eos) { + return Status::InternalError("input pointer is nullptr."); + } + + if (_tableoptions_block == nullptr) { + RETURN_IF_ERROR(get_block_from_fe()); + _total_rows = _tableoptions_block->rows(); + } + + if (_row_idx == _total_rows) { + *eos = true; + return Status::OK(); + } + + int current_batch_rows = std::min(_block_rows_limit, _total_rows - _row_idx); + vectorized::MutableBlock mblock = vectorized::MutableBlock::build_mutable_block(block); + RETURN_IF_ERROR(mblock.add_rows(_tableoptions_block.get(), _row_idx, current_batch_rows)); + _row_idx += current_batch_rows; + + *eos = _row_idx == _total_rows; + return Status::OK(); +} + +} // namespace doris diff --git a/be/src/exec/schema_scanner/schema_table_options_scanner.h b/be/src/exec/schema_scanner/schema_table_options_scanner.h new file mode 100644 index 00000000000..d40f1b73c63 --- /dev/null +++ b/be/src/exec/schema_scanner/schema_table_options_scanner.h @@ -0,0 +1,52 @@ +// 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. + +#pragma once + +#include <vector> + +#include "common/status.h" +#include "exec/schema_scanner.h" + +namespace doris { +class RuntimeState; +namespace vectorized { +class Block; +} // namespace vectorized + +class SchemaTableOptionsScanner : public SchemaScanner { + ENABLE_FACTORY_CREATOR(SchemaTableOptionsScanner); + +public: + SchemaTableOptionsScanner(); + ~SchemaTableOptionsScanner() override = default; + + Status start(RuntimeState* state) override; + Status get_next_block(vectorized::Block* block, bool* eos) override; + + static std::vector<SchemaScanner::ColumnDesc> _s_tbls_columns; + +private: + Status get_block_from_fe(); + + int _block_rows_limit = 4096; + int _row_idx = 0; + int _total_rows = 0; + std::unique_ptr<vectorized::Block> _tableoptions_block = nullptr; + int _rpc_timeout_ms = 3000; +}; +}; // namespace doris diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/SchemaTableType.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/SchemaTableType.java index 8ec426cbf5e..988953ed4cb 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/analysis/SchemaTableType.java +++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/SchemaTableType.java @@ -75,9 +75,10 @@ public enum SchemaTableType { SCH_WORKLOAD_GROUPS("WORKLOAD_GROUPS", "WORKLOAD_GROUPS", TSchemaTableType.SCH_WORKLOAD_GROUPS), SCHE_USER("user", "user", TSchemaTableType.SCH_USER), SCH_PROCS_PRIV("procs_priv", "procs_priv", TSchemaTableType.SCH_PROCS_PRIV), - SCH_WORKLOAD_POLICY("WORKLOAD_POLICY", "WORKLOAD_POLICY", - TSchemaTableType.SCH_WORKLOAD_POLICY); + TSchemaTableType.SCH_WORKLOAD_POLICY), + SCH_TABLE_OPTIONS("TABLE_OPTIONS", "TABLE_OPTIONS", + TSchemaTableType.SCH_TABLE_OPTIONS); private static final String dbName = "INFORMATION_SCHEMA"; private static SelectList fullSelectLists; diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/OlapTable.java b/fe/fe-core/src/main/java/org/apache/doris/catalog/OlapTable.java index 4e9218df817..a223d72bc5e 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/catalog/OlapTable.java +++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/OlapTable.java @@ -1995,6 +1995,20 @@ public class OlapTable extends Table implements MTMVRelatedTableIf { return keysNum; } + public String getKeyColAsString() { + StringBuilder str = new StringBuilder(); + str.append(""); + for (Column column : getBaseSchema()) { + if (column.isKey()) { + if (str.length() != 0) { + str.append(","); + } + str.append(column.getName()); + } + } + return str.toString(); + } + public boolean convertHashDistributionToRandomDistribution() { boolean hasChanged = false; if (defaultDistributionInfo.getType() == DistributionInfoType.HASH) { diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/SchemaTable.java b/fe/fe-core/src/main/java/org/apache/doris/catalog/SchemaTable.java index 88349942719..3d57f922683 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/catalog/SchemaTable.java +++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/SchemaTable.java @@ -511,6 +511,19 @@ public class SchemaTable extends Table { .column("VERSION", ScalarType.createType(PrimitiveType.INT)) .column("WORKLOAD_GROUP", ScalarType.createStringType()) .build())) + .put("table_options", + new SchemaTable(SystemIdGenerator.getNextId(), "table_options", TableType.SCHEMA, + builder().column("TABLE_NAME", ScalarType.createVarchar(NAME_CHAR_LEN)) + .column("TABLE_CATALOG", ScalarType.createVarchar(NAME_CHAR_LEN)) + .column("TABLE_SCHEMA", ScalarType.createVarchar(NAME_CHAR_LEN)) + .column("TABLE_MODEL", ScalarType.createStringType()) + .column("TABLE_MODEL_KEY", ScalarType.createStringType()) + .column("DISTRIBUTE_KEY", ScalarType.createStringType()) + .column("DISTRIBUTE_TYPE", ScalarType.createStringType()) + .column("BUCKETS_NUM", ScalarType.createType(PrimitiveType.INT)) + .column("PARTITION_NUM", ScalarType.createType(PrimitiveType.INT)) + .column("PROPERTIES", ScalarType.createStringType()) + .build())) .build(); protected SchemaTable(long id, String name, TableType type, List<Column> baseSchema) { diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/TableProperty.java b/fe/fe-core/src/main/java/org/apache/doris/catalog/TableProperty.java index 66d6a5291a6..8f010eead54 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/catalog/TableProperty.java +++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/TableProperty.java @@ -30,6 +30,8 @@ import org.apache.doris.thrift.TInvertedIndexStorageFormat; import org.apache.doris.thrift.TStorageFormat; import org.apache.doris.thrift.TStorageMedium; +import com.fasterxml.jackson.core.JsonProcessingException; +import com.fasterxml.jackson.databind.ObjectMapper; import com.google.common.base.Strings; import com.google.common.collect.Maps; import com.google.gson.annotations.SerializedName; @@ -663,4 +665,13 @@ public class TableProperty implements Writable { public void setStorageVaultName(String storageVaultName) { properties.put(PropertyAnalyzer.PROPERTIES_STORAGE_VAULT_NAME, storageVaultName); } + + public String getPropertiesString() throws IOException { + ObjectMapper objectMapper = new ObjectMapper(); + try { + return objectMapper.writeValueAsString(properties); + } catch (JsonProcessingException e) { + throw new IOException(e); + } + } } diff --git a/fe/fe-core/src/main/java/org/apache/doris/tablefunction/MetadataGenerator.java b/fe/fe-core/src/main/java/org/apache/doris/tablefunction/MetadataGenerator.java index 42b76d3c057..b3ed195a935 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/tablefunction/MetadataGenerator.java +++ b/fe/fe-core/src/main/java/org/apache/doris/tablefunction/MetadataGenerator.java @@ -20,10 +20,16 @@ package org.apache.doris.tablefunction; import org.apache.doris.analysis.UserIdentity; import org.apache.doris.catalog.Column; import org.apache.doris.catalog.DatabaseIf; +import org.apache.doris.catalog.DistributionInfo; +import org.apache.doris.catalog.DistributionInfo.DistributionInfoType; import org.apache.doris.catalog.Env; +import org.apache.doris.catalog.HashDistributionInfo; import org.apache.doris.catalog.MTMV; +import org.apache.doris.catalog.OlapTable; import org.apache.doris.catalog.SchemaTable; import org.apache.doris.catalog.Table; +import org.apache.doris.catalog.TableIf; +import org.apache.doris.catalog.TableProperty; import org.apache.doris.common.AnalysisException; import org.apache.doris.common.ClientPool; import org.apache.doris.common.Pair; @@ -80,6 +86,7 @@ import org.apache.logging.log4j.Logger; import org.apache.thrift.TException; import org.jetbrains.annotations.NotNull; +import java.io.IOException; import java.text.SimpleDateFormat; import java.time.Instant; import java.time.LocalDateTime; @@ -100,6 +107,8 @@ public class MetadataGenerator { private static final ImmutableMap<String, Integer> WORKLOAD_SCHED_POLICY_COLUMN_TO_INDEX; + private static final ImmutableMap<String, Integer> TABLE_OPTIONS_COLUMN_TO_INDEX; + static { ImmutableMap.Builder<String, Integer> activeQueriesbuilder = new ImmutableMap.Builder(); List<Column> activeQueriesColList = SchemaTable.TABLE_MAP.get("active_queries").getFullSchema(); @@ -127,6 +136,12 @@ public class MetadataGenerator { } WORKLOAD_SCHED_POLICY_COLUMN_TO_INDEX = policyBuilder.build(); + ImmutableMap.Builder<String, Integer> optionBuilder = new ImmutableMap.Builder(); + List<Column> optionColList = SchemaTable.TABLE_MAP.get("table_options").getFullSchema(); + for (int i = 0; i < optionColList.size(); i++) { + optionBuilder.put(optionColList.get(i).getName().toLowerCase(), i); + } + TABLE_OPTIONS_COLUMN_TO_INDEX = optionBuilder.build(); } public static TFetchSchemaTableDataResult getMetadataTable(TFetchSchemaTableDataRequest request) throws TException { @@ -203,6 +218,10 @@ public class MetadataGenerator { result = workloadSchedPolicyMetadataResult(schemaTableParams); columnIndex = WORKLOAD_SCHED_POLICY_COLUMN_TO_INDEX; break; + case TABLE_OPTIONS: + result = tableOptionsMetadataResult(schemaTableParams); + columnIndex = TABLE_OPTIONS_COLUMN_TO_INDEX; + break; default: return errorResult("invalid schema table name."); } @@ -873,4 +892,76 @@ public class MetadataGenerator { result.setStatus(new TStatus(TStatusCode.OK)); return result; } + + private static TFetchSchemaTableDataResult tableOptionsMetadataResult(TSchemaTableRequestParams params) { + if (!params.isSetCurrentUserIdent()) { + return errorResult("current user ident is not set."); + } + + TFetchSchemaTableDataResult result = new TFetchSchemaTableDataResult(); + List<TRow> dataBatch = Lists.newArrayList(); + List<Long> catalogIds = Env.getCurrentEnv().getCatalogMgr().getCatalogIds(); + for (Long catalogId : catalogIds) { + CatalogIf catalog = Env.getCurrentEnv().getCatalogMgr().getCatalog(catalogId); + List<Long> dbIds = catalog.getDbIds(); + for (Long dbId : dbIds) { + DatabaseIf database = catalog.getDbNullable(dbId); + List<TableIf> tables = database.getTables(); + for (TableIf table : tables) { + if (!(table instanceof OlapTable)) { + continue; + } + OlapTable olapTable = (OlapTable) table; + TRow trow = new TRow(); + trow.addToColumnValue(new TCell().setStringVal(table.getName())); // TABLE_NAME + trow.addToColumnValue(new TCell().setStringVal(catalog.getName())); // TABLE_CATALOG + trow.addToColumnValue(new TCell().setStringVal(database.getFullName())); // TABLE_SCHEMA + trow.addToColumnValue( + new TCell().setStringVal(olapTable.getKeysType().toMetadata())); //TABLE_MODEL + trow.addToColumnValue( + new TCell().setStringVal(olapTable.getKeyColAsString())); // key columTypes + + DistributionInfo distributionInfo = olapTable.getDefaultDistributionInfo(); + if (distributionInfo.getType() == DistributionInfoType.HASH) { + HashDistributionInfo hashDistributionInfo = (HashDistributionInfo) distributionInfo; + List<Column> distributionColumns = hashDistributionInfo.getDistributionColumns(); + StringBuilder distributeKey = new StringBuilder(); + for (Column c : distributionColumns) { + if (distributeKey.length() != 0) { + distributeKey.append(","); + } + distributeKey.append(c.getName()); + } + if (distributeKey.length() == 0) { + trow.addToColumnValue(new TCell().setStringVal("")); + } else { + trow.addToColumnValue( + new TCell().setStringVal(distributeKey.toString())); + } + trow.addToColumnValue(new TCell().setStringVal("HASH")); // DISTRIBUTE_TYPE + } else { + trow.addToColumnValue(new TCell().setStringVal("RANDOM")); // DISTRIBUTE_KEY + trow.addToColumnValue(new TCell().setStringVal("RANDOM")); // DISTRIBUTE_TYPE + } + trow.addToColumnValue(new TCell().setIntVal(distributionInfo.getBucketNum())); // BUCKETS_NUM + trow.addToColumnValue(new TCell().setIntVal(olapTable.getPartitionNum())); // PARTITION_NUM + TableProperty property = olapTable.getTableProperty(); + if (property == null) { + trow.addToColumnValue(new TCell().setStringVal("")); // PROPERTIES + } else { + try { + trow.addToColumnValue( + new TCell().setStringVal(property.getPropertiesString())); // PROPERTIES + } catch (IOException e) { + return errorResult(e.getMessage()); + } + } + dataBatch.add(trow); + } // for table + } // for db + } // for catalog + result.setDataBatch(dataBatch); + result.setStatus(new TStatus(TStatusCode.OK)); + return result; + } } diff --git a/fe/fe-core/src/test/java/org/apache/doris/datasource/RefreshCatalogTest.java b/fe/fe-core/src/test/java/org/apache/doris/datasource/RefreshCatalogTest.java index f64e6523ebf..380b1d71354 100644 --- a/fe/fe-core/src/test/java/org/apache/doris/datasource/RefreshCatalogTest.java +++ b/fe/fe-core/src/test/java/org/apache/doris/datasource/RefreshCatalogTest.java @@ -25,6 +25,7 @@ import org.apache.doris.catalog.Env; import org.apache.doris.catalog.InfoSchemaDb; import org.apache.doris.catalog.MysqlDb; import org.apache.doris.catalog.PrimitiveType; +import org.apache.doris.catalog.SchemaTable; import org.apache.doris.common.FeConstants; import org.apache.doris.datasource.infoschema.ExternalInfoSchemaDatabase; import org.apache.doris.datasource.infoschema.ExternalMysqlDatabase; @@ -103,7 +104,7 @@ public class RefreshCatalogTest extends TestWithFeService { List<String> dbNames2 = test1.getDbNames(); Assertions.assertEquals(5, dbNames2.size()); ExternalInfoSchemaDatabase infoDb = (ExternalInfoSchemaDatabase) test1.getDb(InfoSchemaDb.DATABASE_NAME).get(); - Assertions.assertEquals(32, infoDb.getTables().size()); + Assertions.assertEquals(SchemaTable.TABLE_MAP.size(), infoDb.getTables().size()); TestExternalDatabase testDb = (TestExternalDatabase) test1.getDb("db1").get(); Assertions.assertEquals(2, testDb.getTables().size()); ExternalMysqlDatabase mysqlDb = (ExternalMysqlDatabase) test1.getDb(MysqlDb.DATABASE_NAME).get(); @@ -114,7 +115,7 @@ public class RefreshCatalogTest extends TestWithFeService { CatalogMgr mgr2 = GsonUtils.GSON.fromJson(json, CatalogMgr.class); test1 = mgr2.getCatalog("test1"); infoDb = (ExternalInfoSchemaDatabase) test1.getDb(InfoSchemaDb.DATABASE_NAME).get(); - Assertions.assertEquals(32, infoDb.getTables().size()); + Assertions.assertEquals(SchemaTable.TABLE_MAP.size(), infoDb.getTables().size()); testDb = (TestExternalDatabase) test1.getDb("db1").get(); Assertions.assertEquals(2, testDb.getTables().size()); mysqlDb = (ExternalMysqlDatabase) test1.getDb(MysqlDb.DATABASE_NAME).get(); diff --git a/gensrc/thrift/Descriptors.thrift b/gensrc/thrift/Descriptors.thrift index bd77b875813..9d147c84174 100644 --- a/gensrc/thrift/Descriptors.thrift +++ b/gensrc/thrift/Descriptors.thrift @@ -131,7 +131,8 @@ enum TSchemaTableType { SCH_WORKLOAD_GROUPS, SCH_USER, SCH_PROCS_PRIV, - SCH_WORKLOAD_POLICY; + SCH_WORKLOAD_POLICY, + SCH_TABLE_OPTIONS; } enum THdfsCompression { diff --git a/gensrc/thrift/FrontendService.thrift b/gensrc/thrift/FrontendService.thrift index 8fff907eda2..556443a3d09 100644 --- a/gensrc/thrift/FrontendService.thrift +++ b/gensrc/thrift/FrontendService.thrift @@ -961,6 +961,7 @@ enum TSchemaTableName { WORKLOAD_GROUPS = 3, // db information_schema's table ROUTINES_INFO = 4, // db information_schema's table WORKLOAD_SCHEDULE_POLICY = 5, + TABLE_OPTIONS = 6, } struct TMetadataTableRequestParams { diff --git a/regression-test/data/external_table_p0/jdbc/test_mariadb_jdbc_catalog.out b/regression-test/data/external_table_p0/jdbc/test_mariadb_jdbc_catalog.out index d8bfffd9125..0eb3e6ed9eb 100644 --- a/regression-test/data/external_table_p0/jdbc/test_mariadb_jdbc_catalog.out +++ b/regression-test/data/external_table_p0/jdbc/test_mariadb_jdbc_catalog.out @@ -53,6 +53,7 @@ schemata session_variables statistics table_constraints +table_options table_privileges tables triggers diff --git a/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog.out b/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog.out index 3cc6a9a580e..981dbecd6fe 100644 --- a/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog.out +++ b/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog.out @@ -217,6 +217,7 @@ schemata session_variables statistics table_constraints +table_options table_privileges tables triggers diff --git a/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog_nereids.out b/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog_nereids.out index 22bc4e8279d..fb9bf51ad93 100644 --- a/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog_nereids.out +++ b/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog_nereids.out @@ -185,6 +185,7 @@ schemata session_variables statistics table_constraints +table_options table_privileges tables triggers diff --git a/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_driver5_catalog.out b/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_driver5_catalog.out index a451ecf9b91..318b0840f22 100644 --- a/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_driver5_catalog.out +++ b/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_driver5_catalog.out @@ -227,6 +227,7 @@ schemata session_variables statistics table_constraints +table_options table_privileges tables triggers diff --git a/regression-test/data/query_p0/system/test_table_options.out b/regression-test/data/query_p0/system/test_table_options.out new file mode 100644 index 00000000000..8700b8741dd --- /dev/null +++ b/regression-test/data/query_p0/system/test_table_options.out @@ -0,0 +1,9 @@ +-- This file is automatically generated. You should know what you did if you want to edit this +-- !select -- +aggregate_table internal test_table_options_db AGG user_id,date,city,age,sex user_id HASH 1 1 {"min_load_replica_num":"-1","data_sort.col_num":"5","group_commit_interval_ms":"10000","data_sort.sort_type":"LEXICAL","is_being_synced":"false","binlog.enable":"false","binlog.ttl_seconds":"86400","inverted_index_storage_format":"V1","time_series_compaction_empty_rowsets_threshold":"5","default.replication_allocation":"tag.location.default: 1","time_series_compaction_level_threshold":"1","time [...] +duplicate_table internal test_table_options_db DUP timestamp,type,error_code type HASH 1 1 {"min_load_replica_num":"-1","data_sort.col_num":"3","group_commit_interval_ms":"10000","data_sort.sort_type":"LEXICAL","is_being_synced":"false","binlog.enable":"false","binlog.ttl_seconds":"86400","inverted_index_storage_format":"V1","time_series_compaction_empty_rowsets_threshold":"5","default.replication_allocation":"tag.location.default: 1","time_series_compaction_level_threshold":"1","time_se [...] +listtable internal test_table_options_db AGG user_id,date,timestamp,city,age,sex user_id HASH 16 3 {"min_load_replica_num":"-1","data_sort.col_num":"6","group_commit_interval_ms":"10000","data_sort.sort_type":"LEXICAL","is_being_synced":"false","binlog.enable":"false","binlog.ttl_seconds":"86400","inverted_index_storage_format":"V1","time_series_compaction_empty_rowsets_threshold":"5","default.replication_allocation":"tag.location.default: 1","time_series_compaction_level_threshold":"1", [...] +randomtable internal test_table_options_db DUP user_id,date,timestamp RANDOM RANDOM 16 1 {"min_load_replica_num":"-1","data_sort.col_num":"3","group_commit_interval_ms":"10000","data_sort.sort_type":"LEXICAL","is_being_synced":"false","binlog.enable":"false","binlog.ttl_seconds":"86400","inverted_index_storage_format":"V1","time_series_compaction_empty_rowsets_threshold":"5","default.replication_allocation":"tag.location.default: 1","time_series_compaction_level_threshold":"1","time_seri [...] +rangetable internal test_table_options_db AGG user_id,date,timestamp,city,age,sex user_id HASH 8 3 {"min_load_replica_num":"-1","data_sort.col_num":"6","group_commit_interval_ms":"10000","data_sort.sort_type":"LEXICAL","is_being_synced":"false","binlog.enable":"false","binlog.ttl_seconds":"86400","inverted_index_storage_format":"V1","time_series_compaction_empty_rowsets_threshold":"5","default.replication_allocation":"tag.location.default: 1","time_series_compaction_level_threshold":"1", [...] +unique_table internal test_table_options_db UNI user_id,username user_id HASH 1 1 {"min_load_replica_num":"-1","data_sort.col_num":"2","group_commit_interval_ms":"10000","data_sort.sort_type":"LEXICAL","is_being_synced":"false","binlog.enable":"false","binlog.ttl_seconds":"86400","inverted_index_storage_format":"V1","time_series_compaction_empty_rowsets_threshold":"5","default.replication_allocation":"tag.location.default: 1","time_series_compaction_level_threshold":"1","time_series_comp [...] + diff --git a/regression-test/suites/query_p0/system/test_table_options.groovy b/regression-test/suites/query_p0/system/test_table_options.groovy new file mode 100644 index 00000000000..e53f2ba64e9 --- /dev/null +++ b/regression-test/suites/query_p0/system/test_table_options.groovy @@ -0,0 +1,158 @@ +// 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. + +// Add PL-SQL regression test notice: +// 1. JDBC does not support the execution of stored procedures that return results. You can only Into the execution +// results into a variable or write them into a table, because when multiple result sets are returned, JDBC needs +// to use the prepareCall statement to execute, otherwise the Statemnt of the returned result executes Finalize. +// Send EOF Packet will report an error; +// 2. The format of the result returned by Doris Statement is xxxx\n, xxxx\n, 2 rows affected (0.03 sec). +// PL-SQL uses Print to print variable values in an unformatted format, and JDBC cannot easily obtain them. Real results. +suite("test_table_options") { + def dbName = "test_table_options_db" + sql "drop database if exists ${dbName}" + sql "CREATE DATABASE IF NOT EXISTS ${dbName}" + sql "use ${dbName}" + + sql """ + CREATE TABLE IF NOT EXISTS rangetable + ( + `user_id` LARGEINT NOT NULL COMMENT "User id", + `date` DATE NOT NULL COMMENT "Data fill in date time", + `timestamp` DATETIME NOT NULL COMMENT "Timestamp of data being poured", + `city` VARCHAR(20) COMMENT "The city where the user is located", + `age` SMALLINT COMMENT "User age", + `sex` TINYINT COMMENT "User gender", + `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "User last visit time", + `cost` BIGINT SUM DEFAULT "0" COMMENT "Total user consumption", + `max_dwell_time` INT MAX DEFAULT "0" COMMENT "User maximum dwell time", + `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "User minimum dwell time" + ) + ENGINE=olap + AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`) + PARTITION BY RANGE(`date`) + ( + PARTITION `p201701` VALUES LESS THAN ("2017-02-01"), + PARTITION `p201702` VALUES LESS THAN ("2017-03-01"), + PARTITION `p201703` VALUES LESS THAN ("2017-04-01") + ) + DISTRIBUTED BY HASH(`user_id`) BUCKETS 8 + PROPERTIES + ( + "replication_num" = "1" + ); + """ + sql """ + CREATE TABLE IF NOT EXISTS listtable + ( + `user_id` LARGEINT NOT NULL COMMENT "User id", + `date` DATE NOT NULL COMMENT "Data fill in date time", + `timestamp` DATETIME NOT NULL COMMENT "Timestamp of data being poured", + `city` VARCHAR(20) COMMENT "The city where the user is located", + `age` SMALLINT COMMENT "User Age", + `sex` TINYINT COMMENT "User gender", + `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "User last visit time", + `cost` BIGINT SUM DEFAULT "0" COMMENT "Total user consumption", + `max_dwell_time` INT MAX DEFAULT "0" COMMENT "User maximum dwell time", + `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "User minimum dwell time" + ) + ENGINE=olap + AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`) + PARTITION BY LIST(`city`) + ( + PARTITION `p_cn` VALUES IN ("Beijing", "Shanghai", "Hong Kong"), + PARTITION `p_usa` VALUES IN ("New York", "San Francisco"), + PARTITION `p_jp` VALUES IN ("Tokyo") + ) + DISTRIBUTED BY HASH(`user_id`) BUCKETS 16 + PROPERTIES + ( + "replication_num" = "1" + ); + """ + sql """ + CREATE TABLE IF NOT EXISTS randomtable + ( + `user_id` LARGEINT NOT NULL COMMENT "User id", + `date` DATE NOT NULL COMMENT "Data fill in date time", + `timestamp` DATETIME NOT NULL COMMENT "Timestamp of data being poured", + `city` VARCHAR(20) COMMENT "The city where the user is located", + `age` SMALLINT COMMENT "User Age", + `sex` TINYINT COMMENT "User gender" + ) + ENGINE=olap + DISTRIBUTED BY RANDOM BUCKETS 16 + PROPERTIES + ( + "replication_num" = "1" + ); + """ + sql """ + CREATE TABLE IF NOT EXISTS aggregate_table + ( + `user_id` LARGEINT NOT NULL COMMENT "user id", + `date` DATE NOT NULL COMMENT "data import time", + `city` VARCHAR(20) COMMENT "city", + `age` SMALLINT COMMENT "age", + `sex` TINYINT COMMENT "gender", + `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "last visit date time", + `cost` BIGINT SUM DEFAULT "0" COMMENT "user total cost", + `max_dwell_time` INT MAX DEFAULT "0" COMMENT "user max dwell time", + `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "user min dwell time" + ) + AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`) + DISTRIBUTED BY HASH(`user_id`) BUCKETS 1 + PROPERTIES ( + "replication_allocation" = "tag.location.default: 1" + ); + """ + sql """ + CREATE TABLE IF NOT EXISTS unique_table + ( + `user_id` LARGEINT NOT NULL COMMENT "User ID", + `username` VARCHAR(50) NOT NULL COMMENT "Username", + `city` VARCHAR(20) COMMENT "User location city", + `age` SMALLINT COMMENT "User age", + `sex` TINYINT COMMENT "User gender", + `phone` LARGEINT COMMENT "User phone number", + `address` VARCHAR(500) COMMENT "User address", + `register_time` DATETIME COMMENT "User registration time" + ) + UNIQUE KEY(`user_id`, `username`) + DISTRIBUTED BY HASH(`user_id`) BUCKETS 1 + PROPERTIES ( + "replication_allocation" = "tag.location.default: 1" + ); + """ + sql """ + CREATE TABLE IF NOT EXISTS duplicate_table + ( + `timestamp` DATETIME NOT NULL COMMENT "Log time", + `type` INT NOT NULL COMMENT "Log type", + `error_code` INT COMMENT "Error code", + `error_msg` VARCHAR(1024) COMMENT "Error detail message", + `op_id` BIGINT COMMENT "Operator ID", + `op_time` DATETIME COMMENT "Operation time" + ) + DISTRIBUTED BY HASH(`type`) BUCKETS 1 + PROPERTIES ( + "replication_allocation" = "tag.location.default: 1" + ); + """ + qt_select """select * from information_schema.table_options where table_schema=\"${dbName}\" order by TABLE_NAME; """ + sql "drop database if exists ${dbName}" +} --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
