The mysterious life of mr. Trim (aka. the value displacement trick)

[Stefán Baxter]

Hi,

Here is small trick I think you will like :).

   - With this minimal dataset as /tmp/test.json:
   {"dimensions":{"adults":"A"}}

   - Running this:
   select lower(p.dimensions.budgetLevel) as `field1`,
   lower(p.dimensions.adults) as `field2` from dfs.tmp.`/test.json` as p;

   - To no surprise returns this:
   +---------+---------+
   | field1  | field2  |
   +---------+---------+
   | null    | a       |
   +---------+---------+

Here comes the trick(y) part (hold your breath):

   - With the same data as a Parquet file
   CREATE TABLE dfs.tmp.`/test` AS SELECT * FROM dfs.tmp.`/test.json`;

   - The same query:
   select lower(p.dimensions.budgetLevel) as `field1`,
   lower(p.dimensions.adults) as `field2` from dfs.tmp.`/test/0_0_0.parquet`
   as p;

   - Return this:
   +---------+---------+
   | field1  | field2  |
   +---------+---------+
   | a       | null    |
   +---------+---------+

ta ta !

Best regards,
 -Stefan