---
title: "K Means models"
author: "Edgar Ruiz"
date: "`r Sys.Date()`"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{K Means models}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r setup, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)
library(dplyr)
library(purrr)
library(rlang)
library(nycflights13)
```

## Intro

The `simple_kmeans_db()` function enables running the KMeans model inside the database.  It uses `dplyr` programming to abstract the steps needed produce a model, so that it can then be translated into SQL statements in the background.

## Example setup

In this example, a simple `RSQlite` database will be use to load the `flights` data from the `nycflights13` library.

```{r}
library(dplyr)

con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
RSQLite::initExtension(con)

db_flights <- copy_to(con, nycflights13::flights, "flights")
```

## Running Kmeans clustering

The function `simple_kmeans_db()` can use with local data, or a remote table, such as the `db_flights` variable that is a pointer to the "flights" table inside the SQLite database.  When piping to the function, the only other required arguments are two or more fields separated by comma.  Because it uses 'tidyeval', the variable name auto-completion will work.

```{r}
library(modeldb)

km <- db_flights %>%
  simple_kmeans_db(dep_time, distance)
```

The `simple_kmeans_db()` function uses a progress bar to show you the current cycle, the maximum cycles it's expected to run, the current difference between the previous cycle and the current cycle, and the running time.  The loop will stop once it wither has two matching consecutive cycles, or if it reaches the maximum number of cycles, as determined by the `max_repeats` argument.

The final **centers** are are stored in the `k_center` variable of the returned object
```{r}
km %>% pull(k_center)
```

The latest results are stored in the `tbl` variable of the returned object.  The type of the returned table will match the type of the original source, so if it is a remote source, such as database table, then `tbl` will be a class `tbl_sql`.  This will allow us to do two thing:

- View the SQL statement that was used to find the final centers:
```{r}
dbplyr::remote_query(km)
```

## Under the hood

The `simple_kmeans_db()` function uses `dplyr` and 'tidyeval' to run the KMeans algorithm.  This means that when combined with `dbplyr`, the routines can be run inside a database.  

Unlike other packages that use this same methodology, such as `dbplot` and `tidypredict`, `simple_kmeans_db()` does not create a single `dplyr` code that can be extracted as SQL.  The function produces multiple, serial and dependent SQL statements that run individually inside the database. Each statement uses the current *centroids*, or centers, to estimate new centroids, and then it uses those centroids in a consecutive SQL statement to see if there was any variance.  Effectively, this approach uses R not only as translation layer, but also as an orchestration layer.

## Safeguards for long running jobs

The`simple_kmeans_db()` approach of using multiple and consecutive SQL queries to find the optimal centers, additionally, in KMeans clustering, it matters the order in which the each set of centers is passed.  This creates an imperative to find a way to cache the current centers used in a long running job, in case the job is canceled or fails.  Starting from the centers that were calculated last, will mean that re-starting the job will not being from "0", but from a more advanced, read closer, set of centers.  

The safeguard implemented in this function is trough a file, called *kmeans.csv*.  Each cycle will update the file.  The file name can be changed by modifying the `safeguard_file` argument. Setting the argument to NULL will turn off the safeguard.  The file will be saved to the temporary directory of the R session.. 

In this example we will set the `max_repats` to 10, so as to artificially avoid finding the optimal means
```{r}
km <- db_flights %>%
  simple_kmeans_db(dep_time, distance, max_repeats = 10)
```

In the next run, the "kmeans.csv" file is passed as the `initial_kmeans` argument.  This will make `simple_kmeans_db()` use those centers as the starting point:

```{r}
km <- db_flights %>%
  simple_kmeans_db(dep_time, distance, initial_kmeans = read.csv(file.path(tempdir(), "kmeans.csv")))
```

The second run took 7 cycles to complete, which adds up to the 17 cycles that it initially took in the first example at the top of this article.