Null handling in schema file

[Poovalingam]

Hi,
I'm facing warning message as "Exporting nullable fileld without null handling properties" in the stage in target position. The job is using schema file. I handled null in schema file as empty spaces.

Here comes the list of stages in the job,

1. sequential file ( source - using schema file)
2. Oracle (Upsert)
3. Reject link from oracle to Transformer ( just to abort the job if reject record count exceeds n)
4. 1st Output of transformer to sequential file.
5. 2nd output to Peek stage (just to give the condition saying abort)

In the 4th stage, the record is not getting inserted if NULL values are coming from the transformer(source).

Kindly suggest me what can I do on this case without changing the design using schema file.


Many thanks,
Poovalingam.

[ArndW]

Go to your output sequential file stage and column view, double-click on the column number for the nullable field and see if the column meta data actually does show that you are handling nulls.

[Poovalingam]

Hi Arndw,
Since I'm using schema file, No column names or column no's are there to check the nullable handling.


Thanks & Regards,
Poovalingam.

[ray.wurlod]

Then make sure that the schema file includes Null Field Value properties either in the format string or in the column definitions. Or both.

[Poovalingam]

Thanks Ray..

I have prepared the schema file for target stage as well and it is working fine now.


Regards,
Poova.

[Sreenivasulu]

Nice to know that you used schema file to load data. Never came across to me to try this option

I feel that when you are reading the file you would not written the 'null' handling feature properly.



Regards
Sreeni

[ray.wurlod]

I disagree. That's how the null values got to be in the job in the first place. Because they were being interpreted from the source file.

[Nagin]

Ray,
I have a similar requirement. I need to reject if the incoming column is null with schema file at the source sequential file stage itself.

Can you please share if you have an example of how to specify null field value properties in the format string or column definitions?

Thank You.

[anbu]

Define the field as non-nullable and add a reject link to the sequential file.

Code: Select all

field name:nullable data type

[Nagin]

By default columns are not nullable in a schema file. So, I tried it, but it is not getting rejected. If I specify Column:not nullable the job throws an error and says Unknown schema type.

Here is the content of my schema file

record{final_delim=none, delim='|', quote=none, charset="ISO8859-1"}
(
Col_One:nullable int32;
Col_two:string[10];
Col_Three:string[15];
)

I was wondering if there is an option I should specify in the record definition itself like the delim='|'.

May be some thing like
record{final_delim=none, delim='|', quote=none, null_filed_value="", charset="ISO8859-1"}

Do you have any idea Anbu?

[anbu]

Yes by default the columns are not nullable in the schema file.

Did you see any warnings in the director when all the columns are defined as not nullable in schema file?

[nkbasakater]

Hi Poovalingam,
You can modify your schema file like the following:
record
{final_delim=end, delim='|', null_field='', quote=none, charset="ISO8859-1"}
(
Col_One:nullable int32;
Col_two:string[10];
Col_Three:string[15];
)

null_field='' will specify- On import, the value given to a field containing a null. On export, the value given to an exported field if the source field is set to null.

Thanks,
Nil Kamal

[Nagin]

Thank you Nil Kamal for null_filed. It works without any syntax problems.

But still, the source sequential file is rejecting if it gets a null in place of an integer but not a string. Is the string null handling different?

Any ideas all you experts out there??

[anbu]

Do you see any warnings in director if you remove nullable property from your column Col_One?

[greggknight]

record

{final_delim=end, record_delim='\n', delim=',', null_field="", quote=double}

(

DM_TIMESTAMP:timestamp;

DM_TXID:string;

DM_OPERATION_TYPE:string;

DM_USER:string;

BEFORE_COMPANY:nullable string {null_field=''};

BEFORE_FACDIVN:nullable string {null_field=''};