{"id":6255,"date":"2016-10-15T23:06:59","date_gmt":"2016-10-15T22:06:59","guid":{"rendered":"http:\/\/emilkirkegaard.dk\/en\/?p=6255"},"modified":"2018-06-26T18:27:00","modified_gmt":"2018-06-26T17:27:00","slug":"sql-server-for-population-frequencies-from-1000-genomes","status":"publish","type":"post","link":"https:\/\/emilkirkegaard.dk\/en\/2016\/10\/sql-server-for-population-frequencies-from-1000-genomes\/","title":{"rendered":"SQL server for population frequencies from 1000 genomes"},"content":{"rendered":"
\n

Note: 2018 June 26<\/strong><\/h4>\n

Server down right now, investigating.<\/p>\n

Note: August 16, 2017<\/strong><\/h4>\n

server IP changed to 67.207.92.10.<\/p>\n<\/div>\n

Original post<\/strong><\/h4>\n
\n

We need dplyr for this:<\/p>\n

library(dplyr)<\/code><\/pre>\n
First, use the anon user to log into the SQL server (user = “anon”, pass = “”, ip = “67.207.92.10”, port = 3306):<\/p>\n
sql = src_mysql(\"population_freqs\", host = \"67.207.92.10\", user = \"anon\", port = 3306)<\/code><\/pre>\n
Select the 1000 genomes phase 3 table:<\/p>\n
sql_1kg = tbl(sql, \"1000genomes_phase3\")\r\n\r\n#look at the first 10 rows\r\nsql_1kg<\/code><\/pre>\n
## Source:   query [?? x 35]\r\n## Database: mysql 10.0.27-MariaDB-0ubuntu0.16.04.1 [anon@67.207.92.10:\/population_freqs]\r\n## \r\n##      CHR       SNP    A1    A2    ACB    ASW    BEB    CDX    CEU    CHB\r\n##    <int>     <chr> <chr> <chr>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>\r\n## 1      1 rs1000033     G     T 0.4062 0.3279 0.2151 0.3172 0.2071 0.3107\r\n## 2      1 rs1000050     C     T 0.6302 0.5246 0.2267 0.4247 0.1364 0.3932\r\n## 3      1 rs1000070     T     C 0.6146 0.5902 0.5116 0.4946 0.2828 0.3204\r\n## 4      1 rs1000073     G     A 0.4219 0.3689 0.2093 0.1989 0.6717 0.1456\r\n## 5      1 rs1000075     T     C 0.3750 0.4180 0.2733 0.3172 0.3586 0.2573\r\n## 6      1 rs1000085     C     G 0.0781 0.1393 0.0349 0.0645 0.2121 0.0437\r\n## 7      1 rs1000127     C     T 0.2500 0.2787 0.4012 0.4677 0.3081 0.5728\r\n## 8      1 rs1000184     C     G 0.0521 0.0574 0.2442 0.8333 0.2879 0.7767\r\n## 9      1 rs1000211     T     C 0.0260 0.0246 0.0000 0.0000 0.0000 0.0000\r\n## 10     1 rs1000212     A     G 0.0365 0.0246 0.0000 0.0000 0.0000 0.0000\r\n## # ... with more rows, and 25 more variables: CHS <dbl>, CLM <dbl>,\r\n## #   ESN <dbl>, FIN <dbl>, GBR <dbl>, GIH <dbl>, GWD <dbl>, IBS <dbl>,\r\n## #   ITU <dbl>, JPT <dbl>, KHV <dbl>, LWK <dbl>, MSL <dbl>, MXL <dbl>,\r\n## #   PEL <dbl>, PJL <dbl>, PUR <dbl>, STU <dbl>, TSI <dbl>, YRI <dbl>,\r\n## #   EAS <dbl>, EUR <dbl>, AFR <dbl>, AMR <dbl>, SAS <dbl><\/code><\/pre>\n
The entire file is really large, about 3.6 GB in memory. You often only need a few (1-1000) SNPs, so let\u2019s try downloading only a few:<\/p>\n
#first 10 of the hits from the latest height GWAS\r\n#http:\/\/www.nature.com\/ng\/journal\/v46\/n11\/full\/ng.3097.html\r\nsome_snps = c(\"rs425277\", \"rs9434723\", \"rs10779751\", \"rs2284746\", \"rs12137162\", \r\n\"rs212524\", \"rs1014987\", \"rs2806561\", \"rs4601530\", \"rs926438\")\r\n\r\n#fetch from SQL server\r\n(sql_height_freqs = sql_1kg %>% filter(SNP %in% some_snps) %>% collect())<\/code><\/pre>\n
## # A tibble: 10 x 35\r\n##      CHR        SNP    A1    A2    ACB    ASW    BEB    CDX    CEU    CHB\r\n##    <int>      <chr> <chr> <chr>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>\r\n## 1      1  rs1014987     G     C 0.1458 0.1475 0.1802 0.5000 0.2525 0.4612\r\n## 2      1 rs10779751     A     G 0.6406 0.5984 0.1279 0.0591 0.2677 0.0631\r\n## 3      1 rs12137162     A     C 0.1198 0.1557 0.2384 0.1935 0.2677 0.2621\r\n## 4      1   rs212524     T     C 0.1823 0.1967 0.3605 0.2473 0.4293 0.1796\r\n## 5      1  rs2284746     G     C 0.1927 0.1475 0.4012 0.1882 0.5657 0.2573\r\n## 6      1  rs2806561     A     G 0.3073 0.3934 0.3488 0.5591 0.5909 0.5388\r\n## 7      1   rs425277     T     C 0.0990 0.0984 0.3023 0.1452 0.2576 0.1990\r\n## 8      1  rs4601530     T     C 0.4271 0.3852 0.4535 0.5538 0.2475 0.4515\r\n## 9      1   rs926438     T     C 0.8177 0.7541 0.2093 0.1774 0.5455 0.3010\r\n## 10     1  rs9434723     A     G 0.2240 0.2541 0.1744 0.0538 0.1414 0.0922\r\n## # ... with 25 more variables: CHS <dbl>, CLM <dbl>, ESN <dbl>, FIN <dbl>,\r\n## #   GBR <dbl>, GIH <dbl>, GWD <dbl>, IBS <dbl>, ITU <dbl>, JPT <dbl>,\r\n## #   KHV <dbl>, LWK <dbl>, MSL <dbl>, MXL <dbl>, PEL <dbl>, PJL <dbl>,\r\n## #   PUR <dbl>, STU <dbl>, TSI <dbl>, YRI <dbl>, EAS <dbl>, EUR <dbl>,\r\n## #   AFR <dbl>, AMR <dbl>, SAS <dbl><\/code><\/pre>\n
All the atomic populations are there as well as the 5 super populations (\u2018macro races\u2019). The numbers for the super populations differ slightly from those that can be seen on ensembl because they used weighted means and I used unweighted means.<\/p>\n<\/div>\n