Great, thanks Xuefu. So this only applies to UNION ALL, not UNION DISTINCT? I had wondered about that.
I made the changes and added some subheadings: Union wikidoc <https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Union> -- Column Aliases for UNION ALL <https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Union#LanguageManualUnion-ColumnAliasesforUNIONALL> . Please review it one more time. -- Lefty On Fri, Feb 20, 2015 at 7:06 AM, Xuefu Zhang <xzh...@cloudera.com> wrote: > Hi Lefty, > > The description seems good to me. I just slightly modified it so that it > sounds more "technical", for your consideration. > > Thanks, > Xuefu > > UNION ALL expected the same schema on both sides of the expression list. > As a result, the following query may fail with an error message such as > "FAILED: SemanticException 4:47 Schema of both sides of union should > match." > [query] > In such cases, column aliases can be used to force equal schema: > [corrected query] > > > > On Thu, Feb 19, 2015 at 1:04 AM, Lefty Leverenz <leftylever...@gmail.com> > wrote: > >> Xuefu, I've taken a stab at documenting this in the Union wikidoc >> <https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Union> (near >> the end). Would you please review it and make any necessary corrections or >> additions? >> >> Thanks. >> >> -- Lefty >> >> On Mon, Feb 2, 2015 at 2:02 PM, DU DU <will...@gmail.com> wrote: >> >>> This is a part of standard SQL syntax, isn't it? >>> >>> On Mon, Feb 2, 2015 at 2:22 PM, Xuefu Zhang <xzh...@cloudera.com> wrote: >>> >>>> Yes, I think it would be great if this can be documented. >>>> >>>> --Xuefu >>>> >>>> On Sun, Feb 1, 2015 at 6:34 PM, Lefty Leverenz <leftylever...@gmail.com >>>> > wrote: >>>> >>>>> Xuefu, should this be documented in the Union wikidoc >>>>> <https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Union> >>>>> ? >>>>> >>>>> Is it relevant for other query clauses? >>>>> >>>>> -- Lefty >>>>> >>>>> On Sun, Feb 1, 2015 at 11:27 AM, Philippe Kernévez <pkerne...@octo.com >>>>> > wrote: >>>>> >>>>>> Perfect. >>>>>> >>>>>> Thank you Xuefu. >>>>>> >>>>>> Philippe >>>>>> >>>>>> On Fri, Jan 30, 2015 at 11:32 PM, Xuefu Zhang <xzh...@cloudera.com> >>>>>> wrote: >>>>>> >>>>>>> Use column alias: >>>>>>> >>>>>>> INSERT OVERWRITE TABLE all_dictionaries_ext >>>>>>> SELECT name, id, category FROM dictionary >>>>>>> UNION ALL SELECT NAME, ID, "CAMPAIGN" as category FROM >>>>>>> md_campaigns >>>>>>> >>>>>>> >>>>>>> On Fri, Jan 30, 2015 at 1:41 PM, Philippe Kernévez < >>>>>>> pkerne...@octo.com> wrote: >>>>>>> >>>>>>>> Hi all, >>>>>>>> >>>>>>>> I would like to do union all with a field that is hardcoded in the >>>>>>>> request. >>>>>>>> >>>>>>>> INSERT OVERWRITE TABLE all_dictionaries_ext >>>>>>>> SELECT name, id, category FROM dictionary >>>>>>>> UNION ALL SELECT NAME, ID, "CAMPAIGN" FROM md_campaigns >>>>>>>> >>>>>>>> Name type is String >>>>>>>> Id type is int >>>>>>>> Category type is string >>>>>>>> >>>>>>>> When I run this command I had an error : >>>>>>>> FAILED: SemanticException 4:47 Schema of both sides of union should >>>>>>>> match. _u1-subquery2 does not have the field category. Error >>>>>>>> encountered >>>>>>>> near token 'md_campaigns' >>>>>>>> >>>>>>>> I supposed that the error is cause by the String "CAMPAIGN" which >>>>>>>> should not have a type. >>>>>>>> >>>>>>>> How can do this kind of union ? >>>>>>>> >>>>>>>> The union all with 2 hard coded fields is ok. >>>>>>>> INSERT OVERWRITE TABLE all_dictionaries_ext >>>>>>>> SELECT NAME, ID, "CAMPAIGN" FROM md_campaigns >>>>>>>> UNION ALL SELECT NAME, ID, "AD_SERVER" FROM md_ad_servers >>>>>>>> UNION ALL SELECT NAME, ID, "AVERTISER" FROM md_advertisers >>>>>>>> UNION ALL SELECT NAME, ID, "AGENCIES" FROM md_agencies >>>>>>>> >>>>>>>> >>>>>>>> More debug info : >>>>>>>> >>>>>>>> 15/01/30 22:34:23 [main]: INFO parse.ParseDriver: Parsing command: >>>>>>>> INSERT OVERWRITE TABLE all_dictionaries_ext >>>>>>>> SELECT name, id, category FROM byoa_dictionary >>>>>>>> UNION ALL SELECT NAME, ID, "CAMPAIGN" FROM md_campaigns >>>>>>>> 15/01/30 22:34:23 [main]: INFO parse.ParseDriver: Parse Completed >>>>>>>> 15/01/30 22:34:23 [main]: INFO log.PerfLogger: </PERFLOG >>>>>>>> method=parse start=1422653663887 end=1422653663900 duration=13 >>>>>>>> from=org.apache.hadoop.hive.ql.Driver> >>>>>>>> 15/01/30 22:34:23 [main]: INFO log.PerfLogger: <PERFLOG >>>>>>>> method=semanticAnalyze from=org.apache.hadoop.hive.ql.Driver> >>>>>>>> 15/01/30 22:34:23 [main]: INFO parse.SemanticAnalyzer: Starting >>>>>>>> Semantic Analysis >>>>>>>> 15/01/30 22:34:23 [main]: INFO parse.SemanticAnalyzer: Completed >>>>>>>> phase 1 of Semantic Analysis >>>>>>>> 15/01/30 22:34:23 [main]: INFO parse.SemanticAnalyzer: Get metadata >>>>>>>> for source tables >>>>>>>> 15/01/30 22:34:23 [main]: INFO parse.SemanticAnalyzer: Get metadata >>>>>>>> for subqueries >>>>>>>> 15/01/30 22:34:23 [main]: INFO parse.SemanticAnalyzer: Get metadata >>>>>>>> for source tables >>>>>>>> 15/01/30 22:34:23 [main]: INFO parse.SemanticAnalyzer: Get metadata >>>>>>>> for subqueries >>>>>>>> 15/01/30 22:34:24 [main]: INFO parse.SemanticAnalyzer: Get metadata >>>>>>>> for destination tables >>>>>>>> 15/01/30 22:34:24 [main]: INFO parse.SemanticAnalyzer: Get metadata >>>>>>>> for source tables >>>>>>>> 15/01/30 22:34:24 [main]: INFO parse.SemanticAnalyzer: Get metadata >>>>>>>> for subqueries >>>>>>>> 15/01/30 22:34:24 [main]: INFO parse.SemanticAnalyzer: Get metadata >>>>>>>> for destination tables >>>>>>>> 15/01/30 22:34:24 [main]: INFO parse.SemanticAnalyzer: Get metadata >>>>>>>> for destination tables >>>>>>>> 15/01/30 22:34:24 [main]: INFO parse.SemanticAnalyzer: Completed >>>>>>>> getting MetaData in Semantic Analysis >>>>>>>> 15/01/30 22:34:24 [main]: INFO parse.SemanticAnalyzer: Not invoking >>>>>>>> CBO because the statement has too few joins >>>>>>>> FAILED: SemanticException 4:47 Schema of both sides of union should >>>>>>>> match. _u1-subquery2 does not have the field category. Error >>>>>>>> encountered >>>>>>>> near token 'md_campaigns' >>>>>>>> 15/01/30 22:34:24 [main]: ERROR ql.Driver: FAILED: >>>>>>>> SemanticException 4:47 Schema of both sides of union should match. >>>>>>>> _u1-subquery2 does not have the field category. Error encountered near >>>>>>>> token 'md_campaigns' >>>>>>>> org.apache.hadoop.hive.ql.parse.SemanticException: 4:47 Schema of >>>>>>>> both sides of union should match. _u1-subquery2 does not have the field >>>>>>>> category. Error encountered near token 'md_campaigns' >>>>>>>> at >>>>>>>> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genUnionPlan(SemanticAnalyzer.java:9007) >>>>>>>> at >>>>>>>> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9600) >>>>>>>> at >>>>>>>> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9620) >>>>>>>> at >>>>>>>> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9607) >>>>>>>> at >>>>>>>> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:10093) >>>>>>>> at >>>>>>>> org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:221) >>>>>>>> at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:415) >>>>>>>> at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:303) >>>>>>>> at >>>>>>>> org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1067) >>>>>>>> at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1129) >>>>>>>> at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1004) >>>>>>>> at org.apache.hadoop.hive.ql.Driver.run(Driver.java:994) >>>>>>>> at >>>>>>>> org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:247) >>>>>>>> at >>>>>>>> org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:199) >>>>>>>> at >>>>>>>> org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:410) >>>>>>>> at >>>>>>>> org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:345) >>>>>>>> at >>>>>>>> org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:733) >>>>>>>> at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:677) >>>>>>>> at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:616) >>>>>>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) >>>>>>>> at >>>>>>>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) >>>>>>>> at >>>>>>>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) >>>>>>>> at java.lang.reflect.Method.invoke(Method.java:597) >>>>>>>> at org.apache.hadoop.util.RunJar.run(RunJar.java:221) >>>>>>>> at org.apache.hadoop.util.RunJar.main(RunJar.java:136) >>>>>>>> >>>>>>>> Regards, >>>>>>>> -- >>>>>>>> Philippe Kernévez >>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> Philippe Kernévez >>>>>> >>>>>> >>>>>> >>>>>> Directeur technique (Suisse), >>>>>> pkerne...@octo.com >>>>>> +41 79 888 33 32 >>>>>> >>>>>> Retrouvez OCTO sur OCTO Talk : http://blog.octo.com >>>>>> OCTO Technology http://www.octo.com >>>>>> >>>>> >>>>> >>>> >>> >>> >>> -- >>> Thanks, >>> Dayong >>> >> >> >
