Mailing-List: contact drill-user-help@incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: drill-user@incubator.apache.org
Received-SPF: pass (athena.apache.org: domain of cjbourgade@gmail.com
 designates 209.85.223.180 as permitted sender)
MIME-Version: 1.0
In-Reply-To: 
 <CAA_-67e1xa8z0MTR8G1QYXhhG+uGgohFz=MHLy9oweFWpe3BDw@mail.gmail.com>
References: 
 <CA+rY6eu2rapGo2bt9vSPCSyeBmiAmQKF9mr_qOcUU39c4zVFQg@mail.gmail.com>
	<CAA_-67e1xa8z0MTR8G1QYXhhG+uGgohFz=MHLy9oweFWpe3BDw@mail.gmail.com>
Date: Tue, 25 Nov 2014 11:40:05 -0500
Message-ID: 
 <CA+rY6etMzpc3Rq8nSToL=PWnO5R84nZVA9yKMyEDd0j-7houHQ@mail.gmail.com>
Subject: Re: convert_from example
From: Carol Bourgade <cjbourgade@gmail.com>
To: drill-user@incubator.apache.org
Content-Type: multipart/alternative; boundary=001a113fe4ca3a1d2e0508b19000

--001a113fe4ca3a1d2e0508b19000
Content-Type: text/plain; charset=UTF-8

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 <sphillips@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 <cjbourgade@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
>

--001a113fe4ca3a1d2e0508b19000--