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 <[email protected]> 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 <[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
> >
>
