This message was deleted.

I believe

druid.generic.useDefaultValueForNull

actually does that. If a value cannot be cast to the target type, as in

CAST('foo' AS BIGINT)

, Druid either substitutes a default value (when

druid.generic.useDefaultValueForNull = true

, the default mode), or substitutes NULL (when

druid.generic.useDefaultValueForNull = false

). NULL values cast to non-nullable types are also substituted with a default value. For example, if

druid.generic.useDefaultValueForNull = true

, a null VARCHAR cast to BIGINT is converted to a zero.

Hi Bibek, data is already there in druid datasource, i need to update few columns with condition that if they are 0, set them as null

Can we try with a small data source and try to reingest the same by using above parameter.

Hi Bibek, since Druid segments are immutable once created, you would have to reingest the data on top of itself, changing the value(s) needed in the process. You can do this with both Native and MSQ ingestion, I like MSQ because you can write the statements in SQL ... here is an example using MSQ SQL ingestion to change a single field value and leave the rest of the data unchanged:

REPLACE INTO wikipedia_hr
OVERWRITE where __time >= TIMESTAMP '2016-06-27 00:00:00' and __time < TIMESTAMP '2016-06-27 01:00:00.000'
SELECT
  __time,
  "isRobot",
  "channel",
  "flags",
  "page",
  "diffUrl",
  "added",
  "comment",
  "commentLength",
  "isNew",
  "isMinor",
  case when delta = 0 then null else delta end as delta,
  "user",
  "deltaBucket",
  "deleted",
  "namespace"
FROM "wikipedia_hr"
where __time BETWEEN '2016-06-27T00:00:00.000Z' and '2016-06-27T01:00:00.000Z'
PARTITIONED BY HOUR
CLUSTERED BY "channel", "page"

thanks, it worked.

NULLIF(delta,0)

is another way to write this case statement.

Also, fyi, you can use this technique to do singleton (or multi-row) updates in Druid ... just use something like:

case when <some filter condition that identifies one or more rows that you want to update> then new_value else field_name end as field_name