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