Dynamic Columns

Sometimes defining a static schema up front is not feasible. Instead, a subset of columns may be specified at table create time while the rest are specified at query time. As of Phoenix 1.2, dynamic columns are supported by allowing column definitions in parentheses after the table name in the FROM clause of a SELECT statement. Although this is not standard SQL, it is useful for leveraging the late-binding capability of HBase.

For example:

SELECT eventTime, lastGCTime, usedMemory, maxMemory
FROM EventLog(lastGCTime TIME, usedMemory BIGINT, maxMemory BIGINT)
WHERE eventType = 'OOM' AND lastGCTime < eventTime - 1;

You might define only a subset of event columns at create time, because each event type can have different properties:

CREATE TABLE EventLog (
    eventId BIGINT NOT NULL,
    eventTime TIME NOT NULL,
    eventType CHAR(3),
    CONSTRAINT pk PRIMARY KEY (eventId, eventTime)
);

To upsert a row with dynamic columns:

UPSERT INTO EventLog (
    eventId,
    eventTime,
    eventType,
    lastGCTime TIME,
    usedMemory BIGINT,
    maxMemory BIGINT
)
VALUES (1, CURRENT_TIME(), 'abc', CURRENT_TIME(), 512, 1024);