Apache Calcite对接ClickHouse

  |   0 评论   |   999 浏览

背景

Calcite怎么对接ClickHouse呢?

是的,ClickHouse自带JDBC驱动,使用Calcite来做二次封装一般情况下没有什么意义。

这里只是记录一下此方案的可行性。

结果

经过测试:

1) 标准SQL: 直接使用对接ClickHouse的JDBC驱动即可。
2) SQL方言(SQL dialect):目前已经有一个PR,见[CALCITE-2157] ClickHouse dialect implementation

方法

这里仅对标准SQL情况做了一下测试,对于SQL方言情况尚未情况。

代码

package com.abeffect.calcite.clickhouse;

import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.apache.calcite.adapter.jdbc.JdbcSchema;
import org.apache.calcite.jdbc.CalciteConnection;
import org.apache.calcite.schema.Schema;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.commons.dbcp.BasicDataSource;

public class MainTestClickHouseSchema {

	public static void main(String[] args) throws ClassNotFoundException, SQLException {

		Class.forName("org.apache.calcite.jdbc.Driver");

		Properties info = new Properties();
		info.setProperty("lex", "JAVA");
		Connection connection = DriverManager.getConnection("jdbc:calcite:", info);
		CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);
		SchemaPlus rootSchema = calciteConnection.getRootSchema();

		Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
		BasicDataSource dataSource = new BasicDataSource();
		dataSource.setUrl("jdbc:clickhouse://localhost:8123/dplus");
		dataSource.setUsername("default");
		dataSource.setPassword("abeffect");
		Schema schema = JdbcSchema.create(rootSchema, "flowaters", dataSource, null, "flowaters");

		rootSchema.add("flowaters", schema);
		Statement statement = calciteConnection.createStatement();

		String sql = "select count(*) from flowaters.test_table";
		ResultSet resultSet = statement.executeQuery(sql);

		output(resultSet, System.out);
		resultSet.close();
		statement.close();
		connection.close();
	}

	private static void output(ResultSet resultSet, PrintStream out) throws SQLException {
		final ResultSetMetaData metaData = resultSet.getMetaData();
		final int columnCount = metaData.getColumnCount();
		while (resultSet.next()) {
			for (int i = 1;; i++) {
				out.print(resultSet.getString(i));
				if (i < columnCount) {
					out.print(", ");
				} else {
					out.println();
					break;
				}
			}
		}
	}
}

pom依赖

部分pom依赖

    <dependency>
      <groupId>ru.yandex.clickhouse</groupId>
      <artifactId>clickhouse-jdbc</artifactId>
      <version>0.1.39</version>
    </dependency>

输出

123

FAQ

Object ‘table’ not found within ‘database’

问题:找不到表

java.sql.SQLException: Error while executing SQL "select * from crash": From line 1, column 15 to line 1, column 19: Object 'CRASH' not found within 'abeffect'; did you mean 'crash'?
	at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
	at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
	at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
	at org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:218)
	at com.abeffect.calcite.adapter.crash.TestCrashQuery.main(TestCrashQuery.java:52)
Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, column 15 to line 1, column 19: Object 'CRASH' not found within 'abeffect'; did you mean 'crash'?
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
	at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:803)
	at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:788)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4706)
	at org.apache.calcite.sql.validate.IdentifierNamespace.resolveImpl(IdentifierNamespace.java:161)
	at org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:177)
	at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:947)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:928)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2975)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2960)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3219)
	at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
	at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:947)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:928)
	at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:226)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:903)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:613)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:553)
	at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:264)
	at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:230)
	at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:781)
	at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:640)
	at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:610)
	at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:221)
	at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:603)
	at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:638)
	at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:149)
	... 2 more
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Object 'CRASH' not found within 'abeffect'; did you mean 'crash'?
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
	at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572)
	... 30 more

解决:

		Properties info = new Properties();
		info.put("model", model);
		// 增加下面这一行
		info.put("unquotedCasing", Casing.UNCHANGED.toString());
		
		Connection connection = DriverManager.getConnection("jdbc:calcite:", info);

参考

评论

发表评论

validate