Maybe look at using BYTE_SUBSTR to deconstruct the row_key in the elements of openTSDB, and then convert each element as appropriate.
http://drill.apache.org/docs/string-manipulation/#byte_substr —Andries On Apr 21, 2015, at 8:56 AM, Jacques Nadeau <[email protected]> wrote: > Quick edit, my query should have used "tags" instead of "tag". Corrected > below: > > SELECT row.metric, row.tags[0].key, row.tags[0].value ( > SELECT OPENTSDB_ROW(rowkey) as row from hbase.t1 > )x > > On Tue, Apr 21, 2015 at 8:55 AM, Jacques Nadeau <[email protected]> wrote: > >> It doesn't look like the key has any UTF8 data in it. I recommend you >> create a UDF that breaks the bytes apart into the separate sections using a >> complex output. This way you could write something like this: >> >> select row.metric, row.tag[0].key, row.tag[0].value ( >> SELECT OPENTSDB_ROW(rowkey) as row from hbase.t1 >> )x >> >> The OPENTSDB_ROW function would take in a VarBinary and return a >> ComplexWriter. The code would then map the bytes to a logical structure >> like this: >> >> { >> metric: xxx, >> time: xxx, >> tags: [ >> {key: xxx, value xxx}, >> {key: xxx, value xxx} >> ] >> } >> >> On Tue, Apr 21, 2015 at 8: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 >>> >>> >> >>
