Tech writing assumes true unless proven false. :)
On Sat, Feb 21, 2015 at 1:13 PM, Lefty Leverenz
<leftylever...@gmail.com <mailto:leftylever...@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 <xzh...@cloudera.com
<mailto:xzh...@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
<leftylever...@gmail.com <mailto:leftylever...@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
<xzh...@cloudera.com <mailto:xzh...@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
<leftylever...@gmail.com
<mailto:leftylever...@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
<will...@gmail.com <mailto:will...@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
<xzh...@cloudera.com
<mailto:xzh...@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 <leftylever...@gmail.com
<mailto:leftylever...@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 <pkerne...@octo.com
<mailto:pkerne...@octo.com>> wrote:
Perfect.
Thank you Xuefu.
Philippe
On Fri, Jan 30, 2015 at 11:32 PM,
Xuefu Zhang <xzh...@cloudera.com
<mailto:xzh...@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
<pkerne...@octo.com
<mailto:pkerne...@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),
pkerne...@octo.com
<mailto:pkerne...@octo.com>
+41 79 888 33 32
<tel:%2B41%2079%20888%2033%2032>
Retrouvez OCTO sur OCTO Talk :
http://blog.octo.com
OCTO Technology http://www.octo.com
--
Thanks,
Dayong
