Proprietary + Confidential

​

​

​

​

AI/ML in BigQuery��Xi Cheng

​

Feb 9, 2024�

​

​

Proprietary + Confidential

Confidential + Proprietary

AI/ML in BigQuery

​

​

​

​

• Structured & unstructured data
• Data to AI
• SQL and Python

​

SQL:��

CREATE MODEL()

ML.PREDICT()

ML.GENERATE_TEXT()�ML.GENERATE_EMBEDDING()

ML.MIN_MAX_SCALER()�…�CREATE VECTOR INDEX()�VECTOR_SEARCH()

…

​

​

Python:

�model.fit()�model.predict()

PaLM2TextGenerator()

PaLM2TextEmbedding�Generator()

MaxAbsScaler.fit()

…�similarity_search()�similarity_search_by_vector()�…

​

​

2

AI/ML in BigQuery

Rich Built-in Models

Supervised Learning: Linear /

Logistic regression, Boosted trees, Random Forest, DNN, Wide & Deep models, AutoML Tables

Unsupervised Learning: K-Means, PCA, Matrix Factorization, Autoencoder

Inference

​

AI/ML Applications

Forecasting

MLOps

Model versioning

​

NDA

Time Series: ARIMA_PLUS (univariate) and ARIMA_PLUS_XREG (multivariate)

Hyperparameter Tuning

Pipelines

Imported Model Inference: with TF, TFLite, XGBoost & ONNX (enabling sklearn, Pytorch, SparkML)

Remote Model Inference: with Vertex Endpoint

Feature store

Anomaly Detection

Recommendations

Generative AI

Explainable AI

Natural Language Processing

Computer Vision

Embeddings and Vector Search

Speech to Text

Export Modes: for online inference in Vertex

Feature Engineering & others

Model evaluation

Rich feature engineering support

Model management

Model Monitoring

Document AI

Proprietary + Confidential

AI/ML Pipelines: Capabilities can be combined

ml.understand_text

ml.translate

ml.transcribe

ml.process_document

ml.generate_embedding

ml.annotate_image

ml.generate_text

ml.{predict, recommend, forecast, detect_anomalies}

vector_search

Example Use Case: Provide Insights & Generate Content on Unstructured and Structured Data

Step 1

Step 2

Step 4

Step 5

Step 3

Company Z has audio data in GCS and structured trading data in BigQuery. Portfolio managers need to be able to ask questions of this data and generate emails to the user based on the answers to those questions.

Structured data

Access to audio data by creating an Object Table

CREATE EXTERNAL TABLE

audio.speeches

WITH CONNECTION `us.property-audios` OPTIONS(object_metadata="DIRECTORY",

uris=["gs://demo/speech/*"]);

​

​

Step 4: Embedding Search

Step 5: Generate text using LLM

Transcribe audio files into text by using Speech Chirp model

CREATE OR REPLACE MODEL `bqml.my_speech_model`

OPTIONS (remote_service_type='CLOUD_AI_SPEECH_TO_TEXT_V2'

[speech_recognizer='projects/PROJECT_NUMBER/locations/LOCATION/recognizer/RECOGNIZER_ID'])

​

​

SELECT * FROM

ML.TRANSCRIBE (

MODEL`bqml.my_speech_model`,

TABLE PROJECT_ID.DATASET_ID.OBJECT_TABLE_NAME))

​

​

Step 4: Embedding Search

Step 5: Generate text using LLM

Generate text embeddings using PaLM2 for embedding

CREATE OR REPLACE MODEL `bqml.my_embedding_model`

REMOTE WITH CONNECTION `my_company.my_connection`

OPTIONS(

ENDPOINT = 'textembedding-gecko@latest');

SELECT * FROM

ML.GENERATE_EMBEDDING (

MODEL`bqml.my_embedding_model`,

(SELECT * FROM `bqml.trancribed_data`))

Step 4: Embedding Search

Step 5: Generate text using LLM

Vector Search

Step 3: Generate text embedding

Step 5: Write email using LLM

CREATE VECTOR INDEX my_index ON my_table(embedding_column) �OPTIONS (INDEX_TYPE='IVF|SCAM', DISTANCE_TYPE='EUCLIDEAN|COSINE')

SELECT * FROM

VECTOR_SEARCH(

Table my_table, 'embedding_column',

(SELECT * FROM ML.GENERATE_EMBEDDING(

Model `bqml.my_embedding_model`,

(SELECT 'Tell me something about the brooklyn bridge' AS content))),

top_k => 10)

Generate text using PaLM 2 for text

CREATE OR REPLACE MODEL `bqml.my_gemini_model`

REMOTE WITH CONNECTION `my_company.my_connection`

OPTIONS(

ENDPOINT = 'text-bison@latest');

SELECT * FROM

ML.GENERATE_TEXT (

MODEL`bqml.my_gemini_model`, (

SELECT

CONCAT("Write an email to “, name, “ about ”, search_result) AS prompt

FROM `bqml.joined_search_result_and_user_info`))

Step 5: Embedding Search

Step 4: Generate text embedding

Thank you!

Proprietary + Confidential

​

​

AI DA Strategy update

​

BigQuery ML �support for Generative AI��Jan 24, 2024�

​

​

Proprietary + Confidential

Confidential + Proprietary