You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@spark.apache.org by Martin Gammelsæter <ma...@gmail.com> on 2014/07/04 10:59:32 UTC
Spark SQL user defined functions
Hi!
I have a Spark cluster running on top of a Cassandra cluster, using
Datastax' new driver, and one of the fields of my RDDs is an
XML-string. In a normal Scala sparkjob, parsing that data is no
problem, but I would like to also make that information available
through Spark SQL. So, is there any way to write user defined
functions for Spark SQL? I know that a HiveContext is available, but I
understand that that is for querying data from Hive, and I don't have
Hive in my stack (please correct me if I'm wrong).
I would love to be able to do something like the following:
val casRdd = sparkCtx.cassandraTable("ks", "cf")
// registerAsTable etc
val res = sql("SELECT id, xmlGetTag(xmlfield, 'sometag') FROM cf")
--
Best regards,
Martin Gammelsæter
Re: Spark SQL user defined functions
Posted by Michael Armbrust <mi...@databricks.com>.
The names of the directories that are created for the metastore are
different ("metastore" vs "metastore_db"), but that should be it. Really
we should get rid of LocalHiveContext as it is mostly redundant and the
current state is kind of confusing. I've created a JIRA to figure this out
before the 1.1 release.
On Mon, Jul 7, 2014 at 12:25 AM, Martin Gammelsæter <
martingammelsaeter@gmail.com> wrote:
> Hi again, and thanks for your reply!
>
> On Fri, Jul 4, 2014 at 8:45 PM, Michael Armbrust <mi...@databricks.com>
> wrote:
> >
> >> Sweet. Any idea about when this will be merged into master?
> >
> >
> > It is probably going to be a couple of weeks. There is a fair amount of
> > cleanup that needs to be done. It works though and we used it in most of
> > the demos at the spark summit. Mostly I just need to add tests and move
> it
> > out of HiveContext (there is no good reason for that code to depend on
> > HiveContext). So you could also just try working with that branch.
> >
> >>
> >> This is probably a stupid question, but can you query Spark SQL tables
> >> from a (local?) hive context? In which case using that could be a
> >> workaround until the PR is merged.
> >
> >
> > Yeah, this is kind of subtle. In a HiveContext, SQL Tables are just an
> > additional catalog that sits on top of the metastore. All the query
> > execution occurs in the same code path, including the use of the Hive
> > Function Registry, independent of where the table comes from. So for
> your
> > use case you can just create a hive context, which will create a local
> > metastore automatically if no hive-site.xml is present.
>
> Nice, that sounds like it'll solve my problems. Just for clarity, is
> LocalHiveContext and HiveContext equal if no hive-site.xml is present,
> or are there still differences?
>
> --
> Best regards,
> Martin Gammelsæter
>
Re: Spark SQL user defined functions
Posted by Martin Gammelsæter <ma...@gmail.com>.
Hi again, and thanks for your reply!
On Fri, Jul 4, 2014 at 8:45 PM, Michael Armbrust <mi...@databricks.com> wrote:
>
>> Sweet. Any idea about when this will be merged into master?
>
>
> It is probably going to be a couple of weeks. There is a fair amount of
> cleanup that needs to be done. It works though and we used it in most of
> the demos at the spark summit. Mostly I just need to add tests and move it
> out of HiveContext (there is no good reason for that code to depend on
> HiveContext). So you could also just try working with that branch.
>
>>
>> This is probably a stupid question, but can you query Spark SQL tables
>> from a (local?) hive context? In which case using that could be a
>> workaround until the PR is merged.
>
>
> Yeah, this is kind of subtle. In a HiveContext, SQL Tables are just an
> additional catalog that sits on top of the metastore. All the query
> execution occurs in the same code path, including the use of the Hive
> Function Registry, independent of where the table comes from. So for your
> use case you can just create a hive context, which will create a local
> metastore automatically if no hive-site.xml is present.
Nice, that sounds like it'll solve my problems. Just for clarity, is
LocalHiveContext and HiveContext equal if no hive-site.xml is present,
or are there still differences?
--
Best regards,
Martin Gammelsæter
Re: Spark SQL user defined functions
Posted by Michael Armbrust <mi...@databricks.com>.
> Sweet. Any idea about when this will be merged into master?
>
It is probably going to be a couple of weeks. There is a fair amount of
cleanup that needs to be done. It works though and we used it in most of
the demos at the spark summit. Mostly I just need to add tests and move it
out of HiveContext (there is no good reason for that code to depend on
HiveContext). So you could also just try working with that branch.
> This is probably a stupid question, but can you query Spark SQL tables
> from a (local?) hive context? In which case using that could be a
> workaround until the PR is merged.
Yeah, this is kind of subtle. In a HiveContext, SQL Tables are just an
additional catalog that sits on top of the metastore. All the query
execution occurs in the same code path, including the use of the Hive
Function Registry, independent of where the table comes from. So for your
use case you can just create a hive context, which will create a local
metastore automatically if no hive-site.xml is present.
Re: Spark SQL user defined functions
Posted by Martin Gammelsæter <ma...@gmail.com>.
On Fri, Jul 4, 2014 at 11:39 AM, Michael Armbrust
<mi...@databricks.com> wrote:
> On Fri, Jul 4, 2014 at 1:59 AM, Martin Gammelsæter
> <ma...@gmail.com> wrote:
>>
>> is there any way to write user defined functions for Spark SQL?
> This is coming in Spark 1.1. There is a work in progress PR here:
> https://github.com/apache/spark/pull/1063
Sweet. Any idea about when this will be merged into master?
> If you have a hive context, you can also create udfs using the standard Hive
> APIs and use them in your hive ql queries.
This is probably a stupid question, but can you query Spark SQL tables
from a (local?) hive context? In which case using that could be a
workaround until the PR is merged.
--
Best regards,
Martin Gammelsæter
Re: Spark SQL user defined functions
Posted by Michael Armbrust <mi...@databricks.com>.
On Fri, Jul 4, 2014 at 1:59 AM, Martin Gammelsæter <
martingammelsaeter@gmail.com> wrote:
> is there any way to write user defined functions for Spark SQL?
This is coming in Spark 1.1. There is a work in progress PR here:
https://github.com/apache/spark/pull/1063
If you have a hive context, you can also create udfs using the standard
Hive APIs and use them in your hive ql queries.
Michael
Re: Spark SQL user defined functions
Posted by Takuya UESHIN <ue...@happy-camper.st>.
Ah, sorry for misreading.
I don't think there is a way to use UDF in your SQLs only with SparkSQL.
You might be able to use with SparkHive, but I'm sorry, I don't know well.
I think you should use the function before convert to SchemaRDD if you can.
Thanks.
2014-07-04 18:16 GMT+09:00 Martin Gammelsæter <ma...@gmail.com>
:
> Takuya, thanks for your reply :)
> I am already doing that, and it is working well. My question is, can I
> define arbitrary functions to be used in these queries?
>
> On Fri, Jul 4, 2014 at 11:12 AM, Takuya UESHIN <ue...@happy-camper.st>
> wrote:
> > Hi,
> >
> > You can convert standard RDD of Product class (e.g. case class) to
> SchemaRDD
> > by SQLContext.
> > Load data from Cassandra into RDD of case class, convert it to SchemaRDD
> and
> > register it,
> > then you can use it in your SQLs.
> >
> >
> http://spark.apache.org/docs/latest/sql-programming-guide.html#running-sql-on-rdds
> >
> > Thanks.
> >
> >
> >
> > 2014-07-04 17:59 GMT+09:00 Martin Gammelsæter
> > <ma...@gmail.com>:
> >
> >> Hi!
> >>
> >> I have a Spark cluster running on top of a Cassandra cluster, using
> >> Datastax' new driver, and one of the fields of my RDDs is an
> >> XML-string. In a normal Scala sparkjob, parsing that data is no
> >> problem, but I would like to also make that information available
> >> through Spark SQL. So, is there any way to write user defined
> >> functions for Spark SQL? I know that a HiveContext is available, but I
> >> understand that that is for querying data from Hive, and I don't have
> >> Hive in my stack (please correct me if I'm wrong).
> >>
> >> I would love to be able to do something like the following:
> >>
> >> val casRdd = sparkCtx.cassandraTable("ks", "cf")
> >>
> >> // registerAsTable etc
> >>
> >> val res = sql("SELECT id, xmlGetTag(xmlfield, 'sometag') FROM cf")
> >>
> >> --
> >> Best regards,
> >> Martin Gammelsæter
> >
> >
> >
> >
> > --
> > Takuya UESHIN
> > Tokyo, Japan
> >
> > http://twitter.com/ueshin
>
>
>
> --
> Mvh.
> Martin Gammelsæter
> 92209139
>
--
Takuya UESHIN
Tokyo, Japan
http://twitter.com/ueshin
Re: Spark SQL user defined functions
Posted by Martin Gammelsæter <ma...@gmail.com>.
Takuya, thanks for your reply :)
I am already doing that, and it is working well. My question is, can I
define arbitrary functions to be used in these queries?
On Fri, Jul 4, 2014 at 11:12 AM, Takuya UESHIN <ue...@happy-camper.st> wrote:
> Hi,
>
> You can convert standard RDD of Product class (e.g. case class) to SchemaRDD
> by SQLContext.
> Load data from Cassandra into RDD of case class, convert it to SchemaRDD and
> register it,
> then you can use it in your SQLs.
>
> http://spark.apache.org/docs/latest/sql-programming-guide.html#running-sql-on-rdds
>
> Thanks.
>
>
>
> 2014-07-04 17:59 GMT+09:00 Martin Gammelsæter
> <ma...@gmail.com>:
>
>> Hi!
>>
>> I have a Spark cluster running on top of a Cassandra cluster, using
>> Datastax' new driver, and one of the fields of my RDDs is an
>> XML-string. In a normal Scala sparkjob, parsing that data is no
>> problem, but I would like to also make that information available
>> through Spark SQL. So, is there any way to write user defined
>> functions for Spark SQL? I know that a HiveContext is available, but I
>> understand that that is for querying data from Hive, and I don't have
>> Hive in my stack (please correct me if I'm wrong).
>>
>> I would love to be able to do something like the following:
>>
>> val casRdd = sparkCtx.cassandraTable("ks", "cf")
>>
>> // registerAsTable etc
>>
>> val res = sql("SELECT id, xmlGetTag(xmlfield, 'sometag') FROM cf")
>>
>> --
>> Best regards,
>> Martin Gammelsæter
>
>
>
>
> --
> Takuya UESHIN
> Tokyo, Japan
>
> http://twitter.com/ueshin
--
Mvh.
Martin Gammelsæter
92209139
Re: Spark SQL user defined functions
Posted by Takuya UESHIN <ue...@happy-camper.st>.
Hi,
You can convert standard RDD of Product class (e.g. case class) to
SchemaRDD by SQLContext.
Load data from Cassandra into RDD of case class, convert it to SchemaRDD
and register it,
then you can use it in your SQLs.
http://spark.apache.org/docs/latest/sql-programming-guide.html#running-sql-on-rdds
Thanks.
2014-07-04 17:59 GMT+09:00 Martin Gammelsæter <ma...@gmail.com>
:
> Hi!
>
> I have a Spark cluster running on top of a Cassandra cluster, using
> Datastax' new driver, and one of the fields of my RDDs is an
> XML-string. In a normal Scala sparkjob, parsing that data is no
> problem, but I would like to also make that information available
> through Spark SQL. So, is there any way to write user defined
> functions for Spark SQL? I know that a HiveContext is available, but I
> understand that that is for querying data from Hive, and I don't have
> Hive in my stack (please correct me if I'm wrong).
>
> I would love to be able to do something like the following:
>
> val casRdd = sparkCtx.cassandraTable("ks", "cf")
>
> // registerAsTable etc
>
> val res = sql("SELECT id, xmlGetTag(xmlfield, 'sometag') FROM cf")
>
> --
> Best regards,
> Martin Gammelsæter
>
--
Takuya UESHIN
Tokyo, Japan
http://twitter.com/ueshin