You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by John Omernik <jo...@omernik.com> on 2015/10/12 21:27:42 UTC
Convert from Array to String
Is there an easy way in drill to convert from an ARRAY or MAP to a string
representation of the same? I know this is an odd question, but I realized
I just was spinning my wheels because I thought I was doing something wrong
with JSON and KVGEN and FLATTEN but in reality, it was working fine, I just
didn't see it because SQL Squirrel kept returning "<Unknown Type (2,002)>
instead of the results of the function.
I guess, if I could take the output from FLATTEN(KVGEN()) and wrap it in a
"CAST(FLATTEN(KVGEN()))" then at least I could continue exploration.
I know that it is a workaround for the limitations of Squirrel, but I'd be
interested in other's thoughts on that, is there a tweak to squirrel that
would allow that to work properly, or just casting it as needed. (Note I
tried CAST as STRING and CASTS as VARCHAR with not much success.
Re: Convert from Array to String
Posted by Daniel Barclay <db...@maprtech.com>.
John Omernik wrote:
> Is there an easy way in drill to convert from an ARRAY or MAP to a string
> representation of the same? I know this is an odd question, but I realized
> I just was spinning my wheels because I thought I was doing something wrong
> with JSON and KVGEN and FLATTEN but in reality, it was working fine, I just
> didn't see it because SQL Squirrel kept returning "<Unknown Type (2,002)>
> instead of the results of the function.
Hmmm. 2002 is the value for java.sql.Types.STRUCT.
> ...
Daniel--
Daniel Barclay
MapR Technologies
Re: Convert from Array to String
Posted by John Omernik <jo...@omernik.com>.
Yes Daniel - 2002 is Struct and 2003 is Array (I'm seeing both)
This is not a Drill problem but a squierrel problem, and an annoying one at
that, I've tried using the option to display unknown data types as strings,
now I just <Error> rather than the "Error Unknown Type (2003 or 2002)"
On Mon, Oct 12, 2015 at 3:37 PM, John Omernik <jo...@omernik.com> wrote:
> That seems to be what I am looking for, however, when I do
> convert_from(flatten(kvgen(allias.`column`)), 'json') I get an error
> "Missing function implementation (convert_fromjson, "map required") when I
> do "convert_to(flatten(kvgen()), 'json') then I get what appears to be hex
> encoded data... (Just long strings of hex, I wonder if Squirrel is doing
> that too)
>
>
> On Mon, Oct 12, 2015 at 2:57 PM, Jason Altekruse <altekrusejason@gmail.com
> > wrote:
>
>> We don't implement casts on array or map, but we do have a convert
>> function
>> that will convert a complex structure to json.
>>
>> You can invoke it like this: convert_from( map_or_list_column_name,
>> 'JSON')
>>
>> This will return the data serialized into JSON in a varchar column.
>>
>> On Mon, Oct 12, 2015 at 12:27 PM, John Omernik <jo...@omernik.com> wrote:
>>
>> > Is there an easy way in drill to convert from an ARRAY or MAP to a
>> string
>> > representation of the same? I know this is an odd question, but I
>> realized
>> > I just was spinning my wheels because I thought I was doing something
>> wrong
>> > with JSON and KVGEN and FLATTEN but in reality, it was working fine, I
>> just
>> > didn't see it because SQL Squirrel kept returning "<Unknown Type
>> (2,002)>
>> > instead of the results of the function.
>> >
>> > I guess, if I could take the output from FLATTEN(KVGEN()) and wrap it
>> in a
>> > "CAST(FLATTEN(KVGEN()))" then at least I could continue exploration.
>> >
>> > I know that it is a workaround for the limitations of Squirrel, but I'd
>> be
>> > interested in other's thoughts on that, is there a tweak to squirrel
>> that
>> > would allow that to work properly, or just casting it as needed. (Note I
>> > tried CAST as STRING and CASTS as VARCHAR with not much success.
>> >
>>
>
>
Re: Convert from Array to String
Posted by Steven Phillips <st...@dremio.com>.
convert_to is the correct function in this case. convert_to converts the
Drill type into some encoding. The output of the convert_to function is
VarBinary. Can you try wrapping cast( ... as varchar(255)) and see if that
displays it correctly?
On Mon, Oct 12, 2015 at 1:37 PM, John Omernik <jo...@omernik.com> wrote:
> That seems to be what I am looking for, however, when I do
> convert_from(flatten(kvgen(allias.`column`)), 'json') I get an error
> "Missing function implementation (convert_fromjson, "map required") when I
> do "convert_to(flatten(kvgen()), 'json') then I get what appears to be hex
> encoded data... (Just long strings of hex, I wonder if Squirrel is doing
> that too)
>
>
> On Mon, Oct 12, 2015 at 2:57 PM, Jason Altekruse <altekrusejason@gmail.com
> >
> wrote:
>
> > We don't implement casts on array or map, but we do have a convert
> function
> > that will convert a complex structure to json.
> >
> > You can invoke it like this: convert_from( map_or_list_column_name,
> > 'JSON')
> >
> > This will return the data serialized into JSON in a varchar column.
> >
> > On Mon, Oct 12, 2015 at 12:27 PM, John Omernik <jo...@omernik.com> wrote:
> >
> > > Is there an easy way in drill to convert from an ARRAY or MAP to a
> string
> > > representation of the same? I know this is an odd question, but I
> > realized
> > > I just was spinning my wheels because I thought I was doing something
> > wrong
> > > with JSON and KVGEN and FLATTEN but in reality, it was working fine, I
> > just
> > > didn't see it because SQL Squirrel kept returning "<Unknown Type
> (2,002)>
> > > instead of the results of the function.
> > >
> > > I guess, if I could take the output from FLATTEN(KVGEN()) and wrap it
> in
> > a
> > > "CAST(FLATTEN(KVGEN()))" then at least I could continue exploration.
> > >
> > > I know that it is a workaround for the limitations of Squirrel, but I'd
> > be
> > > interested in other's thoughts on that, is there a tweak to squirrel
> that
> > > would allow that to work properly, or just casting it as needed. (Note
> I
> > > tried CAST as STRING and CASTS as VARCHAR with not much success.
> > >
> >
>
Re: Convert from Array to String
Posted by John Omernik <jo...@omernik.com>.
That seems to be what I am looking for, however, when I do
convert_from(flatten(kvgen(allias.`column`)), 'json') I get an error
"Missing function implementation (convert_fromjson, "map required") when I
do "convert_to(flatten(kvgen()), 'json') then I get what appears to be hex
encoded data... (Just long strings of hex, I wonder if Squirrel is doing
that too)
On Mon, Oct 12, 2015 at 2:57 PM, Jason Altekruse <al...@gmail.com>
wrote:
> We don't implement casts on array or map, but we do have a convert function
> that will convert a complex structure to json.
>
> You can invoke it like this: convert_from( map_or_list_column_name,
> 'JSON')
>
> This will return the data serialized into JSON in a varchar column.
>
> On Mon, Oct 12, 2015 at 12:27 PM, John Omernik <jo...@omernik.com> wrote:
>
> > Is there an easy way in drill to convert from an ARRAY or MAP to a string
> > representation of the same? I know this is an odd question, but I
> realized
> > I just was spinning my wheels because I thought I was doing something
> wrong
> > with JSON and KVGEN and FLATTEN but in reality, it was working fine, I
> just
> > didn't see it because SQL Squirrel kept returning "<Unknown Type (2,002)>
> > instead of the results of the function.
> >
> > I guess, if I could take the output from FLATTEN(KVGEN()) and wrap it in
> a
> > "CAST(FLATTEN(KVGEN()))" then at least I could continue exploration.
> >
> > I know that it is a workaround for the limitations of Squirrel, but I'd
> be
> > interested in other's thoughts on that, is there a tweak to squirrel that
> > would allow that to work properly, or just casting it as needed. (Note I
> > tried CAST as STRING and CASTS as VARCHAR with not much success.
> >
>
Re: Convert from Array to String
Posted by Jason Altekruse <al...@gmail.com>.
We don't implement casts on array or map, but we do have a convert function
that will convert a complex structure to json.
You can invoke it like this: convert_from( map_or_list_column_name,
'JSON')
This will return the data serialized into JSON in a varchar column.
On Mon, Oct 12, 2015 at 12:27 PM, John Omernik <jo...@omernik.com> wrote:
> Is there an easy way in drill to convert from an ARRAY or MAP to a string
> representation of the same? I know this is an odd question, but I realized
> I just was spinning my wheels because I thought I was doing something wrong
> with JSON and KVGEN and FLATTEN but in reality, it was working fine, I just
> didn't see it because SQL Squirrel kept returning "<Unknown Type (2,002)>
> instead of the results of the function.
>
> I guess, if I could take the output from FLATTEN(KVGEN()) and wrap it in a
> "CAST(FLATTEN(KVGEN()))" then at least I could continue exploration.
>
> I know that it is a workaround for the limitations of Squirrel, but I'd be
> interested in other's thoughts on that, is there a tweak to squirrel that
> would allow that to work properly, or just casting it as needed. (Note I
> tried CAST as STRING and CASTS as VARCHAR with not much success.
>