In this document, we’ll first go over some notes about setting up GridDB and connecting to it from R. Then, we’ll ingest some gene expression data from The Cancer Genome Atlas (TCGA), and query the GridDB backend using You can follow along by cloning the source code from here: https:\/\/github.com\/griddbnet\/Blogs\/tree\/gene_analysis<\/p>\n To install GridDB we simply follow the instructions provided in the documentation<\/a>. On an Ubuntu machine, the easiest is to download the Running the code below would require installation (if not already available) of several R packages. Below, I am using the One can install these dependencies via the usual or, in an As documented previously<\/a>, we'll use the For ease of use down the line, we can wrap these two steps, creating a We'll work with some gene expression data from human cancer studies as published by The Cancer Genome Atlas (TCGA) program<\/a>. In particular, we'll focus on data packaged by the Bioconductor package A quick peak at the data sets:<\/p>\n We have expression data for 10 cancers, with a mean of 1.2M observations (rows). Each data set has 5 columns,<\/p>\n and the represented cancers are<\/p>\n To ingest the data, we first create a connection to the GridDB cluster using our function defined above. Then, we define two functions, one to create a table and another to insert data into the table. These functions will allow us to cycle overa list of data frames and load each data frame into the database seamlessly with base R's Some summary simple stats. Nothing biologically meaningful at this point. The main idea is to use the familiar R packages like In this vignette, we saw how we can use GridDB database from R. We ingested one million rows of gene expression data in ten tables in about 1 hour and then queried the data seamlessly using familiar tools like R \/ Introduction In this document, we’ll first go over some notes about setting up GridDB and connecting to it from R. Then, we’ll ingest some gene expression data from The Cancer Genome Atlas (TCGA), and query the GridDB backend using dplyr to generate simple summary statistics. Prerequisites You can follow along by cloning the source code […]<\/p>\n","protected":false},"author":41,"featured_media":29458,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[121],"tags":[],"class_list":["post-46753","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog"],"acf":[],"yoast_head":"\ndplyr<\/code> to generate simple summary statistics.<\/p>\nPrerequisites<\/h2>\n
git clone https:\/\/github.com\/griddbnet\/Blogs.git --branch gene_analysis<\/code><\/pre>\n<\/div>\nInstalling and starting GridDB<\/h3>\n
deb<\/code> package and install with dpkg -i<\/code>. Thereafter, we need to start the GridDB service with sudo systemctl start gridstore<\/code>. After this, we are ready to connect to the service from R in order to load and query data.<\/p>\nInstalling R dependencies<\/h3>\n
attachment<\/code> package to detect the dependencies used by this Quarto markdown document.<\/p>\nattachment::att_from_rmd(\"01.Rmd\")<\/code><\/pre>\n<\/div>\ninstall.packages<\/code> method, except for Bioconductor<\/code> packages that would require BiocManager::install<\/code>:<\/p>\ndeps <- attachment::att_from_rmd(\"01.Rmd\")\ninstall.packages(deps)<\/code><\/pre>\n<\/div>\nUbuntu<\/code> environment (as it might be the case in a cloud setup), afaster approach might be to get pre-compiled versions using Ubuntu<\/code>'s package manager:<\/p>\n# replace `PKG1\/2\/3` with the required package names\nsudo apt install r-cran-PKG1 r-cran-PKG2 r-cran-PKG3 ...<\/code><\/pre>\n<\/div>\nConnecting R to a local GridDB cluster<\/h2>\n
RJDBC<\/code> package (Java database connectivity) to connect to the GridDB cluster. As the cluster is running locally (as would be the case if you followed the installation and getting started guide<\/a>), be sure to use the localhost IP address (127.0.0.1<\/code>) and the port 20001<\/code>.<\/p>\njdbc<\/code> driver and a GridDB connection, in a function called connect_to_griddb<\/code>. Note that we are loading the entire namespaces of RJDBC<\/code> and DBI<\/code>, which might be OK for an exploratory interactive session, but for more serious projects, we'd of course create an R package and import only the needed functions explicitly with importFrom<\/code> or use an approach such as box<\/code><\/a> to create modules with specific imports.<\/p>\nsuppressPackageStartupMessages({\n library(RJDBC)\n library(DBI)\n library(dplyr)\n library(tidyr)\n library(purrr)\n library(qs)\n})\nconnect_to_griddb <- function() {\n drv <- JDBC(driverClass = \"com.toshiba.mwcloud.gs.sql.Driver\",\n # Point this to your gridstore jar\n classPath = \"~\/src\/jdbc\/bin\/gridstore-jdbc.jar\")\n \n conn <-\n dbConnect(drv,\n \"jdbc:gs:\/\/127.0.0.1:20001\/myCluster\/public\",\n \"admin\",\n \"admin\")\n \n return(conn)\n}<\/code><\/pre>\n<\/div>\nData<\/h2>\n
GSEABenchmarkeR<\/code><\/a> and intended to use for benchmarking gene-expression studies. The data were downloaded, pre-formatted into rectangular sets and saved as a qs<\/code><\/a> archive for faster saving and loading.<\/p>\nTCGA <- qs::qread(\"data\/expressions.qs\")\nsapply(TCGA, nrow) |> summary()<\/code><\/pre>\n<\/div>\nTCGA[[1]] |> head() |> knitr::kable()<\/code><\/pre>\n<\/div>\nsapply(TCGA, \"[[\", 1, 5) |> unlist() |> knitr::kable()<\/code><\/pre>\n<\/div>\nLoading our data into GridDB<\/h2>\n
*apply<\/code> family of functions or purrr<\/code>'s map_*<\/code> family of functions.<\/p>\ngriddb <- connect_to_griddb()\ncreate_table <- function(table_name) {\n dbSendUpdate(\n griddb,\n sprintf(\n \"CREATE TABLE IF NOT EXISTS %s (Gene STRING, Sample STRING, Expression INTEGER, Code STRING, Name STRING);\",\n table_name\n )\n )\n}\ninsert_table <- function(conn, name, df, append = TRUE) {\n for (i in seq_len(nrow(df))) {\n dbWriteTable(conn, name, df[i, ], append = append)\n }\n}\n# using `invisible` here to hide non-informative output\ninvisible(lapply(names(TCGA), create_table))\nDBI::dbListTables(griddb)\n# 6 minutes for 10K rows per table. So 6 min for 100K rows\n# 1h for 100K rows per table. So 1h for 1M rows.\ninvisible(pbapply::pblapply(seq_along(TCGA), function(i)\n insert_table(\n conn = griddb,\n name = names(TCGA)[[i]],\n df = TCGA[[i]][1:100000, ],\n append = TRUE\n ))\n)\nDBI::dbDisconnect(griddb)<\/code><\/pre>\n<\/div>\ngriddb <- connect_to_griddb()\niter <- setNames(names(TCGA), names(TCGA))\nmap_dfr(iter, function(x) {\n griddb |> tbl(x) |> collect() |> nrow()\n}) |> knitr::kable()<\/code><\/pre>\n<\/div>\nData analysis<\/h2>\n
dplyr<\/code>, tidyr<\/code> and purrr<\/code> toquery the fast GridDB backend database. The R code below, could well have been developed for a MariaDB<\/code> or PostgresSQL<\/code> database, but it will work equally well with a GridDB setup.<\/p>\nGenes per cancer dataset that have average expression > 2000<\/h3>\n
high_expression_genes <- purrr::map_dfr(iter, .id = \"Cancer\", \n function(x) {\n griddb |>\n tbl(x) |>\n group_by(Gene) |>\n summarise(Mean_expression = mean(Expression, na.rm = TRUE)) |>\n filter(Mean_expression >= 2000) |> \n collect()\n }\n)\ndim(high_expression_genes)\nhead(high_expression_genes) |> knitr::kable()<\/code><\/pre>\n<\/div>\nGenes per cancer dataset that have average expression < 300<\/h3>\n
low_expression_genes <- purrr::map_dfr(iter, .id = \"Cancer\",\n function(x) {\n griddb |>\n tbl(x) |>\n group_by(Gene) |>\n summarise(Mean_expression = mean(Expression, na.rm = TRUE)) |>\n filter(Mean_expression <= 300) |> \n collect() \n }\n)\ndim(low_expression_genes)\ntail(low_expression_genes) |> knitr::kable()<\/code><\/pre>\n<\/div>\nGenes shared among all cancer data sets<\/h3>\n
unique_genes_per_dataset <- purrr::map(iter, \n function(x) {\n griddb |>\n tbl(x) |>\n collect() |>\n pull(Gene) |> \n unique()\n }\n)\nshared_genes <- Reduce(intersect, unique_genes_per_dataset)\nshared_genes<\/code><\/pre>\n<\/div>\nMedian expression per cancer data set of the first 10 genes shared for all data sets<\/h3>\n
first10 <- shared_genes[1:10]\niter2 <- expand.grid(iter, first10, stringsAsFactors = FALSE)\nfirst10_expression <-\n purrr::map2_dfr(iter2$Var1, iter2$Var2, function(.x, .y) {\n griddb |>\n tbl(.x) |>\n filter(Gene == .y) |> \n collect() \n })\nfirst10_expression |>\n group_by(Code, Name, Gene) |>\n summarise(Mean_expression = mean(Expression, na.rm = TRUE)) |>\n ungroup() |> \n select(-Name) |>\n pivot_wider(names_from = Code, values_from = Mean_expression) |> \n knitr::kable()<\/code><\/pre>\n<\/div>\nClean up<\/h2>\n
DBI::dbDisconnect(griddb)<\/code><\/pre>\n<\/div>\nConclusion<\/h2>\n
DBI<\/code> and dplyr<\/code>.<\/p>\nAppendix<\/h2>\n
Bioconductor<\/code> code used to download and preprocess the gene expression data.<\/p>\n# This has a lot of dependencies and can take a few minutes to install\n# BiocManager::install(\"GSEABenchmarkeR\")\nlibrary(GSEABenchmarkeR)\ntcga <- loadEData(\"tcga\", nr.datasets = 10)\ncancer_abbreviations <- names(tcga)\ncancer_names <- c(\"BLCA\" = \"Bladder Urothelial Carcinoma\",\n \"BRCA\" = \"Breast Invasive Carcinoma\",\n \"COAD\" = \"Colon Adenocarcinoma\",\n \"HNSC\" = \"Head and Neck Squamous Cell Carcinoma\",\n \"KICH\" = \"Kidney Chromophobe\",\n \"KIRC\" = \"Kidney Renal Clear Cell Carcinoma\",\n \"KIRP\" = \"Kidney Renal Papillary Cell Carcinoma\",\n \"LIHC\" = \"Liver Hepatocellular Carcinoma\",\n \"LUAD\" = \"Lung Adenocarcinoma\",\n \"LUSC\" = \"Lung Squamous Cell Carcinoma\")\nexpressions <- lapply(seq_along(tcga), function(i) {\n tcga[[i]]@assays@data@listData |> \n as.data.frame() |> \n tibble::rownames_to_column(var = \"Gene\") |> \n tidyr::pivot_longer(cols = -Gene, names_to = \"Sample\", values_to = \"Expression\") |> \n mutate(cancer = cancer_abbreviations[[i]], cancer_name = cancer_names[[i]])\n})\nqs::qsave(expressions, \"expressions.qs\")<\/code><\/pre>\n<\/div>\nSession info<\/h2>\n
sessionInfo()<\/code><\/pre>\n<\/div>\n","protected":false},"excerpt":{"rendered":"