You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by "Manjee, Sunile" <Su...@Teradata.com> on 2015/08/24 19:59:59 UTC
select Dynamic column Name
I am unable to find a sql way to retrieve dynamic column names. For example I have 1000 columns, and 100 of them start with name student. I would like to retrieve all columns which start with student*. Any ideas how I could do this with phoenix?
Re: select Dynamic column Name
Posted by James Taylor <ja...@apache.org>.
Hi Satya,
Since querying directly into the SYSTEM.CATALOG is not a supported API, you
might be affected when trying to upgrade to new versions of Phoenix when
we've made changes to the SYSTEM.CATALOG schema.For example, the
SYSTEM.CATALOG may have columns added and removed. Or we might create new
kind of linking rules (which your queries might pick up accidentally). Or
we might take what's one table and turn it into many: one with tables, one
with indexes and another with column definitions (an extreme example, but
you get the idea).
Thanks,
James
On Thu, Aug 27, 2015 at 9:22 AM, Ns G <ns...@gmail.com> wrote:
> Hi James,
>
> Would you mind if you can elaborate more what you mean by underlying
> schema changes?
>
> I am asking this because we are having a service which fetches columns
> from system catalog. If you can tell how It will affect then I shall
> fallback jdbc metadataapi.
>
> Thanks,
> Satya
> On 27-Aug-2015 8:26 pm, "James Taylor" <ja...@apache.org> wrote:
>
>> Use these standard JDBC APIs:
>> http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html
>>
>> Querying directly against the SYSTEM.CATALOG is not recommended as
>> underlying schema changes may impact you.
>>
>> Thanks,
>> James
>>
>> On Thursday, August 27, 2015, Ns G <ns...@gmail.com> wrote:
>>
>>> Hi James,
>>>
>>> I think we can get the dynamic column names from SYSTEM.CATALOG table.
>>> Using as co-related sub query we can use in select statement and fetch
>>> details. Isn't this an option to consider? Do you see anything wrong with
>>> this approach?
>>>
>>> Thanks,
>>> Satya
>>> On 27-Aug-2015 7:28 pm, "Manjee, Sunile" <Su...@teradata.com>
>>> wrote:
>>>
>>>> James,
>>>> Where can I find info on "JDBC Metadata APIs for each view entity"
>>>>
>>>> From: James Taylor <ja...@apache.org>
>>>> Reply-To: "user@phoenix.apache.org" <us...@phoenix.apache.org>
>>>> Date: Monday, August 24, 2015 at 3:04 PM
>>>> To: user <us...@phoenix.apache.org>
>>>> Subject: Re: select Dynamic column Name
>>>>
>>>> JDBC Metadata APIs for each view entity
>>>>
>>>
Re: select Dynamic column Name
Posted by Ns G <ns...@gmail.com>.
Hi James,
Would you mind if you can elaborate more what you mean by underlying schema
changes?
I am asking this because we are having a service which fetches columns from
system catalog. If you can tell how It will affect then I shall fallback
jdbc metadataapi.
Thanks,
Satya
On 27-Aug-2015 8:26 pm, "James Taylor" <ja...@apache.org> wrote:
> Use these standard JDBC APIs:
> http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html
>
> Querying directly against the SYSTEM.CATALOG is not recommended as
> underlying schema changes may impact you.
>
> Thanks,
> James
>
> On Thursday, August 27, 2015, Ns G <ns...@gmail.com> wrote:
>
>> Hi James,
>>
>> I think we can get the dynamic column names from SYSTEM.CATALOG table.
>> Using as co-related sub query we can use in select statement and fetch
>> details. Isn't this an option to consider? Do you see anything wrong with
>> this approach?
>>
>> Thanks,
>> Satya
>> On 27-Aug-2015 7:28 pm, "Manjee, Sunile" <Su...@teradata.com>
>> wrote:
>>
>>> James,
>>> Where can I find info on "JDBC Metadata APIs for each view entity"
>>>
>>> From: James Taylor <ja...@apache.org>
>>> Reply-To: "user@phoenix.apache.org" <us...@phoenix.apache.org>
>>> Date: Monday, August 24, 2015 at 3:04 PM
>>> To: user <us...@phoenix.apache.org>
>>> Subject: Re: select Dynamic column Name
>>>
>>> JDBC Metadata APIs for each view entity
>>>
>>
Re: select Dynamic column Name
Posted by James Taylor <ja...@apache.org>.
Use these standard JDBC APIs:
http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html
Querying directly against the SYSTEM.CATALOG is not recommended as
underlying schema changes may impact you.
Thanks,
James
On Thursday, August 27, 2015, Ns G <ns...@gmail.com> wrote:
> Hi James,
>
> I think we can get the dynamic column names from SYSTEM.CATALOG table.
> Using as co-related sub query we can use in select statement and fetch
> details. Isn't this an option to consider? Do you see anything wrong with
> this approach?
>
> Thanks,
> Satya
> On 27-Aug-2015 7:28 pm, "Manjee, Sunile" <Sunile.Manjee@teradata.com
> <javascript:_e(%7B%7D,'cvml','Sunile.Manjee@teradata.com');>> wrote:
>
>> James,
>> Where can I find info on "JDBC Metadata APIs for each view entity"
>>
>> From: James Taylor <jamestaylor@apache.org
>> <javascript:_e(%7B%7D,'cvml','jamestaylor@apache.org');>>
>> Reply-To: "user@phoenix.apache.org
>> <javascript:_e(%7B%7D,'cvml','user@phoenix.apache.org');>" <
>> user@phoenix.apache.org
>> <javascript:_e(%7B%7D,'cvml','user@phoenix.apache.org');>>
>> Date: Monday, August 24, 2015 at 3:04 PM
>> To: user <user@phoenix.apache.org
>> <javascript:_e(%7B%7D,'cvml','user@phoenix.apache.org');>>
>> Subject: Re: select Dynamic column Name
>>
>> JDBC Metadata APIs for each view entity
>>
>
Re: select Dynamic column Name
Posted by Ns G <ns...@gmail.com>.
Hi James,
I think we can get the dynamic column names from SYSTEM.CATALOG table.
Using as co-related sub query we can use in select statement and fetch
details. Isn't this an option to consider? Do you see anything wrong with
this approach?
Thanks,
Satya
On 27-Aug-2015 7:28 pm, "Manjee, Sunile" <Su...@teradata.com> wrote:
> James,
> Where can I find info on "JDBC Metadata APIs for each view entity"
>
> From: James Taylor <ja...@apache.org>
> Reply-To: "user@phoenix.apache.org" <us...@phoenix.apache.org>
> Date: Monday, August 24, 2015 at 3:04 PM
> To: user <us...@phoenix.apache.org>
> Subject: Re: select Dynamic column Name
>
> JDBC Metadata APIs for each view entity
>
Re: select Dynamic column Name
Posted by "Manjee, Sunile" <Su...@Teradata.com>.
James,
Where can I find info on "JDBC Metadata APIs for each view entity"
From: James Taylor <ja...@apache.org>>
Reply-To: "user@phoenix.apache.org<ma...@phoenix.apache.org>" <us...@phoenix.apache.org>>
Date: Monday, August 24, 2015 at 3:04 PM
To: user <us...@phoenix.apache.org>>
Subject: Re: select Dynamic column Name
JDBC Metadata APIs for each view entity
Re: select Dynamic column Name
Posted by James Taylor <ja...@apache.org>.
Hi Sunile,
Dynamic columns are not tracked at all in Phoenix, however an alternative
is to leverage support for updatable views[1] in Phoenix in which you can
dynamically create and add columns on-the-fly. In many use cases I've seen
where there are hundreds of sparse columns defined for a schema, there's a
column that determines *which* sparse columns are applicable for a given
row. An example would be a metric_event table in which an event_type column
value defines which columns are associated with a given event_type. This
might be modeled with a base table and a series of views like this:
CREATE TABLE metric_event (
event_type CHAR(2),
event_id VARCHAR,
created_date DATE,
CONSTRAINT pk PRIMARY KEY (event_type, event_id));
CREATE VIEW gc_event( -- define columns specific to a GC event
duration BIGINT,
eden_space BIGINT,
survivor_space BIGINT,
...)
AS SELECT * FROM event WHERE event_type = 'GC'
CREATE VIEW io_event( -- define columns specific to an IO event
io_wait_time BIGINT,
iops BIGINT,
read_workload INTEGER,
write_workload INTEGER,
...)
AS SELECT * FROM event WHERE event_type = 'IO'
Using this approach, Phoenix will keep track of the columns each view for
you so you don't have to. All views reside in the same HBase table (the one
corresponding to your base table). When you do a SELECT * FROM io_event,
you'll get back only those columns defined for that view plus the columns
from the base table. You can also dynamically interrogate the metadata
through the JDBC Metadata APIs for each view entity.
You do not need to create all the views up front at schema creation time
either. You can create them on-the-fly. All views share the same, single
underlying HBase table, so no HBase metadata operations would be performed
when you create a new view. Creating a new view more or less correspond to
doing a Put of a few rows in the SYSTEM.CATALOG table (the HBase table that
holds your metadata), so it's not an expensive/heavy-weight operation to
create and alter them (as it is with a typical RDBMS).
There was a discussion[2] on this a while back on the mail list that you
can look review for other ides too.
HTH. Thanks,
James
[1] http://phoenix.apache.org/views.html
[2]
http://search-hadoop.com/m/9UY0h28FnJ32JFKPT1/view&subj=Re+Select+dynamic+column+content
On Mon, Aug 24, 2015 at 10:59 AM, Manjee, Sunile <Sunile.Manjee@teradata.com
> wrote:
> I am unable to find a sql way to retrieve dynamic column names. For
> example I have 1000 columns, and 100 of them start with name student. I
> would like to retrieve all columns which start with student*. Any ideas
> how I could do this with phoenix?
>