timeseriesdb: Store Time Series

library(timeseriesdb)
con <- createConObj("mbannert",
                    "postgres",
                    "localhost",
                    passwd = "")
lots <- list(ts1 = ts(rnorm(100), start = c(1990,1), freq = 4),
             ts2 = ts(rnorm(135), start = c(1994,3), freq = 12)
storeTimeSeries(con, lots)

• list of time series
• unique identifiers = list names

timeseriesdb: Read Time Series

library(timeseriesdb)
con <- createConObj("mbannert",
                    "postgres",
                    "localhost",
                    passwd = "")
# returns a list, in order to keep class of output the same. 
tsl <- readTimeSeries(con,"ch.kof.barometer")
names(tsl)

## [1] "ch.kof.barometer"

class(tsl)

## [1] "list"   "tslist"

class(tsl$ch.kof.barometer)

## [1] "ts"

timeseriesdb: reading multiple series

# key vectors
tsl <- readTimeSeries(con,
                      c("ch.kof.barometer",
                        "ch.kof.mpc"))

# by regular expression
cpi <- readTimeSeries(con,"ch.fso.cpi", regex = T)

# by stored sets
keys <- readTsSetKeys(con, "kof_indicators")$ts_key
set_of_ts <- readTimeSeries(con, keys)
names(set_of_ts)

## [1] "ch.kof.barometer"                      
## [2] "ch.kof.inu.ng08.fx.q_ql_ass_bs.balance"

Access Beyond R: REST API

library(kofdata)
library(tstools)
tsplot(get_time_series("ch.kof.barometer"))

2. Comprehensive, Multi-Lingual Meta Information

mi.en <- readMetaInformation(con,
                    c("ch.kof.inu.ng08.fx.q_ql_ass_bs.balance"),
                             locale = "en") 
en <- mi.en[[1]][!grepl("unlocal",names(mi.en[[1]]))]
tail(en, 5)

## $variable
## [1] "business situation, assessment"
## 
## $bootstrap
## [1] "none"
## 
## $frequency
## [1] "frequency with maximum series length"
## 
## $level_ext
## [1] "total sector (noga letter code C (MANUFACTURING))"
## 
## $variable_type
## [1] "qualitative question with 3-point scale"

2. Comprehensive, Multi-Lingual Meta Information

mi.en <- readMetaInformation(con,
                    c("ch.kof.inu.ng08.fx.q_ql_ass_bs.balance"),
                             locale = "en")
en <- mi.en[[1]][!grepl("unlocal",names(mi.en[[1]]))]
tail(en, 5)

## $variable
## [1] "business situation, assessment"
## 
## $bootstrap
## [1] "none"
## 
## $frequency
## [1] "frequency with maximum series length"
## 
## $level_ext
## [1] "total sector (noga letter code C (MANUFACTURING))"
## 
## $variable_type
## [1] "qualitative question with 3-point scale"

2. Comprehensive, Multi-Lingual Meta Information

mi.fr <- readMetaInformation(con,
                    c("ch.kof.inu.ng08.fx.q_ql_ass_bs.balance"),
                             locale = "fr")
fr <- mi.fr[[1]][!grepl("unlocal",names(mi.fr[[1]]))]
tail(fr, 5)

## $variable
## [1] "marche des affaires, résultat"
## 
## $bootstrap
## [1] "aucun"
## 
## $frequency
## [1] "fréquence avec longueur maximale de série"
## 
## $level_ext
## [1] "ensemble du secteur (lettre noga C (INDUSTRIE MANUFACTURIÈRE))"
## 
## $variable_type
## [1] "question qualitative avec échelle à 3 points"

3. Revision Handling

readTimeSeries(con, "ch.snb.lb.aus",
               valid_on = "2015-04-23")

## $ch.snb.lb.aus
##          Qtr1     Qtr2     Qtr3     Qtr4
## 2012 118900.5 122974.1 126521.6 128917.5
## 2013 127160.5 140628.4 128375.0 130480.6
## 2014 129416.6 129677.0 126731.2 129805.7
## 2015 120786.3 118784.0                  
## 
## attr(,"class")
## [1] "list"   "tslist"

readTimeSeries(con, "ch.snb.lb.aus",
               valid_on = "2015-07-23")

## $ch.snb.lb.aus
##          Qtr1     Qtr2     Qtr3     Qtr4
## 2012 118900.5 122974.1 126521.6 128917.5
## 2013 127160.5 140628.4 128375.0 130480.6
## 2014 131104.1 131361.7 128411.1 136437.9
## 2015 120903.9 119459.9 115835.0         
## 
## attr(,"class")
## [1] "list"   "tslist"

3. Revision Handling: All

readAllVintages(con, "ch.snb.lb.aus")

Under the Hood: Relations (tables)

Under the Hood: Data Types

• hstore key value pair reduces number of records

• validity is a range NOT a date

• validity and indices implement non-overlapping constraint

A Time Series Record in the DB: Key Value Pairs (hstore)

Summary

1. Long Term Data Conservation ⏳⏳⏳

2. Comprehensive Meta Descriptions 🇫🇷🇨🇭🇺🇸🇮🇹🇧🇷

3. Manage Revisions 🗄

4. Combines advantages of both SQL, NoSQL

Outlook

• Plumber based REST API

• Explore Big Data (greenplum, timescale)

• Row Level Security (Time Series Level)

Feedback

• @whatsgoodio

• https://github.com/mbannert/timeseriesdb