In stix-global/eutradeflows: Download bilateral trade flow data from Eurostat Comext

library(dplyr)
library(eutradeflows)
# To compile this document, run the following at an R prompt:
# rmarkdown::render("~/R/eutradeflows/docs/mysql.Rmd")

knitr::opts_chunk$set(echo = TRUE)

con <- RMariaDB::dbConnect(RMariaDB::MariaDB(), dbname = "tradeflows")

Comparing query time between %like%, == and %in%

Productcode as a numeric vector %like%

productpattern <- 44079998 
tableread <- "vld_comext_monthly"
periodstart <- 201500
system.time(dtf <- tbl(con, tableread) %>%
        filter(productcode %like% productpattern &
                   period > periodstart) %>% 
        addproreppar2tbl(con,.) %>% 
        collect())

Productcode as a character vector %like%

productpattern <- "44079998"
tableread <- "vld_comext_monthly"
periodstart <- 201500
system.time(dtf <- tbl(con, tableread) %>%
        filter(productcode %like% productpattern &
                   period > periodstart) %>% 
        addproreppar2tbl(con,.) %>% 
        collect())

Productcode as a character vector ==

productpattern <- "44079998"
tableread <- "vld_comext_monthly"
periodstart <- 201500
system.time(dtf <- tbl(con, tableread) %>%
        filter(productcode == productpattern &
                   period > periodstart) %>% 
        addproreppar2tbl(con,.) %>% 
        collect())

Productcode as a character vector %in%

productpattern <- "44079998"
tableread <- "vld_comext_monthly"
periodstart <- 201500
system.time(dtf <- tbl(con, tableread) %>%
        filter(productcode %in% productpattern &
                   period > periodstart) %>% 
        addproreppar2tbl(con,.) %>% 
        collect())

Cost of the addproreppar() function

The addproreppar() function add product description, reporter and partner to the querried data.

Productcode as a character vector %in% (several products) without codes

The explain shows "ALL" meaning that it will use a full table scan.

swdcodes <- classificationimm$productcode[classificationimm$productimm=="Sawn: softwood"]
tableread <- "vld_comext_monthly"
periodstart <- 201500
remotequery <-  tbl(con, tableread) %>%
        filter(productcode %in% swdcodes & period > periodstart)
explain(remotequery)
system.time(dtf <- collect(remotequery))

Productcode as a character vector %in% (several products) add prorepparcodes

swdcodes <- classificationimm$productcode[classificationimm$productimm=="Sawn: softwood"]
tableread <- "vld_comext_monthly"
periodstart <- 201500

system.time(dtf <- tbl(con, tableread) %>%
        filter(productcode %in% swdcodes &
                   period > periodstart) %>% 
        addproreppar2tbl(con,.) %>% 
        collect())

Product pattern %like% "pattern%" without product description, reporter and partner

productpattern <- "440799%"
tableread <- "vld_comext_monthly"
periodstart <- 201500
system.time(dtf <- tbl(con, tableread) %>%
        filter(productcode %like% productpattern &
                   period > periodstart) %>% 
        addproreppar2tbl(con,.) %>% 
        collect())

Product pattern %like% "pattern%" with product description, reporter and partner

productpattern <- "440799%"
tableread <- "vld_comext_monthly"
periodstart <- 201500
system.time(dtf <- tbl(con, tableread) %>%
        filter(productcode %like% productpattern &
                   period > periodstart) %>% 
        addproreppar2tbl(con,.) %>% 
        collect())

stix-global/eutradeflows documentation built on Nov. 13, 2020, 9:23 p.m.