hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Lefty Leverenz <leftylever...@gmail.com>
Subject Re: Union all with a field 'hard coded'
Date Mon, 02 Feb 2015 02:34:46 GMT
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 <pkernevez@octo.com>
wrote:

> Perfect.
>
> Thank you Xuefu.
>
> Philippe
>
> On Fri, Jan 30, 2015 at 11:32 PM, Xuefu Zhang <xzhang@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 <pkernevez@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),
> pkernevez@octo.com
> +41 79 888 33 32
>
> Retrouvez OCTO sur OCTO Talk : http://blog.octo.com
> OCTO Technology http://www.octo.com
>

Mime
View raw message