This is an automated email from the ASF dual-hosted git repository. morningman pushed a commit to branch branch-2.1-lakehouse in repository https://gitbox.apache.org/repos/asf/doris.git
commit f12a9f40b14d74758e47280f560884bee61bb858 Author: morningman <[email protected]> AuthorDate: Mon Feb 17 19:15:56 2025 +0800 [feature](sql-dialect)support convert hive view and presto view use sql convertor service #46308 --- .../doris/datasource/hive/HMSExternalTable.java | 50 +++++++- .../doris/nereids/parser/SqlDialectHelper.java | 66 +++++++++++ .../doris/nereids/rules/analysis/BindRelation.java | 5 +- .../java/org/apache/doris/qe/ConnectProcessor.java | 29 +---- .../datasource/hive/HMSExternalTableTest.java | 126 +++++++++++++++++++++ 5 files changed, 246 insertions(+), 30 deletions(-) diff --git a/fe/fe-core/src/main/java/org/apache/doris/datasource/hive/HMSExternalTable.java b/fe/fe-core/src/main/java/org/apache/doris/datasource/hive/HMSExternalTable.java index b056af3ebd6..6a29446cfd9 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/datasource/hive/HMSExternalTable.java +++ b/fe/fe-core/src/main/java/org/apache/doris/datasource/hive/HMSExternalTable.java @@ -64,6 +64,8 @@ import com.google.common.collect.BiMap; import com.google.common.collect.Lists; import com.google.common.collect.Maps; import com.google.common.collect.Sets; +import com.google.gson.Gson; +import com.google.gson.JsonObject; import org.apache.commons.collections.CollectionUtils; import org.apache.commons.collections.MapUtils; import org.apache.commons.lang3.StringUtils; @@ -82,7 +84,9 @@ import org.apache.logging.log4j.LogManager; import org.apache.logging.log4j.Logger; import java.io.IOException; +import java.nio.charset.StandardCharsets; import java.util.ArrayList; +import java.util.Base64; import java.util.Collection; import java.util.Collections; import java.util.HashMap; @@ -456,9 +460,51 @@ public class HMSExternalTable extends ExternalTable implements MTMVRelatedTableI public String getViewText() { String viewText = getViewExpandedText(); if (StringUtils.isNotEmpty(viewText)) { - return viewText; + if (!viewText.equals("/* Presto View */")) { + return viewText; + } + } + + String originalText = getViewOriginalText(); + return parseTrinoViewDefinition(originalText); + } + + /** + * Parse Trino/Presto view definition from the original text. + * The definition is stored in the format: /* Presto View: <base64-encoded-json> * / + * + * The base64 encoded JSON contains the following fields: + * { + * "originalSql": "SELECT * FROM employees", // The original SQL statement + * "catalog": "hive", // The data catalog name + * "schema": "mmc_hive", // The schema name + * ... + * } + * + * @param originalText The original view definition text + * @return The parsed SQL statement, or original text if parsing fails + */ + private String parseTrinoViewDefinition(String originalText) { + if (originalText == null || !originalText.contains("/* Presto View: ")) { + return originalText; + } + + try { + String base64String = originalText.substring( + originalText.indexOf("/* Presto View: ") + "/* Presto View: ".length(), + originalText.lastIndexOf(" */") + ).trim(); + byte[] decodedBytes = Base64.getDecoder().decode(base64String); + String decodedString = new String(decodedBytes, StandardCharsets.UTF_8); + JsonObject jsonObject = new Gson().fromJson(decodedString, JsonObject.class); + + if (jsonObject.has("originalSql")) { + return jsonObject.get("originalSql").getAsString(); + } + } catch (Exception e) { + LOG.warn("Decoding Presto view definition failed", e); } - return getViewOriginalText(); + return originalText; } public String getViewExpandedText() { diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/SqlDialectHelper.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/SqlDialectHelper.java new file mode 100644 index 00000000000..c959982e254 --- /dev/null +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/SqlDialectHelper.java @@ -0,0 +1,66 @@ +// 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. + +package org.apache.doris.nereids.parser; + +import org.apache.doris.catalog.Env; +import org.apache.doris.plugin.DialectConverterPlugin; +import org.apache.doris.plugin.PluginMgr; +import org.apache.doris.qe.SessionVariable; + +import org.apache.commons.lang3.StringUtils; +import org.apache.logging.log4j.LogManager; +import org.apache.logging.log4j.Logger; + +import java.util.List; +import javax.annotation.Nullable; + +/** + * Helper class for SQL dialect conversion. + */ +public class SqlDialectHelper { + private static final Logger LOG = LogManager.getLogger(SqlDialectHelper.class); + + /** + * Convert SQL statement based on current SQL dialect + * + * @param originStmt original SQL statement + * @param sessionVariable session variable containing dialect settings + * @return converted SQL statement, or original statement if conversion fails + */ + public static String convertSqlByDialect(String originStmt, SessionVariable sessionVariable) { + String convertedStmt = originStmt; + @Nullable Dialect sqlDialect = Dialect.getByName(sessionVariable.getSqlDialect()); + if (sqlDialect != null && sqlDialect != Dialect.DORIS) { + PluginMgr pluginMgr = Env.getCurrentEnv().getPluginMgr(); + List<DialectConverterPlugin> plugins = pluginMgr.getActiveDialectPluginList(sqlDialect); + for (DialectConverterPlugin plugin : plugins) { + try { + String convertedSql = plugin.convertSql(originStmt, sessionVariable); + if (StringUtils.isNotEmpty(convertedSql)) { + convertedStmt = convertedSql; + break; + } + } catch (Throwable throwable) { + LOG.warn("Convert sql with dialect {} failed, plugin: {}, sql: {}, use origin sql.", + sqlDialect, plugin.getClass().getSimpleName(), originStmt, throwable); + } + } + } + return convertedStmt; + } +} diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindRelation.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindRelation.java index d412348973f..a1ba0ad7f34 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindRelation.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindRelation.java @@ -46,6 +46,7 @@ import org.apache.doris.nereids.analyzer.UnboundResultSink; import org.apache.doris.nereids.exceptions.AnalysisException; import org.apache.doris.nereids.hint.LeadingHint; import org.apache.doris.nereids.parser.NereidsParser; +import org.apache.doris.nereids.parser.SqlDialectHelper; import org.apache.doris.nereids.pattern.MatchingContext; import org.apache.doris.nereids.properties.LogicalProperties; import org.apache.doris.nereids.properties.PhysicalProperties; @@ -451,11 +452,12 @@ public class BindRelation extends OneAnalysisRuleFactory { ConnectContext ctx = cascadesContext.getConnectContext(); String previousCatalog = ctx.getCurrentCatalog().getName(); String previousDb = ctx.getDatabase(); + String convertedSql = SqlDialectHelper.convertSqlByDialect(ddlSql, ctx.getSessionVariable()); // change catalog and db to hive catalog and db, so that we can parse and analyze the view sql in hive context. ctx.changeDefaultCatalog(hiveCatalog); ctx.setDatabase(hiveDb); try { - return parseAndAnalyzeView(table, ddlSql, cascadesContext); + return parseAndAnalyzeView(table, convertedSql, cascadesContext); } finally { // restore catalog and db in connect context ctx.changeDefaultCatalog(previousCatalog); @@ -515,4 +517,5 @@ public class BindRelation extends OneAnalysisRuleFactory { return part.getId(); }).collect(ImmutableList.toImmutableList()); } + } diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/ConnectProcessor.java b/fe/fe-core/src/main/java/org/apache/doris/qe/ConnectProcessor.java index 6e0f07ec3b8..a7c9a93a9f5 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/qe/ConnectProcessor.java +++ b/fe/fe-core/src/main/java/org/apache/doris/qe/ConnectProcessor.java @@ -57,14 +57,12 @@ import org.apache.doris.nereids.exceptions.NotSupportedException; import org.apache.doris.nereids.exceptions.ParseException; import org.apache.doris.nereids.glue.LogicalPlanAdapter; import org.apache.doris.nereids.minidump.MinidumpUtils; -import org.apache.doris.nereids.parser.Dialect; import org.apache.doris.nereids.parser.NereidsParser; +import org.apache.doris.nereids.parser.SqlDialectHelper; import org.apache.doris.nereids.stats.StatsErrorEstimator; import org.apache.doris.nereids.trees.plans.commands.ExplainCommand; import org.apache.doris.nereids.trees.plans.logical.LogicalPlan; import org.apache.doris.nereids.trees.plans.logical.LogicalSqlCache; -import org.apache.doris.plugin.DialectConverterPlugin; -import org.apache.doris.plugin.PluginMgr; import org.apache.doris.proto.Data; import org.apache.doris.qe.QueryState.MysqlStateType; import org.apache.doris.qe.cache.CacheAnalyzer; @@ -79,7 +77,6 @@ import com.google.common.collect.ImmutableList; import com.google.common.collect.Lists; import com.google.common.collect.Maps; import org.apache.commons.codec.digest.DigestUtils; -import org.apache.commons.lang3.StringUtils; import org.apache.logging.log4j.LogManager; import org.apache.logging.log4j.Logger; import org.apache.thrift.TException; @@ -92,7 +89,6 @@ import java.util.List; import java.util.Map; import java.util.Optional; import java.util.UUID; -import javax.annotation.Nullable; /** * Process one connection, the life cycle is the same as connection @@ -235,7 +231,7 @@ public abstract class ConnectProcessor { MetricRepo.COUNTER_REQUEST_ALL.increase(1L); } - String convertedStmt = convertOriginStmt(originStmt); + String convertedStmt = SqlDialectHelper.convertSqlByDialect(originStmt, ctx.getSessionVariable()); String sqlHash = DigestUtils.md5Hex(convertedStmt); ctx.setSqlHash(sqlHash); @@ -431,27 +427,6 @@ public abstract class ConnectProcessor { return null; } - private String convertOriginStmt(String originStmt) { - String convertedStmt = originStmt; - @Nullable Dialect sqlDialect = Dialect.getByName(ctx.getSessionVariable().getSqlDialect()); - if (sqlDialect != null && sqlDialect != Dialect.DORIS) { - PluginMgr pluginMgr = Env.getCurrentEnv().getPluginMgr(); - List<DialectConverterPlugin> plugins = pluginMgr.getActiveDialectPluginList(sqlDialect); - for (DialectConverterPlugin plugin : plugins) { - try { - String convertedSql = plugin.convertSql(originStmt, ctx.getSessionVariable()); - if (StringUtils.isNotEmpty(convertedSql)) { - convertedStmt = convertedSql; - break; - } - } catch (Throwable throwable) { - LOG.warn("Convert sql with dialect {} failed, plugin: {}, sql: {}, use origin sql.", - sqlDialect, plugin.getClass().getSimpleName(), originStmt, throwable); - } - } - } - return convertedStmt; - } // Use a handler for exception to avoid big try catch block which is a little hard to understand protected void handleQueryException(Throwable throwable, String origStmt, diff --git a/fe/fe-core/src/test/java/org/apache/doris/datasource/hive/HMSExternalTableTest.java b/fe/fe-core/src/test/java/org/apache/doris/datasource/hive/HMSExternalTableTest.java new file mode 100644 index 00000000000..bd29766f39e --- /dev/null +++ b/fe/fe-core/src/test/java/org/apache/doris/datasource/hive/HMSExternalTableTest.java @@ -0,0 +1,126 @@ +// 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. + +package org.apache.doris.datasource.hive; + +import mockit.Injectable; +import org.junit.jupiter.api.Assertions; +import org.junit.jupiter.api.BeforeEach; +import org.junit.jupiter.api.Test; + + +/** + * Test class for HMSExternalTable, focusing on view-related functionality + */ +public class HMSExternalTableTest { + private TestHMSExternalTable table; + private static final String TEST_VIEW_TEXT = "SELECT * FROM test_table"; + private static final String TEST_EXPANDED_VIEW = "/* Presto View */"; + + // Real example of a Presto View definition + private static final String PRESTO_VIEW_ORIGINAL = "/* Presto View: eyJvcmlnaW5hbFNxbCI6IlNFTEVDVFxuICBkZXBhcnRtZW50XG4sIGxlbmd0aChkZXBhcnRtZW50KSBkZXBhcnRtZW50X2xlbmd0aFxuLCBkYXRlX3RydW5jKCd5ZWFyJywgaGlyZV9kYXRlKSB5ZWFyXG5GUk9NXG4gIGVtcGxveWVlc1xuIiwiY2F0YWxvZyI6ImhpdmUiLCJzY2hlbWEiOiJtbWNfaGl2ZSIsImNvbHVtbnMiOlt7Im5hbWUiOiJkZXBhcnRtZW50IiwidHlwZSI6InZhcmNoYXIifSx7Im5hbWUiOiJkZXBhcnRtZW50X2xlbmd0aCIsInR5cGUiOiJiaWdpbnQifSx7Im5hbWUiOiJ5ZWFyIiwidHlwZSI6ImRhdGUifV0sIm93bmVyIjoidHJpbm8v [...] + + // Expected SQL query after decoding and parsing + private static final String EXPECTED_SQL = "SELECT\n department\n, length(department) department_length\n, date_trunc('year', hire_date) year\nFROM\n employees\n"; + + @Injectable + private HMSExternalCatalog mockCatalog; + + private HMSExternalDatabase mockDb; + + @BeforeEach + public void setUp() { + // Create a mock database with minimal required functionality + mockDb = new HMSExternalDatabase(mockCatalog, 1L, "test_db", "remote_test_db") { + @Override + public String getFullName() { + return "test_catalog.test_db"; + } + }; + + table = new TestHMSExternalTable(mockCatalog, mockDb); + } + + @Test + public void testGetViewText_Normal() { + // Test regular view text retrieval + table.setViewOriginalText(TEST_VIEW_TEXT); + table.setViewExpandedText(TEST_VIEW_TEXT); + Assertions.assertEquals(TEST_VIEW_TEXT, table.getViewText()); + } + + @Test + public void testGetViewText_PrestoView() { + // Test Presto view parsing including base64 decode and JSON extraction + table.setViewOriginalText(PRESTO_VIEW_ORIGINAL); + table.setViewExpandedText(TEST_EXPANDED_VIEW); + Assertions.assertEquals(EXPECTED_SQL, table.getViewText()); + } + + @Test + public void testGetViewText_InvalidPrestoView() { + // Test handling of invalid Presto view definition + String invalidPrestoView = "/* Presto View: invalid_base64_content */"; + table.setViewOriginalText(invalidPrestoView); + table.setViewExpandedText(TEST_EXPANDED_VIEW); + Assertions.assertEquals(invalidPrestoView, table.getViewText()); + } + + @Test + public void testGetViewText_EmptyExpandedView() { + // Test handling of empty expanded view text + table.setViewOriginalText(TEST_VIEW_TEXT); + table.setViewExpandedText(""); + Assertions.assertEquals(TEST_VIEW_TEXT, table.getViewText()); + } + + /** + * Test implementation of HMSExternalTable that allows setting view texts + * Uses parent's getViewText() implementation for actual testing + */ + private static class TestHMSExternalTable extends HMSExternalTable { + private String viewExpandedText; + private String viewOriginalText; + + public TestHMSExternalTable(HMSExternalCatalog catalog, HMSExternalDatabase db) { + super(1L, "test_table", "test_table", catalog, db); + } + + @Override + public String getViewExpandedText() { + return viewExpandedText; + } + + @Override + public String getViewOriginalText() { + return viewOriginalText; + } + + public void setViewExpandedText(String viewExpandedText) { + this.viewExpandedText = viewExpandedText; + } + + public void setViewOriginalText(String viewOriginalText) { + this.viewOriginalText = viewOriginalText; + } + + @Override + protected synchronized void makeSureInitialized() { + this.objectCreated = true; + } + } +} --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
