This is an R Markdown document on sourcing data from the MariaDB a mySQL server with R.
# set up your environment
writeLines('PATH="${RTOOLS40_HOME}\\usr\\bin;${PATH}"', con = "~/.Renviron")
#Install Rtools & RMySQL
library(RMySQL)## Loading required package: DBIlibrary(DBI)ucscDb <-dbConnect(MySQL(), user="genome",
host="genome-mysql.soe.ucsc.edu"
)
# do a query & disconnect from the server
result <-dbGetQuery(ucscDb, "show databases;"); dbDisconnect(ucscDb);## [1] TRUE# inspect query
result## Database
## 1 acaChl1
## 2 ailMel1
## 3 allMis1
## 4 allSin1
## 5 amaVit1
## 6 anaPla1
## 7 ancCey1
## 8 angJap1
## 9 anoCar1
## 10 anoCar2
## 11 anoGam1
## 12 anoGam3
## 13 apaSpi1
## 14 apaVit1
## 15 apiMel1
## 16 apiMel2
## 17 aplCal1
## 18 aptFor1
## 19 aptMan1
## 20 aquChr2
## 21 araMac1
## 22 ascSuu1
## 23 balAcu1
## 24 balPav1
## 25 bisBis1
## 26 bosTau2
## 27 bosTau3
## 28 bosTau4
## 29 bosTau5
## 30 bosTau6
## 31 bosTau7
## 32 bosTau8
## 33 bosTau9
## 34 bosTauMd3
## 35 braFlo1
## 36 bruMal2
## 37 bucRhi1
## 38 burXyl1
## 39 caeAng2
## 40 caeJap1
## 41 caeJap4
## 42 caePb1
## 43 caePb2
## 44 caePb3
## 45 caeRem2
## 46 caeRem3
## 47 caeRem4
## 48 caeSp111
## 49 caeSp51
## 50 calAnn1
## 51 calJac1
## 52 calJac3
## 53 calMil1
## 54 canFam1
## 55 canFam2
## 56 canFam3
## 57 capCar1
## 58 carCri1
## 59 cavPor3
## 60 cb1
## 61 cb3
## 62 cb4
## 63 ce10
## 64 ce11
## 65 ce2
## 66 ce4
## 67 ce6
## 68 cerSim1
## 69 chaVoc2
## 70 cheMyd1
## 71 chlSab2
## 72 chlUnd1
## 73 choHof1
## 74 chrPic1
## 75 chrPic2
## 76 ci1
## 77 ci2
## 78 ci3
## 79 colLiv1
## 80 colStr1
## 81 corBra1
## 82 corCor1
## 83 cotJap2
## 84 criGri1
## 85 criGriChoV1
## 86 criGriChoV2
## 87 cucCan1
## 88 danRer1
## 89 danRer10
## 90 danRer11
## 91 danRer2
## 92 danRer3
## 93 danRer4
## 94 danRer5
## 95 danRer6
## 96 danRer7
## 97 dasNov3
## 98 dipOrd1
## 99 dirImm1
## 100 dm1
## 101 dm2
## 102 dm3
## 103 dm6
## 104 dp2
## 105 dp3
## 106 droAna1
## 107 droAna2
## 108 droEre1
## 109 droGri1
## 110 droMoj1
## 111 droMoj2
## 112 droPer1
## 113 droSec1
## 114 droSim1
## 115 droSim2
## 116 droVir1
## 117 droVir2
## 118 droYak1
## 119 droYak2
## 120 eboVir3
## 121 echTel1
## 122 echTel2
## 123 egrGar1
## 124 enhLutNer1
## 125 equCab1
## 126 equCab2
## 127 equCab3
## 128 eriEur1
## 129 eriEur2
## 130 eurHel1
## 131 falChe1
## 132 falPer1
## 133 felCat3
## 134 felCat4
## 135 felCat5
## 136 felCat8
## 137 felCat9
## 138 ficAlb2
## 139 fr1
## 140 fr2
## 141 fr3
## 142 fulGla1
## 143 gadMor1
## 144 galGal2
## 145 galGal3
## 146 galGal4
## 147 galGal5
## 148 galGal6
## 149 galVar1
## 150 gasAcu1
## 151 gavSte1
## 152 gbMeta
## 153 geoFor1
## 154 go
## 155 go080130
## 156 go140213
## 157 go150121
## 158 go180426
## 159 gorGor3
## 160 gorGor4
## 161 gorGor5
## 162 haeCon2
## 163 halAlb1
## 164 halLeu1
## 165 hetBac1
## 166 hetGla1
## 167 hetGla2
## 168 hg16
## 169 hg17
## 170 hg18
## 171 hg19
## 172 hg19Patch10
## 173 hg19Patch13
## 174 hg38
## 175 hg38Patch11
## 176 hgFixed
## 177 hgcentral
## 178 information_schema
## 179 latCha1
## 180 lepDis1
## 181 letCam1
## 182 loaLoa1
## 183 loxAfr3
## 184 macEug1
## 185 macEug2
## 186 macFas5
## 187 manPen1
## 188 melGal1
## 189 melGal5
## 190 melHap1
## 191 melInc2
## 192 melUnd1
## 193 merNub1
## 194 mesUni1
## 195 micMur1
## 196 micMur2
## 197 mm10
## 198 mm10Patch4
## 199 mm5
## 200 mm6
## 201 mm7
## 202 mm8
## 203 mm9
## 204 monDom1
## 205 monDom4
## 206 monDom5
## 207 musFur1
## 208 myoLuc2
## 209 nanPar1
## 210 nasLar1
## 211 necAme1
## 212 nipNip1
## 213 nomLeu1
## 214 nomLeu2
## 215 nomLeu3
## 216 ochPri2
## 217 ochPri3
## 218 oncVol1
## 219 opiHoa1
## 220 oreNil1
## 221 oreNil2
## 222 oreNil3
## 223 ornAna1
## 224 ornAna2
## 225 oryCun2
## 226 oryLat2
## 227 otoGar3
## 228 oviAri1
## 229 oviAri3
## 230 oviAri4
## 231 panPan1
## 232 panPan2
## 233 panRed1
## 234 panTro1
## 235 panTro2
## 236 panTro3
## 237 panTro4
## 238 panTro5
## 239 panTro6
## 240 papAnu2
## 241 papAnu4
## 242 papHam1
## 243 pelCri1
## 244 pelSin1
## 245 performance_schema
## 246 petMar1
## 247 petMar2
## 248 petMar3
## 249 phaCar1
## 250 phaLep1
## 251 phoRub1
## 252 picPub1
## 253 ponAbe2
## 254 ponAbe3
## 255 priExs1
## 256 priPac1
## 257 priPac3
## 258 proCap1
## 259 proteins120806
## 260 proteins121210
## 261 proteins140122
## 262 proteins150225
## 263 proteins160229
## 264 proteins180404
## 265 proteome
## 266 pteGut1
## 267 pteVam1
## 268 pygAde1
## 269 pytBiv1
## 270 rheMac1
## 271 rheMac10
## 272 rheMac2
## 273 rheMac3
## 274 rheMac8
## 275 rhiRox1
## 276 rn3
## 277 rn4
## 278 rn5
## 279 rn6
## 280 sacCer1
## 281 sacCer2
## 282 sacCer3
## 283 saiBol1
## 284 sarHar1
## 285 serCan1
## 286 sorAra1
## 287 sorAra2
## 288 sp120323
## 289 sp121210
## 290 sp140122
## 291 sp150225
## 292 sp160229
## 293 sp180404
## 294 speTri2
## 295 strCam1
## 296 strPur1
## 297 strPur2
## 298 strRat2
## 299 susScr11
## 300 susScr2
## 301 susScr3
## 302 taeGut1
## 303 taeGut2
## 304 tarSyr1
## 305 tarSyr2
## 306 tauEry1
## 307 tetNig1
## 308 tetNig2
## 309 thaSir1
## 310 tinGut2
## 311 triMan1
## 312 triSpi1
## 313 triSui1
## 314 tupBel1
## 315 turTru2
## 316 tytAlb1
## 317 uniProt
## 318 vicPac1
## 319 vicPac2
## 320 visiGene
## 321 wuhCor1
## 322 xenLae2
## 323 xenTro1
## 324 xenTro2
## 325 xenTro3
## 326 xenTro7
## 327 xenTro9
## 328 zonAlb1# set universal user ids
ucsc_user <- "genome"
ucsc_host <- "genome-mysql.cse.ucsc.edu"
hg19 <- dbConnect(MySQL(), user = ucsc_user, host = ucsc_host, db = "hg19")
all_tables <- dbListTables(hg19)
length(all_tables)## [1] 12464all_tables[1:5]## [1] "HInv" "HInvGeneMrna" "acembly" "acemblyClass" "acemblyPep"#see fields
dbListFields(hg19, "acemblyPep")## [1] "name" "seq"#count the number of rows
dbGetQuery(hg19, "select count(*) from acemblyPep")## count(*)
## 1 187692all_tables[16:25]## [1] "affyExonProbesetFull" "affyGnf1h" "affyU133"
## [4] "affyU133Plus2" "affyU95" "agilentCgh1x1m"
## [7] "agilentCgh1x244k" "agilentCgh2x105k" "agilentCgh2x400k"
## [10] "agilentCgh4x180k"affyData <- dbReadTable(hg19, "affyU133Plus2")## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 0 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 1 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 2 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 3 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 4 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 5 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 6 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 7 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 8 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 11 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 12 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 13 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 15 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 16 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 17 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 18 imported as
## numerichead(affyData)## bin matches misMatches repMatches nCount qNumInsert qBaseInsert tNumInsert
## 1 585 530 4 0 23 3 41 3
## 2 585 3355 17 0 109 9 67 9
## 3 585 4156 14 0 83 16 18 2
## 4 585 4667 9 0 68 21 42 3
## 5 585 5180 14 0 167 10 38 1
## 6 585 468 5 0 14 0 0 0
## tBaseInsert strand qName qSize qStart qEnd tName tSize tStart
## 1 898 - 225995_x_at 637 5 603 chr1 249250621 14361
## 2 11621 - 225035_x_at 3635 0 3548 chr1 249250621 14381
## 3 93 - 226340_x_at 4318 3 4274 chr1 249250621 14399
## 4 5743 - 1557034_s_at 4834 48 4834 chr1 249250621 14406
## 5 29 - 231811_at 5399 0 5399 chr1 249250621 19688
## 6 0 - 236841_at 487 0 487 chr1 249250621 27542
## tEnd blockCount
## 1 15816 5
## 2 29483 17
## 3 18745 18
## 4 24893 23
## 5 25078 11
## 6 28029 1
## blockSizes
## 1 93,144,229,70,21,
## 2 73,375,71,165,303,360,198,661,201,1,260,250,74,73,98,155,163,
## 3 690,10,32,33,376,4,5,15,5,11,7,41,277,859,141,51,443,1253,
## 4 99,352,286,24,49,14,6,5,8,149,14,44,98,12,10,355,837,59,8,1500,133,624,58,
## 5 131,26,1300,6,4,11,4,7,358,3359,155,
## 6 487,
## qStarts
## 1 34,132,278,541,611,
## 2 87,165,540,647,818,1123,1484,1682,2343,2545,2546,2808,3058,3133,3206,3317,3472,
## 3 44,735,746,779,813,1190,1195,1201,1217,1223,1235,1243,1285,1564,2423,2565,2617,3062,
## 4 0,99,452,739,764,814,829,836,842,851,1001,1016,1061,1160,1173,1184,1540,2381,2441,2450,3951,4103,4728,
## 5 0,132,159,1460,1467,1472,1484,1489,1497,1856,5244,
## 6 0,
## tStarts
## 1 14361,14454,14599,14968,15795,
## 2 14381,14454,14969,15075,15240,15543,15903,16104,16853,17054,17232,17492,17914,17988,18267,24736,29320,
## 3 14399,15089,15099,15131,15164,15540,15544,15549,15564,15569,15580,15587,15628,15906,16857,16998,17049,17492,
## 4 14406,20227,20579,20865,20889,20938,20952,20958,20963,20971,21120,21134,21178,21276,21288,21298,21653,22492,22551,22559,24059,24211,24835,
## 5 19688,19819,19845,21145,21151,21155,21166,21170,21177,21535,24923,
## 6 27542,query <- dbSendQuery(hg19, "select * from affyU133Plus2 where misMatches between 1 and 3 ")## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 0 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 1 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 2 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 3 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 4 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 5 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 6 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 7 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 8 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 11 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 12 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 13 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 15 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 16 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 17 imported as
## numeric## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 18 imported as
## numericaffyMis <- fetch(query); quantile(affyMis$misMatches)## 0% 25% 50% 75% 100%
## 1 1 2 2 3# bring back top 10 records
affyMisTop10 <- fetch(query, n=15); dbClearResult(query);## [1] TRUEdim(affyMisTop10)## [1] 15 22# Disconnect from the server!!!
dbDisconnect(hg19)## [1] TRUEby Linda, June 2020.