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 Sat, 21 Feb 2015 07:44:50 GMT
Great, thanks Xuefu.  So this only applies to UNION ALL, not UNION
DISTINCT?  I had wondered about that.

I made the changes and added some subheadings:  Union wikidoc
<https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Union>
-- Column
Aliases for UNION ALL
<https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Union#LanguageManualUnion-ColumnAliasesforUNIONALL>
.

Please review it one more time.

-- Lefty

On Fri, Feb 20, 2015 at 7:06 AM, Xuefu Zhang <xzhang@cloudera.com> wrote:

> 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