knitr::opts_chunk$set( collapse = TRUE, comment = "#>" ) library(dplyr) library(purrr) library(rlang) library(nycflights13)
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.
In this example, a simple
RSQlite database will be use to load the
flights data from the
library(dplyr) con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:") RSQLite::initExtension(con) db_flights <- copy_to(con, nycflights13::flights, "flights")
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.
library(modeldb) km <- db_flights %>% simple_kmeans_db(dep_time, distance)
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
The final centers are are stored in the
centers variable of the returned object
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:
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
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.
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
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:
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.
Any scripts or data that you put into this service are public.
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.