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 <jamestay...@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 <sterr...@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 <jamestay...@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 <anilgupt...@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 <sterr...@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
>>>>
>>>
>>>
>>
>
