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.
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