You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@trafodion.apache.org by 乔彦克 <qy...@gmail.com> on 2016/08/17 10:22:40 UTC
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
Re: create table failed
Posted by 乔彦克 <qy...@gmail.com>.
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 <ha...@esgyn.com>于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 <er...@esgyn.com>
> 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:ming.liu@esgyn.cn]
>> *Sent:* Wednesday, August 17, 2016 9:13 AM
>> *To:* user@trafodion.incubator.apache.org
>> *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:qyanke@gmail.com <qy...@gmail.com>]
>> *Sent:* Wednesday, August 17, 2016 6:23 PM
>> *To:* user@trafodion.incubator.apache.org
>> *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
>>
>
>
Re: create table failed
Posted by Hans Zeller <ha...@esgyn.com>.
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 <er...@esgyn.com> 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:ming.liu@esgyn.cn]
> *Sent:* Wednesday, August 17, 2016 9:13 AM
> *To:* user@trafodion.incubator.apache.org
> *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:qyanke@gmail.com <qy...@gmail.com>]
> *Sent:* Wednesday, August 17, 2016 6:23 PM
> *To:* user@trafodion.incubator.apache.org
> *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
>
RE: create table failed
Posted by Eric Owhadi <er...@esgyn.com>.
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:ming.liu@esgyn.cn]
*Sent:* Wednesday, August 17, 2016 9:13 AM
*To:* user@trafodion.incubator.apache.org
*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:qyanke@gmail.com <qy...@gmail.com>]
*Sent:* Wednesday, August 17, 2016 6:23 PM
*To:* user@trafodion.incubator.apache.org
*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
RE: create table failed
Posted by "Liu, Ming (Ming)" <mi...@esgyn.cn>.
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:qyanke@gmail.com]
Sent: Wednesday, August 17, 2016 6:23 PM
To: user@trafodion.incubator.apache.org
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