timeseriesdb

# timeseries<strong>db</strong>
## Manage, Process and Archive Time Series with R and PostgreSQL
### Matthias Bannert (<span class="citation">@whatsgoodio</span>)
### ETH Zurich
### 2019-07-12

---

<!-- 
framework to map R ts objects to PostgreSQL KVP + relations.
talk's not only about the timeseriesdb approach does it, but

-->

---

![johnny](johnnyrrr.jpg)

---

## Why Pair R Time Series /w a Database ?

---

### 1. Long Lived Data Pipelines

--
<font size="30px">❤️ ~  ∞ </font>
--

---

## timeseriesdb: Store Time Series

```r
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

```r
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"
```

```r
class(tsl)
```

```
## [1] "list"   "tslist"
```

```r
class(tsl$ch.kof.barometer)
```

```
## [1] "ts"
```

---

## timeseriesdb: reading multiple series

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

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

```r
# 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

![url](urlwindow.png)

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

![](index_files/figure-html/unnamed-chunk-6-1.png)

---

### 2. Comprehensive, Multi-Lingual Meta Information

```r
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

```r
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)
```

### 2. Comprehensive, Multi-Lingual Meta Information

```r
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

> "I've found the best way to revise your own work is to pretend
> that somebody else wrote it and then to rip the living s*** out 
> of it."

> <footer>--- Don Roff, writer</footer>

---

### 3. Revision Handling

```r
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"
```

```r
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

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

![vintages](vintages2.png)

---

### Under the Hood: Relations (tables)

![relations](relations.png)

---

### Under the Hood: Data Types

![descriptions](desc2.png)

- 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)

![kvp](kvp2.png)

---

## Summary

### 1. Long Term Data Conservation ⏳⏳⏳

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

### 3. Manage Revisions 🗄

### 4. Combines advantages of both SQL, NoSQL

---

## Outlook

![announce](announce2.png)

- Plumber based REST API

- Explore Big Data ([greenplum](https://greenplum.org/), [timescale](https://www.timescale.com/))

- Row Level Security (Time Series Level)

## Feedback

- ![twitter](twitter.png) @whatsgoodio

- ![github](github.png) https://github.com/mbannert/timeseriesdb