7 Modeling with databases

7.1 Single step sampling

Use PostgreSQL TABLESAMPLE clause

Use connection_open() to open a Database connection

con <- connection_open(
  RPostgres::Postgres(),
  host =  "localhost",
  user = get("user"),
  password = get("pwd"),
  port = 5432,
  dbname = "postgres",
  bigint = "integer"
)

Set the orders variable to point to the orders table

orders <- tbl(con, in_schema("retail", "orders"))

Set the orders_view variable to point to the v_orders table

orders_view <- tbl(con, in_schema("retail", "v_orders"))

Pipe orders into the function show_query()

orders %>%
  show_query()

## <SQL>
## SELECT *
## FROM retail.orders

Pipe the previous command into the class() function to see the kind of output show_query() returns

orders %>%
  show_query() %>%
  class()

## <SQL>
## SELECT *
## FROM retail.orders

## [1] "tbl_conn"         "tbl_PqConnection" "tbl_dbi"          "tbl_sql"         
## [5] "tbl_lazy"         "tbl"

Replace show_query() with remote_query() to compare the output types

orders %>%
  remote_query() %>%
  class()

## [1] "sql"       "character"

Replace class() with build_sql(). Use con as the value for the con argument

orders %>%
  remote_query() %>%
  build_sql(con = con)

## <SQL> SELECT *
## FROM retail.orders

Add " TABLESAMPLE BERNOULLI (0.1)" to build_sql() as another ... argument

orders %>%
  remote_query() %>%
  build_sql(con = con, " TABLESAMPLE BERNOULLI (0.1)")

## <SQL> SELECT *
## FROM retail.orders TABLESAMPLE BERNOULLI (0.1)

Pipe the code into tbl(). Use con for the con argument, and . for the rest

orders %>%
  remote_query() %>%
  build_sql(con = con, " TABLESAMPLE BERNOULLI (0.1)") %>%
  tbl(con, .)

## # Source:   SQL [?? x 3]
## # Database: postgres [rstudio_admin@localhost:5432/postgres]
##    order_id customer_id step_id
##       <int>       <int>   <dbl>
##  1   969600          39     965
##  2   970974          89     966
##  3   973046          86     967
##  4   973200          78     967
##  5   975219          75     968
##  6   975885          62     968
##  7   977001          21     969
##  8   979327          53     970
##  9   973304          26     972
## 10   973555          48     972
## # … with more rows

Use inner_join() to add the information from the orders_view pointer, use order_id as the matching field

orders %>%
  remote_query() %>%
  build_sql(con = con, " TABLESAMPLE BERNOULLI (0.1)") %>%
  tbl(con, .)  %>%
  inner_join(orders_view, by = "order_id")

## # Source:   lazy query [?? x 12]
## # Database: postgres [rstudio_admin@localhost:5432/postgres]
##    order_id customer_id.x step_id date  date_year date_month customer_id.y
##       <int>         <int>   <dbl> <chr>     <int>      <int>         <int>
##  1     5693            33       3 2016…      2016          1            33
##  2     7095            69       4 2016…      2016          1            69
##  3     7280            78       4 2016…      2016          1            78
##  4    11260            20       6 2016…      2016          1            20
##  5    11260            20       6 2016…      2016          1            22
##  6    15317            22      13 2016…      2016          1            87
##  7    15317            22      13 2016…      2016          1            22
##  8    15841            41       8 2016…      2016          1            41
##  9    15841            41       8 2016…      2016          1            51
## 10    21315            54      16 2016…      2016          1            54
## # … with more rows, and 5 more variables: customer_name <chr>,
## #   customer_lon <dbl>, customer_lat <dbl>, order_total <dbl>, order_qty <int>

Assign the resulting code to a variable orders_sample_db

orders_sample_db <- orders %>%
  remote_query() %>%
  build_sql(con = con, " TABLESAMPLE BERNOULLI (0.1)") %>%
  tbl(con, .)  %>%
  inner_join(orders_view, by = "order_id")

Use collect() to load the results of orders_sample_db to a new variable called orders_sample
```
orders_sample <- collect(orders_sample_db)
```
Load the dbplot library
```
library(dbplot)
```
Use dbplot_histogram() to visualize the distribution of order_total from orders_sample
```
orders_sample %>% 
  dbplot_histogram(order_total, binwidth = 5)
```
Use dbplot_histogram() to visualize the distribution of order_total from orders_view
```
orders_view %>% 
  dbplot_histogram(order_total, binwidth = 5)
```

7.2 Using `tidymodels` for modeling

Fit and measure the model’s performance using functions from parsnip and yardstick

Load the tidymodels library
```
library(tidymodels)
```

Start with the linear_reg() command, pipe into set_engine(), and use “lm” as its sole argument

linear_reg() %>%
  set_engine("lm")

## Linear Regression Model Specification (regression)
## 
## Computational engine: lm

Pipe into the fit() command. Use the formula: order_total ~ order_qty, and orders_sample as the data argument

linear_reg() %>%
  set_engine("lm") %>%
  fit(order_total ~ order_qty, data = orders_sample)

## parsnip model object
## 
## Fit in:  126ms
## Call:
## stats::lm(formula = formula, data = data)
## 
## Coefficients:
## (Intercept)    order_qty  
##     0.09443      6.62037

Assign the previous code to a variable called parsnip_model

parsnip_model <- linear_reg() %>%
  set_engine("lm") %>%
  fit(order_total ~ order_qty, data = orders_sample)

Use bind_cols() to add the predictions to order_sample. Calculate the prediction with predict()

orders_sample %>% 
  bind_cols(predict(parsnip_model, orders_sample))

## # A tibble: 1,964 x 13
##    order_id customer_id.x step_id date  date_year date_month customer_id.y
##       <int>         <int>   <dbl> <chr>     <int>      <int>         <int>
##  1     1864            35       1 2016…      2016          1            35
##  2     3617            13       2 2016…      2016          1            13
##  3     5279            81       3 2016…      2016          1            81
##  4     5426            42       3 2016…      2016          1            42
##  5     7472             1       4 2016…      2016          1             1
##  6     7967            50       4 2016…      2016          1            50
##  7    11174            74       6 2016…      2016          1            74
##  8    11174            74       6 2016…      2016          1            50
##  9    11532            22      11 2016…      2016          1            62
## 10    11532            22      11 2016…      2016          1            22
## # … with 1,954 more rows, and 6 more variables: customer_name <chr>,
## #   customer_lon <dbl>, customer_lat <dbl>, order_total <dbl>, order_qty <int>,
## #   .pred <dbl>

Pipe the code into the metrics() function. Use order_total as the truth argument, and .pred as the estimate argument

orders_sample %>% 
  bind_cols(predict(parsnip_model, orders_sample)) %>%
  metrics(truth = order_total, estimate = .pred)

## # A tibble: 3 x 3
##   .metric .estimator .estimate
##   <chr>   <chr>          <dbl>
## 1 rmse    standard       3.50 
## 2 rsq     standard       0.939
## 3 mae     standard       2.78

7.3 Score with `tidypredict`

Load the tidypredict library
```
library(tidypredict)
```
Use the parse_model() function to parse parsnip_model, and assign it to a variable called parsed_parsnip
```
parsed_parsnip <- parse_model(parsnip_model)
```

Use str() to see the parsed_parsnip object’s structure

str(parsed_parsnip)

## List of 2
##  $ general:List of 6
##   ..$ model   : chr "lm"
##   ..$ version : num 2
##   ..$ type    : chr "regression"
##   ..$ residual: int 1962
##   ..$ sigma2  : num 12.3
##   ..$ is_glm  : num 0
##  $ terms  :List of 2
##   ..$ :List of 5
##   .. ..$ label       : chr "(Intercept)"
##   .. ..$ coef        : num 0.0944
##   .. ..$ is_intercept: num 1
##   .. ..$ fields      :List of 1
##   .. .. ..$ :List of 2
##   .. .. .. ..$ type: chr "ordinary"
##   .. .. .. ..$ col : chr "(Intercept)"
##   .. ..$ qr          :List of 2
##   .. .. ..$ qr_1: num -0.0226
##   .. .. ..$ qr_2: num -0.0639
##   ..$ :List of 5
##   .. ..$ label       : chr "order_qty"
##   .. ..$ coef        : num 6.62
##   .. ..$ is_intercept: num 0
##   .. ..$ fields      :List of 1
##   .. .. ..$ :List of 2
##   .. .. .. ..$ type: chr "ordinary"
##   .. .. .. ..$ col : chr "order_qty"
##   .. ..$ qr          :List of 2
##   .. .. ..$ qr_1: num 0
##   .. .. ..$ qr_2: num 0.0109
##  - attr(*, "class")= chr [1:3] "parsed_model" "pm_regression" "list"

Use tidypredict_fit() to view the dplyr formula that calculates the prediction

tidypredict_fit(parsed_parsnip)

## 0.0944307069267399 + (order_qty * 6.62036536915535)

Use head() to get the first 10 records from orders_view

orders_view %>%
  head(10)

## # Source:   lazy query [?? x 10]
## # Database: postgres [rstudio_admin@localhost:5432/postgres]
##    order_id date  date_year date_month customer_id customer_name customer_lon
##       <int> <chr>     <int>      <int>       <int> <chr>                <dbl>
##  1     1001 2016…      2016          1          22 Dr. Birdie K…        -122.
##  2     1002 2016…      2016          1           6 Meggan Bruen         -122.
##  3     1003 2016…      2016          1          80 Jessee Rodri…        -122.
##  4     1004 2016…      2016          1          55 Kathryn Stehr        -122.
##  5     1005 2016…      2016          1          73 Merlyn Runol…        -122.
##  6     1006 2016…      2016          1          70 Reggie Mills         -122.
##  7     1007 2016…      2016          1          55 Kathryn Stehr        -122.
##  8     1008 2016…      2016          1          40 Dr. Trace Gl…        -122.
##  9     1009 2016…      2016          1          78 Pricilla Goo…        -122.
## 10     1010 2016…      2016          1          35 Mr. Commodor…        -122.
## # … with 3 more variables: customer_lat <dbl>, order_total <dbl>, order_qty <int>

Pipe the code into mutate(). Assign to a new my_pred variable the results of tidypredict_fit(). Make sure to prefix tidypredict_fit() with the bang-bang operator so that the formula is evaluated.

orders_view %>%
  head(10) %>%
  mutate(my_pred = !! tidypredict_fit(parsed_parsnip))

## # Source:   lazy query [?? x 11]
## # Database: postgres [rstudio_admin@localhost:5432/postgres]
##    order_id date  date_year date_month customer_id customer_name customer_lon
##       <int> <chr>     <int>      <int>       <int> <chr>                <dbl>
##  1     1001 2016…      2016          1          22 Dr. Birdie K…        -122.
##  2     1002 2016…      2016          1           6 Meggan Bruen         -122.
##  3     1003 2016…      2016          1          80 Jessee Rodri…        -122.
##  4     1004 2016…      2016          1          55 Kathryn Stehr        -122.
##  5     1005 2016…      2016          1          73 Merlyn Runol…        -122.
##  6     1006 2016…      2016          1          70 Reggie Mills         -122.
##  7     1007 2016…      2016          1          55 Kathryn Stehr        -122.
##  8     1008 2016…      2016          1          40 Dr. Trace Gl…        -122.
##  9     1009 2016…      2016          1          78 Pricilla Goo…        -122.
## 10     1010 2016…      2016          1          35 Mr. Commodor…        -122.
## # … with 4 more variables: customer_lat <dbl>, order_total <dbl>,
## #   order_qty <int>, my_pred <dbl>

Replace the mutate() command with tidypredict_to_column()

orders_view %>%
  head(10) %>%
  tidypredict_to_column(parsnip_model)

## # Source:   lazy query [?? x 11]
## # Database: postgres [rstudio_admin@localhost:5432/postgres]
##    order_id date  date_year date_month customer_id customer_name customer_lon
##       <int> <chr>     <int>      <int>       <int> <chr>                <dbl>
##  1     1001 2016…      2016          1          22 Dr. Birdie K…        -122.
##  2     1002 2016…      2016          1           6 Meggan Bruen         -122.
##  3     1003 2016…      2016          1          80 Jessee Rodri…        -122.
##  4     1004 2016…      2016          1          55 Kathryn Stehr        -122.
##  5     1005 2016…      2016          1          73 Merlyn Runol…        -122.
##  6     1006 2016…      2016          1          70 Reggie Mills         -122.
##  7     1007 2016…      2016          1          55 Kathryn Stehr        -122.
##  8     1008 2016…      2016          1          40 Dr. Trace Gl…        -122.
##  9     1009 2016…      2016          1          78 Pricilla Goo…        -122.
## 10     1010 2016…      2016          1          35 Mr. Commodor…        -122.
## # … with 4 more variables: customer_lat <dbl>, order_total <dbl>,
## #   order_qty <int>, fit <dbl>

Load the yaml library
```
library(yaml)
```
Use write_yaml() to save the contents of parsed_parsnip into a file called model.yaml
```
write_yaml(parsed_parsnip, "model.yaml")
```
Using read_yaml(), read the contents of the model.yaml file into the a new variable called loaded_model
```
loaded_model <- read_yaml("model.yaml")
```
Use as_parsed_model() to convert the loaded_model variable into a tidypredict parsed model object, assign the results to loaded_model_2
```
loaded_model_2 <- as_parsed_model(loaded_model)
```

7.4 Run predictions in DB

Load the modeldb library
```
library(modeldb)
```

Use select() to pick the order_total and order_qty fields from the orders_sample_db table pointer

orders_sample_db %>%
  select(order_total, order_qty)

## # Source:   lazy query [?? x 2]
## # Database: postgres [rstudio_admin@localhost:5432/postgres]
##    order_total order_qty
##          <dbl>     <int>
##  1       23.2          3
##  2        7.53         1
##  3       13.7          2
##  4       17.6          3
##  5       12.6          2
##  6       31.7          5
##  7       31.7          5
##  8       21.2          3
##  9       21.2          3
## 10       37.1          6
## # … with more rows

Pipe the code into the linear_regression_db() function, pass order_total as the only argument

orders_sample_db %>%
  select(order_total, order_qty) %>% 
  linear_regression_db(order_total)

## # A tibble: 1 x 2
##   `(Intercept)` order_qty
##           <dbl>     <dbl>
## 1         0.105      6.64

Assign the model results to a new variable called db_model

db_model <- orders_sample_db %>%
  select(order_total, order_qty) %>% 
  linear_regression_db(order_total)

Use as_parsed_model() to convert db_model to a parsed model object. Assign to new a variable called pm
```
pm <- as_parsed_model(db_model)
```

Use head() to get the top 10 records, and then pipe into tidypredict_to_column() to add the results from pm

orders_view %>%
  head(10) %>%
  tidypredict_to_column(pm)

## # Source:   lazy query [?? x 11]
## # Database: postgres [rstudio_admin@localhost:5432/postgres]
##    order_id date  date_year date_month customer_id customer_name customer_lon
##       <int> <chr>     <int>      <int>       <int> <chr>                <dbl>
##  1     1001 2016…      2016          1          22 Dr. Birdie K…        -122.
##  2     1002 2016…      2016          1           6 Meggan Bruen         -122.
##  3     1003 2016…      2016          1          80 Jessee Rodri…        -122.
##  4     1004 2016…      2016          1          55 Kathryn Stehr        -122.
##  5     1005 2016…      2016          1          73 Merlyn Runol…        -122.
##  6     1006 2016…      2016          1          70 Reggie Mills         -122.
##  7     1007 2016…      2016          1          55 Kathryn Stehr        -122.
##  8     1008 2016…      2016          1          40 Dr. Trace Gl…        -122.
##  9     1009 2016…      2016          1          78 Pricilla Goo…        -122.
## 10     1010 2016…      2016          1          35 Mr. Commodor…        -122.
## # … with 4 more variables: customer_lat <dbl>, order_total <dbl>,
## #   order_qty <int>, fit <dbl>

7 Modeling with databases

7.1 Single step sampling

7.2 Using tidymodels for modeling

7.3 Score with tidypredict

7.4 Run predictions in DB

7.2 Using `tidymodels` for modeling

7.3 Score with `tidypredict`