Forum Discussion

amubeen's avatar
amubeen
New Contributor II
5 years ago

Loading Null values into Snowflake DB via Bulk Load Snap

Hey All, So I’m trying to load certain columns from a csv file into snowflake, but facing some issues. I have the file, properly parsed it via the csv parser, and I’ve also used a mapper to map t...
smanoharan's avatar
smanoharan
Employee
5 years ago
Copy LinkReport

@amubeen There are two ways that you can handle this use case

  1. Leave the fields you want to loaded as null as Empty in the csv file and un-select the property, “Load empty strings” in Snowflake Bulk Load. This will make sure Snowflake Bulk Load will load the empty values as null in the table

2.The null values coming from a csv parser will be a string. A condition in the mapper to convert the null String values to null will work:
$NULLABLECHAR == “null” ? null : $NULLABLECHAR

cc: @robin

  • dimitri_hristov's avatar
    dimitri_hristov
    Contributor
    6 years ago
    Copy LinkReport

    Hi @vaidyarm,

    I’ve put together a pipeline where I simulate your case. In it I propose two solutions. Assuming that the $olditem array is anywhere in the hierarchy of the input document:

    1. If you put jsonPath($,"$..olditem")[0].map(x=>{"row":x.id}) in an Expression in a Mapper, and assign it to $batch Target Path, you should get the output you described.
    2. The second solution has 2 Mapper Snaps. In the first one, I extract $olditem from anywhere in the hierarchy and assign it to $batch. In the second Mapper, i designate $batch[*] as Mapping Root, and I proceed to remap the array elements.

    Here’s the pipeline:
    Array_key_value_pair_2020_07_01.slp (9.0 KB)

    BR,
    Dimitri

    • vaidyarm's avatar
      vaidyarm
      Contributor
      6 years ago
      Copy LinkReport

      Thanks a Lot !!,

      The root element approach greatly resolved most of the deep JSON Section problems.
      one of the situations where I had multiple such cases,
      Ex: hierarchy as main >> subsection >> item >> item details

      started mapping the deepest section first using root path in mapper and finally the outermost part. it distributed the arrays as required