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
