As you described, SparkSQL does not yet have windowing functions. However,
because we can combine RDD functions with SparkSQL we can achieve the same
thing.
What you essentially want to do is key by “imei” and use reduceByKey to select
the record you want for each key.
val keyedTable = table.keyBy(_(0)) // assuming “imei” is in position 0
val reduced = keyedTable.reduceByKey((a,b) => {
if (a.getInt(1) < b.getInt(1)) a else b
}).map(_._2)
val reducedTable = sqlCtx.applySchema(reduced, table.schema) // since reduced
is now just an RDD we need to reapply a
// schema to it, using the original we
started from
From: LinQili <[email protected]<mailto:[email protected]>>
Date: Monday, January 12, 2015 at 4:23 AM
To: "[email protected]<mailto:[email protected]>"
<[email protected]<mailto:[email protected]>>
Subject: how to select the first row in each group by group?
Hi all:
I am using spark sql to read and write hive tables. But There is a issue that
how to select the first row in each group by group?
In hive, we could write hql like this:
SELECT imei
FROM (
SELECT imei,
row_number() over (PARTITION BY imei ORDER BY login_time ASC) AS
row_num
FROM login_log_2015010914
) a
WHERE row_num = 1
In spark sql, how to write the sql equal to the hql?
