You can use the CONVERT_TO and CONVERT_FROM functions to encode and decode data that is binary or complex. For example, HBase stores data as encoded VARBINARY data. To read HBase data in Drill, convert every column of an HBase table *from* binary to an SQL data type while selecting the data. http://drill.apache.org/docs/data-types/#convert_to-and-convert_from-data-types
On Tue, Apr 21, 2015 at 11:46 AM, Carol McDonald <[email protected]> wrote: > I don't have the ostb table to test , but for byte number conversion > something like > > convert_from(row_key , 'BIGINT_BE') > > https://cwiki.apache.org/confluence/display/DRILL/SQL+Functions > > The following table provides the data types that you use with the > CONVERT_TO and CONVERT_FROM functions: > *Type**Input Type**Output Type*BOOLEAN_BYTEbytes(1)booleanTINYINT_BE > bytes(1)tinyintTINYINTbytes(1)tinyintSMALLINT_BEbytes(2)smallintSMALLINT > bytes(2)smallintINT_BEbytes(4)intINTbytes(4)intBIGINT_BEbytes(8)bigint > BIGINTbytes(8)bigintFLOATbytes(4)float (float4)DOUBLEbytes(8)double > (float8)INT_HADOOPVbytes(1-9)intBIGINT_HADOOPVbytes(1-9)bigint > DATE_EPOCH_BEbytes(8)dateDATE_EPOCHbytes(8)dateTIME_EPOCH_BEbytes(8)time > TIME_EPOCHbytes(8)timeUTF8bytesvarcharUTF16bytesvar16charUINT8bytes(8) > uint8 > > > On Tue, Apr 21, 2015 at 11:38 AM, Christopher Matta <[email protected]> > wrote: > >> I’m trying to use Drill to query time-series data stored in OpenTSDB >> <http://opentsdb.net/>. The row keys are supposed to be byte array >> encoded >> according to this schema: >> http://opentsdb.net/docs/build/html/user_guide/backends/hbase.html >> >> When trying to do a simple CONVERT_FROM I get the following results: >> >> 0: jdbc:drill:zk=172.16.1.175:5181,172.16.1.1> select >> CONVERT_FROM(row_key, 'UTF8') from tsdb limit 100; >> +------------+ >> | EXPR$0 | >> +------------+ >> | U5p | >> | U5p! | >> | U5p" | >> | U5p% | >> | U5p& | >> | U5p* | >> | U5"� | >> | U5"�! | >> >> Which is a long way off of what’s described in the opentsdb schema page. >> >> Any tips for properly querying this kind of data? >> >> Chris Matta >> [email protected] >> 215-701-3146 >> >> > >
