You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@spark.apache.org by pratik khadloya <ti...@gmail.com> on 2015/11/10 20:24:11 UTC
Querying nested struct fields
Hello,
I just saved a PairRDD as a table, but i am not able to query it correctly.
The below and other variations does not seem to work.
scala> hc.sql("select * from agg_imps_df").printSchema()
|-- _1: struct (nullable = true)
| |-- item_id: long (nullable = true)
| |-- flight_id: long (nullable = true)
|-- _2: struct (nullable = true)
| |-- day_hour: string (nullable = true)
| |-- imps: long (nullable = true)
| |-- revenue: double (nullable = true)
scala> hc.sql("select _1:item_id from agg_imps_df limit 10").collect()
Can anyone please suggest the correct way to get the list of item_ids in
the query?
Thanks,
~Pratik
Re: Querying nested struct fields
Posted by pratik khadloya <ti...@gmail.com>.
That worked!! Thanks a lot Michael.
~Pratik
On Tue, Nov 10, 2015 at 12:02 PM Michael Armbrust <mi...@databricks.com>
wrote:
> Oh sorry _1 is not a valid hive identifier, you need to use backticks to
> escape it:
>
> Seq(((1, 2), 2)).toDF().registerTempTable("test")
> sql("SELECT `_1`.`_1` FROM test")
>
> On Tue, Nov 10, 2015 at 11:31 AM, pratik khadloya <ti...@gmail.com>
> wrote:
>
>> I tried the same, didn't work :(
>>
>> scala> hc.sql("select _1.item_id from agg_imps_df limit 10").collect()
>> 15/11/10 14:30:41 INFO parse.ParseDriver: Parsing command: select
>> _1.item_id from agg_imps_df limit 10
>> org.apache.spark.sql.AnalysisException: missing \' at 'from' near
>> '<EOF>'; line 1 pos 23
>> at org.apache.spark.sql.hive.HiveQl$.createPlan(HiveQl.scala:289)
>> at
>> org.apache.spark.sql.hive.ExtendedHiveQlParser$$anonfun$hiveQl$1.apply(ExtendedHiveQlParser.scala:41)
>> at
>> org.apache.spark.sql.hive.ExtendedHiveQlParser$$anonfun$hiveQl$1.apply(ExtendedHiveQlParser.scala:40)
>> at
>> scala.util.parsing.combinator.Parsers$Success.map(Parsers.scala:136)
>> at
>> scala.util.parsing.combinator.Parsers$Success.map(Parsers.scala:135)
>> at
>> scala.util.parsing.combinator.Parsers$Parser$$anonfun$map$1.apply(Parsers.scala:242)
>> at
>> scala.util.parsing.combinator.Parsers$Parser$$anonfun$map$1.apply(Parsers.scala:242)
>> at
>> scala.util.parsing.combinator.Parsers$$anon$3.apply(Parsers.scala:222)
>>
>> On Tue, Nov 10, 2015 at 11:25 AM Michael Armbrust <mi...@databricks.com>
>> wrote:
>>
>>> Use a `.`:
>>>
>>> hc.sql("select _1.item_id from agg_imps_df limit 10").collect()
>>>
>>> On Tue, Nov 10, 2015 at 11:24 AM, pratik khadloya <ti...@gmail.com>
>>> wrote:
>>>
>>>> Hello,
>>>>
>>>> I just saved a PairRDD as a table, but i am not able to query it
>>>> correctly. The below and other variations does not seem to work.
>>>>
>>>> scala> hc.sql("select * from agg_imps_df").printSchema()
>>>> |-- _1: struct (nullable = true)
>>>> | |-- item_id: long (nullable = true)
>>>> | |-- flight_id: long (nullable = true)
>>>> |-- _2: struct (nullable = true)
>>>> | |-- day_hour: string (nullable = true)
>>>> | |-- imps: long (nullable = true)
>>>> | |-- revenue: double (nullable = true)
>>>>
>>>>
>>>> scala> hc.sql("select _1:item_id from agg_imps_df limit 10").collect()
>>>>
>>>>
>>>> Can anyone please suggest the correct way to get the list of item_ids
>>>> in the query?
>>>>
>>>> Thanks,
>>>> ~Pratik
>>>>
>>>
>>>
>
Re: Querying nested struct fields
Posted by Michael Armbrust <mi...@databricks.com>.
Oh sorry _1 is not a valid hive identifier, you need to use backticks to
escape it:
Seq(((1, 2), 2)).toDF().registerTempTable("test")
sql("SELECT `_1`.`_1` FROM test")
On Tue, Nov 10, 2015 at 11:31 AM, pratik khadloya <ti...@gmail.com>
wrote:
> I tried the same, didn't work :(
>
> scala> hc.sql("select _1.item_id from agg_imps_df limit 10").collect()
> 15/11/10 14:30:41 INFO parse.ParseDriver: Parsing command: select
> _1.item_id from agg_imps_df limit 10
> org.apache.spark.sql.AnalysisException: missing \' at 'from' near '<EOF>';
> line 1 pos 23
> at org.apache.spark.sql.hive.HiveQl$.createPlan(HiveQl.scala:289)
> at
> org.apache.spark.sql.hive.ExtendedHiveQlParser$$anonfun$hiveQl$1.apply(ExtendedHiveQlParser.scala:41)
> at
> org.apache.spark.sql.hive.ExtendedHiveQlParser$$anonfun$hiveQl$1.apply(ExtendedHiveQlParser.scala:40)
> at
> scala.util.parsing.combinator.Parsers$Success.map(Parsers.scala:136)
> at
> scala.util.parsing.combinator.Parsers$Success.map(Parsers.scala:135)
> at
> scala.util.parsing.combinator.Parsers$Parser$$anonfun$map$1.apply(Parsers.scala:242)
> at
> scala.util.parsing.combinator.Parsers$Parser$$anonfun$map$1.apply(Parsers.scala:242)
> at
> scala.util.parsing.combinator.Parsers$$anon$3.apply(Parsers.scala:222)
>
> On Tue, Nov 10, 2015 at 11:25 AM Michael Armbrust <mi...@databricks.com>
> wrote:
>
>> Use a `.`:
>>
>> hc.sql("select _1.item_id from agg_imps_df limit 10").collect()
>>
>> On Tue, Nov 10, 2015 at 11:24 AM, pratik khadloya <ti...@gmail.com>
>> wrote:
>>
>>> Hello,
>>>
>>> I just saved a PairRDD as a table, but i am not able to query it
>>> correctly. The below and other variations does not seem to work.
>>>
>>> scala> hc.sql("select * from agg_imps_df").printSchema()
>>> |-- _1: struct (nullable = true)
>>> | |-- item_id: long (nullable = true)
>>> | |-- flight_id: long (nullable = true)
>>> |-- _2: struct (nullable = true)
>>> | |-- day_hour: string (nullable = true)
>>> | |-- imps: long (nullable = true)
>>> | |-- revenue: double (nullable = true)
>>>
>>>
>>> scala> hc.sql("select _1:item_id from agg_imps_df limit 10").collect()
>>>
>>>
>>> Can anyone please suggest the correct way to get the list of item_ids in
>>> the query?
>>>
>>> Thanks,
>>> ~Pratik
>>>
>>
>>
Re: Querying nested struct fields
Posted by pratik khadloya <ti...@gmail.com>.
I tried the same, didn't work :(
scala> hc.sql("select _1.item_id from agg_imps_df limit 10").collect()
15/11/10 14:30:41 INFO parse.ParseDriver: Parsing command: select
_1.item_id from agg_imps_df limit 10
org.apache.spark.sql.AnalysisException: missing \' at 'from' near '<EOF>';
line 1 pos 23
at org.apache.spark.sql.hive.HiveQl$.createPlan(HiveQl.scala:289)
at
org.apache.spark.sql.hive.ExtendedHiveQlParser$$anonfun$hiveQl$1.apply(ExtendedHiveQlParser.scala:41)
at
org.apache.spark.sql.hive.ExtendedHiveQlParser$$anonfun$hiveQl$1.apply(ExtendedHiveQlParser.scala:40)
at
scala.util.parsing.combinator.Parsers$Success.map(Parsers.scala:136)
at
scala.util.parsing.combinator.Parsers$Success.map(Parsers.scala:135)
at
scala.util.parsing.combinator.Parsers$Parser$$anonfun$map$1.apply(Parsers.scala:242)
at
scala.util.parsing.combinator.Parsers$Parser$$anonfun$map$1.apply(Parsers.scala:242)
at
scala.util.parsing.combinator.Parsers$$anon$3.apply(Parsers.scala:222)
On Tue, Nov 10, 2015 at 11:25 AM Michael Armbrust <mi...@databricks.com>
wrote:
> Use a `.`:
>
> hc.sql("select _1.item_id from agg_imps_df limit 10").collect()
>
> On Tue, Nov 10, 2015 at 11:24 AM, pratik khadloya <ti...@gmail.com>
> wrote:
>
>> Hello,
>>
>> I just saved a PairRDD as a table, but i am not able to query it
>> correctly. The below and other variations does not seem to work.
>>
>> scala> hc.sql("select * from agg_imps_df").printSchema()
>> |-- _1: struct (nullable = true)
>> | |-- item_id: long (nullable = true)
>> | |-- flight_id: long (nullable = true)
>> |-- _2: struct (nullable = true)
>> | |-- day_hour: string (nullable = true)
>> | |-- imps: long (nullable = true)
>> | |-- revenue: double (nullable = true)
>>
>>
>> scala> hc.sql("select _1:item_id from agg_imps_df limit 10").collect()
>>
>>
>> Can anyone please suggest the correct way to get the list of item_ids in
>> the query?
>>
>> Thanks,
>> ~Pratik
>>
>
>
Re: Querying nested struct fields
Posted by Michael Armbrust <mi...@databricks.com>.
Use a `.`:
hc.sql("select _1.item_id from agg_imps_df limit 10").collect()
On Tue, Nov 10, 2015 at 11:24 AM, pratik khadloya <ti...@gmail.com>
wrote:
> Hello,
>
> I just saved a PairRDD as a table, but i am not able to query it
> correctly. The below and other variations does not seem to work.
>
> scala> hc.sql("select * from agg_imps_df").printSchema()
> |-- _1: struct (nullable = true)
> | |-- item_id: long (nullable = true)
> | |-- flight_id: long (nullable = true)
> |-- _2: struct (nullable = true)
> | |-- day_hour: string (nullable = true)
> | |-- imps: long (nullable = true)
> | |-- revenue: double (nullable = true)
>
>
> scala> hc.sql("select _1:item_id from agg_imps_df limit 10").collect()
>
>
> Can anyone please suggest the correct way to get the list of item_ids in
> the query?
>
> Thanks,
> ~Pratik
>