You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Kyrill Alyoshin <ky...@gmail.com> on 2014/10/01 00:14:41 UTC
Auto-aliasing of HBase columns in Drill queries
Guys,
We have an HBase system (MapR M7 to be precise) where we store data in
HBase tables under aliased columns. What I mean is that the column keys are
physically named as '1', '2', '3', etc. This is done to save on storage of
data. Typically we use Cascading/Cascalog to query this data. We were able
to add an aliasing layer in our Cascading queries (through Cascading
extension) to allow human readable queries (i.e. using 'firstName' alias
instead of '1').
We'd like to try out Drill for SQL based access. Where should we start
"drilling" in the Drill's code base to develop similar extensions? I am
assuming that Drill currently provides no such functionality out of the
box.
In other words, instead of our HBase query looking like this:
SELECT cast(account[*‘1’*] as varchar(20)) as `Address`
we'd like it to look like this from a user perspective:
SELECT cast(account[*‘address’*] as varchar(20)) as `Address`
Any help in direction is greatly appreciated. We can easily volunteer to
provide such a contribution ourselves, just let us know where to start
"drilling". :-)
Thank you very much!
-Kyrill
Re: Auto-aliasing of HBase columns in Drill queries
Posted by Bob Rumsby <br...@maprtech.com>.
We also need to add some view examples on the main Wiki page (under SQL
Commands).
Bob
On Tue, Sep 30, 2014 at 4:25 PM, Neeraja Rentachintala <
nrentachintala@maprtech.com> wrote:
> Kyrill
> Check out
>
> https://cwiki.apache.org/confluence/display/DRILL/Lesson+2%3A+Run+Queries+with+ANSI+SQL
> for a view example.
>
>
> On Tue, Sep 30, 2014 at 4:20 PM, Kyrill Alyoshin <ky...@gmail.com>
> wrote:
>
> > Tomer,
> >
> > Thanks for the quick reply! I'd been browsing Drill's wiki and docs prior
> > to posting that question and got a sense that "non-materialized" views
> are
> > possible in Drill but literally found zero examples of how to set them
> up.
> > Would you be so kind to provide a pointer on the web? Google queries
> > literally return nothing.
> >
> > Thanks again! ;)
> >
> > -Kyrill
> >
> > On Tue, Sep 30, 2014 at 7:07 PM, Tomer Shiran <ts...@maprtech.com>
> > wrote:
> >
> > > Check out Drill views. You can define a view that exposes the HBase
> table
> > > in whatever way you want, including aliases.
> > >
> > > On Tue, Sep 30, 2014 at 3:14 PM, Kyrill Alyoshin <ky...@gmail.com>
> > > wrote:
> > >
> > > > Guys,
> > > >
> > > > We have an HBase system (MapR M7 to be precise) where we store data
> in
> > > > HBase tables under aliased columns. What I mean is that the column
> keys
> > > are
> > > > physically named as '1', '2', '3', etc. This is done to save on
> storage
> > > of
> > > > data. Typically we use Cascading/Cascalog to query this data. We were
> > > able
> > > > to add an aliasing layer in our Cascading queries (through Cascading
> > > > extension) to allow human readable queries (i.e. using 'firstName'
> > alias
> > > > instead of '1').
> > > >
> > > > We'd like to try out Drill for SQL based access. Where should we
> start
> > > > "drilling" in the Drill's code base to develop similar extensions? I
> am
> > > > assuming that Drill currently provides no such functionality out of
> the
> > > > box.
> > > >
> > > > In other words, instead of our HBase query looking like this:
> > > >
> > > > SELECT cast(account[*‘1’*] as varchar(20)) as `Address`
> > > >
> > > > we'd like it to look like this from a user perspective:
> > > >
> > > > SELECT cast(account[*‘address’*] as varchar(20)) as `Address`
> > > >
> > > > Any help in direction is greatly appreciated. We can easily volunteer
> > to
> > > > provide such a contribution ourselves, just let us know where to
> start
> > > > "drilling". :-)
> > > >
> > > > Thank you very much!
> > > >
> > > > -Kyrill
> > > >
> > >
> > >
> > >
> > > --
> > > Tomer Shiran
> > > VP Product Management | MapR Technologies | 650-804-8657
> > >
> >
>
Re: Auto-aliasing of HBase columns in Drill queries
Posted by Kyrill Alyoshin <ky...@gmail.com>.
Thanks, Neeraja! That works.
On Tue, Sep 30, 2014 at 7:25 PM, Neeraja Rentachintala <
nrentachintala@maprtech.com> wrote:
> Kyrill
> Check out
>
> https://cwiki.apache.org/confluence/display/DRILL/Lesson+2%3A+Run+Queries+with+ANSI+SQL
> for a view example.
>
>
> On Tue, Sep 30, 2014 at 4:20 PM, Kyrill Alyoshin <ky...@gmail.com>
> wrote:
>
> > Tomer,
> >
> > Thanks for the quick reply! I'd been browsing Drill's wiki and docs prior
> > to posting that question and got a sense that "non-materialized" views
> are
> > possible in Drill but literally found zero examples of how to set them
> up.
> > Would you be so kind to provide a pointer on the web? Google queries
> > literally return nothing.
> >
> > Thanks again! ;)
> >
> > -Kyrill
> >
> > On Tue, Sep 30, 2014 at 7:07 PM, Tomer Shiran <ts...@maprtech.com>
> > wrote:
> >
> > > Check out Drill views. You can define a view that exposes the HBase
> table
> > > in whatever way you want, including aliases.
> > >
> > > On Tue, Sep 30, 2014 at 3:14 PM, Kyrill Alyoshin <ky...@gmail.com>
> > > wrote:
> > >
> > > > Guys,
> > > >
> > > > We have an HBase system (MapR M7 to be precise) where we store data
> in
> > > > HBase tables under aliased columns. What I mean is that the column
> keys
> > > are
> > > > physically named as '1', '2', '3', etc. This is done to save on
> storage
> > > of
> > > > data. Typically we use Cascading/Cascalog to query this data. We were
> > > able
> > > > to add an aliasing layer in our Cascading queries (through Cascading
> > > > extension) to allow human readable queries (i.e. using 'firstName'
> > alias
> > > > instead of '1').
> > > >
> > > > We'd like to try out Drill for SQL based access. Where should we
> start
> > > > "drilling" in the Drill's code base to develop similar extensions? I
> am
> > > > assuming that Drill currently provides no such functionality out of
> the
> > > > box.
> > > >
> > > > In other words, instead of our HBase query looking like this:
> > > >
> > > > SELECT cast(account[*‘1’*] as varchar(20)) as `Address`
> > > >
> > > > we'd like it to look like this from a user perspective:
> > > >
> > > > SELECT cast(account[*‘address’*] as varchar(20)) as `Address`
> > > >
> > > > Any help in direction is greatly appreciated. We can easily volunteer
> > to
> > > > provide such a contribution ourselves, just let us know where to
> start
> > > > "drilling". :-)
> > > >
> > > > Thank you very much!
> > > >
> > > > -Kyrill
> > > >
> > >
> > >
> > >
> > > --
> > > Tomer Shiran
> > > VP Product Management | MapR Technologies | 650-804-8657
> > >
> >
>
Re: Auto-aliasing of HBase columns in Drill queries
Posted by Neeraja Rentachintala <nr...@maprtech.com>.
Kyrill
Check out
https://cwiki.apache.org/confluence/display/DRILL/Lesson+2%3A+Run+Queries+with+ANSI+SQL
for a view example.
On Tue, Sep 30, 2014 at 4:20 PM, Kyrill Alyoshin <ky...@gmail.com>
wrote:
> Tomer,
>
> Thanks for the quick reply! I'd been browsing Drill's wiki and docs prior
> to posting that question and got a sense that "non-materialized" views are
> possible in Drill but literally found zero examples of how to set them up.
> Would you be so kind to provide a pointer on the web? Google queries
> literally return nothing.
>
> Thanks again! ;)
>
> -Kyrill
>
> On Tue, Sep 30, 2014 at 7:07 PM, Tomer Shiran <ts...@maprtech.com>
> wrote:
>
> > Check out Drill views. You can define a view that exposes the HBase table
> > in whatever way you want, including aliases.
> >
> > On Tue, Sep 30, 2014 at 3:14 PM, Kyrill Alyoshin <ky...@gmail.com>
> > wrote:
> >
> > > Guys,
> > >
> > > We have an HBase system (MapR M7 to be precise) where we store data in
> > > HBase tables under aliased columns. What I mean is that the column keys
> > are
> > > physically named as '1', '2', '3', etc. This is done to save on storage
> > of
> > > data. Typically we use Cascading/Cascalog to query this data. We were
> > able
> > > to add an aliasing layer in our Cascading queries (through Cascading
> > > extension) to allow human readable queries (i.e. using 'firstName'
> alias
> > > instead of '1').
> > >
> > > We'd like to try out Drill for SQL based access. Where should we start
> > > "drilling" in the Drill's code base to develop similar extensions? I am
> > > assuming that Drill currently provides no such functionality out of the
> > > box.
> > >
> > > In other words, instead of our HBase query looking like this:
> > >
> > > SELECT cast(account[*‘1’*] as varchar(20)) as `Address`
> > >
> > > we'd like it to look like this from a user perspective:
> > >
> > > SELECT cast(account[*‘address’*] as varchar(20)) as `Address`
> > >
> > > Any help in direction is greatly appreciated. We can easily volunteer
> to
> > > provide such a contribution ourselves, just let us know where to start
> > > "drilling". :-)
> > >
> > > Thank you very much!
> > >
> > > -Kyrill
> > >
> >
> >
> >
> > --
> > Tomer Shiran
> > VP Product Management | MapR Technologies | 650-804-8657
> >
>
Re: Auto-aliasing of HBase columns in Drill queries
Posted by Kyrill Alyoshin <ky...@gmail.com>.
Tomer,
Thanks for the quick reply! I'd been browsing Drill's wiki and docs prior
to posting that question and got a sense that "non-materialized" views are
possible in Drill but literally found zero examples of how to set them up.
Would you be so kind to provide a pointer on the web? Google queries
literally return nothing.
Thanks again! ;)
-Kyrill
On Tue, Sep 30, 2014 at 7:07 PM, Tomer Shiran <ts...@maprtech.com> wrote:
> Check out Drill views. You can define a view that exposes the HBase table
> in whatever way you want, including aliases.
>
> On Tue, Sep 30, 2014 at 3:14 PM, Kyrill Alyoshin <ky...@gmail.com>
> wrote:
>
> > Guys,
> >
> > We have an HBase system (MapR M7 to be precise) where we store data in
> > HBase tables under aliased columns. What I mean is that the column keys
> are
> > physically named as '1', '2', '3', etc. This is done to save on storage
> of
> > data. Typically we use Cascading/Cascalog to query this data. We were
> able
> > to add an aliasing layer in our Cascading queries (through Cascading
> > extension) to allow human readable queries (i.e. using 'firstName' alias
> > instead of '1').
> >
> > We'd like to try out Drill for SQL based access. Where should we start
> > "drilling" in the Drill's code base to develop similar extensions? I am
> > assuming that Drill currently provides no such functionality out of the
> > box.
> >
> > In other words, instead of our HBase query looking like this:
> >
> > SELECT cast(account[*‘1’*] as varchar(20)) as `Address`
> >
> > we'd like it to look like this from a user perspective:
> >
> > SELECT cast(account[*‘address’*] as varchar(20)) as `Address`
> >
> > Any help in direction is greatly appreciated. We can easily volunteer to
> > provide such a contribution ourselves, just let us know where to start
> > "drilling". :-)
> >
> > Thank you very much!
> >
> > -Kyrill
> >
>
>
>
> --
> Tomer Shiran
> VP Product Management | MapR Technologies | 650-804-8657
>
Re: Auto-aliasing of HBase columns in Drill queries
Posted by Tomer Shiran <ts...@maprtech.com>.
Check out Drill views. You can define a view that exposes the HBase table
in whatever way you want, including aliases.
On Tue, Sep 30, 2014 at 3:14 PM, Kyrill Alyoshin <ky...@gmail.com>
wrote:
> Guys,
>
> We have an HBase system (MapR M7 to be precise) where we store data in
> HBase tables under aliased columns. What I mean is that the column keys are
> physically named as '1', '2', '3', etc. This is done to save on storage of
> data. Typically we use Cascading/Cascalog to query this data. We were able
> to add an aliasing layer in our Cascading queries (through Cascading
> extension) to allow human readable queries (i.e. using 'firstName' alias
> instead of '1').
>
> We'd like to try out Drill for SQL based access. Where should we start
> "drilling" in the Drill's code base to develop similar extensions? I am
> assuming that Drill currently provides no such functionality out of the
> box.
>
> In other words, instead of our HBase query looking like this:
>
> SELECT cast(account[*‘1’*] as varchar(20)) as `Address`
>
> we'd like it to look like this from a user perspective:
>
> SELECT cast(account[*‘address’*] as varchar(20)) as `Address`
>
> Any help in direction is greatly appreciated. We can easily volunteer to
> provide such a contribution ourselves, just let us know where to start
> "drilling". :-)
>
> Thank you very much!
>
> -Kyrill
>
--
Tomer Shiran
VP Product Management | MapR Technologies | 650-804-8657