You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Steve Terrell <st...@oculus360.us> on 2016/02/24 01:00:08 UTC
Dynamic Fields And Views
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
Re: Dynamic Fields And Views
Posted by Steve Terrell <st...@oculus360.us>.
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 <ja...@apache.org>
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 <st...@oculus360.us>
> 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 <ja...@apache.org>
>> 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 <an...@gmail.com>
>>> 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 <st...@oculus360.us>
>>>> 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
Posted by James Taylor <ja...@apache.org>.
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 <st...@oculus360.us>
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 <ja...@apache.org>
> 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 <an...@gmail.com>
>> 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 <st...@oculus360.us>
>>> 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
Posted by Steve Terrell <st...@oculus360.us>.
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 <ja...@apache.org>
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 <an...@gmail.com> 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 <st...@oculus360.us>
>> 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
Posted by James Taylor <ja...@apache.org>.
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 <an...@gmail.com> 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 <st...@oculus360.us>
> 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
Posted by anil gupta <an...@gmail.com>.
+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 <st...@oculus360.us>
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