## Featureform Our previous post explored different types of feature stores: [Physical](https://clickhouse.com/blog/powering-featurestores-with-clickhouse#physical-store), [Literal](https://clickhouse.com/blog/powering-featurestores-with-clickhouse#literal-store), and [Virtual](https://clickhouse.com/blog/powering-featurestores-with-clickhouse#literal-store). The Virtual store is best suited to ClickHouse, providing it with the opportunity to be used as both the offline store and transformation engine. In this architecture, the feature store is not responsible for managing transformations and the persistence and versioning of features but acts as an orchestrator only. To realize our vision of a virtual feature store, super-charged by ClickHouse, we identified Featureform as an ideal solution with which to integrate. As well as being open-source, allowing us to easily contribute, Featureform also offers mature (by design) integration points for offline stores, online stores, and vector databases. ## Getting started with Featureform and ClickHouse First we install featureform with a simple pip installation:These diagrams highlight additional key components of a feature store, notably the online store. Once a model is trained, it's deployed for real-time predictions, requiring both immediate data, like a user's ID, and precomputed features, such as historical purchases, which are too costly to generate on-the-fly. These features, stored in the online store for quick access, are crucial for latency-sensitive tasks like fraud detection. They are updated from the offline store to ensure they reflect the latest data. For more information, see our earlier blog post. While ClickHouse can be used as an online store, in this post we focus on the training examples and thus on the roles of transformation engine and offline store.
pip install featureform(.venv) clickhouse@PY test_project % featureform deploy docker --quickstart --include_clickhouse
DeprecationWarning: pkg_resources is deprecated as an API. See https://setuptools.pypa.io/en/latest/pkg_resources.html
Deploying Featureform on Docker
Starting Docker deployment on Darwin 23.3.0
Checking if featureform container exists...
Container featureform not found. Creating new container...
'featureform' container started
Checking if quickstart-postgres container exists...
Container quickstart-postgres not found. Creating new container...
'quickstart-postgres' container started
Checking if quickstart-redis container exists...
Container quickstart-redis not found. Creating new container...
'quickstart-redis' container started
Checking if quickstart-clickhouse container exists...
Container quickstart-clickhouse not found. Creating new container...
'quickstart-clickhouse' container started
…
Featureform is now running!
To access the dashboard, visit http://localhost:80
Run jupyter notebook in the quickstart directory to get started.
(.venv) dalemcdiarmid@PY test_project % clickhouse client
ClickHouse client version 24.1.1.1286 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 24.2.1.
55733a345323 :) SHOW TABLES IN fraud
SHOW TABLES FROM fraud
┌─name───────┐
│ creditcard │
└────────────┘
1 row in set. Elapsed: 0.005 sec.
### Connecting Featureform and ClickHouse Remember that Featureform will provide the feature store interface through which we access our data in ClickHouse, allowing us to define reusable and versioned features that we can pass to a model for training. This requires us to register our ClickHouse server with Featureform. This requires only a few lines of code, where we first define a Featureform client (local and insecure for this simple test example) before registering our local ClickHouse instance.We acknowledge that the models used in these notebooks (Logistical Regressions/Decision Trees) do not represent the “state of the art” algorithms for classification. However, the purpose of this blog post is to show how models can be trained with ClickHouse and Featureform and not to represent the “best” or “latest” techniques possible in predicting fraud. Feel free to adapt any examples to use more modern techniques! P.s. I’m also not an ML expert.
Note we use the environment variable
FEATUREFORM_HOSTto specify the location of the Featureform instance. These connection details can alternatively be passed to the client directly.
# install any dependencies into our notebook
!pip install featureform==1.12.6
!pip install river -U
!pip install scikit-learn -U
!pip install seaborn
!pip install googleapis-common-protos
!pip install matplotlib
!pip install matplotlib-inline
!pip install ipywidgets
%env FEATUREFORM_HOST=localhost:7878
%matplotlib inline
from featureform import Client
import featureform as ff
import matplotlib.pyplot as plt
import seaborn as sns
from sklearn.linear_model import SGDClassifier, LogisticRegression
from sklearn.metrics import classification_report
from sklearn.metrics import accuracy_score
import numpy as np
# Featureform client. Local instance and insecure
client = Client(insecure=True)
# Register our local container with Featureform
clickhouse = ff.register_clickhouse(
name="clickhouse",
description="A ClickHouse deployment for example",
host="host.docker.internal",
port=9000,
user="default",
password="",
database="fraud"
)
client.apply(verbose=True)
### Registering a table In addition to acting as a transformation engine and offline store, ClickHouse is, first and foremost, a data source in the ML Ops workflow. Before we can transform and extract features from our fraud table, we first need to register this table in Featureform. This creates the necessary metadata in Featureform so we can version and track our data. The code for this is minimal.The
client.apply()method in Featureform creates the defined resources. These are otherwise evaluated lazily and are created as required (i.e. when used downstream). Registering this dataset causes it to be internally copied in ClickHouse using anINSERT INTO SELECT, effectively making an immutable copy of the data for analysis that Featureform has versioned. See below for further details.
creditcard = clickhouse.register_table(
name="creditcard",
table="creditcard",
)
data = client.dataframe(creditcard, limit=100)
data.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 100 entries, 0 to 99
Data columns (total 31 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 id 100 non-null int64
1 V1 100 non-null float64
2 V2 100 non-null float64
3 V3 100 non-null float64
4 V4 100 non-null float64
5 V5 100 non-null float64
6 V6 100 non-null float64
7 V7 100 non-null float64
8 V8 100 non-null float64
9 V9 100 non-null float64
10 V10 100 non-null float64
11 V11 100 non-null float64
12 V12 100 non-null float64
13 V13 100 non-null float64
14 V14 100 non-null float64
15 V15 100 non-null float64
16 V16 100 non-null float64
17 V17 100 non-null float64
18 V18 100 non-null float64
19 V19 100 non-null float64
20 V20 100 non-null float64
21 V21 100 non-null float64
22 V22 100 non-null float64
23 V23 100 non-null float64
24 V24 100 non-null float64
25 V25 100 non-null float64
26 V26 100 non-null float64
27 V27 100 non-null float64
28 V28 100 non-null float64
29 Amount 100 non-null float64
30 Class 100 non-null int64
dtypes: float64(29), int64(2)
memory usage: 24.3 KB
data['Class'].value_counts().plot.pie(autopct='%3.1f%%',shadow=True, legend= True,startangle =45)
plt.title('Distribution of Class',size=14)
plt.show()
@clickhouse.sql_transformation(inputs=[creditcard])
def classCounts(creditcard):
return "SELECT Class,count() c FROM {{creditcard}} GROUP BY Class"
client.dataframe(classCounts).pivot_table(index='Class', columns=None, values='c', aggfunc='sum').plot.pie(
explode=[0.1, 0], autopct='%3.1f%%', shadow=True, legend=True, startangle=45, subplots=True)
Similarly, when first exploring data, users often use the `describe` method for a data frame to understand the properties of the columns.With our primary data serving as the raw materials, these are then transformed into data sets containing the set of features and labels required for serving and training machine learning models. These transformations can be directly applied to primary data sets or sequenced and executed on other previously transformed data sets. It’s essential to understand that Featureform does not perform the data transformations itself. Instead, it orchestrates ClickHouse to execute the transformation. The results of the transformation will also be stored as a versioned table in ClickHouse for later fast retrieval - a process known as materialization.
data.describe()
import re
@clickhouse.sql_transformation(inputs=[creditcard])
def describe_creditcard(creditcard):
return "SELECT * APPLY count, * APPLY avg, * APPLY std, * APPLY x -> (quantiles(0.25)(x)[1]), * APPLY x -> (quantiles(0.5)(x)[1]), * APPLY x -> (quantiles(0.75)(x)[1]), * APPLY min, * APPLY max FROM {{creditcard}}"
df = client.dataframe(describe_creditcard, limit=1)
df_melted = df.melt()
df_melted['Var'] = df_melted['variable'].apply(lambda x: re.search(r"\((\w*)\)", x).group(1))
df_melted['Statistic'] = df_melted['variable'].apply(
lambda x: re.search(r"(.*)\(\w*\)", x).group(1).replace('arrayElement(', ''))
df_melted.pivot(index='Statistic', columns='Var', values='value').reset_index()
Our final notebook, which users can find in the [Featureform example repository](https://github.com/featureform/featureform/blob/main/notebooks/Fraud_Detection_with_ClickHouse.ipynb), contains a number of transformations designed to perform an analysis of the data. These reproduce the work done by the author of the original notebook, which used pandas directly. We highlight a few of the more interesting transformations below. Correlation matrices can help us understand any linear relationships between variables/columns in our dataset. In pandas, this requires a `corr` method call on the data frame. This specific operation is quite computationally expensive on very large datasets. In SQL, this requires the use of the ClickHouse `corrMatrix` function. The following transformation pivots the results to be consistent with the format expected by the popular [Seaborn visualization library](https://seaborn.pydata.org/).In later versions of the Featureform integration, we would like to abstract this complexity away from the user. Ideally,
describe()should formulate the required ClickHouse query for the user and execute this transparently for the user. Stay tuned.
@clickhouse.sql_transformation(inputs=[creditcard])
def credit_correlation_matrix(creditcard):
return """WITH matrix AS
(
SELECT arrayJoin(arrayMap(row -> arrayMap(col -> round(col, 3), row),
corrMatrix(id, V1, V2, V3, V4, V5, V6, V7, V8, V9, V10, V11, V12, V13, V14,
V15, V16, V17, V18, V19, V20, V21, V22, V23, V24, V25, V26, V27, V28, Amount, Class))) AS matrix
FROM {{creditcard}}
)
SELECT
matrix[1] AS id, matrix[2] AS V1, matrix[3] AS V2, matrix[4] AS V3, matrix[5] AS V4,
matrix[6] AS V5, matrix[7] AS V6, matrix[8] AS V7, matrix[9] AS V8, matrix[10] AS V9,
matrix[11] AS V10, matrix[12] AS V11, matrix[13] AS V12, matrix[14] AS V13,
matrix[15] AS V14, matrix[16] AS V15, matrix[17] AS V16, matrix[18] AS V17,
matrix[19] AS V18, matrix[20] AS V19, matrix[21] AS V20, matrix[22] AS V21,
matrix[23] AS V22, matrix[24] AS V23, matrix[25] AS V24, matrix[26] AS V25,
matrix[27] AS V26, matrix[28] AS V27, matrix[29] AS V28, matrix[30] AS Amount,
matrix[31] AS Class
FROM matrix"""
client.dataframe(credit_correlation_matrix)
paper = plt.figure(figsize=[20, 10])
sns.heatmap(client.dataframe(credit_correlation_matrix, limit=100), cmap='crest', annot=True)
plt.show()
While most of our `V*` features are in the same range, we can see from our earlier `.describe()` that `Amount` has a different scale. We can confirm this scale with a simple box plot.Typically, we might use the results here to identify redundant features. A high correlation between two features suggests that they might convey similar information, and one can potentially be removed without losing significant predictive power. Additionally, correlation with the target variable can highlight which features are most relevant. However, the original notebook obtained good results without doing this.
@clickhouse.sql_transformation(inputs=[creditcard])
def amountQuantitles(creditcard):
return "SELECT arrayJoin(quantiles(0, 0.25, 0.5, 0.75, 1.)(Amount)) AS Amount FROM {{creditcard}}"
client.dataframe(amountQuantitles).plot.box()
@clickhouse.sql_transformation(inputs=[creditcard])
def anynull_creditcard(creditcard):
return "SELECT * APPLY x -> sum(if(x IS NULL, 1, 0)) FROM {{creditcard}}"
client.dataframe(anynull_creditcard, limit=1).melt()
@clickhouse.sql_transformation(inputs=[creditcard])
def credit_duplicates(creditcard):
return "SELECT *, count() AS cnt FROM {{creditcard}} GROUP BY * HAVING cnt > 1"
client.dataframe(credit_duplicates, limit=1)
@clickhouse.sql_transformation(inputs=[creditcard])
def scaled_credit_cards(creditcard):
column_averages = ', '.join([f'avg(V{i})' for i in range(1, 29)])
column_std_deviations = ', '.join([f'stddevPop(V{i})' for i in range(1, 29)])
columns_scaled = ', '.join(f'(V{i} - avgs[{i}]) / stds[{i}] AS V{i}' for i in range(1,29))
return f"WITH ( SELECT [{column_averages}, avg(Amount)] FROM {{{{creditcard}}}} ) AS avgs, (SELECT [{column_std_deviations}, stddevPop(Amount)] FROM {{{{creditcard}}}}) AS stds SELECT id, { columns_scaled }, (Amount - avgs[29])/stds[29] AS Amount, Class FROM {{{{creditcard}}}}"
client.apply()
@ff.entity
class Transaction:
# Register multiple columns from a dataset as features
transaction_features = ff.MultiFeature(
scaled_credit_cards,
client.dataframe(scaled_credit_cards, limit=10),
include_columns=[f"V{i}" for i in range(1, 29)] + ["Amount"],
entity_column="id",
exclude_columns=["Class"],
)
fraud = ff.Label(
scaled_credit_cards[["id", "Class"]], type=ff.Bool,
)
fraud_training_set = ff.register_training_set(
"fraud_training_set",
label=Transaction.fraud,
features=Transaction.transaction_features,
)
client.apply()
With our "training set" ready, we now need to split this into a training and validation set. For Featureform, this is a simple call, requiring us to specify the ratio of the split. For this, we use a classic 80/20 split.Under the hood, each feature is represented by its own table. This allows new entities to be composed of features. This might not be necessary in our simple example, but it unlocks collaboration and reusability, as we’ll discuss later.
# fetch the training set through the FF client
ds = client.training_set(fraud_training_set)
# split our training set ino a train and test set
train, test = ds.train_test_split(test_size=0.2, train_size=0.8, shuffle=True, random_state=3, batch_size=1000)
clf = SGDClassifier(loss='log_loss')
for features, label in train:
clf.partial_fit(features, label, classes=[True,False])
from sklearn.metrics import confusion_matrix
def model_eval(actual, predicted):
acc_score = accuracy_score(actual, predicted)
conf_matrix = confusion_matrix(actual, predicted)
clas_rep = classification_report(actual, predicted)
print('Model Accuracy is: ', round(acc_score, 2))
print(conf_matrix)
print(clas_rep)
def plot_confusion_matrix(cm, classes=None, title='Confusion matrix'):
"""Plots a confusion matrix."""
if classes is not None:
sns.heatmap(cm, xticklabels=classes, yticklabels=classes, vmin=0., vmax=1., annot=True)
else:
sns.heatmap(cm, vmin=0., vmax=1.)
plt.title(title)
plt.ylabel('True label')
plt.xlabel('Predicted label')
# Make a test prediction
pred_test= np.array([])
label_test = np.array([])
for features, label in test:
batch_pred = np.array(clf.predict(features))
pred_test = np.concatenate([pred_test, batch_pred])
label_test = np.concatenate([label_test, np.array(label)])
model_eval(label_test, pred_test)
cm = confusion_matrix(label_test, pred_test)
plot_confusion_matrix(cm, classes=[True, False], title='')
Model Accuracy is: 0.96
[[55024 1825]
[ 2358 54519]]
precision recall f1-score support
0.0 0.96 0.97 0.96 56849
1.0 0.97 0.96 0.96 56877
accuracy 0.96 113726
macro avg 0.96 0.96 0.96 113726
weighted avg 0.96 0.96 0.96 113726
from river import tree
from river import metrics
ds = client.training_set(fraud_training_set)
train, test = ds.train_test_split(test_size=0.2, train_size=0.8, shuffle=True, random_state=3, batch_size=1000)
# Initialize the HoeffdingAdaptiveTreeClassifier
model = tree.HoeffdingAdaptiveTreeClassifier()
# we need to pivot into [({"feature1": value1, "feature2": value2}, label), ...] for the model
feature_names = [t.name for t in Transaction.transaction_features]
n = 0
for features, label in train:
# Update the model with the current instance
for i, feature in enumerate(features):
feature_dict = dict(zip(feature_names, feature))
model.learn_one(feature_dict, label[i])
n += 1
print(f'{n} processed')
predicted_labels = []
actual_labels = []
for features, label in test:
for i, feature in enumerate(features):
feature_dict = dict(zip(feature_names, feature))
predicted_labels.append(model.predict_one(feature_dict))
actual_labels.append(label[i])
# Evaluate the model
model_eval(actual_labels, predicted_labels)
# Plot the confusion matrix
cm = confusion_matrix(actual_labels, predicted_labels)
plot_confusion_matrix(cm)
Model Accuracy is: 0.98
[[55715 1067]
[ 1234 55710]]
precision recall f1-score support
0 0.98 0.98 0.98 56782
1 0.98 0.98 0.98 56944
accuracy 0.98 113726
macro avg 0.98 0.98 0.98 113726
weighted avg 0.98 0.98 0.98 113726
Throughout this blog, we've mentioned how objects in Featureform are versioned without explicitly showing how. Featureform uses a mechanism known as variants to version data sources, transformations, features, labels, and training sets. Each of these resources is immutable by default, ensuring you can confidently utilize versioned resources created by others without the risk of disruption due to upstream modifications. For each resource, a variant parameter exists that can be configured either manually or automatically. Recognizing that later variants of features are not necessarily improvements over previous ones (Machine Learning isn't a linear journey of improvement, unfortunately), the term "[variant" is preferred over "version"](https://docs.featureform.com/concepts/versioning-and-variants#setting-all-variants-in-a-run). In our examples, we've simply relied on automatic versioning. If we changed any resource in our flow (e.g., change the features used for our entity), this would create a new variant of the resource and trigger the recreation of all dependency resources. This tracking of [lineage requires Directed Acyclic Graphs (DAG)](https://docs.featureform.com/concepts/immutability-lineage-and-dags) and employs techniques similar to tools such as dbt that users may be familiar with. ## Conclusion In this post, we've explored how ClickHouse can be used as a feature store to train machine learning models using Featureform. In this role, ClickHouse acts as both a transformation engine and an offline store. We have transformed and scaled a fraud dataset using only SQL and used the subsequent data to incrementally train both Logistic regression and Decision tree-based models to predict whether transactions are fraudulent or not. In the process, we have commented on how using an SQL database with feature stores allows a collaborative approach to feature engineering, improving the usability of features, which in turn reduces model iteration time. In future posts, we will explore how we can take this model to production and integrate with tooling such as AWS Sagemaker for model training.If you wish to explore Featureform more, we recommend familiarizing yourself with the abstractions it provides. We have also only explored where features and entities are materialized in ClickHouse as tables (or views). Featureform offers a number of other features, including on-demand features and streaming capabilities, when batch processing is not appropriate.