Introduction

When a database table is too large to load comfortably into R, the first step is usually to pull a representative sample rather than the full dataset. After that, you can run algorithms such as K-means for unsupervised clustering or K-nearest neighbors for supervised prediction on the sampled data.

The important part is to treat sampling as part of the modeling workflow, not as an afterthought. A biased or badly prepared sample will make both K-means and K-NN look worse than they really are.

Sampling from a Large Database

R can connect to databases through DBI and a backend package such as RSQLite, RMariaDB, or another driver. A simple pattern is:

1library(DBI)
2library(RSQLite)
3
4con <- dbConnect(RSQLite::SQLite(), ":memory:")
5
6dbWriteTable(
7  con,
8  "customers",
9  data.frame(
10    age = sample(18:70, 1000, replace = TRUE),
11    income = sample(30000:120000, 1000, replace = TRUE),
12    segment = sample(c("A", "B"), 1000, replace = TRUE)
13  )
14)
15
16sample_df <- dbGetQuery(
17  con,
18  "SELECT age, income, segment FROM customers LIMIT 200"
19)

LIMIT is fine for a toy example, but in a real database it may not be representative unless the table ordering is already random.

Sampling Strategy Matters

Common sampling approaches include:

• simple random sampling
• stratified sampling by class or segment
• time-based sampling for temporal data

For machine learning, stratified sampling is often safer when one class is much rarer than another. If you sample naively, the minority class may almost disappear.

If the database supports random ordering or table sampling efficiently, use that carefully. Otherwise it may be better to define a sampling key or pull stratified subsets explicitly.

K-means in R

K-means clusters numeric observations into k groups by minimizing within-cluster variance. Before running it, scale numeric variables so one large-magnitude feature does not dominate the distance calculation.

1features <- scale(sample_df[, c("age", "income")])
2
3set.seed(42)
4kmeans_result <- kmeans(features, centers = 3, nstart = 20)
5
6print(kmeans_result$centers)
7print(table(kmeans_result$cluster))

This assigns each sampled row to one of three clusters. K-means is unsupervised, so it does not use the segment label at all.

K-NN in R

K-nearest neighbors is different because it is supervised. You need labeled examples and a train-test split.

1library(class)
2
3set.seed(42)
4index <- sample(seq_len(nrow(sample_df)), size = 0.8 * nrow(sample_df))
5
6train_df <- sample_df[index, ]
7test_df <- sample_df[-index, ]
8
9train_x <- scale(train_df[, c("age", "income")])
10test_x <- scale(
11  test_df[, c("age", "income")],
12  center = attr(train_x, "scaled:center"),
13  scale = attr(train_x, "scaled:scale")
14)
15
16train_y <- train_df$segment
17test_y <- test_df$segment
18
19pred <- knn(train = train_x, test = test_x, cl = train_y, k = 5)
20table(Predicted = pred, Actual = test_y)

The important detail is that the test features are scaled using the training set center and scale values, not scaled independently.

Why Scaling Is Essential

Both K-means and K-NN rely on distance. If income is measured in tens of thousands and age is measured in tens, income will dominate the distance calculation unless you normalize or standardize the features.

That is why preprocessing is not optional here. It is part of the algorithm's behavior.

Working Incrementally

For very large databases, a common workflow is:

1. sample a manageable subset
2. explore and prototype the feature pipeline
3. validate the model logic
4. scale the data extraction strategy or move heavy computation closer to the database or compute platform

R is excellent for prototyping, but it is still worth being deliberate about how much data you move into memory.

Common Pitfalls

• Pulling the first N rows from a sorted table is often not a representative sample.
• Running K-means or K-NN on unscaled numeric features usually distorts distance-based behavior.
• Using K-NN without labeled target data is conceptually wrong; it is a supervised method.
• Sampling after heavy filtering can accidentally remove the variability you wanted the model to learn.

Summary

• Sample large databases deliberately before modeling in R.
• Use representative sampling, not just convenient row limits.
• Scale numeric features before applying K-means or K-NN.
• K-means is unsupervised, while K-NN requires labeled training data.
• Treat data extraction, preprocessing, and modeling as one connected workflow.