spark sql LATERAL VIEW
- User
spark sql LATERAL VIEW
- Assistant
LATERAL VIEW is a feature in Spark SQL that allows users to apply a table-generating function (TGF) to each row of a table, which produces zero or more output rows for each input row. The resulting output rows are then joined with the original input rows to produce a final output table.
The syntax for LATERAL VIEW is as follows:
SELECT ... FROM ... LATERAL VIEW tgf(...) aliasTable AS col1, col2, ...The
tgf(...)is the table-generating function that takes one or more input columns and produces a new table as output. ThealiasTableis a user-defined alias for the output table. Thecol1, col2, ...are the columns to be selected from the output table.For example, consider the following table:
+---+------+ |id |names | +---+------+ |1 |John | |2 |Alice | +---+------+Suppose we have a UDF that takes a name and returns a list of nicknames for that name:
def getNicknames(name: String): Seq[String] = ...We can use LATERAL VIEW to apply this UDF to each row of the input table:
SELECT id, nickname FROM myTable LATERAL VIEW explode(getNicknames(names)) nicknameTable AS nicknameThe
explodefunction is used here to flatten the output ofgetNicknamesfunction into multiple rows. The resulting output table would be:+---+---------+ |id |nickname | +---+---------+ |1 |Johnny | |1 |Johnboy | |2 |Alicey | |2 |Alicia | +---+---------+Where the UDF
getNicknamesreturns multiple nicknames for each input name, and the LATERAL VIEW function applies the UDF to each row of the input table.