Tech writing assumes true unless proven false. :) On Sat, Feb 21, 2015 at 1:13 PM, Lefty Leverenz <[email protected]> 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 <[email protected]> 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 <[email protected] >> > 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 <[email protected]> >>> 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 < >>>> [email protected]> 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 <[email protected]> wrote: >>>>> >>>>>> This is a part of standard SQL syntax, isn't it? >>>>>> >>>>>> On Mon, Feb 2, 2015 at 2:22 PM, Xuefu Zhang <[email protected]> >>>>>> 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 < >>>>>>> [email protected]> 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 < >>>>>>>> [email protected]> wrote: >>>>>>>> >>>>>>>>> Perfect. >>>>>>>>> >>>>>>>>> Thank you Xuefu. >>>>>>>>> >>>>>>>>> Philippe >>>>>>>>> >>>>>>>>> On Fri, Jan 30, 2015 at 11:32 PM, Xuefu Zhang <[email protected] >>>>>>>>> > 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 < >>>>>>>>>> [email protected]> 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), >>>>>>>>> [email protected] >>>>>>>>> +41 79 888 33 32 >>>>>>>>> >>>>>>>>> Retrouvez OCTO sur OCTO Talk : http://blog.octo.com >>>>>>>>> OCTO Technology http://www.octo.com >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> Thanks, >>>>>> Dayong >>>>>> >>>>> >>>>> >>>> >>> >> >
