hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mich Talebzadeh" <m...@peridale.co.uk>
Subject RE: create table as ORC with SORTED BY fails
Date Tue, 03 Nov 2015 21:24:54 GMT
Yes thanks 

 

Something like below works with the selectivity of object_id column --> (select count(distinct(object_id))/count(object_id)
) = 1 

 

create table t (

owner                   varchar(30)

,object_name             varchar(30)

,subobject_name          varchar(30)

,object_id               bigint

,data_object_id          bigint

,object_type             varchar(19)

,created                 timestamp

,last_ddl_time           timestamp

,timestamp               varchar(19)

,status                  varchar(7)

,temporary2              varchar(1)

,generated               varchar(1)

,secondary               varchar(1)

,namespace               bigint

,edition_name            varchar(30)

,padding1                varchar(4000)

,padding2                varchar(3500)

,attribute               varchar(32)

,op_type                 int

,op_time                 timestamp

)

CLUSTERED BY (object_id) INTO 256 BUCKETS

STORED AS ORC

TBLPROPERTIES ( "orc.compress"="SNAPPY",

"orc.create.index"="true",

"orc.bloom.filter.columns"="object_id",

"orc.bloom.filter.fpp"="0.05",

"orc.stripe.size"="268435456",

"orc.row.index.stride"="10000" )

;

 

Mich Talebzadeh

 

Sybase ASE 15 Gold Medal Award 2008

A Winning Strategy: Running the most Critical Financial Data on ASE 15

http://login.sybase.com/files/Product_Overviews/ASE-Winning-Strategy-091908.pdf

Author of the books "A Practitioner’s Guide to Upgrading to Sybase ASE 15", ISBN 978-0-9563693-0-7.


co-author "Sybase Transact SQL Guidelines Best Practices", ISBN 978-0-9759693-0-4

Publications due shortly:

Complex Event Processing in Heterogeneous Environments, ISBN: 978-0-9563693-3-8

Oracle and Sybase, Concepts and Contrasts, ISBN: 978-0-9563693-1-4, volume one out shortly

 

http://talebzadehmich.wordpress.com <http://talebzadehmich.wordpress.com/> 

 

NOTE: The information in this email is proprietary and confidential. This message is for the
designated recipient only, if you are not the intended recipient, you should destroy it immediately.
Any information in this message shall not be understood as given or endorsed by Peridale Technology
Ltd, its subsidiaries or their employees, unless expressly so stated. It is the responsibility
of the recipient to ensure that this email is virus free, therefore neither Peridale Ltd,
its subsidiaries nor their employees accept any responsibility.

 

From: Prasanth Jayachandran [mailto:pjayachandran@hortonworks.com] 
Sent: 03 November 2015 21:15
To: user@hive.apache.org
Subject: Re: create table as ORC with SORTED BY fails

 

Yes. SORTED BY can only be used with CLUSTERED BY .. into ’n’ buckets 

 

If you want to insert data in a sorted order on a specific column, you can add ORDER BY/SORT
BY to the select query for the column that you want to sort. ORDER BY will use single reducer
but you will get global ordering where SORT BY is local ordering and can use multiple reducers.

 

Thanks

Prasanth

On Nov 3, 2015, at 1:54 PM, Sergey Shelukhin <sergey@hortonworks.com <mailto:sergey@hortonworks.com>
> wrote:

 

IIRC sorted by is only supported on bucketed tables.

 

From: Mich Talebzadeh < <mailto:mich@peridale.co.uk> mich@peridale.co.uk>
Reply-To: " <mailto:user@hive.apache.org> user@hive.apache.org" < <mailto:user@hive.apache.org>
user@hive.apache.org>
Date: Tuesday, November 3, 2015 at 01:23
To: " <mailto:user@hive.apache.org> user@hive.apache.org" < <mailto:user@hive.apache.org>
user@hive.apache.org>
Subject: create table as ORC with SORTED BY fails

 

Hi,

 

Any idea why this simple create table fails?

 

hive> create table test (

    >  owner                   varchar(30)

    > ,object_name             varchar(30)

    > ,object_id               bigint

    > )

    > SORTED BY (object_id)

    > STORED AS ORC

    >  TBLPROPERTIES ("orc.compress"="SNAPPY"

    > ,"orc.create,index"="true");

FAILED: ParseException line 6:0 missing EOF at 'SORTED' near ')'

 

If I remove SORTED BY clause it works!

 

Thanks

 

http://talebzadehmich.wordpress.com <http://talebzadehmich.wordpress.com/> 

 

NOTE: The information in this email is proprietary and confidential. This message is for the
designated recipient only, if you are not the intended recipient, you should destroy it immediately.
Any information in this message shall not be understood as given or endorsed by Peridale Technology
Ltd, its subsidiaries or their employees, unless expressly so stated. It is the responsibility
of the recipient to ensure that this email is virus free, therefore neither Peridale Ltd,
its subsidiaries nor their employees accept any responsibility.

 


Mime
View raw message