Hmmm, what I get when I using the CAST:
0: jdbc:drill:zk=local> select CONVERT_FROM(row_key, 'UTF8') as key, CAST(urls.u.status AS INT) AS status FROM hbase.urls WHERE row_key = 'AZ.OC.ICR'; java.lang.RuntimeException: java.sql.SQLException: SYSTEM ERROR: NumberFormatException: � In the documentation I see following: "Use CONVERT_TO and CONVERT_FROM instead of the CAST function for converting binary data types with one exception: When converting an INT or BIGINT number, having a byte count in the destination/source that does not match the byte count of the number in the VARBINARY source/destination, use CAST." But I have 4 bytes in the corresponding cell of the database... On Wed, Jul 22, 2015 at 2:09 PM, Nathaniel Auvil <[email protected]> wrote: > 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 > > > -- With best wishes, Alex Ott http://alexott.net/ Twitter: alexott_en (English), alexott (Russian) Skype: alex.ott
