You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by carol Bourgade <cj...@gmail.com> on 2014/11/25 00:09:45 UTC
convert_from example
can someone give an example using convert_from instead of cast in the
following query on an HBase table :
select cast(row_key as int) as cust_id
from hbase.customers t ;
Re: convert_from example
Posted by Carol Bourgade <cj...@gmail.com>.
this works on drill .06 on the mapr drill sandbox :
create or replace view tview as select cast(convert_from(row_key, 'UTF8')
as bigint) as cust_id,
cast(t.loyalty.membership as varchar(20)) as membership from
dfs.`/tables/customers` t;
select sum(orders.order_total) as sales, tview.membership from hive.orders,
tview where orders.cust_id=tview.cust_id group by tview.membership;
______________________________________
HOWEVER this gives an error:
create or replace view tview as select cast(convert_from(row_key, 'UTF8')
as bigint) as cust_id,
cast(t.loyalty.membership as varchar(20)) as membership from
maprdb.customers t;
select sum(orders.order_total) as sales, tview.membership from hive.orders,
tview where orders.cust_id=tview.cust_id group by tview.membership;
______________________________________
this works:
create or replace view tview as select cast(row_key as bigint) as cust_id,
cast(t.loyalty.membership as varchar(20)) as membership
from maprdb.customers t;
select sum(orders.order_total) as sales, tview.membership from hive.orders,
tview where orders.cust_id=tview.cust_id group by tview.membership;
______________________________________
the data in this HBase (maprdb) table is from a importtsv bulkimport of a
csv file so it is all strings converted to bytearrays. Is it recommended
to just use cast in this case, which always works ? It is still not clear
to me when to use convert_from for an hbase table.
Re: convert_from example
Posted by Carol Bourgade <cj...@gmail.com>.
*this works* on drill .06 on the mapr drill sandbox :
create or replace view tview as select cast(convert_from(row_key, 'UTF8')
as bigint) as cust_id,
cast(t.loyalty.membership as varchar(20)) as membership from
*dfs.`/tables/customers`* t;
select sum(orders.order_total) as sales, tview.membership from hive.orders,
tview where orders.cust_id=tview.cust_id group by tview.membership;
*HOWEVER this gives an error:*
create or replace view tview as select cast(convert_from(row_key, 'UTF8')
as bigint) as cust_id,
cast(t.loyalty.membership as varchar(20)) as membership from
*maprdb.customers* t;
select sum(orders.order_total) as sales, tview.membership from hive.orders,
tview where orders.cust_id=tview.cust_id group by tview.membership;
*and this works: *
create or replace view tview as select *cas*t(row_key as bigint) as cust_id,
cast(t.loyalty.membership as varchar(20)) as membership
from maprdb.customers t;
select sum(orders.order_total) as sales, tview.membership from hive.orders,
tview where orders.cust_id=tview.cust_id group by tview.membership;
the data in this HBase (maprdb) table is from a importtsv bulkimport of a
csv file so it is all strings converted to bytearrays. Is it recommended
to just use cast in this case, which always works ? It is still not clear
to me when to use convert_from for an hbase table.
On Tue, Nov 25, 2014 at 11:40 AM, Carol Bourgade <cj...@gmail.com>
wrote:
> the values in the HBase table are string bytes , ( drill sandbox data)
>
> the following works, create an HBase view :
> create or replace view tview as select cast(row_key as bigint) as cust_id,
> cast(t.loyalty.membership as varchar(20)) as membership
> from maprdb.customers t;
>
> join view with hive orders :
> select sum(orders.order_total) as sales, tview.membership from
> hive.orders, tview where orders.cust_id=tview.cust_id group by
> tview.membership;
>
>
> But if I create this HBase view:
> create or replace view tview as select cast(convert_from(row_key, 'UTF8')
> as bigint) as cust_id,
> cast(t.loyalty.membership as varchar(20)) as membership from
> maprdb.customers t;
>
> I get this error on the same Join :
>
> select sum(orders.order_total) as sales, tview.membership from
> hive.orders, tview where orders.cust_id=tview.cust_id group by
> tview.membership;
> Query failed: Failure due to uncaught exception Encountered an illegal
> char on line 1, column 31: '' [32b09a52-0a16-4d43-9277-1da03d336b29]
> Error: exception while executing query: Failure while trying to get next
> result batch. (state=,code=0)
>
> On Mon, Nov 24, 2014 at 6:24 PM, Steven Phillips <sp...@maprtech.com>
> wrote:
>
>> It depends on how the value is encoded. Possible encodings include, e.g.
>> little endian, big endian, and UTF8 string.
>>
>> select convert_from(row_key, 'INT'); // for little endian encoding
>> select convert_from(row_key, 'INT_BE'); // for big endian encoding
>> select cast(convert_from(row_key, 'UTF8') as int); // for utf8 string
>> encoding
>>
>> On Mon, Nov 24, 2014 at 3:09 PM, carol Bourgade <cj...@gmail.com>
>> wrote:
>>
>> > can someone give an example using convert_from instead of cast in the
>> > following query on an HBase table :
>> >
>> > select cast(row_key as int) as cust_id
>> > from hbase.customers t ;
>> >
>>
>>
>>
>> --
>> Steven Phillips
>> Software Engineer
>>
>> mapr.com
>>
>
>
Re: convert_from example
Posted by Carol Bourgade <cj...@gmail.com>.
the values in the HBase table are string bytes , ( drill sandbox data)
the following works, create an HBase view :
create or replace view tview as select cast(row_key as bigint) as cust_id,
cast(t.loyalty.membership as varchar(20)) as membership
from maprdb.customers t;
join view with hive orders :
select sum(orders.order_total) as sales, tview.membership from hive.orders,
tview where orders.cust_id=tview.cust_id group by tview.membership;
But if I create this HBase view:
create or replace view tview as select cast(convert_from(row_key, 'UTF8')
as bigint) as cust_id,
cast(t.loyalty.membership as varchar(20)) as membership from
maprdb.customers t;
I get this error on the same Join :
select sum(orders.order_total) as sales, tview.membership from hive.orders,
tview where orders.cust_id=tview.cust_id group by tview.membership;
Query failed: Failure due to uncaught exception Encountered an illegal char
on line 1, column 31: '' [32b09a52-0a16-4d43-9277-1da03d336b29]
Error: exception while executing query: Failure while trying to get next
result batch. (state=,code=0)
On Mon, Nov 24, 2014 at 6:24 PM, Steven Phillips <sp...@maprtech.com>
wrote:
> It depends on how the value is encoded. Possible encodings include, e.g.
> little endian, big endian, and UTF8 string.
>
> select convert_from(row_key, 'INT'); // for little endian encoding
> select convert_from(row_key, 'INT_BE'); // for big endian encoding
> select cast(convert_from(row_key, 'UTF8') as int); // for utf8 string
> encoding
>
> On Mon, Nov 24, 2014 at 3:09 PM, carol Bourgade <cj...@gmail.com>
> wrote:
>
> > can someone give an example using convert_from instead of cast in the
> > following query on an HBase table :
> >
> > select cast(row_key as int) as cust_id
> > from hbase.customers t ;
> >
>
>
>
> --
> Steven Phillips
> Software Engineer
>
> mapr.com
>
Re: convert_from example
Posted by Aditya <ad...@gmail.com>.
To expand on Steven's answer, you can use CAST() if the data is either
(1)stored as text or (2) in its native internal format but is differently
typed, for example (1)a double value 1234.56 is stored in a CSV file as
..., "1234.56", ...., or (2) a to use a Long value as TIMESTAMP.
OTOH, convert_from() helps you to interpret data encoded in some binary
format, i.e. a VARBINARY column which contains Integer as 4 bytes little
endian 32 encoding (1 = 01 00 00 00).
On Mon, Nov 24, 2014 at 3:24 PM, Steven Phillips <sp...@maprtech.com>
wrote:
> It depends on how the value is encoded. Possible encodings include, e.g.
> little endian, big endian, and UTF8 string.
>
> select convert_from(row_key, 'INT'); // for little endian encoding
> select convert_from(row_key, 'INT_BE'); // for big endian encoding
> select cast(convert_from(row_key, 'UTF8') as int); // for utf8 string
> encoding
>
> On Mon, Nov 24, 2014 at 3:09 PM, carol Bourgade <cj...@gmail.com>
> wrote:
>
> > can someone give an example using convert_from instead of cast in the
> > following query on an HBase table :
> >
> > select cast(row_key as int) as cust_id
> > from hbase.customers t ;
> >
>
>
>
> --
> Steven Phillips
> Software Engineer
>
> mapr.com
>
Re: convert_from example
Posted by Steven Phillips <sp...@maprtech.com>.
It depends on how the value is encoded. Possible encodings include, e.g.
little endian, big endian, and UTF8 string.
select convert_from(row_key, 'INT'); // for little endian encoding
select convert_from(row_key, 'INT_BE'); // for big endian encoding
select cast(convert_from(row_key, 'UTF8') as int); // for utf8 string
encoding
On Mon, Nov 24, 2014 at 3:09 PM, carol Bourgade <cj...@gmail.com>
wrote:
> can someone give an example using convert_from instead of cast in the
> following query on an HBase table :
>
> select cast(row_key as int) as cust_id
> from hbase.customers t ;
>
--
Steven Phillips
Software Engineer
mapr.com