You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by MohammadReza Mofateh <mo...@gmail.com> on 2014/10/30 09:02:24 UTC
Query on Array
Hi,
I can't find a tutorial explaining how to get started with Drill (besides
querying the sample data that comes with Drill like Arrays or JSON).
I have some Array data on HBASE that I would like to query (Join row_keys
of table A with Arrays in table B)
, how do I query?
I asked a way for JSON but no body didn't answer me.But I found the way.
Best
Re: Query on Array
Posted by Ted Dunning <te...@gmail.com>.
If you mean to have an array of keys that should be looked up in HBase one
by one, that will be a bit hard to support right now in Drill.
The problem is that there is no way to flatten an array. That also means
that you can't yet write a user defined table generating function (UDTF)
that would do the lookups for you.
On Fri, Oct 31, 2014 at 11:37 AM, MohammadReza Mofateh <mo...@gmail.com>
wrote:
> Hi
> Thank you a lot.
> Are you mean drill doesn't support this?
> By your advises How do I put arrays?
> How do I query (join) on them If it's possible?
> Could you guide me about this?
>
> Best
>
> On Fri, Oct 31, 2014 at 1:06 AM, Jinfeng Ni <ji...@gmail.com> wrote:
>
> > I think Drill currently does not allow to put a array in the = predicate.
> >
> > With the complex type such array/map, the semantics of = operator is not
> > clearly defined. In other words, we need decide whether "=" means
> > existence of a pair of values from the two lists satisfying the
> comparison,
> > or the two lists are exactly same. (btw: In XQuery, "=" uses the
> > semantics of existence, while the traditional = semantics is represented
> by
> > value comparison operator "eq"). We probably first need define the
> > semantics of comparison operator for complex type, then add the support
> to
> > put such complex data operators into join predicate .
> >
> >
> >
> >
> >
> >
> > On Thu, Oct 30, 2014 at 11:57 AM, MohammadReza Mofateh <
> > mofateh72@gmail.com>
> > wrote:
> >
> > > Hi
> > > Thank you.
> > > I do successfully query on JSON ,But problems on *Arrays*.
> > > Table A:
> > > row_key,i:year (value is a year, for e.g. : 1993)
> > > Table B:
> > > row_key,i:yearsLived (value is an array,for e.g. : "yearsLived":
> > > ["1990", "1993", "1998", "2008"])
> > > I put exactly : "yearsLived": ["1990", "1993", "1998", "2008"] into
> > > B.i.yearsLived
> > > And I want to Join on them like:
> > > Select * from A join B on hbase.A.i.year=hbase.B.i.yearsLived
> > >
> > > How do I implement this query?
> > > Is it possible to implementing in Apache drill?Do you have any idea?
> > >
> > > I'm using version 0.5.0.
> > >
> > > Best
> > >
> > > On Thu, Oct 30, 2014 at 7:50 PM, Jacques Nadeau <ja...@apache.org>
> > > wrote:
> > > >
> > > > Can you go into more detail about the data is structured in HBase?
> > > > Especially in table B. HBase doesn't have built in data types so
> > people
> > > > typically need to CONVERT_FROM a format (including JSON). The docs
> are
> > > > here:
> > > >
> > >
> > >
> >
> https://cwiki.apache.org/confluence/display/DRILL/SQL+Functions#SQLFunctions-ConvertFunctions
> > > >
> > > > Note that for some reason, JSON isn't listed in that list. It is
> > > available
> > > > for conversion as well.
> > > >
> > > > On Thu, Oct 30, 2014 at 1:02 AM, MohammadReza Mofateh <
> > > mofateh72@gmail.com
> > > >
> > > > wrote:
> > > >
> > > > > Hi,
> > > > > I can't find a tutorial explaining how to get started with Drill
> > > (besides
> > > > > querying the sample data that comes with Drill like Arrays or
> JSON).
> > > > >
> > > > > I have some Array data on HBASE that I would like to query (Join
> > > row_keys
> > > > > of table A with Arrays in table B)
> > > > > , how do I query?
> > > > >
> > > > > I asked a way for JSON but no body didn't answer me.But I found the
> > > way.
> > > > >
> > > > > Best
> > > > >
> > >
> >
>
Re: Query on Array
Posted by MohammadReza Mofateh <mo...@gmail.com>.
Hi
Thank you a lot.
Are you mean drill doesn't support this?
By your advises How do I put arrays?
How do I query (join) on them If it's possible?
Could you guide me about this?
Best
On Fri, Oct 31, 2014 at 1:06 AM, Jinfeng Ni <ji...@gmail.com> wrote:
> I think Drill currently does not allow to put a array in the = predicate.
>
> With the complex type such array/map, the semantics of = operator is not
> clearly defined. In other words, we need decide whether "=" means
> existence of a pair of values from the two lists satisfying the comparison,
> or the two lists are exactly same. (btw: In XQuery, "=" uses the
> semantics of existence, while the traditional = semantics is represented by
> value comparison operator "eq"). We probably first need define the
> semantics of comparison operator for complex type, then add the support to
> put such complex data operators into join predicate .
>
>
>
>
>
>
> On Thu, Oct 30, 2014 at 11:57 AM, MohammadReza Mofateh <
> mofateh72@gmail.com>
> wrote:
>
> > Hi
> > Thank you.
> > I do successfully query on JSON ,But problems on *Arrays*.
> > Table A:
> > row_key,i:year (value is a year, for e.g. : 1993)
> > Table B:
> > row_key,i:yearsLived (value is an array,for e.g. : "yearsLived":
> > ["1990", "1993", "1998", "2008"])
> > I put exactly : "yearsLived": ["1990", "1993", "1998", "2008"] into
> > B.i.yearsLived
> > And I want to Join on them like:
> > Select * from A join B on hbase.A.i.year=hbase.B.i.yearsLived
> >
> > How do I implement this query?
> > Is it possible to implementing in Apache drill?Do you have any idea?
> >
> > I'm using version 0.5.0.
> >
> > Best
> >
> > On Thu, Oct 30, 2014 at 7:50 PM, Jacques Nadeau <ja...@apache.org>
> > wrote:
> > >
> > > Can you go into more detail about the data is structured in HBase?
> > > Especially in table B. HBase doesn't have built in data types so
> people
> > > typically need to CONVERT_FROM a format (including JSON). The docs are
> > > here:
> > >
> >
> >
> https://cwiki.apache.org/confluence/display/DRILL/SQL+Functions#SQLFunctions-ConvertFunctions
> > >
> > > Note that for some reason, JSON isn't listed in that list. It is
> > available
> > > for conversion as well.
> > >
> > > On Thu, Oct 30, 2014 at 1:02 AM, MohammadReza Mofateh <
> > mofateh72@gmail.com
> > >
> > > wrote:
> > >
> > > > Hi,
> > > > I can't find a tutorial explaining how to get started with Drill
> > (besides
> > > > querying the sample data that comes with Drill like Arrays or JSON).
> > > >
> > > > I have some Array data on HBASE that I would like to query (Join
> > row_keys
> > > > of table A with Arrays in table B)
> > > > , how do I query?
> > > >
> > > > I asked a way for JSON but no body didn't answer me.But I found the
> > way.
> > > >
> > > > Best
> > > >
> >
>
Re: Query on Array
Posted by Jinfeng Ni <ji...@gmail.com>.
I think Drill currently does not allow to put a array in the = predicate.
With the complex type such array/map, the semantics of = operator is not
clearly defined. In other words, we need decide whether "=" means
existence of a pair of values from the two lists satisfying the comparison,
or the two lists are exactly same. (btw: In XQuery, "=" uses the
semantics of existence, while the traditional = semantics is represented by
value comparison operator "eq"). We probably first need define the
semantics of comparison operator for complex type, then add the support to
put such complex data operators into join predicate .
On Thu, Oct 30, 2014 at 11:57 AM, MohammadReza Mofateh <mo...@gmail.com>
wrote:
> Hi
> Thank you.
> I do successfully query on JSON ,But problems on *Arrays*.
> Table A:
> row_key,i:year (value is a year, for e.g. : 1993)
> Table B:
> row_key,i:yearsLived (value is an array,for e.g. : "yearsLived":
> ["1990", "1993", "1998", "2008"])
> I put exactly : "yearsLived": ["1990", "1993", "1998", "2008"] into
> B.i.yearsLived
> And I want to Join on them like:
> Select * from A join B on hbase.A.i.year=hbase.B.i.yearsLived
>
> How do I implement this query?
> Is it possible to implementing in Apache drill?Do you have any idea?
>
> I'm using version 0.5.0.
>
> Best
>
> On Thu, Oct 30, 2014 at 7:50 PM, Jacques Nadeau <ja...@apache.org>
> wrote:
> >
> > Can you go into more detail about the data is structured in HBase?
> > Especially in table B. HBase doesn't have built in data types so people
> > typically need to CONVERT_FROM a format (including JSON). The docs are
> > here:
> >
>
> https://cwiki.apache.org/confluence/display/DRILL/SQL+Functions#SQLFunctions-ConvertFunctions
> >
> > Note that for some reason, JSON isn't listed in that list. It is
> available
> > for conversion as well.
> >
> > On Thu, Oct 30, 2014 at 1:02 AM, MohammadReza Mofateh <
> mofateh72@gmail.com
> >
> > wrote:
> >
> > > Hi,
> > > I can't find a tutorial explaining how to get started with Drill
> (besides
> > > querying the sample data that comes with Drill like Arrays or JSON).
> > >
> > > I have some Array data on HBASE that I would like to query (Join
> row_keys
> > > of table A with Arrays in table B)
> > > , how do I query?
> > >
> > > I asked a way for JSON but no body didn't answer me.But I found the
> way.
> > >
> > > Best
> > >
>
Re: Query on Array
Posted by MohammadReza Mofateh <mo...@gmail.com>.
Hi
Thank you.
I do successfully query on JSON ,But problems on *Arrays*.
Table A:
row_key,i:year (value is a year, for e.g. : 1993)
Table B:
row_key,i:yearsLived (value is an array,for e.g. : "yearsLived":
["1990", "1993", "1998", "2008"])
I put exactly : "yearsLived": ["1990", "1993", "1998", "2008"] into
B.i.yearsLived
And I want to Join on them like:
Select * from A join B on hbase.A.i.year=hbase.B.i.yearsLived
How do I implement this query?
Is it possible to implementing in Apache drill?Do you have any idea?
I'm using version 0.5.0.
Best
On Thu, Oct 30, 2014 at 7:50 PM, Jacques Nadeau <ja...@apache.org> wrote:
>
> Can you go into more detail about the data is structured in HBase?
> Especially in table B. HBase doesn't have built in data types so people
> typically need to CONVERT_FROM a format (including JSON). The docs are
> here:
>
https://cwiki.apache.org/confluence/display/DRILL/SQL+Functions#SQLFunctions-ConvertFunctions
>
> Note that for some reason, JSON isn't listed in that list. It is
available
> for conversion as well.
>
> On Thu, Oct 30, 2014 at 1:02 AM, MohammadReza Mofateh <mofateh72@gmail.com
>
> wrote:
>
> > Hi,
> > I can't find a tutorial explaining how to get started with Drill
(besides
> > querying the sample data that comes with Drill like Arrays or JSON).
> >
> > I have some Array data on HBASE that I would like to query (Join
row_keys
> > of table A with Arrays in table B)
> > , how do I query?
> >
> > I asked a way for JSON but no body didn't answer me.But I found the way.
> >
> > Best
> >
Re: Query on Array
Posted by Jacques Nadeau <ja...@apache.org>.
Can you go into more detail about the data is structured in HBase?
Especially in table B. HBase doesn't have built in data types so people
typically need to CONVERT_FROM a format (including JSON). The docs are
here:
https://cwiki.apache.org/confluence/display/DRILL/SQL+Functions#SQLFunctions-ConvertFunctions
Note that for some reason, JSON isn't listed in that list. It is available
for conversion as well.
On Thu, Oct 30, 2014 at 1:02 AM, MohammadReza Mofateh <mo...@gmail.com>
wrote:
> Hi,
> I can't find a tutorial explaining how to get started with Drill (besides
> querying the sample data that comes with Drill like Arrays or JSON).
>
> I have some Array data on HBASE that I would like to query (Join row_keys
> of table A with Arrays in table B)
> , how do I query?
>
> I asked a way for JSON but no body didn't answer me.But I found the way.
>
> Best
>