Apache Spark整合Spring Boot连接Oracle数据库报错求助

背景与需求 #

我是Apache Spark新手，需求如下：从生产、测试两个Oracle数据库的对应表中获取数据，逐列对比后将结果插入测试环境的审计表，后续由任务更新测试环境数据。数据量极大，涉及多张schema各异的表，记录数达万亿级。当前无法连接并读取数据库，附上核心代码、报错日志和POM配置，求可行解决方案。

核心代码 #

public static void main(String[] args) {
        SpringApplication.run(ApacheSparkApplication.class, args);
        SparkSession spark = SparkSession.builder().master("local[*]")
                .appName("SparkandOracledbTest").getOrCreate();
        Dataset<Row> oracleDF = spark.read().format("oracle")
                .option("adbId", "jdbc:oracle:thin:@host:port/dbname")
                .option("dbtable", "owner.tablename")
                .option("user", "user")
                .option("password", "password")
                .load();
        oracleDF.show();
}

报错日志 #

2022-10-26 08:25:22.557  INFO 5448 --- [  restartedMain] o.s.jetty.server.handler.ContextHandler  : Started o.s.j.s.ServletContextHandler@244c93d7{/metrics/json,null,AVAILABLE,@Spark}
2022-10-26 08:25:22.765  WARN 5448 --- [  restartedMain] o.apache.spark.sql.internal.SharedState  : URL.setURLStreamHandlerFactory failed to set FsUrlStreamHandlerFactory
2022-10-26 08:25:22.766  INFO 5448 --- [  restartedMain] o.apache.spark.sql.internal.SharedState  : Setting hive.metastore.warehouse.dir ('null') to the value of spark.sql.warehouse.dir.
2022-10-26 08:25:22.776  INFO 5448 --- [  restartedMain] o.apache.spark.sql.internal.SharedState  : Warehouse path is 'file:/C:/Vijay/csv/spark-warehouse'.
2022-10-26 08:25:22.793  INFO 5448 --- [  restartedMain] o.s.jetty.server.handler.ContextHandler  : Started o.s.j.s.ServletContextHandler@7b364643{/SQL,null,AVAILABLE,@Spark}
2022-10-26 08:25:22.794  INFO 5448 --- [  restartedMain] o.s.jetty.server.handler.ContextHandler  : Started o.s.j.s.ServletContextHandler@76e99147{/SQL/json,null,AVAILABLE,@Spark}
2022-10-26 08:25:22.795  INFO 5448 --- [  restartedMain] o.s.jetty.server.handler.ContextHandler  : Started o.s.j.s.ServletContextHandler@40a15ec9{/SQL/execution,null,AVAILABLE,@Spark}
2022-10-26 08:25:22.796  INFO 5448 --- [  restartedMain] o.s.jetty.server.handler.ContextHandler  : Started o.s.j.s.ServletContextHandler@d385186{/SQL/execution/json,null,AVAILABLE,@Spark}
2022-10-26 08:25:22.798  INFO 5448 --- [  restartedMain] o.s.jetty.server.handler.ContextHandler  : Started o.s.j.s.ServletContextHandler@3fa6b59a{/static/sql,null,AVAILABLE,@Spark}
Exception in thread "restartedMain" java.lang.reflect.InvocationTargetException
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49)
Caused by: java.lang.NoClassDefFoundError: org/codehaus/janino/InternalCompilerException
    at org.apache.spark.sql.execution.datasources.DataSource$.lookupDataSource(DataSource.scala:675)
    at org.apache.spark.sql.execution.datasources.DataSource$.lookupDataSourceV2(DataSource.scala:725)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:207)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:171)
    at com.spark.csv.ApacheSparkApplication.main(ApacheSparkApplication.java:34)
    ... 5 more
Caused by: java.lang.ClassNotFoundException: org.codehaus.janino.InternalCompilerException
    at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:581)
    at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:178)
    at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:522)
    ... 10 more

POM.xml配置 #

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
       <!-- https://mvnrepository.com/artifact/org.apache.spark/spark-core -->
        <dependency>
            <groupId>org.apache.spark</groupId>
            <artifactId>spark-core_2.13</artifactId>
            <version>3.3.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-devtools -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
       </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.spark/spark-sql -->
        <dependency>
            <groupId>org.apache.spark</groupId>
            <artifactId>spark-sql_2.13</artifactId>
            <version>3.3.0</version>
            <scope>provided</scope>
        </dependency>
       <!-- https://mvnrepository.com/artifact/org.codehaus.janino/janino -->
        <dependency>
            <groupId>org.codehaus.janino</groupId>
            <artifactId>janino</artifactId>
       </dependency>
        <!-- https://mvnrepository.com/artifact/org.codehaus.janino/commons-compiler -->
        <dependency>
            <groupId>org.codehaus.janino</groupId>
            <artifactId>commons-compiler</artifactId>
       </dependency>
       <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.oracle.ojdbc/ojdbc8 -->
        <dependency>
            <groupId>com.oracle.ojdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>19.3.0.0</version>
        </dependency>
   </dependencies>

解决方案 #

1. 修复Janino依赖缺失问题 #

报错核心是ClassNotFoundException: org.codehaus.janino.InternalCompilerException，原因是Janino依赖未指定版本，且Spark SQL的scope=provided导致本地开发时无法获取完整依赖。

修改POM配置：

• 给Janino和commons-compiler指定与Spark 3.3.0兼容的版本（推荐3.0.16）
• 移除Spark SQL的scope=provided（生产环境提交任务时，可根据集群Spark版本再调整scope）

修改后的依赖片段：

<!-- Spark SQL -->
<dependency>
    <groupId>org.apache.spark</groupId>
    <artifactId>spark-sql_2.13</artifactId>
    <version>3.3.0</version>
</dependency>
<!-- Janino -->
<dependency>
    <groupId>org.codehaus.janino</groupId>
    <artifactId>janino</artifactId>
    <version>3.0.16</version>
</dependency>
<dependency>
    <groupId>org.codehaus.janino</groupId>
    <artifactId>commons-compiler</artifactId>
    <version>3.0.16</version>
</dependency>

2. 修正Oracle连接方式 #

Spark官方没有内置format("oracle")的数据源，需使用标准JDBC方式连接：

修改代码中的读取逻辑：

Dataset<Row> oracleDF = spark.read()
    .format("jdbc")
    .option("url", "jdbc:oracle:thin:@host:port/dbname")
    .option("dbtable", "owner.tablename")
    .option("user", "user")
    .option("password", "password")
    .option("driver", "oracle.jdbc.OracleDriver")
    .load();

3. 大数据量场景优化建议 #

针对万亿级数据量，提前做好以下优化：

• 分区读取：使用partitionColumn、lowerBound、upperBound、numPartitions参数分批次读取Oracle数据，避免单分区压力过大
• 预定义Schema：提前定义DataFrame的Schema，减少Spark自动推断Schema的开销
• 对比逻辑优化：使用Spark的except算子或自定义UDF做列级对比，避免全量加载数据到内存
• 审计表写入优化：使用mode("append")写入审计表，配合分区表设计减少写入压力

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