HiveSQL：查询结构数组(HiveSQL: Query an array of structs)

HiveSQL：查询结构数组(HiveSQL: Query an array of structs)

我有一个结构数组的表（源自Spark镶木地板文件）。 例如，表树看起来像这样：

| -- family_name : string | -- kids : array | -- element : struct | -- name : string | -- id : string | -- class_grades : struct | -- math : int | -- science : int

我想得到所有family_name ，其中至少有一个孩子的math成绩高于90.请注意，孩子的数量可能因家庭而异。 我怎样才能做到这一点？

I have a table with an array of structs (originates from Spark parquet file). As an example, The table tree looks like this:

| -- family_name : string | -- kids : array | -- element : struct | -- name : string | -- id : string | -- class_grades : struct | -- math : int | -- science : int

I'd like to get all the family_names where there's at least one kid with a math grade above 90. Note that the number of kids may vary between families. How can I do this?

最满意答案

想办法：

SELECT family_name, grades.math FROM (SELECT family_name, grades FROM table_name LATERAL VIEW OUTER explode(class_grades) c as grades) AS exploded_grades WHERE grades.math > 90

Figure it out:

SELECT family_name, grades.math FROM (SELECT family_name, grades FROM table_name LATERAL VIEW OUTER explode(class_grades) c as grades) AS exploded_grades WHERE grades.math > 90