写在前面

无意中逛群主GitHub看到一个好玩的项目，就试着走了一波，有惊无险的跟了下来，中间还被我火眼金睛挑出来了3个bugs反馈给他！

原本以为就是一步步跟着说明书来搭建一个自己的网页工具。没曾想到我用Windows电脑演示，竟是一个非常困难的过程。

配置MySQL数据库

软件下载

进入https://dev.mysql.com/downloads/mysql/>按照下图演示进行软件下载

软件安装

我修改了只截图里的参数，其他保持默认

更改配置

安装成功之后，为了保证后续的R语言能正常使用MySQL，需要修改配置文件。默认安装的配置文件在"C:\ProgramData\MySQL\MySQL Server 8.0\my.ini"。

在 [mysqld]中添加 local-infile=1

之后重启服务或者重启电脑也行。

创建用户和数据库

打开MySQL的终端

在其中创建新用户，并赋予全部的权限。

# 创建数据库
CREATE DATABASE tfmapperdb
DEFAULT CHARACTER SET latin1
DEFAULT COLLATE latin1_general_ci;
# 创建用户
CREATE USER tfmapperuser IDENTIFIED BY 'tfmapper_@Abc';
GRANT ALL PRIVILEGES ON tfmapperdb.* TO 'tfmapperuser'@'%';

使用latin1的编码，这是因为后续的诡异报错。

下载Shiny代码

GitHub地址为: https://github.com/jmzeng1314/TF_map, 选择ZIP进行下载解压缩。

安装必要的R包

安装方式如下GIF图所示，基本步骤就是打开Shiny项目中的 TF_map.Rproj,然后打开其中 scripts/install_packages.R, 把里面提到的所有R包都装了就行。

这里面还没有提到一些物种数据的R包，因此这里也一并装了

BiocManager::install("org.Hs.eg.db")
BiocManager::install('org.Mm.eg.db')
BiocManager::install('GEOmetadb')

数据导入MySQL

这一步极其的繁琐，因为涉及到数据过多，有些数据还特别大。

先加载好R包和配置账号密码, 调用MySQL

library(RMySQL)
host <<- "127.0.0.1"
port <<- 3306
user <<- "tfmapperuser"
password <<- 'tfmapper_@Abc'
library(RMySQL)
con <- dbConnect(MySQL(), host=host, port=port, user=user, password=password)
sql="USE tfmapperdb;"
dbSendQuery(con, sql)

之后你需要检查编码，使用 dbGetQuery(con,"SHOW VARIABLES LIKE 'character_set_%';")查看编码

保证 character_set_database应该是latin1. (如果是UTF-8会在后续分析中报错)

gene table

原本需要下载下面两个数据，

gencode.v20.annotation.gtf.gz
gencode.vM20.annotation.gtf.gz

然后用Perl脚本进行预处理，但是格式化的 gencode_v29_human_gene_info和 gencode_v29_human_gene_info已经在解压缩的 files里，因此不需要额外处理

a=read.table('files/gencode_v29_human_gene_info',sep = '\t')
head(a)
colnames(a)=c('symbol' , 'type' , 'ensembl' , 'chr' ,'start', 'end' )
dbWriteTable(con, 'gencode_v29_human_gene_info', a, append=F,row.names=F)
sql='show tables;'
dbGetQuery(con, sql)
a=read.table('files/gencode_vM20_mouse_gene_info',sep = '\t')
head(a)
colnames(a)=c('symbol' , 'type' , 'ensembl' , 'chr' ,'start', 'end' )
dbWriteTable(con, 'gencode_vM20_mouse_gene_info', a, append=F,row.names=F)
sql='show tables;'
dbGetQuery(con, sql)

gene name

从org包里面提取基因ID和基因名的对应关系，然后导入数据库中。

library(org.Hs.eg.db)
g2s=toTable(org.Hs.egSYMBOL)
g2n=toTable(org.Hs.egGENENAME)
s2n=merge(g2s,g2n,by='gene_id')
dbWriteTable(con, 'human_genename',s2n, append=F,row.names=F)
library(org.Mm.eg.db)
g2s=toTable(org.Mm.egSYMBOL)
g2n=toTable(org.Mm.egGENENAME)
s2n=merge(g2s,g2n,by='gene_id')
head(s2n)
dbWriteTable(con, 'mouse_genename',s2n, append=F,row.names=F)

Cistrome metadata

将如下8个文件导入数据库

TFhumaninformation.txt
TFmousedata_information.txt
cahumandata_information.txt
camousedata_information.txt
histonehumandata_information.txt
histonemousedata_information.txt
otherhumandata_information.txt
othermousedata_information.txt

tmp <- lapply(list.files(path = 'files/metaFiles/',pattern = "*.information.txt"),
function(x){
a=read.table(file.path('files/metaFiles/',x),sep='\t',fill = T,quote = "")
a$species=strsplit(x,'_')[[1]][2]
a$type=strsplit(x,'_')[[1]][1]
return(a)
})
tmp <- do.call(rbind,tmp)[,c(1,2,4:7,9:10)]
head(tmp)
colnames(tmp) <- strsplit('sampleID GSM bs1 bs2 bs3 IP species type','\\s+' )[[1]]
head(tmp)
dbWriteTable(con, 'cistrome_metadata', tmp , append=F,row.names=F)
write.csv(tmp,'cistrome_metadata.csv')

这里如果之前创建数据库未设置成latin1编码，那么遇到一个报错

经过我排查，发现罪魁祸首就是"NUP98–PHF23"中的"中间连字符，中间那个"–"根本不是连字符。因为如果用UTF-8格式为csv后，在excel查看内容就变成了"NUP98鈥揚HF23"

Cistrome GSM metadata

下载 GEOmetadb.sqlite.gz文件。有两种方式，一种是先用 library(GEOmetadb)加载R包，然后用 getSQLiteFile()下载；或者是直接从http://starbuck1.s3.amazonaws.com/sradb/GEOmetadb.sqlite.gz进行下载，解压缩后文件是7G左右。

db <- "GEOmetadb.sqlite" #实际地址，我放在了Shiny目录下
file.info(db)
conGEO <- dbConnect(SQLite(),db)
dbListTables(conGEO)
dbListFields(conGEO,"gsm")
dbGetQuery(conGEO,"select * from gsm where gsm='GSM1267210' ")
gsm_list=tmp$GSM
length(unique(gsm_list))
this_sql=paste0("select * from gsm where gsm in ('",paste(gsm_list,collapse = "','"),"')")
print(this_sql)
#tmp=dbGetQuery(con,'select * from gsm ')
tmp2=dbGetQuery(conGEO,this_sql)
dbDisconnect(conGEO)
## some of the GSM id missed in the GEOmetadb.sqlite, I didn't check the reason.
dbWriteTable(con, 'cistrome_GSM_metadata', tmp2 , append=F,row.names=F)
write.csv(tmp2,'cistrome_GSM_metadata.csv')

注:这一步运行建立在上一步的成功基础上，没有上一步的tmp数据框，运行时一定出错。

Encode metadata

导入Encode的如下文件

humanTFGRCh38.conservative.bed.list.txt
humanhistoneGRCh38.replicated.peaks.bed.list.txt
mouseTFmm10.conservative.peaks.bed.list.txt
mousehistonemm10.replicated.peaks.bed.list.txt

setwd('files/metaFiles/')
a=read.table('human_histone_GRCh38.replicated.peaks.bed.list.txt',sep = '\t',
colClasses=c('character'),stringsAsFactors = F,quote = '')
# OLD :'cellline','celltype','tissue'
# NEW : 'bs1','bs2','bs3'
colnames(a)=c('sampleID','uniqID','bs1','bs2','bs3','gender','age','IP','url')
a$species='human';a$type='histone'
head(a)
# dbGetQuery(con,"DROP TABLE encode_metadata")
dbWriteTable(con, 'encode_metadata', a, append=F,row.names=F)
a=read.table('human_TF_GRCh38.conservative.bed.list.txt',sep = '\t',
colClasses=c('character'),stringsAsFactors = F,quote = '')
colnames(a)=c('sampleID','uniqID','bs1','bs2','bs3','gender','age','IP','url')
a$species='human';a$type='TF'
dbWriteTable(con, 'encode_metadata', a, append=T,row.names=F)
a=read.table('mouse_histone_mm10.replicated.peaks.bed.list.txt',sep = '\t',
colClasses=c('character'),stringsAsFactors = F,quote = '')
colnames(a)=c('sampleID','uniqID','bs1','bs2','bs3','gender','age','IP','url')
a$species='mouse';a$type='histone'
dbWriteTable(con, 'encode_metadata', a, append=T,row.names=F)
a=read.table('mouse_TF_mm10.conservative.peaks.bed.list.txt',sep = '\t',
colClasses=c('character'),stringsAsFactors = F,quote = '')
colnames(a)=c('sampleID','uniqID','bs1','bs2','bs3','gender','age','IP','url')
a$species='mouse';a$type='TF'
dbWriteTable(con, 'encode_metadata', a, append=T,row.names=F)
setwd('../../')

Peaks tables (2X2X2X(23+21))

这一步的数据比较多，需要发邮件到jmzeng1314@163.com 向作者申请处理后的peak文件。作者会给你提供一个微云网盘链接，我们以其中比较小的文件为例进行演示。

在shiny的应用文件夹创建一个文件夹db,然后将下面文件下载到该文件夹中。（选择一个较小的作为演示）

注请保持原有的文件夹层次关系，例如这次下载的文件就应该存放在db/cistrome/human/DNase下。

如下是代码部分, 代码会遍历db下面的所有以merge.txt结尾的文件，

all_tables<-dbListTables(con)
all_tables
root_dir <- 'db'
## totally 100Gb files
all_files <- list.files(root_dir,pattern='*merge.txt',all.files=T,recursive=T)
all_files
lapply(all_files, function(x){
# x=all_files[1]
this_file <- file.path(root_dir,x)
print(this_file)
print(Sys.time())
this_table=strsplit(gsub('\\/','_', x ),"\\.")[[1]][1]
this_table
#if( this_table %in% all_tables){
if(T){
dbSendQuery(con,paste0(" drop table if exists ",this_table))
#sql <- paste0(" CREATE TABLE ",this_table,
# " (sampleID VARCHAR(25) NOT NULL, feature_type tinyint(1) NOT NULL DEFAULT 0, start int(10) DEFAULT NULL, end int(10) DEFAULT NULL, score double DEFAULT NULL, dis int(10) DEFAULT NULL, symbol VARCHAR(50) DEFAULT NULL, attri VARCHAR(100) DEFAULT NULL ,p double DEFAULT NULL,q double DEFAULT NULL, KEY idx_symbol (symbol),KEY idx_position (start,end) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 "
)
#print(sql)
dbSendQuery(con,sql)
dat <- data.table::fread(this_file,stringsAsFactors = F,sep='\t',quote = "")
colnames(dat) <- c('sampleID','feature_type','start','end','score','dis','symbol','attri','p','q')
dbWriteTable(con, this_table, dat, append=T,row.names=F)
print(this_table)
}
})

运行作者源代码时遇到如下报错, 经过检索发现是一个RMySQL的bug，无论你是否添加append=TRUE，他就是会报错。所以我把原先的sql语句删掉，不再事先创建数据表。

Error in .local(conn, statement, ...) :
could not run statement: Table 'cistrome_human_dnase_chr1' already exists

后面也可以按照自己的需求下载对应的文件，运行上述代码将数据导入MySQL。

启动Shiny

在上述的准备之后，就可以在本地运行该Shiny应用。但是呢，这里还有一个坑，就是在作者的ui.R里面，作者居然还在里面给我藏了一个有趣的bug。

刚开始运行时，R提示了几个warnning，然后网页端里面的内容明显没有完全加载。

经过我的直觉排查，我发现作者在ui.R里面使用了中文的引号。

导致Shiny在解析时报错，因此解决方案就是替换成英文的引号，如下

使用方法就是选择IP类型，填写基因就好（这里写的是21号染色体上的一个基因）

手把手教你使用shiny创建一个网页工具（基于Windows）

写在前面

配置MySQL数据库

软件下载

软件安装