♊️ GemiNews 🗞️ (dev)

Demo 1: Embeddings + Recommendation Demo 2: Bella RAGa Demo 3: NewRetriever Demo 4: Assistant function calling

🗞️Unlock the Power of Machine Learning Without Coding: A Beginner’s Guide to BigQuery ML

🏷️ bigquery 🏷️ data 🏷️ bigquery-ml 🏷️ google-cloud-platform 🏷️ machine-learning

🗿Semantically Similar Articles (by :title_embedding)

Unlock the Power of Machine Learning Without Coding: A Beginner’s Guide to BigQuery ML

2024-04-03 - Brian Ling (from Google Cloud - Medium)

Demystifying machine learning for data analysts — build predictive models directly within your data warehouseA vibrant illustration of a data warehouse with colorful machine learning algorithms swirling around it, representing the ease of integrationAs 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?IntroductionBigQuery 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.PurposeThis 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.Use CasesPredicting customer churn: Identify customers at risk of leaving.Fraud detection: Uncover unusual patterns in financial transactions.Demand forecasting: Predict future sales to optimize inventory.Sentiment analysis: Understand customer feedback trends.Skill PrerequisitesBasic understanding of SQL.Familiarity with BigQuery and Google Cloud Platform (GCP).DisclaimerBigQuery 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.Step-by-Step WalkthroughPrerequisitesA Google Cloud Platform project with billing enabled.BigQuery access and the necessary IAM permissions.A dataset in BigQuery to train your model.Architecture DiagramCreating a Machine Learning Model in BigQuery MLCreate your datasetTo create a dataset, click on the View actions icon next to your project ID and select Create dataset.Name your Dataset ID bqml_lab and click Create dataset.2. Create a modelGo to BigQuery EDITOR, paste the following query to create a model that predicts purchase likelihood:CREATE OR REPLACE MODEL bqml_lab.sample_modelOPTIONS(model_type='logistic_reg') ASSELECT IF(totals.transactions IS NULL, 0, 1) AS label, IFNULL(device.operatingSystem, "") AS os, device.isMobile AS is_mobile, IFNULL(geoNetwork.country, "") AS country, IFNULL(totals.pageviews, 0) AS pageviewsFROM bigquery-public-data.google_analytics_sample.ga_sessions_*WHERE _TABLE_SUFFIX BETWEEN '20160801' AND '20170631'LIMIT 100000;Explanations:bqml_lab is the dataset, sample_model is the model name.We’re using binary logistic regression (model_type=’logistic_reg’).label is what we aim to predict (purchases).Features include device OS, mobile status, country, and pageviews.3. Evaluate your model:Replace the previous query with the following and click Run:SELECT *FROM ML.EVALUATE(MODEL `bqml_lab.sample_model`, (SELECT IF(totals.transactions IS NULL, 0, 1) AS label, IFNULL(device.operatingSystem, "") AS os, device.isMobile AS is_mobile, IFNULL(geoNetwork.country, "") AS country, IFNULL(totals.pageviews, 0) AS pageviewsFROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'));When the query is complete, click the Results tab below the query text area. You should see a table similar to this:Explanations:Want to know how well your model performs? Check out these key terms: precision, recall, accuracy, f1_score, log_loss, roc_auc: You can consult the machine learning glossary for definitions.4. Use your model to predict outcomesWith 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:SELECT country, SUM(predicted_label) as total_predicted_purchasesFROM ML.PREDICT(MODEL `bqml_lab.sample_model`, (SELECT IFNULL(device.operatingSystem, "") AS os, device.isMobile AS is_mobile, IFNULL(totals.pageviews, 0) AS pageviews, IFNULL(geoNetwork.country, "") AS countryFROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'))GROUP BY countryORDER BY total_predicted_purchases DESCLIMIT 10;When the query is complete, click the Results tab below the query text area. The results should look like the following:🎊Congratulations! You used BigQuery ML to create a binary logistic regression model, evaluate the model, and use the model to make predictions.OutroRecap key takeaways:BigQuery ML allows you to build machine learning models using SQL.No specialized machine learning expertise is required.BQML models are easily integrated into your existing BigQuery workflows.Call to Action/Next StepsExplore other BQML model types: Experiment with classification, time series forecasting, and more.Dive deeper into model evaluation and optimization techniques.👋 By the way, if you happen to be a startup owner who is actively seeking to propel your business to new heights with Cloud: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. Register now and secure your spot!⚒️Startup Onboarding Workshop⛑️Generative AI Workshop🔑Data Analytics Workshop🔐Security Workshop📠Modern Applications WorkshopUnlock the Power of Machine Learning Without Coding: A Beginner’s Guide to BigQuery ML was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

🏷️ bigquery 🏷️ data 🏷️ bigquery-ml 🏷️ google-cloud-platform 🏷️ machine-learning
[Blogs] 🌎 https://medium.com/google-cloud/unlock-the-power-of-machine-learning-without-coding-a-beginners-guide-to-bigquery-ml-0ae9ba293544?source=rss----e52cf94d98af---4 [🧠] [v2] article_embedding_description: {:llm_project_id=>"Unavailable", :llm_dimensions=>nil, :article_size=>8843, :llm_embeddings_model_name=>"textembedding-gecko"}
[🧠] [v1/3] title_embedding_description: {:ricc_notes=>"[embed-v3] Fixed on 9oct24. Only seems incompatible at first glance with embed v1.", :llm_project_id=>"unavailable possibly not using Vertex", :llm_dimensions=>nil, :article_size=>8843, :poly_field=>"title", :llm_embeddings_model_name=>"textembedding-gecko"}
[🧠] [v1/3] summary_embedding_description:
[🧠] As per bug https://github.com/palladius/gemini-news-crawler/issues/4 we can state this article belongs to titile/summary version: v3 (very few articles updated on 9oct24)

🗿article.to_s 

------------------------------
Title: Unlock the Power of Machine Learning Without Coding: A Beginner’s Guide to BigQuery ML
[content]
Demystifying machine learning for data analysts — build predictive models directly within your data warehouseA vibrant illustration of a data warehouse with colorful machine learning algorithms swirling around it, representing the ease of integrationAs 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?IntroductionBigQuery 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.PurposeThis 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.Use CasesPredicting customer churn: Identify customers at risk of leaving.Fraud detection: Uncover unusual patterns in financial transactions.Demand forecasting: Predict future sales to optimize inventory.Sentiment analysis: Understand customer feedback trends.Skill PrerequisitesBasic understanding of SQL.Familiarity with BigQuery and Google Cloud Platform (GCP).DisclaimerBigQuery 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.Step-by-Step WalkthroughPrerequisitesA Google Cloud Platform project with billing enabled.BigQuery access and the necessary IAM permissions.A dataset in BigQuery to train your model.Architecture DiagramCreating a Machine Learning Model in BigQuery MLCreate your datasetTo create a dataset, click on the View actions icon next to your project ID and select Create dataset.Name your Dataset ID bqml_lab and click Create dataset.2. Create a modelGo to BigQuery EDITOR, paste the following query to create a model that predicts purchase likelihood:CREATE OR REPLACE MODEL bqml_lab.sample_modelOPTIONS(model_type='logistic_reg') ASSELECT  IF(totals.transactions IS NULL, 0, 1) AS label,  IFNULL(device.operatingSystem, "") AS os,  device.isMobile AS is_mobile,  IFNULL(geoNetwork.country, "") AS country,  IFNULL(totals.pageviews, 0) AS pageviewsFROM  bigquery-public-data.google_analytics_sample.ga_sessions_*WHERE  _TABLE_SUFFIX BETWEEN '20160801' AND '20170631'LIMIT 100000;Explanations:bqml_lab is the dataset, sample_model is the model name.We’re using binary logistic regression (model_type=’logistic_reg’).label is what we aim to predict (purchases).Features include device OS, mobile status, country, and pageviews.3. Evaluate your model:Replace the previous query with the following and click Run:SELECT  *FROM  ML.EVALUATE(MODEL `bqml_lab.sample_model`, (SELECT  IF(totals.transactions IS NULL, 0, 1) AS label,  IFNULL(device.operatingSystem, "") AS os,  device.isMobile AS is_mobile,  IFNULL(geoNetwork.country, "") AS country,  IFNULL(totals.pageviews, 0) AS pageviewsFROM  `bigquery-public-data.google_analytics_sample.ga_sessions_*`WHERE  _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'));When the query is complete, click the Results tab below the query text area. You should see a table similar to this:Explanations:Want to know how well your model performs? Check out these key terms: precision, recall, accuracy, f1_score, log_loss, roc_auc: You can consult the machine learning glossary for definitions.4. Use your model to predict outcomesWith 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:SELECT  country,  SUM(predicted_label) as total_predicted_purchasesFROM  ML.PREDICT(MODEL `bqml_lab.sample_model`, (SELECT  IFNULL(device.operatingSystem, "") AS os,  device.isMobile AS is_mobile,  IFNULL(totals.pageviews, 0) AS pageviews,  IFNULL(geoNetwork.country, "") AS countryFROM  `bigquery-public-data.google_analytics_sample.ga_sessions_*`WHERE  _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'))GROUP BY countryORDER BY total_predicted_purchases DESCLIMIT 10;When the query is complete, click the Results tab below the query text area. The results should look like the following:🎊Congratulations! You used BigQuery ML to create a binary logistic regression model, evaluate the model, and use the model to make predictions.OutroRecap key takeaways:BigQuery ML allows you to build machine learning models using SQL.No specialized machine learning expertise is required.BQML models are easily integrated into your existing BigQuery workflows.Call to Action/Next StepsExplore other BQML model types: Experiment with classification, time series forecasting, and more.Dive deeper into model evaluation and optimization techniques.👋 By the way, if you happen to be a startup owner who is actively seeking to propel your business to new heights with Cloud: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. Register now and secure your spot!⚒️Startup Onboarding Workshop⛑️Generative AI Workshop🔑Data Analytics Workshop🔐Security Workshop📠Modern Applications WorkshopUnlock the Power of Machine Learning Without Coding: A Beginner’s Guide to BigQuery ML was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.
[/content]

Author: Brian Ling
PublishedDate: 2024-04-03
Category: Blogs
NewsPaper: Google Cloud - Medium
Tags: bigquery, data, bigquery-ml, google-cloud-platform, machine-learning
{"id"=>5263,
"title"=>"Unlock the Power of Machine Learning Without Coding: A Beginner’s Guide to BigQuery ML",
"summary"=>nil,
"content"=>"

Demystifying machine learning for data analysts — build predictive models directly within your data warehouse

\"\"
A vibrant illustration of a data warehouse with colorful machine learning algorithms swirling around it, representing the ease of integration

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?

Introduction

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.

Purpose

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.

Use Cases

  • Predicting customer churn: Identify customers at risk of leaving.
  • Fraud detection: Uncover unusual patterns in financial transactions.
  • Demand forecasting: Predict future sales to optimize inventory.
  • Sentiment analysis: Understand customer feedback trends.

Skill Prerequisites

  • Basic understanding of SQL.
  • Familiarity with BigQuery and Google Cloud Platform (GCP).

Disclaimer

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.

Step-by-Step Walkthrough

Prerequisites

  • A Google Cloud Platform project with billing enabled.
  • BigQuery access and the necessary IAM permissions.
  • A dataset in BigQuery to train your model.

Architecture Diagram

\"\"

Creating a Machine Learning Model in BigQuery ML

  1. Create your dataset
  • To create a dataset, click on the View actions icon next to your project ID and select Create dataset.
  • Name your Dataset ID bqml_lab and click Create dataset.

2. Create a model

  • Go to BigQuery EDITOR, paste the following query to create a model that predicts purchase likelihood:
\"\"
CREATE OR REPLACE MODEL bqml_lab.sample_model
OPTIONS(model_type='logistic_reg') AS
SELECT
  IF(totals.transactions IS NULL, 0, 1) AS label,
  IFNULL(device.operatingSystem, "") AS os,
  device.isMobile AS is_mobile,
  IFNULL(geoNetwork.country, "") AS country,
  IFNULL(totals.pageviews, 0) AS pageviews
FROM
  bigquery-public-data.google_analytics_sample.ga_sessions_*
WHERE
  _TABLE_SUFFIX BETWEEN '20160801' AND '20170631'
LIMIT 100000;

Explanations:

  • bqml_lab is the dataset, sample_model is the model name.
  • We’re using binary logistic regression (model_type=’logistic_reg’).
  • label is what we aim to predict (purchases).
  • Features include device OS, mobile status, country, and pageviews.

3. Evaluate your model:

  • Replace the previous query with the following and click Run:
SELECT
  *
FROM
  ML.EVALUATE(MODEL `bqml_lab.sample_model`, (
SELECT
  IF(totals.transactions IS NULL, 0, 1) AS label,
  IFNULL(device.operatingSystem, "") AS os,
  device.isMobile AS is_mobile,
  IFNULL(geoNetwork.country, "") AS country,
  IFNULL(totals.pageviews, 0) AS pageviews
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'));
  • When the query is complete, click the Results tab below the query text area. You should see a table similar to this:
\"\"

Explanations:

  • Want to know how well your model performs? Check out these key terms: precision, recall, accuracy, f1_score, log_loss, roc_auc: You can consult the machine learning glossary for definitions.

4. Use your model to predict outcomes

  • 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:
SELECT
  country,
  SUM(predicted_label) as total_predicted_purchases
FROM
  ML.PREDICT(MODEL `bqml_lab.sample_model`, (
SELECT
  IFNULL(device.operatingSystem, "") AS os,
  device.isMobile AS is_mobile,
  IFNULL(totals.pageviews, 0) AS pageviews,
  IFNULL(geoNetwork.country, "") AS country
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'))
GROUP BY country
ORDER BY total_predicted_purchases DESC
LIMIT 10;
  • When the query is complete, click the Results tab below the query text area. The results should look like the following:
\"\"

🎊Congratulations! You used BigQuery ML to create a binary logistic regression model, evaluate the model, and use the model to make predictions.

Outro

Recap key takeaways:

  • BigQuery ML allows you to build machine learning models using SQL.
  • No specialized machine learning expertise is required.
  • BQML models are easily integrated into your existing BigQuery workflows.

Call to Action/Next Steps

  • Explore other BQML model types: Experiment with classification, time series forecasting, and more.
  • Dive deeper into model evaluation and optimization techniques.

👋 By the way, if you happen to be a startup owner who is actively seeking to propel your business to new heights with Cloud:

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. Register now and secure your spot!

⚒️Startup Onboarding Workshop

⛑️Generative AI Workshop

🔑Data Analytics Workshop

🔐Security Workshop

📠Modern Applications Workshop

\"\"

Unlock the Power of Machine Learning Without Coding: A Beginner’s Guide to BigQuery ML was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

",
"author"=>"Brian Ling",
"link"=>"https://medium.com/google-cloud/unlock-the-power-of-machine-learning-without-coding-a-beginners-guide-to-bigquery-ml-0ae9ba293544?source=rss----e52cf94d98af---4",
"published_date"=>Wed, 03 Apr 2024 04:46:56.000000000 UTC +00:00,
"image_url"=>nil,
"feed_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",
"language"=>nil,
"active"=>true,
"ricc_source"=>"feedjira::v1",
"created_at"=>Wed, 03 Apr 2024 11:09:08.084370000 UTC +00:00,
"updated_at"=>Mon, 21 Oct 2024 18:28:43.119204000 UTC +00:00,
"newspaper"=>"Google Cloud - Medium",
"macro_region"=>"Blogs"}
Edit this article
Back to articles