Thanks James /Samarth, that helped. Sorry for the duplicate emails.
On Wed Dec 03 2014 at 1:41:01 PM Samarth Jain <[email protected]> wrote: > 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 <[email protected]> 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 <[email protected]> >> 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 <[email protected]> >>> 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 >>> >>
