How to "expand" multi-value fields in JSON using SQL in Java (possibly with LATERAL VIEW)?

Question

I'm trying to transform my JSON input from

col1 | col2
row1   [value1, value2]
row2   [value3, value2]

to

col1 | col2
row1   value1
row1   value2
row2   value3
row2   value2

Apparently LATERAL VIEW is the way to go, but I can't seem to get it right.

I've been trying the following:

df= sqlContext.sql(
        "SELECT col1, col2" +
        "FROM temptable LATERAL VIEW col2 AS col2"
);

(Note: the variable sqlContext is actually a HiveContext.)

But this gives me org.apache.spark.sql.AnalysisException: missing EOF at 'LATERAL' near 'temptable ';. What does it mean by End of File?

How can I achieve this in JAVA?

Answer 1

You forgot a space between col2 and FROM in your query. The way you wrote it, the query is then SELECT col1, col2FROM temptable LATERAL VIEW col2 AS col2 but you should write

df= sqlContext.sql(
    "SELECT col1, col2 FROM temptable LATERAL VIEW col2 AS col2"
);