SQL and R

R平台及编程语言支持浩大的数据科学技术，他拥有几十年的的历史和超过7000个包，这挂在CRAN的包纷杂的让你无法决定从哪里入手。R-Basics和Visualizing Data with R提供了基础的指导，但是没有详细介绍如何用R操作数据集。

幸运的是，数据库专业人员可以通过他们的精湛的SQL技术，短时间内在这个领域变得更有效率。如你所愿，R支持使用SQL检索中心位置的关系数据库中的数据。然而，一些R包允许你超出这领域创建介于处理和分析数据之间的集席数据集的飞速查询，而不管数据的来源和最终目标。在这文章，我们将会看到一些使用不同R包，来通过SQL处理数据的方法。

微软收购Revolution Analytics太让人兴奋了，这随后会导致R被纳入SQLServer 2016.。SQLServer 2016有预览功能，但是它在发布之前仍然会有修改，Simple-talk充斥着大量聚焦SQLServer的优秀文章。一旦这发布细节被确定下来，你可以期待更多后续的声明R在SQLServer的应用的文章。但是由于现在最终版本尚不可用，Simple-Talky已经通过SQL Server Access from R做了介绍，这文章将展示开源的R在Rstudio环境上使用SQL和其他的关系数据库。

SQL的部分吸引人的地方在于他能在许多不同产品上应用。大量的技术专业人员发现他们必须支持和交互化大范围的开源和商业化技术。除了非开源有特定性能和拓展，SQL实现跨越比较标准，所以它的使用将在这里作为典型将会被目前只使用SQLServer的人熟悉。这文章将会包含两个数据库介绍，SQLite（一个小的免费使用的数据库，其中有不少使用者甚至不了解它及Oracle），Oracle 一个巨大的数据库等供应商、SQLServer的竞争对手。RODBC的使用已经被Simple-Talk的文章–‘Making Data Analytics Simpler: SQL Server and R’所涵盖

RSQLite包

SQLite是世界上部署最广泛的数据库。它是包含在Android，iPhone和iOS设备，也在Firefox，Chrome和Safari网络浏览器。苹果和微软分别在他们的OSX和Windows10操作系统上使用了SQLite，并且有许多其他产品应用了SQLite。它非常容易使用，对这些需要一个数据库可用，但要避免经常安装和配置外部数据库相关的开销的开发商有很大的价值。在本演示中，我们将下载并安装RSQLite包–将SQLite的集成到RStudio上运行的R的工具。

这例子将展现怎么使用我们熟悉的SQL语句把数据集完美的写在SQLite数据库用于随后的分析，这些数据是关于汽车性能和燃油效率问题的。这数据集包含于初始安装R时。由于被包含的数据在R可用，这就没有必要去从分开的表格或者外部来源导入。这样的数据集的使用保存在R文件示例；所以他们是在R安装时或者在新包导入时伴随代码而添加上来的。

在“mtcars”数据集默认于R：它包括1974年的Motor Trend US杂志的数据（因此在数据集名“MT”）包括在内。数据集描述了32类机动车的燃料消耗和汽车设计、表现等各个方面和性能的关系。帮助功能将展示包含深入此数据集的结构和内容的描述文档。

为了访问该数据集，它必须先被加载。这将“黏贴”数据集到用户的当前R会话。

数据集是指代一种为由行和列组成的框。数据帧是足够小，可以使用视图命令以一个类似电子表格形式显示。

如果你想在不关闭R的前提下从对话中移除数据集来释放资源。你可以使用rm函数。当你运行这命令，你将注意到环境变量中的mtcar变量列表消失。

在Rstudio中，SQLite包必须安装（除非它早先已经安装）。可以调用该库函数把包载入当前的工作环境。

接下来，我们会创建一个新的、空的SQLite数据库用于存储汽车数据。SQLite有一个相当简单的数据存储机制，所有数据库数据存储在单一的文件中。当数据库创建时这个文件名字必须特殊化，并且返回一个这个数据库连接用于后续的访问、操作数据和数据结构的命令。

这个命令在当前工作目录创建一个叫做“mycars.db”的文件。如果你不能确定在那个位置，你可以使用getwd（）函数来获取工目录，或者setwd(‘目录路径’）来指定一个不同的工作目录。去真正创建一张表，我们将会从mtcar数据集读取数据并写入新的数据库。

用加载的数据，和一个活动数据库连接到SQLite数据库，我们就可以通过指定的连接、表的名称、以及包含要永久保存的数据的数据帧的名称来写入数据。

这个简单的语句在数据库中创建了一张数据类型类似R数据框的列的表。表列的名称是基于在数据框中的列的名称。没有复杂的CREATE TABLE语句要求与列名的明确的定义及数据类型、精度、存储配置或其他选项。当聚焦执行临时探索性数据分析时这种细节是不必要的，而没必要像在被供长期使用的集中数据库一样定义模式。然而如果你想使用标准的SQL DDL，它是可以运行的CREATE TABLE语句的。

SQLite的，像其它关系数据库存储描述它包含的对象的元数据。在数据库中的表可以通过调用一个函数列出。

同样地，通过一个给定的表名及连接可以列出字段名。

有了可用的连接、建好的数据库、并填充了数据的表，现在可以使用dbGetQuery功能执行查询。

标准的SQL语法是可用的，但如在SQL嵌在字符串其他情况下，你需要考虑你的引号的使用。往往最简单的是用双引号包围你的查询，以便在SQL语句字符串可以用单引号括起来。

如你所愿，也可在RSQLite使用SQL修改表。但是，如果你想要覆盖先前创建的表的话，就存在快捷方式。下面的例子中从car数据框行名中提取make列,其中行名中make,model是连接的。

该语句在着本质上是，“在叫’mtcars’的数据框上创建新的列并且使用行名填充每行值，查找子字符串从第一个空白开始到原来的字符串结束的位置,并且移除该子字符串。”剩下的是字符串的首个单词。这作为结果的数据框可以被查看，以显示添加上去新增列是作为最后列。

新增列可以和其他列一样用于查询。

这RSQLite包使取文件、表格及其他来源中数据变得简捷，并快速将其集成到SQL访问的数据库。它可以让你无需花费额外的时间、资源、精力去设置或者维护外部数据库就能够做数据处理大量

跟这一样方便的是，另一个叫sqldf的包进一步简化这种类型的处理。它允许你在没有丁点建立一个数据库的想法的条件下，在数据框上使用SQL。

sqldf 包

这在长期使用SQL（或类似SQL）的语言，探讨和处理数据有着巨大价值。数据科学专业人士常常面临着来自不同数据源的数据整合的挑战。其中许多是关系型数据库，所以需要SQL检索数据。此外，NoSQL的数据源往往支持高层次，描述性的，类似SQL的语言。例如，Hadoop的用户可以使用Hive和Pig。Cassandra提供了使用Cassandra的查询语言（CQL）访问存储在列族（类似于关系表）的数据的功能。在许多情况下，在任意的文本文件的数据，被结构化得足够容易地导入到数据库，以及各种实用程序通常用于使半结构化数据的SQL可访问化。从关系术语角度思考数据使数据整洁、格式化的意义甚至超越了相关领域。

该sqldf包允许您访问使用SQL数据帧。无论在哪里的原始数据，只要其包含在数据框中就可以查询。这意味着数据可以从各种数据源（分隔的文件，一个网页，网页的API，一个关系数据库，NoSQL的datasoures等）读入，并随后查询和处理，就像它是在一个单一的关系数据库中。它是多么简单：打开一个新的R会话，安装包，加载和mtcars数据。

SQLDF允许查询数据框就好像它是一个表。并且它传递查询到sqldf函数就像传递一个字符串到sqldf函数一样简单。

如果你照着做并且在Rstudio运行该语句，这行数是对的，但是包含各种车名的行名丢失了。

这原因是行名不是标准的列，被默认的sqldf忽略了。使输出包含这些行，调用时指定 row.names= T.

在R中有许多方式去创建新的数据框–基本的语言包含一些支持函数，而且R包像dplyr,reshape是普遍用的。通过sqldf你可以绕过这些。实际上，这sqldf调用它自己会返回一个数据框。记住这些，你可以调用一系列的sqldf函数来逐步处理或汇总的数据集。

这df对象包含查询结果的数据框。如果你将通过这种方式处理数据框，你最好把一列普通值作为行名。

新的列是在数据框可以找到。并且任何查询结果，甚至它的原始数据非常广泛都是返回一个新的数据框。

合理地给数据集的列使用别名会使后续处理更加方便。建议限制列名长度。

sqldf上的SQL会在幕后映射成在RSQLite的SQL，数据将会写入SQLite进行查询。R内部的mtcars数据集很方便做例子。尽管用在展示或者学习功能上非常快，但是它对于必须从外部检索数据的实际应用还不够。

文件导入

在看制作直接链接到数据库之前，认识到读取分隔的文件到RStudio是多么简单和直接是非常重要的。这可能是有点冒犯那些习惯于创建使用ODBC或JDBC直接连接到数据库的应用程序的软件开发人员。但R用户经常需要将来自几个不同的数据源的数据集成。与其花费时间和精力配置特定的软件包并加载驱动程序，从查询到数据文件导出数据和文件读入RStudio是值得考虑的。这种做法也可以规避需要一个数据库运行资源密集型的SQL语句多次。数据导出为CSV是许多关系型数据库系统的良好支持的选项。 SQLServer的的Management Studio中有一个“结果到文本”对话框，“逗号分隔”可以指定为输出格式。 MySQL有一个非标准的SQL SELECT子句指定OUTFILE条款。许多SQL客户有以这种方式将数据导出选项。从数据库导出CSV的可使用任何电子表格程序进行快速验证。 R本身可以从各种文件格式导入数据。这种灵活性导致额外的复杂性并崔生大量的针对性的函数，其中许多具有大量的可设定参数，以改变它们的行为。 RStudio掩盖这种复杂性，并提供了导入文件的简单对话。如果你没有一个得心应手的CSV文件，您可以基于我们前面看到的mtcars数据集通过R创建一个。

导入这个可以通过选择环境变量栏“Import Dataset”项操作并选择“From Text File”。

A dialog is opened that provides a preview of how the data will be imported based on the options selected.In most cases, uncheck stringsAsFactors and the

一个演示数据怎么手动导入的预览对话框被打开。在大多数情况下，取消stringsAsFactors并

选择默认就足够了。

对于导入的数据，时间R命令要求去读取并且在console上预览。这数据是先读进R，预览命令时之后调用，作用于新导入的数据并展示它的目录。

如果你愿意，read.csv命令可以被搁置，并在脚本中使用，从而不必在将来的对话框中使用交互方式导入数据。由于数据通常可以方便地导出到简单的文本文件，他们经常是将数据放入RStudio最简单的方法，然而这并不是理所当然的。有时，当将要处理的关系数据库中的数据量大的令人不敢问津，或将要创建的数据帧的数量大得使手动导入导出的多个数据文件很繁琐笨重。在这些情况下，对数据库的直接连接是最好的选择。有大量的数据库专向的包支持直接连接，这些包中绝大部分都是基于RJDBC包，RJDBC包可以独立使用以访问大量类型的数据库。

★知识大分享★

全部都是电子书，根据书名点击进去即可进入下载页面，不用谢我，请叫我“雷锋”！

但，首先您要点击“阅读原文”才能进入合辑页面下载！

原文始发于微信公众号（PPV课数据科学社区）：SQL and R