Return-Path: X-Original-To: apmail-hive-user-archive@www.apache.org Delivered-To: apmail-hive-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 1D19318DF6 for ; Fri, 15 Jan 2016 16:08:06 +0000 (UTC) Received: (qmail 97007 invoked by uid 500); 15 Jan 2016 16:08:04 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 96938 invoked by uid 500); 15 Jan 2016 16:08:04 -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 96928 invoked by uid 99); 15 Jan 2016 16:08:04 -0000 Received: from Unknown (HELO spamd2-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 15 Jan 2016 16:08:03 +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 8EC361A00CE for ; Fri, 15 Jan 2016 16:08:03 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd2-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 4.102 X-Spam-Level: **** X-Spam-Status: No, score=4.102 tagged_above=-999 required=6.31 tests=[HTML_MESSAGE=3, KAM_COUK=1.1, LOTS_OF_MONEY=0.001, URIBL_BLOCKED=0.001] autolearn=disabled Received: from mx1-us-west.apache.org ([10.40.0.8]) by localhost (spamd2-us-west.apache.org [10.40.0.9]) (amavisd-new, port 10024) with ESMTP id bGvu-XHLOwsz for ; Fri, 15 Jan 2016 16:07:51 +0000 (UTC) Received: from sulu.netzoomi.net (sulu.netzoomi.net [83.138.144.103]) by mx1-us-west.apache.org (ASF Mail Server at mx1-us-west.apache.org) with ESMTP id 46FA62051D for ; Fri, 15 Jan 2016 16:07:50 +0000 (UTC) Received: from vulcan.netzoomi.net (unknown [212.100.249.54]) by sulu.netzoomi.net (Postfix) with ESMTP id D99F76A4C36 for ; Fri, 15 Jan 2016 16:07:40 +0000 (GMT) X-Envelope-From: Received: from w7 (cpc86449-seve24-2-0-cust177.13-3.cable.virginm.net [86.19.59.178]) by vulcan.netzoomi.net (Postfix) with ESMTPA id A5C7812481B5 for ; Fri, 15 Jan 2016 16:07:40 +0000 (GMT) From: "Mich Talebzadeh" To: References: <004b01d14fa6$efe0af20$cfa20d60$@peridale.co.uk> In-Reply-To: <004b01d14fa6$efe0af20$cfa20d60$@peridale.co.uk> Subject: RE: Importing currency figures with currency indicator to Hive from CSV Date: Fri, 15 Jan 2016 16:07:43 -0000 Message-ID: <005b01d14fae$dddbac00$99930400$@peridale.co.uk> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_005C_01D14FAE.DDE066F0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQGfybGRMBe+uT+noMHBd5m1cDtXGZ9fyiSQ Content-Language: en-gb This is a multipart message in MIME format. ------=_NextPart_000_005C_01D14FAE.DDE066F0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi, =20 What is the equivalent of Oracle TO_NUMBER function in Hive please? =20 Thanks =20 Dr Mich Talebzadeh =20 LinkedIn = = https://www.linkedin.com/profile/view?id=3DAAEAAAAWh2gBxianrbJd6zP6AcPCCd= OABUrV8Pw =20 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-0919= 08.pdf Author of the books "A Practitioner=E2=80=99s Guide to Upgrading to = Sybase ASE 15", ISBN 978-0-9563693-0-7.=20 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 =20 = http://talebzadehmich.wordpress.com =20 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. =20 From: Mich Talebzadeh [mailto:mich@peridale.co.uk]=20 Sent: 15 January 2016 15:11 To: user@hive.apache.org Subject: Importing currency figures with currency indicator to Hive from = CSV =20 Hi, =20 =20 How to do convert a column stored as String in Hive into Decimal if = possible. =20 The excel looks like this =20 Invoice Number Payment date Net VAT Total 360 10/02/2014 =C2=A310,000.00 =C2=A32000.00 =C2=A312,000.00 =20 And the file (before bzip2) looks like this on imported directory in = Linux Invoice Number,Payment date,Net,VAT,Total 360,10/02/2014,"=E2=96=9212,000.00",=E2=96=922000.00,"=E2=96=9212,000.00"= =20 Once the data inserted from the external table into Hive (as string), = looks like below +-------------------+-----------------+--------------+-------------+-----= ---------+--+ | t2.invoicenumber | t2.paymentdate | t2.net | = t2.vat | t2.total | +-------------------+-----------------+--------------+-------------+-----= ---------+--+ | 360 | 10/02/2014 | ?10,000.00 | ?2000.00 | = ?12,000.00 | | Is there any way to resolve this column to decimal format? =20 Thanks =20 =20 Dr Mich Talebzadeh =20 LinkedIn = = https://www.linkedin.com/profile/view?id=3DAAEAAAAWh2gBxianrbJd6zP6AcPCCd= OABUrV8Pw =20 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-0919= 08.pdf Author of the books "A Practitioner=E2=80=99s Guide to Upgrading to = Sybase ASE 15", ISBN 978-0-9563693-0-7.=20 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 =20 = http://talebzadehmich.wordpress.com =20 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. =20 ------=_NextPart_000_005C_01D14FAE.DDE066F0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Hi,

 

What is = the equivalent of Oracle TO_NUMBER function in Hive = please?

 

Thanks

 

Dr = Mich Talebzadeh

=  

Linke= dIn =C2=A0https://www.linkedin.com/profile/view?id=3DAAEAAAAWh= 2gBxianrbJd6zP6AcPCCdOABUrV8Pw

 

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

Author of the books "A = Practitioner=E2=80=99s 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<= /o:p>

 

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.

 

From: Mich Talebzadeh = [mailto:mich@peridale.co.uk]
Sent: 15 January 2016 = 15:11
To: user@hive.apache.org
Subject: Importing = currency figures with currency indicator to Hive from = CSV

 

Hi,

 

 

How to = do convert a column stored as String in Hive into Decimal if = possible.

 

The excel looks like = this

 

Invoice = Number

Payment = date

Net

VAT

Total

360

10/02/2014

=C2=A310,000.00

=C2=A32000.00

=C2=A312,000.00

<= p class=3DMsoNormal = style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> 

And the file = (before bzip2) looks like this on imported directory in = Linux

Invoice Number,Payment = date,Net,VAT,Total

360,10/02/2014,"=E2=96=9212,000.00",=E2=96=922000.00,"=E2=96=9212,000.00"=

 

Once the data = inserted from the external table into Hive (as string), looks like = below

+-------------------+-----------------+--------------+--= -----------+--------------+--+

| t2.invoicenumber  = | t2.paymentdate  |    t2.net    |   = t2.vat    |   t2.total   = |

+-------------------+-----------------+--------------+--= -----------+--------------+--+

| = 360           &nbs= p;   | 10/02/2014      | = ?10,000.00  =  | = ?2000.00    | ?12,000.00 =   |

|

Is there any way to = resolve this column to decimal format?

 

Thanks

 

 

Dr = Mich Talebzadeh

=  

Linke= dIn  https://www.linkedin.com/profile/view?id=3DAAEAAAAWh= 2gBxianrbJd6zP6AcPCCdOABUrV8Pw

 

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

Author of the books "A = Practitioner=E2=80=99s 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<= /o:p>

 

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.

 

------=_NextPart_000_005C_01D14FAE.DDE066F0--