Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 6512C200BD5 for ; Thu, 8 Dec 2016 16:18:13 +0100 (CET) Received: by cust-asf.ponee.io (Postfix) id 62F76160B1F; Thu, 8 Dec 2016 15:18:13 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 0DBB0160B1E for ; Thu, 8 Dec 2016 16:18:11 +0100 (CET) Received: (qmail 77924 invoked by uid 500); 8 Dec 2016 15:18:10 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 77914 invoked by uid 99); 8 Dec 2016 15:18:10 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd2-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 08 Dec 2016 15:18:10 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd2-us-west.apache.org (ASF Mail Server at spamd2-us-west.apache.org) with ESMTP id 4F7921A94DF for ; Thu, 8 Dec 2016 15:18:10 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd2-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 1.998 X-Spam-Level: * X-Spam-Status: No, score=1.998 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, HTML_MESSAGE=2, RCVD_IN_DNSWL_NONE=-0.0001, RCVD_IN_MSPIKE_H2=-0.001, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001, URIBL_BLOCKED=0.001] autolearn=disabled Authentication-Results: spamd2-us-west.apache.org (amavisd-new); dkim=pass (1024-bit key) header.d=lebara.onmicrosoft.com Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd2-us-west.apache.org [10.40.0.9]) (amavisd-new, port 10024) with ESMTP id TGmuF9HbDRBI for ; Thu, 8 Dec 2016 15:18:07 +0000 (UTC) Received: from EUR01-HE1-obe.outbound.protection.outlook.com (mail-he1eur01on0127.outbound.protection.outlook.com [104.47.0.127]) by mx1-lw-us.apache.org (ASF Mail Server at mx1-lw-us.apache.org) with ESMTPS id 2036D5F29A for ; Thu, 8 Dec 2016 15:18:06 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=lebara.onmicrosoft.com; s=selector1-lebara-com; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=KR6HkHfzCsjSDGk7s2Qh+JHjsKXFcIoC/Hqn57nstVQ=; b=Df1a7/xEn66lEe/vlFZY66OeM9UjMlyGMcVLTCdU4boAWF8gG65uuTDQ0dt1UPyJlsA5tyetsWVmWHHtkVOsfPZ2/LL1lHKJmUaT/lTuf0T81fusBZmUvHy5fUiVbvl/CH/tCNaI8sXmO18x4QM9T/iFZJRQe8H0R9mixfuoSIA= Received: from DB5PR07MB0806.eurprd07.prod.outlook.com (10.161.196.12) by DB5PR07MB0808.eurprd07.prod.outlook.com (10.161.196.139) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.771.4; Thu, 8 Dec 2016 15:17:40 +0000 Received: from DB5PR07MB0806.eurprd07.prod.outlook.com ([10.161.196.12]) by DB5PR07MB0806.eurprd07.prod.outlook.com ([10.161.196.12]) with mapi id 15.01.0771.008; Thu, 8 Dec 2016 15:17:40 +0000 From: Joaquin Alzola To: "Brotanek, Jan" , "user@hive.apache.org" Subject: RE: ORC and Table partition Thread-Topic: ORC and Table partition Thread-Index: AdJRWbApnV8iCWn1RAOlWue+VlVZfAAAGODwAABbfXAAADngAAAAW4ZAAAIS9dA= Date: Thu, 8 Dec 2016 15:17:40 +0000 Message-ID: References: <3b2e1ebb61b04bff941ee514bc74e332@Marika.adastracorp.net> In-Reply-To: <3b2e1ebb61b04bff941ee514bc74e332@Marika.adastracorp.net> Accept-Language: en-GB, en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: spf=none (sender IP is ) smtp.mailfrom=Joaquin.Alzola@lebara.com; x-originating-ip: [165.225.80.96] x-ms-office365-filtering-correlation-id: 61587b5c-5a4f-47bb-1eae-08d41f7d59f0 x-microsoft-antispam: UriScan:;BCL:0;PCL:0;RULEID:(22001);SRVR:DB5PR07MB0808; x-microsoft-exchange-diagnostics: 1;DB5PR07MB0808;7:tgR+EEw7H3P9Y3RULypI7bSFjB3KT3sTsjLidxCR8qzSmxMIloXQjFyPer3vOtP3erD59j9B4Q8FYlJdgoKf7YkZNkkGlR4MSRviKSx9C+IKycps6jRJ0QhsPN0tdR0wBGRhDRUXDCmzjJqJifhFbxNkwtOqj/fkxlQMC1f9Un1kzr6OgPkXc8Og6eRj8EuyTTxHocnfnSPFhJGdX2x2LpTab+IljLUyTvcGLwKTMH5PJ+06qqa21vLsMPeIU4/nrkfSnWhyH+uN+RDKS34vl4VssbY/VRtQTL2pZFG6h4UiIqBURsx1a8wkX7pB1HXCyhUVDL35YeFyrtImmbWEPr4kHF3SLfmKSrQQ3g/IGqSVXlefD2T6M1kOpUGT6KsPjma7dzHE+zAEVLMGeUnvMZy9tYOhXQqwgBOdms2jNAL0bqLxww7uUKICbTu64aVT8aQuB0h4aq0TTsqEE7ILrg== x-microsoft-antispam-prvs: x-exchange-antispam-report-test: UriScan:(265634631926514)(192196412842102)(21748063052155); x-exchange-antispam-report-cfa-test: BCL:0;PCL:0;RULEID:(6040375)(601004)(2401047)(5005006)(8121501046)(3002001)(10201501046)(6041248)(20161123560025)(20161123562025)(20161123564025)(20161123555025)(20161123558021)(6072148);SRVR:DB5PR07MB0808;BCL:0;PCL:0;RULEID:;SRVR:DB5PR07MB0808; x-forefront-prvs: 0150F3F97D x-forefront-antispam-report: SFV:NSPM;SFS:(10019020)(7916002)(39840400002)(39410400002)(39450400003)(199003)(189002)(68736007)(2950100002)(2900100001)(3480700004)(6506006)(2501003)(8936002)(122556002)(81156014)(3660700001)(7696004)(7846002)(2906002)(5660300001)(3900700001)(7736002)(33656002)(9686002)(81166006)(92566002)(86362001)(8676002)(107886002)(101416001)(66066001)(790700001)(93886004)(54356999)(76176999)(50986999)(106356001)(3846002)(105586002)(606004)(102836003)(77096006)(3280700002)(38730400001)(74316002)(5001770100001)(97736004)(76576001)(189998001)(6116002)(229853002);DIR:OUT;SFP:1102;SCL:1;SRVR:DB5PR07MB0808;H:DB5PR07MB0806.eurprd07.prod.outlook.com;FPR:;SPF:None;PTR:InfoNoRecords;A:1;MX:1;LANG:en; received-spf: None (protection.outlook.com: lebara.com does not designate permitted sender hosts) spamdiagnosticoutput: 1:99 spamdiagnosticmetadata: NSPM Content-Type: multipart/alternative; boundary="_000_DB5PR07MB080656CCCF00168F0768AA1AF0840DB5PR07MB0806eurp_" MIME-Version: 1.0 X-OriginatorOrg: lebara.com X-MS-Exchange-CrossTenant-originalarrivaltime: 08 Dec 2016 15:17:40.5133 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: d7093539-83cd-4991-b1b3-aacef74cf097 X-MS-Exchange-Transport-CrossTenantHeadersStamped: DB5PR07MB0808 archived-at: Thu, 08 Dec 2016 15:18:13 -0000 --_000_DB5PR07MB080656CCCF00168F0768AA1AF0840DB5PR07MB0806eurp_ Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable Thanks Jan insert into table ret_mms_cdrs_orc PARTITION (country=3D'TALK',year=3D'2016= ',month=3D'12') select * from ret_mms_cdrs where country=3D'TALK' and year= =3D'2016' and month=3D'12'; I was missing the PARTITION sentence. From: Brotanek, Jan [mailto:Jan.Brotanek@adastragrp.com] Sent: 08 December 2016 14:20 To: Joaquin Alzola 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 =3D 20161212) select a, b from test.source; From: Joaquin Alzola [mailto:Joaquin.Alzola@lebara.com] Sent: =E8tvrtek 8. prosince 2016 15:08 To: Brotanek, Jan > 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' > 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"=3D"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 > 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: =E8tvrtek 8. prosince 2016 14:49 To: 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"=3D"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 cont= act 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 cont= act 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 cont= act the sender immediately upon receipt. --_000_DB5PR07MB080656CCCF00168F0768AA1AF0840DB5PR07MB0806eurp_ Content-Type: text/html; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable

Thanks Ja= n

&nbs= p;

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

&nbs= p;

I was mis= sing the PARTITION sentence.

&nbs= p;

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 i= nto it from text table

 

insert into tab= le test.partitions PARTITION (part_col =3D 20161212)<= o:p>

select

a,

b

from test.sou= rce;

From: Joaquin Alzola [ma= ilto:Joaquin.Alzola@lebara.com]
Sent: =E8tvrtek 8. prosince 2016 15:08
To: Brotanek, Jan <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>
Subject: RE: ORC and Table partition

 

Asking because I have a partition but for textfile:<= o:p>

Table: RET_mms_cdrs

COMMENT 'Retail MMS CDRs'

PARTITIONED BY(country STRING, year STRING, month ST= RING)

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'<= /o:p>

PARTITIONED BY(country STRING, year STRING, month ST= RING)

ROW FORMAT DELIMITED=

FIELDS TERMINATED BY '|'

LINES TERMINATED BY '\n'

STORED AS ORC tblproperties ("orc.compress"= ;=3D"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 [m= ailto:Jan.Brotanek@adastragrp.com]
Sent: 08 December 2016 13:51
To: Joaquin Alzola <= 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<= /span>,2),

VAL1 DECIMAL(13<= /span>,2),

VAL2 DECIMAL(13<= /span>,2),

VAL3 DECIMAL(13<= /span>,2),

VAL4 DECIMAL(13<= /span>,2),

VAL5 DECIMAL(13<= /span>,2),

VAL6 DECIMAL(13<= /span>,2),

VAL7 DECIMAL(13<= /span>,2),

VAL8 DECIMAL(13<= /span>,2),

VAL9 DECIMAL(13<= /span>,2)

)=

PARTITIONED BY (part_col string)

STORED AS ORC;

 

zlib compression type is default<= /span>

 

From: Joaquin Alzola [ma= ilto:Joaquin.Alzola@lebara.com]
Sent: =E8tvrtek 8. prosince 2016 14:49
To: user@hive.apache.org=
Subject: ORC and Table partition

 

Hi Guys

 

Can the ORC files and the table partitions coexist o= n the same table?

 

Such as ….

 

)

COMMENT 'Retail MMS CDRs'<= /o:p>

PARTITIONED BY(country STRING, year STRING, month ST= RING)

ROW FORMAT DELIMITED=

FIELDS TERMINATED BY '|'

LINES TERMINATED BY '\n'

STORED AS ORC tblproperties ("orc.compress"= ;=3D"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 di= sclose 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 di= sclose 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 cont= act the sender immediately upon receipt. --_000_DB5PR07MB080656CCCF00168F0768AA1AF0840DB5PR07MB0806eurp_--