hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Joaquin Alzola <Joaquin.Alz...@lebara.com>
Subject RE: ORC and Table partition
Date Thu, 08 Dec 2016 15:17:40 GMT
Thanks Jan

insert into table ret_mms_cdrs_orc PARTITION (country='TALK',year='2016',month='12') select
* from ret_mms_cdrs where country='TALK' and year='2016' and month='12';

I was missing the PARTITION sentence.

From: Brotanek, Jan [mailto:Jan.Brotanek@adastragrp.com]
Sent: 08 December 2016 14:20
To: Joaquin Alzola <Joaquin.Alzola@lebara.com>
Subject: RE: ORC and Table partition

create partitioned ORC table first and then insert into it from text table

insert into table test.partitions PARTITION (part_col = 20161212)
select
a,
b
from test.source;
From: Joaquin Alzola [mailto:Joaquin.Alzola@lebara.com]
Sent: čtvrtek 8. prosince 2016 15:08
To: Brotanek, Jan <Jan.Brotanek@adastragrp.com<mailto:Jan.Brotanek@adastragrp.com>>
Subject: RE: ORC and Table partition

Sorry, by mistake I reply only to you.
Just send another email to the list.

From: Joaquin Alzola
Sent: 08 December 2016 14:07
To: 'Brotanek, Jan' <Jan.Brotanek@adastragrp.com<mailto:Jan.Brotanek@adastragrp.com>>
Subject: RE: ORC and Table partition

Asking because I have a partition but for textfile:
Table: RET_mms_cdrs
COMMENT 'Retail MMS CDRs'
PARTITIONED BY(country STRING, year STRING, month STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

And need to move it to an ORC stored file:
Table: RET_mms_cdrs_orc
COMMENT 'Retail MMS CDRs'
PARTITIONED BY(country STRING, year STRING, month STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
STORED AS ORC tblproperties ("orc.compress"="ZLIB");

But when doing:
INSERT INTO TABLE RET_mms_cdrs_orc SELECT * FROM RET_mms_cdrs

0: jdbc:hive2://localhost:10000> select count(*) from RET_mms_cdrs;
+-------+--+
|  _c0  |
+-------+--+
| 4554  |
+-------+--+

0: jdbc:hive2://localhost:10000> select count(*) from RET_mms_cdrs_orc;
+------+--+
| _c0  |
+------+--+
| 0    |
+------+--+

So it is not passing the info from one table to the other ORC table.
Cause I think this is the only way to add ORC files into Hive.

From: Brotanek, Jan [mailto:Jan.Brotanek@adastragrp.com]
Sent: 08 December 2016 13:51
To: Joaquin Alzola <Joaquin.Alzola@lebara.com<mailto:Joaquin.Alzola@lebara.com>>
Subject: RE: ORC and Table partition

Sure.

create table if not exists CEOSK.CEO_CUST_MKIB2
(
DAY STRING,
SITE DECIMAL(5,0),
VAL0 DECIMAL(13,2),
VAL1 DECIMAL(13,2),
VAL2 DECIMAL(13,2),
VAL3 DECIMAL(13,2),
VAL4 DECIMAL(13,2),
VAL5 DECIMAL(13,2),
VAL6 DECIMAL(13,2),
VAL7 DECIMAL(13,2),
VAL8 DECIMAL(13,2),
VAL9 DECIMAL(13,2)
)
PARTITIONED BY (part_col string)
STORED AS ORC;

zlib compression type is default

From: Joaquin Alzola [mailto:Joaquin.Alzola@lebara.com]
Sent: čtvrtek 8. prosince 2016 14:49
To: user@hive.apache.org<mailto:user@hive.apache.org>
Subject: ORC and Table partition

Hi Guys

Can the ORC files and the table partitions coexist on the same table?

Such as ....

)
COMMENT 'Retail MMS CDRs'
PARTITIONED BY(country STRING, year STRING, month STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
STORED AS ORC tblproperties ("orc.compress"="ZLIB");

BR

Joaquin
This email is confidential and may be subject to privilege. If you are not the intended recipient,
please do not copy or disclose its content but contact the sender immediately upon receipt.
This email is confidential and may be subject to privilege. If you are not the intended recipient,
please do not copy or disclose its content but contact the sender immediately upon receipt.
This email is confidential and may be subject to privilege. If you are not the intended recipient,
please do not copy or disclose its content but contact the sender immediately upon receipt.

Mime
View raw message