hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From DU DU <will...@gmail.com>
Subject Re: Union all with a field 'hard coded'
Date Mon, 02 Feb 2015 22:02:46 GMT
This is a part of standard SQL syntax, isn't it?

On Mon, Feb 2, 2015 at 2:22 PM, Xuefu Zhang <xzhang@cloudera.com> wrote:

> Yes, I think it would be great if this can be documented.
>
> --Xuefu
>
> On Sun, Feb 1, 2015 at 6:34 PM, Lefty Leverenz <leftyleverenz@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 <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
>>>
>>
>>
>


-- 
Thanks,
Dayong

Mime
View raw message