PySpark连接NetSuite Suite Analytics JDBC：查询分类表触发NullPointerException但OA_TABLES可正常查询

问题复现 #

我注意到你用PySpark通过JDBC连接NetSuite时，查询系统表OA_TABLES可以正常执行，但查询业务分类表"Fl - Accountant".classification时却触发了NullPointerException，以下是你的代码和错误详情：

可正常运行的代码（查询OA_TABLES）

jdbc_url = "jdbc:ns://xxx.connect.api.netsuite.com:1708;ServerDataSource=NetSuite2.com;Encrypted=1;NegotiateSSLClose=false;CustomProperties=(AccountID=xxx;RoleID=xxx)"
driver = "com.netsuite.jdbc.openaccess.OpenAccessDriver"
output_path = "/tmp/netsuite_all_tables_chained"

try:
    print(f" query and writing directly to CSV...... {output_path}")
    spark.read \
        .format("jdbc") \
        .option("url", jdbc_url) \
        .option("dbtable", "OA_TABLES") \
        .option("user", "analytx") \
        .option("password", "xxxx") \
        .option("driver", driver) \
        .load() \
        .write \
        .format("csv") \
        .option("header", "true") \
        .mode("overwrite") \
        .save(output_path)
    print(f"SUCCESS {output_path}")
except Exception as e:
    print(f"ugh doesnt work ")
    raise e

触发异常的代码（查询分类表）

jdbc_url = "jdbc:ns://xxx.connect.api.netsuite.com:1708;ServerDataSource=NetSuite2.com;Encrypted=1;NegotiateSSLClose=false;CustomProperties=(AccountID=xxx;RoleID=xx)"
driver = "com.netsuite.jdbc.openaccess.OpenAccessDriver"
netsuite_table_to_read = '"Fl - Accountant".classification'
output_path = "/tmp/netsuite_all_tables_chained"

try:
    print(f" query and writing directly to CSV...... {output_path}")
    spark.read \
        .format("jdbc") \
        .option("url", jdbc_url) \
        .option("dbtable", netsuite_table_to_read ) \
        .option("user", "xxxx") \
        .option("password", "xxx") \
        .option("driver", driver) \
        .load() \
        .write \
        .format("csv") \
        .option("header", "true") \
        .mode("overwrite") \
        .save(output_path)
    print(f"SUCCESS yo{output_path}")
except Exception as e:
    print(f"ugh doesnt work ")
    raise e

具体错误栈

SparkException: Job aborted due to stage failure: Task 0 in stage 20.0 failed 4 times, most recent failure: Lost task 0.3 in stage 20.0 (TID 68) (10.21.40.196 executor 0): java.lang.NullPointerException
at com.netsuite.jdbc.base.hk.a(oajc:654)
at com.netsuite.jdbc.base.dj.c(oajc:494)
at com.netsuite.jdbc.base.ax.a(oajc:1926)
at com.netsuite.jdbc.base.fv.getTimestamp(oajc:5211)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$.$anonfun$makeGetter$15(JdbcUtils.scala:521)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$.$anonfun$makeGetter$15$adapted(JdbcUtils.scala:520)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anon$1.getNext(JdbcUtils.scala:385)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anon$1.getNext(JdbcUtils.scala:366)
at org.apache.spark.util.NextIterator.hasNext(NextIterator.scala:73)
at org.apache.spark.InterruptibleIterator.hasNext(InterruptibleIterator.scala:37)
at org.apache.spark.util.CompletionIterator.hasNext(CompletionIterator.scala:31)
at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage1.processNext(Unknown Source)
at org.apache.spark.sql.execution.BufferedRowIterator.hasNext(BufferedRowIterator.java:43)
at org.apache.spark.sql.execution.WholeStageCodegenEvaluatorFactory$WholeStageCodegenPartitionEvaluator$$anon$1.hasNext(WholeStageCodegenEvaluatorFactory.scala:50)
at scala.collection.Iterator$$anon$10.hasNext(Iterator.scala:460)
at scala.collection.convert.Wrappers$IteratorWrapper.hasNext(Wrappers.scala:32)
at com.google.common.collect.Iterators$PeekingImpl.hasNext(Iterators.java:1139)
at com.databricks.photon.NativeRowBatchIterator.hasNext(NativeRowBatchIterator.java:44)
at 0xc7c5392 <photon>.HasNext(external/workspace_spark_3_5/photon/jni-wrappers/jni-row-batch-iterator.cc:50)
at com.databricks.photon.JniApiImpl.hasNext(Native Method)
at com.databricks.photon.JniApi.hasNext(JniApi.scala)
at com.databricks.photon.JniExecNode.hasNext(JniExecNode.java:79)
at com.databricks.photon.BasePh...
File <command-7917885491786650>, line 35
32 except Exception as e:
33 print(f"fffffff ")
---> 35 raise e
File <command-7917885491786650>, line 28
13 print(f" query and writing directly to CSV...... {output_path}")
15 netsuite_table_to_read = '"Fl - Accountant".classification'
16 spark.read \
17 .format("jdbc") \
18 .option("url", jdbc_url) \
19 .option("dbtable", netsuite_table_to_read) \
20 .option("user", "xxxxx") \
21 .option("password", "[REDACTED]") \
22 .option("driver", driver) \
23 .load() \
24 .write \
25 .format("csv") \
26 .option("header", "true") \
27 .mode("overwrite") \
---> 28 .save(output_path)
30 print(f"SUCCESS yo{output_path}")

问题原因分析 #

从错误栈可以精准定位问题：NetSuite的OpenAccess JDBC驱动在处理分类表中的NULL Timestamp类型字段时存在bug。当Spark通过JdbcUtils调用驱动的getTimestamp()方法读取NULL值的时间戳字段时，驱动没有正确返回null，反而触发了空指针异常。

而OA_TABLES作为系统表，要么没有这类含NULL值的时间戳字段，要么驱动对系统表的字段处理逻辑做了特殊兼容，因此可以正常查询。另外也需要确认你的NetSuite角色是否对分类表的所有字段拥有完整读取权限——如果某个时间戳字段无权限，驱动也可能返回异常值导致空指针。

解决方案 #

针对这个问题，你可以尝试以下几种有效的解决方法：

1. 用自定义SQL查询替代直接指定表名，处理NULL时间戳

通过query参数（而非dbtable）编写SQL，显式处理NULL时间戳字段，比如用NVL函数将NULL值替换为默认时间，或者直接排除有问题的字段：

jdbc_url = "jdbc:ns://xxx.connect.api.netsuite.com:1708;ServerDataSource=NetSuite2.com;Encrypted=1;NegotiateSSLClose=false;CustomProperties=(AccountID=xxx;RoleID=xx)"
driver = "com.netsuite.jdbc.openaccess.OpenAccessDriver"
output_path = "/tmp/netsuite_all_tables_chained"

# 自定义查询：替换NULL时间戳为默认值，替换成你实际的时间戳字段名
custom_query = """
SELECT 
    *,
    NVL(your_timestamp_column, '1970-01-01 00:00:00') AS your_timestamp_column
FROM "Fl - Accountant".classification
"""

try:
    print(f" query and writing directly to CSV...... {output_path}")
    spark.read \
        .format("jdbc") \
        .option("url", jdbc_url) \
        .option("query", custom_query) \  # 用query代替dbtable
        .option("user", "xxxx") \
        .option("password", "xxx") \
        .option("driver", driver) \
        .load() \
        .write \
        .format("csv") \
        .option("header", "true") \
        .mode("overwrite") \
        .save(output_path)
    print(f"SUCCESS yo{output_path}")
except Exception as e:
    print(f"ugh doesnt work ")
    raise e

如果你不确定具体哪个字段有问题，可以先查询表的元数据，或者用SELECT * EXCEPT(problematic_timestamp_col)直接排除问题字段。

2. 升级NetSuite JDBC驱动版本

访问NetSuite官方下载最新的OpenAccess JDBC驱动，替换当前使用的jar包——新版本大概率修复了这类NULL值处理的bug。

3. 配置Spark JDBC的NULL值映射参数

尝试添加Spark JDBC的NULL值映射参数，让驱动正确识别并处理NULL值：

spark.read \
    .format("jdbc") \
    .option("url", jdbc_url) \
    .option("dbtable", '"Fl - Accountant".classification') \
    .option("user", "xxxx") \
    .option("password", "xxx") \
    .option("driver", driver) \
    .option("nullValue", "") \  # 配置NULL值映射为空字符串
    .option("nanValue", "") \
    .load()

4. 验证NetSuite角色权限

登录NetSuite后台，检查当前RoleID对应的角色是否对Fl - Accountant分类表的所有字段拥有查看权限，特别是时间戳类型的字段。权限不足也可能导致驱动无法正常读取字段值，进而触发异常。

内容来源于stack exchange