Share this
Making Machine Learning easily available with SQL in BigQuery
by Tom Wamelink on May 20, 2021 9:56:03 AM
BigQuery ML (BQML) makes machine learning available directly from Google’s data warehouse using only SQL for coding. Models are built and evaluated in BigQuery and stored in tables where predictions can be made directly on data in the data warehouse. Many model types are supported, including deep neural networks, time series analysis, K-means clustering and linear and logistic regression. Tensorflow models can be imported for scoring or developing new models in BigQuery. There is also an Auto ML function that can find a good model for your case among alternative models.
In other words: BQML brings machine learning to the data warehouse rather than data to a machine learning platform. This increases the speed of developing models significantly. Data does not need to be moved or formatted for Python based ML frameworks and the dependence of less tools reduces complexity of the process.
Use case example: predicting conversion intent
We can predict the conversion of website visitors and use conversion intent for online audiences, for example to optimise advertising spend. My blog focuses especially on the functionality and ‘how to’ in BQML. For a more extensive example of predicting conversion intent you can read the blogs of my colleague Lotte about the same topic.
The following SQL statements will help you find a model to predict conversion intent:
- A first SQL statement appoints the location table to store the trained model, provides model parameters (logistic regression for this case), selects data for training just like any SQL query would (e.g. country, mobile device, operating system, pageviews for this case), appoints the label data (conversion) and starts the training process.
- A next statement using the ML.EVALUATE function starts evaluation of the model, provides the location of the previously stored model and selects data for evaluation again like any query would. Evaluation metrics for the model show up in BigQuery once the query ends.
- After finding a good model the next statement uses the ML.PREDICT function, providing the location of the model and selecting the input data of website visitors we want to predict conversion for directly from the data warehouse.
This use case only needs some basic SQL coding for applying Machine Learning and no knowledge of ML frameworks is required. Data analysts with knowledge of the data warehouse can build ML models themselves using SQL to prepare data, specify model parameters and training and evaluate the model. This makes BQML available to a wider audience in organisations and also reduces developing time.
There is an one-minute video about BQML on YouTube.
Interested in using BQML for your use case? We are happy to show you the abilities and guide you with your first BQML project.
ABOUT CRYSTALLOIDS
Crystalloids helps companies improve their customer experiences and build marketing technology. Founded in 2006 in the Netherlands, Crystalloids builds crystal-clear solutions that turn customer data into information and knowledge into wisdom. As a leading Google Cloud Partner, Crystalloids combines experience in software development, data science, and marketing, making them one of a kind IT company. Using the Agile approach Crystalloids ensures that use cases show immediate value to their clients and frees their time to focus on decision making and less on programming.
Share this
- November 2024 (5)
- October 2024 (2)
- September 2024 (1)
- August 2024 (1)
- July 2024 (4)
- June 2024 (2)
- May 2024 (1)
- April 2024 (4)
- March 2024 (2)
- February 2024 (2)
- January 2024 (4)
- December 2023 (1)
- November 2023 (4)
- October 2023 (4)
- September 2023 (4)
- June 2023 (2)
- May 2023 (2)
- April 2023 (1)
- March 2023 (1)
- January 2023 (4)
- December 2022 (3)
- November 2022 (5)
- October 2022 (3)
- July 2022 (1)
- May 2022 (2)
- April 2022 (2)
- March 2022 (5)
- February 2022 (3)
- January 2022 (5)
- December 2021 (5)
- November 2021 (4)
- October 2021 (2)
- September 2021 (2)
- August 2021 (3)
- July 2021 (4)
- May 2021 (2)
- April 2021 (2)
- February 2021 (2)
- January 2021 (1)
- December 2020 (1)
- October 2020 (2)
- September 2020 (1)
- August 2020 (2)
- July 2020 (2)
- June 2020 (1)
- March 2020 (2)
- February 2020 (1)
- January 2020 (1)
- December 2019 (1)
- November 2019 (3)
- October 2019 (2)
- September 2019 (3)
- August 2019 (2)
- July 2019 (3)
- June 2019 (5)
- May 2019 (2)
- April 2019 (4)
- March 2019 (2)
- February 2019 (2)
- January 2019 (4)
- December 2018 (2)
- November 2018 (2)
- October 2018 (1)
- September 2018 (2)
- August 2018 (3)
- July 2018 (3)
- May 2018 (2)
- April 2018 (4)
- March 2018 (5)
- February 2018 (2)
- January 2018 (3)
- November 2017 (2)
- October 2017 (2)