Re: convert_from example

[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

[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.