clickhouse / clickhouse-jdbc-bridge Goto Github PK

A JDBC proxy from ClickHouse to external databases

License: Apache License 2.0

Java 97.80% Shell 0.78% Dockerfile 1.42% JavaScript 0.01%

clickhouse clickhouse-jdbc-bridge external-database

clickhouse-jdbc-bridge's Introduction

Note

clickhouse-jdbc-bridge contains experimental codes and is no longer supported. It may contain reliability and security vulnerabilities. Use it at your own risk.

ClickHouse JDBC Bridge

JDBC bridge for ClickHouse®. It acts as a stateless proxy passing queries from ClickHouse to external datasources. With this extension, you can run distributed query on ClickHouse across multiple datasources in real time, which in a way simplifies the process of building data pipelines for data warehousing, monitoring and integrity check etc.

Overview

Known Issues / Limitation

Connection issue like jdbc-bridge is not running or connect timed out - see performance section and this issue for details
Complex data types like Array and Tuple are currently not supported - they're treated as String
Pushdown is not supported and query may execute twice because of type inferring
Mutation is not fully supported - only insertion in simple cases
Scripting is experimental

Quick Start

Docker Compose

git clone https://github.com/ClickHouse/clickhouse-jdbc-bridge.git
cd clickhouse-jdbc-bridge/misc/quick-start
docker-compose up -d
...
docker-compose ps

           Name                         Command               State              Ports
--------------------------------------------------------------------------------------------------
quick-start_ch-server_1         /entrypoint.sh                   Up      8123/tcp, 9000/tcp, 9009/tcp
quick-start_db-mariadb10_1      docker-entrypoint.sh mysqld      Up      3306/tcp
quick-start_db-mysql5_1         docker-entrypoint.sh mysqld      Up      3306/tcp, 33060/tcp
quick-start_db-mysql8_1         docker-entrypoint.sh mysqld      Up      3306/tcp, 33060/tcp
quick-start_db-postgres13_1     docker-entrypoint.sh postgres    Up      5432/tcp
quick-start_jdbc-bridge_1       /sbin/my_init                    Up      9019/tcp

# issue below query, and you'll see "ch-server        1" returned
docker-compose run ch-server clickhouse-client --query="select * from jdbc('self?datasource_column', 'select 1')"

Docker CLI

It's easier to get started using all-in-one docker image:

# build all-in-one docker image
git clone https://github.com/ClickHouse/clickhouse-jdbc-bridge.git
cd clickhouse-jdbc-bridge
docker build -t my/clickhouse-all-in-one -f all-in-one.Dockerfile .

# start container in background
docker run --rm -d --name ch-and-jdbc-bridge my/clickhouse-all-in-one

# enter container to add datasource and issue query
docker exec -it ch-and-jdbc-bridge bash

cp /etc/clickhouse-jdbc-bridge/config/datasources/datasource.json.example \
    /etc/clickhouse-jdbc-bridge/config/datasources/ch-server.json

# you're supposed to see "ch-server        1" returned from ClickHouse
clickhouse-client --query="select * from jdbc('self?datasource_column', 'select 1')"

Alternatively, if you prefer the hard way ;)

# create a network for ClickHouse and JDBC brigde, so that they can communicate with each other
docker network create ch-net --attachable
# start the two containers
docker run --rm -d --network ch-net --name jdbc-bridge --hostname jdbc-bridge clickhouse/jdbc-bridge
docker run --rm -d --network ch-net --name ch-server --hostname ch-server \
    --entrypoint /bin/bash clickhouse/clickhouse-server -c \
    "echo '<clickhouse><jdbc_bridge><host>jdbc-bridge</host><port>9019</port></jdbc_bridge></clickhouse>' \
        > /etc/clickhouse-server/config.d/jdbc_bridge_config.xml && /entrypoint.sh"
# add named datasource and query
docker exec -it jdbc-bridge cp /app/config/datasources/datasource.json.example \
    /app/config/datasources/ch-server.json
docker exec -it jdbc-bridge cp /app/config/queries/query.json.example \
    /app/config/queries/show-query-logs.json
# issue below query, and you'll see "ch-server        1" returned from ClickHouse
docker exec -it ch-server clickhouse-client \
    --query="select * from jdbc('self?datasource_column', 'select 1')"

Debian/RPM Package

Besides docker, you can download and install released Debian/RPM package on existing Linux system.

Debian/Ubuntu

apt update && apt install -y procps wget
wget https://github.com/ClickHouse/clickhouse-jdbc-bridge/releases/download/v2.1.0/clickhouse-jdbc-bridge_2.1.0-1_all.deb
apt install --no-install-recommends -f ./clickhouse-jdbc-bridge_2.1.0-1_all.deb
clickhouse-jdbc-bridge

CentOS/RHEL

yum install -y wget
wget https://github.com/ClickHouse/clickhouse-jdbc-bridge/releases/download/v2.1.0/clickhouse-jdbc-bridge-2.1.0-1.noarch.rpm
yum localinstall -y clickhouse-jdbc-bridge-2.1.0-1.noarch.rpm
clickhouse-jdbc-bridge

Java CLI

wget https://github.com/ClickHouse/clickhouse-jdbc-bridge/releases/download/v2.1.0/clickhouse-jdbc-bridge-2.1.0-shaded.jar
# add named datasource
wget -P config/datasources https://raw.githubusercontent.com/ClickHouse/clickhouse-jdbc-bridge/master/misc/quick-start/jdbc-bridge/config/datasources/ch-server.json
# start jdbc bridge, and then issue below query in ClickHouse for testing
# select * from jdbc('ch-server', 'select 1')
java -jar clickhouse-jdbc-bridge-2.1.0-shaded.jar

Usage

In most cases, you'll use jdbc table function to query against external datasources:

select * from jdbc('<datasource>', '<schema>', '<query>')

schema is optional but others are mandatory. Please be aware that the query is in native format of the given datasource. For example, if the query is select * from some_table limit 10, it may work in MariaDB but not in PostgreSQL, as the latter one does not understand limit.

Assuming you started a test environment using docker-compose, please refer to examples below to get familiar with JDBC bridge.

Data Source

-- show datasources and usage
select * from jdbc('', 'show datasources')
-- access named datasource
select * from jdbc('ch-server', 'select 1')
-- adhoc datasource is NOT recommended for security reason
select *
from jdbc('jdbc:clickhouse://localhost:8123/system?compress=false&ssl=false&user=default', 'select 1')

Schema

By default, any adhoc query passed to JDBC bridge will be executed twice. The first run is for type inferring, while the second for retrieving results. Although metadata will be cached(for up to 5 minutes by default), executing same query twice could be a problem - that's where schema comes into play.

-- inline schema
select * from jdbc('ch-server', 'num UInt8, str String', 'select 1 as num, ''2'' as str')
select * from jdbc('ch-server', 'num Nullable(Decimal(10,0)), Nullable(str FixedString(1)) DEFAULT ''x''', 'select 1 as num, ''2'' as str')
-- named schema
select * from jdbc('ch-server', 'query-log', 'show-query-logs')

Query

-- adhoc query
select * from jdbc('ch-server', 'system', 'select * from query_log where user != ''default''')
select * from jdbc('ch-server', 'select * from query_log where user != ''default''')
select * from jdbc('ch-server', 'select * from system.query_log where user != ''default''')

-- table query
select * from jdbc('ch-server', 'system', 'query_log')
select * from jdbc('ch-server', 'query_log')

-- saved query
select * from jdbc('ch-server', 'scripts/show-query-logs.sql')

-- named query
select * from jdbc('ch-server', 'show-query-logs')

-- scripting
select * from jdbc('script', '[1,2,3]')
select * from jdbc('script', 'js', '[1,2,3]')
select * from jdbc('script', 'scripts/one-two-three.js')

Query Parameters

select *
from jdbc('ch-server?datasource_column&max_rows=1&fetch_size=1&one=1&two=2',
    'select {{one}} union all select {{ two }}')

Query result:

┌─datasource─┬─1─┐
│ ch-server  │ 1 │
└────────────┴───┘

JDBC Table

drop table if exists system.test;
create table system.test (
    a String,
    b UInt8
) engine=JDBC('ch-server', '', 'select user as a, is_initial_query as b from system.processes');

JDBC Dictionary

drop dictionary if exists system.dict_test;
create dictionary system.dict_test
(
    b UInt64 DEFAULT 0,
    a String
) primary key b
SOURCE(CLICKHOUSE(HOST 'localhost' PORT 9000 USER 'default' TABLE 'test' DB 'system'))
LIFETIME(MIN 82800 MAX 86400)
LAYOUT(FLAT());

Mutation

-- use query parameter
select * from jdbc('ch-server?mutation', 'drop table if exists system.test_table');
select * from jdbc('ch-server?mutation', 'create table system.test_table(a String, b UInt8) engine=Memory()');
select * from jdbc('ch-server?mutation', 'insert into system.test_table values(''a'', 1)');
select * from jdbc('ch-server?mutation', 'truncate table system.test_table');

-- use JDBC table engine
drop table if exists system.test_table;
create table system.test_table (
    a String,
    b UInt8
) engine=Memory();

drop table if exists system.jdbc_table;
create table system.jdbc_table (
    a String,
    b UInt8
) engine=JDBC('ch-server?batch_size=1000', 'system', 'test_table');

insert into system.jdbc_table(a, b) values('a', 1);

select * from system.test_table;

Query result:

┌─a─┬─b─┐
│ a │ 1 │
└───┴───┘

Monitoring

You can use Prometheus to monitor metrics exposed by JDBC bridge.
```
curl -v http://jdbc-bridge:9019/metrics
```

Configuration

JDBC Driver

By default, all JDBC drivers should be placed under drivers directory. You can override that by customizing driverUrls in datasource configuration file. For example:

{
    "testdb": {
        "driverUrls": [
            "drivers/mariadb",
            "D:\\drivers\\mariadb",
            "/mnt/d/drivers/mariadb",
            "https://repo1.maven.org/maven2/org/mariadb/jdbc/mariadb-java-client/2.7.4/mariadb-java-client-2.7.4.jar"
        ],
        "driverClassName": "org.mariadb.jdbc.Driver",
        ...
    }
}

Named Data Source

By default, named datasource is defined in configuration file in JSON format under config/datasources directory. You may check examples at misc/quick-start/jdbc-bridge/config/datasources. If you use modern editors like VSCode, you may find it's helpful to use JSON schema for validation and smart autocomplete.
Saved Query

Saved queries and scripts are under scripts directory by default. For example: show-query-logs.sql.
Named Query

Similar as named datasource, named queries are JSON configuration files under config/queries. You may refer to examples at misc/quick-start/jdbc-bridge/config/queries.
Logging

You can customize logging configuration in logging.properties.
Vert.x

If you're familiar with Vert.x, you can customize its configuration by changing config/httpd.json and config/vertx.json.

Query Parameters

All supported query parameters can be found at here. datasource_column=true can be simplied as datasource_column, for example:

select * from jdbc('ch-server?datasource_column=true', 'select 1')

select * from jdbc('ch-server?datasource_column', 'select 1')

Timeout

Couple of timeout settings you should be aware of:
1. datasource timeout, for example: max_execution_time in MariaDB
2. JDBC driver timeout, for example: connectTimeout and socketTimeout in MariaDB Connector/J
3. JDBC bridge timeout, for examples: queryTimeout in config/server.json, and maxWorkerExecuteTime in config/vertx.json
4. ClickHouse timeout like max_execution_time, keep_alive_timeout and http_receive_timeout etc.
5. Client timeout, for example: socketTimeout in ClickHouse JDBC driver

Migration

Upgrade to 2.x

2.x is a complete re-write not fully compatible with older version. You'll have to re-define your datasources and update your queries accordingly.

Build

You can use Maven to build ClickHouse JDBC bridge, for examples:

git clone https://github.com/ClickHouse/clickhouse-jdbc-bridge.git
cd clickhouse-jdbc-bridge
# compile and run unit tests
mvn -Prelease verify
# release shaded jar, rpm and debian packages
mvn -Prelease package

In order to build docker images:

git clone https://github.com/ClickHouse/clickhouse-jdbc-bridge.git
cd clickhouse-jdbc-bridge
docker build -t my/clickhouse-jdbc-bridge .
# or if you want to build the all-ine-one image
docker build --build-arg revision=20.9.3 -f all-in-one.Dockerfile -t my/clickhouse-all-in-one .

Develop

JDBC bridge is extensible. You may take ConfigDataSource and ScriptDataSource as examples to create your own extension.

An extension for JDBC bridge is basically a Java class with 3 optional parts:

Extension Name

By default, extension class name will be treated as name for the extension. However, you can declare a static member in your extension class to override that, for instance:
```
public static final String EXTENSION_NAME = "myExtension";
```
Initialize Method

Initialize method will be called once and only once at the time when loading your extension, for example:
```
public static void initialize(ExtensionManager manager) {
    ...
}
```
Instantiation Method

In order to create instance of your extension, in general you should define a static method like below so that JDBC bridge knows how(besides walking through all possible constructors):
```
public static MyExtension newInstance(Object... args) {
    ...
}
```

Assume your extension class is com.mycompany.MyExtension, you can load it into JDBC bridge by:

put your extension package(e.g. my-extension.jar) and required dependencies under extensions directory
update server.json by adding your extension, for example

    ...
    "extensions": [
        ...
        {
            "class": "com.mycompany.MyExtension"
        }
    ]
    ...

Note: order of the extension matters. The first NamedDataSource extension will be set as default for all named datasources.

Performance

Below is a rough performance comparison to help you understand overhead caused by JDBC bridge as well as its stability. MariaDB, ClickHouse, and JDBC bridge are running on separated KVMs. ApacheBench(ab) is used on another KVM to simulate 20 concurrent users to issue same query 100,000 times after warm-up. Please refer to this in order to setup test environment and run tests by yourself.

Test Case	Time Spent(s)	Throughput(#/s)	Failed Requests	Min(ms)	Mean(ms)	Median(ms)	Max(ms)
clickhouse_ping	801.367	124.79	0	1	160	4	1,075
jdbc-bridge_ping	804.017	124.38	0	1	161	10	3,066
clickhouse_url(clickhouse)	801.448	124.77	3	3	160	8	1,077
clickhouse_url(jdbc-bridge)	811.299	123.26	446	3	162	10	3,066
clickhouse_constant-query	797.775	125.35	0	1	159	4	1,077
clickhouse_constant-query(mysql)	1,598.426	62.56	0	7	320	18	2,049
clickhouse_constant-query(remote)	802.212	124.66	0	2	160	8	3,073
clickhouse_constant-query(url)	801.686	124.74	0	3	160	11	1,123
clickhouse_constant-query(jdbc)	925.087	108.10	5,813	14	185	75	4,091
clickhouse(patched)_constant-query(jdbc)	833.892	119.92	1,577	10	167	51	3,109
clickhouse(patched)_constant-query(jdbc-dual)	846.403	118.15	3,021	8	169	50	3,054
clickhouse_10k-rows-query	854.886	116.97	0	12	171	99	1,208
clickhouse_10k-rows-query(mysql)	1,657.425	60.33	0	28	331	123	2,228
clickhouse_10k-rows-query(remote)	854.610	117.01	0	12	171	99	1,201
clickhouse_10k-rows-query(url)	853.292	117.19	5	23	171	105	2,026
clickhouse_10k-rows-query(jdbc)	1,483.565	67.41	11,588	66	297	206	2,051
clickhouse(patched)_10k-rows-query(jdbc)	1,186.422	84.29	6,632	61	237	184	2,021
clickhouse(patched)_10k-rows-query(jdbc-dual)	1,080.676	92.53	4,195	65	216	180	2,013

Note: clickhouse(patched) is a patched version of ClickHouse server by disabling XDBC bridge health check. jdbc-dual on the other hand means dual instances of JDBC bridge managed by docker swarm on same KVM(due to limited resources ;).

Test Case	(Decoded) URL
clickhouse_ping	`http://ch-server:8123/ping`
jdbc-bridge_ping	`http://jdbc-bridge:9019/ping`
clickhouse_url(clickhouse)	`http://ch-server:8123/?query=select * from url('http://ch-server:8123/ping', CSV, 'results String')`
clickhouse_url(jdbc-bridge)	`http://ch-server:8123/?query=select * from url('http://jdbc-bridge:9019/ping', CSV, 'results String')`
clickhouse_constant-query	`http://ch-server:8123/?query=select 1`
clickhouse_constant-query(mysql)	`http://ch-server:8123/?query=select * from mysql('mariadb:3306', 'test', 'constant', 'root', 'root')`
clickhouse_constant-query(remote)	`http://ch-server:8123/?query=select * from remote('ch-server:9000', system.constant, 'default', '')`
clickhouse_constant-query(url)	`http://ch-server:8123/?query=select * from url('http://ch-server:8123/?query=select 1', CSV, 'results String')`
clickhouse_constant-query(jdbc)	`http://ch-server:8123/?query=select * from jdbc('mariadb', 'constant')`
clickhouse_10k-rows-query	`http://ch-server:8123/?query=select 1`
clickhouse_10k-rows-query(mysql)	`http://ch-server:8123/?query=select * from mysql('mariadb:3306', 'test', '10k_rows', 'root', 'root')`
clickhouse_10k-rows-query(remote)	`http://ch-server:8123/?query=select * from remote('ch-server:9000', system.10k_rows, 'default', '')`
clickhouse_10k-rows-query(url)	`http://ch-server:8123/?query=select * from url('http://ch-server:8123/?query=select * from 10k_rows', CSV, 'results String')`
clickhouse_10k-rows-query(jdbc)	`http://ch-server:8123/?query=select * from jdbc('mariadb', 'small-table')`

clickhouse-jdbc-bridge's People

Contributors

Stargazers

Watchers

Forkers

santyagoseaman zhoushineyoung iamnotgay frankegoesdown tribein ramazanpolat nauu arenadata guishijin romankagan zhangrenhua viokuma junneyang 91team iceyung ssavva05 imperio-wxm ailsexixi shchelchkov zhicwu russ168 meijies clickhouse-java xiaodiandian zxf gongstring jfischerlaunch misselvexu zoujianchao 2011aad cjjhust lizu18xz danrufus jjwygjj ai2hub isgasho yingyingqiqi databaseleague siao-pin init-cerebra miv david0718 isabella232 jamesmaidment odenio diggid4ever alexcd90 lbvffvbl ruslanen abner-guo polymedia mehanizm baiguidong puzzled-stone maxnilz tespkg etoakor zvtrung zz412000428

clickhouse-jdbc-bridge's Issues

Cannot build via docker

Hi!

I tried build from master branch ang got error.

All output :

[INFO]
[INFO] Results:
[INFO]
[ERROR] Failures:
[ERROR] ExpandedUrlClassLoaderTest.testClassLoaderWithOldAndNewClass:176->testLoadClassAndMethod:83 expected [null] but found [public boolean ru.yandex.clickhouse.settings.ClickHouseProperties.isUsePathAsDb()]
[INFO]
[ERROR] Tests run: 42, Failures: 1, Errors: 0, Skipped: 0
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 01:06 min
[INFO] Finished at: 2021-01-02T19:03:01Z
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.apache.maven.plugins:maven-surefire-plugin:2.22.2:test (default-test) on project clickhouse-jdbc-bridge: There are test failures.
[ERROR]
[ERROR] Please refer to /app/target/surefire-reports for the individual test results.
[ERROR] Please refer to dump files (if any exist) [date].dump, [date]-jvmRun[N].dump and [date].dumpstream.
[ERROR] -> [Help 1]
[ERROR]
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR]
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoFailureException
The command '/bin/sh -c mvn -Drevision=${revision} package' returned a non-zero code: 1

Best regards!

Does 2.0 support connecting to SparkSQL ?

DB::Exception: jdbc-bridge is not running. Please, start it manually.

Recently I executed the "insert into from " on Click House client，it showed that jdbc-bridge is not running.In fact ,I already ran it.

Exception thrown after insertion

How to get this fixed?

Add docs about production ready connect clickhouse in docker with jdbc bridge

due to: SQLState(08001) VendorCode(0) No suitable driver found for jdbc:mysql://

[root@middleware ~]# java -jar ./clickhouse-jdbc-bridge-2.0.0-SNAPSHOT.jar /root/mysql-connector-java-5.1.44.jar
2020-12-09 17:16:36.036 [main] [INFO ] {Utils:837} - Loading JSON from file [config/vertx.json]...
2020-12-09 17:16:36.036 [main] [WARN ] {Utils:846} - Failed to load JSON from file config/vertx.json
2020-12-09 17:16:36.036 [vert.x-eventloop-thread-0] [INFO ] {Utils:837} - Loading JSON from file [config/server.json]...
2020-12-09 17:16:36.036 [vert.x-eventloop-thread-0] [WARN ] {Utils:846} - Failed to load JSON from file config/server.json
2020-12-09 17:16:36.036 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:531} - Registering consumer to monitor configuration file(s) at [config/datasources]
2020-12-09 17:16:36.036 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:531} - Registering consumer to monitor configuration file(s) at [config/schemas]
2020-12-09 17:16:36.036 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:531} - Registering consumer to monitor configuration file(s) at [config/queries]
2020-12-09 17:16:36.036 [vert.x-eventloop-thread-0] [INFO ] {DefaultDataSourceManager:196} - Registering new type of datasource: [jdbc] -> [ru.yandex.clickhouse.jdbcbridge.impl.JdbcDataSource]
2020-12-09 17:16:36.036 [vert.x-eventloop-thread-0] [INFO ] {DefaultDataSourceManager:200} - Default datasource type is set to [jdbc]
2020-12-09 17:16:36.036 [vert.x-eventloop-thread-0] [INFO ] {DefaultDataSourceManager:196} - Registering new type of datasource: [config] -> [ru.yandex.clickhouse.jdbcbridge.impl.ConfigDataSource]
2020-12-09 17:16:36.036 [vert.x-eventloop-thread-0] [INFO ] {DefaultDataSourceManager:196} - Registering new type of datasource: [script] -> [ru.yandex.clickhouse.jdbcbridge.impl.ScriptDataSource]
2020-12-09 17:16:36.036 [vert.x-eventloop-thread-0] [INFO ] {Utils:837} - Loading JSON from file [config/httpd.json]...
2020-12-09 17:16:36.036 [vert.x-eventloop-thread-0] [WARN ] {Utils:846} - Failed to load JSON from file config/httpd.json
2020-12-09 17:16:36.036 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:220} - Starting web server...
2020-12-09 17:16:36.036 [vert.x-worker-thread-1] [INFO ] {DefaultDataSourceManager:262} - No datasource configuration found
2020-12-09 17:16:36.036 [vert.x-worker-thread-1] [INFO ] {DefaultQueryManager:77} - No schema configuration found
2020-12-09 17:16:36.036 [vert.x-worker-thread-1] [INFO ] {DefaultQueryManager:76} - No query configuration found
2020-12-09 17:16:37.037 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:224} - Server http://0.0.0.0:9019 started in 803 ms
2020-12-09 17:16:43.043 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:235} - [/ping] Context:
{__body-handled=true}
2020-12-09 17:16:43.043 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:236} - [/ping] Headers:
Host: 192.168.106.102
Connection: Close

2020-12-09 17:16:43.043 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:237} - [/ping] Parameters:

2020-12-09 17:16:43.043 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:235} - [/] Context:
{__body-handled=true}
2020-12-09 17:16:43.043 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:236} - [/] Headers:
Host: 192.168.106.102
Transfer-Encoding: chunked
Connection: Close

2020-12-09 17:16:43.043 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:237} - [/] Parameters:
connection_string: jdbc:mysql://xxx.xxx.xxx.xxx:3306/test?user=root&password=xxxxxxx
columns: columns format version: 1
2 columns:
int_id Int32
float Float32

max_block_size: 65505

2020-12-09 17:16:43.043 [vert.x-worker-thread-5] [DEBUG] {JdbcBridgeVerticle:342} - Generated query:
SELECT int_id, float FROM test.test
Normalized query:
test
2020-12-09 17:16:43.043 [vert.x-worker-thread-5] [DEBUG] {ResponseWriter:44} - Start Time=1607505403823, Timeout=-1000, Max Block Size=65505
2020-12-09 17:16:43.043 [vert.x-worker-thread-5] [INFO ] {NamedDataSource:494} - Executing query(schema=[test]):
SELECT int_id, float FROM test.test
2020-12-09 17:16:43.043 [vert.x-eventloop-thread-0] [ERROR] {JdbcBridgeVerticle:262} - Failed to respond
ru.yandex.clickhouse.jdbcbridge.core.DataAccessException: Failed to access [jdbc:mysql://xxx.xxx.xxx.xxx:3306/test?user=root&password=xxxxxxx] due to: SQLState(08001) VendorCode(0) No suitable driver found for jdbc:mysql://xxx.xxx.xxx.xxx:3306/test?user=root&password=xxxxxxx
at ru.yandex.clickhouse.jdbcbridge.impl.JdbcDataSource.writeQueryResult(JdbcDataSource.java:586)
at ru.yandex.clickhouse.jdbcbridge.core.NamedDataSource.executeQuery(NamedDataSource.java:506)
at ru.yandex.clickhouse.jdbcbridge.JdbcBridgeVerticle.lambda$handleQuery$5(JdbcBridgeVerticle.java:392)
at ru.yandex.clickhouse.jdbcbridge.internal.vertx.core.impl.ContextImpl.lambda$executeBlocking$2(ContextImpl.java:313)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at ru.yandex.clickhouse.jdbcbridge.internal.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: No suitable driver found for jdbc:mysql://xxx:3306/test?user=root&password=xxxxxxx
at java.sql.DriverManager.getConnection(DriverManager.java:689)
at java.sql.DriverManager.getConnection(DriverManager.java:270)
at ru.yandex.clickhouse.jdbcbridge.impl.JdbcDataSource.getConnection(JdbcDataSource.java:357)
at ru.yandex.clickhouse.jdbcbridge.impl.JdbcDataSource.writeQueryResult(JdbcDataSource.java:567)
... 7 more

Beyond 2.0, Clickhouse-Data-Bridge

I've been using clickhouse-jdbc-bridge v1.0 for a long time. It helps me tremendously. A couple of days ago, I wanted to build a new Clickhouse cluster that uses the new version of the clickhouse-jdbc-bridge but the 2.0 looks like too much feature squeezed into one product that makes it hard to grasp, especially for newcomers. In terms of usage and features, it may seem pretty obvious for old users of the clickhouse-jdbc-bridge but believe me, it is more complicated than it sounds. Remember that clickhouse-jdbc-bridge started to act as a proxy between Clickhouse and other JDBC databases. But after 2.0 release, it is no more limited to JDBC. It evolved in a way that makes it a general-purpose data fetching solution for Clickhouse. Therefore this naming it as "jdbc-bridge" seems inaccurate and incomplete.

To make it more clear for users and also make it easily extendible, understandable, and usable, here I propose some major changes to this product.

I propose to release a new version (v3.0) with a new name and clear description like this:

The new version is ClickHouse Data Bridge and it is v3.0.
It contains a number of bridges that can be used to transfer data between Clickhouse and other products.
JDBC bridge is just one of them. It is used to transfer data between Clickhouse and other JDBC compliant databases. It is currently just a simplex data bridge, which means the data transfer is uni-directional. Therefore it can only transfer data from JDBC compliant databases to Clickhouse. But it has the potential to be a duplex bridge, which will allow users to also transfer data from Clickhouse to JDBC compliant databases. In fact, the JDBC bridge supports INSERT to write data TO JDBC databases but it is work-in-progress.
Another well-known bridge is the Native Bridge, which connects Clickhouse to another Clickhouse. Clickhouse-server has a built-in duplex Native bridge, which is implemented as a table function (remote and remoteSecure)
We also have JavaScript bridge, which can be used to run JS code and get the result.
URL bridge is used to transfer data between Clickhouse and an HTTP server (this is also implemented in Clickhouse-server code as URL table engine).
REST bridge is a specialized kinf of URL bridge, which follows common REST standards to transfer data between Clickhouse and REST services.
Plugin architecture helps user to write their own bridge with Java and make it a .jar file to plug into ClickHouse Data Bridge.

I believe naming is much more important than we think. Because naming things properly makes it easy to distinguish things and understand the difference. What do you think @alex-krash , @alexey-milovidov ?

Could community add some wiki about the setting of SQL Server to ClickHouse-jdbc-bridge

Could community add some wiki about the setting of SQL Server to ClickHouse-jdbc-bridge?Plz

Could you provide a RPM Package？

In wiki page，there is an example

sudo dpkg -i target/clickhouse-jdbc-bridge.deb
Could you provide a RPM package?

jdbc-bridge seems broken with latest CH release (21.5.5.12)

I am not sure if this should be posted in this repo or the main clickhouse repository (I did post an issue there as well. I will close or the other depending what I hear)?

After upgrading my docker-compose file to the lastest version of clickhouse, the jdbc-bridge no longer works. Every query against it returns the same type of error:

SQL Error [86]: ClickHouse exception, code: 86, host: ***.***.com, port: 8123; Code: 86, e.displayText() = DB::Exception: Received error from remote server /?connection_string=&max_block_size=65505. HTTP status code: 500 Internal Server Error, body: At least one column is needed. (version 21.5.5.12 (official build))

If I downgrade clickhouse to 21.4.7.3 everything works again.

Here is the full stack trace in case it helps:

jdbc-bridge_1  | 2021-05-24 21:30:20.020 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:288} - [/columns_info] Parameters:
jdbc-bridge_1  | connection_string:
jdbc-bridge_1  | table: show datasources
jdbc-bridge_1  | external_table_functions_use_nulls: true
jdbc-bridge_1  |
jdbc-bridge_1  | 2021-05-24 21:30:20.020 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:359} - Raw query:
jdbc-bridge_1  | show datasources
jdbc-bridge_1  | 2021-05-24 21:30:20.020 [vert.x-eventloop-thread-0] [DEBUG] {NamedDataSource:438} - Inferring columns: schema=[], query=[show datasources]
jdbc-bridge_1  | 2021-05-24 21:30:20.020 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:404} - Columns info:
jdbc-bridge_1  | [columns format version: 1
jdbc-bridge_1  | 10 columns:
jdbc-bridge_1  | `name` Nullable(String)
jdbc-bridge_1  | `is_alias` Nullable(UInt8)
jdbc-bridge_1  | `instance` Nullable(Int32)
jdbc-bridge_1  | `create_datetime` Nullable(DateTime)
jdbc-bridge_1  | `type` Nullable(String)
jdbc-bridge_1  | `parameters` Nullable(String)
jdbc-bridge_1  | `defaults` Nullable(String)
jdbc-bridge_1  | `custom_columns` Nullable(String)
jdbc-bridge_1  | `cache_usage` Nullable(String)
jdbc-bridge_1  | `pool_usage` Nullable(String)
jdbc-bridge_1  | ]
jdbc-bridge_1  | 2021-05-24 21:30:20.020 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:286} - [/ping] Context:
jdbc-bridge_1  | {__body-handled=true}
jdbc-bridge_1  | 2021-05-24 21:30:20.020 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:287} - [/ping] Headers:
jdbc-bridge_1  | Host: jdbc-bridge
jdbc-bridge_1  | Connection: Close
jdbc-bridge_1  |
jdbc-bridge_1  | 2021-05-24 21:30:20.020 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:288} - [/ping] Parameters:
jdbc-bridge_1  |
jdbc-bridge_1  | 2021-05-24 21:30:20.020 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:286} - [/ping] Context:
jdbc-bridge_1  | {__body-handled=true}
jdbc-bridge_1  | 2021-05-24 21:30:20.020 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:287} - [/ping] Headers:
jdbc-bridge_1  | Host: jdbc-bridge
jdbc-bridge_1  | Connection: Close
jdbc-bridge_1  |
jdbc-bridge_1  | 2021-05-24 21:30:20.020 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:288} - [/ping] Parameters:
jdbc-bridge_1  |
jdbc-bridge_1  | 2021-05-24 21:30:20.020 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:286} - [/identifier_quote] Context:
jdbc-bridge_1  | {__body-handled=true}
jdbc-bridge_1  | 2021-05-24 21:30:20.020 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:287} - [/identifier_quote] Headers:
jdbc-bridge_1  | Host: jdbc-bridge
jdbc-bridge_1  | Connection: Close
jdbc-bridge_1  |
jdbc-bridge_1  | 2021-05-24 21:30:20.020 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:288} - [/identifier_quote] Parameters:
jdbc-bridge_1  | connection_string:
jdbc-bridge_1  |
jdbc-bridge_1  | 2021-05-24 21:30:20.020 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:286} - [/] Context:
jdbc-bridge_1  | {__body-handled=true}
jdbc-bridge_1  | 2021-05-24 21:30:20.020 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:287} - [/] Headers:
jdbc-bridge_1  | Host: jdbc-bridge
jdbc-bridge_1  | Transfer-Encoding: chunked
jdbc-bridge_1  | Connection: Close
jdbc-bridge_1  |
jdbc-bridge_1  | 2021-05-24 21:30:20.020 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:288} - [/] Parameters:
jdbc-bridge_1  | connection_string:
jdbc-bridge_1  | max_block_size: 65505
jdbc-bridge_1  |
jdbc-bridge_1  | 2021-05-24 21:30:20.020 [vert.x-worker-thread-10] [DEBUG] {JdbcBridgeVerticle:439} - Generated query:
jdbc-bridge_1  |
jdbc-bridge_1  | Normalized query:
jdbc-bridge_1  |
jdbc-bridge_1  | 2021-05-24 21:30:20.020 [vert.x-worker-thread-10] [DEBUG] {ResponseWriter:45} - Start Time=1621891820242, Timeout=-1000, Max Block Size=65505
jdbc-bridge_1  | 2021-05-24 21:30:20.020 [vert.x-eventloop-thread-0] [ERROR] {JdbcBridgeVerticle:316} - Failed to respond
jdbc-bridge_1  | java.lang.IllegalArgumentException: At least one column is needed.
jdbc-bridge_1  |        at ru.yandex.clickhouse.jdbcbridge.core.TableDefinition.<init>(TableDefinition.java:86)
jdbc-bridge_1  |        at ru.yandex.clickhouse.jdbcbridge.core.TableDefinition.fromString(TableDefinition.java:371)
jdbc-bridge_1  |        at ru.yandex.clickhouse.jdbcbridge.core.QueryParser.getTable(QueryParser.java:116)
jdbc-bridge_1  |        at ru.yandex.clickhouse.jdbcbridge.JdbcBridgeVerticle.handleQuery(JdbcBridgeVerticle.java:464)
jdbc-bridge_1  |        at ru.yandex.clickhouse.jdbcbridge.JdbcBridgeVerticle$$Lambda$113/0x00000000b81097e0.handle(Unknown Source)
jdbc-bridge_1  |        at ru.yandex.clickhouse.jdbcbridge.internal.vertx.ext.web.impl.BlockingHandlerDecorator.lambda$handle$0(BlockingHandlerDecorator.java:48)
jdbc-bridge_1  |        at ru.yandex.clickhouse.jdbcbridge.internal.vertx.ext.web.impl.BlockingHandlerDecorator$$Lambda$207/0x00000000b850bd90.handle(Unknown Source)
jdbc-bridge_1  |        at ru.yandex.clickhouse.jdbcbridge.internal.vertx.core.impl.ContextImpl.lambda$executeBlocking$2(ContextImpl.java:313)
jdbc-bridge_1  |        at ru.yandex.clickhouse.jdbcbridge.internal.vertx.core.impl.ContextImpl$$Lambda$93/0x00000000b8091c40.run(Unknown Source)
jdbc-bridge_1  |        at ru.yandex.clickhouse.jdbcbridge.internal.vertx.core.impl.TaskQueue.run(TaskQueue.java:76)
jdbc-bridge_1  |        at ru.yandex.clickhouse.jdbcbridge.internal.vertx.core.impl.TaskQueue$$Lambda$76/0x00000000982df370.run(Unknown Source)
jdbc-bridge_1  |        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
jdbc-bridge_1  |        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
jdbc-bridge_1  |        at ru.yandex.clickhouse.jdbcbridge.internal.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
jdbc-bridge_1  |        at java.lang.Thread.run(Thread.java:823)

Here is my docker-compose file:

version: "2"

# Only 'ch-server' and 'jdbc-bridge' are mandatory.
# You may remove any db-xxx to save memory.
services:
  ch-server:
    image: yandex/clickhouse-server:21.5.5.12  #21.4.7.3
    ports:
       - 8123:8123
#       - 9000:9000
       - 9009:9009
    hostname: ch-server
    volumes:
      - ./ch-server/config:/etc/clickhouse-server/config.d
      #- ./ch-server/data:/var/lib/clickhouse
      #- ./ch-server/logs:/var/log/clickhouse
    mem_limit: 512m
    restart: always

  jdbc-bridge:
    image: yandex/clickhouse-jdbc-bridge:2.0.1
    hostname: jdbc-bridge
    # In general you don't need to define any environment variable
    # Below are all default settings just for demonstration
    environment:
      CONFIG_DIR: config # configuration directory
      HTTPD_CONFIG_FILE: httpd.json # httpd configuration file
      SERVER_CONFIG_FILE: server.json # server configuration file
      VERTX_CONFIG_FILE: vertx.json # vertx configuration file
      DATASOURCE_CONFIG_DIR: datasources # named datasource directory
      DRIVER_DIR: drivers # driver directory
      EXTENSION_DIR: extensions # extension directory
      QUERY_CONFIG_DIR: queries # named query directory
      CUSTOM_DRIVER_LOADER: "true" # whether use custom driver loader or not
      JDBC_BRIDGE_JVM_OPTS: # use CPU and memory allocated by container

    # You may want to keep datasources, queries, SQL scripts, and maybe drivers in a git repo
    volumes:
      - ./jdbc-bridge/config:/app/config
      - ./jdbc-bridge/drivers:/app/drivers
      - ./jdbc-bridge/scripts:/app/scripts
    mem_limit: 512m
    restart: always

Postgresql numeric data type not supportted

2020-05-14 10:58:24,428 [ HTTP Handler-17 ] {ColumnsInfoServlet} - Can not map SQL type 2 (NUMERIC) to ClickHouse
java.sql.SQLException: Can not map SQL type 2 (NUMERIC) to ClickHouse
at ru.yandex.clickhouse.jdbcbridge.db.clickhouse.ClickHouseConverter.getInstructionBySQLType(ClickHouseConverter.java:133)
at ru.yandex.clickhouse.jdbcbridge.db.clickhouse.ClickHouseConverter.getBySQLType(ClickHouseConverter.java:107)
at ru.yandex.clickhouse.jdbcbridge.db.clickhouse.ClickHouseConverter.getColumnsDDL(ClickHouseConverter.java:154)
at ru.yandex.clickhouse.jdbcbridge.servlet.ColumnsInfoServlet.doPost(ColumnsInfoServlet.java:53)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:755)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1617)
at ru.yandex.clickhouse.jdbcbridge.servlet.RequestLogger.doFilter(RequestLogger.java:32)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1604)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:545)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:190)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:485)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.server.Server.handle(Server.java:500)
at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:383)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:547)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:375)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:270)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:388)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:806)
at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938)
at java.lang.Thread.run(Thread.java:745)

Can't select tables with big number of columns

When selecting tables with a lot of columns you get "414 Request-URI Too Long" error. I understand it's bad idea to select all columns, but sometimes we need to look at all columns for first few rows.

Pushdowns ; Views;

Good day!

Pushdown is not supported and query may execute twice because of type inferring

Is there issue or limitation?

create view my_view as select * from jdbc('connection', 'select * from table')
Query 1:
select * from my_view where a = 1 limit 100
Query 2:
select * from jdbc('connection', 'select * from table where a = 1') limit 100

Are the queries equals?

Is there a way, in the current implementation, to do some kind of substitution :

select * from jdbc('conn', 'select * from table where a = {{value}} ?

Best regards!

clickhouse-jdbc-bridge-2.0.0 No suitable driver found for jdbc

[root@master clickhouse-jdbc-bridge]# java -jar clickhouse-jdbc-bridge-2.0.0.jar --driver-path /usr/local/clickhouse-jdbc-bridge/ --listen-host 0.0.0.0
2020-12-06 23:37:53.053 [main] [INFO ] {Utils:837} - Loading JSON from file [config/vertx.json]...
2020-12-06 23:37:53.053 [main] [WARN ] {Utils:846} - Failed to load JSON from file config/vertx.json
2020-12-06 23:37:54.054 [vert.x-eventloop-thread-0] [INFO ] {Utils:837} - Loading JSON from file [config/server.json]...
2020-12-06 23:37:54.054 [vert.x-eventloop-thread-0] [WARN ] {Utils:846} - Failed to load JSON from file config/server.json
2020-12-06 23:37:54.054 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:531} - Registering consumer to monitor configuration file(s) at [config/datasources]
2020-12-06 23:37:54.054 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:531} - Registering consumer to monitor configuration file(s) at [config/schemas]
2020-12-06 23:37:54.054 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:531} - Registering consumer to monitor configuration file(s) at [config/queries]
2020-12-06 23:37:54.054 [vert.x-eventloop-thread-0] [INFO ] {DefaultDataSourceManager:196} - Registering new type of datasource: [jdbc] -> [ru.yandex.clickhouse.jdbcbridge.impl.JdbcDataSource]
2020-12-06 23:37:54.054 [vert.x-eventloop-thread-0] [INFO ] {DefaultDataSourceManager:200} - Default datasource type is set to [jdbc]
2020-12-06 23:37:54.054 [vert.x-eventloop-thread-0] [INFO ] {DefaultDataSourceManager:196} - Registering new type of datasource: [config] -> [ru.yandex.clickhouse.jdbcbridge.impl.ConfigDataSource]
2020-12-06 23:37:54.054 [vert.x-eventloop-thread-0] [INFO ] {DefaultDataSourceManager:196} - Registering new type of datasource: [script] -> [ru.yandex.clickhouse.jdbcbridge.impl.ScriptDataSource]
2020-12-06 23:37:54.054 [vert.x-eventloop-thread-0] [INFO ] {Utils:837} - Loading JSON from file [config/httpd.json]...
2020-12-06 23:37:54.054 [vert.x-eventloop-thread-0] [WARN ] {Utils:846} - Failed to load JSON from file config/httpd.json
2020-12-06 23:37:55.055 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:220} - Starting web server...
2020-12-06 23:37:56.056 [vert.x-worker-thread-1] [INFO ] {DefaultDataSourceManager:262} - No datasource configuration found
2020-12-06 23:37:56.056 [vert.x-worker-thread-1] [INFO ] {DefaultQueryManager:77} - No schema configuration found
2020-12-06 23:37:56.056 [vert.x-worker-thread-1] [INFO ] {DefaultQueryManager:76} - No query configuration found
2020-12-06 23:37:56.056 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:224} - Server http://0.0.0.0:9019 started in 2383 ms
2020-12-06 23:38:03.003 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:235} - [/ping] Context:
{__body-handled=true}
2020-12-06 23:38:03.003 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:236} - [/ping] Headers:
Host: 127.0.0.1
Connection: Close

2020-12-06 23:38:03.003 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:237} - [/ping] Parameters:

2020-12-06 23:38:03.003 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:235} - [/] Context:
{__body-handled=true}
2020-12-06 23:38:03.003 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:236} - [/] Headers:
Host: 127.0.0.1
Transfer-Encoding: chunked
Connection: Close

2020-12-06 23:38:03.003 [vert.x-eventloop-thread-0] [DEBUG] {JdbcBridgeVerticle:237} - [/] Parameters:
connection_string: jdbc:mysql://172.24.140.162:3306/mydb?user=root&password=**
columns: columns format version: 1
4 columns:
int_id Int32
int_nullable Nullable(Int32)
float Float32
float_nullable Nullable(Float32)

max_block_size: 65505

2020-12-06 23:38:03.003 [vert.x-worker-thread-4] [DEBUG] {JdbcBridgeVerticle:342} - Generated query:
SELECT int_id, int_nullable, float, float_nullable FROM mydb.test
Normalized query:
test
2020-12-06 23:38:03.003 [vert.x-worker-thread-4] [DEBUG] {ResponseWriter:44} - Start Time=1607269083911, Timeout=-1000, Max Block Size=65505
2020-12-06 23:38:03.003 [vert.x-worker-thread-4] [INFO ] {NamedDataSource:494} - Executing query(schema=[mydb]):
SELECT int_id, int_nullable, float, float_nullable FROM mydb.test
2020-12-06 23:38:03.003 [vert.x-eventloop-thread-0] [ERROR] {JdbcBridgeVerticle:262} - Failed to respond
ru.yandex.clickhouse.jdbcbridge.core.DataAccessException: Failed to access [jdbc:mysql://172.24.140.162:3306/mydb?user=root&password=] due to: SQLState(08001) VendorCode(0) No suitable driver found for jdbc:mysql://172.24.140.162:3306/mydb?user=root&password=
at ru.yandex.clickhouse.jdbcbridge.impl.JdbcDataSource.writeQueryResult(JdbcDataSource.java:586)
at ru.yandex.clickhouse.jdbcbridge.core.NamedDataSource.executeQuery(NamedDataSource.java:506)
at ru.yandex.clickhouse.jdbcbridge.JdbcBridgeVerticle.lambda$handleQuery$5(JdbcBridgeVerticle.java:392)
at ru.yandex.clickhouse.jdbcbridge.internal.vertx.core.impl.ContextImpl.lambda$executeBlocking$2(ContextImpl.java:313)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at ru.yandex.clickhouse.jdbcbridge.internal.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: No suitable driver found for jdbc:mysql://172.24.140.162:3306/mydb?user=root&password=**
at java.sql.DriverManager.getConnection(DriverManager.java:689)
at java.sql.DriverManager.getConnection(DriverManager.java:270)
at ru.yandex.clickhouse.jdbcbridge.impl.JdbcDataSource.getConnection(JdbcDataSource.java:357)
at ru.yandex.clickhouse.jdbcbridge.impl.JdbcDataSource.writeQueryResult(JdbcDataSource.java:567)
... 7 more

Failed to insert default value

I have a jdbc source table without 'operateTime' datetime column and a destination clickhouse table with 'operateTime' column with default value defined like below:

operatetime DateTime DEFAULT now()

The question is when I insert data from select like following:
INSERT INTO ck_table SELECT *
FROM jdbc('msjdbc', 'select * from dbo.jdbc_table')

It always brings out "Code: 20. DB::Exception: Received from 192.168.1.38:9000. DB::Exception: Number of columns doesn't match."

License issue

Happy new year @alex-krash!

Quick question: is this released under the terms of Apache License 2.0 like ClickHouse server? I forked your repo and is about to make some changes(e.g. connection pooling, adhoc query etc.). Not sure if we're allowed to use this internally and if there's any concern of publishing the changes on GitHub.

Package into RPM

Looks like this plugin might be applicable.
https://github.com/mojohaus/rpm-maven-plugin

mysql DECIMAL not support

java.sql.SQLException: Can not map SQL type 3 (DECIMAL) to ClickHouse
at ru.yandex.clickhouse.jdbcbridge.db.clickhouse.ClickHouseConverter.getInstructionBySQLType(ClickHouseConverter.java:91)
at ru.yandex.clickhouse.jdbcbridge.db.clickhouse.ClickHouseConverter.getBySQLType(ClickHouseConverter.java:66)
at ru.yandex.clickhouse.jdbcbridge.db.clickhouse.ClickHouseConverter.getColumnsDDL(ClickHouseConverter.java:107)
at ru.yandex.clickhouse.jdbcbridge.servlet.ColumnsInfoServlet.doPost(ColumnsInfoServlet.java:53)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:865)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1655)
at ru.yandex.clickhouse.jdbcbridge.servlet.RequestLogger.doFilter(RequestLogger.java:32)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:533)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:205)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:473)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:144)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
at org.eclipse.jetty.server.Server.handle(Server.java:503)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:364)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:260)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:305)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:118)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:333)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:310)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:168)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:126)
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:366)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:765)
at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:683)
at java.lang.Thread.run(Thread.java:748)

Postgresql array type mapping support?

An exception was raised while trying to select some rows from a PostgreSQL table:

java.sql.SQLException: Can not map SQL type 2003 (ARRAY) to ClickHouse

even if I just a non-array column.

I'm assuming the ARRAY type is not support as of today, as I compiled the jar from the git repo master branch.

Clickhouse ver: 20.9.3.45
OS: Debian 10
openjdk version "1.8.0_265"
OpenJDK Runtime Environment (AdoptOpenJDK)(build 1.8.0_265-b01)
OpenJDK 64-Bit Server VM (AdoptOpenJDK)(build 25.265-b01, mixed mode)
JDBC drive: postgresql-42.2.18.jar

MS SQL Server

Does this library support MS sql server?

Stop JDBC bridge with error: !STOPPED

bash-4.4# java -jar clickhouse-jdbc-bridge-1.0.1.jar --driver-path /jdbc-drivers --datasources /datasources.properties
2020-08-18 06:02:51,889 [ main ] {JdbcBridge} - Starting jdbc-bridge
2020-08-18 06:02:51,921 [ main ] {ClickHouseDriver} - Driver registered
2020-08-18 06:02:51,926 [ main ] {JdbcDriverLoader} - Looking for driver files in /jdbc-drivers
2020-08-18 06:02:51,991 [ main ] {JdbcDriverLoader} - Found 5 JAR file
2020-08-18 06:02:51,991 [ main ] {JdbcDriverLoader} - Looking for driver in file /jdbc-drivers/h2-1.4.197.jar
2020-08-18 06:02:51,994 [ main ] {JdbcDriverLoader} - Registered driver org.h2.Driver
2020-08-18 06:02:51,995 [ main ] {JdbcDriverLoader} - Looking for driver in file /jdbc-drivers/mysql-connector-java-8.0.13.jar
2020-08-18 06:02:52,004 [ main ] {JdbcDriverLoader} - Registered driver com.mysql.cj.jdbc.Driver
2020-08-18 06:02:52,004 [ main ] {JdbcDriverLoader} - Looking for driver in file /jdbc-drivers/ojdbc7.jar
2020-08-18 06:02:52,098 [ main ] {JdbcDriverLoader} - Registered driver oracle.jdbc.OracleDriver
2020-08-18 06:02:52,098 [ main ] {JdbcDriverLoader} - Looking for driver in file /jdbc-drivers/postgresql-42.2.5.jar
2020-08-18 06:02:52,105 [ main ] {JdbcDriverLoader} - Registered driver org.postgresql.Driver
2020-08-18 06:02:52,105 [ main ] {JdbcDriverLoader} - Looking for driver in file /jdbc-drivers/sqlite-jdbc-3.23.1.jar
2020-08-18 06:02:52,107 [ main ] {JdbcDriverLoader} - Registered driver org.sqlite.JDBC
2020-08-18 06:02:52,108 [ main ] {BridgeConnectionManager} - Loading aliases from file /datasources.properties
2020-08-18 06:02:52,163 [ main ] {JdbcBridge} - Will bind to localhost/127.0.0.1:9019
2020-08-18 06:02:52,229 [ main ] {JdbcBridge} - Starting server
2020-08-18 06:02:52,274 [ main ] {JdbcBridge} - Stop JDBC bridge with error: !STOPPED

Complex data types support

Support complex data types like Array, Tuple and maybe nested object.

Can't run the bridge

When I run the bridge, it failed with the error:
2020-08-20 10:14:32,932 [ main ] {JdbcBridge} - Starting jdbc-bridge
2020-08-20 10:14:32,975 [ main ] {ClickHouseDriver} - Driver registered
2020-08-20 10:14:32,979 [ main ] {JdbcDriverLoader} - Looking for driver files in /var/lib/clickhouse-jdbc-bridge
2020-08-20 10:14:32,980 [ main ] {JdbcDriverLoader} - Found 1 JAR file
2020-08-20 10:14:32,980 [ main ] {JdbcDriverLoader} - Looking for driver in file /var/lib/clickhouse-jdbc-bridge/mysql-connector-java-5.1.49.jar
Exception in thread "main" java.lang.NoClassDefFoundError: java/sql/Driver
at java.base/java.lang.ClassLoader.defineClass1(Native Method)
at java.base/java.lang.ClassLoader.defineClass(ClassLoader.java:1017)
at java.base/java.security.SecureClassLoader.defineClass(SecureClassLoader.java:151)
at java.base/java.net.URLClassLoader.defineClass(URLClassLoader.java:514)
at java.base/java.net.URLClassLoader$1.run(URLClassLoader.java:422)
at java.base/java.net.URLClassLoader$1.run(URLClassLoader.java:416)
at java.base/java.security.AccessController.doPrivileged(AccessController.java:691)
at java.base/java.net.URLClassLoader.findClass(URLClassLoader.java:415)
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:589)
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:522)
at java.base/java.lang.Class.forName0(Native Method)
at java.base/java.lang.Class.forName(Class.java:427)
at java.base/java.util.ServiceLoader$LazyClassPathLookupIterator.nextProviderClass(ServiceLoader.java:1211)
at java.base/java.util.ServiceLoader$LazyClassPathLookupIterator.hasNextService(ServiceLoader.java:1222)
at java.base/java.util.ServiceLoader$LazyClassPathLookupIterator.hasNext(ServiceLoader.java:1266)
at java.base/java.util.ServiceLoader$2.hasNext(ServiceLoader.java:1301)
at java.base/java.util.ServiceLoader$3.hasNext(ServiceLoader.java:1386)
at ru.yandex.clickhouse.jdbcbridge.db.jdbc.JdbcDriverLoader.load(JdbcDriverLoader.java:61)
at ru.yandex.clickhouse.jdbcbridge.JdbcBridge.run(JdbcBridge.java:138)
at ru.yandex.clickhouse.jdbcbridge.JdbcBridge.main(JdbcBridge.java:67)
Caused by: java.lang.ClassNotFoundException: java.sql.Driver
at java.base/java.net.URLClassLoader.findClass(URLClassLoader.java:435)
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:589)
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:522)
... 20 more

It is really strange. The jdbc driver of mysql is from official site.

Get query from URL

With saved query, we can offload complex queries to a local file, which in general is managed by git. It would be easier to use if we take a step further by loading query or data from URL like shown below:

select * 
from jdbc('self', 'https://some.host/path/to/a.sql')

select * 
from jdbc('self?load_into_table=logs.raw_data', 'https://some.host/path/to/a.csv')

Failed to run clickhouse-server with clickhouse-jdbc-bridge-service in docker-compose.

This is my docker-compose.yml

version: "3"
services:
    clickhouse-server:
     image: yandex/clickhouse-server
     ports:
     - "8123:8123"
     - "9000:9000"
     - "9009:9009"

    clickhouse-jdbc-bridge:
        image: riftbit/clickhouse-jdbc-bridge-service
        ports:
          - '9019:9019'
       depends_on:
          - clickhouse-server

I put clickhouse-server and clickhouse-jdbc-bridge-service in the same docker-compose file, but got error from the clickhouse-server

jdbc-bridge is not running. Please, start it manually (version 19.17.2.4 (official build))

It seems the clickhouse-server cann't find the clickhouse-jdbc-bridge service.And how to configure clickhouse-server to looking for the clickhouse-jdbc-bridge

Add support of source database function as table

Tried to make following scenario work and didn't succeed.

PostgreSQL:
CREATE OR REPLACE FUNCTION tst(input_parameter VARCHAR) RETURNS TABLE (result VARCHAR)
AS 'SELECT $1::VARCHAR AS test;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

ClickHouse:
SELECT * FROM jdbc('postgresql://192.168.242.1:5431/?user=CR_TEST&password=CR', 'public', "tst('test')")

What made me think it might work?
Without function created CH raises an error: function public.tst(unknown) does not exist? Hint: No function matches the given name and argument types.
But when function exists I got: relation "public.tst('test')" does not exist.
Something is definitely trying to make a call to the entity/function.

load postgresql data failed

when I try to used sql below to load data from a greenplum(with postgresql driver) table to a clickhouse table

insert into t_cqaj_all select * from jdbc('datasource://gp190','db_ajzj','t_cqaj_all')

I got an error

Received exception from server (version 20.5.4):
Code: 33. DB::Exception: Received from localhost:9000. DB::Exception: Cannot read all data. Bytes read: 2. Bytes expected: 4.: While executing JDBC.

there are some error in clickhouse-jdbc-bridge log

2020-08-06 20:03:58,061 [ HTTP Handler-45 ] {QueryHandlerServlet} <ERROR> - Not a UInt32 value: -184924800
java.lang.IllegalStateException: Not a UInt32 value: -184924800
	at ru.yandex.clickhouse.util.ClickHouseRowBinaryStream.writeUInt32(ClickHouseRowBinaryStream.java:128)
	at ru.yandex.clickhouse.util.ClickHouseRowBinaryStream.writeDateTime(ClickHouseRowBinaryStream.java:151)
	at ru.yandex.clickhouse.jdbcbridge.db.clickhouse.ClickHouseConverter.lambda$static$7(ClickHouseConverter.java:76)
	at ru.yandex.clickhouse.jdbcbridge.db.clickhouse.ClickHouseFieldSerializer.serialize(ClickHouseFieldSerializer.java:34)
	at ru.yandex.clickhouse.jdbcbridge.db.clickhouse.ClickHouseRowSerializer.serialize(ClickHouseRowSerializer.java:22)
	at ru.yandex.clickhouse.jdbcbridge.servlet.QueryHandlerServlet.doPost(QueryHandlerServlet.java:60)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:755)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1617)
	at ru.yandex.clickhouse.jdbcbridge.servlet.RequestLogger.doFilter(RequestLogger.java:32)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1604)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:545)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:190)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:485)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
	at org.eclipse.jetty.server.Server.handle(Server.java:500)
	at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:383)
	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:547)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:375)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:270)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
	at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:388)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:806)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938)
	at java.lang.Thread.run(Thread.java:748)
2020-08-06 20:03:58,061 [ HTTP Handler-45 ] {HttpChannel} <WARN> - /
java.lang.IllegalStateException: COMMITTED
	at org.eclipse.jetty.server.HttpChannelState.sendError(HttpChannelState.java:893)
	at org.eclipse.jetty.server.Response.sendError(Response.java:430)
	at ru.yandex.clickhouse.jdbcbridge.servlet.QueryHandlerServlet.doPost(QueryHandlerServlet.java:65)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:755)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1617)
	at ru.yandex.clickhouse.jdbcbridge.servlet.RequestLogger.doFilter(RequestLogger.java:32)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1604)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:545)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:190)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:485)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
	at org.eclipse.jetty.server.Server.handle(Server.java:500)
	at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:383)
	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:547)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:375)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:270)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
	at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:388)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:806)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938)
	at java.lang.Thread.run(Thread.java:748)

the only numeric type in source table is int4, and the reference columns in target table are all set to Int64.

I'm confused that there is UInt32 no where, is it some thing wrong in the progress of clickhouse-jdbc-bridge?

source tables ddl:

CREATE TABLE db_ajzj.t_cqaj_all (
	c_bh bpchar(32) NULL,
	c_ajbh bpchar(32) NULL,
	c_ajzlbdm varchar(300) NULL,
	c_ajzlb varchar(300) NULL,
	c_jbfyid varchar(300) NULL,
	n_jbfybs int4 NULL,
	c_jbfy varchar(300) NULL,
	c_bh_cqaj bpchar(32) NULL,
	c_cqaj_ajzlbdm varchar(300) NULL,
	c_cqaj_ajzlb varchar(300) NULL,
	c_cqaj_jbfyid varchar(300) NULL,
	n_cqaj_jbfybs int4 NULL,
	c_cqaj_jbfy varchar(300) NULL,
	c_cqaj_ah varchar(300) NULL,
	c_cqaj_salydm varchar(300) NULL,
	c_cqaj_saly varchar(300) NULL,
	c_cqaj_laayid varchar(300) NULL,
	n_cqaj_laaybs int4 NULL,
	c_cqaj_laaymc varchar(300) NULL,
	dt_cqaj_lasj timestamp NULL,
	dt_cqaj_jasj timestamp NULL,
	c_cqaj_cbrid varchar(300) NULL,
	n_cqaj_cbrbs int4 NULL,
	c_cqaj_cbrmc varchar(300) NULL,
	c_cqaj_cbsptid varchar(300) NULL,
	n_cqaj_cbsptbs int4 NULL,
	c_cqaj_cbspt varchar(300) NULL,
	c_cqaj_jafsdm varchar(300) NULL,
	c_cqaj_jafs varchar(300) NULL,
	dt_cjsj timestamp NULL,
	dt_zhgxsj timestamp NULL,
	c_cqaj_jaayid varchar(300) NULL,
	n_cqaj_jaaybs int4 NULL,
	c_cqaj_jaaymc varchar(300) NULL,
	c_tqxaly varchar(300) NULL,
	dt_cqaj_sasj timestamp NULL,
	n_xh int4 NULL
)
DISTRIBUTED BY (c_ajbh);

target table ddl

CREATE TABLE default.t_cqaj_all
(
    `c_bh` Nullable(String),
    `c_ajbh` String,
    `c_ajzlbdm` Nullable(String),
    `c_ajzlb` Nullable(String),
    `c_jbfyid` Nullable(String),
    `n_jbfybs` Nullable(Int64),
    `c_jbfy` Nullable(String),
    `c_bh_cqaj` Nullable(String),
    `c_cqaj_ajzlbdm` Nullable(String),
    `c_cqaj_ajzlb` Nullable(String),
    `c_cqaj_jbfyid` Nullable(String),
    `n_cqaj_jbfybs` Nullable(Int64),
    `c_cqaj_jbfy` Nullable(String),
    `c_cqaj_ah` Nullable(String),
    `c_cqaj_salydm` Nullable(String),
    `c_cqaj_saly` Nullable(String),
    `c_cqaj_laayid` Nullable(String),
    `n_cqaj_laaybs` Nullable(Int64),
    `c_cqaj_laaymc` Nullable(String),
    `dt_cqaj_lasj` Nullable(DateTime),
    `dt_cqaj_jasj` Nullable(DateTime),
    `c_cqaj_cbrid` Nullable(String),
    `n_cqaj_cbrbs` Nullable(Int64),
    `c_cqaj_cbrmc` Nullable(String),
    `c_cqaj_cbsptid` Nullable(String),
    `n_cqaj_cbsptbs` Nullable(Int64),
    `c_cqaj_cbspt` Nullable(String),
    `c_cqaj_jafsdm` Nullable(String),
    `c_cqaj_jafs` Nullable(String),
    `dt_cjsj` Nullable(DateTime),
    `dt_zhgxsj` Nullable(DateTime),
    `c_cqaj_jaayid` Nullable(String),
    `n_cqaj_jaaybs` Nullable(Int64),
    `c_cqaj_jaaymc` Nullable(String),
    `c_tqxaly` Nullable(String),
    `dt_cqaj_sasj` Nullable(DateTime),
    `n_xh` Nullable(Int64)
)
ENGINE = MergeTree
ORDER BY c_ajbh
SETTINGS index_granularity = 8192;

Cache

Hello!

According to the documentation:
By default, any adhoc query passed to JDBC bridge will be executed twice. The first run is for type inferring, while the second for retrieving results. Although metadata will be cached(for up to 5 minutes by default), executing same query twice could be a problem - that's where schema comes into play.

I do not understand what parameter needed to increase?

In my case, chema never changes. Can I use "caching" queries like select * from table limit 10 periodically to get the schema in the cache?

Did I understand correctly that if select * - selects all columns then this cache will be valid for select col1, col2?

Can this reduce this overhead?

Best regards!

Table and schema reverse

String tableAndSchema = Stream.of(schema, table)
.filter(s -> !StringUtil.isBlank(s))
.map(s -> quote + s + quote)
.collect(Collectors.joining("."));

What's difference between clickhouse-jdbc and clickhouse-jdbc-bridge?

I am so puzzled why clickhouse adds another "clickhouse-jdbc-bridge" tool after providing a clickhouse-jdbc? For example I need transport SQL Server table data into clickhouse,which project is better choice。Thanks

support select sql

support
SELECT * FROM jdbc('jdbc:xx','xxx','select a,b,c from xxx where a>0 and rownum<1000')
Note the escape of the quote

UInt16 type data read error

When inserting data, readUInt8 is used to read UInt16 type data.

Can't select null field with presto jdbc ,because ResultSetMetaData.columnNullable is "unknow"

2019-05-15 14:37:31,804 [ HTTP Handler-27 ] {QueryHandlerServlet} - query is SELECT "id", "subject_id", "paper_id", "name", "detail", "exam_type", "region_level", "regions", "clazz_levels", "school_level", "school_ids", "apply_start_at", "apply_stop_at", "exam_start_at", "exam_stop_at", "correct_stop_at", "result_issue_at", "duration_minutes", "submit_after_minutes", "question_sort", "status", "agent_name", "agent_code", "file_url", "file_name", "creator_id", "created_at", "updated_at", "group_id", "teacher_id", "deleted_at", "papers", "ranks", "dt" FROM "dw_livecast"."bak_bdm_mongo_exam"
2019-05-15 14:37:32,351 [ HTTP Handler-27 ] {QueryHandlerServlet} -
java.lang.NullPointerException
at com.google.common.base.Preconditions.checkNotNull(Preconditions.java:212)
at ru.yandex.clickhouse.util.ClickHouseRowBinaryStream.writeString(ClickHouseRowBinaryStream.java:72)
at ru.yandex.clickhouse.jdbcbridge.db.clickhouse.ClickHouseConverter.lambda$static$13(ClickHouseConverter.java:58)
at ru.yandex.clickhouse.jdbcbridge.db.clickhouse.ClickHouseFieldSerializer.serialize(ClickHouseFieldSerializer.java:34)
at ru.yandex.clickhouse.jdbcbridge.db.clickhouse.ClickHouseRowSerializer.serialize(ClickHouseRowSerializer.java:22)
at ru.yandex.clickhouse.jdbcbridge.servlet.QueryHandlerServlet.doPost(QueryHandlerServlet.java:60)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:865)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1655)
at ru.yandex.clickhouse.jdbcbridge.servlet.RequestLogger.doFilter(RequestLogger.java:32)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:533)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:205)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:473)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:144)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
at org.eclipse.jetty.server.Server.handle(Server.java:503)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:364)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:260)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:305)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:118)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:333)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:310)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:168)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.produce(EatWhatYouKill.java:132)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:765)
at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:683)
at java.lang.Thread.run(Thread.java:748)

Can not map SQL type 3 (DECIMAL) to ClickHouse

Trying to use it to connect to IBM DB2.

Connection is successful but if the remote table has a column type of DECIMAL then we get Can not map SQL type 3 (DECIMAL) to ClickHouse..

Tried several cases, in all cases, if the table contains a column that is typed ad DECIMAL, this error pops up.

WORKAROUND:

Create a view that converts DECIMAL values to BIGINT.

Does 2.0 support connecting to oracle

When I connect to Oracle through clickhouse-jdbc-bridge2.0, it reports an error.Does 2.0 support connecting to oracle.
The details are as follows：
NamedDataSource [oracle] file oracle.json:
{
"$schema": "../datasource.jschema",
"oracle": {
"driverUrls": [
"/smartbiMpp/jdbc-bridge/config/datasources/drivers/oracle/oracle.jar"
],
"driverClassName": "oracle.jdbc.driver.OracleDriver",
"jdbcUrl": "jdbc:oracle:thin:@192.168.153.1:1521/orcl",
"username": "smartbi",
"password": "smartbi",
"maximumPoolSize": 5
}
}
sql:
select count(1) dd from jdbc('oracle', 'smartbi', 'test')
error log:
Code: 86. DB::Exception: Received from 192.168.153.128:9000. DB::Exception: Received error from remote server /columns_info?connection_string=oracle&schema=smartbi&table=test. HTTP status code: 500 Internal Server Error, body: NamedDataSource [oracle] does not exist!.

when startup clickhouse-jdbc-bridge2.0,the log is :
2021-01-27 11:06:07.007 [vert.x-eventloop-thread-0] [INFO ] {Utils:940} - Loading JSON from file [config/httpd.json]...
2021-01-27 11:06:07.007 [vert.x-eventloop-thread-0] [WARN ] {Utils:949} - Failed to load JSON from file config/httpd.json
2021-01-27 11:06:07.007 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:270} - Starting web server...
2021-01-27 11:06:07.007 [vert.x-worker-thread-1] [INFO ] {JsonFileRepository:75} - No NamedSchema configuration found
2021-01-27 11:06:07.007 [vert.x-worker-thread-1] [INFO ] {JsonFileRepository:75} - No NamedQuery configuration found
2021-01-27 11:06:07.007 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:274} - Server http://0.0.0.0:9019 started in 1101 ms
2021-01-27 11:06:07.007 [vert.x-worker-thread-2] [INFO ] {JsonFileRepository:87} - Loading NamedDataSource configuration...
2021-01-27 11:06:07.007 [vert.x-worker-thread-2] [INFO ] {BaseRepository:206} - Adding NamedDataSource(id=mssql)...
2021-01-27 11:06:08.008 [vert.x-worker-thread-2] [WARN ] {DriverDataSource:70} - Registered driver with driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver was not found, trying direct instantiation.
Jan 27, 2021 11:06:08 AM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL
WARNING: TLSv1 was negotiated. Please update server and client to use TLSv1.2 at minimum.
2021-01-27 11:06:08.008 [vert.x-worker-thread-2] [INFO ] {BaseRepository:206} - Adding NamedDataSource(id=mysql)...
2021-01-27 11:06:08.008 [vert.x-worker-thread-2] [WARN ] {DriverDataSource:70} - Registered driver with driverClassName=com.mysql.jdbc.Driver was not found, trying direct instantiation.
2021-01-27 11:06:09.009 [vert.x-worker-thread-2] [INFO ] {BaseRepository:206} - Adding NamedDataSource(id=oracle)...
2021-01-27 11:06:09.009 [vert.x-worker-thread-2] [WARN ] {DriverDataSource:70} - Registered driver with driverClassName=oracle.jdbc.driver.OracleDriver was not found, trying direct instantiation.

I have 3 NamedDataSource:mysql 、mssql、oracle, when I startup clickhouse-jdbc-bridge, 3 NamedDataSource has the both WARN:
Registered driver with driverClassName=XXXXXX was not found, trying direct instantiation.
But the mysql and mssql can su connect to the database successfully, the oracle reports errors.

insert into is broken (At least one column is needed)

I'm using CH 21.9.3.30 and the latest jdbc bridge (using container, I guess it's 2.0.4). I was able to set up jdbc table and then select from it, but insert throws At least one column is needed.

-- docker run -e "ACCEPT_EULA=Y" -e 'SA_PASSWORD=myPass' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest

create table jdbc_test (a Int32, b Int32)
ENGINE=JDBC('jdbc:sqlserver://172.17.0.4;databaseName=test;username=sa;password=myPass', 'dbo', 'test')

 -- select works as expected
select * from jdbc_test

-- insert throwing  "At least one column is needed" error
insert into jdbc_test(a, b)
select 2, 4

jdbc postgres fail

hello , jdbc bridage fail for postgres driver as the following

Code: 86. DB::Exception: Received from localhost:9000. DB::Exception: Received error from remote server /identifier_quote?connection_string=jdbc%3Apostgresql%3A%2F%2Fttc-...... HTTP status code: 500 Server Error, body: No suitable driver found for jdbc:postgresql://.......

failed when use clickhouse-jdbc-bridge-2.0.1-shaded.jar

Good day!
i exec
java -jar clickhouse-jdbc-bridge-2.0.1-shaded.jar

select *from jdbc('jdbc:clickhouse://localhost:8123/system?compress=false&ssl=false&user=default', 'select 1')

it returns

Failed to infer schema from [jdbc:clickhouse://localhost:8123/system?compress=false&ssl=false&user=default] due to: Not able to find suitable driver for datasource: jdbc:clickhouse://localhost:8123/system?compress=false&ssl=false&user=default (version 20.12.5.14 (official build))

What did I do wrong ?
Best regards!

Apache Phoenix JDBC driver support

Please add Apache Phoenix JDBC driver support. When I try to use it with bridge, I get

2019-11-14 14:35:09,135 [ HTTP Handler-30 ] {ColumnsInfoServlet} -
java.lang.UnsupportedOperationException
at org.apache.phoenix.jdbc.PhoenixConnection.setClientInfo(PhoenixConnection.java:1010)
at ru.yandex.clickhouse.jdbcbridge.db.jdbc.BridgeConnectionManager.get(BridgeConnectionManager.java:81)
at ru.yandex.clickhouse.jdbcbridge.servlet.ColumnsInfoServlet.doPost(ColumnsInfoServlet.java:39)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:865)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1655)
at ru.yandex.clickhouse.jdbcbridge.servlet.RequestLogger.doFilter(RequestLogger.java:32)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:533)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:205)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:473)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:144)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
at org.eclipse.jetty.server.Server.handle(Server.java:503)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:364)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:260)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:305)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:118)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:333)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:310)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:168)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.produce(EatWhatYouKill.java:132)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:765)
at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:683)
at java.lang.Thread.run(Thread.java:748)

Environment
OS: NAME="Ubuntu" VERSION="16.04.5 LTS (Xenial Xerus)"
JAVA: openjdk version "1.8.0_222"
OpenJDK Runtime Environment (build 1.8.0_222-8u222-b10-1ubuntu1~16.04.1-b10)
OpenJDK 64-Bit Server VM (build 25.222-b10, mixed mode)
ClickHouse: version 19.16.2.2 (official build)
Phoenix jdbc: phoenix-4.14.2-HBase-1.4-client.jar
Run: java -jar clickhouse-jdbc-bridge-1.0.jar --datasources /etc/clickhouse-jdbc-bridge/datasources.properties --driver-path /var/lib/clickhouse-jdbc-bridge --err-log-path /etc/clickhouse-jdbc-bridge/error.log --log-path /etc/clickhouse-jdbc-bridge/bridge.log

connect exception

i have start it jdbc-bridge ，and it is running ，but happen "DB::Exception: jdbc-bridge is not running. ",thanks.
Code: 410, e.displayText() = DB::Exception: jdbc-bridge is not running. Please, start it manually (version 20.4.4.18 (official build))

at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:135)

at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:492)

at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:427)

at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:170)

at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:419)

at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:779)

at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:2966)

at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:111)

at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:170)

at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:109)

at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$17.run(ResultSetViewer.java:3580)

at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:103)

at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)

how to convert .deb file to .rpm file?

Hi, the .deb file only work for ubuntu system, but how can we use it for centos system ?

Schema overriding

Some JDBC drivers may return null value for non-null column :< Query parameter null_as_default may help in some cases but not all as it changed value. On the other hand, adding null-check rs.getObject(1) == null in JDBC bridge may have side effect, especially when dealing with large columns(e.g. BLOB or CLOB etc.). We can introduce schema overriding as a workaround, for example:

-- when there's * in the inline-schema, we'll need to execute the query(fetch_size=1 and max_rows=1) for type inferring,
-- and then use custom column definitions to override
select *
from jdbc(
    'ds1',
    '*, non_null_column Nullable(String), non_exist_column_will_be_discarded String',
    'select * from some_table'
)

This will also help for implicit type conversion without changing inner query.

-- assume below query will return two columns: key UInt8, value Int32
select * from jdbc('ds1', 'select * from some_table')
-- key UInt8, value String
select * from jdbc('ds1', 'value String, *', 'select * from some_table')
-- key String, value String
select * from jdbc('ds1', '* Int64, key String, value Int32, value String', 'select * from some_table')

How to use variables inside jdbc query

Hello!
This query
select point from coords limit 1
returns "(1.0,2.0)"
And i have this query:
select a.st_astext as point from jdbc( 'postgres13?one=1.0&two=2.0', 'select st_astext(st_transform(st_setsrid(st_point({{one}}, {{ two }}), 4326), 3857))' ) a;
I want to paste coordinates from clickhouse table to jdbc query. How i can do it?
Thanks

2.0.3 can't load mysql jar

2021-06-12 19:33:51.051 [main] [INFO ] {Utils:940} - Loading JSON from file [config/vertx.json]...
2021-06-12 19:33:51.051 [main] [WARN ] {Utils:949} - Failed to load JSON from file config/vertx.json
2021-06-12 19:33:52.052 [vert.x-eventloop-thread-0] [INFO ] {Utils:940} - Loading JSON from file [config/server.json]...
2021-06-12 19:33:52.052 [vert.x-eventloop-thread-0] [WARN ] {Utils:949} - Failed to load JSON from file config/server.json
2021-06-12 19:33:52.052 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:580} - Start to monitor configuration file(s) at [config/datasources]
2021-06-12 19:33:52.052 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:580} - Start to monitor configuration file(s) at [config/schemas]
2021-06-12 19:33:52.052 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:580} - Start to monitor configuration file(s) at [config/queries]
2021-06-12 19:33:52.052 [vert.x-eventloop-thread-0] [INFO ] {BaseRepository:275} - Registering new type of NamedDataSource: [jdbc] -> [ru.yandex.clickhouse.jdbcbridge.impl.JdbcDataSource]
2021-06-12 19:33:52.052 [vert.x-eventloop-thread-0] [INFO ] {BaseRepository:278} - Default type of NamedDataSource is set to [jdbc]
2021-06-12 19:33:52.052 [vert.x-eventloop-thread-0] [INFO ] {BaseRepository:275} - Registering new type of NamedDataSource: [script] -> [ru.yandex.clickhouse.jdbcbridge.impl.ScriptDataSource]
2021-06-12 19:33:52.052 [vert.x-eventloop-thread-0] [INFO ] {Utils:940} - Loading JSON from file [config/httpd.json]...
2021-06-12 19:33:52.052 [vert.x-eventloop-thread-0] [WARN ] {Utils:949} - Failed to load JSON from file config/httpd.json
2021-06-12 19:33:52.052 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:272} - Starting web server...
2021-06-12 19:33:52.052 [vert.x-worker-thread-1] [INFO ] {JsonFileRepository:75} - No NamedSchema configuration found
2021-06-12 19:33:52.052 [vert.x-worker-thread-1] [INFO ] {JsonFileRepository:75} - No NamedQuery configuration found
2021-06-12 19:33:53.053 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:276} - Server http://0.0.0.0:9019 started in 1301 ms
2021-06-12 19:33:53.053 [vert.x-worker-thread-2] [INFO ] {JsonFileRepository:87} - Loading NamedDataSource configuration...
2021-06-12 19:33:53.053 [vert.x-worker-thread-2] [INFO ] {BaseRepository:206} - Adding NamedDataSource(id=mysql8)...
2021-06-12 19:33:53.053 [vert.x-worker-thread-2] [ERROR] {HikariConfig:482} - Failed to load driver class com.mysql.jdbc.Driver from HikariConfig class classloader sun.misc.Launcher$AppClassLoader@70dea4e
2021-06-12 19:33:53.053 [vert.x-worker-thread-2] [ERROR] {PropertyElf:164} - Failed to set property driverClassName on target class ru.yandex.clickhouse.jdbcbridge.internal.zaxxer.hikari.HikariConfig
java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at ru.yandex.clickhouse.jdbcbridge.internal.zaxxer.hikari.util.PropertyElf.setProperty(PropertyElf.java:150)
at ru.yandex.clickhouse.jdbcbridge.internal.zaxxer.hikari.util.PropertyElf.lambda$setTargetFromProperties$0(PropertyElf.java:59)
at java.util.Hashtable.forEach(Hashtable.java:879)
at ru.yandex.clickhouse.jdbcbridge.internal.zaxxer.hikari.util.PropertyElf.setTargetFromProperties(PropertyElf.java:54)
at ru.yandex.clickhouse.jdbcbridge.internal.zaxxer.hikari.HikariConfig.(HikariConfig.java:135)
at ru.yandex.clickhouse.jdbcbridge.impl.JdbcDataSource.(JdbcDataSource.java:440)
at ru.yandex.clickhouse.jdbcbridge.impl.JdbcDataSource.newInstance(JdbcDataSource.java:298)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at ru.yandex.clickhouse.jdbcbridge.core.Extension.newInstance(Extension.java:224)
at ru.yandex.clickhouse.jdbcbridge.core.BaseRepository.createFromConfig(BaseRepository.java:97)
at ru.yandex.clickhouse.jdbcbridge.core.BaseRepository.update(BaseRepository.java:209)
at ru.yandex.clickhouse.jdbcbridge.impl.JsonFileRepository.reload(JsonFileRepository.java:94)
at ru.yandex.clickhouse.jdbcbridge.JdbcBridgeVerticle.lambda$null$5(JdbcBridgeVerticle.java:591)
at ru.yandex.clickhouse.jdbcbridge.internal.vertx.core.impl.ContextImpl.lambda$executeBlocking$2(ContextImpl.java:313)
at ru.yandex.clickhouse.jdbcbridge.internal.vertx.core.impl.TaskQueue.run(TaskQueue.java:76)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at ru.yandex.clickhouse.jdbcbridge.internal.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.RuntimeException: Failed to load driver class com.mysql.jdbc.Driver in either of HikariConfig class loader or Thread context classloader
at ru.yandex.clickhouse.jdbcbridge.internal.zaxxer.hikari.HikariConfig.setDriverClassName(HikariConfig.java:486)
... 26 more
2021-06-12 19:33:53.053 [vert.x-worker-thread-2] [WARN ] {BaseRepository:222} - Failed to add NamedDataSource(id=mysql8)
java.lang.IllegalStateException: Failed to create instance from extension: class ru.yandex.clickhouse.jdbcbridge.impl.JdbcDataSource
at ru.yandex.clickhouse.jdbcbridge.core.Extension.newInstance(Extension.java:227)
at ru.yandex.clickhouse.jdbcbridge.core.BaseRepository.createFromConfig(BaseRepository.java:97)
at ru.yandex.clickhouse.jdbcbridge.core.BaseRepository.update(BaseRepository.java:209)
at ru.yandex.clickhouse.jdbcbridge.impl.JsonFileRepository.reload(JsonFileRepository.java:94)
at ru.yandex.clickhouse.jdbcbridge.JdbcBridgeVerticle.lambda$null$5(JdbcBridgeVerticle.java:591)
at ru.yandex.clickhouse.jdbcbridge.internal.vertx.core.impl.ContextImpl.lambda$executeBlocking$2(ContextImpl.java:313)
at ru.yandex.clickhouse.jdbcbridge.internal.vertx.core.impl.TaskQueue.run(TaskQueue.java:76)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at ru.yandex.clickhouse.jdbcbridge.internal.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
at ru.yandex.clickhouse.jdbcbridge.internal.zaxxer.hikari.util.PropertyElf.setProperty(PropertyElf.java:165)
at ru.yandex.clickhouse.jdbcbridge.internal.zaxxer.hikari.util.PropertyElf.lambda$setTargetFromProperties$0(PropertyElf.java:59)
at java.util.Hashtable.forEach(Hashtable.java:879)
at ru.yandex.clickhouse.jdbcbridge.internal.zaxxer.hikari.util.PropertyElf.setTargetFromProperties(PropertyElf.java:54)
at ru.yandex.clickhouse.jdbcbridge.internal.zaxxer.hikari.HikariConfig.(HikariConfig.java:135)
at ru.yandex.clickhouse.jdbcbridge.impl.JdbcDataSource.(JdbcDataSource.java:440)
at ru.yandex.clickhouse.jdbcbridge.impl.JdbcDataSource.newInstance(JdbcDataSource.java:298)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at ru.yandex.clickhouse.jdbcbridge.core.Extension.newInstance(Extension.java:224)
... 10 more
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at ru.yandex.clickhouse.jdbcbridge.internal.zaxxer.hikari.util.PropertyElf.setProperty(PropertyElf.java:150)
... 21 more
Caused by: java.lang.RuntimeException: Failed to load driver class com.mysql.jdbc.Driver in either of HikariConfig class loader or Thread context classloader
at ru.yandex.clickhouse.jdbcbridge.internal.zaxxer.hikari.HikariConfig.setDriverClassName(HikariConfig.java:486)
... 26 more

feature: cli tool

While jdbc-bridge functionality is awsome, I think in certain scenarios it might be overkill to spin up/monitor another service (e.g. for some etl script you run on client side). I presume it should be somewhat straightforward (basically replacing http stream with stdin/stdout or file). Something like this:

clickhouse-client --query 'select * from test' | jdbc-tool --write --table test
jdbc-tool  --query "select * from test" | clickhouse-client --query "INSERT INTO ...."

Does bridge support cocurrent jdbcfunction?

I hava exucte a very long sql such as "insert into table select * from jdbc(.....)" where jdbc(....) has about 1 blilion rows.
It takes a very long time to finish the sql.

But I found that when the sql excuting , the other sql that use jdbc fuction and jdbcTable will block until the sql above has been done.

Every jdbcfunciton must be excueted one by one? sync??

I have change the default timeout to a very long time. inlude the config in server.json 、vertx.json、datasource.json.

The work.num of bridge use the default value

If I missed something??

Thank U for your Reply.

Some problems happened to the configuration of ck-jdbc-bridge

Ahaha,it's me again.Thank for this project to solve my demand.Meanwhile,There are some configuration problems I wanna put up .The main directory is below :

clickhouse-jdbc-bridge
├── docker
├── drivers
├── misc
├── src
└── target

target
└── datasources
└── msjdbc.json

msjdbc.json:
`
{
"msjdbc": {
"driverUrls": [
"/opt/dev/clickhouse-jdbc-bridge/drivers/mssql-jdbc-6.4.0.jre7.jar"
],
"driverClassName": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
"jdbcUrl": "jdbc:sqlserver://xx.xxx:1433;databaseName=xxxx;",
"username": "xxx",
"password": "xxx"
}

}

Q1.According to some tutorials about ck-jdbc-bridge for SQL Server，I downloaded a "mssql-jdbc-6.4.0.jre7.jar
" to this directory : "/opt/dev/clickhouse-jdbc-bridge/drivers" and deleted the default test files in the datasources,so I addded a msjdbc.json in this directory. I started ck-jdbc-bridge and it worked .but there is a warn"driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver was not found" ,I can sure this classname is existed .So what it means? is it a bug?

2021-04-16 17:43:13.013 [main] [INFO ] {Utils:940} - Loading JSON from file [config/vertx.json]...
2021-04-16 17:43:13.013 [main] [WARN ] {Utils:949} - Failed to load JSON from file config/vertx.json
2021-04-16 17:43:13.013 [vert.x-eventloop-thread-0] [INFO ] {Utils:940} - Loading JSON from file [config/server.json]...
2021-04-16 17:43:13.013 [vert.x-eventloop-thread-0] [WARN ] {Utils:949} - Failed to load JSON from file config/server.json
2021-04-16 17:43:13.013 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:573} - Start to monitor configuration file(s) at [config/datasources]
2021-04-16 17:43:13.013 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:573} - Start to monitor configuration file(s) at [config/schemas]
2021-04-16 17:43:13.013 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:573} - Start to monitor configuration file(s) at [config/queries]
2021-04-16 17:43:13.013 [vert.x-eventloop-thread-0] [INFO ] {BaseRepository:275} - Registering new type of NamedDataSource: [jdbc] -> [ru.yandex.clickhouse.jdbcbridge.impl.JdbcDataSource]
2021-04-16 17:43:13.013 [vert.x-eventloop-thread-0] [INFO ] {BaseRepository:278} - Default type of NamedDataSource is set to [jdbc]
2021-04-16 17:43:13.013 [vert.x-eventloop-thread-0] [INFO ] {BaseRepository:275} - Registering new type of NamedDataSource: [script] -> [ru.yandex.clickhouse.jdbcbridge.impl.ScriptDataSource]
2021-04-16 17:43:13.013 [vert.x-eventloop-thread-0] [INFO ] {Utils:940} - Loading JSON from file [config/httpd.json]...
2021-04-16 17:43:13.013 [vert.x-eventloop-thread-0] [WARN ] {Utils:949} - Failed to load JSON from file config/httpd.json
2021-04-16 17:43:13.013 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:270} - Starting web server...
2021-04-16 17:43:14.014 [vert.x-worker-thread-1] [INFO ] {JsonFileRepository:75} - No NamedSchema configuration found
2021-04-16 17:43:14.014 [vert.x-worker-thread-1] [INFO ] {JsonFileRepository:75} - No NamedQuery configuration found
2021-04-16 17:43:14.014 [vert.x-worker-thread-2] [INFO ] {JsonFileRepository:87} - Loading NamedDataSource configuration...
2021-04-16 17:43:14.014 [vert.x-eventloop-thread-0] [INFO ] {JdbcBridgeVerticle:274} - Server http://0.0.0.0:9019 started in 1249 ms
2021-04-16 17:43:14.014 [vert.x-worker-thread-2] [INFO ] {BaseRepository:206} - Adding NamedDataSource(id=msjdbc)...
2021-04-16 17:43:14.014 [vert.x-worker-thread-2] [WARN ] {DriverDataSource:70} - Registered driver with driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver was not found, trying direct instantiation.

Q2. Following the log above,the bridge exec parse " vertx.json/server.json" at first ,failed to load them then monitor thr datasource files.So what is this vertx.json and server.json . Should I configure them at first?

Q3:_If I have two datasources :greenplum and sqlserver ，may I configure them into one json file? Or it needs two independent json files？ What is the correct configuration of datasource json file? _

Have a good day.Thanks !

Build fails for java 10.

[INFO] Changes detected - recompiling the module!
[INFO] Compiling 17 source files to /home/ubuntu/clickhouse-jdbc-bridge/target/classes
[INFO] -------------------------------------------------------------
[ERROR] COMPILATION ERROR :
[INFO] -------------------------------------------------------------
[ERROR] /home/ubuntu/clickhouse-jdbc-bridge/src/main/java/ru/yandex/clickhouse/jdbcbridge/db/jdbc/J
  symbol:   class Service
  location: package sun.misc
[INFO] 1 error
[INFO] -------------------------------------------------------------
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 12.349 s
[INFO] Finished at: 2018-11-06T13:30:25Z
[INFO] Final Memory: 21M/70M
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.apache.maven.plugins:maven-compiler-plugin:3.2:compile (default-
[ERROR] /home/ubuntu/clickhouse-jdbc-bridge/src/main/java/ru/yandex/clickhouse/jdbcbridge/db/jdbc/J
[ERROR]   symbol:   class Service
[ERROR]   location: package sun.misc
[ERROR]
[ERROR] -> [Help 1]
[ERROR]
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR]
[ERROR] For more information about the errors and possible solutions, please read the following art
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoFailureException
ubuntu@ip-172-31-47-213:~/clickhouse-jdbc-bridge$ java --version
openjdk 10.0.2 2018-07-17
OpenJDK Runtime Environment (build 10.0.2+13-Ubuntu-1ubuntu0.18.04.3)
OpenJDK 64-Bit Server VM (build 10.0.2+13-Ubuntu-1ubuntu0.18.04.3, mixed mode)

jdbc tableFunction doesn't add quotes when inferring table metadata

Describe the bug
jdbc table function doesn't add quotes for identifiers when inferring table metadata, but does when quering the data

How to reproduce

ClickHouse server version 18.14.17 revision 54409
clickhouse-jdbc-bridge
PostgreSQL 10
Create table in PostgreSQL with quoted identifier

CREATE TABLE "Channels" (
	"Id" serial NOT NULL,
	"Name" varchar(64) NOT NULL,
	"IsActive" bool NOT NULL
);

Query to run in Clickhouse that lead to unexpected result

select * from jdbc('jdbc:postgresql://pghost:5432/testdb','', 'Channels')

Expected behavior
Query is executed without error.
Postgres receives the following queries

SELECT * FROM "Channels" WHERE 1 = 0

 SELECT "Id", "Name", "IsActive" FROM "Channels"

Error message and/or stacktrace
log of clickhouse-jdbc-bridge

2019-02-28 14:26:21,985 [ HTTP Handler-42 ] {RequestLogger} - GET request to /ping
2019-02-28 14:26:22,013 [ HTTP Handler-26 ] {RequestLogger} - POST request to /columns_info
2019-02-28 14:26:22,208 [ HTTP Handler-26 ] {ColumnsInfoServlet} - Inferring schema by query SELECT * FROM Channels WHERE 1 = 0
2019-02-28 14:26:22,215 [ HTTP Handler-26 ] {ColumnsInfoServlet} - ОШИБКА: отношение "channels" не существует
Position: 15
org.postgresql.util.PSQLException: ОШИБКА: отношение "channels" не существует
Position: 15
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:224)
at ru.yandex.clickhouse.jdbcbridge.servlet.ColumnsInfoServlet.doPost(ColumnsInfoServlet.java:46)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:865)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1655)
at ru.yandex.clickhouse.jdbcbridge.servlet.RequestLogger.doFilter(RequestLogger.java:32)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1642)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:533)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:205)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:473)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:144)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
at org.eclipse.jetty.server.Server.handle(Server.java:503)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:364)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:260)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:305)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:118)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:333)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:310)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:168)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.produce(EatWhatYouKill.java:132)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:765)
at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:683)
at java.lang.Thread.run(Thread.java:748)

Additional context
The expected result is correct only in "always add quotes" condition. Meanwhile PostgreSQL can use quotes for identifiers or not for different objects at the same time.

Add support for Oracle JDBC

Currently without any code modification it is hard to work with Oracle database. Following problems are encountered:

Can't pass login and password information to connection. Oracle JDBC requires passing login and password in properties, rather than in connection string. https://razorsql.com/articles/oracle_jdbc_connect.html
Can't select tables with NUMERIC, NVARCHAR columns
"ORA-01882: timezone region not found" error
"Invalid or unsupported name for clientInfo" with new ojdbc8 driver

Recommend Projects

React

A declarative, efficient, and flexible JavaScript library for building user interfaces.
Vue.js

🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript

TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
TensorFlow

An Open Source Machine Learning Framework for Everyone
Django

The Web framework for perfectionists with deadlines.
Laravel

A PHP framework for web artisans
D3

Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

javascript

JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
web

Some thing interesting about web. New door for the world.
server

A server is a program made to process requests and deliver data to clients.
Machine learning

Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Visualization

Some thing interesting about visualization, use data art
Game

Some thing interesting about game, make everyone happy.

Recommend Org

Facebook

We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft

Open source projects and samples from Microsoft.
Google

Google ❤️ Open Source for everyone.
Alibaba

Alibaba Open Source for everyone
D3

Data-Driven Documents codes.
Tencent

China tencent open source team.