phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <jamestay...@apache.org>
Subject Re: select Dynamic column Name
Date Mon, 24 Aug 2015 20:04:16 GMT
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?
>

Mime
View raw message