phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Samarth Jain <samarth.j...@gmail.com>
Subject Re: Parsing rowkey of existing Hbase table while creating a View
Date Wed, 03 Dec 2014 19:39:49 GMT
Hi Vijay,

One of the closing parentheses is wrongly placed.

Try:
create *view* "events" (
          "cid" UNSIGNED_LONG,
          "timestamp" UNSIGNED_LONG,
          "category" CHAR(128),
           D."pc" VARCHAR,
           D."un" VARCHAR,
           D."ug" VARCHAR
           CONSTRAINT pk PRIMARY KEY (cid, timestamp, category));

- Samarth

On Wednesday, December 3, 2014, Vijay Kukkala <accessvj@gmail.com> wrote:

> James,
>
> Thank you for your response.
>
> Wondering if I would be able to parse the rowkey in the view as you
> specified using the table definition.
> create *view* "events" (
>           "cid" UNSIGNED_LONG,
>           "timestamp" UNSIGNED_LONG,
>           "category" CHAR(128),
>            D."pc" VARCHAR,
>            D."un" VARCHAR,
>            D."ug" VARCHAR)
> CONSTRAINT pk PRIMARY KEY (cid, timestamp, category);
>
> This resulted in an error
> Error: ERROR 602 (42P00): Syntax error. Missing "EOF" at line 1, column
> 466. (state=42P00,code=602)
>
> I am sure this is not what you meant.
>
> thanks,
> Vijay
>
> On Wed Dec 03 2014 at 12:00:22 PM James Taylor <jamestaylor@apache.org
> <javascript:_e(%7B%7D,'cvml','jamestaylor@apache.org');>> wrote:
>
>> Hi Vijay,
>> Yes, you can declare a composite primary key with fixed length sizes
>> for each part. The types you use for each column depend on how you
>> serialized the data into the rowkey. Are they all strings with a fixed
>> length? If so, it'd look something like this:
>>
>> create table "events" (
>>     customerId CHAR(30) NOT NULL,
>>     date CHAR(20) NOT NULL,
>>     category CHAR(50) NOT NULL,
>>     D."pc" VARCHAR,
>>     D."un" VARCHAR,
>>     D."ug" VARCHAR,
>>     D."c0" VARCHAR,
>>     CONSTRAINT pk PRIMARY KEY (customerId, date, category));
>>
>> As far as dynamic columns, you can take a look at
>> http://phoenix.apache.org/dynamic_columns.html. Another alternative
>> would be to just declare all of the possible column qualifiers, as it
>> doesn't hurt anything to declare them even if there's no data in them.
>>
>> You may want to consider creating a new table and copying the existing
>> data into it using UPSERT SELECT:
>> http://phoenix.apache.org/language/index.html#upsert_select. In that
>> case, you could just do a CREATE VIEW for your "events" table, as you
>> wouldn't be updating it since you'd switch to using the new table
>> instead. If you go this route, you'd be able to use additional Phoenix
>> functionality such as arrays
>> (http://phoenix.apache.org/array_type.html) and/or updatable views
>> (http://phoenix.apache.org/views.html) to model your different event
>> types.
>>
>> Thanks,
>> James
>>
>> On Wed, Dec 3, 2014 at 6:53 AM, Vijay Kukkala <vijay.kukkala@gmail.com
>> <javascript:_e(%7B%7D,'cvml','vijay.kukkala@gmail.com');>> wrote:
>> > Hi,
>> > Phoenix version: 4.0  on HDP 2.1
>> > I have an existing Hbase table with rowkey whose format is (customerId,
>> > date, category). It is stored as bytes[100] in Hbase.
>> > I am trying to create a Phoenix view on this table using this DDL
>> command
>> > create table "events" (pk VARBINARY PRIMARY KEY, D."pc" VARCHAR, D."un"
>> > VARCHAR, D."ug" VARCHAR, D."c0" VARCHAR);
>> >
>> > I am able to access the column values correctly. The PK shows up as a
>> > address location. I have two questions
>> > * Primary Key: Is this correct conversion? Is there a way to parse the
>> > rowkey using functions or any other way. I know the offset of each
>> > attribute.
>> > * Columns: Is there a way to specify columns dynamically
>> > we store array values with qualifiers as c[0-3]
>> >
>> > I am trying to migrate the old Hbase data to a phoenix table where the
>> > rowKey is split into specific columns
>> >
>> > thanks in advance,
>> > Vijay
>>
>

Mime
View raw message