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 >
