| title | description | og | extra_header_bits | extra_body_bits | |||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
🧪 🕸️ WebR, Vite + 🦆 DuckDB via Observable's Standard Library |
A Toy Modeling Example |
|
|
|
|
Experiment Hypothesis:
We can use DuckDB to wrangle data for us, let R do some "modeling", and let Observable Plot show us the results
Experiment parameters:
- Webr
- An R function we'll make directly callable as a JS function
- Observable Standard Library's
DuckDBCLient - Observable Plot
- Lit (web components)
- Vite (for building)
GreyNoise will reach 1,000 tags on or about ❓❓❓❓❓❓.
Building off of the previous experiment, today we will combine DuckDB data ops with WebR, letting R do some trivial modeling with glm on data we load and wrangle with DuckDB.
Let's be super clear, right up front: this data is small enough to load into R, process in R, and then model and plot in R without any other packages (save {svglite}). It is deliberately a toy example to make it easier to work with while showing the core concepts of loading from a database, doing a more than trivial database query, passing data to R, and getting a result back.
At work, one of the core work products from my team are what we call "tags". They are detection rules for vulnerability exploit checks/attempts, good/bad actors, and more. We're coming up on the human-psyche-significant "1,000" value for total number of tags. Today's example predicts when that happens based on the volume time series.
Here are the tables we have:
This is the schema for our tags table:
This is what's in it:
Now, we need to compute the cumulative sum for each day and keep track of days elapsed so we can pass those vectors to our model.
It's not a horrible SQL query, especially if we break it up using common table expressions (ref: duckdb.js):
-- Setup a date range that spans the entire min/max created_at
-- We need this b/c we don't have tags every day so there are
-- gaps in the time series
WITH date_range AS (
SELECT UNNEST(generate_series(
(SELECT MIN(created_at) FROM tags),
(SELECT MAX(created_at) FROM tags),
INTERVAL '1 day'
)) AS date
),
-- count number of tags/day
grouped_tags AS (
SELECT
created_at,
COUNT(*) AS daily_count
FROM
tags
GROUP BY
created_at
),
-- join to the full range and fill in values
joined_dates_counts AS (
SELECT
dr.date,
COALESCE(gt.daily_count, 0) AS filled_daily_count
FROM
date_range dr
LEFT JOIN
grouped_tags gt
ON
dr.date = gt.created_at
)
-- get the cumulative sum and days since the min created_at
SELECT
date,
filled_daily_count,
SUM(filled_daily_count) OVER (ORDER BY date) AS running_cumulative_sum,
DATEDIFF('day', (SELECT MIN(date) FROM joined_dates_counts), date) AS days_elapsed
FROM
joined_dates_counts;Here's what those "tag stats" look like:
We will use R to predict when the tag count will reach a specified value, this is the function we'll be using (ref: r.js):
function(csum, days_elapsed, target_csum) {
# saddest. model. ever.
model <- glm(csum ~ days_elapsed, family = "poisson")
predicted_days_elapsed <- days_elapsed
predicted_days_elapsed_ret <- c()
predicted_days_csum_ret <- c()
while (TRUE) {
predicted_days_elapsed <- max(predicted_days_elapsed) + 1
predict(
model,
newdata = data.frame(days_elapsed = predicted_days_elapsed),
type = "response"
) -> predicted_csum
predicted_days_csum_ret <- c(predicted_days_csum_ret, predicted_csum)
predicted_days_elapsed_ret <- c(predicted_days_elapsed_ret, predicted_days_elapsed)
if (predicted_csum >= target_csum) break
}
data.frame(
days_elapsed = predicted_days_elapsed_ret,
tagCount = predicted_days_csum_ret
)
}Sure, that could be fancier, but we don't need fancy for this example.
We then use the fact that:
await R`function NAME(…) {}`produces a callable JS function (also in r.js) and we use it with the vectors we made from the database
// call the function
const nDays = await predict(
tagsCumSum.map(d => d.csum),
tagsCumSum.map(d => d.days_elapsed),
1_000
)
// get the last ("1,000" prediction) elapsed day and min date
const lastDay = nDays.values[0].values[ nDays.values[0].values.length-1]
const minDate = ddbResToArray(
await db.sql`SELECT min(created_at) AS min_date FROM tags`
)[0].min_date
// …
// display the computed "1,000" date
predictedDate.textContent = addDays(minDate, lastDay).toDateString()Core files:
├── index.md # what we render into index.html via the justfile
├── src
│ ├── components.css # CSS specific to component styling
│ ├── index.css # core SSS
│ ├── action-button.js # Lit component for the button
│ ├── data-frame-view.js # Lit component for displaying tables
│ ├── ojs-shorthand-plot.js # Lit component for Observable plots
│ ├── simple-message.js # Lit component for simple output messages/text
│ ├── status-message.js # Lit component for my WebR status message up top
│ ├── main.js # main app runner
│ ├── r.js # WebR context creation and support functions
│ ├── duckdb.js # DuckDB context creation and support functions and queries
│ └── utils.js # Miscellaneous utilities
└──
You can find the source on GitHub.
Brought to you by @hrbrmstr
"Carnac" image by The Tonight Show Starring Johnny Carson, Fair use, https://en.wikipedia.org/w/index.php?curid=2560897