Go to Qiita Advent Calendar 2024 Top
LoginSignup
44
51

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@pollseedin株式会社ブレインポート

【R言語】Rの始め方とMySQLとの連携

Last updated at Posted at 2014-10-01

#Rについて
統計解析向けのプログラミング言語として自分のサービスに組み込むことにした。
パフォーマンスの面から、IO処理が過多な環境では使えないので管理画面で主に活用することとする。
要は、グラフとか見たり機械学習させたりしたいわけですね。今の仕事（プロジェクト）ではただデータの分析にしか使ってないのでDBアクセスを踏まえて今回はやってみることとします。
まだ面白いことはかけません。

Rのインストール

##本体のインストール
R本体のミラーサイト
R Studio

brew

brewでインストールした場合には、以下のMySQLインストール時のエラーは出ませんでした。

.sh
brew tap homebrew/science
brew install r
brew cask install xquartz
brew cask install rstudio

##ライブラリ

###R Studioを起動し、MySQLのライブラリをインストールします

> install.packages("DBI")
> install.packages("RMySQL")

###エラーがでてしまいました。
どうやらバージョンを指定してやる必要があるみたいです。バージョン指定でインストールしましょう。

  package ‘RMySQL’ is available as a source package but not as a binary

Warning in install.packages :
  package ‘RMySQL’ is not available (for R version 3.1.1)

###さらにエラーが発生

INSTRUCTIONS:

1. Define and export the 2 shell variables PKG_CPPFLAGS and
   PKG_LIBS to include the directory for header files (*.h)
   and libraries, for example (using Bourne shell syntax):

      export PKG_CPPFLAGS="-I<MySQL-include-dir>"
      export PKG_LIBS="-L<MySQL-lib-dir> -lmysqlclient"

   Re-run the R INSTALL command:

      R CMD INSTALL RMySQL_<version>.tar.gz

2. Alternatively, you may pass the configure arguments
      --with-mysql-dir=<base-dir> (distribution directory)
   or
      --with-mysql-inc=<base-inc> (where MySQL header files reside)
      --with-mysql-lib=<base-lib> (where MySQL libraries reside)
   in the call to R INSTALL --configure-args='...' 

   R CMD INSTALL --configure-args='--with-mysql-dir=DIR' RMySQL_<version>.tar.gz

###以上の指示を試行

.bash_profile
〜〜〜
# R lang
PKG_CPPFLAGS=-I/usr/local/mysql/include
PKG_LIBS=-"L/usr/local/mysql/lib -lmysqlclient"
export PATH=$PKG_CPPFLAGS:$PATH
export PATH=$PPKG_LIBS:$PATH
〜〜〜

$ wget http://cran.r-project.org/bin/macosx/contrib/3.1/RMySQL_0.9-3.tgz
$ R CMD INSTALL RMySQL_0.9-3.tgz
* installing to library ‘/Library/Frameworks/R.framework/Versions/3.1/Resources/library’
* installing *binary* package ‘RMySQL’ ...
* DONE (RMySQL)

無事にインストールできました。良かった。ハマリポイントなのかよくわからんが、依存関係でたまにこういうことあったらこんな感じでやるってことね。指示が丁寧すぎてRって超親切な言語なんだなと感じた。

#それではインストールされてるか確認

##DBI

> library(help='DBI')

		 パッケージ ‘DBI’ の情報 

 記述: 

Package:            DBI
Version:            0.3.1
Title:              R Database Interface
Author:             R Special Interest Group on Databases (R-SIG-DB)
Maintainer:         Hadley Wickham <hadley@rstudio.com>
Depends:            R (>= 2.15.0), methods
Suggests:           testthat, RSQLite
Description:        A database interface (DBI) definition for communication between R and relational database management
                    systems.  All classes in this package are virtual and need to be extended by the various R/DBMS
                    implementations.
License:            LGPL (>= 2)
URL:                https://github.com/rstats-db/DBI
BugReports:         https://github.com/rstats-db/DBI/issues
Collate:            'DBObject.R' 'DBConnection.R' 'DBDriver.R' 'DBResult.R' 'compliance.R' 'keywords.R' 'quote.R' 'util.R'
Packaged:           2014-09-23 21:42:02 UTC; hadley
NeedsCompilation:   no
Repository:         CRAN
Date/Publication:   2014-09-24 07:27:12
Built:              R 3.1.1; ; 2014-09-25 04:54:34 UTC; unix

 索引: 

DBIConnection-class     DBIConnection class.
DBIDriver-class         DBIDriver class.
DBIObject-class         DBIObject class.
DBIResult-class         DBIResult class.
SQL                     SQL quoting.
dbClearResult           Clear a result set.
dbColumnInfo            Information about result types.
dbConnect               Create a connection to a DBMS.
dbDataType              Determine the SQL data type of an object.
dbDisconnect            Disconnect (close) a connection
dbDriver                Load and unload database drivers.
dbExistsTable           Does a table exist?
dbFetch                 Fetch records from a previously executed query.
dbGetException          Get DBMS exceptions.
dbGetInfo               Get DBMS metadata.
dbGetQuery              Send query, retrieve results and then clear
                        result set.
dbGetRowCount           The number of rows fetched so far.
dbGetRowsAffected       The number of rows affected by data modifying
                        query.
dbGetStatement          Get the statement associated with a result set
dbHasCompleted          Has the operation completed?
dbIsValid               Is this DBMS object still valid?
dbListConnections       List currently open connections.
dbListFields            List field names of a remote table.
dbListResults           A list of all pending results.
dbListTables            List remote tables.
dbReadTable             Copy data frames to and from database tables.
dbRemoveTable           Remove a table from the database.
dbSendQuery             Execute a statement on a given database
                        connection.
dbiCheckCompliance      Check a driver for compliance with DBI.
make.db.names           Make R identifiers into legal SQL identifiers.
transactions            Begin/commit/rollback SQL transactions

##RMySQL

> library(help='RMySQL')

		 パッケージ ‘RMySQL’ の情報 

 記述: 

Package:            RMySQL
Version:            0.9-3
Date:               2012-01-17
Title:              R interface to the MySQL database
Author:             David A. James and Saikat DebRoy
Maintainer:         Jeffrey Horner <jeffrey.horner@gmail.com>
Description:        Database interface and MySQL driver for R.  This version complies with the database interface definition
                    as implemented in the package DBI 0.2-2.
LazyLoad:           true
Depends:            R (>= 2.8.0), methods, DBI (>= 0.2-2), utils
License:            GPL-2
URL:                http://biostat.mc.vanderbilt.edu/RMySQL, https://github.com/jeffreyhorner/RMySQL
Collate:            S4R.R zzz.R MySQLSupport.R dbObjectId.R MySQL.R
Packaged:           2012-01-17 16:38:02 UTC; hornerj
Repository:         CRAN
Date/Publication:   2012-01-17 18:10:41
Built:              R 3.1.0; x86_64-apple-darwin10.8.0; 2013-10-24 17:09:06 UTC; unix

 索引: 

MySQL                   Instantiate a MySQL client from the current R
                        session
MySQLConnection-class   Class MySQLConnection
MySQLDriver-class       Class MySQLDriver
MySQLObject-class       Class MySQLObject
MySQLResult-class       Class MySQLResult
RMySQL-package          R interface to the MySQL database
SQLKeywords-methods     Make R/S-Plus identifiers into legal SQL
                        identifiers
dbApply                 Apply R functions to remote groups of DBMS rows
                        (experimental)
dbApply-methods         Apply R/S-Plus functions to remote groups of
                        DBMS rows (experimental)
dbBuildTableDefinition
                        Build the SQL CREATE TABLE definition as a
                        string
dbCallProc-methods      Call an SQL stored procedure
dbCommit-methods        DBMS Transaction Management
dbConnect-methods       Create a connection object to an MySQL DBMS
dbDataType-methods      Determine the SQL Data Type of an S object
dbDriver-methods        MySQL implementation of the Database Interface
                        (DBI) classes and drivers
dbEscapeStrings         Escape SQL-special characters in strings
dbEscapeStrings-methods
                        Escape a Character Vector According to SQL
                        rules
dbGetInfo               Database interface meta-data
dbListTables-methods    List items from an MySQL DBMS and from objects
dbNextResult            Fetch next result set from an SQL script or
                        stored procedure (experimental)
dbNextResult-methods    Fetch Next Result Set from Multiple Statements
                        or Stored Procedures
dbObjectId-class        Class dbObjectId
dbReadTable-methods     Convenience functions for Importing/Exporting
                        DBMS tables
dbSendQuery-methods     Execute a statement on a given database
                        connection
dbSetDataMappings-methods
                        Set data mappings between MySQL and R/S-Plus
fetch-methods           Fetch records from a previously executed query
isIdCurrent             Check whether a database handle object is valid
                        or not
mysqlClientLibraryVersions
                        MySQL Check for Compiled Versus Loaded Client
                        Library Versions
mysqlDBApply            Apply R/S-Plus functions to remote groups of
                        DBMS rows (experimental)
mysqlInitDriver         Support Functions
summary-methods         Summarize an MySQL object

とりあえず、パッケージの情報が出てきていたら問題ないでしょう。

#検索の仕方とMySQLとの連携
基本的に、補完機能が優れているので迷うことはありません。

sample.r

library(DBI)
library(RMySQL)

test_table.test1 <- dbGetQuery(dbConnect(dbDriver("MySQL"), dbname="test_schema", user="root", password="password"), "select * from test_schema.test_table where test = 1;")

View(test_table.test1)

RMySQLのwikiを参考にした。基本的にはこちらの記述で取得可能。

##データを操作

sample.r
y <- test_table.test1$id
plot(y)

このソースでは、test_tableテーブルのtest1変数からidカラムにアクセスしてそれをy変数に代入しています
plot(y)という記述により、簡単な図を表示することができます。確認程度に使えます。データがおかしかったりするとすぐにエラーを吐いてくれるので非常に楽ですね。

ここから、データ分析や機械学習をすることができる。
時間を見つけてその辺りも更新出来たらいいなと思う。

44
51
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
44
51

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?