Exception while writing to BigQuery table with BigNumeric data type

[lbg-samson-daniel]

java.lang.IllegalArgumentException: BigNumeric precision is too wide (76), Spark can only handle decimal types with max precision of 38
at com.google.cloud.spark.bigquery.SchemaConverters.getStandardDataType(SchemaConverters.java:385)
at com.google.cloud.spark.bigquery.SchemaConverters.lambda$getDataType$3(SchemaConverters.java:340)
at java.base/java.util.Optional.orElseGet(Optional.java:369)
at com.google.cloud.spark.bigquery.SchemaConverters.getDataType(SchemaConverters.java:340)
at com.google.cloud.spark.bigquery.SchemaConverters.convert(SchemaConverters.java:286)
at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
at java.base/java.util.Iterator.forEachRemaining(Iterator.java:133)
at java.base/java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801)
at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
at com.google.cloud.spark.bigquery.SchemaConverters.toSpark(SchemaConverters.java:69)
at com.google.cloud.spark.bigquery.v2.Spark3Util.createBigQueryTableInstance(Spark3Util.java:53)
at com.google.cloud.spark.bigquery.v2.Spark34BigQueryTableProvider.getBigQueryTableInternal(Spark34BigQueryTableProvider.java:33)
at com.google.cloud.spark.bigquery.v2.Spark31BigQueryTableProvider.inferSchema(Spark31BigQueryTableProvider.java:40)
at org.apache.spark.sql.execution.datasources.v2.DataSourceV2Utils$.getTableFromProvider(DataSourceV2Utils.scala:90)
at org.apache.spark.sql.DataFrameWriter.getTable$1(DataFrameWriter.scala:284)
at org.apache.spark.sql.DataFrameWriter.saveInternal(DataFrameWriter.scala:300)
at org.apache.spark.sql.DataFrameWriter.save(DataFrameWriter.scala:251)
at com.lloydsbanking.commons.client.BQClient.writeDataWithPartitioning(BQClient.java:178)
at com.lloydsbanking.ingestion.handler.SaveDataToODPHandler.processStep(SaveDataToODPHandler.java:75)
at com.lloydsbanking.ingestion.handler.ExecutionHandlerExecutor.execute(ExecutionHandlerExecutor.java:19)
at com.lloydsbanking.ingestion.processor.BatchJobProcessor.lambda$execute$0(BatchJobProcessor.java:69)
at java.base/java.util.stream.Streams$RangeIntSpliterator.forEachRemaining(Streams.java:104)
at java.base/java.util.stream.IntPipeline$Head.forEach(IntPipeline.java:593)
at com.lloydsbanking.ingestion.processor.BatchJobProcessor.execute(BatchJobProcessor.java:38)
at com.lloydsbanking.ingestion.job.BaseJob.main(BaseJob.java:47)
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.apache.spark.deploy.JavaMainApplication.start(SparkApplication.scala:52)
at org.apache.spark.deploy.SparkSubmit.org$apache$spark$deploy$SparkSubmit$$runMain(SparkSubmit.scala:1216)
at org.apache.spark.deploy.SparkSubmit.doRunMain$1(SparkSubmit.scala:195)
at org.apache.spark.deploy.SparkSubmit.submit(SparkSubmit.scala:218)
at org.apache.spark.deploy.SparkSubmit.doSubmit(SparkSubmit.scala:92)
at org.apache.spark.deploy.SparkSubmit$$anon$2.doSubmit(SparkSubmit.scala:1314)
at org.apache.spark.deploy.SparkSubmit$.main(SparkSubmit.scala:1323)
at org.apache.spark.deploy.SparkSubmit.main(SparkSubmit.scala)
@davidrabinowitz I think its trying to pull BigQuery table schema before write to table and its throwing this error this I am getting only when executed from dataproc and not from my local system

[davidrabinowitz]

The title explains the issue - the BigQuery schema cannot be converted to a spark schema as Spark cannot handle Decimals with precision larger than 38.

[lbg-samson-daniel]

I understand that but what the work around for the issue its due to big query dependency library that comes with dataproc as in local the version I am using doesnt have the issue this is the version I am using in local com.google.cloud.spark:spark-bigquery-with-dependencies_2.12:0.31.1
I tried including it as additional jar but then I am getting a different issue
java.util.ServiceConfigurationError: org.apache.spark.sql.sources.DataSourceRegister: com.google.cloud.spark.bigquery.BigQueryRelationProvider not a subtype

updating the BigQuery table schema is not an option for us

[lbg-samson-daniel]

@davidrabinowitz do you have any suggestions

[matteomorlack]

Take a look at this section directly from readme.md. You need to add an additional parameter with the version you need. Try to specify the version that works for you in local this way

Specifying the Spark BigQuery connector version in a Dataproc cluster

Dataproc clusters created using image 2.1 and above, or batches using the Dataproc serverless service come with built-in Spark BigQuery connector. Using the standard --jars or --packages (or alternatively, the spark.jars/spark.jars.packages configuration) won't help in this case as the built-in connector takes precedence.

To use another version than the built-in one, please do one of the following:

For Dataproc clusters, using image 2.1 and above, add the following flag on cluster creation to upgrade the version --metadata SPARK_BQ_CONNECTOR_VERSION=0.43.1, or --metadata SPARK_BQ_CONNECTOR_URL=gs://spark-lib/bigquery/spark-3.3-bigquery-0.43.1.jar to create the cluster with a different jar. The URL can point to any valid connector JAR for the cluster's Spark version.

For Dataproc serverless batches, add the following property on batch creation to upgrade the version: --properties dataproc.sparkBqConnector.version=0.43.1, or --properties dataproc.sparkBqConnector.uri=gs://spark-lib/bigquery/spark-3.3-bigquery-0.43.1.jar to create the batch with a different jar. The URL can point to any valid connector JAR for the runtime's Spark version.

[lbg-samson-daniel]

can it be added in the spark submit job properties rather than the cluster is there any documentation you can point me to for the compatible bq library version for dataproc version 2.2
@matteomorlack

[davidrabinowitz]

Only if you remove the connector built into the image. Otherwise you would have two connectors in the classpath which will fail spark

[lbg-manasa-padimalla]

even upgrading the connector version to 0.43.1 still cant able to cast with the precision which BQ is expecting
is there any workaround for this

[davidrabinowitz]

Please notice that regardless of the connector version, spark's max precision is 38, and this is hard coded to the spark's code.