In-database R coming to SQL Server 2016

http://blog.revolutionanalytics.com/2015/05/r-in-sql-server.html

by Hansi • 11 years ago

64 31 11 years ago

blog.revolutionanalytics.com

I hope it's not as bad as PL/R. Seemed like a good idea, but the performance was so terrible that it was essentially useless.

jeltz• 11 years ago

Any idea what makes PL/R slow? Is it time spent doing data conversions from PostgreSQL types to R types?

tankenmate• 11 years ago

This article[0] shows exactly this problem; and it links to patches that handle this issue. The article is 4.5 years old, not sure if the patches have been upstreamed.

[0] http://www.credativ.co.uk/credativ-blog/2010/07/postgresql-t...

atorralb• 11 years ago

just so you know, is already in SAP HANA... is nice to see that programming languages are part of a database a not just an extension

toddkazakov2• 11 years ago

It's not actually in the database. It's a lousy implementation which does not use the distributed nature of HANA. You fetch the data first and then you do your analyses in R which is hosted near the DB. If you want parallel processing in R, you're on your own.

fs1112• 11 years ago

How does that work when R is GPL licensed? Doesn't that make SQL server a derived work?

olh• 11 years ago

Not the same R implementation.

fs1112• 11 years ago

I see. Did they re-implement it from scratch?

toddkazakov2• 11 years ago

Revolution have their own implementation of R called OpenR.

hadley3• 11 years ago

No, that's not their own implementation. It's GNU r bundled with some extras

Jake232• 11 years ago

I'm always reluctant to these kinds of ideas, of executing code on/within my database server.

I know it's apparently sandboxed, but that didn't work out too well for ElasticSearch recently: https://jordan-wright.github.io/blog/2015/03/08/elasticsearc....

SigmundA2• 11 years ago

SQL is code running in the database server. Most SQL implementations, TSQL, PL/SQL, PGSQL are turing complete as well.

bladecatcher3• 11 years ago

While this is a good concept in theory, I'd be skeptical about building on top of such a system. The primary reason is the slowness of R. I built heavy duty data mining systems using a stack of kdb/q and R. In my experience, R, when used for simple clustering algorithms like k-means and k-medoids slowed down my system by nearly 70 times. This is despite running parallelized versions of these algorithms (by means of the SPRINT R package) using mpiexec.

IMO, there is a very big gap in this space. There is an urgent need for high performant data inference languages. MATLAB is decent, but is still clunky for my taste. Plus, I prefer the simplicity of a file mapped column oriented database like the one offered by kdb. As KDB is too expensive for me right now, I'm considering building on top of the excellent J language/JDB database stack for my big data needs.

gaius2• 11 years ago

This will all be down to the implementation of DataFrame no?

In other words if you put q ontop of a R DataFrame would you expect the same performance? Or if you ran R ontop of K.

bladecatcher3• 11 years ago

I'm not sure if I understand your question correctly. kx provides shared libraries to make R and q talk to each other. The specific approach I followed used a shared library that brings R into the kdb+ memory space, meaning all the R statistical routines and graphing capabilities can be invoked directly from kdb+. Using this method means data is not passed between remote processes.

steego2• 11 years ago

How does Julia work in this space? What's it lacking?

bladecatcher3• 11 years ago

I do not have experience with Julia. I'm interested in languages built for time series data specifically. APL derivatives shine in this regard, in the sense that they provide the bare metal language composed of a few very powerful verbs and adverbs. For example, I could whip up a simple linear regression package in about 10 lines of tight code. These languages also lend themselves to parallelism in a very straightforward manner by means of the "parallel apply" verb. In addition , the brevity that languages like q offer is simply unparalleled.

Lofkin• 11 years ago

Julia fills this need. Also python with numba. Julia is the future, takes the best syntax from python, matlab couples that with a beautiful and extensible type system and uses this to drive JIT llvm with speed on the order of C.

nonfamous• 11 years ago

Blog post author here. SQL Server will be embedding Revolution R Enterprise, which eliminates the slowness and memory constraints of base R for many machine learning and statistical modeling algorithms. It runs parallel (not single-threaded) algorithms in a dedicated sandbox that stream data from SQL Server (and so eliminating RAM constraints). You can see some benchmarks in this white paper http://www.revolutionanalytics.com/sites/default/files/revol... -- for example it can run stepwise linear regression on 5M rows and 100 variables in 14 seconds. (Benchmark scripts at: https://github.com/RevolutionAnalytics/Benchmark )

gtrubetskoy• 11 years ago

For what it's worth, PostgreSQL had this since 2003. http://www.joeconway.com/plr/

IMHO scripts running in a database server never work all that well - debugging is a nightmare. At least this has been my experience from trying PG plpython a few years ago.

Link to the original announcement email: http://www.postgresql.org/message-id/3E514A46.2040604@joecon...

hadley3• 11 years ago

R does have some pretty cool tools for after the fact debugging, like dump.frames, but few people know about them.

vittore• 11 years ago

Why Microsoft? Why? Why not python?

M8• 11 years ago

They are not gluing layers, they are adding statistical capabilities. Each language surves it's purpose.

SigmundA2• 11 years ago

I would say why not improve the CLR support that has been there since SQL 2005?

BrentOzar• 11 years ago

I would say why not improve the CLR support that has been there since SQL 2005?

Because it won't cause new license sales, whereas integrating R might.

modarts• 11 years ago

Shudder

sorokod• 11 years ago

How does Java inside Oracle DB is doing nowadays?

In-database R coming to SQL Server 2016

Related Stories