docker run –rm –name some-mysql -p 3306:3306 -v $(pwd)/docker_mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=pythonetl mysql:5.7
docker run -d –name some-mysql -p 3306:3306 -v $(pwd)/docker_mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=pythonetl mysql:8.0 mysqld –default-authentication-plugin=mysql_native_password
ALTER USER 'yourusername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'youpassword';
CREATE DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;#install.packages('dplyr')
library(dplyr)## Warning: package 'dplyr' was built under R version 3.5.2##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union#install.packages('RMariaDB')
#install.packages('dbplyr')
library('dbplyr')## Warning: package 'dbplyr' was built under R version 3.5.2##
## Attaching package: 'dbplyr'## The following objects are masked from 'package:dplyr':
##
## ident, sqllibrary('RMariaDB')
data("iris")
##iris example
conn = dbConnect(MariaDB(),dbname='test',host='127.0.0.1',port=3306,user='root',password='pythonetl')
db_drop_table(conn,'iris')## [1] 0copy_to(conn,iris,temporary = F)
tbl(conn,"iris") %>%
select(starts_with('Sepal'),'Species') %>%
group_by(Species) %>%
summarise_at(.funs=funs(mean(.,na.rm=T),sd(.,na.rm=T)),.vars=vars(starts_with('Sepal'))) %>%
collect()## Warning: funs() is soft deprecated as of dplyr 0.8.0
## please use list() instead
##
## # Before:
## funs(name = f(.)
##
## # After:
## list(name = ~f(.))
## This warning is displayed once per session.## # A tibble: 3 x 5
## Species Sepal.Length_me… Sepal.Width_mean Sepal.Length_sd Sepal.Width_sd
## <chr> <dbl> <dbl> <dbl> <dbl>
## 1 setosa 5.01 3.43 0.352 0.379
## 2 versico… 5.94 2.77 0.516 0.314
## 3 virgini… 6.59 2.97 0.636 0.322dbGetQuery(conn,'select * from iris') %>% filter(Species == 'setosa')## Sepal.Length Sepal.Width Petal.Length Petal.Width Species
## 1 5.1 3.5 1.4 0.2 setosa
## 2 4.9 3.0 1.4 0.2 setosa
## 3 4.7 3.2 1.3 0.2 setosa
## 4 4.6 3.1 1.5 0.2 setosa
## 5 5.0 3.6 1.4 0.2 setosa
## 6 5.4 3.9 1.7 0.4 setosa
## 7 4.6 3.4 1.4 0.3 setosa
## 8 5.0 3.4 1.5 0.2 setosa
## 9 4.4 2.9 1.4 0.2 setosa
## 10 4.9 3.1 1.5 0.1 setosa
## 11 5.4 3.7 1.5 0.2 setosa
## 12 4.8 3.4 1.6 0.2 setosa
## 13 4.8 3.0 1.4 0.1 setosa
## 14 4.3 3.0 1.1 0.1 setosa
## 15 5.8 4.0 1.2 0.2 setosa
## 16 5.7 4.4 1.5 0.4 setosa
## 17 5.4 3.9 1.3 0.4 setosa
## 18 5.1 3.5 1.4 0.3 setosa
## 19 5.7 3.8 1.7 0.3 setosa
## 20 5.1 3.8 1.5 0.3 setosa
## 21 5.4 3.4 1.7 0.2 setosa
## 22 5.1 3.7 1.5 0.4 setosa
## 23 4.6 3.6 1.0 0.2 setosa
## 24 5.1 3.3 1.7 0.5 setosa
## 25 4.8 3.4 1.9 0.2 setosa
## 26 5.0 3.0 1.6 0.2 setosa
## 27 5.0 3.4 1.6 0.4 setosa
## 28 5.2 3.5 1.5 0.2 setosa
## 29 5.2 3.4 1.4 0.2 setosa
## 30 4.7 3.2 1.6 0.2 setosa
## 31 4.8 3.1 1.6 0.2 setosa
## 32 5.4 3.4 1.5 0.4 setosa
## 33 5.2 4.1 1.5 0.1 setosa
## 34 5.5 4.2 1.4 0.2 setosa
## 35 4.9 3.1 1.5 0.2 setosa
## 36 5.0 3.2 1.2 0.2 setosa
## 37 5.5 3.5 1.3 0.2 setosa
## 38 4.9 3.6 1.4 0.1 setosa
## 39 4.4 3.0 1.3 0.2 setosa
## 40 5.1 3.4 1.5 0.2 setosa
## 41 5.0 3.5 1.3 0.3 setosa
## 42 4.5 2.3 1.3 0.3 setosa
## 43 4.4 3.2 1.3 0.2 setosa
## 44 5.0 3.5 1.6 0.6 setosa
## 45 5.1 3.8 1.9 0.4 setosa
## 46 4.8 3.0 1.4 0.3 setosa
## 47 5.1 3.8 1.6 0.2 setosa
## 48 4.6 3.2 1.4 0.2 setosa
## 49 5.3 3.7 1.5 0.2 setosa
## 50 5.0 3.3 1.4 0.2 setosadbGetQuery(conn,'select `Sepal.Length` from iris')## Sepal.Length
## 1 5.1
## 2 4.9
## 3 4.7
## 4 4.6
## 5 5.0
## 6 5.4
## 7 4.6
## 8 5.0
## 9 4.4
## 10 4.9
## 11 5.4
## 12 4.8
## 13 4.8
## 14 4.3
## 15 5.8
## 16 5.7
## 17 5.4
## 18 5.1
## 19 5.7
## 20 5.1
## 21 5.4
## 22 5.1
## 23 4.6
## 24 5.1
## 25 4.8
## 26 5.0
## 27 5.0
## 28 5.2
## 29 5.2
## 30 4.7
## 31 4.8
## 32 5.4
## 33 5.2
## 34 5.5
## 35 4.9
## 36 5.0
## 37 5.5
## 38 4.9
## 39 4.4
## 40 5.1
## 41 5.0
## 42 4.5
## 43 4.4
## 44 5.0
## 45 5.1
## 46 4.8
## 47 5.1
## 48 4.6
## 49 5.3
## 50 5.0
## 51 7.0
## 52 6.4
## 53 6.9
## 54 5.5
## 55 6.5
## 56 5.7
## 57 6.3
## 58 4.9
## 59 6.6
## 60 5.2
## 61 5.0
## 62 5.9
## 63 6.0
## 64 6.1
## 65 5.6
## 66 6.7
## 67 5.6
## 68 5.8
## 69 6.2
## 70 5.6
## 71 5.9
## 72 6.1
## 73 6.3
## 74 6.1
## 75 6.4
## 76 6.6
## 77 6.8
## 78 6.7
## 79 6.0
## 80 5.7
## 81 5.5
## 82 5.5
## 83 5.8
## 84 6.0
## 85 5.4
## 86 6.0
## 87 6.7
## 88 6.3
## 89 5.6
## 90 5.5
## 91 5.5
## 92 6.1
## 93 5.8
## 94 5.0
## 95 5.6
## 96 5.7
## 97 5.7
## 98 6.2
## 99 5.1
## 100 5.7
## 101 6.3
## 102 5.8
## 103 7.1
## 104 6.3
## 105 6.5
## 106 7.6
## 107 4.9
## 108 7.3
## 109 6.7
## 110 7.2
## 111 6.5
## 112 6.4
## 113 6.8
## 114 5.7
## 115 5.8
## 116 6.4
## 117 6.5
## 118 7.7
## 119 7.7
## 120 6.0
## 121 6.9
## 122 5.6
## 123 7.7
## 124 6.3
## 125 6.7
## 126 7.2
## 127 6.2
## 128 6.1
## 129 6.4
## 130 7.2
## 131 7.4
## 132 7.9
## 133 6.4
## 134 6.3
## 135 6.1
## 136 7.7
## 137 6.3
## 138 6.4
## 139 6.0
## 140 6.9
## 141 6.7
## 142 6.9
## 143 5.8
## 144 6.8
## 145 6.7
## 146 6.7
## 147 6.3
## 148 6.5
## 149 6.2
## 150 5.9dbListTables(conn)## [1] "applenews" "iris"setwd('~/lecture/riii')
load('Statistics/applenews.RData')
str(applenews)## 'data.frame': 1500 obs. of 5 variables:
## $ content : chr "\n (更新:新增影片)想要透過刮刮樂彩券一夕致富，但他卻用錯方法！台中市一名"| __truncated__ "\n 澳洲一名就讀雪梨大學的華裔博士生，日前公開一段燒毀中國護照的影片，還"| __truncated__ "\n 【行銷專題企劃】房價高高在上，沒錢買房沒關係，但你認為自己是聰明的租"| __truncated__ "\n 本內容由中央廣播電臺提供 美國國防部長卡特(Ash Carter)今天(15日"| __truncated__ ...
## $ title : chr "【更新】搶2.2萬彩券刮中1.4萬 沒發財還得入獄" "拿到澳洲護照後 他放火燒中國護照" "【特企】房市大追擊- 租屋這些事情要小心" "【央廣RTI】美菲軍演 美防長南海登艦" ...
## $ dt : POSIXct, format: "2016-04-15 14:32:00" "2016-04-15 14:32:00" ...
## $ category: chr "社會" "國際" "地產" "國際" ...
## $ clicked : int 1754 0 0 0 311 24 20 314 27 308 ...applenews = applenews[,-1]
## applenews example
copy_to(conn,applenews,temporary = F,overwrite=T)
tbl(conn,"applenews")## # Source: table<applenews> [?? x 4]
## # Database: mysql 8.0.16 [root@127.0.0.1:/test]
## title dt category clicked
## <chr> <dttm> <chr> <int>
## 1 【更新】搶2.2萬彩券刮中1.4萬 沒發財還得入獄… 2016-04-15 22:32:00 社會 1754
## 2 拿到澳洲護照後 他放火燒中國護照 2016-04-15 22:32:00 國際 0
## 3 【特企】房市大追擊- 租屋這些事情要小心… 2016-04-15 22:31:00 地產 0
## 4 【央廣RTI】美菲軍演 美防長南海登艦 2016-04-15 22:30:00 國際 0
## 5 全球最閃牽手夫妻 絕美禮服出自台灣… 2016-04-15 22:28:00 時尚 311
## 6 公司遭搜索 浩鼎籲檢調勿公開商業機密… 2016-04-15 22:28:00 財經 24
## 7 【央廣RTI】每318秒就有1人罹癌 大腸癌名列第一… 2016-04-15 22:25:00 生活 20
## 8 垃圾掉滿地 村民請神明幫忙 2016-04-15 22:24:00 生活 314
## 9 【熊本強震】取消去九州 華航5月8日前退改票免手續費… 2016-04-15 22:24:00 生活 27
## 10 麵龜摻非工業色素 千顆不良品早下肚 2016-04-15 22:23:00 生活 308
## # … with more rowsdbSendQuery(conn, "SET NAMES utf8");## <MariaDBResult>
## SQL SET NAMES utf8
## ROWS Fetched: 0 [complete]
## Changed: 0tbl(conn,"applenews")## Warning in result_create(conn@ptr, statement, is_statement): Cancelling
## previous query## # Source: table<applenews> [?? x 4]
## # Database: mysql 8.0.16 [root@127.0.0.1:/test]
## title dt category clicked
## <chr> <dttm> <chr> <int>
## 1 【更新】搶2.2萬彩券刮中1.4萬 沒發財還得入獄… 2016-04-15 22:32:00 社會 1754
## 2 拿到澳洲護照後 他放火燒中國護照 2016-04-15 22:32:00 國際 0
## 3 【特企】房市大追擊- 租屋這些事情要小心… 2016-04-15 22:31:00 地產 0
## 4 【央廣RTI】美菲軍演 美防長南海登艦 2016-04-15 22:30:00 國際 0
## 5 全球最閃牽手夫妻 絕美禮服出自台灣… 2016-04-15 22:28:00 時尚 311
## 6 公司遭搜索 浩鼎籲檢調勿公開商業機密… 2016-04-15 22:28:00 財經 24
## 7 【央廣RTI】每318秒就有1人罹癌 大腸癌名列第一… 2016-04-15 22:25:00 生活 20
## 8 垃圾掉滿地 村民請神明幫忙 2016-04-15 22:24:00 生活 314
## 9 【熊本強震】取消去九州 華航5月8日前退改票免手續費… 2016-04-15 22:24:00 生活 27
## 10 麵龜摻非工業色素 千顆不良品早下肚 2016-04-15 22:23:00 生活 308
## # … with more rowscategory_stat = tbl(conn,"applenews") %>%
group_by(category) %>%
summarise_at(.funs=funs(min(.,na.rm=T),max(.,na.rm=T),mean(.,na.rm=T)), .vars=vars(matches('clicked'))) %>%
arrange(desc(mean)) %>%
collect()#install.packages("mongolite")
library("mongolite")
m=mongo(collection = 'test',db = 'test',url='mongodb://localhost:27017')
m$drop()
m$insert(iris)## List of 5
## $ nInserted : num 150
## $ nMatched : num 0
## $ nRemoved : num 0
## $ nUpserted : num 0
## $ writeErrors: list()m$count()## [1] 150m$find()## Sepal_Length Sepal_Width Petal_Length Petal_Width Species
## 1 5.1 3.5 1.4 0.2 setosa
## 2 4.9 3.0 1.4 0.2 setosa
## 3 4.7 3.2 1.3 0.2 setosa
## 4 4.6 3.1 1.5 0.2 setosa
## 5 5.0 3.6 1.4 0.2 setosa
## 6 5.4 3.9 1.7 0.4 setosa
## 7 4.6 3.4 1.4 0.3 setosa
## 8 5.0 3.4 1.5 0.2 setosa
## 9 4.4 2.9 1.4 0.2 setosa
## 10 4.9 3.1 1.5 0.1 setosa
## 11 5.4 3.7 1.5 0.2 setosa
## 12 4.8 3.4 1.6 0.2 setosa
## 13 4.8 3.0 1.4 0.1 setosa
## 14 4.3 3.0 1.1 0.1 setosa
## 15 5.8 4.0 1.2 0.2 setosa
## 16 5.7 4.4 1.5 0.4 setosa
## 17 5.4 3.9 1.3 0.4 setosa
## 18 5.1 3.5 1.4 0.3 setosa
## 19 5.7 3.8 1.7 0.3 setosa
## 20 5.1 3.8 1.5 0.3 setosa
## 21 5.4 3.4 1.7 0.2 setosa
## 22 5.1 3.7 1.5 0.4 setosa
## 23 4.6 3.6 1.0 0.2 setosa
## 24 5.1 3.3 1.7 0.5 setosa
## 25 4.8 3.4 1.9 0.2 setosa
## 26 5.0 3.0 1.6 0.2 setosa
## 27 5.0 3.4 1.6 0.4 setosa
## 28 5.2 3.5 1.5 0.2 setosa
## 29 5.2 3.4 1.4 0.2 setosa
## 30 4.7 3.2 1.6 0.2 setosa
## 31 4.8 3.1 1.6 0.2 setosa
## 32 5.4 3.4 1.5 0.4 setosa
## 33 5.2 4.1 1.5 0.1 setosa
## 34 5.5 4.2 1.4 0.2 setosa
## 35 4.9 3.1 1.5 0.2 setosa
## 36 5.0 3.2 1.2 0.2 setosa
## 37 5.5 3.5 1.3 0.2 setosa
## 38 4.9 3.6 1.4 0.1 setosa
## 39 4.4 3.0 1.3 0.2 setosa
## 40 5.1 3.4 1.5 0.2 setosa
## 41 5.0 3.5 1.3 0.3 setosa
## 42 4.5 2.3 1.3 0.3 setosa
## 43 4.4 3.2 1.3 0.2 setosa
## 44 5.0 3.5 1.6 0.6 setosa
## 45 5.1 3.8 1.9 0.4 setosa
## 46 4.8 3.0 1.4 0.3 setosa
## 47 5.1 3.8 1.6 0.2 setosa
## 48 4.6 3.2 1.4 0.2 setosa
## 49 5.3 3.7 1.5 0.2 setosa
## 50 5.0 3.3 1.4 0.2 setosa
## 51 7.0 3.2 4.7 1.4 versicolor
## 52 6.4 3.2 4.5 1.5 versicolor
## 53 6.9 3.1 4.9 1.5 versicolor
## 54 5.5 2.3 4.0 1.3 versicolor
## 55 6.5 2.8 4.6 1.5 versicolor
## 56 5.7 2.8 4.5 1.3 versicolor
## 57 6.3 3.3 4.7 1.6 versicolor
## 58 4.9 2.4 3.3 1.0 versicolor
## 59 6.6 2.9 4.6 1.3 versicolor
## 60 5.2 2.7 3.9 1.4 versicolor
## 61 5.0 2.0 3.5 1.0 versicolor
## 62 5.9 3.0 4.2 1.5 versicolor
## 63 6.0 2.2 4.0 1.0 versicolor
## 64 6.1 2.9 4.7 1.4 versicolor
## 65 5.6 2.9 3.6 1.3 versicolor
## 66 6.7 3.1 4.4 1.4 versicolor
## 67 5.6 3.0 4.5 1.5 versicolor
## 68 5.8 2.7 4.1 1.0 versicolor
## 69 6.2 2.2 4.5 1.5 versicolor
## 70 5.6 2.5 3.9 1.1 versicolor
## 71 5.9 3.2 4.8 1.8 versicolor
## 72 6.1 2.8 4.0 1.3 versicolor
## 73 6.3 2.5 4.9 1.5 versicolor
## 74 6.1 2.8 4.7 1.2 versicolor
## 75 6.4 2.9 4.3 1.3 versicolor
## 76 6.6 3.0 4.4 1.4 versicolor
## 77 6.8 2.8 4.8 1.4 versicolor
## 78 6.7 3.0 5.0 1.7 versicolor
## 79 6.0 2.9 4.5 1.5 versicolor
## 80 5.7 2.6 3.5 1.0 versicolor
## 81 5.5 2.4 3.8 1.1 versicolor
## 82 5.5 2.4 3.7 1.0 versicolor
## 83 5.8 2.7 3.9 1.2 versicolor
## 84 6.0 2.7 5.1 1.6 versicolor
## 85 5.4 3.0 4.5 1.5 versicolor
## 86 6.0 3.4 4.5 1.6 versicolor
## 87 6.7 3.1 4.7 1.5 versicolor
## 88 6.3 2.3 4.4 1.3 versicolor
## 89 5.6 3.0 4.1 1.3 versicolor
## 90 5.5 2.5 4.0 1.3 versicolor
## 91 5.5 2.6 4.4 1.2 versicolor
## 92 6.1 3.0 4.6 1.4 versicolor
## 93 5.8 2.6 4.0 1.2 versicolor
## 94 5.0 2.3 3.3 1.0 versicolor
## 95 5.6 2.7 4.2 1.3 versicolor
## 96 5.7 3.0 4.2 1.2 versicolor
## 97 5.7 2.9 4.2 1.3 versicolor
## 98 6.2 2.9 4.3 1.3 versicolor
## 99 5.1 2.5 3.0 1.1 versicolor
## 100 5.7 2.8 4.1 1.3 versicolor
## 101 6.3 3.3 6.0 2.5 virginica
## 102 5.8 2.7 5.1 1.9 virginica
## 103 7.1 3.0 5.9 2.1 virginica
## 104 6.3 2.9 5.6 1.8 virginica
## 105 6.5 3.0 5.8 2.2 virginica
## 106 7.6 3.0 6.6 2.1 virginica
## 107 4.9 2.5 4.5 1.7 virginica
## 108 7.3 2.9 6.3 1.8 virginica
## 109 6.7 2.5 5.8 1.8 virginica
## 110 7.2 3.6 6.1 2.5 virginica
## 111 6.5 3.2 5.1 2.0 virginica
## 112 6.4 2.7 5.3 1.9 virginica
## 113 6.8 3.0 5.5 2.1 virginica
## 114 5.7 2.5 5.0 2.0 virginica
## 115 5.8 2.8 5.1 2.4 virginica
## 116 6.4 3.2 5.3 2.3 virginica
## 117 6.5 3.0 5.5 1.8 virginica
## 118 7.7 3.8 6.7 2.2 virginica
## 119 7.7 2.6 6.9 2.3 virginica
## 120 6.0 2.2 5.0 1.5 virginica
## 121 6.9 3.2 5.7 2.3 virginica
## 122 5.6 2.8 4.9 2.0 virginica
## 123 7.7 2.8 6.7 2.0 virginica
## 124 6.3 2.7 4.9 1.8 virginica
## 125 6.7 3.3 5.7 2.1 virginica
## 126 7.2 3.2 6.0 1.8 virginica
## 127 6.2 2.8 4.8 1.8 virginica
## 128 6.1 3.0 4.9 1.8 virginica
## 129 6.4 2.8 5.6 2.1 virginica
## 130 7.2 3.0 5.8 1.6 virginica
## 131 7.4 2.8 6.1 1.9 virginica
## 132 7.9 3.8 6.4 2.0 virginica
## 133 6.4 2.8 5.6 2.2 virginica
## 134 6.3 2.8 5.1 1.5 virginica
## 135 6.1 2.6 5.6 1.4 virginica
## 136 7.7 3.0 6.1 2.3 virginica
## 137 6.3 3.4 5.6 2.4 virginica
## 138 6.4 3.1 5.5 1.8 virginica
## 139 6.0 3.0 4.8 1.8 virginica
## 140 6.9 3.1 5.4 2.1 virginica
## 141 6.7 3.1 5.6 2.4 virginica
## 142 6.9 3.1 5.1 2.3 virginica
## 143 5.8 2.7 5.1 1.9 virginica
## 144 6.8 3.2 5.9 2.3 virginica
## 145 6.7 3.3 5.7 2.5 virginica
## 146 6.7 3.0 5.2 2.3 virginica
## 147 6.3 2.5 5.0 1.9 virginica
## 148 6.5 3.0 5.2 2.0 virginica
## 149 6.2 3.4 5.4 2.3 virginica
## 150 5.9 3.0 5.1 1.8 virginicam$find(query='{"Species":"virginica"}',fields = '{"Sepal_Length": true,"Sepal_Width": true}',limit = 5,sort = '{"Sepal_Length": 1}')## _id Sepal_Length Sepal_Width
## 1 5d2c5163ad8a32724b0b064c 4.9 2.5
## 2 5d2c5163ad8a32724b0b065b 5.6 2.8
## 3 5d2c5163ad8a32724b0b0653 5.7 2.5
## 4 5d2c5163ad8a32724b0b0647 5.8 2.7
## 5 5d2c5163ad8a32724b0b0654 5.8 2.8