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.brota...@adastragrp.com] Sent: 08 December 2016 14:20 To: Joaquin Alzola <joaquin.alz...@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.alz...@lebara.com] Sent: čtvrtek 8. prosince 2016 15:08 To: Brotanek, Jan <jan.brota...@adastragrp.com<mailto:jan.brota...@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.brota...@adastragrp.com<mailto:jan.brota...@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.brota...@adastragrp.com] Sent: 08 December 2016 13:51 To: Joaquin Alzola <joaquin.alz...@lebara.com<mailto:joaquin.alz...@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.alz...@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.
