This is an automated email from the ASF dual-hosted git repository.
danny0405 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new 39e5856 [CALCITE-2157] ClickHouse dialect implementation (Chris
Baynes)
39e5856 is described below
commit 39e58566c1ac02824d99ae9260d3315539efd57e
Author: Chris Baynes <binaryexp+git...@gmail.com>
AuthorDate: Tue Jan 30 14:13:12 2018 +0100
[CALCITE-2157] ClickHouse dialect implementation (Chris Baynes)
close apache/calcite#618
---
.../java/org/apache/calcite/sql/SqlDialect.java | 4 +
.../apache/calcite/sql/SqlDialectFactoryImpl.java | 5 +
.../calcite/sql/dialect/ClickHouseSqlDialect.java | 242 +++++++++++++++++++++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 83 ++++++-
4 files changed, 333 insertions(+), 1 deletion(-)
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
index 07f82c6..2ef11f9 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
@@ -274,6 +274,9 @@ public class SqlDialect {
case "ACCESS":
return DatabaseProduct.ACCESS;
case "APACHE DERBY":
+ return DatabaseProduct.DERBY;
+ case "CLICKHOUSE":
+ return DatabaseProduct.CLICKHOUSE;
case "DBMS:CLOUDSCAPE":
return DatabaseProduct.DERBY;
case "HIVE":
@@ -1226,6 +1229,7 @@ public class SqlDialect {
ACCESS("Access", "\"", NullCollation.HIGH),
BIG_QUERY("Google BigQuery", "`", NullCollation.LOW),
CALCITE("Apache Calcite", "\"", NullCollation.HIGH),
+ CLICKHOUSE("ClickHouse", "`", NullCollation.LOW),
MSSQL("Microsoft SQL Server", "[", NullCollation.HIGH),
MYSQL("MySQL", "`", NullCollation.LOW),
ORACLE("Oracle", "\"", NullCollation.HIGH),
diff --git
a/core/src/main/java/org/apache/calcite/sql/SqlDialectFactoryImpl.java
b/core/src/main/java/org/apache/calcite/sql/SqlDialectFactoryImpl.java
index 6d265f1..82d07cf 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialectFactoryImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialectFactoryImpl.java
@@ -22,6 +22,7 @@ import org.apache.calcite.sql.dialect.AccessSqlDialect;
import org.apache.calcite.sql.dialect.AnsiSqlDialect;
import org.apache.calcite.sql.dialect.BigQuerySqlDialect;
import org.apache.calcite.sql.dialect.CalciteSqlDialect;
+import org.apache.calcite.sql.dialect.ClickHouseSqlDialect;
import org.apache.calcite.sql.dialect.Db2SqlDialect;
import org.apache.calcite.sql.dialect.DerbySqlDialect;
import org.apache.calcite.sql.dialect.FirebirdSqlDialect;
@@ -102,6 +103,8 @@ public class SqlDialectFactoryImpl implements
SqlDialectFactory {
return new AccessSqlDialect(c);
case "APACHE DERBY":
return new DerbySqlDialect(c);
+ case "CLICKHOUSE":
+ return new ClickHouseSqlDialect(c);
case "DBMS:CLOUDSCAPE":
return new DerbySqlDialect(c);
case "HIVE":
@@ -244,6 +247,8 @@ public class SqlDialectFactoryImpl implements
SqlDialectFactory {
return BigQuerySqlDialect.DEFAULT;
case CALCITE:
return CalciteSqlDialect.DEFAULT;
+ case CLICKHOUSE:
+ return ClickHouseSqlDialect.DEFAULT;
case DB2:
return Db2SqlDialect.DEFAULT;
case DERBY:
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/ClickHouseSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/ClickHouseSqlDialect.java
new file mode 100644
index 0000000..a05799d
--- /dev/null
+++
b/core/src/main/java/org/apache/calcite/sql/dialect/ClickHouseSqlDialect.java
@@ -0,0 +1,242 @@
+/*
+ * 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.calcite.sql.dialect;
+
+import org.apache.calcite.avatica.util.TimeUnitRange;
+import org.apache.calcite.config.NullCollation;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.sql.SqlAbstractDateTimeLiteral;
+import org.apache.calcite.sql.SqlBasicTypeNameSpec;
+import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlDataTypeSpec;
+import org.apache.calcite.sql.SqlDateLiteral;
+import org.apache.calcite.sql.SqlDialect;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlLiteral;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlSpecialOperator;
+import org.apache.calcite.sql.SqlTimeLiteral;
+import org.apache.calcite.sql.SqlTimestampLiteral;
+import org.apache.calcite.sql.SqlWriter;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.sql.type.BasicSqlType;
+import org.apache.calcite.sql.type.SqlTypeName;
+
+import com.google.common.base.Preconditions;
+
+/**
+ * A <code>SqlDialect</code> implementation for the ClickHouse database.
+ */
+public class ClickHouseSqlDialect extends SqlDialect {
+ public static final SqlDialect.Context DEFAULT_CONTEXT =
SqlDialect.EMPTY_CONTEXT
+ .withDatabaseProduct(SqlDialect.DatabaseProduct.CLICKHOUSE)
+ .withIdentifierQuoteString("`")
+ .withNullCollation(NullCollation.LOW);
+
+ public static final SqlDialect DEFAULT = new
ClickHouseSqlDialect(DEFAULT_CONTEXT);
+
+ private static final SqlSpecialOperator CLICKHOUSE_SUBSTRING =
+ new SqlSpecialOperator("substring", SqlKind.OTHER_FUNCTION) {
+ public void unparse(
+ SqlWriter writer,
+ SqlCall call,
+ int leftPrec,
+ int rightPrec) {
+ writer.print(getName());
+ final SqlWriter.Frame frame =
+ writer.startList(SqlWriter.FrameTypeEnum.FUN_CALL, "(", ")");
+ for (SqlNode operand : call.getOperandList()) {
+ writer.sep(",");
+ operand.unparse(writer, 0, 0);
+ }
+ writer.endList(frame);
+ }
+ };
+
+ /** Creates a ClickHouseSqlDialect. */
+ public ClickHouseSqlDialect(Context context) {
+ super(context);
+ }
+
+ @Override public boolean supportsCharSet() {
+ return false;
+ }
+
+ @Override public boolean supportsNestedAggregations() {
+ return false;
+ }
+
+ @Override public boolean supportsWindowFunctions() {
+ return false;
+ }
+
+ @Override public CalendarPolicy getCalendarPolicy() {
+ return CalendarPolicy.SHIFT;
+ }
+
+ @Override public SqlNode getCastSpec(RelDataType type) {
+ if (type instanceof BasicSqlType) {
+ SqlTypeName typeName = type.getSqlTypeName();
+ switch (typeName) {
+ case VARCHAR:
+ return createSqlDataTypeSpecByName("String", typeName);
+ case TINYINT:
+ return createSqlDataTypeSpecByName("Int8", typeName);
+ case SMALLINT:
+ return createSqlDataTypeSpecByName("Int16", typeName);
+ case INTEGER:
+ return createSqlDataTypeSpecByName("Int32", typeName);
+ case BIGINT:
+ return createSqlDataTypeSpecByName("Int64", typeName);
+ case FLOAT:
+ return createSqlDataTypeSpecByName("Float32", typeName);
+ case DOUBLE:
+ return createSqlDataTypeSpecByName("Float64", typeName);
+ case DATE:
+ return createSqlDataTypeSpecByName("Date", typeName);
+ case TIMESTAMP:
+ case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
+ return createSqlDataTypeSpecByName("DateTime", typeName);
+ }
+ }
+
+ return super.getCastSpec(type);
+ }
+
+ private SqlDataTypeSpec createSqlDataTypeSpecByName(String typeAlias,
SqlTypeName typeName) {
+ SqlBasicTypeNameSpec spec = new SqlBasicTypeNameSpec(typeName,
SqlParserPos.ZERO) {
+ @Override public void unparse(SqlWriter writer, int leftPrec, int
rightPrec) {
+ // unparse as an identifier to ensure that type names are cased
correctly
+ writer.identifier(typeAlias, true);
+ }
+ };
+ return new SqlDataTypeSpec(spec, SqlParserPos.ZERO);
+ }
+
+ @Override public void unparseDateTimeLiteral(SqlWriter writer,
+ SqlAbstractDateTimeLiteral literal, int leftPrec, int rightPrec) {
+ String toFunc;
+ if (literal instanceof SqlDateLiteral) {
+ toFunc = "toDate";
+ } else if (literal instanceof SqlTimestampLiteral) {
+ toFunc = "toDateTime";
+ } else if (literal instanceof SqlTimeLiteral) {
+ toFunc = "toTime";
+ } else {
+ throw new RuntimeException("ClickHouse does not support DateTime
literal: "
+ + literal);
+ }
+
+ writer.literal(toFunc + "('" + literal.toFormattedString() + "')");
+ }
+
+ @Override public void unparseOffsetFetch(SqlWriter writer, SqlNode offset,
+ SqlNode fetch) {
+ Preconditions.checkArgument(fetch != null);
+
+ writer.newlineAndIndent();
+ final SqlWriter.Frame frame =
+ writer.startList(SqlWriter.FrameTypeEnum.FETCH);
+ writer.keyword("LIMIT");
+
+ if (offset != null) {
+ offset.unparse(writer, -1, -1);
+ writer.sep(",", true);
+ }
+
+ fetch.unparse(writer, -1, -1);
+ writer.endList(frame);
+ }
+
+ @Override public void unparseCall(SqlWriter writer, SqlCall call,
+ int leftPrec, int rightPrec) {
+ if (call.getOperator() == SqlStdOperatorTable.SUBSTRING) {
+ CLICKHOUSE_SUBSTRING.unparse(writer, call, 0, 0);
+ } else {
+ switch (call.getKind()) {
+ case FLOOR:
+ if (call.operandCount() != 2) {
+ super.unparseCall(writer, call, leftPrec, rightPrec);
+ return;
+ }
+
+ unparseFloor(writer, call);
+ break;
+
+ case COUNT:
+ // CH returns NULL rather than 0 for COUNT(DISTINCT) of NULL values.
+ // https://github.com/yandex/ClickHouse/issues/2494
+ // Wrap the call in a CH specific coalesce (assumeNotNull).
+ if (call.getFunctionQuantifier() != null
+ && call.getFunctionQuantifier().toString().equals("DISTINCT")) {
+ writer.print("assumeNotNull");
+ SqlWriter.Frame frame = writer.startList("(", ")");
+ super.unparseCall(writer, call, leftPrec, rightPrec);
+ writer.endList(frame);
+ } else {
+ super.unparseCall(writer, call, leftPrec, rightPrec);
+ }
+ break;
+
+ default:
+ super.unparseCall(writer, call, leftPrec, rightPrec);
+ }
+ }
+ }
+
+ /**
+ * Unparses datetime floor for ClickHouse.
+ *
+ * @param writer Writer
+ * @param call Call
+ */
+ private void unparseFloor(SqlWriter writer, SqlCall call) {
+ final SqlLiteral timeUnitNode = call.operand(1);
+ TimeUnitRange unit = (TimeUnitRange) timeUnitNode.getValue();
+
+ String funName;
+ switch (unit) {
+ case YEAR:
+ funName = "toStartOfYear";
+ break;
+ case MONTH:
+ funName = "toStartOfMonth";
+ break;
+ case WEEK:
+ funName = "toMonday";
+ break;
+ case DAY:
+ funName = "toDate";
+ break;
+ case HOUR:
+ funName = "toStartOfHour";
+ break;
+ case MINUTE:
+ funName = "toStartOfMinute";
+ break;
+ default:
+ throw new RuntimeException("ClickHouse does not support FLOOR for time
unit: "
+ + unit);
+ }
+
+ writer.print(funName);
+ SqlWriter.Frame frame = writer.startList("(", ")");
+ call.operand(0).unparse(writer, 0, 0);
+ writer.endList(frame);
+ }
+}
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 3ecb46f..9e53070 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -2060,13 +2060,32 @@ class RelToSqlConverterTest {
sql(query).dialect(mySqlDialect(NullCollation.LAST)).ok(expected);
}
+ @Test void testCastToVarchar() {
+ String query = "select cast(\"product_id\" as varchar) from \"product\"";
+ final String expectedClickHouse = "SELECT CAST(`product_id` AS `String`)\n"
+ + "FROM `foodmart`.`product`";
+ final String expectedMysql = "SELECT CAST(`product_id` AS CHAR)\n"
+ + "FROM `foodmart`.`product`";
+ sql(query)
+ .withClickHouse()
+ .ok(expectedClickHouse)
+ .withMysql()
+ .ok(expectedMysql);
+ }
+
@Test void testSelectQueryWithLimitClauseWithoutOrder() {
String query = "select \"product_id\" from \"product\" limit 100 offset
10";
final String expected = "SELECT \"product_id\"\n"
+ "FROM \"foodmart\".\"product\"\n"
+ "OFFSET 10 ROWS\n"
+ "FETCH NEXT 100 ROWS ONLY";
- sql(query).ok(expected);
+ final String expectedClickHouse = "SELECT `product_id`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "LIMIT 10, 100";
+ sql(query)
+ .ok(expected)
+ .withClickHouse()
+ .ok(expectedClickHouse);
}
@Test void testSelectQueryWithLimitOffsetClause() {
@@ -2790,6 +2809,14 @@ class RelToSqlConverterTest {
.ok(expected);
}
+ @Test void testFloorClickHouse() {
+ String query = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\"";
+ String expected = "SELECT toStartOfMinute(`hire_date`)\nFROM
`foodmart`.`employee`";
+ sql(query)
+ .withClickHouse()
+ .ok(expected);
+ }
+
@Test void testFloorPostgres() {
String query = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\"";
String expected = "SELECT DATE_TRUNC('MINUTE', \"hire_date\")\nFROM
\"foodmart\".\"employee\"";
@@ -2834,6 +2861,25 @@ class RelToSqlConverterTest {
.ok(expected);
}
+ @Test public void testFloorWeek() {
+ final String query = "SELECT floor(\"hire_date\" TO WEEK) FROM
\"employee\"";
+ final String expectedClickHouse = "SELECT toMonday(`hire_date`)\n"
+ + "FROM `foodmart`.`employee`";
+ final String expectedMssql = "SELECT CONVERT(DATETIME,
CONVERT(VARCHAR(10), "
+ + "DATEADD(day, - (6 + DATEPART(weekday, [hire_date] )) % 7,
[hire_date] ), 126))\n"
+ + "FROM [foodmart].[employee]";
+ final String expectedMysql = "SELECT STR_TO_DATE(DATE_FORMAT(`hire_date` ,
'%x%v-1'), "
+ + "'%x%v-%w')\n"
+ + "FROM `foodmart`.`employee`";
+ sql(query)
+ .withClickHouse()
+ .ok(expectedClickHouse)
+ .withMssql()
+ .ok(expectedMssql)
+ .withMysql()
+ .ok(expectedMysql);
+ }
+
@Test void testUnparseSqlIntervalQualifierDb2() {
String queryDatePlus = "select * from \"employee\" where \"hire_date\" +
"
+ "INTERVAL '19800' SECOND(5) > TIMESTAMP '2005-10-17 00:00:00' ";
@@ -2957,6 +3003,24 @@ class RelToSqlConverterTest {
.ok(expected);
}
+ @Test public void testFloorMonth() {
+ final String query = "SELECT floor(\"hire_date\" TO MONTH) FROM
\"employee\"";
+ final String expectedClickHouse = "SELECT toStartOfMonth(`hire_date`)\n"
+ + "FROM `foodmart`.`employee`";
+ final String expectedMssql = "SELECT CONVERT(DATETIME, CONVERT(VARCHAR(7),
[hire_date] , "
+ + "126)+'-01')\n"
+ + "FROM [foodmart].[employee]";
+ final String expectedMysql = "SELECT DATE_FORMAT(`hire_date`,
'%Y-%m-01')\n"
+ + "FROM `foodmart`.`employee`";
+ sql(query)
+ .withClickHouse()
+ .ok(expectedClickHouse)
+ .withMssql()
+ .ok(expectedMssql)
+ .withMysql()
+ .ok(expectedMysql);
+ }
+
@Test void testFloorMysqlHour() {
String query = "SELECT floor(\"hire_date\" TO HOUR) FROM \"employee\"";
String expected = "SELECT DATE_FORMAT(`hire_date`, '%Y-%m-%d %H:00:00')\n"
@@ -2994,6 +3058,9 @@ class RelToSqlConverterTest {
final String expected = "SELECT TRUNC(hire_date, 'MI')\n"
+ "FROM foodmart.employee\n"
+ "GROUP BY TRUNC(hire_date, 'MI')";
+ final String expectedClickHouse = "SELECT toStartOfMinute(`hire_date`)\n"
+ + "FROM `foodmart`.`employee`\n"
+ + "GROUP BY toStartOfMinute(`hire_date`)";
final String expectedOracle = "SELECT TRUNC(\"hire_date\", 'MINUTE')\n"
+ "FROM \"foodmart\".\"employee\"\n"
+ "GROUP BY TRUNC(\"hire_date\", 'MINUTE')";
@@ -3007,6 +3074,8 @@ class RelToSqlConverterTest {
sql(query)
.withHsqldb()
.ok(expected)
+ .withClickHouse()
+ .ok(expectedClickHouse)
.withOracle()
.ok(expectedOracle)
.withPostgresql()
@@ -3018,6 +3087,8 @@ class RelToSqlConverterTest {
@Test void testSubstring() {
final String query = "select substring(\"brand_name\" from 2) "
+ "from \"product\"\n";
+ final String expectedClickHouse = "SELECT substring(`brand_name`, 2)\n"
+ + "FROM `foodmart`.`product`";
final String expectedOracle = "SELECT SUBSTR(\"brand_name\", 2)\n"
+ "FROM \"foodmart\".\"product\"";
final String expectedPostgresql = "SELECT SUBSTRING(\"brand_name\" FROM
2)\n"
@@ -3027,6 +3098,8 @@ class RelToSqlConverterTest {
final String expectedMysql = "SELECT SUBSTRING(`brand_name` FROM 2)\n"
+ "FROM `foodmart`.`product`";
sql(query)
+ .withClickHouse()
+ .ok(expectedClickHouse)
.withOracle()
.ok(expectedOracle)
.withPostgresql()
@@ -3045,6 +3118,8 @@ class RelToSqlConverterTest {
@Test void testSubstringWithFor() {
final String query = "select substring(\"brand_name\" from 2 for 3) "
+ "from \"product\"\n";
+ final String expectedClickHouse = "SELECT substring(`brand_name`, 2, 3)\n"
+ + "FROM `foodmart`.`product`";
final String expectedOracle = "SELECT SUBSTR(\"brand_name\", 2, 3)\n"
+ "FROM \"foodmart\".\"product\"";
final String expectedPostgresql = "SELECT SUBSTRING(\"brand_name\" FROM 2
FOR 3)\n"
@@ -3056,6 +3131,8 @@ class RelToSqlConverterTest {
final String expectedMssql = "SELECT SUBSTRING([brand_name], 2, 3)\n"
+ "FROM [foodmart].[product]";
sql(query)
+ .withClickHouse()
+ .ok(expectedClickHouse)
.withOracle()
.ok(expectedOracle)
.withPostgresql()
@@ -5100,6 +5177,10 @@ class RelToSqlConverterTest {
return dialect(SqlDialect.DatabaseProduct.CALCITE.getDialect());
}
+ Sql withClickHouse() {
+ return dialect(SqlDialect.DatabaseProduct.CLICKHOUSE.getDialect());
+ }
+
Sql withDb2() {
return dialect(SqlDialect.DatabaseProduct.DB2.getDialect());
}
