You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Sudhakar Thota <st...@maprtech.com> on 2015/02/03 20:52:39 UTC
Drill - Flatten function - help please
Hi Drillers,
I have a request here about flatten function. I want to know if this is an expected behavior of drill flatten function with json documents.
Listing-1 is contents of my json collection where I have intentionally included a empty array.
Listing-2 shows that if I don’t call flatten function it shows all records.
Listing-3 shows that if I call flatten function, row6 (r6cl1…) is ignored. I am using that in my projections not in predicate, but it looks like treated as predicate.
Please correct me if I am wrong and let me know if there is any work around.
Listing:1
0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, t.c.z from dfs.`/data/nested/clicks/sthota_test_1.json` as t;
+------------+------------+------------+------------+------------+
| a | b | EXPR$2 | EXPR$3 | EXPR$4 |
+------------+------------+------------+------------+------------+
| r1cl1 | r1c2 | 1 | a string | ["1"] |
| r2cl1 | r2c2 | 2 | a string | ["1","2"] |
| r3cl1 | r3c2 | 3 | a string | ["1","2","3"] |
| r4cl1 | r4c2 | 4 | a string | ["1","2","3","4"] |
| r5cl1 | r5c2 | 5 | a string | ["1","2","3","4","5"] |
| r6cl1 | r6c2 | 6 | a string | [] |
+------------+------------+------------+------------+------------+
6 rows selected (0.125 seconds)
0: jdbc:drill:>
Listing:2
0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y from dfs.`/data/nested/clicks/sthota_test_1.json` as t;
+------------+------------+------------+------------+
| a | b | EXPR$2 | EXPR$3 |
+------------+------------+------------+------------+
| r1cl1 | r1c2 | 1 | a string |
| r2cl1 | r2c2 | 2 | a string |
| r3cl1 | r3c2 | 3 | a string |
| r4cl1 | r4c2 | 4 | a string |
| r5cl1 | r5c2 | 5 | a string |
| r6cl1 | r6c2 | 6 | a string |
+------------+------------+------------+------------+
6 rows selected (0.163 seconds)
Listing:3
0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, flatten(t.c.z) from dfs.`/data/nested/clicks/sthota_test_1.json` as t;
+------------+------------+------------+------------+------------+
| a | b | EXPR$2 | EXPR$3 | EXPR$4 |
+------------+------------+------------+------------+------------+
| r1cl1 | r1c2 | 1 | a string | 1 |
| r2cl1 | r2c2 | 2 | a string | 1 |
| r2cl1 | r2c2 | 2 | a string | 2 |
| r3cl1 | r3c2 | 3 | a string | 1 |
| r3cl1 | r3c2 | 3 | a string | 2 |
| r3cl1 | r3c2 | 3 | a string | 3 |
| r4cl1 | r4c2 | 4 | a string | 1 |
| r4cl1 | r4c2 | 4 | a string | 2 |
| r4cl1 | r4c2 | 4 | a string | 3 |
| r4cl1 | r4c2 | 4 | a string | 4 |
| r5cl1 | r5c2 | 5 | a string | 1 |
| r5cl1 | r5c2 | 5 | a string | 2 |
| r5cl1 | r5c2 | 5 | a string | 3 |
| r5cl1 | r5c2 | 5 | a string | 4 |
| r5cl1 | r5c2 | 5 | a string | 5 |
+------------+------------+------------+------------+------------+
15 rows selected (0.171 seconds)
Thanks
Sudhakar Thota
Re: Drill - Flatten function - help please
Posted by Sudhakar Thota <st...@maprtech.com>.
It is a good work around.
Thanks a lot Andries.
On Feb 3, 2015, at 3:11 PM, Andries Engelbrecht <ae...@maprtech.com> wrote:
> Sudhakar,
>
> Use an outer join for your desired outcome. As discussed flatten behaves per spec, but the spec may need some discussion.
>
> Try this for a workaround
>
> select q.a, q.b, q.c.x, q.c.y, zz.flat from dfs.twitter.`sudhakar.json` q left join (select t.a, flatten(t.c.z) as flat from dfs.twitter.`sudhakar.json` t) zz on (q.a=zz.a);
>
>
> —Andries
>
>
>
> On Feb 3, 2015, at 2:53 PM, Sudhakar Thota <st...@maprtech.com> wrote:
>
>> Jason,
>>
>> Thank you.
>>
>> I was testing this on customer data and found this limitation.
>> As I cannot put out the customer data, I have used some test data to verify it myself.
>>
>> Please let me know if there is any work around to get nulls or empty spaces wherever the array is empty.
>>
>> Steven,
>>
>> I remember using the similar one on mongo, I don’t remember that eliminating the records. But I can check. I haven’t used Big Query for this, I used it for flat tables, but I can check.
>>
>> Thanks
>> Sudhakar Thota
>>
>>
>>
>> On Feb 3, 2015, at 2:45 PM, Jason Altekruse <al...@gmail.com> wrote:
>>
>>> Sudhakar opened an issue for this, so I responded there. Steven is right,
>>> this is the current expected functionality, but I discuss there the reasons
>>> for it and opened the discussion for use cases that need this functionality.
>>>
>>> On Tue, Feb 3, 2015 at 2:28 PM, Steven Phillips <sp...@maprtech.com>
>>> wrote:
>>>
>>>> I think flatten is functioning as designed, but perhaps we need to treat
>>>> this case differently. It might make sense to return a single row with a
>>>> null for the flattened column in this case. The only model for "flatten"
>>>> that I know of is google Big Query. We should see how they handle this
>>>> case.
>>>>
>>>> On Tue, Feb 3, 2015 at 12:27 PM, MapR <st...@maprtech.com> wrote:
>>>>
>>>>> In a table, column with null values we
>>>>> present as nulls or empty values with other columns. I never had an
>>>>> experience with c1, c2, c3 (columns ) not presented when c3 has null
>>>> values
>>>>> even though c1, c2 have notnull values.
>>>>>
>>>>> Sudhakar Thota
>>>>> Sent from my iPhone
>>>>>
>>>>>> On Feb 3, 2015, at 11:57 AM, Andries Engelbrecht <
>>>>> aengelbrecht@maprtech.com> wrote:
>>>>>>
>>>>>> In listing 3 it looks like flatten is doing the correct thing by
>>>>> representing each element in the array with a new row and the element in
>>>>> the column output.
>>>>>> And also for arrays that are empty there is no output row.
>>>>>>
>>>>>> —Andries
>>>>>>
>>>>>>> On Feb 3, 2015, at 11:54 AM, Sudhakar Thota <st...@maprtech.com>
>>>>> wrote:
>>>>>>>
>>>>>>> It will be more appropriate if I add my file to have data to
>>>> facilitate
>>>>> quick test.
>>>>>>>
>>>>>>> [root@maprdemo clicks]# cat sthota_test_1.json
>>>>>>> {"a":"r1cl1", "b":"r1c2","c":{ "x" : 1, "y" : "a string", "z" : [ 1]
>>>> }}
>>>>>>> {"a":"r2cl1", "b":"r2c2","c":{ "x" : 2, "y" : "a string", "z" : [ 1,2]
>>>>> }}
>>>>>>> {"a":"r3cl1", "b":"r3c2","c":{ "x" : 3, "y" : "a string", "z" : [
>>>>> 1,2,3] }}
>>>>>>> {"a":"r4cl1", "b":"r4c2","c":{ "x" : 4, "y" : "a string", "z" : [
>>>>> 1,2,3,4] }}
>>>>>>> {"a":"r5cl1", "b":"r5c2","c":{ "x" : 5, "y" : "a string", "z" : [
>>>>> 1,2,3,4,5] }}
>>>>>>> {"a":"r6cl1", "b":"r6c2","c":{ "x" : 6, "y" : "a string" }}
>>>>>>>
>>>>>>>
>>>>>>> Begin forwarded message:
>>>>>>>
>>>>>>>> From: Sudhakar Thota <st...@maprtech.com>
>>>>>>>> Subject: Drill - Flatten function - help please
>>>>>>>> Date: February 3, 2015 at 11:52:39 AM PST
>>>>>>>> To: user@drill.apache.org
>>>>>>>>
>>>>>>>> Hi Drillers,
>>>>>>>>
>>>>>>>> I have a request here about flatten function. I want to know if this
>>>>> is an expected behavior of drill flatten function with json documents.
>>>>>>>>
>>>>>>>> Listing-1 is contents of my json collection where I have
>>>> intentionally
>>>>> included a empty array.
>>>>>>>> Listing-2 shows that if I don’t call flatten function it shows all
>>>>> records.
>>>>>>>> Listing-3 shows that if I call flatten function, row6 (r6cl1…) is
>>>>> ignored. I am using that in my projections not in predicate, but it looks
>>>>> like treated as predicate.
>>>>>>>>
>>>>>>>> Please correct me if I am wrong and let me know if there is any work
>>>>> around.
>>>>>>>>
>>>>>>>> Listing:1
>>>>>>>>
>>>>>>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, t.c.z from
>>>>> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>>> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
>>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>>> | r1cl1 | r1c2 | 1 | a string | ["1"] |
>>>>>>>> | r2cl1 | r2c2 | 2 | a string | ["1","2"] |
>>>>>>>> | r3cl1 | r3c2 | 3 | a string | ["1","2","3"] |
>>>>>>>> | r4cl1 | r4c2 | 4 | a string |
>>>>> ["1","2","3","4"] |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string |
>>>>> ["1","2","3","4","5"] |
>>>>>>>> | r6cl1 | r6c2 | 6 | a string | [] |
>>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>>> 6 rows selected (0.125 seconds)
>>>>>>>> 0: jdbc:drill:>
>>>>>>>>
>>>>>>>> Listing:2
>>>>>>>>
>>>>>>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y from
>>>>> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>>>>>>>> +------------+------------+------------+------------+
>>>>>>>> | a | b | EXPR$2 | EXPR$3 |
>>>>>>>> +------------+------------+------------+------------+
>>>>>>>> | r1cl1 | r1c2 | 1 | a string |
>>>>>>>> | r2cl1 | r2c2 | 2 | a string |
>>>>>>>> | r3cl1 | r3c2 | 3 | a string |
>>>>>>>> | r4cl1 | r4c2 | 4 | a string |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string |
>>>>>>>> | r6cl1 | r6c2 | 6 | a string |
>>>>>>>> +------------+------------+------------+------------+
>>>>>>>> 6 rows selected (0.163 seconds)
>>>>>>>>
>>>>>>>> Listing:3
>>>>>>>>
>>>>>>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, flatten(t.c.z) from
>>>>> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>>> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
>>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>>> | r1cl1 | r1c2 | 1 | a string | 1 |
>>>>>>>> | r2cl1 | r2c2 | 2 | a string | 1 |
>>>>>>>> | r2cl1 | r2c2 | 2 | a string | 2 |
>>>>>>>> | r3cl1 | r3c2 | 3 | a string | 1 |
>>>>>>>> | r3cl1 | r3c2 | 3 | a string | 2 |
>>>>>>>> | r3cl1 | r3c2 | 3 | a string | 3 |
>>>>>>>> | r4cl1 | r4c2 | 4 | a string | 1 |
>>>>>>>> | r4cl1 | r4c2 | 4 | a string | 2 |
>>>>>>>> | r4cl1 | r4c2 | 4 | a string | 3 |
>>>>>>>> | r4cl1 | r4c2 | 4 | a string | 4 |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string | 1 |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string | 2 |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string | 3 |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string | 4 |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string | 5 |
>>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>>> 15 rows selected (0.171 seconds)
>>>>>>>>
>>>>>>>> Thanks
>>>>>>>> Sudhakar Thota
>>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Steven Phillips
>>>> Software Engineer
>>>>
>>>> mapr.com
>>>>
>>
>
Re: Drill - Flatten function - help please
Posted by Andries Engelbrecht <ae...@maprtech.com>.
Result set
+------------+------------+------------+------------+------------+
| a | b | EXPR$2 | EXPR$3 | flat |
+------------+------------+------------+------------+------------+
| r1cl1 | r1c2 | 1 | a string | 1 |
| r2cl1 | r2c2 | 2 | a string | 1 |
| r2cl1 | r2c2 | 2 | a string | 2 |
| r3cl1 | r3c2 | 3 | a string | 1 |
| r3cl1 | r3c2 | 3 | a string | 3 |
| r3cl1 | r3c2 | 3 | a string | 2 |
| r4cl1 | r4c2 | 4 | a string | 1 |
| r4cl1 | r4c2 | 4 | a string | 4 |
| r4cl1 | r4c2 | 4 | a string | 3 |
| r4cl1 | r4c2 | 4 | a string | 2 |
| r5cl1 | r5c2 | 5 | a string | 1 |
| r5cl1 | r5c2 | 5 | a string | 5 |
| r5cl1 | r5c2 | 5 | a string | 4 |
| r5cl1 | r5c2 | 5 | a string | 3 |
| r5cl1 | r5c2 | 5 | a string | 2 |
| r6cl1 | r6c2 | 6 | a string | null |
+------------+------------+------------+------------+------------+
16 rows selected (0.186 seconds)
On Feb 3, 2015, at 3:11 PM, Andries Engelbrecht <ae...@maprtech.com> wrote:
> Sudhakar,
>
> Use an outer join for your desired outcome. As discussed flatten behaves per spec, but the spec may need some discussion.
>
> Try this for a workaround
>
> select q.a, q.b, q.c.x, q.c.y, zz.flat from dfs.twitter.`sudhakar.json` q left join (select t.a, flatten(t.c.z) as flat from dfs.twitter.`sudhakar.json` t) zz on (q.a=zz.a);
>
>
> —Andries
>
>
>
> On Feb 3, 2015, at 2:53 PM, Sudhakar Thota <st...@maprtech.com> wrote:
>
>> Jason,
>>
>> Thank you.
>>
>> I was testing this on customer data and found this limitation.
>> As I cannot put out the customer data, I have used some test data to verify it myself.
>>
>> Please let me know if there is any work around to get nulls or empty spaces wherever the array is empty.
>>
>> Steven,
>>
>> I remember using the similar one on mongo, I don’t remember that eliminating the records. But I can check. I haven’t used Big Query for this, I used it for flat tables, but I can check.
>>
>> Thanks
>> Sudhakar Thota
>>
>>
>>
>> On Feb 3, 2015, at 2:45 PM, Jason Altekruse <al...@gmail.com> wrote:
>>
>>> Sudhakar opened an issue for this, so I responded there. Steven is right,
>>> this is the current expected functionality, but I discuss there the reasons
>>> for it and opened the discussion for use cases that need this functionality.
>>>
>>> On Tue, Feb 3, 2015 at 2:28 PM, Steven Phillips <sp...@maprtech.com>
>>> wrote:
>>>
>>>> I think flatten is functioning as designed, but perhaps we need to treat
>>>> this case differently. It might make sense to return a single row with a
>>>> null for the flattened column in this case. The only model for "flatten"
>>>> that I know of is google Big Query. We should see how they handle this
>>>> case.
>>>>
>>>> On Tue, Feb 3, 2015 at 12:27 PM, MapR <st...@maprtech.com> wrote:
>>>>
>>>>> In a table, column with null values we
>>>>> present as nulls or empty values with other columns. I never had an
>>>>> experience with c1, c2, c3 (columns ) not presented when c3 has null
>>>> values
>>>>> even though c1, c2 have notnull values.
>>>>>
>>>>> Sudhakar Thota
>>>>> Sent from my iPhone
>>>>>
>>>>>> On Feb 3, 2015, at 11:57 AM, Andries Engelbrecht <
>>>>> aengelbrecht@maprtech.com> wrote:
>>>>>>
>>>>>> In listing 3 it looks like flatten is doing the correct thing by
>>>>> representing each element in the array with a new row and the element in
>>>>> the column output.
>>>>>> And also for arrays that are empty there is no output row.
>>>>>>
>>>>>> —Andries
>>>>>>
>>>>>>> On Feb 3, 2015, at 11:54 AM, Sudhakar Thota <st...@maprtech.com>
>>>>> wrote:
>>>>>>>
>>>>>>> It will be more appropriate if I add my file to have data to
>>>> facilitate
>>>>> quick test.
>>>>>>>
>>>>>>> [root@maprdemo clicks]# cat sthota_test_1.json
>>>>>>> {"a":"r1cl1", "b":"r1c2","c":{ "x" : 1, "y" : "a string", "z" : [ 1]
>>>> }}
>>>>>>> {"a":"r2cl1", "b":"r2c2","c":{ "x" : 2, "y" : "a string", "z" : [ 1,2]
>>>>> }}
>>>>>>> {"a":"r3cl1", "b":"r3c2","c":{ "x" : 3, "y" : "a string", "z" : [
>>>>> 1,2,3] }}
>>>>>>> {"a":"r4cl1", "b":"r4c2","c":{ "x" : 4, "y" : "a string", "z" : [
>>>>> 1,2,3,4] }}
>>>>>>> {"a":"r5cl1", "b":"r5c2","c":{ "x" : 5, "y" : "a string", "z" : [
>>>>> 1,2,3,4,5] }}
>>>>>>> {"a":"r6cl1", "b":"r6c2","c":{ "x" : 6, "y" : "a string" }}
>>>>>>>
>>>>>>>
>>>>>>> Begin forwarded message:
>>>>>>>
>>>>>>>> From: Sudhakar Thota <st...@maprtech.com>
>>>>>>>> Subject: Drill - Flatten function - help please
>>>>>>>> Date: February 3, 2015 at 11:52:39 AM PST
>>>>>>>> To: user@drill.apache.org
>>>>>>>>
>>>>>>>> Hi Drillers,
>>>>>>>>
>>>>>>>> I have a request here about flatten function. I want to know if this
>>>>> is an expected behavior of drill flatten function with json documents.
>>>>>>>>
>>>>>>>> Listing-1 is contents of my json collection where I have
>>>> intentionally
>>>>> included a empty array.
>>>>>>>> Listing-2 shows that if I don’t call flatten function it shows all
>>>>> records.
>>>>>>>> Listing-3 shows that if I call flatten function, row6 (r6cl1…) is
>>>>> ignored. I am using that in my projections not in predicate, but it looks
>>>>> like treated as predicate.
>>>>>>>>
>>>>>>>> Please correct me if I am wrong and let me know if there is any work
>>>>> around.
>>>>>>>>
>>>>>>>> Listing:1
>>>>>>>>
>>>>>>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, t.c.z from
>>>>> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>>> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
>>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>>> | r1cl1 | r1c2 | 1 | a string | ["1"] |
>>>>>>>> | r2cl1 | r2c2 | 2 | a string | ["1","2"] |
>>>>>>>> | r3cl1 | r3c2 | 3 | a string | ["1","2","3"] |
>>>>>>>> | r4cl1 | r4c2 | 4 | a string |
>>>>> ["1","2","3","4"] |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string |
>>>>> ["1","2","3","4","5"] |
>>>>>>>> | r6cl1 | r6c2 | 6 | a string | [] |
>>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>>> 6 rows selected (0.125 seconds)
>>>>>>>> 0: jdbc:drill:>
>>>>>>>>
>>>>>>>> Listing:2
>>>>>>>>
>>>>>>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y from
>>>>> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>>>>>>>> +------------+------------+------------+------------+
>>>>>>>> | a | b | EXPR$2 | EXPR$3 |
>>>>>>>> +------------+------------+------------+------------+
>>>>>>>> | r1cl1 | r1c2 | 1 | a string |
>>>>>>>> | r2cl1 | r2c2 | 2 | a string |
>>>>>>>> | r3cl1 | r3c2 | 3 | a string |
>>>>>>>> | r4cl1 | r4c2 | 4 | a string |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string |
>>>>>>>> | r6cl1 | r6c2 | 6 | a string |
>>>>>>>> +------------+------------+------------+------------+
>>>>>>>> 6 rows selected (0.163 seconds)
>>>>>>>>
>>>>>>>> Listing:3
>>>>>>>>
>>>>>>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, flatten(t.c.z) from
>>>>> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>>> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
>>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>>> | r1cl1 | r1c2 | 1 | a string | 1 |
>>>>>>>> | r2cl1 | r2c2 | 2 | a string | 1 |
>>>>>>>> | r2cl1 | r2c2 | 2 | a string | 2 |
>>>>>>>> | r3cl1 | r3c2 | 3 | a string | 1 |
>>>>>>>> | r3cl1 | r3c2 | 3 | a string | 2 |
>>>>>>>> | r3cl1 | r3c2 | 3 | a string | 3 |
>>>>>>>> | r4cl1 | r4c2 | 4 | a string | 1 |
>>>>>>>> | r4cl1 | r4c2 | 4 | a string | 2 |
>>>>>>>> | r4cl1 | r4c2 | 4 | a string | 3 |
>>>>>>>> | r4cl1 | r4c2 | 4 | a string | 4 |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string | 1 |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string | 2 |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string | 3 |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string | 4 |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string | 5 |
>>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>>> 15 rows selected (0.171 seconds)
>>>>>>>>
>>>>>>>> Thanks
>>>>>>>> Sudhakar Thota
>>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Steven Phillips
>>>> Software Engineer
>>>>
>>>> mapr.com
>>>>
>>
>
Re: Drill - Flatten function - help please
Posted by Andries Engelbrecht <ae...@maprtech.com>.
Sudhakar,
Use an outer join for your desired outcome. As discussed flatten behaves per spec, but the spec may need some discussion.
Try this for a workaround
select q.a, q.b, q.c.x, q.c.y, zz.flat from dfs.twitter.`sudhakar.json` q left join (select t.a, flatten(t.c.z) as flat from dfs.twitter.`sudhakar.json` t) zz on (q.a=zz.a);
—Andries
On Feb 3, 2015, at 2:53 PM, Sudhakar Thota <st...@maprtech.com> wrote:
> Jason,
>
> Thank you.
>
> I was testing this on customer data and found this limitation.
> As I cannot put out the customer data, I have used some test data to verify it myself.
>
> Please let me know if there is any work around to get nulls or empty spaces wherever the array is empty.
>
> Steven,
>
> I remember using the similar one on mongo, I don’t remember that eliminating the records. But I can check. I haven’t used Big Query for this, I used it for flat tables, but I can check.
>
> Thanks
> Sudhakar Thota
>
>
>
> On Feb 3, 2015, at 2:45 PM, Jason Altekruse <al...@gmail.com> wrote:
>
>> Sudhakar opened an issue for this, so I responded there. Steven is right,
>> this is the current expected functionality, but I discuss there the reasons
>> for it and opened the discussion for use cases that need this functionality.
>>
>> On Tue, Feb 3, 2015 at 2:28 PM, Steven Phillips <sp...@maprtech.com>
>> wrote:
>>
>>> I think flatten is functioning as designed, but perhaps we need to treat
>>> this case differently. It might make sense to return a single row with a
>>> null for the flattened column in this case. The only model for "flatten"
>>> that I know of is google Big Query. We should see how they handle this
>>> case.
>>>
>>> On Tue, Feb 3, 2015 at 12:27 PM, MapR <st...@maprtech.com> wrote:
>>>
>>>> In a table, column with null values we
>>>> present as nulls or empty values with other columns. I never had an
>>>> experience with c1, c2, c3 (columns ) not presented when c3 has null
>>> values
>>>> even though c1, c2 have notnull values.
>>>>
>>>> Sudhakar Thota
>>>> Sent from my iPhone
>>>>
>>>>> On Feb 3, 2015, at 11:57 AM, Andries Engelbrecht <
>>>> aengelbrecht@maprtech.com> wrote:
>>>>>
>>>>> In listing 3 it looks like flatten is doing the correct thing by
>>>> representing each element in the array with a new row and the element in
>>>> the column output.
>>>>> And also for arrays that are empty there is no output row.
>>>>>
>>>>> —Andries
>>>>>
>>>>>> On Feb 3, 2015, at 11:54 AM, Sudhakar Thota <st...@maprtech.com>
>>>> wrote:
>>>>>>
>>>>>> It will be more appropriate if I add my file to have data to
>>> facilitate
>>>> quick test.
>>>>>>
>>>>>> [root@maprdemo clicks]# cat sthota_test_1.json
>>>>>> {"a":"r1cl1", "b":"r1c2","c":{ "x" : 1, "y" : "a string", "z" : [ 1]
>>> }}
>>>>>> {"a":"r2cl1", "b":"r2c2","c":{ "x" : 2, "y" : "a string", "z" : [ 1,2]
>>>> }}
>>>>>> {"a":"r3cl1", "b":"r3c2","c":{ "x" : 3, "y" : "a string", "z" : [
>>>> 1,2,3] }}
>>>>>> {"a":"r4cl1", "b":"r4c2","c":{ "x" : 4, "y" : "a string", "z" : [
>>>> 1,2,3,4] }}
>>>>>> {"a":"r5cl1", "b":"r5c2","c":{ "x" : 5, "y" : "a string", "z" : [
>>>> 1,2,3,4,5] }}
>>>>>> {"a":"r6cl1", "b":"r6c2","c":{ "x" : 6, "y" : "a string" }}
>>>>>>
>>>>>>
>>>>>> Begin forwarded message:
>>>>>>
>>>>>>> From: Sudhakar Thota <st...@maprtech.com>
>>>>>>> Subject: Drill - Flatten function - help please
>>>>>>> Date: February 3, 2015 at 11:52:39 AM PST
>>>>>>> To: user@drill.apache.org
>>>>>>>
>>>>>>> Hi Drillers,
>>>>>>>
>>>>>>> I have a request here about flatten function. I want to know if this
>>>> is an expected behavior of drill flatten function with json documents.
>>>>>>>
>>>>>>> Listing-1 is contents of my json collection where I have
>>> intentionally
>>>> included a empty array.
>>>>>>> Listing-2 shows that if I don’t call flatten function it shows all
>>>> records.
>>>>>>> Listing-3 shows that if I call flatten function, row6 (r6cl1…) is
>>>> ignored. I am using that in my projections not in predicate, but it looks
>>>> like treated as predicate.
>>>>>>>
>>>>>>> Please correct me if I am wrong and let me know if there is any work
>>>> around.
>>>>>>>
>>>>>>> Listing:1
>>>>>>>
>>>>>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, t.c.z from
>>>> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>> | r1cl1 | r1c2 | 1 | a string | ["1"] |
>>>>>>> | r2cl1 | r2c2 | 2 | a string | ["1","2"] |
>>>>>>> | r3cl1 | r3c2 | 3 | a string | ["1","2","3"] |
>>>>>>> | r4cl1 | r4c2 | 4 | a string |
>>>> ["1","2","3","4"] |
>>>>>>> | r5cl1 | r5c2 | 5 | a string |
>>>> ["1","2","3","4","5"] |
>>>>>>> | r6cl1 | r6c2 | 6 | a string | [] |
>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>> 6 rows selected (0.125 seconds)
>>>>>>> 0: jdbc:drill:>
>>>>>>>
>>>>>>> Listing:2
>>>>>>>
>>>>>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y from
>>>> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>>>>>>> +------------+------------+------------+------------+
>>>>>>> | a | b | EXPR$2 | EXPR$3 |
>>>>>>> +------------+------------+------------+------------+
>>>>>>> | r1cl1 | r1c2 | 1 | a string |
>>>>>>> | r2cl1 | r2c2 | 2 | a string |
>>>>>>> | r3cl1 | r3c2 | 3 | a string |
>>>>>>> | r4cl1 | r4c2 | 4 | a string |
>>>>>>> | r5cl1 | r5c2 | 5 | a string |
>>>>>>> | r6cl1 | r6c2 | 6 | a string |
>>>>>>> +------------+------------+------------+------------+
>>>>>>> 6 rows selected (0.163 seconds)
>>>>>>>
>>>>>>> Listing:3
>>>>>>>
>>>>>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, flatten(t.c.z) from
>>>> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>> | r1cl1 | r1c2 | 1 | a string | 1 |
>>>>>>> | r2cl1 | r2c2 | 2 | a string | 1 |
>>>>>>> | r2cl1 | r2c2 | 2 | a string | 2 |
>>>>>>> | r3cl1 | r3c2 | 3 | a string | 1 |
>>>>>>> | r3cl1 | r3c2 | 3 | a string | 2 |
>>>>>>> | r3cl1 | r3c2 | 3 | a string | 3 |
>>>>>>> | r4cl1 | r4c2 | 4 | a string | 1 |
>>>>>>> | r4cl1 | r4c2 | 4 | a string | 2 |
>>>>>>> | r4cl1 | r4c2 | 4 | a string | 3 |
>>>>>>> | r4cl1 | r4c2 | 4 | a string | 4 |
>>>>>>> | r5cl1 | r5c2 | 5 | a string | 1 |
>>>>>>> | r5cl1 | r5c2 | 5 | a string | 2 |
>>>>>>> | r5cl1 | r5c2 | 5 | a string | 3 |
>>>>>>> | r5cl1 | r5c2 | 5 | a string | 4 |
>>>>>>> | r5cl1 | r5c2 | 5 | a string | 5 |
>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>> 15 rows selected (0.171 seconds)
>>>>>>>
>>>>>>> Thanks
>>>>>>> Sudhakar Thota
>>>>>
>>>>
>>>
>>>
>>>
>>> --
>>> Steven Phillips
>>> Software Engineer
>>>
>>> mapr.com
>>>
>
Re: Drill - Flatten function - help please
Posted by Andries Engelbrecht <ae...@maprtech.com>.
I would think an outer join will be more expensive than a predicate on null.
Having flatten produce the extra rows with null is much simpler and easier problem to deal to filter them out, than having to do the workaround to produce the missing rows if desired with an outer join.
Something form an end user perspective to consider.
—Andries
On Feb 3, 2015, at 3:21 PM, Jason Altekruse <al...@gmail.com> wrote:
> I tried looking in the BigQuery docs for a reference point on this. I
> cannot find anywhere that specifies the behavior with empty lists, nor did
> a few google searches and stackoverflow pages shed any light on it. Its
> seems like we might need to just load data into bigquery and test it out
> ourselves to find the answer.
>
> I do suspect that they act the same as we do currently for the reasons I
> mention on the JIRA. Please take some time to review my comments there and
> describe the types of queries being used, they may justify a change to
> flatten. I think there is value to the current implementations behavior,
> but we should review it in the context of all use cases.
>
> -Jason
>
> On Tue, Feb 3, 2015 at 2:53 PM, Sudhakar Thota <st...@maprtech.com> wrote:
>
>> Jason,
>>
>> Thank you.
>>
>> I was testing this on customer data and found this limitation.
>> As I cannot put out the customer data, I have used some test data to
>> verify it myself.
>>
>> Please let me know if there is any work around to get nulls or empty
>> spaces wherever the array is empty.
>>
>> Steven,
>>
>> I remember using the similar one on mongo, I don’t remember that
>> eliminating the records. But I can check. I haven’t used Big Query for
>> this, I used it for flat tables, but I can check.
>>
>> Thanks
>> Sudhakar Thota
>>
>>
>>
>> On Feb 3, 2015, at 2:45 PM, Jason Altekruse <al...@gmail.com>
>> wrote:
>>
>>> Sudhakar opened an issue for this, so I responded there. Steven is right,
>>> this is the current expected functionality, but I discuss there the
>> reasons
>>> for it and opened the discussion for use cases that need this
>> functionality.
>>>
>>> On Tue, Feb 3, 2015 at 2:28 PM, Steven Phillips <sp...@maprtech.com>
>>> wrote:
>>>
>>>> I think flatten is functioning as designed, but perhaps we need to treat
>>>> this case differently. It might make sense to return a single row with a
>>>> null for the flattened column in this case. The only model for "flatten"
>>>> that I know of is google Big Query. We should see how they handle this
>>>> case.
>>>>
>>>> On Tue, Feb 3, 2015 at 12:27 PM, MapR <st...@maprtech.com> wrote:
>>>>
>>>>> In a table, column with null values we
>>>>> present as nulls or empty values with other columns. I never had an
>>>>> experience with c1, c2, c3 (columns ) not presented when c3 has null
>>>> values
>>>>> even though c1, c2 have notnull values.
>>>>>
>>>>> Sudhakar Thota
>>>>> Sent from my iPhone
>>>>>
>>>>>> On Feb 3, 2015, at 11:57 AM, Andries Engelbrecht <
>>>>> aengelbrecht@maprtech.com> wrote:
>>>>>>
>>>>>> In listing 3 it looks like flatten is doing the correct thing by
>>>>> representing each element in the array with a new row and the element
>> in
>>>>> the column output.
>>>>>> And also for arrays that are empty there is no output row.
>>>>>>
>>>>>> —Andries
>>>>>>
>>>>>>> On Feb 3, 2015, at 11:54 AM, Sudhakar Thota <st...@maprtech.com>
>>>>> wrote:
>>>>>>>
>>>>>>> It will be more appropriate if I add my file to have data to
>>>> facilitate
>>>>> quick test.
>>>>>>>
>>>>>>> [root@maprdemo clicks]# cat sthota_test_1.json
>>>>>>> {"a":"r1cl1", "b":"r1c2","c":{ "x" : 1, "y" : "a string", "z" : [ 1]
>>>> }}
>>>>>>> {"a":"r2cl1", "b":"r2c2","c":{ "x" : 2, "y" : "a string", "z" : [
>> 1,2]
>>>>> }}
>>>>>>> {"a":"r3cl1", "b":"r3c2","c":{ "x" : 3, "y" : "a string", "z" : [
>>>>> 1,2,3] }}
>>>>>>> {"a":"r4cl1", "b":"r4c2","c":{ "x" : 4, "y" : "a string", "z" : [
>>>>> 1,2,3,4] }}
>>>>>>> {"a":"r5cl1", "b":"r5c2","c":{ "x" : 5, "y" : "a string", "z" : [
>>>>> 1,2,3,4,5] }}
>>>>>>> {"a":"r6cl1", "b":"r6c2","c":{ "x" : 6, "y" : "a string" }}
>>>>>>>
>>>>>>>
>>>>>>> Begin forwarded message:
>>>>>>>
>>>>>>>> From: Sudhakar Thota <st...@maprtech.com>
>>>>>>>> Subject: Drill - Flatten function - help please
>>>>>>>> Date: February 3, 2015 at 11:52:39 AM PST
>>>>>>>> To: user@drill.apache.org
>>>>>>>>
>>>>>>>> Hi Drillers,
>>>>>>>>
>>>>>>>> I have a request here about flatten function. I want to know if this
>>>>> is an expected behavior of drill flatten function with json documents.
>>>>>>>>
>>>>>>>> Listing-1 is contents of my json collection where I have
>>>> intentionally
>>>>> included a empty array.
>>>>>>>> Listing-2 shows that if I don’t call flatten function it shows all
>>>>> records.
>>>>>>>> Listing-3 shows that if I call flatten function, row6 (r6cl1…) is
>>>>> ignored. I am using that in my projections not in predicate, but it
>> looks
>>>>> like treated as predicate.
>>>>>>>>
>>>>>>>> Please correct me if I am wrong and let me know if there is any work
>>>>> around.
>>>>>>>>
>>>>>>>> Listing:1
>>>>>>>>
>>>>>>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, t.c.z from
>>>>> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>>> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
>>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>>> | r1cl1 | r1c2 | 1 | a string | ["1"] |
>>>>>>>> | r2cl1 | r2c2 | 2 | a string | ["1","2"] |
>>>>>>>> | r3cl1 | r3c2 | 3 | a string | ["1","2","3"]
>> |
>>>>>>>> | r4cl1 | r4c2 | 4 | a string |
>>>>> ["1","2","3","4"] |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string |
>>>>> ["1","2","3","4","5"] |
>>>>>>>> | r6cl1 | r6c2 | 6 | a string | [] |
>>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>>> 6 rows selected (0.125 seconds)
>>>>>>>> 0: jdbc:drill:>
>>>>>>>>
>>>>>>>> Listing:2
>>>>>>>>
>>>>>>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y from
>>>>> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>>>>>>>> +------------+------------+------------+------------+
>>>>>>>> | a | b | EXPR$2 | EXPR$3 |
>>>>>>>> +------------+------------+------------+------------+
>>>>>>>> | r1cl1 | r1c2 | 1 | a string |
>>>>>>>> | r2cl1 | r2c2 | 2 | a string |
>>>>>>>> | r3cl1 | r3c2 | 3 | a string |
>>>>>>>> | r4cl1 | r4c2 | 4 | a string |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string |
>>>>>>>> | r6cl1 | r6c2 | 6 | a string |
>>>>>>>> +------------+------------+------------+------------+
>>>>>>>> 6 rows selected (0.163 seconds)
>>>>>>>>
>>>>>>>> Listing:3
>>>>>>>>
>>>>>>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, flatten(t.c.z) from
>>>>> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>>> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
>>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>>> | r1cl1 | r1c2 | 1 | a string | 1 |
>>>>>>>> | r2cl1 | r2c2 | 2 | a string | 1 |
>>>>>>>> | r2cl1 | r2c2 | 2 | a string | 2 |
>>>>>>>> | r3cl1 | r3c2 | 3 | a string | 1 |
>>>>>>>> | r3cl1 | r3c2 | 3 | a string | 2 |
>>>>>>>> | r3cl1 | r3c2 | 3 | a string | 3 |
>>>>>>>> | r4cl1 | r4c2 | 4 | a string | 1 |
>>>>>>>> | r4cl1 | r4c2 | 4 | a string | 2 |
>>>>>>>> | r4cl1 | r4c2 | 4 | a string | 3 |
>>>>>>>> | r4cl1 | r4c2 | 4 | a string | 4 |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string | 1 |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string | 2 |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string | 3 |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string | 4 |
>>>>>>>> | r5cl1 | r5c2 | 5 | a string | 5 |
>>>>>>>> +------------+------------+------------+------------+------------+
>>>>>>>> 15 rows selected (0.171 seconds)
>>>>>>>>
>>>>>>>> Thanks
>>>>>>>> Sudhakar Thota
>>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Steven Phillips
>>>> Software Engineer
>>>>
>>>> mapr.com
>>>>
>>
>>
Re: Drill - Flatten function - help please
Posted by Jason Altekruse <al...@gmail.com>.
I tried looking in the BigQuery docs for a reference point on this. I
cannot find anywhere that specifies the behavior with empty lists, nor did
a few google searches and stackoverflow pages shed any light on it. Its
seems like we might need to just load data into bigquery and test it out
ourselves to find the answer.
I do suspect that they act the same as we do currently for the reasons I
mention on the JIRA. Please take some time to review my comments there and
describe the types of queries being used, they may justify a change to
flatten. I think there is value to the current implementations behavior,
but we should review it in the context of all use cases.
-Jason
On Tue, Feb 3, 2015 at 2:53 PM, Sudhakar Thota <st...@maprtech.com> wrote:
> Jason,
>
> Thank you.
>
> I was testing this on customer data and found this limitation.
> As I cannot put out the customer data, I have used some test data to
> verify it myself.
>
> Please let me know if there is any work around to get nulls or empty
> spaces wherever the array is empty.
>
> Steven,
>
> I remember using the similar one on mongo, I don’t remember that
> eliminating the records. But I can check. I haven’t used Big Query for
> this, I used it for flat tables, but I can check.
>
> Thanks
> Sudhakar Thota
>
>
>
> On Feb 3, 2015, at 2:45 PM, Jason Altekruse <al...@gmail.com>
> wrote:
>
> > Sudhakar opened an issue for this, so I responded there. Steven is right,
> > this is the current expected functionality, but I discuss there the
> reasons
> > for it and opened the discussion for use cases that need this
> functionality.
> >
> > On Tue, Feb 3, 2015 at 2:28 PM, Steven Phillips <sp...@maprtech.com>
> > wrote:
> >
> >> I think flatten is functioning as designed, but perhaps we need to treat
> >> this case differently. It might make sense to return a single row with a
> >> null for the flattened column in this case. The only model for "flatten"
> >> that I know of is google Big Query. We should see how they handle this
> >> case.
> >>
> >> On Tue, Feb 3, 2015 at 12:27 PM, MapR <st...@maprtech.com> wrote:
> >>
> >>> In a table, column with null values we
> >>> present as nulls or empty values with other columns. I never had an
> >>> experience with c1, c2, c3 (columns ) not presented when c3 has null
> >> values
> >>> even though c1, c2 have notnull values.
> >>>
> >>> Sudhakar Thota
> >>> Sent from my iPhone
> >>>
> >>>> On Feb 3, 2015, at 11:57 AM, Andries Engelbrecht <
> >>> aengelbrecht@maprtech.com> wrote:
> >>>>
> >>>> In listing 3 it looks like flatten is doing the correct thing by
> >>> representing each element in the array with a new row and the element
> in
> >>> the column output.
> >>>> And also for arrays that are empty there is no output row.
> >>>>
> >>>> —Andries
> >>>>
> >>>>> On Feb 3, 2015, at 11:54 AM, Sudhakar Thota <st...@maprtech.com>
> >>> wrote:
> >>>>>
> >>>>> It will be more appropriate if I add my file to have data to
> >> facilitate
> >>> quick test.
> >>>>>
> >>>>> [root@maprdemo clicks]# cat sthota_test_1.json
> >>>>> {"a":"r1cl1", "b":"r1c2","c":{ "x" : 1, "y" : "a string", "z" : [ 1]
> >> }}
> >>>>> {"a":"r2cl1", "b":"r2c2","c":{ "x" : 2, "y" : "a string", "z" : [
> 1,2]
> >>> }}
> >>>>> {"a":"r3cl1", "b":"r3c2","c":{ "x" : 3, "y" : "a string", "z" : [
> >>> 1,2,3] }}
> >>>>> {"a":"r4cl1", "b":"r4c2","c":{ "x" : 4, "y" : "a string", "z" : [
> >>> 1,2,3,4] }}
> >>>>> {"a":"r5cl1", "b":"r5c2","c":{ "x" : 5, "y" : "a string", "z" : [
> >>> 1,2,3,4,5] }}
> >>>>> {"a":"r6cl1", "b":"r6c2","c":{ "x" : 6, "y" : "a string" }}
> >>>>>
> >>>>>
> >>>>> Begin forwarded message:
> >>>>>
> >>>>>> From: Sudhakar Thota <st...@maprtech.com>
> >>>>>> Subject: Drill - Flatten function - help please
> >>>>>> Date: February 3, 2015 at 11:52:39 AM PST
> >>>>>> To: user@drill.apache.org
> >>>>>>
> >>>>>> Hi Drillers,
> >>>>>>
> >>>>>> I have a request here about flatten function. I want to know if this
> >>> is an expected behavior of drill flatten function with json documents.
> >>>>>>
> >>>>>> Listing-1 is contents of my json collection where I have
> >> intentionally
> >>> included a empty array.
> >>>>>> Listing-2 shows that if I don’t call flatten function it shows all
> >>> records.
> >>>>>> Listing-3 shows that if I call flatten function, row6 (r6cl1…) is
> >>> ignored. I am using that in my projections not in predicate, but it
> looks
> >>> like treated as predicate.
> >>>>>>
> >>>>>> Please correct me if I am wrong and let me know if there is any work
> >>> around.
> >>>>>>
> >>>>>> Listing:1
> >>>>>>
> >>>>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, t.c.z from
> >>> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
> >>>>>> +------------+------------+------------+------------+------------+
> >>>>>> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
> >>>>>> +------------+------------+------------+------------+------------+
> >>>>>> | r1cl1 | r1c2 | 1 | a string | ["1"] |
> >>>>>> | r2cl1 | r2c2 | 2 | a string | ["1","2"] |
> >>>>>> | r3cl1 | r3c2 | 3 | a string | ["1","2","3"]
> |
> >>>>>> | r4cl1 | r4c2 | 4 | a string |
> >>> ["1","2","3","4"] |
> >>>>>> | r5cl1 | r5c2 | 5 | a string |
> >>> ["1","2","3","4","5"] |
> >>>>>> | r6cl1 | r6c2 | 6 | a string | [] |
> >>>>>> +------------+------------+------------+------------+------------+
> >>>>>> 6 rows selected (0.125 seconds)
> >>>>>> 0: jdbc:drill:>
> >>>>>>
> >>>>>> Listing:2
> >>>>>>
> >>>>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y from
> >>> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
> >>>>>> +------------+------------+------------+------------+
> >>>>>> | a | b | EXPR$2 | EXPR$3 |
> >>>>>> +------------+------------+------------+------------+
> >>>>>> | r1cl1 | r1c2 | 1 | a string |
> >>>>>> | r2cl1 | r2c2 | 2 | a string |
> >>>>>> | r3cl1 | r3c2 | 3 | a string |
> >>>>>> | r4cl1 | r4c2 | 4 | a string |
> >>>>>> | r5cl1 | r5c2 | 5 | a string |
> >>>>>> | r6cl1 | r6c2 | 6 | a string |
> >>>>>> +------------+------------+------------+------------+
> >>>>>> 6 rows selected (0.163 seconds)
> >>>>>>
> >>>>>> Listing:3
> >>>>>>
> >>>>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, flatten(t.c.z) from
> >>> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
> >>>>>> +------------+------------+------------+------------+------------+
> >>>>>> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
> >>>>>> +------------+------------+------------+------------+------------+
> >>>>>> | r1cl1 | r1c2 | 1 | a string | 1 |
> >>>>>> | r2cl1 | r2c2 | 2 | a string | 1 |
> >>>>>> | r2cl1 | r2c2 | 2 | a string | 2 |
> >>>>>> | r3cl1 | r3c2 | 3 | a string | 1 |
> >>>>>> | r3cl1 | r3c2 | 3 | a string | 2 |
> >>>>>> | r3cl1 | r3c2 | 3 | a string | 3 |
> >>>>>> | r4cl1 | r4c2 | 4 | a string | 1 |
> >>>>>> | r4cl1 | r4c2 | 4 | a string | 2 |
> >>>>>> | r4cl1 | r4c2 | 4 | a string | 3 |
> >>>>>> | r4cl1 | r4c2 | 4 | a string | 4 |
> >>>>>> | r5cl1 | r5c2 | 5 | a string | 1 |
> >>>>>> | r5cl1 | r5c2 | 5 | a string | 2 |
> >>>>>> | r5cl1 | r5c2 | 5 | a string | 3 |
> >>>>>> | r5cl1 | r5c2 | 5 | a string | 4 |
> >>>>>> | r5cl1 | r5c2 | 5 | a string | 5 |
> >>>>>> +------------+------------+------------+------------+------------+
> >>>>>> 15 rows selected (0.171 seconds)
> >>>>>>
> >>>>>> Thanks
> >>>>>> Sudhakar Thota
> >>>>
> >>>
> >>
> >>
> >>
> >> --
> >> Steven Phillips
> >> Software Engineer
> >>
> >> mapr.com
> >>
>
>
Re: Drill - Flatten function - help please
Posted by Sudhakar Thota <st...@maprtech.com>.
Jason,
Thank you.
I was testing this on customer data and found this limitation.
As I cannot put out the customer data, I have used some test data to verify it myself.
Please let me know if there is any work around to get nulls or empty spaces wherever the array is empty.
Steven,
I remember using the similar one on mongo, I don’t remember that eliminating the records. But I can check. I haven’t used Big Query for this, I used it for flat tables, but I can check.
Thanks
Sudhakar Thota
On Feb 3, 2015, at 2:45 PM, Jason Altekruse <al...@gmail.com> wrote:
> Sudhakar opened an issue for this, so I responded there. Steven is right,
> this is the current expected functionality, but I discuss there the reasons
> for it and opened the discussion for use cases that need this functionality.
>
> On Tue, Feb 3, 2015 at 2:28 PM, Steven Phillips <sp...@maprtech.com>
> wrote:
>
>> I think flatten is functioning as designed, but perhaps we need to treat
>> this case differently. It might make sense to return a single row with a
>> null for the flattened column in this case. The only model for "flatten"
>> that I know of is google Big Query. We should see how they handle this
>> case.
>>
>> On Tue, Feb 3, 2015 at 12:27 PM, MapR <st...@maprtech.com> wrote:
>>
>>> In a table, column with null values we
>>> present as nulls or empty values with other columns. I never had an
>>> experience with c1, c2, c3 (columns ) not presented when c3 has null
>> values
>>> even though c1, c2 have notnull values.
>>>
>>> Sudhakar Thota
>>> Sent from my iPhone
>>>
>>>> On Feb 3, 2015, at 11:57 AM, Andries Engelbrecht <
>>> aengelbrecht@maprtech.com> wrote:
>>>>
>>>> In listing 3 it looks like flatten is doing the correct thing by
>>> representing each element in the array with a new row and the element in
>>> the column output.
>>>> And also for arrays that are empty there is no output row.
>>>>
>>>> —Andries
>>>>
>>>>> On Feb 3, 2015, at 11:54 AM, Sudhakar Thota <st...@maprtech.com>
>>> wrote:
>>>>>
>>>>> It will be more appropriate if I add my file to have data to
>> facilitate
>>> quick test.
>>>>>
>>>>> [root@maprdemo clicks]# cat sthota_test_1.json
>>>>> {"a":"r1cl1", "b":"r1c2","c":{ "x" : 1, "y" : "a string", "z" : [ 1]
>> }}
>>>>> {"a":"r2cl1", "b":"r2c2","c":{ "x" : 2, "y" : "a string", "z" : [ 1,2]
>>> }}
>>>>> {"a":"r3cl1", "b":"r3c2","c":{ "x" : 3, "y" : "a string", "z" : [
>>> 1,2,3] }}
>>>>> {"a":"r4cl1", "b":"r4c2","c":{ "x" : 4, "y" : "a string", "z" : [
>>> 1,2,3,4] }}
>>>>> {"a":"r5cl1", "b":"r5c2","c":{ "x" : 5, "y" : "a string", "z" : [
>>> 1,2,3,4,5] }}
>>>>> {"a":"r6cl1", "b":"r6c2","c":{ "x" : 6, "y" : "a string" }}
>>>>>
>>>>>
>>>>> Begin forwarded message:
>>>>>
>>>>>> From: Sudhakar Thota <st...@maprtech.com>
>>>>>> Subject: Drill - Flatten function - help please
>>>>>> Date: February 3, 2015 at 11:52:39 AM PST
>>>>>> To: user@drill.apache.org
>>>>>>
>>>>>> Hi Drillers,
>>>>>>
>>>>>> I have a request here about flatten function. I want to know if this
>>> is an expected behavior of drill flatten function with json documents.
>>>>>>
>>>>>> Listing-1 is contents of my json collection where I have
>> intentionally
>>> included a empty array.
>>>>>> Listing-2 shows that if I don’t call flatten function it shows all
>>> records.
>>>>>> Listing-3 shows that if I call flatten function, row6 (r6cl1…) is
>>> ignored. I am using that in my projections not in predicate, but it looks
>>> like treated as predicate.
>>>>>>
>>>>>> Please correct me if I am wrong and let me know if there is any work
>>> around.
>>>>>>
>>>>>> Listing:1
>>>>>>
>>>>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, t.c.z from
>>> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>>>>>> +------------+------------+------------+------------+------------+
>>>>>> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
>>>>>> +------------+------------+------------+------------+------------+
>>>>>> | r1cl1 | r1c2 | 1 | a string | ["1"] |
>>>>>> | r2cl1 | r2c2 | 2 | a string | ["1","2"] |
>>>>>> | r3cl1 | r3c2 | 3 | a string | ["1","2","3"] |
>>>>>> | r4cl1 | r4c2 | 4 | a string |
>>> ["1","2","3","4"] |
>>>>>> | r5cl1 | r5c2 | 5 | a string |
>>> ["1","2","3","4","5"] |
>>>>>> | r6cl1 | r6c2 | 6 | a string | [] |
>>>>>> +------------+------------+------------+------------+------------+
>>>>>> 6 rows selected (0.125 seconds)
>>>>>> 0: jdbc:drill:>
>>>>>>
>>>>>> Listing:2
>>>>>>
>>>>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y from
>>> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>>>>>> +------------+------------+------------+------------+
>>>>>> | a | b | EXPR$2 | EXPR$3 |
>>>>>> +------------+------------+------------+------------+
>>>>>> | r1cl1 | r1c2 | 1 | a string |
>>>>>> | r2cl1 | r2c2 | 2 | a string |
>>>>>> | r3cl1 | r3c2 | 3 | a string |
>>>>>> | r4cl1 | r4c2 | 4 | a string |
>>>>>> | r5cl1 | r5c2 | 5 | a string |
>>>>>> | r6cl1 | r6c2 | 6 | a string |
>>>>>> +------------+------------+------------+------------+
>>>>>> 6 rows selected (0.163 seconds)
>>>>>>
>>>>>> Listing:3
>>>>>>
>>>>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, flatten(t.c.z) from
>>> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>>>>>> +------------+------------+------------+------------+------------+
>>>>>> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
>>>>>> +------------+------------+------------+------------+------------+
>>>>>> | r1cl1 | r1c2 | 1 | a string | 1 |
>>>>>> | r2cl1 | r2c2 | 2 | a string | 1 |
>>>>>> | r2cl1 | r2c2 | 2 | a string | 2 |
>>>>>> | r3cl1 | r3c2 | 3 | a string | 1 |
>>>>>> | r3cl1 | r3c2 | 3 | a string | 2 |
>>>>>> | r3cl1 | r3c2 | 3 | a string | 3 |
>>>>>> | r4cl1 | r4c2 | 4 | a string | 1 |
>>>>>> | r4cl1 | r4c2 | 4 | a string | 2 |
>>>>>> | r4cl1 | r4c2 | 4 | a string | 3 |
>>>>>> | r4cl1 | r4c2 | 4 | a string | 4 |
>>>>>> | r5cl1 | r5c2 | 5 | a string | 1 |
>>>>>> | r5cl1 | r5c2 | 5 | a string | 2 |
>>>>>> | r5cl1 | r5c2 | 5 | a string | 3 |
>>>>>> | r5cl1 | r5c2 | 5 | a string | 4 |
>>>>>> | r5cl1 | r5c2 | 5 | a string | 5 |
>>>>>> +------------+------------+------------+------------+------------+
>>>>>> 15 rows selected (0.171 seconds)
>>>>>>
>>>>>> Thanks
>>>>>> Sudhakar Thota
>>>>
>>>
>>
>>
>>
>> --
>> Steven Phillips
>> Software Engineer
>>
>> mapr.com
>>
Re: Drill - Flatten function - help please
Posted by Jason Altekruse <al...@gmail.com>.
Sudhakar opened an issue for this, so I responded there. Steven is right,
this is the current expected functionality, but I discuss there the reasons
for it and opened the discussion for use cases that need this functionality.
On Tue, Feb 3, 2015 at 2:28 PM, Steven Phillips <sp...@maprtech.com>
wrote:
> I think flatten is functioning as designed, but perhaps we need to treat
> this case differently. It might make sense to return a single row with a
> null for the flattened column in this case. The only model for "flatten"
> that I know of is google Big Query. We should see how they handle this
> case.
>
> On Tue, Feb 3, 2015 at 12:27 PM, MapR <st...@maprtech.com> wrote:
>
> > In a table, column with null values we
> > present as nulls or empty values with other columns. I never had an
> > experience with c1, c2, c3 (columns ) not presented when c3 has null
> values
> > even though c1, c2 have notnull values.
> >
> > Sudhakar Thota
> > Sent from my iPhone
> >
> > > On Feb 3, 2015, at 11:57 AM, Andries Engelbrecht <
> > aengelbrecht@maprtech.com> wrote:
> > >
> > > In listing 3 it looks like flatten is doing the correct thing by
> > representing each element in the array with a new row and the element in
> > the column output.
> > > And also for arrays that are empty there is no output row.
> > >
> > > —Andries
> > >
> > >> On Feb 3, 2015, at 11:54 AM, Sudhakar Thota <st...@maprtech.com>
> > wrote:
> > >>
> > >> It will be more appropriate if I add my file to have data to
> facilitate
> > quick test.
> > >>
> > >> [root@maprdemo clicks]# cat sthota_test_1.json
> > >> {"a":"r1cl1", "b":"r1c2","c":{ "x" : 1, "y" : "a string", "z" : [ 1]
> }}
> > >> {"a":"r2cl1", "b":"r2c2","c":{ "x" : 2, "y" : "a string", "z" : [ 1,2]
> > }}
> > >> {"a":"r3cl1", "b":"r3c2","c":{ "x" : 3, "y" : "a string", "z" : [
> > 1,2,3] }}
> > >> {"a":"r4cl1", "b":"r4c2","c":{ "x" : 4, "y" : "a string", "z" : [
> > 1,2,3,4] }}
> > >> {"a":"r5cl1", "b":"r5c2","c":{ "x" : 5, "y" : "a string", "z" : [
> > 1,2,3,4,5] }}
> > >> {"a":"r6cl1", "b":"r6c2","c":{ "x" : 6, "y" : "a string" }}
> > >>
> > >>
> > >> Begin forwarded message:
> > >>
> > >>> From: Sudhakar Thota <st...@maprtech.com>
> > >>> Subject: Drill - Flatten function - help please
> > >>> Date: February 3, 2015 at 11:52:39 AM PST
> > >>> To: user@drill.apache.org
> > >>>
> > >>> Hi Drillers,
> > >>>
> > >>> I have a request here about flatten function. I want to know if this
> > is an expected behavior of drill flatten function with json documents.
> > >>>
> > >>> Listing-1 is contents of my json collection where I have
> intentionally
> > included a empty array.
> > >>> Listing-2 shows that if I don’t call flatten function it shows all
> > records.
> > >>> Listing-3 shows that if I call flatten function, row6 (r6cl1…) is
> > ignored. I am using that in my projections not in predicate, but it looks
> > like treated as predicate.
> > >>>
> > >>> Please correct me if I am wrong and let me know if there is any work
> > around.
> > >>>
> > >>> Listing:1
> > >>>
> > >>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, t.c.z from
> > dfs.`/data/nested/clicks/sthota_test_1.json` as t;
> > >>> +------------+------------+------------+------------+------------+
> > >>> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
> > >>> +------------+------------+------------+------------+------------+
> > >>> | r1cl1 | r1c2 | 1 | a string | ["1"] |
> > >>> | r2cl1 | r2c2 | 2 | a string | ["1","2"] |
> > >>> | r3cl1 | r3c2 | 3 | a string | ["1","2","3"] |
> > >>> | r4cl1 | r4c2 | 4 | a string |
> > ["1","2","3","4"] |
> > >>> | r5cl1 | r5c2 | 5 | a string |
> > ["1","2","3","4","5"] |
> > >>> | r6cl1 | r6c2 | 6 | a string | [] |
> > >>> +------------+------------+------------+------------+------------+
> > >>> 6 rows selected (0.125 seconds)
> > >>> 0: jdbc:drill:>
> > >>>
> > >>> Listing:2
> > >>>
> > >>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y from
> > dfs.`/data/nested/clicks/sthota_test_1.json` as t;
> > >>> +------------+------------+------------+------------+
> > >>> | a | b | EXPR$2 | EXPR$3 |
> > >>> +------------+------------+------------+------------+
> > >>> | r1cl1 | r1c2 | 1 | a string |
> > >>> | r2cl1 | r2c2 | 2 | a string |
> > >>> | r3cl1 | r3c2 | 3 | a string |
> > >>> | r4cl1 | r4c2 | 4 | a string |
> > >>> | r5cl1 | r5c2 | 5 | a string |
> > >>> | r6cl1 | r6c2 | 6 | a string |
> > >>> +------------+------------+------------+------------+
> > >>> 6 rows selected (0.163 seconds)
> > >>>
> > >>> Listing:3
> > >>>
> > >>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, flatten(t.c.z) from
> > dfs.`/data/nested/clicks/sthota_test_1.json` as t;
> > >>> +------------+------------+------------+------------+------------+
> > >>> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
> > >>> +------------+------------+------------+------------+------------+
> > >>> | r1cl1 | r1c2 | 1 | a string | 1 |
> > >>> | r2cl1 | r2c2 | 2 | a string | 1 |
> > >>> | r2cl1 | r2c2 | 2 | a string | 2 |
> > >>> | r3cl1 | r3c2 | 3 | a string | 1 |
> > >>> | r3cl1 | r3c2 | 3 | a string | 2 |
> > >>> | r3cl1 | r3c2 | 3 | a string | 3 |
> > >>> | r4cl1 | r4c2 | 4 | a string | 1 |
> > >>> | r4cl1 | r4c2 | 4 | a string | 2 |
> > >>> | r4cl1 | r4c2 | 4 | a string | 3 |
> > >>> | r4cl1 | r4c2 | 4 | a string | 4 |
> > >>> | r5cl1 | r5c2 | 5 | a string | 1 |
> > >>> | r5cl1 | r5c2 | 5 | a string | 2 |
> > >>> | r5cl1 | r5c2 | 5 | a string | 3 |
> > >>> | r5cl1 | r5c2 | 5 | a string | 4 |
> > >>> | r5cl1 | r5c2 | 5 | a string | 5 |
> > >>> +------------+------------+------------+------------+------------+
> > >>> 15 rows selected (0.171 seconds)
> > >>>
> > >>> Thanks
> > >>> Sudhakar Thota
> > >
> >
>
>
>
> --
> Steven Phillips
> Software Engineer
>
> mapr.com
>
Re: Drill - Flatten function - help please
Posted by Steven Phillips <sp...@maprtech.com>.
I think flatten is functioning as designed, but perhaps we need to treat
this case differently. It might make sense to return a single row with a
null for the flattened column in this case. The only model for "flatten"
that I know of is google Big Query. We should see how they handle this case.
On Tue, Feb 3, 2015 at 12:27 PM, MapR <st...@maprtech.com> wrote:
> In a table, column with null values we
> present as nulls or empty values with other columns. I never had an
> experience with c1, c2, c3 (columns ) not presented when c3 has null values
> even though c1, c2 have notnull values.
>
> Sudhakar Thota
> Sent from my iPhone
>
> > On Feb 3, 2015, at 11:57 AM, Andries Engelbrecht <
> aengelbrecht@maprtech.com> wrote:
> >
> > In listing 3 it looks like flatten is doing the correct thing by
> representing each element in the array with a new row and the element in
> the column output.
> > And also for arrays that are empty there is no output row.
> >
> > —Andries
> >
> >> On Feb 3, 2015, at 11:54 AM, Sudhakar Thota <st...@maprtech.com>
> wrote:
> >>
> >> It will be more appropriate if I add my file to have data to facilitate
> quick test.
> >>
> >> [root@maprdemo clicks]# cat sthota_test_1.json
> >> {"a":"r1cl1", "b":"r1c2","c":{ "x" : 1, "y" : "a string", "z" : [ 1] }}
> >> {"a":"r2cl1", "b":"r2c2","c":{ "x" : 2, "y" : "a string", "z" : [ 1,2]
> }}
> >> {"a":"r3cl1", "b":"r3c2","c":{ "x" : 3, "y" : "a string", "z" : [
> 1,2,3] }}
> >> {"a":"r4cl1", "b":"r4c2","c":{ "x" : 4, "y" : "a string", "z" : [
> 1,2,3,4] }}
> >> {"a":"r5cl1", "b":"r5c2","c":{ "x" : 5, "y" : "a string", "z" : [
> 1,2,3,4,5] }}
> >> {"a":"r6cl1", "b":"r6c2","c":{ "x" : 6, "y" : "a string" }}
> >>
> >>
> >> Begin forwarded message:
> >>
> >>> From: Sudhakar Thota <st...@maprtech.com>
> >>> Subject: Drill - Flatten function - help please
> >>> Date: February 3, 2015 at 11:52:39 AM PST
> >>> To: user@drill.apache.org
> >>>
> >>> Hi Drillers,
> >>>
> >>> I have a request here about flatten function. I want to know if this
> is an expected behavior of drill flatten function with json documents.
> >>>
> >>> Listing-1 is contents of my json collection where I have intentionally
> included a empty array.
> >>> Listing-2 shows that if I don’t call flatten function it shows all
> records.
> >>> Listing-3 shows that if I call flatten function, row6 (r6cl1…) is
> ignored. I am using that in my projections not in predicate, but it looks
> like treated as predicate.
> >>>
> >>> Please correct me if I am wrong and let me know if there is any work
> around.
> >>>
> >>> Listing:1
> >>>
> >>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, t.c.z from
> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
> >>> +------------+------------+------------+------------+------------+
> >>> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
> >>> +------------+------------+------------+------------+------------+
> >>> | r1cl1 | r1c2 | 1 | a string | ["1"] |
> >>> | r2cl1 | r2c2 | 2 | a string | ["1","2"] |
> >>> | r3cl1 | r3c2 | 3 | a string | ["1","2","3"] |
> >>> | r4cl1 | r4c2 | 4 | a string |
> ["1","2","3","4"] |
> >>> | r5cl1 | r5c2 | 5 | a string |
> ["1","2","3","4","5"] |
> >>> | r6cl1 | r6c2 | 6 | a string | [] |
> >>> +------------+------------+------------+------------+------------+
> >>> 6 rows selected (0.125 seconds)
> >>> 0: jdbc:drill:>
> >>>
> >>> Listing:2
> >>>
> >>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y from
> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
> >>> +------------+------------+------------+------------+
> >>> | a | b | EXPR$2 | EXPR$3 |
> >>> +------------+------------+------------+------------+
> >>> | r1cl1 | r1c2 | 1 | a string |
> >>> | r2cl1 | r2c2 | 2 | a string |
> >>> | r3cl1 | r3c2 | 3 | a string |
> >>> | r4cl1 | r4c2 | 4 | a string |
> >>> | r5cl1 | r5c2 | 5 | a string |
> >>> | r6cl1 | r6c2 | 6 | a string |
> >>> +------------+------------+------------+------------+
> >>> 6 rows selected (0.163 seconds)
> >>>
> >>> Listing:3
> >>>
> >>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, flatten(t.c.z) from
> dfs.`/data/nested/clicks/sthota_test_1.json` as t;
> >>> +------------+------------+------------+------------+------------+
> >>> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
> >>> +------------+------------+------------+------------+------------+
> >>> | r1cl1 | r1c2 | 1 | a string | 1 |
> >>> | r2cl1 | r2c2 | 2 | a string | 1 |
> >>> | r2cl1 | r2c2 | 2 | a string | 2 |
> >>> | r3cl1 | r3c2 | 3 | a string | 1 |
> >>> | r3cl1 | r3c2 | 3 | a string | 2 |
> >>> | r3cl1 | r3c2 | 3 | a string | 3 |
> >>> | r4cl1 | r4c2 | 4 | a string | 1 |
> >>> | r4cl1 | r4c2 | 4 | a string | 2 |
> >>> | r4cl1 | r4c2 | 4 | a string | 3 |
> >>> | r4cl1 | r4c2 | 4 | a string | 4 |
> >>> | r5cl1 | r5c2 | 5 | a string | 1 |
> >>> | r5cl1 | r5c2 | 5 | a string | 2 |
> >>> | r5cl1 | r5c2 | 5 | a string | 3 |
> >>> | r5cl1 | r5c2 | 5 | a string | 4 |
> >>> | r5cl1 | r5c2 | 5 | a string | 5 |
> >>> +------------+------------+------------+------------+------------+
> >>> 15 rows selected (0.171 seconds)
> >>>
> >>> Thanks
> >>> Sudhakar Thota
> >
>
--
Steven Phillips
Software Engineer
mapr.com
Re: Drill - Flatten function - help please
Posted by MapR <st...@maprtech.com>.
In a table, column with null values we
present as nulls or empty values with other columns. I never had an experience with c1, c2, c3 (columns ) not presented when c3 has null values even though c1, c2 have notnull values.
Sudhakar Thota
Sent from my iPhone
> On Feb 3, 2015, at 11:57 AM, Andries Engelbrecht <ae...@maprtech.com> wrote:
>
> In listing 3 it looks like flatten is doing the correct thing by representing each element in the array with a new row and the element in the column output.
> And also for arrays that are empty there is no output row.
>
> —Andries
>
>> On Feb 3, 2015, at 11:54 AM, Sudhakar Thota <st...@maprtech.com> wrote:
>>
>> It will be more appropriate if I add my file to have data to facilitate quick test.
>>
>> [root@maprdemo clicks]# cat sthota_test_1.json
>> {"a":"r1cl1", "b":"r1c2","c":{ "x" : 1, "y" : "a string", "z" : [ 1] }}
>> {"a":"r2cl1", "b":"r2c2","c":{ "x" : 2, "y" : "a string", "z" : [ 1,2] }}
>> {"a":"r3cl1", "b":"r3c2","c":{ "x" : 3, "y" : "a string", "z" : [ 1,2,3] }}
>> {"a":"r4cl1", "b":"r4c2","c":{ "x" : 4, "y" : "a string", "z" : [ 1,2,3,4] }}
>> {"a":"r5cl1", "b":"r5c2","c":{ "x" : 5, "y" : "a string", "z" : [ 1,2,3,4,5] }}
>> {"a":"r6cl1", "b":"r6c2","c":{ "x" : 6, "y" : "a string" }}
>>
>>
>> Begin forwarded message:
>>
>>> From: Sudhakar Thota <st...@maprtech.com>
>>> Subject: Drill - Flatten function - help please
>>> Date: February 3, 2015 at 11:52:39 AM PST
>>> To: user@drill.apache.org
>>>
>>> Hi Drillers,
>>>
>>> I have a request here about flatten function. I want to know if this is an expected behavior of drill flatten function with json documents.
>>>
>>> Listing-1 is contents of my json collection where I have intentionally included a empty array.
>>> Listing-2 shows that if I don’t call flatten function it shows all records.
>>> Listing-3 shows that if I call flatten function, row6 (r6cl1…) is ignored. I am using that in my projections not in predicate, but it looks like treated as predicate.
>>>
>>> Please correct me if I am wrong and let me know if there is any work around.
>>>
>>> Listing:1
>>>
>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, t.c.z from dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>>> +------------+------------+------------+------------+------------+
>>> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
>>> +------------+------------+------------+------------+------------+
>>> | r1cl1 | r1c2 | 1 | a string | ["1"] |
>>> | r2cl1 | r2c2 | 2 | a string | ["1","2"] |
>>> | r3cl1 | r3c2 | 3 | a string | ["1","2","3"] |
>>> | r4cl1 | r4c2 | 4 | a string | ["1","2","3","4"] |
>>> | r5cl1 | r5c2 | 5 | a string | ["1","2","3","4","5"] |
>>> | r6cl1 | r6c2 | 6 | a string | [] |
>>> +------------+------------+------------+------------+------------+
>>> 6 rows selected (0.125 seconds)
>>> 0: jdbc:drill:>
>>>
>>> Listing:2
>>>
>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y from dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>>> +------------+------------+------------+------------+
>>> | a | b | EXPR$2 | EXPR$3 |
>>> +------------+------------+------------+------------+
>>> | r1cl1 | r1c2 | 1 | a string |
>>> | r2cl1 | r2c2 | 2 | a string |
>>> | r3cl1 | r3c2 | 3 | a string |
>>> | r4cl1 | r4c2 | 4 | a string |
>>> | r5cl1 | r5c2 | 5 | a string |
>>> | r6cl1 | r6c2 | 6 | a string |
>>> +------------+------------+------------+------------+
>>> 6 rows selected (0.163 seconds)
>>>
>>> Listing:3
>>>
>>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, flatten(t.c.z) from dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>>> +------------+------------+------------+------------+------------+
>>> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
>>> +------------+------------+------------+------------+------------+
>>> | r1cl1 | r1c2 | 1 | a string | 1 |
>>> | r2cl1 | r2c2 | 2 | a string | 1 |
>>> | r2cl1 | r2c2 | 2 | a string | 2 |
>>> | r3cl1 | r3c2 | 3 | a string | 1 |
>>> | r3cl1 | r3c2 | 3 | a string | 2 |
>>> | r3cl1 | r3c2 | 3 | a string | 3 |
>>> | r4cl1 | r4c2 | 4 | a string | 1 |
>>> | r4cl1 | r4c2 | 4 | a string | 2 |
>>> | r4cl1 | r4c2 | 4 | a string | 3 |
>>> | r4cl1 | r4c2 | 4 | a string | 4 |
>>> | r5cl1 | r5c2 | 5 | a string | 1 |
>>> | r5cl1 | r5c2 | 5 | a string | 2 |
>>> | r5cl1 | r5c2 | 5 | a string | 3 |
>>> | r5cl1 | r5c2 | 5 | a string | 4 |
>>> | r5cl1 | r5c2 | 5 | a string | 5 |
>>> +------------+------------+------------+------------+------------+
>>> 15 rows selected (0.171 seconds)
>>>
>>> Thanks
>>> Sudhakar Thota
>
Re: Drill - Flatten function - help please
Posted by Andries Engelbrecht <ae...@maprtech.com>.
In listing 3 it looks like flatten is doing the correct thing by representing each element in the array with a new row and the element in the column output.
And also for arrays that are empty there is no output row.
—Andries
On Feb 3, 2015, at 11:54 AM, Sudhakar Thota <st...@maprtech.com> wrote:
> It will be more appropriate if I add my file to have data to facilitate quick test.
>
> [root@maprdemo clicks]# cat sthota_test_1.json
> {"a":"r1cl1", "b":"r1c2","c":{ "x" : 1, "y" : "a string", "z" : [ 1] }}
> {"a":"r2cl1", "b":"r2c2","c":{ "x" : 2, "y" : "a string", "z" : [ 1,2] }}
> {"a":"r3cl1", "b":"r3c2","c":{ "x" : 3, "y" : "a string", "z" : [ 1,2,3] }}
> {"a":"r4cl1", "b":"r4c2","c":{ "x" : 4, "y" : "a string", "z" : [ 1,2,3,4] }}
> {"a":"r5cl1", "b":"r5c2","c":{ "x" : 5, "y" : "a string", "z" : [ 1,2,3,4,5] }}
> {"a":"r6cl1", "b":"r6c2","c":{ "x" : 6, "y" : "a string" }}
>
>
> Begin forwarded message:
>
>> From: Sudhakar Thota <st...@maprtech.com>
>> Subject: Drill - Flatten function - help please
>> Date: February 3, 2015 at 11:52:39 AM PST
>> To: user@drill.apache.org
>>
>> Hi Drillers,
>>
>> I have a request here about flatten function. I want to know if this is an expected behavior of drill flatten function with json documents.
>>
>> Listing-1 is contents of my json collection where I have intentionally included a empty array.
>> Listing-2 shows that if I don’t call flatten function it shows all records.
>> Listing-3 shows that if I call flatten function, row6 (r6cl1…) is ignored. I am using that in my projections not in predicate, but it looks like treated as predicate.
>>
>> Please correct me if I am wrong and let me know if there is any work around.
>>
>> Listing:1
>>
>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, t.c.z from dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>> +------------+------------+------------+------------+------------+
>> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
>> +------------+------------+------------+------------+------------+
>> | r1cl1 | r1c2 | 1 | a string | ["1"] |
>> | r2cl1 | r2c2 | 2 | a string | ["1","2"] |
>> | r3cl1 | r3c2 | 3 | a string | ["1","2","3"] |
>> | r4cl1 | r4c2 | 4 | a string | ["1","2","3","4"] |
>> | r5cl1 | r5c2 | 5 | a string | ["1","2","3","4","5"] |
>> | r6cl1 | r6c2 | 6 | a string | [] |
>> +------------+------------+------------+------------+------------+
>> 6 rows selected (0.125 seconds)
>> 0: jdbc:drill:>
>>
>> Listing:2
>>
>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y from dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>> +------------+------------+------------+------------+
>> | a | b | EXPR$2 | EXPR$3 |
>> +------------+------------+------------+------------+
>> | r1cl1 | r1c2 | 1 | a string |
>> | r2cl1 | r2c2 | 2 | a string |
>> | r3cl1 | r3c2 | 3 | a string |
>> | r4cl1 | r4c2 | 4 | a string |
>> | r5cl1 | r5c2 | 5 | a string |
>> | r6cl1 | r6c2 | 6 | a string |
>> +------------+------------+------------+------------+
>> 6 rows selected (0.163 seconds)
>>
>> Listing:3
>>
>> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, flatten(t.c.z) from dfs.`/data/nested/clicks/sthota_test_1.json` as t;
>> +------------+------------+------------+------------+------------+
>> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
>> +------------+------------+------------+------------+------------+
>> | r1cl1 | r1c2 | 1 | a string | 1 |
>> | r2cl1 | r2c2 | 2 | a string | 1 |
>> | r2cl1 | r2c2 | 2 | a string | 2 |
>> | r3cl1 | r3c2 | 3 | a string | 1 |
>> | r3cl1 | r3c2 | 3 | a string | 2 |
>> | r3cl1 | r3c2 | 3 | a string | 3 |
>> | r4cl1 | r4c2 | 4 | a string | 1 |
>> | r4cl1 | r4c2 | 4 | a string | 2 |
>> | r4cl1 | r4c2 | 4 | a string | 3 |
>> | r4cl1 | r4c2 | 4 | a string | 4 |
>> | r5cl1 | r5c2 | 5 | a string | 1 |
>> | r5cl1 | r5c2 | 5 | a string | 2 |
>> | r5cl1 | r5c2 | 5 | a string | 3 |
>> | r5cl1 | r5c2 | 5 | a string | 4 |
>> | r5cl1 | r5c2 | 5 | a string | 5 |
>> +------------+------------+------------+------------+------------+
>> 15 rows selected (0.171 seconds)
>>
>> Thanks
>> Sudhakar Thota
>>
>
Fwd: Drill - Flatten function - help please
Posted by Sudhakar Thota <st...@maprtech.com>.
It will be more appropriate if I add my file to have data to facilitate quick test.
[root@maprdemo clicks]# cat sthota_test_1.json
{"a":"r1cl1", "b":"r1c2","c":{ "x" : 1, "y" : "a string", "z" : [ 1] }}
{"a":"r2cl1", "b":"r2c2","c":{ "x" : 2, "y" : "a string", "z" : [ 1,2] }}
{"a":"r3cl1", "b":"r3c2","c":{ "x" : 3, "y" : "a string", "z" : [ 1,2,3] }}
{"a":"r4cl1", "b":"r4c2","c":{ "x" : 4, "y" : "a string", "z" : [ 1,2,3,4] }}
{"a":"r5cl1", "b":"r5c2","c":{ "x" : 5, "y" : "a string", "z" : [ 1,2,3,4,5] }}
{"a":"r6cl1", "b":"r6c2","c":{ "x" : 6, "y" : "a string" }}
Begin forwarded message:
> From: Sudhakar Thota <st...@maprtech.com>
> Subject: Drill - Flatten function - help please
> Date: February 3, 2015 at 11:52:39 AM PST
> To: user@drill.apache.org
>
> Hi Drillers,
>
> I have a request here about flatten function. I want to know if this is an expected behavior of drill flatten function with json documents.
>
> Listing-1 is contents of my json collection where I have intentionally included a empty array.
> Listing-2 shows that if I don’t call flatten function it shows all records.
> Listing-3 shows that if I call flatten function, row6 (r6cl1…) is ignored. I am using that in my projections not in predicate, but it looks like treated as predicate.
>
> Please correct me if I am wrong and let me know if there is any work around.
>
> Listing:1
>
> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, t.c.z from dfs.`/data/nested/clicks/sthota_test_1.json` as t;
> +------------+------------+------------+------------+------------+
> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
> +------------+------------+------------+------------+------------+
> | r1cl1 | r1c2 | 1 | a string | ["1"] |
> | r2cl1 | r2c2 | 2 | a string | ["1","2"] |
> | r3cl1 | r3c2 | 3 | a string | ["1","2","3"] |
> | r4cl1 | r4c2 | 4 | a string | ["1","2","3","4"] |
> | r5cl1 | r5c2 | 5 | a string | ["1","2","3","4","5"] |
> | r6cl1 | r6c2 | 6 | a string | [] |
> +------------+------------+------------+------------+------------+
> 6 rows selected (0.125 seconds)
> 0: jdbc:drill:>
>
> Listing:2
>
> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y from dfs.`/data/nested/clicks/sthota_test_1.json` as t;
> +------------+------------+------------+------------+
> | a | b | EXPR$2 | EXPR$3 |
> +------------+------------+------------+------------+
> | r1cl1 | r1c2 | 1 | a string |
> | r2cl1 | r2c2 | 2 | a string |
> | r3cl1 | r3c2 | 3 | a string |
> | r4cl1 | r4c2 | 4 | a string |
> | r5cl1 | r5c2 | 5 | a string |
> | r6cl1 | r6c2 | 6 | a string |
> +------------+------------+------------+------------+
> 6 rows selected (0.163 seconds)
>
> Listing:3
>
> 0: jdbc:drill:> SELECT t.a,t.b,t.c.x, t.c.y, flatten(t.c.z) from dfs.`/data/nested/clicks/sthota_test_1.json` as t;
> +------------+------------+------------+------------+------------+
> | a | b | EXPR$2 | EXPR$3 | EXPR$4 |
> +------------+------------+------------+------------+------------+
> | r1cl1 | r1c2 | 1 | a string | 1 |
> | r2cl1 | r2c2 | 2 | a string | 1 |
> | r2cl1 | r2c2 | 2 | a string | 2 |
> | r3cl1 | r3c2 | 3 | a string | 1 |
> | r3cl1 | r3c2 | 3 | a string | 2 |
> | r3cl1 | r3c2 | 3 | a string | 3 |
> | r4cl1 | r4c2 | 4 | a string | 1 |
> | r4cl1 | r4c2 | 4 | a string | 2 |
> | r4cl1 | r4c2 | 4 | a string | 3 |
> | r4cl1 | r4c2 | 4 | a string | 4 |
> | r5cl1 | r5c2 | 5 | a string | 1 |
> | r5cl1 | r5c2 | 5 | a string | 2 |
> | r5cl1 | r5c2 | 5 | a string | 3 |
> | r5cl1 | r5c2 | 5 | a string | 4 |
> | r5cl1 | r5c2 | 5 | a string | 5 |
> +------------+------------+------------+------------+------------+
> 15 rows selected (0.171 seconds)
>
> Thanks
> Sudhakar Thota
>