Hi Lahiru, In this instance no two tables comprise of identical rows. Therefore the query doesn't execute combines. I presume that union would be more efficient than join in such a scenario
Thank you! On Tue, Mar 8, 2016 at 1:43 PM, Lahiru Cooray <[email protected]> wrote: > Hi Charini, > In addition, if you could get the same resultset using sql joins (instead > of unions) you can increase the performance when querying. > When using union's, it takes a considerable effort when combining the > distinct results. > > On Tue, Mar 8, 2016 at 1:23 PM, Charini Nanayakkara <[email protected]> > wrote: > >> Hi Niranda, >> Thanks a lot! this worked >> >> On Tue, Mar 8, 2016 at 12:50 PM, Niranda Perera <[email protected]> wrote: >> >>> Hi Charini, >>> >>> there is a problem with the query here. use the following query >>> >>> INSERT OVERWRITE TABLE All_three >>> >>> select * from ( >>> SELECT SYMBOL, VOLUME FROM First >>> UNION >>> SELECT SYMBOL, VOLUME FROM Middle >>> UNION >>> SELECT SYMBOL, VOLUME FROM Third >>> >>> ) temp; >>> >>> Essentially what we do here is, wrapping the union result into one >>> temporary data element named 'temp' and select everything from there. >>> >>> SparkSQL parser only takes one single select element in the insert >>> queries, and at the end of a select query it expects a limit (if >>> available). therefore, you need to wrap the subsequent select statements >>> into one select element. >>> >>> Hope this resolves your issue >>> >>> Best >>> >>> On Tue, Mar 8, 2016 at 8:47 AM, Charini Nanayakkara <[email protected]> >>> wrote: >>> >>>> Hi, >>>> The following query was attempted to be executed when performing batch >>>> analytics with WSO2 DAS using Spark SQL. Tables 'First', 'Middle' and >>>> 'Third' are required to be combined and written to table 'All_three'. >>>> >>>> INSERT OVERWRITE TABLE All_three SELECT SYMBOL, VOLUME FROM First UNION >>>> SELECT SYMBOL, VOLUME FROM Middle UNION SELECT SYMBOL, VOLUME FROM Third; >>>> >>>> >>>> Following error is displayed on WSO2 DAS when this query is executed: >>>> >>>> ERROR: [1.79] failure: ``limit'' expected but `union' found INSERT >>>> OVERWRITE TABLE X1234_All_three SELECT SYMBOL, VOLUME FROM X1234_First >>>> UNION SELECT SYMBOL, VOLUME FROM X1234_Middle UNION SELECT SYMBOL, VOLUME >>>> FROM X1234_Third ^ >>>> >>>> >>>> Using LIMIT with UNION is not a necessity to the best of my knowledge. >>>> Enclosing the SELECT queries in parentheses too was attempted which didn't >>>> work. What am I doing wrong here? Thank you in advance! >>>> >>>> >>>> >>>> >>>> >>>> -- >>>> Charini Vimansha Nanayakkara >>>> Software Engineer at WSO2 >>>> Mobile: 0714126293 >>>> >>>> >>>> _______________________________________________ >>>> Dev mailing list >>>> [email protected] >>>> http://wso2.org/cgi-bin/mailman/listinfo/dev >>>> >>>> >>> >>> >>> -- >>> *Niranda Perera* >>> Software Engineer, WSO2 Inc. >>> Mobile: +94-71-554-8430 >>> Twitter: @n1r44 <https://twitter.com/N1R44> >>> https://pythagoreanscript.wordpress.com/ >>> >> >> >> >> -- >> Charini Vimansha Nanayakkara >> Software Engineer at WSO2 >> Mobile: 0714126293 >> >> >> _______________________________________________ >> Dev mailing list >> [email protected] >> http://wso2.org/cgi-bin/mailman/listinfo/dev >> >> > > > -- > *Lahiru Cooray* > Software Engineer > WSO2, Inc.;http://wso2.com/ > lean.enterprise.middleware > > Mobile: +94 715 654154 > -- Charini Vimansha Nanayakkara Software Engineer at WSO2 Mobile: 0714126293
_______________________________________________ Dev mailing list [email protected] http://wso2.org/cgi-bin/mailman/listinfo/dev
