如果只能在本地机器上操作,如何最好地使用R和SQL?

23

我试图改进我的工作流程,希望社区能提供见解,因为我相对于“大数据”还比较新。

通常我会从公共来源下载几个互相关联的数据框。在拉出多个数据框之后,在对我的最终数据集进行回归分析和/或其他推断统计之前,我会执行各种数据处理步骤(例如过滤、排序、聚合、自定义计算)。

具体来说,你推荐哪种策略:

  1. 从Web下载所有数据作为单独的文件到本地机器上,然后直接使用R进行处理(就像我一直在做的那样)。我看到这个潜在的问题是:所有的东西都在R的工作环境中,这可能会拖慢处理速度并使我的机器崩溃。

或者

  1. 从Web下载所有数据作为单独的文件到本地机器上,使用sqldf在本地机器上创建一个数据库,并使用sqldf的跟进查询从该数据库中提取和汇总信息,然后在R中进行最终数据分析。我看到这个潜在的问题是:用sqldf在我的本地机器上创建由少数表/数据框组成的数据库,比简单地保存几个独立的.csv文件要大。

我对统计技术非常熟悉,但是当涉及到数据库管理和服务器操作时,我承认有一些知识漏洞。我已经熟悉了SQL作为一种语言的底层细节,并且知道如何在R工作环境中使用sqldf与数据框进行交互。但是,我不知道这相比于学习如何使用基本的R函数来过滤、排序和聚合数据会带来什么优势。此外,我阅读了一些关于将SQL Server与R配对的炒作页面,但由于我在本地运行所有内容,因此不确定这是否适合我。

对于这位新手如何通过将R与某种实现SQL的方法结合起来来改进我的数据处理和分析,有什么提示吗?

提前感谢您!

3个回答

9
鉴于您正在寻找“工作流程最佳实践”,对于可重复性透明度应给予高度重视。由于您的目标是数据分析而不是数据收集或数据库管理,因此没有充分的理由创建您自己的数据库,自定义数据库可能会使您的工作流程和分析更加不透明。简而言之,如果您不需要构建数据库,请勿这样做。
听起来您的工作流程如下:
1. 从公共来源下载数据(最好是.csv或类似的格式) 2. 清理和处理数据 3. 对(可能链接的)已清洁的数据运行分析
我建议将您的工作流程划分为两个不同的步骤:

1. 下载和清理数据

如果您的文件都是 .csv(或其他常规分隔符文件),则只需要使用data.table包即可完成此步骤。您可以编写单个R脚本来下载、清理和保存所需的数据。以下是一个最小示例:
# Download data
library(data.table)
salary_data <- fread('https://data.phila.gov/api/views/25gh-t2gp/rows.csv')

# Clean data (only looking at City Council salaries)
cleaned_data <- salary_data[Department == 'CITY COUNCIL']

# Saving cleaned data
save(cleaned_data, file = 'my_file_name.rda', compress = TRUE)

理想情况下,您只需要运行一次该文件即可生成您实际执行统计分析的数据集。如果您决定以不同的方式清洗或处理数据,请返回此文件,进行适当的更改并重新运行它。建议为每个要下载的文件编写一个脚本,以便轻松查看从源头直接处理原始数据的方式(透明性)。仅拥有此文件即可满足可重复性

2. 统计分析

如果您需要合并数据集,data.table 提供了一种快速而透明的方法。只需加载已清理的各个数据集,确定要用于合并它们的键,然后将它们合并。然后在合并的数据集上运行您的分析。以下是此功能的示例:
# dt1 has salary information for 10 people and dt2 
# has the number of kids for the same 10 people
library(data.table)
dt1 <- data.table(id = 1:10, salary = sample(0:100000, 10)
dt2 <- data.table(id = 1:10, kids = sample(0:5, 10)
save(dt1, file = 'dt1.rda', compress = TRUE)
save(dt2, file = 'dt2.rda', compress = TRUE)

# Loading and merging data
load(file = 'dt1.rda')
load(file = 'dt2.rda')
setkey(dt1, id)
setkey(dt2, id)
merged_dt <- merge(dt1, dt2)

# Doing regression analysis on merged data
reg <- lm(salary ~ kids, data = merged_dt)

这使得合并过程和后续分析变得透明可重复

总结

该过程确保您的数据源、数据清理/处理和分析有良好的文档记录,透明且可重复。此外,该过程可以随着计算机而扩展。如果您不需要构建数据库,则不要构建。

如果数据对我的计算机来说太大怎么办?如果需要更多空间,只需在专用服务器或亚马逊 Web 服务机器上运行已经编写的代码。

如果数据对专用服务器来说太大怎么办?很可能数据存储在实际数据库中,工作流程中唯一更改的部分是数据下载和(可能)部分处理将成为 SQL 查询到数据库(最可能使用运行 R 中的 SQL 查询的 DBI 包),然后应该足够小以在本地或专用服务器上运行。

如果我的数据太大怎么办?您可能应该考虑更重型的大数据语言,如 Hadoop。

补充说明:如果您的数据不是常规分隔格式(如 Excel、SAS 或 Stata 文件),那么我建议使用tidyverse包中的download_file()函数(具有读取这些不太愉快但常见文件的出色功能)。

library(tidyverse)
taxi_data_excel <- download.file(url = 'http://www.nyc.gov/html/tlc/downloads/excel/current_medallion_drivers.xls', destfile = 'taxi_data_excel.xls')
taxi_data <- read_excel('taxi_data_excel.xls')

然后像往常一样进行清洁。

如果可以的话,我会给这个答案加上+2。是的,除非你真的必须使用sqldf,否则请不要使用它:当你有SQL经验时,强大的data.table相当简单易学,并提供了更多可能性。将数据表写入文件是一个非常灵活和高效的替代方法。 - Jealie

6
首先要明确的是,sqldf不是一个数据库,而是一个允许你在SQL语法中操作data.frame对象的包。确切地说,它使用后端的SQLite,但你不应该把sqldf包当作一个数据库。 sqldf是一个好用且方便的包。在某些情况下,它可能也很有效,但效率不是其主要目标。我建议你考虑一下data.table包。它专为效率而设计,性能可能会让你惊喜。
选择策略的第一个和主要建议如下:尊重权衡因素!在R中部署实际的SQL数据库可能会给你带来巨大优势,但它会增加开发过程的显著开销。这完全取决于项目范围。没有通用的规则,但我可以尝试指出一些经验之谈。
  • 默认情况下,我会尽量避免涉及SQL数据库,除非我遇到了特定于项目的SQL参数。

  • 如果瓶颈在RAM上,而R仅需处理汇总数据,则你确实应该考虑使用SQL数据库。例如,MySQL将处理分页、缓存和多线程-这可能是重要的参数。

  • 如果不同来源的数据结构存在显着差异,则使用SQL将增加额外开销,因为你必须在R和SQL中进行管理-要避免这种情况。另一方面,如果有很多具有相同数据结构的源,则数据库会给你带来良好的改进。

  • 如果你只需要处理源数据,则处理文件就可以了。但如果你需要反复运行并保存所有输出、更改、版本等,则数据库成为必需。

这只是我的个人意见。

澄清一下,我不认为sqldf是一个数据库。 - Jayden.Cameron
我正在权衡在r中使用sqldf包创建本地数据库的成本/收益,并继续使用sqldf通过SQLite语法来查询存储在该数据库中的数据,而不是使用内置的r函数(例如mergeaggregatewithby和其他函数)。您的项目列表帮助我更好地了解使用数据库的一些权衡,非常感谢。然而,我并不完全理解您对data.table包的推荐如何适用。 - Jayden.Cameron
正如Vyga所说,sqldf既不是数据库,也不能让你在本地机器上“创建数据库”。该软件包仅允许您查询R中的数据框。Data.table软件包是在R中使用数据框的替代方案。它专为效率和易用性而设计。我认为Vyga只是暗示您可以将其与R(带或不带DB)的任一选择一起使用,而不是使用数据框。 - AYR
@Jayden.Cameron,你需要注意 data.table 是一个用于数据操作的包,可以进行像 mergesortaggregate 等操作。这是一种替代方法,有时候会非常有效。此外,你还可以尝试使用 ddply 包作为很好的 by 函数替代方案。 - Vyga

5
这在很大程度上取决于你的环境基础设施,但在“大数据”世界中,我建议使用两者,因为它们各自具有难以放弃的优点。
大多数数据清洗和操作可以在两个平台上执行,但有些会牺牲性能,有些则会消耗资源。 内存: R的环境大多位于RAM内部。这样更快,但并不总是必要的。如果你有一个100GB的数据集,将其加载到RAM中是不可行的。大多数数据库已经引入了内存表,因此,如果有特定的表需要更快的访问,则始终可以将它们加载到RAM中。 索引和分区: 在数据库上对已经高效索引和分区的数据进行查询比通过CSV文件容易得多。大多数探索性分析都是在数据的分区或集群上完成的,放弃这一点是巨大的性能折衷。 下载和存储: 在R中编写用于下载数据并将其上传到数据库的脚本非常容易。在数据库中,数据可以更轻松地存储以便快速访问,并且可以高效地压缩以提高性能和可扩展性。 表视图: 有许多数据集或数据集的基本操作您可能希望存储以供以后使用。在数据库中,您可以利用表视图,这些视图可以跨任意数量的表连接和操作数据。为了在R中获得相同的结果,您必须加载所有相关表并每次访问相同的数据时执行合并和操作。 分析: 这就是R构建的目的。许多数据库甚至无法执行最基本的分析,因此我会将所有统计分析留在R中。
我相信还有许多可以在R和数据库之间进行比较的优点/缺点。如果您只是为了娱乐而处理少量数据,则可以完全使用R。否则,请同时使用两者。它更容易、更快速,也更舒适。

1
非常感谢您的反馈。然而,作为一个新手,我对您最后的评论有所保留;我不认为在使用数据库和 r 之间来回切换是容易、快速或舒适的,我也不完全理解它们如何有效地结合使用。您提到使用 r 可以下载数据并将其上传到数据库中,在那里可以进行高效的压缩以实现更好的性能和可扩展性。怎么做呢?使用 sqldf,我可以将数据库“ATTACH”到我的本地机器上,然后可以继续在 r 中使用它,但这不是一个小文件,我认为这可能是低效的。 - Jayden.Cameron
在数据库和R之间没有来回切换。把数据库看作你的文件存储系统,把R看作你的平台。你可以利用RODBC包连接到数据库,并使用sqlSave函数将数据保存到数据库中(新表、追加等)。我不是在谈论使用sqldf。那是专门用于使用SQL查询R中的数据对象,而不是查询数据库。 无论问题和环境如何,如果你真的想在这个领域取得进展,我强烈建议你熟悉基本的数据库知识。你可以很容易地在本地设置一个MySQL数据库。 - AYR
我同意你的观点,我需要增加我的基本数据库知识。恐怕由于我缺乏数据库知识,我的初始问题可能无法得到100%令我满意的答案。我已经学习了Coursera关于SQL的课程,它教会了我如何使用各种查询。我已经学会了如何使用sqldfr中的数据框上使用这些查询。然而,我仍然不知道如何在本地创建数据库。我会采纳你的建议,开始熟悉MySQL。如果您有更多的教学工具可以推荐,我将不胜感激。 - Jayden.Cameron

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接