Re: Dynamic Fields And Views

[Steve Terrell]

Cool!  I'm using 4.6.0.  Will soon try 4.7.0.  Thank you, James!

On Thu, Feb 25, 2016 at 2:55 PM, James Taylor 
wrote:

> This worked for me in the latest 4.7.0 RC3:
>
> Connected to: Phoenix (version 4.7)
> Driver: PhoenixEmbeddedDriver (version 4.7)
> Autocommit status: true
> Transaction isolation: TRANSACTION_READ_COMMITTED
> Building list of tables and columns for tab-completion (set fastconnect to
> true to skip)...
> 83/83 (100%) Done
> Done
> sqlline version 1.1.8
> 0: jdbc:phoenix:localhost> create table TMP_SNACKS(k bigint primary key,
> c1 varchar);
> No rows affected (0.322 seconds)
> 0: jdbc:phoenix:localhost> upsert into TMP_SNACKS(k, c1, "page_title"
> varchar) values(1,'a','b');
> 1 row affected (0.053 seconds)
> 0: jdbc:phoenix:localhost> create view MY_VIEW("page_title" varchar) as
> select * from TMP_SNACKS;
> No rows affected (0.048 seconds)
> 0: jdbc:phoenix:localhost> select * from MY_VIEW;
> ++-+-+
> | K  | C1  | page_title  |
> ++-+-+
> | 1  | a   | b   |
> ++-+-+
> 1 row selected (0.056 seconds)
>
>
> On Thu, Feb 25, 2016 at 11:33 AM, Steve Terrell 
> wrote:
>
>> Sorry, no worky.
>>
>> I have a table named TMP_SNACKS.  TMP_SNACKS has a few static fields and
>> many dynamic fields.  Usually there are only a few dynamic columns (5 or
>> less) that I am interested in.
>>
>> One of the dynamic fields in TMP_SNACKS is "page_title".  I tried this:
>>
>> create view MY_VIEW("page_title" varchar) as select * from TMP_SNACKS
>>
>> Then I tried a select:
>> select "page_title" from MY_VIEW limit 1;
>> The result was
>> java.lang.RuntimeException:
>> org.apache.phoenix.exception.PhoenixIOException:
>> org.apache.hadoop.hbase.regionserver.NoSuchColumnFamilyException: Column
>> family 0 does not exist in region
>> TMP_SNACKS,,1455911043400.8665a1ac6da8ffe95454a5299a8e55f3. ...
>>
>> I may not have described my problem very well, but I have already played
>> around with the syntax a lot and am pretty sure there is no current
>> solution.  But I would love to be wrong.  :)
>>
>> Thanks,
>> Steve
>>
>> On Thu, Feb 25, 2016 at 12:45 PM, James Taylor 
>> wrote:
>>
>>> Hi Steve,
>>>
>>> You can do what you want with a view today, but the syntax is just a bit
>>> different than what you tried. You declare your dynamic columns after the
>>> view name, like this:
>>>
>>> create MY_VIEW("dynamic_field" varchar) as select * from MY_TABLE
>>>
>>> You can also alter a view and dynamically add/remove columns on-the-fly.
>>>
>>> alter view MY_VIEW add "dynamic_field2" varchar;
>>> alter view MY_VIEW drop column "dynamic_field1";
>>>
>>> You can even conditionally add a column only if it's not already there:
>>>
>>> alter view MY_VIEW add if not exists "dynamic_field2" varchar;
>>>
>>> See full syntax here[1].
>>>
>>> It's a cheap operation, as the only thing that's happening behind the
>>> scenes is the update of the metadata. The advantage (as you've seen) is
>>> that Phoenix is tracking all your dynamic columns.
>>>
>>> Thanks,
>>> James
>>>
>>> [1] https://phoenix.apache.org/language/index.html#alter
>>>
>>> On Thu, Feb 25, 2016 at 3:31 AM, anil gupta 
>>> wrote:
>>>
 +1 for a view that has dynamic columns. This would make life easier
 with dynamic columns.

 On Tue, Feb 23, 2016 at 4:00 PM, Steve Terrell 
 wrote:

> I have a table with many dynamic fields.  Works great.  However, it's
> a bit of a nuisance to have to supply each dynamic field's type in every
> query.
>
> Example:
> select "dynamic_field" from MY_TABLE("dynamic_field" varchar)
>
> This example is not too bad, but image it with 5+ dynamic fields being
> used.  The select statement becomes very verbose.
>
> I understand the reason behind requiring the field type of each
> dynamic field.  But I was wondering if there is a way to define a view 
> that
> manages the dynamic field types so that I could do something like this:
>
> create MY_VIEW as select * from MY_TABLE("dynamic_field" varchar)
> -- invalid syntax; pseudo code of what I wish I could do.
>
> select "dynamic_field" from MY_VIEW
>
> Should I create a JIRA for a new feature?  Or is this fundamentally
> not possible?
>
> Thanks,
> Steve
>



 --
 Thanks & Regards,
 Anil Gupta

>>>
>>>
>>
>

Re: Dynamic Fields And Views

[James Taylor]

This worked for me in the latest 4.7.0 RC3:

Connected to: Phoenix (version 4.7)
Driver: PhoenixEmbeddedDriver (version 4.7)
Autocommit status: true
Transaction isolation: TRANSACTION_READ_COMMITTED
Building list of tables and columns for tab-completion (set fastconnect to
true to skip)...
83/83 (100%) Done
Done
sqlline version 1.1.8
0: jdbc:phoenix:localhost> create table TMP_SNACKS(k bigint primary key, c1
varchar);
No rows affected (0.322 seconds)
0: jdbc:phoenix:localhost> upsert into TMP_SNACKS(k, c1, "page_title"
varchar) values(1,'a','b');
1 row affected (0.053 seconds)
0: jdbc:phoenix:localhost> create view MY_VIEW("page_title" varchar) as
select * from TMP_SNACKS;
No rows affected (0.048 seconds)
0: jdbc:phoenix:localhost> select * from MY_VIEW;
++-+-+
| K  | C1  | page_title  |
++-+-+
| 1  | a   | b   |
++-+-+
1 row selected (0.056 seconds)


On Thu, Feb 25, 2016 at 11:33 AM, Steve Terrell 
wrote:

> Sorry, no worky.
>
> I have a table named TMP_SNACKS.  TMP_SNACKS has a few static fields and
> many dynamic fields.  Usually there are only a few dynamic columns (5 or
> less) that I am interested in.
>
> One of the dynamic fields in TMP_SNACKS is "page_title".  I tried this:
>
> create view MY_VIEW("page_title" varchar) as select * from TMP_SNACKS
>
> Then I tried a select:
> select "page_title" from MY_VIEW limit 1;
> The result was
> java.lang.RuntimeException:
> org.apache.phoenix.exception.PhoenixIOException:
> org.apache.hadoop.hbase.regionserver.NoSuchColumnFamilyException: Column
> family 0 does not exist in region
> TMP_SNACKS,,1455911043400.8665a1ac6da8ffe95454a5299a8e55f3. ...
>
> I may not have described my problem very well, but I have already played
> around with the syntax a lot and am pretty sure there is no current
> solution.  But I would love to be wrong.  :)
>
> Thanks,
> Steve
>
> On Thu, Feb 25, 2016 at 12:45 PM, James Taylor 
> wrote:
>
>> Hi Steve,
>>
>> You can do what you want with a view today, but the syntax is just a bit
>> different than what you tried. You declare your dynamic columns after the
>> view name, like this:
>>
>> create MY_VIEW("dynamic_field" varchar) as select * from MY_TABLE
>>
>> You can also alter a view and dynamically add/remove columns on-the-fly.
>>
>> alter view MY_VIEW add "dynamic_field2" varchar;
>> alter view MY_VIEW drop column "dynamic_field1";
>>
>> You can even conditionally add a column only if it's not already there:
>>
>> alter view MY_VIEW add if not exists "dynamic_field2" varchar;
>>
>> See full syntax here[1].
>>
>> It's a cheap operation, as the only thing that's happening behind the
>> scenes is the update of the metadata. The advantage (as you've seen) is
>> that Phoenix is tracking all your dynamic columns.
>>
>> Thanks,
>> James
>>
>> [1] https://phoenix.apache.org/language/index.html#alter
>>
>> On Thu, Feb 25, 2016 at 3:31 AM, anil gupta 
>> wrote:
>>
>>> +1 for a view that has dynamic columns. This would make life easier with
>>> dynamic columns.
>>>
>>> On Tue, Feb 23, 2016 at 4:00 PM, Steve Terrell 
>>> wrote:
>>>
 I have a table with many dynamic fields.  Works great.  However, it's a
 bit of a nuisance to have to supply each dynamic field's type in every
 query.

 Example:
 select "dynamic_field" from MY_TABLE("dynamic_field" varchar)

 This example is not too bad, but image it with 5+ dynamic fields being
 used.  The select statement becomes very verbose.

 I understand the reason behind requiring the field type of each dynamic
 field.  But I was wondering if there is a way to define a view that manages
 the dynamic field types so that I could do something like this:

 create MY_VIEW as select * from MY_TABLE("dynamic_field" varchar)
 -- invalid syntax; pseudo code of what I wish I could do.

 select "dynamic_field" from MY_VIEW

 Should I create a JIRA for a new feature?  Or is this fundamentally not
 possible?

 Thanks,
 Steve

>>>
>>>
>>>
>>> --
>>> Thanks & Regards,
>>> Anil Gupta
>>>
>>
>>
>

Re: Dynamic Fields And Views

[James Taylor]

Hi Steve,

You can do what you want with a view today, but the syntax is just a bit
different than what you tried. You declare your dynamic columns after the
view name, like this:

create MY_VIEW("dynamic_field" varchar) as select * from MY_TABLE

You can also alter a view and dynamically add/remove columns on-the-fly.

alter view MY_VIEW add "dynamic_field2" varchar;
alter view MY_VIEW drop column "dynamic_field1";

You can even conditionally add a column only if it's not already there:

alter view MY_VIEW add if not exists "dynamic_field2" varchar;

See full syntax here[1].

It's a cheap operation, as the only thing that's happening behind the
scenes is the update of the metadata. The advantage (as you've seen) is
that Phoenix is tracking all your dynamic columns.

Thanks,
James

[1] https://phoenix.apache.org/language/index.html#alter

On Thu, Feb 25, 2016 at 3:31 AM, anil gupta  wrote:

> +1 for a view that has dynamic columns. This would make life easier with
> dynamic columns.
>
> On Tue, Feb 23, 2016 at 4:00 PM, Steve Terrell 
> wrote:
>
>> I have a table with many dynamic fields.  Works great.  However, it's a
>> bit of a nuisance to have to supply each dynamic field's type in every
>> query.
>>
>> Example:
>> select "dynamic_field" from MY_TABLE("dynamic_field" varchar)
>>
>> This example is not too bad, but image it with 5+ dynamic fields being
>> used.  The select statement becomes very verbose.
>>
>> I understand the reason behind requiring the field type of each dynamic
>> field.  But I was wondering if there is a way to define a view that manages
>> the dynamic field types so that I could do something like this:
>>
>> create MY_VIEW as select * from MY_TABLE("dynamic_field" varchar)
>> -- invalid syntax; pseudo code of what I wish I could do.
>>
>> select "dynamic_field" from MY_VIEW
>>
>> Should I create a JIRA for a new feature?  Or is this fundamentally not
>> possible?
>>
>> Thanks,
>> Steve
>>
>
>
>
> --
> Thanks & Regards,
> Anil Gupta
>

Re: Dynamic Fields And Views

[anil gupta]

+1 for a view that has dynamic columns. This would make life easier with
dynamic columns.

On Tue, Feb 23, 2016 at 4:00 PM, Steve Terrell 
wrote:

> I have a table with many dynamic fields.  Works great.  However, it's a
> bit of a nuisance to have to supply each dynamic field's type in every
> query.
>
> Example:
> select "dynamic_field" from MY_TABLE("dynamic_field" varchar)
>
> This example is not too bad, but image it with 5+ dynamic fields being
> used.  The select statement becomes very verbose.
>
> I understand the reason behind requiring the field type of each dynamic
> field.  But I was wondering if there is a way to define a view that manages
> the dynamic field types so that I could do something like this:
>
> create MY_VIEW as select * from MY_TABLE("dynamic_field" varchar)
> -- invalid syntax; pseudo code of what I wish I could do.
>
> select "dynamic_field" from MY_VIEW
>
> Should I create a JIRA for a new feature?  Or is this fundamentally not
> possible?
>
> Thanks,
> Steve
>



-- 
Thanks & Regards,
Anil Gupta

Dynamic Fields And Views

[Steve Terrell]

I have a table with many dynamic fields.  Works great.  However, it's a bit
of a nuisance to have to supply each dynamic field's type in every query.

Example:
select "dynamic_field" from MY_TABLE("dynamic_field" varchar)

This example is not too bad, but image it with 5+ dynamic fields being
used.  The select statement becomes very verbose.

I understand the reason behind requiring the field type of each dynamic
field.  But I was wondering if there is a way to define a view that manages
the dynamic field types so that I could do something like this:

create MY_VIEW as select * from MY_TABLE("dynamic_field" varchar)
-- invalid syntax; pseudo code of what I wish I could do.

select "dynamic_field" from MY_VIEW

Should I create a JIRA for a new feature?  Or is this fundamentally not
possible?

Thanks,
Steve