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 <[email protected]>
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 <[email protected]>
> 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 <[email protected]>
>> 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 <[email protected]>
>>> 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
>>>
>>
>>
>
