Working with null values in BigQuery

Hi,
I am working on Google BigQuery and it seems that it doesn't accept null values. So I replaced them with 0s. The problem occurred when calculating average.
For example, consider this file,
_______________________________________
A|B|C|D
India|1|4|6
Sri Lanka|8||4
Nepal|3|0|6
__________________________________________
Now say, I have to take average of B,C & D: (B+C+D)/3
Now for row with India, this will work fine. (1+4+6)/3
For row with Nepal, (3+0+6)/3
But for row with Sri Lanka, it should be (8+4)/2
But I am replacing null with 0, thus calculation becoming (8+0+4)/3. Which is wrong.
If I replace null with 0s, it gives wrong output.
How do I make things to work such that nulls are ignored while 0s are counted?
3 REPLIES
Community Manager

Re: Working with null values in BigQuery

Hi 
Which version of Talend Studio are you using? I am going to reproduce and investigate this issue.
BR
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: Working with null values in BigQuery

Hi Shong,
Talend Enterprise Big Data 5.4.1.r111943
One Star

Re: Working with null values in BigQuery

Hey all, sorry for bumping an old thread, but it looks like this is the only relevant thread to my problem.
I'm trying to import data into BigQuery which has nullable fields, but bigquery doesn't handle \N values. Is it possible for tBigQueryOutput to write null values as empty strings (even for Integers and Floats)?
If not, is there a workaround available? (parsing each field for null value seems like a hassle for 100+ fields)
Thanks,
Jochem