4
$\begingroup$

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?

Do you simply access some SQL Workbench and download a .csv file then read the file in your software? Or do you gain access to the server directly through your software?

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

Improve this question
$\endgroup$

4 Answers 4

Reset to default
7
$\begingroup$

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.

Improve this answer
$\endgroup$
2
  • $\begingroup$ 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? $\endgroup$
    – Nicklovn
    Commented Apr 16, 2019 at 13:21
  • $\begingroup$ 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. $\endgroup$
    – mincorp
    Commented Apr 16, 2019 at 13:27
1
$\begingroup$

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.

Improve this answer
$\endgroup$
1
$\begingroup$

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.

This article about dbplyr 1.4.0 shows how smart the package has become .

Improve this answer
$\endgroup$
0
$\begingroup$

There are packages like RODBC which allow to get your dataset directly from the connections, There are other packages and ways to do it, but I think this proves the point that is possible to integrate querying with analysis.

RODBC

Improve this answer
$\endgroup$
4
  • $\begingroup$ Yes I am familiar with that fact that it is possible. I was actually moreso curious to hear how you guys do things in your various companies. I'm working on a project and I'd like to make it as close to realistic as possible. Thank you kindly for your reply. $\endgroup$
    – Nicklovn
    Commented Apr 16, 2019 at 13:20
  • 1
    $\begingroup$ That's the way is done. I just finished a project in my company using exactly this method. In this context I would add that sometimes, we don't connect to raw data but what we do is to make the query so we obtain the data ready to be analysed. For example: We didn't query for every transaction, we looked to summarise the data for every client in SQL and then modelling it. $\endgroup$
    – Juan Esteban de la Calle
    Commented Apr 16, 2019 at 13:40
  • $\begingroup$ That's awesome. I appreciate the response. I actually just used a different package called RMySQL which reads data in as a dataframe. it's very cool. It's great to see your perspective. $\endgroup$
    – Nicklovn
    Commented Apr 16, 2019 at 13:45
  • $\begingroup$ @JuanEstebandelaCalle is totally right about summarising on the DB. Using the storage layer to aggregate as fully as reasonable before the data is in your R session is really useful. $\endgroup$
    – DaveRGP
    Commented Apr 29, 2019 at 9:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.