what do you mean database table here? you have repeating column names for the table namely deptid
0: jdbc:hive2://rhes564:10010/default> SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptid = d.deptid; INFO : OK +-----------+---------+-----------+-----------+--------------+--+ | e.emp_id | e.name | e.deptid | d.deptid | d.dept_name | +-----------+---------+-----------+-----------+--------------+--+ | 1001 | aba | 10 | 10 | DEV | | 1002 | abs | 20 | 20 | TEST | | 1003 | abd | 10 | 10 | DEV | | 1001 | aba | 10 | 10 | DEV | | 1002 | abs | 20 | 20 | TEST | | 1003 | abd | 10 | 10 | DEV | | 1004 | abf | 30 | 30 | IT | | 1005 | abg | 10 | 10 | DEV | | 1004 | abf | 30 | 30 | IT | | 1005 | abg | 10 | 10 | DEV | | 1006 | abh | 20 | 20 | TEST | | 1007 | abj | 10 | 10 | DEV | | 1006 | abh | 20 | 20 | TEST | | 1007 | abj | 10 | 10 | DEV | | 1008 | abk | 30 | 30 | IT | | 1009 | abl | 20 | 20 | TEST | | 1010 | abq | 10 | 10 | DEV | | 1008 | abk | 30 | 30 | IT | | 1009 | abl | 20 | 20 | TEST | | 1010 | abq | 10 | 10 | DEV | +-----------+---------+-----------+-----------+--------------+--+ 20 rows selected (44.351 seconds) HTH Dr Mich Talebzadeh LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* http://talebzadehmich.wordpress.com *Disclaimer:* Use it at your own risk. Any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on this email's technical content is explicitly disclaimed. The author will in no case be liable for any monetary damages arising from such loss, damage or destruction. On 6 July 2016 at 17:48, radha <[email protected]> wrote: > Hi , > Thanks all, its working fine the issue is with some space for the dept id, > > I have one more doubt for the non matching records its showing null word, > even if i write into HDFS also its showing null word how can we avoid > writing null for the non matching columns, i want just empty value ("") > > same input i used in the dept table i removed the last row and the below > code i used to write into hdfs. > > DataFrame joinResult = sqlContext.sql("SELECT * FROM EMP e LEFT OUTER JOIN > DEPT d ON e.deptid = d.deptid"); > joinResult.javaRDD().repartition(1).map(new > Function<Row, String>() { > private static final long serialVersionUID = > 9185646063977504742L; > @Override > public String call(Row arg0) throws Exception { > String s; > > > s=arg0.getString(0)+"\u001c"+arg0.getString(1)+"\u001c"+arg0.getString(2)+"\u001c"+arg0.getString(3)+"\u001c"+arg0.getString(4)+"\u001e"; > return s; > } > }).saveAsTextFile(args[2]); > > > Output in HDFS File > > 10 1001 aba 10 dev > 10 1003 abd 10 dev > 10 1005 abg 10 dev > 10 1007 abj 10 dev > 10 1010 abq 10 dev > 20 1002 abs 20 Test > 20 1006 abh 20 Test > 20 1009 abl 20 Test > 30 1004 abf null null > 30 1008 abk null null > > in my case i want to store the join result back to data base table and its > storing "null" word for those non matching records, i want to store as > ""(empty value) for the non matching rows. > > > > -- > View this message in context: > http://apache-spark-user-list.1001560.n3.nabble.com/Spark-Left-outer-Join-issue-using-programmatic-sql-joins-tp27295p27299.html > Sent from the Apache Spark User List mailing list archive at Nabble.com. > > --------------------------------------------------------------------- > To unsubscribe e-mail: [email protected] > >
