Hi Dave,
Your schema looks like this
NATION --< CUSTOMER --< ORDERS
I have a similar schema with the following relationship
COUNTRIES --< CUSTOMERS --< SALES
I tried this one and it works both in Hive 1.2.1 and Spark getting total sales
for each country
SELECT c.country_name AS nation,
sum (amount_sold) AS salesamount
FROM countries c
INNER JOIN customers cs
ON c.country_id = cs.country_id
INNER JOIN sales s
ON cs.cust_id = s.cust_id
GROUP BY country_name
ORDER BY country_name
It works OK
HTH
Dr Mich Talebzadeh
LinkedIn
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>
https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
Sybase ASE 15 Gold Medal Award 2008
A Winning Strategy: Running the most Critical Financial Data on ASE 15
<http://login.sybase.com/files/Product_Overviews/ASE-Winning-Strategy-091908.pdf>
http://login.sybase.com/files/Product_Overviews/ASE-Winning-Strategy-091908.pdf
Author of the books "A Practitioner’s Guide to Upgrading to Sybase ASE 15",
ISBN 978-0-9563693-0-7.
co-author "Sybase Transact SQL Guidelines Best Practices", ISBN
978-0-9759693-0-4
Publications due shortly:
Complex Event Processing in Heterogeneous Environments, ISBN: 978-0-9563693-3-8
Oracle and Sybase, Concepts and Contrasts, ISBN: 978-0-9563693-1-4, volume one
out shortly
<http://talebzadehmich.wordpress.com/> http://talebzadehmich.wordpress.com
NOTE: The information in this email is proprietary and confidential. This
message is for the designated recipient only, if you are not the intended
recipient, you should destroy it immediately. Any information in this message
shall not be understood as given or endorsed by Peridale Technology Ltd, its
subsidiaries or their employees, unless expressly so stated. It is the
responsibility of the recipient to ensure that this email is virus free,
therefore neither Peridale Technology Ltd, its subsidiaries nor their employees
accept any responsibility.
From: Dave Nicodemus [mailto:dave.nicode...@gmail.com]
Sent: 04 February 2016 19:29
To: user@hive.apache.org
Subject: NPE from simple nested ANSI Join
Using hive 1.2.1.2.3 Connecting using JDBC, issuing the following query :
SELECT COUNT(*)
FROM nation n
INNER JOIN (customer c
INNER JOIN orders o ON c.c_custkey = o.o_custkey)
ON n.n_nationkey = c.c_nationkey;
Generates the NPE and stack below. Fields are integer data type
Does anyone know if this is a known issue and whether it's fixed someplace ?
Thanks,
Dave
Stack
--------
Caused by: java.lang.NullPointerExcEeption: Remote
java.lang.NullPointerException: null
at
org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.isPresent(SemanticAnalyzer.java:2046)
at
org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.parseJoinCondPopulateAlias(SemanticAnalyzer.java:2109)
at
org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.parseJoinCondPopulateAlias(SemanticAnalyzer.java:2185)
at
org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.parseJoinCondition(SemanticAnalyzer.java:2445)
at
org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.parseJoinCondition(SemanticAnalyzer.java:2386)
at
org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genJoinTree(SemanticAnalyzer.java:8192)
at
org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genJoinTree(SemanticAnalyzer.java:8131)
at
org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9709)
at
org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9636)
at
org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genOPTree(SemanticAnalyzer.java:10109)
at
org.apache.hadoop.hive.ql.parse.CalcitePlanner.genOPTree(CalcitePlanner.java:329)
at
org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:10120)
at
org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:211)
at
org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:227)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:454)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:314)
at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1164)
at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1158)
at
org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:110)
