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" <rinku.g...@fisglobal.com> To: user@hive.apache.org, ashwanthku...@googlemail.com 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: ashwanth.ku...@gmail.com [mailto:ashwanth.ku...@gmail.com] On Behalf Of Ashwanth Kumar Sent: 02 May 2012 10:50 To: user@hive.apache.org 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 < rinku.g...@fisglobal.com > 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 rinku.g...@fisglobal.com 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.
