Thanks for all your valuable replies.
I tried VARCHAR 16 BYTES, it goes well.
SQL>CREATE TABLE EVENT_BYTES (
v_date timestamp(6) NOT NULL,
sid varchar(16 BYTES) NOT NULL DEFAULT '',
uid varchar(16 BYTES) NOT NULL DEFAULT '',
vid int unsigned NOT NULL ,
idx tinyint NOT NULL,
created_at int unsigned NOT NULL ,
p_id bigint NOT NULL ,
category varchar(255) CHARACTER SET UTF8 NOT NULL,
e_action varchar(255) CHARACTER SET UTF8 NOT NULL,
label varchar(255) CHARACTER SET UTF8 NOT NULL,
e_value int NOT NULL,
is_bounced tinyint NOT NULL DEFAULT 0,
primary key (sid,v_date desc,uid,vid,idx)
)
salt using 4 partitions on (sid,v_date,uid,vid)
division by (date_trunc('day', v_date))
HBASE_OPTIONS( DATA_BLOCK_ENCODING = 'FAST_DIFF',
COMPRESSION='GZ',
MEMSTORE_FLUSH_SIZE = '1073741824');
--- SQL operation complete.
*But, there is a weird thing, I used the 'varchar(255)' yesterday and
succeed in create table once, only once(it also get stuck when create the
table in the shell). After that I got the errors when using 'VARCHAR(255)'
to create table.*
SQL>showddl event;
CREATE TABLE TRAFODION.SEABASE.EVENT
(
* V_DATE TIMESTAMP(6) NO DEFAULT NOT NULL NOT*
* DROPPABLE NOT SERIALIZED*
* , SID VARCHAR(255 CHARS) CHARACTER SET UTF8*
* COLLATE DEFAULT DEFAULT _ISO88591'' NOT NULL NOT DROPPABLE
SERIALIZED*
* , UID VARCHAR(255 CHARS) CHARACTER SET UTF8*
* COLLATE DEFAULT DEFAULT _ISO88591'' NOT NULL NOT DROPPABLE
SERIALIZED*
* , VID INT UNSIGNED NO DEFAULT NOT NULL NOT*
* DROPPABLE SERIALIZED*
, CREATED_AT INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE SERIALIZED
, P_ID LARGEINT NO DEFAULT NOT NULL NOT
DROPPABLE
SERIALIZED
, CATEGORY VARCHAR(255 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
, E_ACTION VARCHAR(255 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
, LABEL VARCHAR(255 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
, E_VALUE INT NO DEFAULT NOT NULL NOT DROPPABLE
SERIALIZED
, IS_BOUNCED SMALLINT DEFAULT 0 NOT NULL NOT
DROPPABLE
SERIALIZED
, *PRIMARY KEY (SID ASC, V_DATE DESC, UID ASC, VID ASC)*
)
SALT USING 4 PARTITIONS
ON (SID, V_DATE, UID, VID)
DIVISION BY (DATE_TRUNC('DAY',V_DATE)
NAMED AS ("_DIVISION_1_"))
HBASE_OPTIONS
(
DATA_BLOCK_ENCODING = 'FAST_DIFF',
COMPRESSION = 'GZ',
MEMSTORE_FLUSH_SIZE = '1073741824'
)
;
--- SQL operation complete.
Hans Zeller <[email protected]>于2016年8月18日周四 上午1:34写道：
> Hi,
>
> Another thing you can do is to specify the limit in the VARCHAR in bytes,
> not in characters. If you can do that and if you normally store 1, 2 or 3
> byte characters in the key, that could be more efficient. When specifying
> the limit in characters, every UTF-8 character uses the maximum length of 4
> bytes in the key.
>
> For example, change
>
> VARCHAR(256) CHARACTER SET UTF8
>
>
> to
>
> VARCHAR(256 BYTES) CHARACTER SET UTF8 -- when storing ASCII data only
>
>
> or
>
> VARCHAR(768 BYTES) CHARACTER SET UTF8 -- to store up to 255 Chinese
> characters
>
>
> Oracle uses similar syntax.
>
> Thanks,
>
> Hans
>
> On Wed, Aug 17, 2016 at 7:28 AM, Eric Owhadi <[email protected]>
> wrote:
>
>> Hi Qiao and Ming,
>>
>> I am wondering if it is not related to the max size of keys in bytes. I
>> recall having seen it in the code, but would not bet that it was around 2K?
>>
>> Varchar in UTF8 stored as key is very wide because had to be stored
>> explode.
>>
>> When using columns in PK, VARCHAR is not a good choice, as the data will
>> always be present padded with blank up to the max size. So all classical
>> benefits of varchar (of not consuming space when string is small) is
>> actually not achieved when column is part of PK.
>>
>> Would you consider using CHAR instead of varchar? And would you consider
>> a different character set than UTF8 that is wide when stored exploded for
>> the columns used as PK?
>>
>>
>>
>> Also Qiao, if you are looking for performance, I don’t know if you are
>> aware of Trafodion’s Aligned Format feature, but with such a long PK, I
>> believe it will be of great help for your use case?
>>
>> Basically it allows storing the whole row as a single hbase cell, making
>> repetition of key for storing each column in single cell less of an issue.
>>
>>
>>
>>
>>
>> Hope this helps,
>>
>> Regards,
>> Eric
>>
>>
>>
>>
>>
>> *From:* Liu, Ming (Ming) [mailto:[email protected]]
>> *Sent:* Wednesday, August 17, 2016 9:13 AM
>> *To:* [email protected]
>> *Subject:* RE: create table failed
>>
>>
>>
>> Hi, Qiao,
>>
>>
>>
>> This is a defect, would you please help to file a JIRA?
>>
>>
>>
>> I can reproduce it, will make some investigation on this issue.
>>
>>
>>
>> One workaround for you is to change the DDL a little:
>>
>> uid VARCHAR(255) to VARCHAR(254)
>>
>>
>>
>> Hope it works for you.
>>
>>
>>
>> Thanks,
>>
>> Ming
>>
>>
>>
>> *From:* 乔彦克 [mailto:[email protected] <[email protected]>]
>> *Sent:* Wednesday, August 17, 2016 6:23 PM
>> *To:* [email protected]
>> *Subject:* create table failed
>>
>>
>>
>> Hi,all
>>
>> Now I've got new problems. Since I have date in my columns, I want to try
>> the division feature of trafodion.
>>
>> I use the bellow sql to create tables, but only get these errors
>>
>> "**** ERROR[29157] There was a problem reading from the server*
>>
>> **** ERROR[29160] The message header was not long enough*
>>
>> **** ERROR[29157] There was a problem reading from the server*
>>
>> **** ERROR[29160] The message header was not long enough*".
>>
>> can someone help me or show me error, many thanks.
>>
>>
>>
>> sql:
>>
>> "CREATE TABLE page (
>>
>> sid varchar(255) CHARACTER SET UTF8 NOT NULL DEFAULT '',
>>
>> v_date timestamp(6) NOT NULL,
>>
>> uid varchar(255) CHARACTER SET UTF8 NOT NULL,
>>
>> vid int unsigned NOT NULL,
>>
>> stime int unsigned NOT NULL,
>>
>> etime int unsigned NOT NULL,
>>
>> pid bigint NOT NULL,
>>
>> cnum int unsigned NOT NULL DEFAULT 0,
>>
>> enum int unsigned NOT NULL DEFAULT 0,
>>
>> primary key (sid,v_date desc,uid,vid)
>>
>> )
>>
>> salt using 4 partitions on (sid,v_date,uid,vid)
>>
>> division by (date_trunc('day', v_date))
>>
>> HBASE_OPTIONS( DATA_BLOCK_ENCODING = 'FAST_DIFF',
>>
>> COMPRESSION='GZ',
>>
>> MEMSTORE_FLUSH_SIZE = '1073741824');"
>>
>>
>>
>> Any reply is appreciated!
>>
>> Thank you.
>>
>> Qiao
>>
>
>
