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 >> > >
