You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@spark.apache.org by ashu <as...@iiitb.org> on 2015/02/05 07:11:39 UTC
Tableau beta connector
Hi,
I am trying out the tableau beta connector to Spark SQL. I have few basics
question:
Will this connector be able to fetch the schemaRDDs into tableau.
Will all the schemaRDDs be exposed to tableau?
Basically I am not getting what tableau will fetch at data-source? Is it
existing files in HDFS? RDDs or something else.
Question may be naive but I did not get answer anywhere else. Would really
appreciate if someone has already tried it, can help me with this.
Thanks,
Ashutosh
--
View this message in context: http://apache-spark-user-list.1001560.n3.nabble.com/Tableau-beta-connector-tp21512.html
Sent from the Apache Spark User List mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@spark.apache.org
For additional commands, e-mail: user-help@spark.apache.org
Re: Tableau beta connector
Posted by "Ashutosh Trivedi (MT2013030)" <as...@iiitb.org>.
okay. So the queries tableau will run on the persisted data will be through SPARK SQL to improve performance and to take advantage of SPARK SQL.
Thanks again Denny
________________________________
From: Denny Lee <de...@gmail.com>
Sent: Thursday, February 5, 2015 1:27 PM
To: Ashutosh Trivedi (MT2013030); İsmail Keskin
Cc: user@spark.apache.org
Subject: Re: Tableau beta connector
The context is that you would create your RDDs and then persist them in Hive. Once in Hive, the data is accessible from the Tableau extract through Spark thrift server.
On Wed, Feb 4, 2015 at 23:36 Ashutosh Trivedi (MT2013030) <as...@iiitb.org>> wrote:
Thanks Denny and Ismail.
Denny ,I went through your blog, It was great help. I guess tableau beta connector also following the same procedure,you described in blog. I am building the Spark now.
Basically what I don't get is, where to put my data so that tableau can extract.
So Ismail,its just Spark SQL. No RDDs I think I am getting it now . We use spark for our big data processing and we want processed data (Rdd) into tableau. So we should put our data in hive metastore and tableau will extract it from there using this connector? Correct me if I am wrong.
I guess I have to look at how thrift server works.
________________________________
From: Denny Lee <de...@gmail.com>>
Sent: Thursday, February 5, 2015 12:20 PM
To: İsmail Keskin; Ashutosh Trivedi (MT2013030)
Cc: user@spark.apache.org<ma...@spark.apache.org>
Subject: Re: Tableau beta connector
Some quick context behind how Tableau interacts with Spark / Hive can also be found at https://www.concur.com/blog/en-us/connect-tableau-to-sparksql - its for how to connect from Tableau to the thrift server before the official Tableau beta connector but should provide some of the additional context called out. HTH!
On Wed Feb 04 2015 at 10:47:23 PM İsmail Keskin <is...@dilisim.com>> wrote:
Tableau connects to Spark Thrift Server via an ODBC driver. So, none of the RDD stuff applies, you just issue SQL queries from Tableau.
The table metadata can come from Hive Metastore if you place your hive-site.xml to configuration directory of Spark.
On Thu, Feb 5, 2015 at 8:11 AM, ashu <as...@iiitb.org>> wrote:
Hi,
I am trying out the tableau beta connector to Spark SQL. I have few basics
question:
Will this connector be able to fetch the schemaRDDs into tableau.
Will all the schemaRDDs be exposed to tableau?
Basically I am not getting what tableau will fetch at data-source? Is it
existing files in HDFS? RDDs or something else.
Question may be naive but I did not get answer anywhere else. Would really
appreciate if someone has already tried it, can help me with this.
Thanks,
Ashutosh
--
View this message in context: http://apache-spark-user-list.1001560.n3.nabble.com/Tableau-beta-connector-tp21512.html
Sent from the Apache Spark User List mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@spark.apache.org<ma...@spark.apache.org>
For additional commands, e-mail: user-help@spark.apache.org<ma...@spark.apache.org>
Re: Tableau beta connector
Posted by Denny Lee <de...@gmail.com>.
Could you clarify what you mean by "build another Spark and work through
Spark Submit"?
If you are referring to utilizing Spark spark and thrift, you could start
the Spark service and then have your spark-shell, spark-submit, and/or
thrift service aim at the master you have started.
On Thu Feb 05 2015 at 2:02:04 AM Ashutosh Trivedi (MT2013030) <
ashutosh.trivedi@iiitb.org> wrote:
> Hi Denny , Ismail one last question..
>
>
> Is it necessary to build another Spark and work through Spark-submit ?
>
>
> I work on IntelliJ using SBT as build script, I have Hive set up with
> postgres as metastore, I can run the hive server using command
>
> *hive --service metastore*
>
> *hive --service hiveserver2*
>
>
> After that if I can use hive-context in my code
>
> val sqlContext = new org.apache.spark.sql.hive.HiveContext(sc)
>
>
> Do some processing on RDD and persist it on hive using registerTempTable
>
> and tableau can extract that RDD persisted on hive.
>
>
> Regards,
>
> Ashutosh
>
>
> ------------------------------
> *From:* Denny Lee <de...@gmail.com>
>
> *Sent:* Thursday, February 5, 2015 1:27 PM
> *To:* Ashutosh Trivedi (MT2013030); İsmail Keskin
> *Cc:* user@spark.apache.org
> *Subject:* Re: Tableau beta connector
> The context is that you would create your RDDs and then persist them in
> Hive. Once in Hive, the data is accessible from the Tableau extract through
> Spark thrift server.
> On Wed, Feb 4, 2015 at 23:36 Ashutosh Trivedi (MT2013030) <
> ashutosh.trivedi@iiitb.org> wrote:
>
>> Thanks Denny and Ismail.
>>
>>
>> Denny ,I went through your blog, It was great help. I guess tableau
>> beta connector also following the same procedure,you described in blog. I
>> am building the Spark now.
>>
>> Basically what I don't get is, where to put my data so that tableau can
>> extract.
>>
>>
>> So Ismail,its just Spark SQL. No RDDs I think I am getting it now . We
>> use spark for our big data processing and we want *processed data (Rdd)*
>> into tableau. So we should put our data in hive metastore and tableau will
>> extract it from there using this connector? Correct me if I am wrong.
>>
>>
>> I guess I have to look at how thrift server works.
>> ------------------------------
>> *From:* Denny Lee <de...@gmail.com>
>> *Sent:* Thursday, February 5, 2015 12:20 PM
>> *To:* İsmail Keskin; Ashutosh Trivedi (MT2013030)
>> *Cc:* user@spark.apache.org
>> *Subject:* Re: Tableau beta connector
>>
>> Some quick context behind how Tableau interacts with Spark / Hive
>> can also be found at
>> https://www.concur.com/blog/en-us/connect-tableau-to-sparksql - its for
>> how to connect from Tableau to the thrift server before the official
>> Tableau beta connector but should provide some of the additional context
>> called out. HTH!
>>
>> On Wed Feb 04 2015 at 10:47:23 PM İsmail Keskin <
>> ismail.keskin@dilisim.com> wrote:
>>
>>> Tableau connects to Spark Thrift Server via an ODBC driver. So, none of
>>> the RDD stuff applies, you just issue SQL queries from Tableau.
>>>
>>> The table metadata can come from Hive Metastore if you place your
>>> hive-site.xml to configuration directory of Spark.
>>>
>>> On Thu, Feb 5, 2015 at 8:11 AM, ashu <as...@iiitb.org> wrote:
>>>
>>>> Hi,
>>>> I am trying out the tableau beta connector to Spark SQL. I have few
>>>> basics
>>>> question:
>>>> Will this connector be able to fetch the schemaRDDs into tableau.
>>>> Will all the schemaRDDs be exposed to tableau?
>>>> Basically I am not getting what tableau will fetch at data-source? Is it
>>>> existing files in HDFS? RDDs or something else.
>>>> Question may be naive but I did not get answer anywhere else. Would
>>>> really
>>>> appreciate if someone has already tried it, can help me with this.
>>>>
>>>> Thanks,
>>>> Ashutosh
>>>>
>>>>
>>>>
>>>> --
>>>> View this message in context:
>>>> http://apache-spark-user-list.1001560.n3.nabble.com/Tableau-beta-connector-tp21512.html
>>>> Sent from the Apache Spark User List mailing list archive at Nabble.com.
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-unsubscribe@spark.apache.org
>>>> For additional commands, e-mail: user-help@spark.apache.org
>>>>
>>>>
>>>
Re: Tableau beta connector
Posted by Denny Lee <de...@gmail.com>.
The context is that you would create your RDDs and then persist them in
Hive. Once in Hive, the data is accessible from the Tableau extract through
Spark thrift server.
On Wed, Feb 4, 2015 at 23:36 Ashutosh Trivedi (MT2013030) <
ashutosh.trivedi@iiitb.org> wrote:
> Thanks Denny and Ismail.
>
>
> Denny ,I went through your blog, It was great help. I guess tableau beta
> connector also following the same procedure,you described in blog. I am
> building the Spark now.
>
> Basically what I don't get is, where to put my data so that tableau can
> extract.
>
>
> So Ismail,its just Spark SQL. No RDDs I think I am getting it now . We
> use spark for our big data processing and we want *processed data (Rdd)*
> into tableau. So we should put our data in hive metastore and tableau will
> extract it from there using this connector? Correct me if I am wrong.
>
>
> I guess I have to look at how thrift server works.
> ------------------------------
> *From:* Denny Lee <de...@gmail.com>
> *Sent:* Thursday, February 5, 2015 12:20 PM
> *To:* İsmail Keskin; Ashutosh Trivedi (MT2013030)
> *Cc:* user@spark.apache.org
> *Subject:* Re: Tableau beta connector
>
> Some quick context behind how Tableau interacts with Spark / Hive can
> also be found at
> https://www.concur.com/blog/en-us/connect-tableau-to-sparksql - its for
> how to connect from Tableau to the thrift server before the official
> Tableau beta connector but should provide some of the additional context
> called out. HTH!
>
> On Wed Feb 04 2015 at 10:47:23 PM İsmail Keskin <is...@dilisim.com>
> wrote:
>
>> Tableau connects to Spark Thrift Server via an ODBC driver. So, none of
>> the RDD stuff applies, you just issue SQL queries from Tableau.
>>
>> The table metadata can come from Hive Metastore if you place your
>> hive-site.xml to configuration directory of Spark.
>>
>> On Thu, Feb 5, 2015 at 8:11 AM, ashu <as...@iiitb.org> wrote:
>>
>>> Hi,
>>> I am trying out the tableau beta connector to Spark SQL. I have few
>>> basics
>>> question:
>>> Will this connector be able to fetch the schemaRDDs into tableau.
>>> Will all the schemaRDDs be exposed to tableau?
>>> Basically I am not getting what tableau will fetch at data-source? Is it
>>> existing files in HDFS? RDDs or something else.
>>> Question may be naive but I did not get answer anywhere else. Would
>>> really
>>> appreciate if someone has already tried it, can help me with this.
>>>
>>> Thanks,
>>> Ashutosh
>>>
>>>
>>>
>>> --
>>> View this message in context:
>>> http://apache-spark-user-list.1001560.n3.nabble.com/Tableau-beta-connector-tp21512.html
>>> Sent from the Apache Spark User List mailing list archive at Nabble.com.
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@spark.apache.org
>>> For additional commands, e-mail: user-help@spark.apache.org
>>>
>>>
>>
Re: Tableau beta connector
Posted by "Ashutosh Trivedi (MT2013030)" <as...@iiitb.org>.
Thanks Denny and Ismail.
Denny ,I went through your blog, It was great help. I guess tableau beta connector also following the same procedure,you described in blog. I am building the Spark now.
Basically what I don't get is, where to put my data so that tableau can extract.
So Ismail,its just Spark SQL. No RDDs I think I am getting it now . We use spark for our big data processing and we want processed data (Rdd) into tableau. So we should put our data in hive metastore and tableau will extract it from there using this connector? Correct me if I am wrong.
I guess I have to look at how thrift server works.
________________________________
From: Denny Lee <de...@gmail.com>
Sent: Thursday, February 5, 2015 12:20 PM
To: İsmail Keskin; Ashutosh Trivedi (MT2013030)
Cc: user@spark.apache.org
Subject: Re: Tableau beta connector
Some quick context behind how Tableau interacts with Spark / Hive can also be found at https://www.concur.com/blog/en-us/connect-tableau-to-sparksql - its for how to connect from Tableau to the thrift server before the official Tableau beta connector but should provide some of the additional context called out. HTH!
On Wed Feb 04 2015 at 10:47:23 PM İsmail Keskin <is...@dilisim.com>> wrote:
Tableau connects to Spark Thrift Server via an ODBC driver. So, none of the RDD stuff applies, you just issue SQL queries from Tableau.
The table metadata can come from Hive Metastore if you place your hive-site.xml to configuration directory of Spark.
On Thu, Feb 5, 2015 at 8:11 AM, ashu <as...@iiitb.org>> wrote:
Hi,
I am trying out the tableau beta connector to Spark SQL. I have few basics
question:
Will this connector be able to fetch the schemaRDDs into tableau.
Will all the schemaRDDs be exposed to tableau?
Basically I am not getting what tableau will fetch at data-source? Is it
existing files in HDFS? RDDs or something else.
Question may be naive but I did not get answer anywhere else. Would really
appreciate if someone has already tried it, can help me with this.
Thanks,
Ashutosh
--
View this message in context: http://apache-spark-user-list.1001560.n3.nabble.com/Tableau-beta-connector-tp21512.html
Sent from the Apache Spark User List mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@spark.apache.org<ma...@spark.apache.org>
For additional commands, e-mail: user-help@spark.apache.org<ma...@spark.apache.org>
Re: Tableau beta connector
Posted by Denny Lee <de...@gmail.com>.
Some quick context behind how Tableau interacts with Spark / Hive can also
be found at https://www.concur.com/blog/en-us/connect-tableau-to-sparksql
- its for how to connect from Tableau to the thrift server before the
official Tableau beta connector but should provide some of the additional
context called out. HTH!
On Wed Feb 04 2015 at 10:47:23 PM İsmail Keskin <is...@dilisim.com>
wrote:
> Tableau connects to Spark Thrift Server via an ODBC driver. So, none of
> the RDD stuff applies, you just issue SQL queries from Tableau.
>
> The table metadata can come from Hive Metastore if you place your
> hive-site.xml to configuration directory of Spark.
>
> On Thu, Feb 5, 2015 at 8:11 AM, ashu <as...@iiitb.org> wrote:
>
>> Hi,
>> I am trying out the tableau beta connector to Spark SQL. I have few basics
>> question:
>> Will this connector be able to fetch the schemaRDDs into tableau.
>> Will all the schemaRDDs be exposed to tableau?
>> Basically I am not getting what tableau will fetch at data-source? Is it
>> existing files in HDFS? RDDs or something else.
>> Question may be naive but I did not get answer anywhere else. Would really
>> appreciate if someone has already tried it, can help me with this.
>>
>> Thanks,
>> Ashutosh
>>
>>
>>
>> --
>> View this message in context:
>> http://apache-spark-user-list.1001560.n3.nabble.com/Tableau-beta-connector-tp21512.html
>> Sent from the Apache Spark User List mailing list archive at Nabble.com.
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@spark.apache.org
>> For additional commands, e-mail: user-help@spark.apache.org
>>
>>
>
Re: Tableau beta connector
Posted by İsmail Keskin <is...@dilisim.com>.
Tableau connects to Spark Thrift Server via an ODBC driver. So, none of the
RDD stuff applies, you just issue SQL queries from Tableau.
The table metadata can come from Hive Metastore if you place your
hive-site.xml to configuration directory of Spark.
On Thu, Feb 5, 2015 at 8:11 AM, ashu <as...@iiitb.org> wrote:
> Hi,
> I am trying out the tableau beta connector to Spark SQL. I have few basics
> question:
> Will this connector be able to fetch the schemaRDDs into tableau.
> Will all the schemaRDDs be exposed to tableau?
> Basically I am not getting what tableau will fetch at data-source? Is it
> existing files in HDFS? RDDs or something else.
> Question may be naive but I did not get answer anywhere else. Would really
> appreciate if someone has already tried it, can help me with this.
>
> Thanks,
> Ashutosh
>
>
>
> --
> View this message in context:
> http://apache-spark-user-list.1001560.n3.nabble.com/Tableau-beta-connector-tp21512.html
> Sent from the Apache Spark User List mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@spark.apache.org
> For additional commands, e-mail: user-help@spark.apache.org
>
>
Re: Tableau beta connector
Posted by Todd Nist <ts...@gmail.com>.
Hi Markus,
Was just thinking a bit more about this and your use case, mine as well
long term, ingesting json and saving as parquet or json for constructing
table. I wonder if one can leverage the CREATE EXTERNAL TABLE XXX and
associate it with the appropriate SerDe for json or parquet:
*Parquet*
CREATE EXTERNAL TABLE XXX (...)
ROW FORMAT SERDE 'parquet.hive.serde.ParquetHiveSerDe'
STORED AS
INPUTFORMAT "parquet.hive.DeprecatedParquetInputFormat"
OUTPUTFORMAT "parquet.hive.DeprecatedParquetOutputFormat"
LOCATION '/your/location/here';
CREATE EXTERNAL TABLE IF NOT EXISTS XXX (
field1 string, field2 int, field3 string, field4 double)
ROW FORMAT SERDE
'org.apache.hadoop.hive.contrib.serde2.JsonSerde'
LOCATION '/path-to/my_table/';
Then just saves the JSON or Parquet to the '/your/location/here'. Not sure
if that would work or not will need to give that a try.
Let me know if that makes sense or you have tired it already.
-Todd
On Wed, Feb 18, 2015 at 3:54 PM, Todd Nist <ts...@gmail.com> wrote:
> Hi Markus,
>
> Well I got it working, as shown below. I'm open to suggestions or a
> different approach. I have mysql setup now as the hivemetastore and the
> hive-site.xml in my $SPARK_HOME/conf directory. I am currently reading
> from a csv file, using the spark-csv package, and simply converting to a
> simple table, code needs some clean up. Ignore the "Helper" object.
> Hopefully, it will assist in some small way.
>
> import com.databricks.spark.csv._
> import org.apache.spark.{SparkConf, SparkContext}
> import org.apache.spark.sql._
>
> object Helper {
> import org.joda.time._
> import org.joda.time.format._
>
> def toDateTime(dt: String, time: String) = {
> //val formatter: DateTimeFormatter = DateTimeFormat.forPattern("dd/mm/yyyy HH:mm:ss");
> val formatter = DateTimeFormat.forPattern("MM/dd/yyyy'T'HH:mm:ss.SSSZZ");
> val date: DateTime = formatter.parseDateTime(s"$dt" + s"T$time:00.0+02:00")
> date
> }
> def toSqlTimeString(dt: DateTime) = {
> val fmt = DateTimeFormat.forPattern("MM/dd/yyyy HH:mm:ss")
> val sqlTimeString = fmt.print(dt)
> sqlTimeString
> }
> }
>
> object CSVToHive {
> import Helper._
>
> @transient
> val hiveContext = new org.apache.spark.sql.hive.HiveContext(sc)
>
> import hiveContext._
>
> // clean up table if it exists
> hiveContext.hql("drop table if exists ods_demo")
>
> val ods = hiveContext.csvFile("/user/data/csv/Demo1.csv")
>
> ods.registerTempTable("demo")
>
> // use hql("select date, time, ...").map( case Row(...))
> val rdd = hql("select * from demo").map(
> row => (
> toSqlTimeString(toDateTime(row(0).toString, row(1).toString)), // date, time as string
> row(2).toString,
> row(3).toString.toInt,
> row(4).toString,
> row(5).toString.toInt,
> row(6).toString, // host_name
> row(7).toString.toInt,
> row(8).toString,
> row(9).toString.replaceAll("%", "").toInt,
> row(10).toString,
> row(11).toString.toInt,
> row(12).toString,
> row(13).toString.toInt
> )
> )
>
> rdd.saveAsTable("ods_demo")
>
> }
>
> Now from Tableau I can navigate to my table by connecting to the
> $SPARK_HOME/sbin/start-thriftserver ... selecting the SparkSQL Beta then
> selecting default schema, and my table.
>
>
>
>
> The one thing that Michael Ambrust @ databrick recommended was this:
>
> You can start a JDBC server with an existing context. See my answer here:
>> http://apache-spark-user-list.1001560.n3.nabble.com/Standard-SQL-tool-access-to-SchemaRDD-td20197.html
>
>
> So something like this based on example from Cheng Lian:
>
> *Server*
>
> import org.apache.spark.sql.hive.HiveContext
> import org.apache.spark.sql.catalyst.types._
>
> val sparkContext = sc
> import sparkContext._
> val sqlContext = new HiveContext(sparkContext)
> import sqlContext._
> makeRDD((1,"hello") :: (2,"world") ::Nil).toSchemaRDD.cache().registerTempTable("t")
> import org.apache.spark.sql.hive.thriftserver._
> HiveThriftServer2.startWithContext(sqlContext)
>
> Then Startup
>
> ./bin/beeline -u jdbc:hive2://localhost:10000/default
> 0: jdbc:hive2://localhost:10000/default> select * from t;
>
>
> I have not tried this yet from Tableau. My understanding is that the
> tempTable is only valid as long as the sqlContext is, so if one terminates
> the code representing the *Server*, and then restarts the standard thrift
> server, sbin/start-thriftserver ..., the table won't be available. With
> the hive approach shown in CSVtoHive example, table survives restarts.
>
> I have talked with Tableau on this as well and this is there response:
>
> I connected with Databricks and they say this should be better in Spark
>> 1.3. Then you can both create and persist metastore tables that read from
>> Spark SQL datasources, and hopefully you will be able to see temporary
>> tables as well when you do a SHOW TABLES.
>>
>> This will also likely require some changes on the Tableau side so we'll
>> need to make some updates in our connector to make the temp tables visible.
>
>
> So I have asked for clarification showing them how I am doing it now, but
> am still waiting for a reply.
>
> Anyways those are the steps I to to get it working, if there is a cleaner
> way please let me know.
>
> HTH.
>
> -Todd
>
> On Wed, Feb 18, 2015 at 2:19 PM, ganterm <ga...@gmail.com> wrote:
>
>> Ashutosh,
>>
>> Were you able to figure this out? I am having the exact some question.
>> I think the answer is to use Spark SQL to create/load a table in Hive
>> (e.g.
>> execute the HiveQL CREATE TABLE statement) but I am not sure. Hoping for
>> something more simple than that.
>>
>> Anybody?
>>
>> Thanks!
>>
>>
>>
>> --
>> View this message in context:
>> http://apache-spark-user-list.1001560.n3.nabble.com/Tableau-beta-connector-tp21512p21709.html
>> Sent from the Apache Spark User List mailing list archive at Nabble.com.
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@spark.apache.org
>> For additional commands, e-mail: user-help@spark.apache.org
>>
>>
>
Re: Tableau beta connector
Posted by Todd Nist <ts...@gmail.com>.
Hi Markus,
Well I got it working, as shown below. I'm open to suggestions or a
different approach. I have mysql setup now as the hivemetastore and the
hive-site.xml in my $SPARK_HOME/conf directory. I am currently reading
from a csv file, using the spark-csv package, and simply converting to a
simple table, code needs some clean up. Ignore the "Helper" object.
Hopefully, it will assist in some small way.
import com.databricks.spark.csv._
import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql._
object Helper {
import org.joda.time._
import org.joda.time.format._
def toDateTime(dt: String, time: String) = {
//val formatter: DateTimeFormatter =
DateTimeFormat.forPattern("dd/mm/yyyy HH:mm:ss");
val formatter = DateTimeFormat.forPattern("MM/dd/yyyy'T'HH:mm:ss.SSSZZ");
val date: DateTime = formatter.parseDateTime(s"$dt" + s"T$time:00.0+02:00")
date
}
def toSqlTimeString(dt: DateTime) = {
val fmt = DateTimeFormat.forPattern("MM/dd/yyyy HH:mm:ss")
val sqlTimeString = fmt.print(dt)
sqlTimeString
}
}
object CSVToHive {
import Helper._
@transient
val hiveContext = new org.apache.spark.sql.hive.HiveContext(sc)
import hiveContext._
// clean up table if it exists
hiveContext.hql("drop table if exists ods_demo")
val ods = hiveContext.csvFile("/user/data/csv/Demo1.csv")
ods.registerTempTable("demo")
// use hql("select date, time, ...").map( case Row(...))
val rdd = hql("select * from demo").map(
row => (
toSqlTimeString(toDateTime(row(0).toString,
row(1).toString)), // date, time as string
row(2).toString,
row(3).toString.toInt,
row(4).toString,
row(5).toString.toInt,
row(6).toString, // host_name
row(7).toString.toInt,
row(8).toString,
row(9).toString.replaceAll("%", "").toInt,
row(10).toString,
row(11).toString.toInt,
row(12).toString,
row(13).toString.toInt
)
)
rdd.saveAsTable("ods_demo")
}
Now from Tableau I can navigate to my table by connecting to the
$SPARK_HOME/sbin/start-thriftserver ... selecting the SparkSQL Beta then
selecting default schema, and my table.
The one thing that Michael Ambrust @ databrick recommended was this:
You can start a JDBC server with an existing context. See my answer here:
> http://apache-spark-user-list.1001560.n3.nabble.com/Standard-SQL-tool-access-to-SchemaRDD-td20197.html
So something like this based on example from Cheng Lian:
*Server*
import org.apache.spark.sql.hive.HiveContext
import org.apache.spark.sql.catalyst.types._
val sparkContext = sc
import sparkContext._
val sqlContext = new HiveContext(sparkContext)
import sqlContext._
makeRDD((1,"hello") :: (2,"world")
::Nil).toSchemaRDD.cache().registerTempTable("t")
import org.apache.spark.sql.hive.thriftserver._
HiveThriftServer2.startWithContext(sqlContext)
Then Startup
./bin/beeline -u jdbc:hive2://localhost:10000/default
0: jdbc:hive2://localhost:10000/default> select * from t;
I have not tried this yet from Tableau. My understanding is that the
tempTable is only valid as long as the sqlContext is, so if one terminates
the code representing the *Server*, and then restarts the standard thrift
server, sbin/start-thriftserver ..., the table won't be available. With
the hive approach shown in CSVtoHive example, table survives restarts.
I have talked with Tableau on this as well and this is there response:
I connected with Databricks and they say this should be better in Spark
> 1.3. Then you can both create and persist metastore tables that read from
> Spark SQL datasources, and hopefully you will be able to see temporary
> tables as well when you do a SHOW TABLES.
>
> This will also likely require some changes on the Tableau side so we'll
> need to make some updates in our connector to make the temp tables visible.
So I have asked for clarification showing them how I am doing it now, but
am still waiting for a reply.
Anyways those are the steps I to to get it working, if there is a cleaner
way please let me know.
HTH.
-Todd
On Wed, Feb 18, 2015 at 2:19 PM, ganterm <ga...@gmail.com> wrote:
> Ashutosh,
>
> Were you able to figure this out? I am having the exact some question.
> I think the answer is to use Spark SQL to create/load a table in Hive (e.g.
> execute the HiveQL CREATE TABLE statement) but I am not sure. Hoping for
> something more simple than that.
>
> Anybody?
>
> Thanks!
>
>
>
> --
> View this message in context:
> http://apache-spark-user-list.1001560.n3.nabble.com/Tableau-beta-connector-tp21512p21709.html
> Sent from the Apache Spark User List mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@spark.apache.org
> For additional commands, e-mail: user-help@spark.apache.org
>
>
Re: Tableau beta connector
Posted by "Ashutosh Trivedi (MT2013030)" <as...@iiitb.org>.
Thanks Todd. great stuff :)
Regards,
Ashu
________________________________
From: Todd Nist <ts...@gmail.com>
Sent: Thursday, February 19, 2015 7:46 PM
To: Ashutosh Trivedi (MT2013030)
Cc: user@spark.apache.org
Subject: Re: Tableau beta connector
I am able to connect by doing the following using the Tableau Initial SQL and a custom query:
1. First ingest csv file or json and save out to file system:
import org.apache.spark.sql.SQLContext
import com.databricks.spark.csv._
val sqlContext = new SQLContext(sc)
val demo = sqlContext.csvFile("/user/data/csv/demo.csv")
demo.toJSON.saveAsTextFile("/user/data/json/test")
2. Start $SPARK_HOME/sbin/start-thirftserver:
./sbin/start-thriftserver.sh --master spark://radtech.io:7077<http://radtech.io:7077> --total-executor-cores 2 --driver-class-path --hiveconf hive.server2.thrift.port=10001 --hiveconf hive.server2.thrift.bind.host radtech.io<http://radtech.io>
3. Start tableau session. Create a connection to thrift server via SparkSQL (Beta) connector.
4. In Tableau add the following to the "Initial SQL"
create temporary table test
using org.apache.spark.sql.json
options (path '/user/data/json/test/*');
cache table test;
* Refresh connection.
Then select "New Custom SQL" and issue something like:
select * from test;
You will see your table appear.
HTH.
-Todd
On Thu, Feb 19, 2015 at 5:41 AM, ashu <as...@iiitb.org>> wrote:
Hi,
I would like you to read my stack overflow answer to this question. If you need more clarification feel free to drop a msg.
http://stackoverflow.com/questions/28403664/connect-to-existing-hive-in-intellij-using-sbt-as-build
Regards,
Ashutosh
________________________________
From: ganterm [via Apache Spark User List] <ml-node+[hidden email]<http:///user/SendEmail.jtp?type=node&node=21719&i=0>>
Sent: Thursday, February 19, 2015 12:49 AM
To: Ashutosh Trivedi (MT2013030)
Subject: Re: Tableau beta connector
Ashutosh,
Were you able to figure this out? I am having the exact some question.
I think the answer is to use Spark SQL to create/load a table in Hive (e.g. execute the HiveQL CREATE TABLE statement) but I am not sure. Hoping for something more simple than that.
Anybody?
Thanks!
________________________________
If you reply to this email, your message will be added to the discussion below:
http://apache-spark-user-list.1001560.n3.nabble.com/Tableau-beta-connector-tp21512p21709.html
To unsubscribe from Tableau beta connector, click here.
NAML<http://apache-spark-user-list.1001560.n3.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
________________________________
View this message in context: Re: Tableau beta connector<http://apache-spark-user-list.1001560.n3.nabble.com/Tableau-beta-connector-tp21512p21719.html>
Sent from the Apache Spark User List mailing list archive<http://apache-spark-user-list.1001560.n3.nabble.com/> at Nabble.com.
Re: Tableau beta connector
Posted by Todd Nist <ts...@gmail.com>.
I am able to connect by doing the following using the Tableau Initial SQL
and a custom query:
1.
First ingest csv file or json and save out to file system:
import org.apache.spark.sql.SQLContext
import com.databricks.spark.csv._
val sqlContext = new SQLContext(sc)
val demo = sqlContext.csvFile("/user/data/csv/demo.csv")
demo.toJSON.saveAsTextFile("/user/data/json/test”)
2.
Start $SPARK_HOME/sbin/start-thirftserver:
./sbin/start-thriftserver.sh --master spark://radtech.io:7077
--total-executor-cores 2 --driver-class-path --hiveconf
hive.server2.thrift.port=10001 --hiveconf
hive.server2.thrift.bind.host radtech.io
3.
Start tableau session. Create a connection to thrift server via SparkSQL
(Beta) connector.
4.
In Tableau add the following to the “Initial SQL”
create temporary table test
using org.apache.spark.sql.json
options (path '/user/data/json/test/*’);
cache table test;
1. Refresh connection.
Then select “New Custom SQL” and issue something like:
select * from test;
You will see your table appear.
HTH.
-Todd
On Thu, Feb 19, 2015 at 5:41 AM, ashu <as...@iiitb.org> wrote:
> Hi,
>
> I would like you to read my stack overflow answer to this question. If you
> need more clarification feel free to drop a msg.
>
>
> http://stackoverflow.com/questions/28403664/connect-to-existing-hive-in-intellij-using-sbt-as-build
>
>
> Regards,
>
> Ashutosh
> ------------------------------
> *From:* ganterm [via Apache Spark User List] <ml-node+[hidden email]
> <http:///user/SendEmail.jtp?type=node&node=21719&i=0>>
> *Sent:* Thursday, February 19, 2015 12:49 AM
> *To:* Ashutosh Trivedi (MT2013030)
> *Subject:* Re: Tableau beta connector
>
> Ashutosh,
>
> Were you able to figure this out? I am having the exact some question.
> I think the answer is to use Spark SQL to create/load a table in Hive
> (e.g. execute the HiveQL CREATE TABLE statement) but I am not sure. Hoping
> for something more simple than that.
>
> Anybody?
>
> Thanks!
>
> ------------------------------
> If you reply to this email, your message will be added to the discussion
> below:
>
> http://apache-spark-user-list.1001560.n3.nabble.com/Tableau-beta-connector-tp21512p21709.html
> To unsubscribe from Tableau beta connector, click here.
> NAML
> <http://apache-spark-user-list.1001560.n3.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>
> ------------------------------
> View this message in context: Re: Tableau beta connector
> <http://apache-spark-user-list.1001560.n3.nabble.com/Tableau-beta-connector-tp21512p21719.html>
>
> Sent from the Apache Spark User List mailing list archive
> <http://apache-spark-user-list.1001560.n3.nabble.com/> at Nabble.com.
>
Re: Tableau beta connector
Posted by "Ashutosh Trivedi (MT2013030)" <as...@iiitb.org>.
Hi,
I would like you to read my stack overflow answer to this question. If you need more clarification feel free to drop a msg.
http://stackoverflow.com/questions/28403664/connect-to-existing-hive-in-intellij-using-sbt-as-build
Regards,
Ashutosh
________________________________
From: ganterm [via Apache Spark User List] <ml...@n3.nabble.com>
Sent: Thursday, February 19, 2015 12:49 AM
To: Ashutosh Trivedi (MT2013030)
Subject: Re: Tableau beta connector
Ashutosh,
Were you able to figure this out? I am having the exact some question.
I think the answer is to use Spark SQL to create/load a table in Hive (e.g. execute the HiveQL CREATE TABLE statement) but I am not sure. Hoping for something more simple than that.
Anybody?
Thanks!
________________________________
If you reply to this email, your message will be added to the discussion below:
http://apache-spark-user-list.1001560.n3.nabble.com/Tableau-beta-connector-tp21512p21709.html
To unsubscribe from Tableau beta connector, click here<http://apache-spark-user-list.1001560.n3.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=21512&code=YXNodXRvc2gudHJpdmVkaUBpaWl0Yi5vcmd8MjE1MTJ8LTM5MzMxOTc2MQ==>.
NAML<http://apache-spark-user-list.1001560.n3.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
Re: Tableau beta connector
Posted by ashu <as...@iiitb.org>.
Hi,
I would like you to read my stack overflow answer to this question. If you need more clarification feel free to drop a msg.
http://stackoverflow.com/questions/28403664/connect-to-existing-hive-in-intellij-using-sbt-as-build
Regards,
Ashutosh
________________________________
From: ganterm [via Apache Spark User List] <ml...@n3.nabble.com>
Sent: Thursday, February 19, 2015 12:49 AM
To: Ashutosh Trivedi (MT2013030)
Subject: Re: Tableau beta connector
Ashutosh,
Were you able to figure this out? I am having the exact some question.
I think the answer is to use Spark SQL to create/load a table in Hive (e.g. execute the HiveQL CREATE TABLE statement) but I am not sure. Hoping for something more simple than that.
Anybody?
Thanks!
________________________________
If you reply to this email, your message will be added to the discussion below:
http://apache-spark-user-list.1001560.n3.nabble.com/Tableau-beta-connector-tp21512p21709.html
To unsubscribe from Tableau beta connector, click here<http://apache-spark-user-list.1001560.n3.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=21512&code=YXNodXRvc2gudHJpdmVkaUBpaWl0Yi5vcmd8MjE1MTJ8LTM5MzMxOTc2MQ==>.
NAML<http://apache-spark-user-list.1001560.n3.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
--
View this message in context: http://apache-spark-user-list.1001560.n3.nabble.com/Tableau-beta-connector-tp21512p21719.html
Sent from the Apache Spark User List mailing list archive at Nabble.com.
Re: Tableau beta connector
Posted by ganterm <ga...@gmail.com>.
Ashutosh,
Were you able to figure this out? I am having the exact some question.
I think the answer is to use Spark SQL to create/load a table in Hive (e.g.
execute the HiveQL CREATE TABLE statement) but I am not sure. Hoping for
something more simple than that.
Anybody?
Thanks!
--
View this message in context: http://apache-spark-user-list.1001560.n3.nabble.com/Tableau-beta-connector-tp21512p21709.html
Sent from the Apache Spark User List mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@spark.apache.org
For additional commands, e-mail: user-help@spark.apache.org