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
