You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Joaquin Alzola <Jo...@lebara.com> on 2016/12/08 13:48:52 UTC
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.
RE: ORC and Table partition
Posted by Joaquin Alzola <Jo...@lebara.com>.
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 <Jo...@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<ma...@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.
RE: ORC and Table partition
Posted by Joaquin Alzola <Jo...@lebara.com>.
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 <Jo...@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 <Ja...@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' <Ja...@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 <Jo...@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<ma...@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.