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 >
