今天參加了一個SQL的Workshop,本來早上九點實在是對我有點早想着不來的,不過發現并沒有用過AWS,就還是來看一看。這裡是這次Workshop的Github連接配接:Learning Analytics Curriculum & Teaching
詳情還是參見Github,這裡僅為簡單羅列重點内容,有問題可以私信!
用R連接配接的好處就是可以直接在R裡面操作資料庫并進行資料分析,不需要到處.csv的中間檔案,然後再加載進R的工作空間,也算是效率上的小提升吧!
擁有一個Amazon Web Services(AWS)賬号
注冊位址:https://aws.amazon.com/
*注:需要信用卡,但是以下操作皆免費,也許需要科學上網。
在AWS上建立一個MySQL的執行個體
- 登入你的AWS賬号
- 找到
→ \rightarrow →Services
→ \rightarrow →Database
RDS
- 點選
Create Database
-
下選擇Choose a database creation method
Standard Create
-
裡選擇Engine options
MySQL
-
選擇Templates
Free tier
-
中設定Settings
,如DB instance identifier
sqltest
-
下的Settings
中設定Credential settings
和username
password
- 将Under
中的Connectivity
裡的Additional connectivity configuration
勾選為Publicly accessible
Yes
- 在
裡設定Additional configuration
,如database name
testdb
- 不要勾選
Automatic backups
- 點選
Create database
設定Security Group
- 在
裡點選Security Groups
然後點選Inbound
Edit
- 點選
選擇Add Rule
修改SQL/Aurora
為Source
My IP
用R連接配接AWS上的資料庫
- DBI: A database interface 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.
- RMySQL: Legacy ‘DBI’ interface to ‘MySQL’ / ‘MariaDB’ based on old code ported from S-PLUS. A modern ‘MySQL’ client based on ‘Rcpp’ is available from the ‘RMariaDB’ package.
library(DBI)
library(RMySQL)
db_user <- 'admin'
db_password <- 'your_password'
db_name <- 'testdb'
# 這個是RDS=>Databases=>Connectivity&security裡面的Endpoint
db_host <- 'sqltest.c24zvetb1ohx.us-east-2.rds.amazonaws.com'
db_port <- 3306
mydb <- dbConnect(MySQL(), user = db_user, password = db_password, dbname = db_name, host = db_host, port = db_port)
summary(mydb)