AnsweredAssumed Answered

"Flatten" rows with a non-JSON array?

Question asked by mattk on Aug 15, 2016
Latest reply on Aug 26, 2016 by mattk

With CSV data like:

id date array

1, 2016-01-01, "{1,2,3}"

2, 2016-01-02, "{4,5,6}"

 

I would like to "flatten" the data on the "array" column like so:

id date element

1, 2016-01-01, 1

1, 2016-01-01, 2

1, 2016-01-01, 3

2, 2016-01-02, 4

2, 2016-01-02, 5

2, 2016-01-02, 6

 

This would enable me to load the data into Parquet files and partition on the "element" column which is a common search key. Is this possible using only Drill SQL? We currently are not using other components save for MapR-FS.

 

I could of course use STRPOS to search the array column in a scan, but I believe if I could partition on the individual elements some query performance would be gained?

Outcomes