# How do Data Scientists integrate Predictive Modeling with SQL?

I've heard a lot about the importance of knowing SQL but how do you guys at the larger firms integrate your software for modeling/analysis with the server that holds the data?

This question is coming from the guy who learned SQL but never learned how to synchronize that with R.

There is no need to download data in CSV format, in most cases it is actually bad practice. Consider cases where data size > 1GB and is updated daily. This would add a considerable overhead and is not easy to automate.

Instead you can take a look at various R packages that exist to fetch data from your SQL database. Using RODBC, SQLite, sqldf or other available libraries you can import data to R, run queries, create tables, update tables and pretty much anything else you will need.

• This is what I was thinking. Do you automatically update the training data and retrain models? I.e., perhaps one static model and one constantly retrained model? – Nicklovn Apr 16 '19 at 13:21
• If the data changes in your database and your model always gets the latest data, then yes you will be able to do it automatically. If you want a static model then you just specify for example a date range and this will be your training data set no matter how many times you run the model. The point is that you can choose which one you want and implement it effectively with these libraries avoiding the pains of using static csvs. – mincorp Apr 16 '19 at 13:27

Agree with @mincorp about RODBC, and that we don't use Workbenches and csvs most of the time in production.

Additionally though, 'production' data science also tends to exist in a separate box to the database. This can be for performance reasons; some models are big, and most DBA's rightly don't like you taking up resource in a process they don't really control. It can also be for maintenance reasons; by 'owning you own hardware' (even if it's an AWS EC2), you can tear down, redeploy, and rearchitect as you want, without impacting the DB at all.

Doing the plumbing between these has been both my biggest learning curve in recent years and the part of data science that I totally didn't expect until even after I'd been doing it a little while.

Learning how to make interfaces and maintain your own work for your own models is the secret key skill for a data scientist.

Package dbplyr is a relatively new package that translates complex R commands into SQL statements, which get then executed on the DB Server.

Especially for aggregated datasets or joined,filtered datasets this brings performance benefits. No need to fetch large amounts of rows over the network, to perform processing locally; and you also get indexing and possibly other optimizations from the RDBMS.