hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Xuefu Zhang <xzh...@cloudera.com>
Subject Re: Union all with a field 'hard coded'
Date Fri, 20 Feb 2015 15:06:52 GMT
Hi Lefty,

The description seems good to me. I just slightly modified it so that it
sounds more "technical", for your consideration.

Thanks,
Xuefu

UNION ALL expected the same schema on both sides of the expression list. As
a result, the following query may fail with an error message such as
"FAILED: SemanticException 4:47 Schema of both sides of union should
match."
[query]
In such cases, column aliases can be used to force equal schema:
[corrected query]



On Thu, Feb 19, 2015 at 1:04 AM, Lefty Leverenz <leftyleverenz@gmail.com>
wrote:

> Xuefu, I've taken a stab at documenting this in the Union wikidoc
> <https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Union> (near
> the end).  Would you please review it and make any necessary corrections or
> additions?
>
> Thanks.
>
> -- Lefty
>
> On Mon, Feb 2, 2015 at 2:02 PM, DU DU <willddy@gmail.com> wrote:
>
>> 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