Predicting Bitcoin price with BigQuery ML

In the previous blog I described how to fetch a bitcoin data set and prepare it for AutoML Tables to create a (highly accurate) machine learning model to predict Bitcoin price.

This blog post describes a complementary (or alternative) approach – how to use BigQuery ML to create a (simpler) regression model using a SQL like syntax. The advantage of this approach is that training time is very rapid and that it is very easy to use for batch-based predictions also using SQL like syntax. The blog assumes that you have a table with data in BigQuery (see previous blog post for how to do that)

  1. Test the query that is the core part of the model – training data

Most of the data needed is already in the table, but in addition need to create the label to predict using LEAD() method and since BigQuery ML requires non-NULL data (e.g. not having NaN values) that is set to 0 for the label with IFNULL.

Figure 1 – BigQuery SQL query generating training data

2. Generate the regression model for predicting Bitcoin price

3. Evaluate model – get predicted_label as new value


Have shown how to use BigQuery ML regression on a BitCoin dataset to predict Bitcoin price, given how easy this is to use even at large scale (e.g. several hundred billion rows) this can be good start when doing predictions in on tabular data.

Best regards,

Amund Tveit

Continue Reading