You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by fj...@apache.org on 2018/07/20 18:21:07 UTC
[incubator-druid] branch master updated: Add concat and textcat SQL
functions (#6005)
This is an automated email from the ASF dual-hosted git repository.
fjy pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-druid.git
The following commit(s) were added to refs/heads/master by this push:
new efab3b0 Add concat and textcat SQL functions (#6005)
efab3b0 is described below
commit efab3b01609787d2f1bd026608d6006d8333793a
Author: Jonathan Wei <jo...@users.noreply.github.com>
AuthorDate: Fri Jul 20 11:21:04 2018 -0700
Add concat and textcat SQL functions (#6005)
---
docs/content/querying/sql.md | 2 +
.../builtin/ConcatOperatorConversion.java | 72 ++++++++++++++
.../builtin/TextcatOperatorConversion.java | 66 +++++++++++++
.../sql/calcite/planner/DruidOperatorTable.java | 4 +
.../io/druid/sql/calcite/CalciteQueryTest.java | 108 +++++++++++++++++++++
5 files changed, 252 insertions(+)
diff --git a/docs/content/querying/sql.md b/docs/content/querying/sql.md
index 314a3ed..ffd655a 100644
--- a/docs/content/querying/sql.md
+++ b/docs/content/querying/sql.md
@@ -125,6 +125,8 @@ String functions accept strings, and return a type appropriate to the function.
|Function|Notes|
|--------|-----|
|`x \|\| y`|Concat strings x and y.|
+|`CONCAT(expr, expr...)`|Concats a list of expressions.|
+|`TEXTCAT(expr, expr)`|Two argument version of CONCAT.|
|`LENGTH(expr)`|Length of expr in UTF-16 code units.|
|`CHAR_LENGTH(expr)`|Synonym for `LENGTH`.|
|`CHARACTER_LENGTH(expr)`|Synonym for `LENGTH`.|
diff --git a/sql/src/main/java/io/druid/sql/calcite/expression/builtin/ConcatOperatorConversion.java b/sql/src/main/java/io/druid/sql/calcite/expression/builtin/ConcatOperatorConversion.java
new file mode 100644
index 0000000..21268b9
--- /dev/null
+++ b/sql/src/main/java/io/druid/sql/calcite/expression/builtin/ConcatOperatorConversion.java
@@ -0,0 +1,72 @@
+/*
+ * 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 io.druid.sql.calcite.expression.builtin;
+
+import io.druid.sql.calcite.expression.DruidExpression;
+import io.druid.sql.calcite.expression.OperatorConversions;
+import io.druid.sql.calcite.expression.SqlOperatorConversion;
+import io.druid.sql.calcite.planner.Calcites;
+import io.druid.sql.calcite.planner.PlannerContext;
+import io.druid.sql.calcite.table.RowSignature;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.sql.SqlFunction;
+import org.apache.calcite.sql.SqlFunctionCategory;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.type.SqlTypeName;
+
+public class ConcatOperatorConversion implements SqlOperatorConversion
+{
+ private static final SqlFunction SQL_FUNCTION = new SqlFunction(
+ "CONCAT",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.explicit(
+ factory -> Calcites.createSqlType(factory, SqlTypeName.VARCHAR)
+ ),
+ null,
+ OperandTypes.SAME_VARIADIC,
+ SqlFunctionCategory.STRING
+ );
+
+ @Override
+ public SqlFunction calciteOperator()
+ {
+ return SQL_FUNCTION;
+ }
+
+ @Override
+ public DruidExpression toDruidExpression(
+ final PlannerContext plannerContext,
+ final RowSignature rowSignature,
+ final RexNode rexNode
+ )
+ {
+ return OperatorConversions.convertCall(
+ plannerContext,
+ rowSignature,
+ rexNode,
+ druidExpressions -> DruidExpression.of(
+ null,
+ DruidExpression.functionCall("concat", druidExpressions)
+ )
+ );
+ }
+}
diff --git a/sql/src/main/java/io/druid/sql/calcite/expression/builtin/TextcatOperatorConversion.java b/sql/src/main/java/io/druid/sql/calcite/expression/builtin/TextcatOperatorConversion.java
new file mode 100644
index 0000000..a34b57f
--- /dev/null
+++ b/sql/src/main/java/io/druid/sql/calcite/expression/builtin/TextcatOperatorConversion.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 io.druid.sql.calcite.expression.builtin;
+
+import io.druid.sql.calcite.expression.DruidExpression;
+import io.druid.sql.calcite.expression.OperatorConversions;
+import io.druid.sql.calcite.expression.SqlOperatorConversion;
+import io.druid.sql.calcite.planner.PlannerContext;
+import io.druid.sql.calcite.table.RowSignature;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.sql.SqlFunction;
+import org.apache.calcite.sql.SqlFunctionCategory;
+import org.apache.calcite.sql.type.SqlTypeFamily;
+import org.apache.calcite.sql.type.SqlTypeName;
+
+public class TextcatOperatorConversion implements SqlOperatorConversion
+{
+ private static final SqlFunction SQL_FUNCTION = OperatorConversions
+ .operatorBuilder("textcat")
+ .operandTypes(SqlTypeFamily.CHARACTER, SqlTypeFamily.CHARACTER)
+ .requiredOperands(2)
+ .returnType(SqlTypeName.VARCHAR)
+ .functionCategory(SqlFunctionCategory.STRING)
+ .build();
+
+ @Override
+ public SqlFunction calciteOperator()
+ {
+ return SQL_FUNCTION;
+ }
+
+ @Override
+ public DruidExpression toDruidExpression(
+ final PlannerContext plannerContext,
+ final RowSignature rowSignature,
+ final RexNode rexNode
+ )
+ {
+ return OperatorConversions.convertCall(
+ plannerContext,
+ rowSignature,
+ rexNode,
+ druidExpressions -> DruidExpression.of(
+ null,
+ DruidExpression.functionCall("concat", druidExpressions)
+ )
+ );
+ }
+}
diff --git a/sql/src/main/java/io/druid/sql/calcite/planner/DruidOperatorTable.java b/sql/src/main/java/io/druid/sql/calcite/planner/DruidOperatorTable.java
index 1fd43d1..9ebc87b 100644
--- a/sql/src/main/java/io/druid/sql/calcite/planner/DruidOperatorTable.java
+++ b/sql/src/main/java/io/druid/sql/calcite/planner/DruidOperatorTable.java
@@ -42,6 +42,7 @@ import io.druid.sql.calcite.expression.UnarySuffixOperatorConversion;
import io.druid.sql.calcite.expression.builtin.BTrimOperatorConversion;
import io.druid.sql.calcite.expression.builtin.CastOperatorConversion;
import io.druid.sql.calcite.expression.builtin.CeilOperatorConversion;
+import io.druid.sql.calcite.expression.builtin.ConcatOperatorConversion;
import io.druid.sql.calcite.expression.builtin.DateTruncOperatorConversion;
import io.druid.sql.calcite.expression.builtin.ExtractOperatorConversion;
import io.druid.sql.calcite.expression.builtin.FloorOperatorConversion;
@@ -52,6 +53,7 @@ import io.druid.sql.calcite.expression.builtin.RegexpExtractOperatorConversion;
import io.druid.sql.calcite.expression.builtin.ReinterpretOperatorConversion;
import io.druid.sql.calcite.expression.builtin.StrposOperatorConversion;
import io.druid.sql.calcite.expression.builtin.SubstringOperatorConversion;
+import io.druid.sql.calcite.expression.builtin.TextcatOperatorConversion;
import io.druid.sql.calcite.expression.builtin.TimeArithmeticOperatorConversion;
import io.druid.sql.calcite.expression.builtin.TimeExtractOperatorConversion;
import io.druid.sql.calcite.expression.builtin.TimeFloorOperatorConversion;
@@ -146,6 +148,8 @@ public class DruidOperatorTable implements SqlOperatorTable
.add(new RegexpExtractOperatorConversion())
.add(new StrposOperatorConversion())
.add(new SubstringOperatorConversion())
+ .add(new ConcatOperatorConversion())
+ .add(new TextcatOperatorConversion())
.add(new AliasedOperatorConversion(new SubstringOperatorConversion(), "SUBSTR"))
.add(new TimeArithmeticOperatorConversion.TimeMinusIntervalOperatorConversion())
.add(new TimeArithmeticOperatorConversion.TimePlusIntervalOperatorConversion())
diff --git a/sql/src/test/java/io/druid/sql/calcite/CalciteQueryTest.java b/sql/src/test/java/io/druid/sql/calcite/CalciteQueryTest.java
index 78792e0..1339214 100644
--- a/sql/src/test/java/io/druid/sql/calcite/CalciteQueryTest.java
+++ b/sql/src/test/java/io/druid/sql/calcite/CalciteQueryTest.java
@@ -6824,6 +6824,114 @@ public class CalciteQueryTest extends CalciteTestBase
);
}
+ @Test
+ public void testConcat() throws Exception
+ {
+ testQuery(
+ "SELECT CONCAT(dim1, '-', dim1, '_', dim1) as dimX FROM foo",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(QSS(Filtration.eternity()))
+ .virtualColumns(EXPRESSION_VIRTUAL_COLUMN(
+ "v0",
+ "concat(\"dim1\",'-',\"dim1\",'_',\"dim1\")",
+ ValueType.STRING
+ ))
+ .columns("v0")
+ .resultFormat(ScanQuery.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{"-_"},
+ new Object[]{"10.1-10.1_10.1"},
+ new Object[]{"2-2_2"},
+ new Object[]{"1-1_1"},
+ new Object[]{"def-def_def"},
+ new Object[]{"abc-abc_abc"}
+ )
+ );
+
+ testQuery(
+ "SELECT CONCAT(dim1, CONCAT(dim2,'x'), m2, 9999, dim1) as dimX FROM foo",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(QSS(Filtration.eternity()))
+ .virtualColumns(EXPRESSION_VIRTUAL_COLUMN(
+ "v0",
+ "concat(\"dim1\",concat(\"dim2\",'x'),\"m2\",9999,\"dim1\")",
+ ValueType.STRING
+ ))
+ .columns("v0")
+ .resultFormat(ScanQuery.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{"ax1.09999"},
+ new Object[]{"10.1x2.0999910.1"},
+ new Object[]{"2x3.099992"},
+ new Object[]{"1ax4.099991"},
+ new Object[]{"defabcx5.09999def"},
+ new Object[]{"abcx6.09999abc"}
+ )
+ );
+ }
+
+ @Test
+ public void testTextcat() throws Exception
+ {
+ testQuery(
+ "SELECT textcat(dim1, dim1) as dimX FROM foo",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(QSS(Filtration.eternity()))
+ .virtualColumns(EXPRESSION_VIRTUAL_COLUMN("v0", "concat(\"dim1\",\"dim1\")", ValueType.STRING))
+ .columns("v0")
+ .resultFormat(ScanQuery.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{""},
+ new Object[]{"10.110.1"},
+ new Object[]{"22"},
+ new Object[]{"11"},
+ new Object[]{"defdef"},
+ new Object[]{"abcabc"}
+ )
+ );
+
+ testQuery(
+ "SELECT textcat(dim1, CAST(m2 as VARCHAR)) as dimX FROM foo",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(QSS(Filtration.eternity()))
+ .virtualColumns(EXPRESSION_VIRTUAL_COLUMN(
+ "v0",
+ "concat(\"dim1\",CAST(\"m2\", 'STRING'))",
+ ValueType.STRING
+ ))
+ .columns("v0")
+ .resultFormat(ScanQuery.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{"1.0"},
+ new Object[]{"10.12.0"},
+ new Object[]{"23.0"},
+ new Object[]{"14.0"},
+ new Object[]{"def5.0"},
+ new Object[]{"abc6.0"}
+ )
+ );
+ }
+
private void testQuery(
final String sql,
final List<Query> expectedQueries,
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org