to convert data, use the CAST function as in: Select CAST(hbase.urls as VARCHAR(64)) as url from ...
On Wed, Jul 22, 2015 at 7:22 AM, Alex Ott <[email protected]> wrote: > Hello > > I'm starting to play with Apache Drill & try to use it with HBase. > > I have following questions: > - I have HBase table, where some columns have minus sign ('-') in the name, > like, 'raw-url', etc. How I can query this table & do conversion of the > the corresponding columns? I tried to use single quotes around name, but in > this case the name of column itself is returned: > > 0: jdbc:drill:zk=local> select CONVERT_FROM(row_key, 'UTF8') as key, > CONVERT_FROM('urls.u.raw-url', 'UTF8') AS url FROM > hbase.urls WHERE row_key = 'AZ.OC.ICR'; > +------------+-----------------+ > | key | url | > +------------+-----------------+ > | AZ.OC.ICR | urls.u.raw-url | > +------------+-----------------+ > > Use of backquotes or double quotes leads to the error. > > - Another question is about data conversion - I have 'status' column that > holds integer value (as binary), but when I'm trying to convert it from > binary to INT, then I get value different from stored in the DB: > > For example, for this row I have status field equal to 200 (0xC8) > > hbase(main):004:0> get 'urls', 'AZ.OC.ICR', {COLUMN => 'u'} > COLUMN > CELL > > u:check-td timestamp=1422651539493, > value=2015-01-30T07:53:17Z > u:checked timestamp=1422651539493, > value=\xFF > u:imp-td timestamp=1414402209086, > value=2014-09-11T06:51:41Z > u:raw-url timestamp=1411476725886, value= > http://RCI.CO.ZA > u:status timestamp=1411476725886, > value=\x00\x00\x00\xC8 > 5 row(s) in 0.0300 seconds > > But when I do query from Drill, I get some big negative number: > > 0: jdbc:drill:zk=local> select CONVERT_FROM(row_key, 'UTF8') as key, > CONVERT_FROM(urls.u.status, 'INT') AS status FROM hbase.urls WHERE row_key > = 'AZ.OC.ICR' > . . . . . . . . . . . > ; > +------------+-------------+ > | key | status | > +------------+-------------+ > | AZ.OC.ICR | -939524096 | > +------------+-------------+ > > What is the correct way of converting binary data into corresponding > representation? > > Thank you > > -- > With best wishes, Alex Ott > http://alexott.net/ > Twitter: alexott_en (English), alexott (Russian) > Skype: alex.ott >
