使用Greenplum PXF外部表写入SQL Server时遇类型转换异常求助

错误现象 #

执行插入语句时触发500错误，具体报错：

postgres=# insert into foreign_sqlserver values(1,'alice',11),(2,'bob',20);
psql: ERROR:  remote component error (500) from '127.0.0.1:5888':  Type  Exception Report   Message  java.io.DataInputStream cannot be cast to [B   Description  The server encountered an unexpected condition that prevented it from fulfilling the request.   Exception   java.lang.ClassCastException: java.io.DataInputStream cannot be cast to [B (libchurl.c:967)  (seg1 172.16.100.109:6001 pid=12562) (libchurl.c:967)

操作步骤 #

SQL Server端执行的SQL #

create database test
go 

use test
go

create schema alice
go

create table alice.student
(
    id   int,
    name varchar,
    age  int
)
go

Greenplum端执行的SQL #

CREATE EXTENSION pxf_fdw ;

DROP SERVER sqlserv_server cascade;

CREATE SERVER sqlserv_server FOREIGN DATA WRAPPER jdbc_pxf_fdw OPTIONS (
    jdbc_driver 'com.microsoft.sqlserver.jdbc.SQLServerDriver',
    db_url 'jdbc:sqlserver://172.16.100.109:1433;databaseName=test;encrypt=true;trustServerCertificate=true;',
    batch_size '10000',
    fetch_size '2000'
);

CREATE USER MAPPING FOR mxadmin SERVER sqlserv_server OPTIONS ( user 'sa' , pass 'Aa123456');

CREATE FOREIGN TABLE foreign_sqlserver (
    id int4,
    name varchar(20) ,
    age int4
)
SERVER sqlserv_server
OPTIONS (resource 'alice.student');

PXF服务日志（pxf-service.log） #

2023-03-27 13:08:37.0736 ERROR tomcat-http--8 org.greenplum.pxf.service.bridge.WriteBridge - Failed to close bridge resources: java.io.DataInputStream cannot be cast to [B
2023-03-27 13:18:24.0044 ERROR tomcat-http--13 org.greenplum.pxf.service.rest.WritableResource - Exception: totalWritten so far 9999 to alice.student
java.lang.ClassCastException: java.io.DataInputStream cannot be cast to [B
        at org.greenplum.pxf.plugins.jdbc.JdbcResolver.decodeOneRowToPreparedStatement(JdbcResolver.java:309)
        at org.greenplum.pxf.plugins.jdbc.writercallable.BatchWriterCallable.call(BatchWriterCallable.java:68)
        at org.greenplum.pxf.plugins.jdbc.JdbcAccessor.writeNextObject(JdbcAccessor.java:247)
        at org.greenplum.pxf.service.bridge.WriteBridge.setNext(WriteBridge.java:78)
        at org.greenplum.pxf.service.rest.WritableResource.stream(WritableResource.java:138)
        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 com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
        at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:205)
        at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
        at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:288)
        at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
        at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
        at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
        at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
        at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1469)
        at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1400)
        at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1349)
        at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1339)
        at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:416)
        at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:537)
        at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:699)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.greenplum.pxf.service.servlet.SecurityServletFilter.lambda$doFilter$0(SecurityServletFilter.java:145)
        at java.security.AccessController.doPrivileged(Native Method)
        at javax.security.auth.Subject.doAs(Subject.java:422)
        at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1893)
        at org.greenplum.pxf.service.servlet.SecurityServletFilter.doFilter(SecurityServletFilter.java:158)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:492)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:165)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:1025)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:452)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1201)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:654)
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:317)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:748)
2023-03-27 13:18:24.0045 ERROR tomcat-http--13 org.greenplum.pxf.service.bridge.WriteBridge - Failed to close bridge resources: java.io.DataInputStream cannot be cast to [B

问题原因 #

日志中的java.lang.ClassCastException: java.io.DataInputStream cannot be cast to [B表明，PXF的JDBC插件在尝试将DataInputStream转换为字节数组时失败，核心原因是数据传输的序列化格式不匹配，通常是由于外部表未指定正确的格式化器，或者PXF版本存在兼容性bug。

解决方法 #

1. 指定外部表的格式化配置 #

创建FOREIGN TABLE时，需在OPTIONS中添加格式化器配置，确保数据以JDBC插件预期的格式传输：

CREATE FOREIGN TABLE foreign_sqlserver (
    id int4,
    name varchar(20) ,
    age int4
)
SERVER sqlserv_server
OPTIONS (
    resource 'alice.student',
    format 'CUSTOM',
    formatter 'org.greenplum.pxf.plugins.jdbc.JdbcFormatter'
);

2. 升级PXF版本 #

该错误在PXF部分旧版本中属于已知bug，建议将PXF升级至最新稳定版本（如6.10及以上），新版本已修复JDBC写入时的类型转换问题。

3. 修正SQL Server表字段定义 #

SQL Server中未指定长度的varchar默认长度为1，可能引发数据截断或兼容性问题，建议修改为明确长度：

alter table alice.student alter column name varchar(20);

内容的提问来源于stack exchange，提问作者cousin