search

zhicwu / clickhouse-datasource-bridge

An alternative implementation of JDBC Bridge for ClickHouse.
https://hub.docker.com/repository/docker/zhicwu/clickhouse-datasource-bridge
Apache License 2.0
2 stars 4 forks source link

Connect to ElasticSearch JDBC (opendistro) #2

Open bralbral opened 3 years ago

bralbral commented 3 years ago

Hello!

Thanks for the library! I successfully connected to the MSSQL server but problems appeared with elasticsearch.

I tried select from elasticsearch using your datasource bridge.

I added 2 lines in your Dockerfile:

ELASTICSEARCH_OPENDISTRO_DRIVER_VERSION=1.11.0.0 as environ variable and $MAVEN_REPO_URL/com/amazon/opendistroforelasticsearch/client/opendistro-sql-jdbc/$ELASTICSEARCH_OPENDISTRO_DRIVER_VERSION/opendistro-sql-jdbc-$ELASTICSEARCH_OPENDISTRO_DRIVER_VERSION.jar \ in install line.

After i executed :

select * from jdbc('jdbc:elasticsearch://https://192.168.0.103:9200/?trustSelfSigned=true&hostnameVerification=false&auth=basic&user=admin&password=admin&requestCompression=true','kibana_sample_data_flights')

and it gave me error:

jdbc-bridge | 2020-11-07 16:11:49.049 [vert.x-eventloop-thread-0] [INFO ] {DataSourceBridgeVerticle:205} - Raw query: jdbc-bridge | kibana_sample_data_flights jdbc-bridge | 2020-11-07 16:11:49.049 [vert.x-eventloop-thread-0] [WARN ] {ClickHouseJdbcDataSource:154} - Failed call setClientInfo due to Client info is not supported. jdbc-bridge | 2020-11-07 16:11:49.049 [vert.x-eventloop-thread-0] [WARN ] {ClickHouseJdbcDataSource:154} - Failed call setClientInfo due to Client info is not supported. jdbc-bridge | 2020-11-07 16:11:49.049 [vert.x-eventloop-thread-0] [WARN ] {ClickHouseJdbcDataSource:572} - Identifier quote string cannot be empty string, had to change it to default jdbc-bridge | 2020-11-07 16:11:49.049 [vert.x-eventloop-thread-0] [WARN ] {ClickHouseJdbcDataSource:154} - Failed call setClientInfo due to Client info is not supported. jdbc-bridge | 2020-11-07 16:11:49.049 [vert.x-eventloop-thread-0] [WARN ] {ClickHouseJdbcDataSource:572} - Identifier quote string cannot be empty string, had to change it to default jdbc-bridge | 2020-11-07 16:11:49.049 [vert.x-worker-thread-15] [INFO ] {ClickHouseJdbcDataSource:582} - Executing SQL: jdbc-bridge | SELECTOrigin,FlightNum,FlightDelay,DistanceMiles,FlightTimeMin,OriginWeather,dayOfWeek,AvgTicketPrice,Carrier,FlightDelayMin,OriginRegion,DestAirportID,FlightDelayType,timestamp,Dest,FlightTimeHour,Cancelled,DistanceKilometers,OriginCityName,DestWeather,OriginCountry,DestCountry,DestRegion,OriginAirportID,DestCityNameFROMkibana_sample_data_flights jdbc-bridge | 2020-11-07 16:11:49.049 [vert.x-worker-thread-15] [WARN ] {ClickHouseJdbcDataSource:154} - Failed call setClientInfo due to Client info is not supported. jdbc-bridge | 2020-11-07 16:11:49.049 [vert.x-eventloop-thread-0] [ERROR] {DataSourceBridgeVerticle:192} - Failed to respond jdbc-bridge | java.lang.IllegalStateException: Failed to execute SQL jdbc-bridge | at com.github.clickhouse.bridge.jdbc.ClickHouseJdbcDataSource.executeQuery(ClickHouseJdbcDataSource.java:599) jdbc-bridge | at com.github.clickhouse.bridge.DataSourceBridgeVerticle.lambda$handleQuery$7(DataSourceBridgeVerticle.java:325) jdbc-bridge | at com.github.clickhouse.bridge.DataSourceBridgeVerticle$$Lambda$133/00000000A4BE8AE0.handle(Unknown Source) jdbc-bridge | at io.vertx.core.impl.ContextImpl.lambda$executeBlocking$2(ContextImpl.java:316) jdbc-bridge | at io.vertx.core.impl.ContextImpl$$Lambda$30/00000000A4265CC0.run(Unknown Source) jdbc-bridge | at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) jdbc-bridge | at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) jdbc-bridge | at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30) jdbc-bridge | at java.lang.Thread.run(Thread.java:821) jdbc-bridge | Caused by: java.sql.SQLDataException: Can not convert object 'false' of type 'java.lang.Boolean' to type 'java.lang.Integer' jdbc-bridge | at com.amazon.opendistroforelasticsearch.jdbc.types.TypeConverter.objectConversionException(TypeConverter.java:48) jdbc-bridge | at com.amazon.opendistroforelasticsearch.jdbc.types.BaseTypeConverter.convert(BaseTypeConverter.java:63) jdbc-bridge | at com.amazon.opendistroforelasticsearch.jdbc.ResultSetImpl.getObjectX(ResultSetImpl.java:589) jdbc-bridge | at com.amazon.opendistroforelasticsearch.jdbc.ResultSetImpl.getObjectX(ResultSetImpl.java:583) jdbc-bridge | at com.amazon.opendistroforelasticsearch.jdbc.ResultSetImpl.getIntX(ResultSetImpl.java:265) jdbc-bridge | at com.amazon.opendistroforelasticsearch.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:259) jdbc-bridge | at com.github.clickhouse.bridge.jdbc.ClickHouseJdbcDataSource.stream(ClickHouseJdbcDataSource.java:420) jdbc-bridge | at com.github.clickhouse.bridge.jdbc.ClickHouseJdbcDataSource.executeQuery(ClickHouseJdbcDataSource.java:588) jdbc-bridge | ... 8 more

As I understand it, the problem is somewhere in your implementation or implementation of jdbc function in clickhouse.

Best regards!

bralbral commented 3 years ago

UPD : problem in boolean fields.

Query select Origin, FlightNum, DistanceMiles, FlightTimeMin, OriginWeather, dayOfWeek, AvgTicketPrice, Carrier, FlightDelayMin, OriginRegion, DestAirportID, FlightDelayType, timestamp, Dest, FlightTimeHour, DistanceKilometers, OriginCityName, DestWeather, OriginCountry, DestCountry, DestRegion, OriginAirportID, DestCityName from jdbc('jdbc:elasticsearch://https://192.168.0.103:9200/?trustSelfSigned=true&hostnameVerification=false&auth=basic&user=admin&password=admin&requestCompression=true','kibana_sample_data_flights') limit 1 works like a charm if delete boolean fields.

zhicwu commented 3 years ago

Thanks for letting me know. This repo is deprecated as the code has been merged into official repo. I'm working on a new release targeting Dec and I will try to fix the issue there.