Jacques, I just spent an hour or more trying to read the docs on convert_from/to. I had no success.
There are plenty of examples of converting to or from UTF-8, but none describing conversions to do with integers. In doing (lots of) experiments, I have failed to 1) create a constant of binary type using values or values + convert or values + cast 2) create a value utf-8 format 3) get any interesting convert_from or convert_to to work Can you help with an example that illustrates using CONVERT_FROM on a big-endian integer? Typical queries include: 0: jdbc:drill:zk=local> SELECT CONVERT_TO('[ [1, 2], [3, 4], [5]]' ,'UTF-8') AS MYCOL1 FROM sys.version; *Error: SYSTEM ERROR: org.apache.drill.exec.work.foreman.ForemanException: Unexpected exception during fragment initialization: null* 0: jdbc:drill:zk=local> select cast(x as BINARY(10)) foo from (values 1000)tbl(x); Jul 22, 2015 4:15:45 PM org.apache.calcite.sql.validate.SqlValidatorException <init> SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Cast function cannot convert value of type INTEGER to type BINARY(10) Jul 22, 2015 4:15:45 PM org.apache.calcite.runtime.CalciteException <init> SEVERE: org.apache.calcite.runtime.CalciteContextException: From line 1, column 8 to line 1, column 28: Cast function cannot convert value of type INTEGER to type BINARY(10) *Error: PARSE ERROR: From line 1, column 8 to line 1, column 28: Cast function cannot convert value of type INTEGER to type BINARY(10)* *[Error Id: 20732209-b06e-4ff0-8371-4f04eb0b7a12 on 172.16.0.61:31010 <http://172.16.0.61:31010>] (state=,code=0)* 0: jdbc:drill:zk=local> select convert_to(x, 'INT') from (values 1000) tbl(x) ; *Error: SYSTEM ERROR: org.apache.drill.exec.exception.SchemaChangeException: Failure while trying to materialize incoming schema. Errors* *Error in expression at index -1. Error: Missing function implementation: [convert_toint(BIGINT-OPTIONAL)]. Full expression: --UNKNOWN EXPRESSION--..* On Wed, Jul 22, 2015 at 2:40 PM, Jacques Nadeau <jacq...@dremio.com> wrote: > 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 <alex...@gmail.com> 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 < > > nathaniel.au...@gmail.com> > > 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 <alex...@gmail.com> 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 > > >