Check out MemSQL's Community Edition for this very use case. We shipped MemSQL with an open-source multi-threaded, bi-directional connector to Spark.

The DB has two storage engines: in-memory row tables, and on-disk column tables for efficient compression and permanent retention. Then, it becomes an easy task of INSERT/SELECT...FROM to move data from memory to disk very quickly.

Do this: http://blog.cloudera.com/blog/2014/11/how-to-do-near-real-ti...

Then at the end instead of writing to HBase you can write JDBC to do the insert.

JDBC works great if you have a large RDD that you want to persist in one go.

But if you are writing row by row you will need to implement your own batching algorithm and connection pooling to get any decent performance.

You could always use mapPartition to open one connection per partition.

Nice link. Spark jobs run Java code anyway, so why not write to JDBC with the results of processing. Makes perfect sense.

May I ask, why do you want to integrate Spark in the middle of the two? I am seeing Spark used more for distributed processing/caching data rather than being a conduit for data movement from one system to another.

You have a realtime write only database and you want to update a historical database from that write only database? Or do you just want to join data across the two sources on the fly? Those are two pretty different use cases.

Based on what you're asking, you might find these two articles interesting:

- http://blog.confluent.io/2015/03/04/turning-the-database-ins...

- http://lambda-architecture.net/

Well, maybe I'm totally off on this, but it's more that I'd like to be able to run analytics which include real-time data without having any notable pauses. I'm willing to look at anything in terms of getting the data from the real time capture into the historical database as long as the spark queries "just work".

Sorry, I think maybe "integrating between" was the wrong way to phrase it.

On the other hand, I mean there's clean up and preprocessing I want to do on data that goes into the historical dataset, so hey, why not do that clean up/processing with Spark?

I've seen Lambda Architecture before, but it seems like it's kinda gone dark and unless I just totally overlooked it, I don't think there was a "Hey, this is the way to do it guys!"

Not sure if you have used it but Spark is exceptionally good at data movement.

In fact that is what a lot of people initially started using it for (as a replacement for Hive/Pig). You can write SQL against HCatalog tables, do some transformation work then write the results out to a different system. We have hundreds of jobs that do just this.

Well, I guess that is the power of it being so general purpose. I have used Spark more for analytics (and Spark SQL) but not extensively for ETL. What you're saying makes sense, you're still using Spark as an execution/computation engine, just writing the plumbing code to use it like an intermediary ETL tool.

While Spark is not intended for ETL per se, when I need to copy data from s3 to HDFS, I just use sc.textFile and sc.saveAsTextFile, in most of my use cases it does it pretty fast.

But Spark is mostly a computation engine replacing MapReduce (plus a standalone cluster management option). not an ETL tool.

I would look into other tools, such as https://projects.apache.org/projects/sqoop.html but I'm sure you know it already.

Sqoop does the extraction but not the transformation part of ETL and is only used for bulk moves not iterative.

I know it's cool to bash MongoDB but it is really nicely integrated with Spark, extremely quick for a write workloads (3000 writes/second on our slow drives and that's inside the RDD map) and doesn't flinch even when getting it to write 1 billion rows in quick succession. One thing that is really nice is that being schemaless you don't have to worry about setting up tables structures beforehand.

You can look at Cassandra which is historically known for exceptional write performance.

All the Spark links & hype I see is Cassandra Cassandra Cassandra, can you point me to a link or place that talks about hooking Spark to MongoDB?

you can use Spark JDBC RDD with Apache Phoenix, it's pretty fast: https://phoenix.apache.org/performance.html

You can even go one better and use the Spark integration directly:

https://phoenix.apache.org/phoenix_spark.html