notebooks/bqml-generate-embedding-with-multimodalembedding-model.ipynb (270 lines of code) (raw):
{
"cells": [
{
"cell_type": "markdown",
"metadata": {
"id": "wsWKk9LTf-i3"
},
"source": [
"# BigQuery ML (BQML) Multimodal Embedding\n",
"\n",
"This tutorial accompanies the blog post [Introducing multimodal and structured data embedding support in BigQuery](https://cloud.google.com/blog/products/data-analytics/bigquery-multimodal-embeddings-generation) introduces how to generate multimodal embeddings includes text and image embedding. For more information, please check the [BQML embedding overview](https://cloud.google.com/bigquery/docs/ai-application-overview#text_embedding) and [ML.GENERATE_EMBEDDING documentation](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-generate-embedding) to know how to generate and use other embeddings in Big Query."
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "qnyAEkTYgBSi"
},
"source": [
"---\n",
"## Find \u0026 Replace Project ID, Connection ID and DataSet ID\n",
"\n",
"This workflow uses IPython Magics for readability and for making SQL queries easy to copy/paste to other editors, like the BigQuery console. It is recommended to first do a find \u0026 replace of the project id, connection id and dataset id used in this notebook with the one in your environment. \n",
"- Replace all occurences of `bigquerytestdefault` with the value of your project id.\n",
"- Replace all occurences of `github_connection` with the value of your remote connection id.\n",
"- Replace all occurences of `bqml_tutorial` with the value of your dataset id."
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "ZMPbU78egEPd"
},
"source": [
"---\n",
"## Colab Setup\n",
"\n",
"To run this notebook in Colab click [](https://colab.research.google.com/github/GoogleCloudPlatform/bigquery-ml-utils/notebooks/bqml-generate-embedding-with-multimodalembedding-model.ipynb) and run the cells in this section. Otherwise, skip this section.\n",
"\n",
"This cell will authenticate to GCP (follow prompts in the popup)."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "Sizt41bvgLkq"
},
"outputs": [],
"source": [
"#@title Setup credentials\n",
"\n",
"from google.colab import auth\n",
"auth.authenticate_user()\n",
"print('Authenticated')\n",
"\n",
"PROJECT_ID='bigquerytestdefault'\n",
"from google.cloud import bigquery\n",
"client = bigquery.Client(PROJECT_ID)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "qHkp6gPEgNCR"
},
"outputs": [],
"source": [
"#@title Enable data table display\n",
"\n",
"%load_ext google.colab.data_table"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "_Jn8NnjCgPii"
},
"outputs": [],
"source": [
"#@title Function that displays images from GCS bucket.\n",
"import io\n",
"from PIL import Image\n",
"import matplotlib.pyplot as plt\n",
"import tensorflow as tf\n",
"\n",
"def printImages(results):\n",
" image_results_list = list(results)\n",
" amt_of_images = len(image_results_list)\n",
"\n",
" fig, axes = plt.subplots(nrows=amt_of_images, ncols=2, figsize=(20, 20))\n",
" fig.tight_layout()\n",
" fig.subplots_adjust(hspace=0.5)\n",
" for i in range(amt_of_images):\n",
" gcs_uri = image_results_list[i][0]\n",
" text = image_results_list[i][1]\n",
" f = tf.io.gfile.GFile(gcs_uri, 'rb')\n",
" stream = io.BytesIO(f.read())\n",
" img = Image.open(stream)\n",
" axes[i, 0].axis('off')\n",
" axes[i, 0].imshow(img)\n",
" axes[i, 1].axis('off')\n",
" axes[i, 1].text(0, 0, text, fontsize=10)\n",
" plt.show()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "xUZpO2dfgRl9"
},
"outputs": [],
"source": [
"#@title Create Image Object table from public dataset.\n",
"%%bigquery --project bigquerytestdefault\n",
"\n",
"CREATE OR REPLACE EXTERNAL TABLE\n",
"`bqml_tutorial.image_object_table`\n",
"WITH CONNECTION `bigquerytestdefault.us.github_connection`\n",
"OPTIONS(\n",
" object_metadata = 'SIMPLE',\n",
" uris = ['gs://gcs-public-data--met/*']);"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "3jGSCfHZgVR1"
},
"outputs": [],
"source": [
"#@title Display first 10 images in the image object table\n",
"\n",
"display_first_10_images_query = \"\"\"\n",
" SELECT *\n",
" FROM bqml_tutorial.image_object_table\n",
" WHERE content_type = 'image/jpeg'\n",
" LIMIT 10;\n",
"\"\"\"\n",
"printImages(client.query(display_first_10_images_query))"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "Wd2eNJTkgW4z"
},
"outputs": [],
"source": [
"#@title Create a multimodalembedding model\n",
"%%bigquery --project bigquerytestdefault\n",
"\n",
"CREATE OR REPLACE MODEL `bqml_tutorial.multimodalembedding_model`\n",
"REMOTE WITH CONNECTION `bigquerytestdefault.us.github_connection`\n",
"OPTIONS (endpoint=\"multimodalembedding@001\");"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "PWDL52fXgY7t"
},
"outputs": [],
"source": [
"#@title Generate image embeddings from the image object table.\n",
"%%bigquery --project bigquerytestdefault\n",
"\n",
"CREATE OR REPLACE TABLE `bqml_tutorial.image_embed_table`\n",
"AS\n",
"SELECT *\n",
"FROM\n",
" ML.GENERATE_EMBEDDING(\n",
" MODEL `bqml_tutorial.multimodalembedding_model`,\n",
" (\n",
" SELECT * FROM `bqml_tutorial.image_object_table` WHERE content_type = 'image/jpeg' LIMIT 10000\n",
" ));"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "uGEV-vw3gaKd"
},
"outputs": [],
"source": [
"#@title Show image embedding results.\n",
"%%bigquery --project bigquerytestdefault\n",
"\n",
"SELECT * FROM `bqml_tutorial.image_embed_table` limit 10;"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "aY9yScY_gblq"
},
"outputs": [],
"source": [
"#@title Input text prompt: \"give me a best match picture of a dress\". Vector search find best match.\n",
"%%bigquery --project bigquerytestdefault\n",
"\n",
"CREATE OR REPLACE TABLE `bqml_tutorial.vector_search_table` AS\n",
"SELECT base.uri AS gcs_uri, distance\n",
"FROM\n",
" VECTOR_SEARCH(\n",
" TABLE `bqml_tutorial.image_embed_table`,\n",
" \"ml_generate_embedding_result\",\n",
" (\n",
" SELECT * FROM ML.GENERATE_EMBEDDING(\n",
" MODEL `bqml_tutorial.multimodalembedding_model`,\n",
" (\n",
" SELECT \"give me a best match picture of a wedding dress\" AS content\n",
" )\n",
" )\n",
" ),\n",
" \"ml_generate_embedding_result\",\n",
" top_k =\u003e 5);"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "0HuSt8drgcxh"
},
"outputs": [],
"source": [
"#@title Find the best match image.\n",
"best_match_query = \"\"\"\n",
" SELECT * FROM `bqml_tutorial.vector_search_table`\n",
" ORDER BY distance limit 5;\n",
"\"\"\"\n",
"\n",
"printImages(client.query(best_match_query))"
]
}
],
"metadata": {
"colab": {
"private_outputs": true,
"provenance": [
{
"file_id": "/piper/depot/google3/third_party/py/bigquery_ml_utils/notebooks/bqml-generate-embedding-with-multimodalembedding-model.ipynb",
"timestamp": 1717102474229
},
{
"file_id": "1QILN4RbjreJ7FCPpqxHgYC7f-5Vvik7Q",
"timestamp": 1712962748047
}
]
},
"kernelspec": {
"display_name": "Python 3",
"name": "python3"
},
"language_info": {
"name": "python"
}
},
"nbformat": 4,
"nbformat_minor": 0
}