Making neural network predictions with presto SQL

There are many ways to deploy and make a prediction with a neural network.

Today I will focus on a solution that relies solely on presto. Using only SQL queries for the predictions and storing all model weights on its tables.

This is not a post to defend on why this solution works neither defending that this might be a good solution for you. It's just a solution I've developed for the workloads we use on Kovi and thought it might be useful for anyone that is a heavy user of AWS Athena like we are.

This post also won't explain how a neural network works,

Creating sample model

To create a proof of concept, I'll create a simple analytical solution for the following neural network.

Sample neural network diagram

The x0 is the input layer with a single feature, the output is also single. Aside from the weights, every neuron, except for those on the input layer, will have a bias. So we have:

Given the weights, bias, and setting a generic activation a1 for the hidden layer and a generic activation a2 for the output layer we can solve the output with the following equation:

Creating the weights table

To start our model I will define a table with the following syntax:

Using the following query:

Creating the layer activations table

The layer activations have the following syntax:

Using the following query:

Creating the bias table

And last but not least, the bias table uses the following syntax:

Using the following query:

Densifying parameters

Since there's an arbitrary number of neurons and layers in a model, we'll have to use a function that might loop every item. The functions that work this way on presto are array function, so we'll transform thus tables in the model in array form.

First we'll densify the weights for each neuron.

And then densify the weights on each layer.

Finally densify the entire model.

Solving the analytical model

Since we're done preparing the parameters, it is time to solve that model. Using the solve query

Yields the following:

[a2(b3 + a1(b1 + x0 * w1) * w3 + a1(b2 + x0 * w2) * w4)]

Thus, the exact expected analytical result.

The entire analytical query is hosted on the following gist.

You can see a real implementation using numerical values and real activation functions on the following gist:

In the next posts…

We learned how to solve a generic artificial neural network on a presto, for the following post we'll have:

— Simple python script that convert weights between tensorflow and the weight tables
— Solving recurrent neural network on presto