Re: Convert from Array to String

[Daniel Barclay]

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 "
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

[John Omernik]

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 
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  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 "
> > 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.
> >
>

Convert from Array to String

[John Omernik]

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 "
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

[Jason Altekruse]

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  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 "
> 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

[John Omernik]

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  rather than the "Error Unknown Type (2003 or 2002)"



On Mon, Oct 12, 2015 at 3:37 PM, John Omernik  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  > 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  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 "> (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.
>> >
>>
>
>