【详述】hive的decimal字段查询报错，调整一直不能查询出数据，请求大佬支援
【背景】建立starrocks的外部表，附件有驱动和截图，下面是创建外部表的相关语句。
drop resource hive_jdbc_enhanced;
CREATE EXTERNAL RESOURCE hive_jdbc_enhanced
PROPERTIES (
“type” = “jdbc”,
“user” = “hive”,
“password” = “123456”,
“jdbc_uri” = “jdbc:hive2://192.168.9.40:10000/test;hive.decimal.handle.as=string”,
“driver_url” = “file:///data/drivers/hive-jdbc-4.0.1-standalone.jar”,
“driver_class” = “org.apache.hive.jdbc.HiveDriver”
);
drop table a_test_1;
CREATE EXTERNAL TABLE a_test_1 (
id bigint(20) NULL COMMENT “”,
price decimal(38,2) NULL COMMENT “”
) ENGINE=JDBC
PROPERTIES (
“resource” = “hive_jdbc_enhanced”,
“table” = “orders0721_bak”
);
select price from a_test_1;
– org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [1064] [42000]: Type mismatches on column[price], JDBC result type is BigInteger, please set the type to largeint
– at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
– at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:582)
– at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$1(SQLQueryJob.java:491)
– at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:189)
– at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:498)
– at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:924)
– at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3895)
– at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123)
– at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:189)
– at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121)
– at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5096)
– at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
– at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
– Caused by: java.sql.SQLSyntaxErrorException: Type mismatches on column[price], JDBC result type is BigInteger, please set the type to largeint
– at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
– at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
– at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763)
– at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
– at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330)
– at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
– … 12 more
– 这是源表的建表ddl
– test.orders0721_bak definition
CREATE TABLE test.orders0721_bak(
id bigint COMMENT ‘id 自增主键’,
order_serial string COMMENT ‘订单号’,
price decimal(12,2) COMMENT ‘总价钱’,
user_id bigint COMMENT ‘所属用户’,
status tinyint COMMENT ‘订单状态：0-用户未确认,1-付款中,2-已支付,3-已取消,4-已完成’,
date_created timestamp COMMENT ‘创建时间’,
date_updated timestamp COMMENT ‘更新时间’,
description string COMMENT ‘备注’,
is_invoice tinyint COMMENT ‘是否需要发票：0-否,1-是’,
shipping_status tinyint COMMENT ‘发货状态：0-未发货,1-已发货’,
payment_method tinyint COMMENT ‘支付方式：0-在线支付,2-线下支付’,
disabled tinyint COMMENT ‘是否废弃：0-否,1-是’,
req_client_ip string COMMENT ‘客户端IP’,
referer string COMMENT ‘用户来源’)
CLUSTERED BY (
user_id)
INTO 8 BUCKETS
ROW FORMAT SERDE
‘org.apache.hadoop.hive.ql.io.orc.OrcSerde’
STORED AS INPUTFORMAT
‘org.apache.hadoop.hive.ql.io.orc.OrcInputFormat’
OUTPUTFORMAT
‘org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat’
LOCATION
‘hdfs://namenode:8020/user/hive/warehouse/test.db/orders0721_bak’
TBLPROPERTIES (
‘transient_lastDdlTime’=‘1753080437’);
【是否存算分离】否
【StarRocks版本】3.2.11-10a5f0e
【附件】
hive-jdbc-4.0.1-standalone.jar (41.6 MB)