*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 <[email protected]> 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 <[email protected]> > 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 <[email protected]> >> 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 >> > >
