inst/sandpit/mysql.R

In MRCIEU/TwoSampleMR: Two Sample MR Functions and Interface to MR Base Database

# mysql -u epxjz -h epi-franklin.epi.bris.ac.uk -p
# wv-92n_YjB

# mysql -u mruser -h epi-franklin.epi.bris.ac.uk -p
# TMG_F1WnTL

# mysql -u gh13047 -h epi-franklin.epi.bris.ac.uk -p
# ri.K-2Gbvd

mydb <- dbConnect(MySQL(), user='epxjz', password='wv-92n_YjB', dbname='mrbase', host='epi-franklin.epi.bris.ac.uk')
dbListTables(mydb)
dbListFields(mydb, 'assoc')


rs <- dbSendQuery(mydb, "select * from study")
d <- dbFetch(rs, n=-1)


load("~/repo/mr_base/inst/data/tel.RData")
snps <- as.character(unique(tel$SNP))
te <- paste("+", paste(snps, collapse=" +"), sep="")

query <- paste("SELECT * FROM mrbase_assoc_info WHERE MATCH (snp_id) AGAINST ('", te, "' IN BOOLEAN MODE);", sep="")

query <- "SELECT * FROM assoc WHERE name='rs10936599'"
rs <- dbSendQuery(mydb, query)
d <- fetch(rs, n=10)
dim(d)


# ssh -L 3306:localhost:3306 gh13047@epi-franklin.epi.bris.ac.uk
# mysql -u gh13047 -h 127.0.0.1 -P 3306 -p
# ri.K-2Gbvd

# mysql -u mruser -h 127.0.0.1 -P 3306 -p
# TMG_F1WnTL

# use mrbase;
#
# describe assoc;
# describe snps;
# describe study;
#
# SELECT COUNT(*) FROM study;
# SELECT COUNT(*) FROM snps;
# SELECT COUNT(*) FROM assoc;
# 1.7 billion rows

# SELECT * FROM study limit 10;
# SELECT * FROM snps WHERE name='rs13078807';
# SELECT * FROM assoc WHERE snp=207707;
#
# SELECT * FROM assoc limit 10;
# SELECT * FROM assoc WHERE snp=2223704;
#
# SELECT a.*, b.*, c.*
# FROM assoc a, snps b, study c
# WHERE a.snp=b.id AND a.study=c.id
# AND (b.name='rs10900000' OR b.name='rs10000010' OR b.name='rs10000092')
# AND (c.filename='cardiogramplusc4d_180814_update_data.txt.uniform.af.txt' OR c.filename='All_ancestries_SNP_gwas_mc_merge_nogc.tbl.uniq.gz.uniform.af.txt')
# ORDER BY filename;
#
#
# SELECT a.*, b.*, c.*
# FROM assoc a, snps b, study c
# WHERE a.snp=b.id AND a.study=c.id
# AND b.name IN ('rs10900000', 'rs10000010', 'rs10000092')
# AND c.filename IN ('cardiogramplusc4d_180814_update_data.txt.uniform.af.txt', 'All_ancestries_SNP_gwas_mc_merge_nogc.tbl.uniq.gz.uniform.af.txt', 'MAGIC_INSULIN_SECRETION_DI_for_release_HMrel27.txt.uniform.af.txt')
# ORDER BY filename;