Well a quick dirty way would be to use CASE WHEN with your SELECT statement.
Eg. create table tmp_test ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' as select [normalCols], CASE WHEN col = NULL THEN 'NULL' ELSE col END AS col, [otherCols] from table1; PS - Well this isn't a perfect way to do it, but if your number of columns are less and also you know which columns are likely to contain NULL values then you can use this. 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
