Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> '\N' is displayed in case of null column values in exporting hive query results to CSV file


Copy link to this message
-
Re: '\N' is displayed in case of null column values in exporting hive query results to CSV file
Hi Rinku,
That's the expected behaviour to differentiate between NULL, "NULL" (string) and "" (empty string).

If you would like something else to be serialized, you can use coalesce UDF (https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF)

Say, you wanted to serialize empty string for all nulls, your query would look something like

select
  coalesce(my_possibly_null_col, '') as my_possibly_null_col
from
  my_table
Mark

Mark Grover, Business Intelligence Analyst
OANDA Corporation

www: oanda.com www: fxtrade.com

----- Original Message -----
From: "Rinku Garg" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED], [EMAIL PROTECTED]
Sent: Wednesday, May 2, 2012 1:30:03 AM
Subject: RE: '\N' is displayed in case of null column values in exporting hive query results to CSV file
Hi Ashwanth,

Thanks for the quick reply. NULL column value is not located in last(in schema). We are getting ‘\N’ for every column which has a null values whether it is a first column, middle column or last column.

Thanks & Regards,

Rinku Garg
IT Specialist - Global Commercial Services
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Ashwanth Kumar
Sent: 02 May 2012 10:50
To: [EMAIL PROTECTED]
Subject: Re: '\N' is displayed in case of null column values in exporting hive query results to CSV file

Is the NULL value column located in last (in schema)?

On Wed, May 2, 2012 at 10:44 AM, Garg, Rinku < [EMAIL PROTECTED] > wrote:

Hi All,

We did a successful setup of hadoop-0.20.203.0 and hive-0.7.1. We also loaded a large number of CSV files into HDFS successfully. We can query through hive CLI. Now when we execute any query on the hive command prompt, we are getting result back.

While we did following steps to get back hive query results in CSV file, BUT if any column that contains ‘null’ in hive tables we are getting ‘\N’ in CSV file.

· set hive.exec.compress.output=false;

· create table tmp_test ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' as select * from table1;

· $HADOOP_HOME/bin/hadoop fs -cat /user/hive/warehouse/tmp_test/000000_0 > tmp_test.csv

We also tried creating UDFs eqivalent to nvl of oracle, but that also did not helped. Below is the URL that we followed for UDF

https://issues.apache.org/jira/browse/HIVE-2361?page=com.atlassian.jira.plugin.system.issuetabpanels:changehistory-tabpanel#issue-tabs

https://cwiki.apache.org/confluence/display/Hive/HivePlugins

Please suggest any pointers to resolve this ‘\N’ problem if case of empty column values.

Thanks & Regards,

Rinku Garg
IT Specialist - Global Commercial Services

Fidelity National Information Services (FIS)
#52, Industrial Area, Phase - II, Chandigarh - 160 002, India

US: +1 414 341 6464 Extn: 2516

Tel: +91 172 305 6619

Cell: +91 941 740 5922

Fax: +91 172 305 6603

[EMAIL PROTECTED]

www.fisglobal.com
_____________
The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
--
Ashwanth Kumar / ashwanthkumar.in
_____________
The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.