Editing article

Title

Summary

Content

<h4><strong>Demystifying machine learning for data analysts — build predictive models directly within your data warehouse</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Vi1P_vFVVIMno9Yd" /><figcaption><em>A vibrant illustration of a data warehouse with colorful machine learning algorithms swirling around it, representing the ease of integration</em></figcaption></figure><h4>As a data analyst, you’re constantly seeking insights to drive better business decisions. But traditional machine learning often means complex coding, separate environments, and a reliance on specialized skills that your team might not possess. What if you could tap into the power of predictive modeling without leaving the comfort of your familiar data warehouse?</h4><h3>Introduction</h3><p>BigQuery ML (BQML) opens the door to machine learning for those who are experts in SQL. It bridges the gap between data analysts and machine learning specialists, allowing you to create, train, and deploy a variety of powerful machine learning models directly within Google Cloud’s BigQuery.</p><h3>Purpose</h3><p>This blog post will guide you through a hands-on exploration of BigQuery ML. We’ll cover the basics, walk you through a practical use case, and discuss its potential to revolutionize how you use your data.</p><h3>Use Cases</h3><ul><li><strong>Predicting customer churn:</strong> Identify customers at risk of leaving.</li><li><strong>Fraud detection:</strong> Uncover unusual patterns in financial transactions.</li><li><strong>Demand forecasting:</strong> Predict future sales to optimize inventory.</li><li><strong>Sentiment analysis:</strong> Understand customer feedback trends.</li></ul><h3>Skill Prerequisites</h3><ul><li>Basic understanding of SQL.</li><li>Familiarity with BigQuery and Google Cloud Platform (GCP).</li></ul><h3>Disclaimer</h3><p>BigQuery ML is a powerful tool, but it’s important to use it responsibly. Ensure your data is unbiased and representative of real-world scenarios to avoid inaccurate or discriminatory predictions.</p><h3>Step-by-Step Walkthrough</h3><h3>Prerequisites</h3><ul><li>A Google Cloud Platform project with billing enabled.</li><li>BigQuery access and the necessary IAM permissions.</li><li>A dataset in BigQuery to train your model.</li></ul><h3><strong>Architecture Diagram</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*hTT3JmEa0zyDZN_V" /></figure><h3>Creating a Machine Learning Model in BigQuery ML</h3><ol><li><strong>Create your dataset</strong></li></ol><ul><li>To create a dataset, click on the <strong>View actions</strong> icon next to your project ID and select <strong>Create dataset</strong>.</li><li>Name your Dataset ID <strong><em>bqml_lab</em></strong> and click <strong>Create dataset</strong>.</li></ul><p><strong>2. Create a model</strong></p><ul><li>Go to BigQuery <strong>EDITOR</strong>, paste the following query to create a model that predicts purchase likelihood:</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Nor09QiVnXUSVHP2" /></figure><pre>CREATE OR REPLACE MODEL bqml_lab.sample_model<br>OPTIONS(model_type=&#39;logistic_reg&#39;) AS<br>SELECT<br>  IF(totals.transactions IS NULL, 0, 1) AS label,<br>  IFNULL(device.operatingSystem, &quot;&quot;) AS os,<br>  device.isMobile AS is_mobile,<br>  IFNULL(geoNetwork.country, &quot;&quot;) AS country,<br>  IFNULL(totals.pageviews, 0) AS pageviews<br>FROM<br>  bigquery-public-data.google_analytics_sample.ga_sessions_*<br>WHERE<br>  _TABLE_SUFFIX BETWEEN &#39;20160801&#39; AND &#39;20170631&#39;<br>LIMIT 100000;</pre><p><strong>Explanations:</strong></p><ul><li><em>bqml_lab</em> is the dataset, sample_model is the model name.</li><li>We’re using binary logistic regression (model_type=’<em>logistic_reg</em>’).</li><li>label is what we aim to predict (purchases).</li><li>Features include device OS, mobile status, country, and pageviews.</li></ul><p>3. <strong>Evaluate your model:</strong></p><ul><li>Replace the previous query with the following and click Run:</li></ul><pre>SELECT<br>  *<br>FROM<br>  ML.EVALUATE(MODEL `bqml_lab.sample_model`, (<br>SELECT<br>  IF(totals.transactions IS NULL, 0, 1) AS label,<br>  IFNULL(device.operatingSystem, &quot;&quot;) AS os,<br>  device.isMobile AS is_mobile,<br>  IFNULL(geoNetwork.country, &quot;&quot;) AS country,<br>  IFNULL(totals.pageviews, 0) AS pageviews<br>FROM<br>  `bigquery-public-data.google_analytics_sample.ga_sessions_*`<br>WHERE<br>  _TABLE_SUFFIX BETWEEN &#39;20170701&#39; AND &#39;20170801&#39;));</pre><ul><li>When the query is complete, click the Results tab below the query text area. You should see a table similar to this:</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*aeXd0JLNNxsqOQCn" /></figure><p><strong>Explanations:</strong></p><ul><li>Want to know how well your model performs? Check out these key terms: <strong>precision, recall, accuracy, f1_score, log_loss, roc_auc:</strong> You can consult the <a href="https://developers.google.com/machine-learning/glossary/">machine learning glossary</a> for definitions.</li></ul><p><strong>4. Use your model to predict outcomes</strong></p><ul><li>With this query you will try to predict the number of transactions made by visitors of each country, sort the results, and select the top 10 countries by purchases:</li></ul><pre>SELECT<br>  country,<br>  SUM(predicted_label) as total_predicted_purchases<br>FROM<br>  ML.PREDICT(MODEL `bqml_lab.sample_model`, (<br>SELECT<br>  IFNULL(device.operatingSystem, &quot;&quot;) AS os,<br>  device.isMobile AS is_mobile,<br>  IFNULL(totals.pageviews, 0) AS pageviews,<br>  IFNULL(geoNetwork.country, &quot;&quot;) AS country<br>FROM<br>  `bigquery-public-data.google_analytics_sample.ga_sessions_*`<br>WHERE<br>  _TABLE_SUFFIX BETWEEN &#39;20170701&#39; AND &#39;20170801&#39;))<br>GROUP BY country<br>ORDER BY total_predicted_purchases DESC<br>LIMIT 10;</pre><ul><li>When the query is complete, click the Results tab below the query text area. The results should look like the following:</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*390uRFMb4ttUw_5I" /></figure><p>🎊Congratulations! You used BigQuery ML to create a binary logistic regression model, evaluate the model, and use the model to make predictions.</p><h3>Outro</h3><h3>Recap key takeaways:</h3><ul><li>BigQuery ML allows you to build machine learning models using SQL.</li><li>No specialized machine learning expertise is required.</li><li>BQML models are easily integrated into your existing BigQuery workflows.</li></ul><h3>Call to Action/Next Steps</h3><ul><li>Explore other BQML model types: Experiment with classification, time series forecasting, and more.</li><li>Dive deeper into model evaluation and optimization techniques.</li></ul><p><strong>👋 </strong>By the way, if you happen to be a startup owner who is actively seeking to propel your business to new heights with Cloud:</p><p>We invite you to join our exclusive virtual live workshops (links below), where you’ll gain hands-on guidance from Google Cloud experts and discover how to seamlessly integrate GCP into your operations. Don’t miss this limited-time opportunity to empower your startup with the knowledge and expertise needed to thrive in the cloud-driven world. <a href="https://rsvp.withgoogle.com/events/let-s-learn-about-google-cloud">Register now</a> and secure your spot!</p><p>⚒️<a href="https://rsvp.withgoogle.com/events/onboarding-workshop">Startup Onboarding Workshop</a></p><p>⛑️<a href="https://rsvp.withgoogle.com/events/gen-ai-workshop">Generative AI Workshop</a></p><p>🔑<a href="https://rsvp.withgoogle.com/events/data-analytics">Data Analytics Workshop</a></p><p>🔐<a href="https://rsvp.withgoogle.com/events/security-workshop">Security Workshop</a></p><p>📠<a href="https://rsvp.withgoogle.com/events/app-mod-workshops">Modern Applications Workshop</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=0ae9ba293544" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/unlock-the-power-of-machine-learning-without-coding-a-beginners-guide-to-bigquery-ml-0ae9ba293544">Unlock the Power of Machine Learning Without Coding: A Beginner’s Guide to BigQuery ML</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>

Author

Link

Published date

Image url

Feed url

Guid

Hidden blurb

--- !ruby/object:Feedjira::Parser::RSSEntry
title: 'Unlock the Power of Machine Learning Without Coding: A Beginner’s Guide to
  BigQuery ML'
url: https://medium.com/google-cloud/unlock-the-power-of-machine-learning-without-coding-a-beginners-guide-to-bigquery-ml-0ae9ba293544?source=rss----e52cf94d98af---4
author: Brian Ling
categories:
- bigquery
- data
- bigquery-ml
- google-cloud-platform
- machine-learning
published: 2024-04-03 04:46:56.000000000 Z
entry_id: !ruby/object:Feedjira::Parser::GloballyUniqueIdentifier
  is_perma_link: 'false'
  guid: https://medium.com/p/0ae9ba293544
carlessian_info:
  news_filer_version: 2
  newspaper: Google Cloud - Medium
  macro_region: Blogs
rss_fields:
- title
- url
- author
- categories
- published
- entry_id
- content
content: "<h4><strong>Demystifying machine learning for data analysts — build predictive
  models directly within your data warehouse</strong></h4><figure><img alt=\"\" src=\"https://cdn-images-1.medium.com/max/1024/0*Vi1P_vFVVIMno9Yd\"
  /><figcaption><em>A vibrant illustration of a data warehouse with colorful machine
  learning algorithms swirling around it, representing the ease of integration</em></figcaption></figure><h4>As
  a data analyst, you’re constantly seeking insights to drive better business decisions.
  But traditional machine learning often means complex coding, separate environments,
  and a reliance on specialized skills that your team might not possess. What if you
  could tap into the power of predictive modeling without leaving the comfort of your
  familiar data warehouse?</h4><h3>Introduction</h3><p>BigQuery ML (BQML) opens the
  door to machine learning for those who are experts in SQL. It bridges the gap between
  data analysts and machine learning specialists, allowing you to create, train, and
  deploy a variety of powerful machine learning models directly within Google Cloud’s
  BigQuery.</p><h3>Purpose</h3><p>This blog post will guide you through a hands-on
  exploration of BigQuery ML. We’ll cover the basics, walk you through a practical
  use case, and discuss its potential to revolutionize how you use your data.</p><h3>Use
  Cases</h3><ul><li><strong>Predicting customer churn:</strong> Identify customers
  at risk of leaving.</li><li><strong>Fraud detection:</strong> Uncover unusual patterns
  in financial transactions.</li><li><strong>Demand forecasting:</strong> Predict
  future sales to optimize inventory.</li><li><strong>Sentiment analysis:</strong>
  Understand customer feedback trends.</li></ul><h3>Skill Prerequisites</h3><ul><li>Basic
  understanding of SQL.</li><li>Familiarity with BigQuery and Google Cloud Platform (GCP).</li></ul><h3>Disclaimer</h3><p>BigQuery
  ML is a powerful tool, but it’s important to use it responsibly. Ensure your data
  is unbiased and representative of real-world scenarios to avoid inaccurate or discriminatory
  predictions.</p><h3>Step-by-Step Walkthrough</h3><h3>Prerequisites</h3><ul><li>A
  Google Cloud Platform project with billing enabled.</li><li>BigQuery access and
  the necessary IAM permissions.</li><li>A dataset in BigQuery to train your model.</li></ul><h3><strong>Architecture
  Diagram</strong></h3><figure><img alt=\"\" src=\"https://cdn-images-1.medium.com/max/1024/0*hTT3JmEa0zyDZN_V\"
  /></figure><h3>Creating a Machine Learning Model in BigQuery ML</h3><ol><li><strong>Create
  your dataset</strong></li></ol><ul><li>To create a dataset, click on the <strong>View
  actions</strong> icon next to your project ID and select <strong>Create dataset</strong>.</li><li>Name
  your Dataset ID <strong><em>bqml_lab</em></strong> and click <strong>Create dataset</strong>.</li></ul><p><strong>2.
  Create a model</strong></p><ul><li>Go to BigQuery <strong>EDITOR</strong>, paste
  the following query to create a model that predicts purchase likelihood:</li></ul><figure><img
  alt=\"\" src=\"https://cdn-images-1.medium.com/max/1024/0*Nor09QiVnXUSVHP2\" /></figure><pre>CREATE
  OR REPLACE MODEL bqml_lab.sample_model<br>OPTIONS(model_type=&#39;logistic_reg&#39;)
  AS<br>SELECT<br>  IF(totals.transactions IS NULL, 0, 1) AS label,<br>  IFNULL(device.operatingSystem,
  &quot;&quot;) AS os,<br>  device.isMobile AS is_mobile,<br>  IFNULL(geoNetwork.country,
  &quot;&quot;) AS country,<br>  IFNULL(totals.pageviews, 0) AS pageviews<br>FROM<br>
  \ bigquery-public-data.google_analytics_sample.ga_sessions_*<br>WHERE<br>  _TABLE_SUFFIX
  BETWEEN &#39;20160801&#39; AND &#39;20170631&#39;<br>LIMIT 100000;</pre><p><strong>Explanations:</strong></p><ul><li><em>bqml_lab</em>
  is the dataset, sample_model is the model name.</li><li>We’re using binary logistic
  regression (model_type=’<em>logistic_reg</em>’).</li><li>label is what we aim to
  predict (purchases).</li><li>Features include device OS, mobile status, country,
  and pageviews.</li></ul><p>3. <strong>Evaluate your model:</strong></p><ul><li>Replace
  the previous query with the following and click Run:</li></ul><pre>SELECT<br>  *<br>FROM<br>
  \ ML.EVALUATE(MODEL `bqml_lab.sample_model`, (<br>SELECT<br>  IF(totals.transactions
  IS NULL, 0, 1) AS label,<br>  IFNULL(device.operatingSystem, &quot;&quot;) AS os,<br>
  \ device.isMobile AS is_mobile,<br>  IFNULL(geoNetwork.country, &quot;&quot;) AS
  country,<br>  IFNULL(totals.pageviews, 0) AS pageviews<br>FROM<br>  `bigquery-public-data.google_analytics_sample.ga_sessions_*`<br>WHERE<br>
  \ _TABLE_SUFFIX BETWEEN &#39;20170701&#39; AND &#39;20170801&#39;));</pre><ul><li>When
  the query is complete, click the Results tab below the query text area. You should
  see a table similar to this:</li></ul><figure><img alt=\"\" src=\"https://cdn-images-1.medium.com/max/1024/0*aeXd0JLNNxsqOQCn\"
  /></figure><p><strong>Explanations:</strong></p><ul><li>Want to know how well your
  model performs? Check out these key terms: <strong>precision, recall, accuracy,
  f1_score, log_loss, roc_auc:</strong> You can consult the <a href=\"https://developers.google.com/machine-learning/glossary/\">machine
  learning glossary</a> for definitions.</li></ul><p><strong>4. Use your model to
  predict outcomes</strong></p><ul><li>With this query you will try to predict the
  number of transactions made by visitors of each country, sort the results, and select
  the top 10 countries by purchases:</li></ul><pre>SELECT<br>  country,<br>  SUM(predicted_label)
  as total_predicted_purchases<br>FROM<br>  ML.PREDICT(MODEL `bqml_lab.sample_model`,
  (<br>SELECT<br>  IFNULL(device.operatingSystem, &quot;&quot;) AS os,<br>  device.isMobile
  AS is_mobile,<br>  IFNULL(totals.pageviews, 0) AS pageviews,<br>  IFNULL(geoNetwork.country,
  &quot;&quot;) AS country<br>FROM<br>  `bigquery-public-data.google_analytics_sample.ga_sessions_*`<br>WHERE<br>
  \ _TABLE_SUFFIX BETWEEN &#39;20170701&#39; AND &#39;20170801&#39;))<br>GROUP BY
  country<br>ORDER BY total_predicted_purchases DESC<br>LIMIT 10;</pre><ul><li>When
  the query is complete, click the Results tab below the query text area. The results
  should look like the following:</li></ul><figure><img alt=\"\" src=\"https://cdn-images-1.medium.com/max/1024/0*390uRFMb4ttUw_5I\"
  /></figure><p>\U0001F38ACongratulations! You used BigQuery ML to create a binary
  logistic regression model, evaluate the model, and use the model to make predictions.</p><h3>Outro</h3><h3>Recap
  key takeaways:</h3><ul><li>BigQuery ML allows you to build machine learning models
  using SQL.</li><li>No specialized machine learning expertise is required.</li><li>BQML
  models are easily integrated into your existing BigQuery workflows.</li></ul><h3>Call
  to Action/Next Steps</h3><ul><li>Explore other BQML model types: Experiment with
  classification, time series forecasting, and more.</li><li>Dive deeper into model
  evaluation and optimization techniques.</li></ul><p><strong>\U0001F44B </strong>By
  the way, if you happen to be a startup owner who is actively seeking to propel your
  business to new heights with Cloud:</p><p>We invite you to join our exclusive virtual
  live workshops (links below), where you’ll gain hands-on guidance from Google Cloud
  experts and discover how to seamlessly integrate GCP into your operations. Don’t
  miss this limited-time opportunity to empower your startup with the knowledge and
  expertise needed to thrive in the cloud-driven world. <a href=\"https://rsvp.withgoogle.com/events/let-s-learn-about-google-cloud\">Register
  now</a> and secure your spot!</p><p>⚒️<a href=\"https://rsvp.withgoogle.com/events/onboarding-workshop\">Startup
  Onboarding Workshop</a></p><p>⛑️<a href=\"https://rsvp.withgoogle.com/events/gen-ai-workshop\">Generative
  AI Workshop</a></p><p>\U0001F511<a href=\"https://rsvp.withgoogle.com/events/data-analytics\">Data
  Analytics Workshop</a></p><p>\U0001F510<a href=\"https://rsvp.withgoogle.com/events/security-workshop\">Security
  Workshop</a></p><p>\U0001F4E0<a href=\"https://rsvp.withgoogle.com/events/app-mod-workshops\">Modern
  Applications Workshop</a></p><img src=\"https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=0ae9ba293544\"
  width=\"1\" height=\"1\" alt=\"\"><hr><p><a href=\"https://medium.com/google-cloud/unlock-the-power-of-machine-learning-without-coding-a-beginners-guide-to-bigquery-ml-0ae9ba293544\">Unlock
  the Power of Machine Learning Without Coding: A Beginner’s Guide to BigQuery ML</a>
  was originally published in <a href=\"https://medium.com/google-cloud\">Google Cloud
  - Community</a> on Medium, where people are continuing the conversation by highlighting
  and responding to this story.</p>"

Language

Active

Ricc internal notes

Ricc source

Show this article Back to articles