Let me clarify this a bit. If the data is encoded as text (UTF8), then cast is what you want to use. If the data is encoded in a binary representation (such as 4 byte little or big endian integer), then you want to use CONVERT_FROM. CONVERT_FROM is about converting from a binary representation to a particular data type. CAST is about converting between known data types.
On Wed, Jul 22, 2015 at 5:29 AM, Alex Ott <[email protected]> wrote: > 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 >
