search

ClickHouse / clickhouse-jdbc-bridge

A JDBC proxy from ClickHouse to external databases
Apache License 2.0
167 stars 60 forks source link

load postgresql data failed #57

Closed dreamerfable closed 3 years ago

dreamerfable commented 4 years ago

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;
dreamerfable commented 4 years ago

sorry I can't give the data dump.

the max number in this columns are

5013    5013    9860    1958517471  624015007

the min number in this columns are

2050    0   2   134348815   134348803
zhicwu commented 3 years ago

Looks like a type mapping issue, let me see if I can fix this in 2.0.

zhicwu commented 3 years ago

With 2.0.0 release, you can customize type mapping(from nativeType or jdbcType) in your datasource configuration file like this one.