Hm, these DDLs look quite normal. Forgot to ask what version of Spark
SQL are you using? Can you reproduce this issue with the master branch?
Also, a small sample of input data that can reproduce this issue can be
very helpful. For example, you can run SELECT * FROM tbl TABLESAMPLE (10
ROWS) against all tables involved in your query.
On 10/27/14 4:51 PM, lyf刘钰帆 wrote:
Yes. All data are located in the HDFS.
CREATE TABLE tblDate(
dateID string,
theyearmonth string,
theyear string,
themonth string,
thedate string,
theweek string,
theweeks string,
thequot string,
thetenday string,
thehalfmonth string
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' ;
CREATE TABLE tblA(
number STRING,
locationid STRING,
dateID string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' ;
CREATE TABLE tblB (
number STRING,
rownum int,
itemid STRING,
qty INT,
price int,
amount int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' ;
LOAD DATA LOCAL INPATH '/home/data/testFolder/qryTheDate.txt' INTO
TABLE tblDate;
LOAD DATA LOCAL INPATH '/home/data/testFolder/qrytblA.txt' INTO TABLE
tblA;
LOAD DATA LOCAL INPATH '/home/data/testFolder/qrytblB.txt' INTO TABLE
tblB;
*发件人:*Cheng Lian [mailto:[email protected]]
*发 送时间:*2014年10月27日16:48
*收件人:*lyf刘钰帆; [email protected]
*主题:*Re: SparkSQL display wrong result
Would you mind to share DDLs of all involved tables? What format are
these tables stored in? Is this issue specific to this query? I guess
Hive, Shark and Spark SQL all read from the same HDFS dataset?
On 10/27/14 3:45 PM, lyf刘钰帆wrote:
Hi,
I am using SparkSQL 1.1.0 with cdh 4.6.0 recently, however, the
SparkSQL will print wired results than Hive(shark)
The SQL:
select c.theyear, sum(b.amount) from tblA a join tblB b on
a.number = b.number join tbldate c on a.dateid = c.dateid group by
c.theyear order by theyear;
*where *
dateID string,
theyear string,
amount int
number STRING,
Result by Shark:
+----------+-----------+
| theyear | _c1 |
+----------+-----------+
| 2004 | *1403018* |
| 2005 | 5557850 |
| 2006 | 7203061 |
| 2007 | 11300432 |
| 2008 | 12109328 |
| 2009 | 5365447 |
| 2010 | 188944 |
+----------+-----------+
Result by Hive:
theyear _c1
2004 *1403018*
2005 5557850
2006 7203061
2007 11300432
2008 12109328
2009 5365447
2010 188944
Result by SparkSQL:
+----------+-----------+
| theyear | c_1 |
+----------+-----------+
| 2004 | *3265696* |
| 2005 | 13247234 |
| 2006 | 13670416 |
| 2007 | 16711974 |
| 2008 | 14670698 |
| 2009 | 6322137 |
| 2010 | 210924 |
+----------+-----------+
Best regards
Patrick Liu刘 钰帆| 1#3F122
