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 Sun, 22 Feb 2015 00:09:20 GMT
Tech writing assumes true unless proven false. :)

On Sat, Feb 21, 2015 at 1:13 PM, Lefty Leverenz <leftyleverenz@gmail.com>
wrote:

> I haven't tried union distinct, but I assume the same rule applies.
>>
>
> Reasonable assumption, so I'll remove "ALL" and see if anyone contradicts
> it.  (Tech writing by successive approximation.)
>
> Thanks again.
>
> -- Lefty
>
> On Sat, Feb 21, 2015 at 6:27 AM, Xuefu Zhang <xzhang@cloudera.com> wrote:
>
>> I haven't tried union distinct, but I assume the same rule applies.
>>
>> Thanks for putting it together. It looks good to me.
>>
>> --Xuefu
>>
>> On Fri, Feb 20, 2015 at 11:44 PM, Lefty Leverenz <leftyleverenz@gmail.com
>> > wrote:
>>
>>> 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