Null vs Blank Issue

[ilaria-ia]

I'm using the cobrix library to read an EBDCID binary file, obtained as a table extraction on DB2. The reading operation output is writed as CSV file on HDFS.
For parsing the binary file I'm using a copybook file present on HDFS.
Here is the spark reading command:
spark.read.
format (sourceFileFormat).
option ("copybook", copybookStagingHDFSPath).
option ("schema_retention_policy", "collapse_root").
option ("string_trimming_policy", "none").
load (sourceFileStagingHDFSPath + file)
The resulting output on CSV file does not allow the distinction between BLANK ("") and NULL cells but are both treated as empty strings.

Is there a way to treat BLANK ("") value and NULL value differently, in order to have an output corresponding to the data present on the DB2 source database?

[yruslan]

Hi,

Cobrix does distinguish between nulls and empty values.
It's up to the CSV writer to write different values for nulls and empty values.

You can achieve this by specifying a value that should be written to a CSV field when the input value is null.

df.write
  .option("nullValue", "NULL")
  .csv("/some/path")

[ilaria-ia]

Hi,

Thanks for the reply. I tested the writing mode you recommended, but the problem did not go away. Executing a show on the dataframe created by the read operation and comparing it with the source table on DB2, it was verified that the null cells on the source are read blank in the dataframe. So the error is being read not writing. In fact, since null cells are not created in the dataframe, the option option ("nullValue", "NULL") does not generate null values ​​in the csv written on HDFS.
For the sake of completeness, I report an example to help understand the difference between db2 and post-reading dataframe.

Examble_DB2vsDataframePostRead.xlsx

This is read operation:
val sourceFileDF: (Int, DataFrame) = Try.apply({
spark.read.
format(sourceFileFormat).
option("copybook", copybookStagingHDFSPath).
option("schema_retention_policy", "collapse_root").
option("string_trimming_policy", "left").
option("inferSchema",false).
load(sourceFileStagingHDFSPath + file)

Is it possible that I need to add special options to fix the problem?

[yruslan]

I see. What are the data types of the problematic columns? How their PICs look like in teh copybook?

I have a suspicion that it might be the limitation of the mainframe format itself. It might be that empty strings are encoded exactly the same as nulls on the export from DB2. If this is the case, there is nothing we can do.

[ilaria-ia]

Data tyoes and PICs of columns are:
campo1 PIC X(20). CHAR(20) FOR SBCS DATA WITH DEFAULT NULL
FILLER PIC X(1).
campo2 PIC X(4). CHAR(4) FOR SBCS DATA WITH DEFAULT NULL
FILLER PIC X(1).
campo3 PIC X(102). VARCHAR(100) FOR SBCS DATA WITH DEFAULT NULL
FILLER PIC X(1).
campo4 PIC X(8). CHAR(8) FOR SBCS DATA WITH DEFAULT NULL
FILLER PIC X(1).
campo5 PIC X(2). CHAR(2) FOR SBCS DATA WITH DEFAULT NULL
FILLER PIC X(1).
campo6 PIC X(3). CHAR(3) FOR SBCS DATA WITH DEFAULT NULL
FILLER PIC X(1).
campo7 PIC X(1). CHAR(1) FOR SBCS DATA WITH DEFAULT NULL
FILLER PIC X(1).
campo8 PIC X(10). DATE WITH DEFAULT NULL
FILLER PIC X(1).
campo9 PIC X(20). CHAR(20) FOR SBCS DATA WITH DEFAULT NULL
FILLER PIC X(1).
campo10 PIC X(102). VARCHAR(100) FOR SBCS DATA WITH DEFAULT NULL
FILLER PIC X(1).

[yruslan]

That's what I was suspected. Fields that are encoded as 'PIC X' don't support the notion of NULLs. So when you export a table from DB2 to a mainframe file, both empty strings and nulls are encoded as "40404040" (40 is space in EBCDIC). If encoded values are different, it might be possible to differentiate it.

Could I ask you to run with .option("debug", "true") to see how empty strings and nulls are encoded?

[ilaria-ia]

Is this option valid for spark-cobol version 0.2.5? I have spark 2.2.0

[yruslan]

No, this option has been introduced quite recently. But you can try spark-cobol 2.2.0 with Spark 2.2.0.

[ilaria-ia]

Unfortunately, with version 2.2.0 I always have this error which I cannot resolve:
Exception in thread "main" java.lang.NoClassDefFoundError: org/antlr/v4/runtime/CharStreams

[yruslan]

This might be easy to solve. :)
Get this jar: antlr4-runtime-4.7.2.jar and add it to spark-submit / spark-shell with --jars

[ilaria-ia]

I did this, but error is
INFO source.DefaultSource: Cobrix 'spark-cobol' build 2.2.0 (2020-12-30T10:25:41)
ANTLR Tool version 4.7.2 used for code generation does not match the current runtime version 4.5.3ANTLR Runtime version 4.7.2 used for parser compilation does not match the current runtime version 4.5.3Exception in thread "main" java.lang.ExceptionInInitializerError

[yruslan]

It's strange. What's your command line?

[yruslan]

I checked. It seems older Spark versions used different ANTLR and they provide it in runtime.
You can solve this by compiling the latest Cobrix from sources replacing Spark version.

But that won't solve nulls issue. Alternatively, you can use a HEX editor to look into the data file and check if nulls are encoded differently than empty strings.

[ilaria-ia]

If i replace FILLER with this format copybook:
FIELD PIC X(20).
FIELD-NULL REDEFINES
FIELD PIC X(20).
do i have to set a particular option?

[yruslan]

No additional options are necessary here. But what are you trying to achieve?