hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mich Talebzadeh" <m...@peridale.co.uk>
Subject ORC table stats from orcfiledump
Date Wed, 20 Jan 2016 18:32:48 GMT
Hi,

 

I did some tests on ORC table by creating a simple ORC table as below

 

CREATE TABLE orctest

(

  PROD_ID        bigint                       ,

  CUST_ID        bigint                       ,

  TIME_ID        timestamp                    ,

  CHANNEL_ID     bigint                       ,

  PROMO_ID       bigint                       ,

  QUANTITY_SOLD  decimal(10)                  ,

  AMOUNT_SOLD    decimal(10)

)

CLUSTERED BY (PROD_ID) INTO 256 BUCKETS

STORED AS ORC

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

TBLPROPERTIES (

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

"orc.stripe.size"="16777216",

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

;

show create table orctest;

insert into orctest

select * from smallsales limit 5000000

;

analyze table orctest compute statistics;

 

 

 

I deliberately set the stripe size to be 16MB and row index size to be
10,000

 

Table is created with 5Million rows as below

 

NFO  : Table oraclehadoop.orctest stats: [numFiles=1, numRows=5000000,
totalSize=32140689, rawDataSize=1480000000]

 

 

Now if I dump the content of ORC file I see as below

 

Questions I have:

 

1.    ORC will use be default ZLIB. That means that oRC file will use some
fo

2. I specified the stripe size of 16MB. It has created 3 stripes of 172,0000
+ 172,0000 + 156,000 rows. Does it decide on the number of stripes based on
the total table size which seems to be 30MB (File length: 32140689 bytes)

3.    There is no mention of statistics for row index in batch of 10,000? Is
this correct

4. Finally under Stripes:what are those statistics? Are they offset values? 

 

 

 

hive --orcfiledump  /user/hive/warehouse/oraclehadoop.db/orctest/000000_0

 

 

Compression: ZLIB

Compression size: 262144

Type:
struct<_col0:bigint,_col1:bigint,_col2:timestamp,_col3:bigint,_col4:bigint,_
col5:decimal(10,0),_col6:decimal(10,0)>

 

Stripe Statistics:

  Stripe 1:

    Column 0: count: 1720000 hasNull: false

    Column 1: count: 1720000 hasNull: false min: 13 max: 148 sum: 134770570

    Column 2: count: 1720000 hasNull: false min: 2 max: 101000 sum:
12541768461

    Column 3: count: 1720000 hasNull: false min: 883612800000 max:
1009756800000

    Column 4: count: 1720000 hasNull: false min: 2 max: 9 sum: 4923112

    Column 5: count: 1720000 hasNull: false min: 33 max: 999 sum: 1678552095

    Column 6: count: 1720000 hasNull: false min: 1 max: 1 sum: 1720000

    Column 7: count: 1720000 hasNull: false min: 6 max: 1783 sum: 184490864

  Stripe 2:

    Column 0: count: 1720000 hasNull: false

    Column 1: count: 1720000 hasNull: false min: 13 max: 148 sum: 134793536

    Column 2: count: 1720000 hasNull: false min: 2 max: 101000 sum:
12545167155

    Column 3: count: 1720000 hasNull: false min: 883612800000 max:
1009756800000

    Column 4: count: 1720000 hasNull: false min: 2 max: 9 sum: 4924236

    Column 5: count: 1720000 hasNull: false min: 33 max: 999 sum: 1678720214

    Column 6: count: 1720000 hasNull: false min: 1 max: 1 sum: 1720000

    Column 7: count: 1720000 hasNull: false min: 6 max: 1783 sum: 184666196

  Stripe 3:

    Column 0: count: 1560000 hasNull: false

    Column 1: count: 1560000 hasNull: false min: 13 max: 148 sum: 122265414

    Column 2: count: 1560000 hasNull: false min: 2 max: 101000 sum:
11367296072

    Column 3: count: 1560000 hasNull: false min: 883612800000 max:
1009756800000

    Column 4: count: 1560000 hasNull: false min: 2 max: 9 sum: 4465701

    Column 5: count: 1560000 hasNull: false min: 33 max: 999 sum: 1522502365

    Column 6: count: 1560000 hasNull: false min: 1 max: 1 sum: 1560000

    Column 7: count: 1560000 hasNull: false min: 6 max: 1783 sum: 167524289

 

File Statistics:

  Column 0: count: 5000000 hasNull: false

  Column 1: count: 5000000 hasNull: false min: 13 max: 148 sum: 391829520

  Column 2: count: 5000000 hasNull: false min: 2 max: 101000 sum:
36454231688

  Column 3: count: 5000000 hasNull: false min: 883612800000 max:
1009756800000

  Column 4: count: 5000000 hasNull: false min: 2 max: 9 sum: 14313049

  Column 5: count: 5000000 hasNull: false min: 33 max: 999 sum: 4879774674

  Column 6: count: 5000000 hasNull: false min: 1 max: 1 sum: 5000000

  Column 7: count: 5000000 hasNull: false min: 6 max: 1783 sum: 536681349

 

Stripes:

  Stripe: offset: 3 data: 11039881 rows: 1720000 tail: 137 index: 14370

    Stream: column 0 section ROW_INDEX start: 3 length 30

    Stream: column 1 section ROW_INDEX start: 33 length 1853

    Stream: column 2 section ROW_INDEX start: 1886 length 2415

    Stream: column 3 section ROW_INDEX start: 4301 length 2284

    Stream: column 4 section ROW_INDEX start: 6585 length 1451

    Stream: column 5 section ROW_INDEX start: 8036 length 1674

    Stream: column 6 section ROW_INDEX start: 9710 length 1714

    Stream: column 7 section ROW_INDEX start: 11424 length 2949

    Stream: column 1 section DATA start: 14373 length 1797330

    Stream: column 2 section DATA start: 1811703 length 3280925

    Stream: column 3 section DATA start: 5092628 length 3592057

    Stream: column 3 section SECONDARY start: 8684685 length 1797

    Stream: column 4 section DATA start: 8686482 length 590101

    Stream: column 5 section DATA start: 9276583 length 124066

    Stream: column 6 section DATA start: 9400649 length 7653

    Stream: column 6 section SECONDARY start: 9408302 length 1797

    Stream: column 7 section DATA start: 9410099 length 1642358

    Stream: column 7 section SECONDARY start: 11052457 length 1797

    Encoding column 0: DIRECT

    Encoding column 1: DIRECT_V2

    Encoding column 2: DIRECT_V2

    Encoding column 3: DIRECT_V2

    Encoding column 4: DIRECT_V2

    Encoding column 5: DIRECT_V2

    Encoding column 6: DIRECT_V2

    Encoding column 7: DIRECT_V2

  Stripe: offset: 11054391 data: 11041929 rows: 1720000 tail: 137 index:
14292

    Stream: column 0 section ROW_INDEX start: 11054391 length 30

    Stream: column 1 section ROW_INDEX start: 11054421 length 1858

    Stream: column 2 section ROW_INDEX start: 11056279 length 2398

    Stream: column 3 section ROW_INDEX start: 11058677 length 2242

    Stream: column 4 section ROW_INDEX start: 11060919 length 1443

    Stream: column 5 section ROW_INDEX start: 11062362 length 1669

    Stream: column 6 section ROW_INDEX start: 11064031 length 1714

    Stream: column 7 section ROW_INDEX start: 11065745 length 2938

    Stream: column 1 section DATA start: 11068683 length 1797175

    Stream: column 2 section DATA start: 12865858 length 3283080

    Stream: column 3 section DATA start: 16148938 length 3591894

    Stream: column 3 section SECONDARY start: 19740832 length 1797

    Stream: column 4 section DATA start: 19742629 length 590201

    Stream: column 5 section DATA start: 20332830 length 123640

    Stream: column 6 section DATA start: 20456470 length 7653

    Stream: column 6 section SECONDARY start: 20464123 length 1797

    Stream: column 7 section DATA start: 20465920 length 1642895

    Stream: column 7 section SECONDARY start: 22108815 length 1797

    Encoding column 0: DIRECT

    Encoding column 1: DIRECT_V2

    Encoding column 2: DIRECT_V2

    Encoding column 3: DIRECT_V2

    Encoding column 4: DIRECT_V2

    Encoding column 5: DIRECT_V2

    Encoding column 6: DIRECT_V2

    Encoding column 7: DIRECT_V2

  Stripe: offset: 22110749 data: 10016151 rows: 1560000 tail: 138 index:
13096

    Stream: column 0 section ROW_INDEX start: 22110749 length 30

    Stream: column 1 section ROW_INDEX start: 22110779 length 1704

    Stream: column 2 section ROW_INDEX start: 22112483 length 2201

    Stream: column 3 section ROW_INDEX start: 22114684 length 2051

    Stream: column 4 section ROW_INDEX start: 22116735 length 1333

    Stream: column 5 section ROW_INDEX start: 22118068 length 1534

    Stream: column 6 section ROW_INDEX start: 22119602 length 1552

    Stream: column 7 section ROW_INDEX start: 22121154 length 2691

    Stream: column 1 section DATA start: 22123845 length 1628303

    Stream: column 2 section DATA start: 23752148 length 2980652

    Stream: column 3 section DATA start: 26732800 length 3257688

    Stream: column 3 section SECONDARY start: 29990488 length 1633

    Stream: column 4 section DATA start: 29992121 length 535364

    Stream: column 5 section DATA start: 30527485 length 112530

    Stream: column 6 section DATA start: 30640015 length 6934

    Stream: column 6 section SECONDARY start: 30646949 length 1633

    Stream: column 7 section DATA start: 30648582 length 1489781

    Stream: column 7 section SECONDARY start: 32138363 length 1633

    Encoding column 0: DIRECT

    Encoding column 1: DIRECT_V2

    Encoding column 2: DIRECT_V2

    Encoding column 3: DIRECT_V2

    Encoding column 4: DIRECT_V2

    Encoding column 5: DIRECT_V2

    Encoding column 6: DIRECT_V2

    Encoding column 7: DIRECT_V2

 

File length: 32140689 bytes

Padding length: 0 bytes

Padding ratio: 0%

 

 

 

Dr Mich Talebzadeh

 

LinkedIn
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABU
rV8Pw>
https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUr
V8Pw

 

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>
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 Technology Ltd, its subsidiaries nor their
employees accept any responsibility.

 


Mime
View raw message