Menganalisis data multimodal dengan UDF SQL dan Python
Tutorial ini menunjukkan cara menganalisis data multimodal menggunakan kueri SQL dan fungsi yang ditentukan pengguna (UDF) Python.
Tutorial ini menggunakan katalog produk dari set data toko hewan Cymbal publik.
Tujuan
- Gunakan nilai
ObjectRefuntuk menyimpan data gambar bersama data terstruktur dalam tabel standar BigQuery. - Buat teks berdasarkan data gambar dari tabel standar menggunakan
fungsi
AI.GENERATE_TABLE. - Ubah gambar yang ada untuk membuat gambar baru menggunakan UDF Python.
- Kelompokkan PDF untuk analisis lebih lanjut menggunakan UDF Python.
- Gunakan model Gemini dan fungsi
ML.GENERATE_TEXTuntuk menganalisis data PDF yang dipecah-pecah. - Buat embedding berdasarkan data gambar dari tabel standar menggunakan
fungsi
ML.GENERATE_EMBEDDING. - Memproses data multimodal yang diurutkan menggunakan array nilai
ObjectRef.
Biaya
Dalam dokumen ini, Anda akan menggunakan komponen Google Cloudyang dapat ditagih berikut:
- BigQuery: you incur costs for the data that you process in BigQuery.
- BigQuery Python UDFs: you incur costs for using Python UDFs.
- Cloud Storage: you incur costs for the objects stored in Cloud Storage.
- Vertex AI: you incur costs for calls to Vertex AI models.
Untuk membuat perkiraan biaya berdasarkan proyeksi penggunaan Anda,
gunakan kalkulator harga.
Untuk mengetahui informasi selengkapnya, lihat halaman harga berikut:
Sebelum memulai
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Verify that billing is enabled for your Google Cloud project.
-
Enable the BigQuery, BigQuery Connection, Cloud Storage, and Vertex AI APIs.
Peran yang diperlukan
Untuk mendapatkan izin yang Anda perlukan untuk menyelesaikan tutorial ini, minta administrator Anda untuk memberi Anda peran IAM berikut:
-
Membuat koneksi:
BigQuery Connection Admin (
roles/bigquery.connectionAdmin) -
Memberikan izin ke akun layanan koneksi:
Project IAM Admin (
roles/resourcemanager.projectIamAdmin) -
Buat bucket Cloud Storage:
Storage Admin (
roles/storage.admin) -
Membuat set data, model, UDF, dan tabel, serta menjalankan tugas BigQuery:
Admin BigQuery (
roles/bigquery.admin) -
Buat URL yang memungkinkan Anda membaca dan mengubah objek Cloud Storage:
BigQuery ObjectRef Admin (
roles/bigquery.objectRefAdmin)
Untuk mengetahui informasi selengkapnya tentang cara memberikan peran, lihat Mengelola akses ke project, folder, dan organisasi.
Anda mungkin juga bisa mendapatkan izin yang diperlukan melalui peran khusus atau peran bawaan lainnya.
Siapkan
Di bagian ini, Anda akan membuat set data, koneksi, tabel, dan model yang digunakan dalam tutorial ini.
Membuat set data
Buat set data BigQuery untuk memuat objek yang Anda buat dalam tutorial ini:
Di Google Cloud konsol, buka halaman BigQuery.
Di panel Explorer, pilih project Anda.
Luaskan opsi Actions, lalu klik Create dataset. Panel Buat set data akan terbuka.
Untuk Dataset ID, ketik
cymbal_pets.Klik Create dataset.
Membuat bucket
Buat bucket Cloud Storage untuk menyimpan objek yang telah diubah:
Buka halaman Bucket.
Klik Create.
Di halaman Create a bucket, di bagian Get started, masukkan nama yang unik secara global yang memenuhi persyaratan nama bucket.
Klik Buat.
Membuat koneksi
Buat koneksi resource Cloud dan dapatkan akun layanan koneksi. BigQuery menggunakan koneksi untuk mengakses objek di Cloud Storage:
Buka halaman BigQuery.
Di panel Penjelajah, klik Tambahkan data.
Dialog Tambahkan data akan terbuka.
Di panel Filter Menurut, di bagian Jenis Sumber Data, pilih Aplikasi Bisnis.
Atau, di kolom Telusuri sumber data, Anda dapat memasukkan
Vertex AI.Di bagian Sumber data unggulan, klik Vertex AI.
Klik kartu solusi Vertex AI Models: BigQuery Federation.
Dalam daftar Connection type, pilih Vertex AI remote models, remote functions and BigLake (Cloud Resource).
Di kolom Connection ID, ketik
cymbal_conn.Klik Create connection.
Klik Buka koneksi.
Di panel Info koneksi, salin ID akun layanan untuk digunakan pada langkah berikut.
Memberikan izin ke akun layanan koneksi
Beri akun layanan koneksi peran yang sesuai untuk mengakses layanan lain. Anda harus memberikan peran ini di project yang sama dengan yang Anda buat atau pilih di bagian Sebelum memulai. Pemberian
peran dalam project yang berbeda akan menghasilkan error bqcx-1234567890-xxxx@gcp-sa-bigquery-condel.iam.gserviceaccount.com
does not have the permission to access resource.
Memberikan izin pada bucket Cloud Storage
Beri akun layanan akses untuk menggunakan objek di bucket yang Anda buat:
Buka halaman Bucket.
Klik nama bucket yang Anda buat.
Klik Izin.
Klik Berikan akses. Dialog Berikan akses akan terbuka.
Di kolom New principals, masukkan ID akun layanan yang Anda salin sebelumnya.
Di kolom Select a role, pilih Cloud Storage, lalu pilih Storage Object User.
Klik Simpan.
Memberikan izin untuk menggunakan model Vertex AI
Beri akun layanan akses untuk menggunakan model Vertex AI:
Buka halaman IAM & Admin.
Klik Berikan akses. Dialog Berikan akses akan terbuka.
Di kolom New principals, masukkan ID akun layanan yang Anda salin sebelumnya.
Di kolom Pilih peran, pilih Vertex AI, lalu pilih Pengguna Vertex AI.
Klik Simpan.
Buat tabel data contoh
Buat tabel untuk menyimpan informasi produk hewan peliharaan Cymbal.
Buat tabel products
Buat tabel standar yang berisi informasi produk hewan peliharaan Cymbal:
Di Google Cloud konsol, buka halaman BigQuery.
Di editor kueri, jalankan kueri berikut untuk membuat tabel
products:LOAD DATA OVERWRITE cymbal_pets.products FROM FILES( format = 'avro', uris = [ 'gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/tables/products/products_*.avro']);
Buat tabel product_images
Buat tabel objek yang berisi gambar produk hewan peliharaan Cymbal:
Di editor kueri halaman BigQuery, jalankan kueri berikut untuk membuat tabel
product_images:CREATE OR REPLACE EXTERNAL TABLE cymbal_pets.product_images WITH CONNECTION `us.cymbal_conn` OPTIONS ( object_metadata = 'SIMPLE', uris = ['gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/images/*.png'], max_staleness = INTERVAL 30 MINUTE, metadata_cache_mode = AUTOMATIC);
Buat tabel product_manuals
Buat tabel objek yang berisi manual produk hewan peliharaan Cymbal:
Di editor kueri halaman BigQuery, jalankan kueri berikut untuk membuat tabel
product_manuals:CREATE OR REPLACE EXTERNAL TABLE cymbal_pets.product_manuals WITH CONNECTION `us.cymbal_conn` OPTIONS ( object_metadata = 'SIMPLE', uris = ['gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/documents/*.pdf']);
Membuat model pembuatan teks
Buat model jarak jauh BigQuery ML yang merepresentasikan model Gemini Vertex AI:
Di editor kueri halaman BigQuery, jalankan kueri berikut untuk membuat model jarak jauh:
CREATE OR REPLACE MODEL `cymbal_pets.gemini` REMOTE WITH CONNECTION `us.cymbal_conn` OPTIONS (ENDPOINT = 'gemini-2.0-flash');
Membuat model pembuatan embedding
Buat model jarak jauh BigQuery ML yang merepresentasikan model penyematan multimodal Vertex AI:
Di editor kueri halaman BigQuery, jalankan kueri berikut untuk membuat model jarak jauh:
CREATE OR REPLACE MODEL `cymbal_pets.embedding_model` REMOTE WITH CONNECTION `us.cymbal_conn` OPTIONS (ENDPOINT = 'multimodalembedding@001');
Membuat tabel products_mm dengan data multimodal
Buat tabel products_mm yang berisi kolom image yang diisi dengan
gambar produk dari tabel objek product_images. Kolom image yang dibuat adalah kolom STRUCT yang menggunakan format ObjectRef.
Di editor kueri halaman BigQuery, jalankan kueri berikut untuk membuat tabel
products_mmdan mengisi kolomimage:CREATE OR REPLACE TABLE cymbal_pets.products_mm AS SELECT products.* EXCEPT (uri), ot.ref AS image FROM cymbal_pets.products INNER JOIN cymbal_pets.product_images ot ON ot.uri = products.uri;
Di editor kueri halaman BigQuery, jalankan kueri berikut untuk melihat data kolom
image:SELECT product_name, image FROM cymbal_pets.products_mm`
Hasilnya akan terlihat seperti berikut:
+--------------------------------+--------------------------------------+-----------------------------------------------+------------------------------------------------+ | product_name | image.uri | image.version | image.authorizer | image.details | +--------------------------------+--------------------------------------+-----------------------------------------------+------------------------------------------------+ | AquaClear Aquarium Background | gs://cloud-samples-data/bigquery/ | 1234567891011 | myproject.region.myconnection | {"gcs_metadata":{"content_type":"image/png", | | | tutorials/cymbal-pets/images/ | | | "md5_hash":"494f63b9b137975ff3e7a11b060edb1d", | | | aquaclear-aquarium-background.png | | | "size":1282805,"updated":1742492680017000}} | +--------------------------------+--------------------------------------+-----------------------------------------------+------------------------------------------------+ | AquaClear Aquarium | gs://cloud-samples-data/bigquery/ | 2345678910112 | myproject.region.myconnection | {"gcs_metadata":{"content_type":"image/png", | | Gravel Vacuum | tutorials/cymbal-pets/images/ | | | "md5_hash":"b7bfc2e2641a77a402a1937bcf0003fd", | | | aquaclear-aquarium-gravel-vacuum.png | | | "size":820254,"updated":1742492682411000}} | +--------------------------------+--------------------------------------+-----------------------------------------------+------------------------------------------------+ | ... | ... | ... | | ... | +--------------------------------+--------------------------------------+-----------------------------------------------+------------------------------------------------+
Membuat informasi produk menggunakan model Gemini
Gunakan model Gemini untuk membuat data berikut bagi produk toko hewan:
- Tambahkan kolom
image_descriptionke tabelproducts_mm. - Isi kolom
animal_type,search_keywords, dansubcategorytabelproducts_mm. - Jalankan kueri yang menampilkan deskripsi setiap merek produk dan juga jumlah produk dari merek tersebut. Deskripsi merek dibuat dengan menganalisis informasi produk untuk semua produk dari merek tersebut, termasuk gambar produk.
Di editor kueri halaman BigQuery, jalankan kueri berikut untuk membuat dan mengisi kolom
image_description:CREATE OR REPLACE TABLE cymbal_pets.products_mm AS SELECT product_id, product_name, brand, category, subcategory, animal_type, search_keywords, price, description, inventory_level, supplier_id, average_rating, image, image_description FROM AI.GENERATE_TABLE( MODEL `cymbal_pets.gemini`, ( SELECT ('Can you describe the following image?', OBJ.GET_ACCESS_URL(image, 'r')) AS prompt, * FROM cymbal_pets.products_mm ), STRUCT('image_description STRING' AS output_schema));
Di editor kueri halaman BigQuery, jalankan kueri berikut untuk memperbarui kolom
animal_type,search_keywords, dansubcategorydengan data yang dihasilkan:UPDATE cymbal_pets.products_mm p SET p.animal_type = s.animal_type, p.search_keywords = s.search_keywords, p.subcategory = s.subcategory FROM ( SELECT animal_type, search_keywords, subcategory, uri FROM AI.GENERATE_TABLE( MODEL `cymbal_pets.gemini`, ( SELECT ( 'For the image of a pet product, concisely generate the following metadata.' '1) animal_type and 2) 5 SEO search keywords, and 3) product subcategory', OBJ.GET_ACCESS_URL(image, 'r'), description) AS prompt, image.uri AS uri, FROM cymbal_pets.products_mm ), STRUCT( 'animal_type STRING, search_keywords ARRAY<STRING>, subcategory STRING' AS output_schema, 100 AS max_output_tokens)) ) s WHERE p.image.uri = s.uri;
Di editor kueri halaman BigQuery, jalankan kueri berikut untuk melihat data yang dihasilkan:
SELECT product_name, image_description, animal_type, search_keywords, subcategory, FROM cymbal_pets.products_mm;
Hasilnya akan terlihat seperti berikut:
+--------------------------------+-------------------------------------+-------------+------------------------+------------------+ | product_name | image.description | animal_type | search_keywords | subcategory | +--------------------------------+-------------------------------------+-------------+------------------------+------------------+ | AquaClear Aquarium Background | The image shows a colorful coral | fish | aquarium background | aquarium decor | | | reef backdrop. The background is a | | fish tank backdrop | | | | blue ocean with a bright light... | | coral reef decor | | | | | | underwater scenery | | | | | | aquarium decoration | | +--------------------------------+-------------------------------------+-------------+------------------------+------------------+ | AquaClear Aquarium | The image shows a long, clear | fish | aquarium gravel vacuum | aquarium | | Gravel Vacuum | plastic tube with a green hose | | aquarium cleaning | cleaning | | | attached to one end. The tube... | | aquarium maintenance | | | | | | fish tank cleaning | | | | | | gravel siphon | | +--------------------------------+-------------------------------------+-------------+------------------------+------------------+ | ... | ... | ... | ... | ... | +--------------------------------+-------------------------------------+-------------+------------------------+------------------+
Di editor kueri halaman BigQuery, jalankan kueri berikut untuk membuat deskripsi setiap merek produk dan juga jumlah produk dari merek tersebut:
SELECT brand, brand_description, cnt FROM AI.GENERATE_TABLE( MODEL `cymbal_pets.gemini`, ( SELECT brand, COUNT(*) AS cnt, ( 'Use the images and text to give one concise brand description for a website brand page.' 'Return the description only.', ARRAY_AGG(OBJ.GET_ACCESS_URL(image, 'r')), ARRAY_AGG(description), ARRAY_AGG(category), ARRAY_AGG(subcategory)) AS prompt FROM cymbal_pets.products_mm GROUP BY brand ), STRUCT('brand_description STRING' AS output_schema)) ORDER BY cnt DESC;
Hasilnya akan terlihat seperti berikut:
+--------------+-------------------------------------+-----+ | brand | brand.description | cnt | +--------------+-------------------------------------+-----+ | AquaClear | AquaClear is a brand of aquarium | 33 | | | and pond care products that offer | | | | a wide range of solutions for... | | +--------------+-------------------------------------+-----+ | Ocean | Ocean Bites is a brand of cat food | 28 | | Bites | that offers a variety of recipes | | | | and formulas to meet the specific.. | | +--------------+-------------------------------------+-----+ | ... | ... |... | +--------------+-------------------------------------+-----+
Membuat UDF Python untuk mengubah gambar produk
Buat UDF Python untuk mengonversi gambar produk menjadi skala abu-abu.
UDF Python menggunakan library open source , dan juga menggunakan eksekusi paralel untuk mentransformasi beberapa gambar secara bersamaan.
Di editor kueri halaman BigQuery, jalankan kueri berikut untuk membuat UDF
to_grayscale:CREATE OR REPLACE FUNCTION cymbal_pets.to_grayscale(src_json STRING, dst_json STRING) RETURNS STRING LANGUAGE python WITH CONNECTION `us.cymbal_conn` OPTIONS (entry_point='to_grayscale', runtime_version='python-3.11', packages=['numpy', 'opencv-python']) AS """ import cv2 as cv import numpy as np from urllib.request import urlopen, Request import json # Transform the image to grayscale. def to_grayscale(src_ref, dst_ref): src_json = json.loads(src_ref) srcUrl = src_json["access_urls"]["read_url"] dst_json = json.loads(dst_ref) dstUrl = dst_json["access_urls"]["write_url"] req = urlopen(srcUrl) arr = np.asarray(bytearray(req.read()), dtype=np.uint8) img = cv.imdecode(arr, -1) # 'Load it as it is' # Convert the image to grayscale gray_image = cv.cvtColor(img, cv.COLOR_BGR2GRAY) # Send POST request to the URL _, img_encoded = cv.imencode('.png', gray_image) req = Request(url=dstUrl, data=img_encoded.tobytes(), method='PUT', headers = { "Content-Type": "image/png", }) with urlopen(req) as f: pass return dst_ref """;
Mengubah gambar produk
Buat tabel products_grayscale dengan kolom ObjectRef yang berisi
jalur tujuan dan pemberi otorisasi untuk gambar skala abu-abu. Jalur
tujuan berasal dari jalur gambar asli.
Setelah membuat tabel, jalankan fungsi to_grayscale untuk membuat gambar skala abu-abu, menulisnya ke bucket Cloud Storage, lalu menampilkan nilai ObjectRefRuntime yang berisi URL akses dan metadata untuk gambar skala abu-abu.
Di editor kueri halaman BigQuery, jalankan kueri berikut untuk membuat tabel
products_grayscale:CREATE OR REPLACE TABLE cymbal_pets.products_grayscale AS SELECT product_id, product_name, image, OBJ.MAKE_REF( CONCAT('gs://BUCKET/cymbal-pets-images/grayscale/', REGEXP_EXTRACT(image.uri, r'([^/]+)$')), 'us.cymbal_conn') AS gray_image FROM cymbal_pets.products_mm;
Ganti
BUCKETdengan nama bucket yang Anda buat.Di editor kueri halaman BigQuery, jalankan kueri berikut untuk membuat gambar skala abu-abu, menulisnya ke bucket Cloud Storage, lalu menampilkan nilai
ObjectRefRuntimeyang berisi URL akses dan metadata untuk gambar skala abu-abu:SELECT cymbal_pets.to_grayscale( TO_JSON_STRING(OBJ.GET_ACCESS_URL(image, 'r')), TO_JSON_STRING(OBJ.GET_ACCESS_URL(gray_image, 'rw'))) FROM cymbal_pets.products_grayscale;
Hasilnya akan terlihat seperti berikut:
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | f0 | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | {"access_urls":{"expiry_time":"2025-04-26T03:00:48Z", | | "read_url":"https://storage.googleapis.com/mybucket/cymbal-pets-images%2Fgrayscale%2Focean-bites-salmon-%26-tuna-cat-food.png?additional_read URL_information", | | "write_url":"https://storage.googleapis.com/myproject/cymbal-pets-images%2Fgrayscale%2Focean-bites-salmon-%26-tuna-cat-food.png?additional_write URL_information"}, | | "objectref":{"authorizer":"myproject.region.myconnection","uri":"gs://myproject/cymbal-pets-images/grayscale/ocean-bites-salmon-&-tuna-cat-food.png"}} | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | {"access_urls":{"expiry_time":"2025-04-26T03:00:48Z", | | "read_url":"https://storage.googleapis.com/mybucket/cymbal-pets-images%2Fgrayscale%2Ffluffy-buns-guinea-pig-tunnel.png?additional _read URL_information", | | "write_url":"https://storage.googleapis.com/myproject/cymbal-pets-images%2Fgrayscale%2Focean-bites-salmon-%26-tuna-cat-food.png?additional_write_URL_information"}, | | "objectref":{"authorizer":"myproject.region.myconnection","uri":"gs://myproject/cymbal-pets-images%2Fgrayscale%2Ffluffy-buns-guinea-pig-tunnel.png"}} | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ... | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Membuat UDF Python untuk membagi data PDF
Buat UDF Python untuk membagi objek PDF yang berisi manual produk hewan peliharaan Cymbal menjadi beberapa bagian.
PDF sering kali berukuran sangat besar dan mungkin tidak sesuai dengan satu panggilan ke model AI generatif. Dengan membagi PDF menjadi beberapa bagian, Anda dapat menyimpan data PDF dalam format yang siap digunakan untuk membuat model agar analisis lebih mudah.
Di editor kueri halaman BigQuery, jalankan kueri berikut untuk membuat UDF
chunk_pdf:-- This function chunks the product manual PDF into multiple parts. -- The function accepts an ObjectRefRuntime value for the PDF file and the chunk size. -- It then parses the PDF, chunks the contents, and returns an array of chunked text. CREATE OR REPLACE FUNCTION cymbal_pets.chunk_pdf(src_json STRING, chunk_size INT64, overlap_size INT64) RETURNS ARRAY<STRING> LANGUAGE python WITH CONNECTION `us.cymbal_conn` OPTIONS (entry_point='chunk_pdf', runtime_version='python-3.11', packages=['pypdf']) AS """ import io import json from pypdf import PdfReader # type: ignore from urllib.request import urlopen, Request def chunk_pdf(src_ref: str, chunk_size: int, overlap_size: int) -> str: src_json = json.loads(src_ref) srcUrl = src_json["access_urls"]["read_url"] req = urlopen(srcUrl) pdf_file = io.BytesIO(bytearray(req.read())) reader = PdfReader(pdf_file, strict=False) # extract and chunk text simultaneously all_text_chunks = [] curr_chunk = "" for page in reader.pages: page_text = page.extract_text() if page_text: curr_chunk += page_text # split the accumulated text into chunks of a specific size with overlaop # this loop implements a sliding window approach to create chunks while len(curr_chunk) >= chunk_size: split_idx = curr_chunk.rfind(" ", 0, chunk_size) if split_idx == -1: split_idx = chunk_size actual_chunk = curr_chunk[:split_idx] all_text_chunks.append(actual_chunk) overlap = curr_chunk[split_idx + 1 : split_idx + 1 + overlap_size] curr_chunk = overlap + curr_chunk[split_idx + 1 + overlap_size :] if curr_chunk: all_text_chunks.append(curr_chunk) return all_text_chunks """;
Menganalisis data PDF
Jalankan fungsi chunk_pdf untuk membagi data PDF dalam tabel product_manuals, lalu buat tabel product_manual_chunk_strings yang berisi satu bagian PDF per baris. Gunakan model Gemini pada data product_manual_chunk_strings untuk meringkas informasi hukum yang terdapat dalam manual produk.
Di editor kueri halaman BigQuery, jalankan kueri berikut untuk membuat tabel
product_manual_chunk_strings:CREATE OR REPLACE TABLE cymbal_pets.product_manual_chunk_strings AS SELECT chunked FROM cymbal_pets.product_manuals, UNNEST (cymbal_pets.chunk_pdf( TO_JSON_STRING( OBJ.GET_ACCESS_URL(OBJ.MAKE_REF(uri, 'us.cymbal_conn'), 'r')), 1000, 100 )) as chunked;
Di editor kueri halaman BigQuery, jalankan kueri berikut untuk menganalisis data PDF menggunakan model Gemini:
SELECT ml_generate_text_llm_result FROM ML.GENERATE_TEXT( MODEL `cymbal_pets.gemini`, ( SELECT ( 'Can you summarize the product manual as bullet points? Highlight the legal clauses', chunked) AS prompt, FROM cymbal_pets.product_manual_chunk_strings ), STRUCT( TRUE AS FLATTEN_JSON_OUTPUT));
Hasilnya akan terlihat seperti berikut:
+-------------------------------------------------------------------------------------------------------------------------------------------+ | ml_generate_text_llm_result | +-------------------------------------------------------------------------------------------------------------------------------------------+ | ## CritterCuisine Pro 5000 Automatic Pet Feeder Manual Summary: | | | | **Safety:** | | | | * **Stability:** Place feeder on a level, stable surface to prevent tipping. | | * **Power Supply:** Only use the included AC adapter. Using an incompatible adapter can damage the unit and void the warranty. | | * **Cord Safety:** Keep the power cord out of reach of pets to prevent chewing or entanglement. | | * **Children:** Supervise children around the feeder. This is not a toy. | | * **Pet Health:** Consult your veterinarian before using an automatic feeder if your pet has special dietary needs, health conditions, or | +-------------------------------------------------------------------------------------------------------------------------------------------+ | ## Product Manual Summary: | | | | **6.3 Manual Feeding:** | | | | * Press MANUAL button to dispense a single portion (Meal 1 size). **(Meal Enabled)** | | | | **6.4 Recording a Voice Message:** | | | | * Press and hold VOICE button. | | * Speak clearly into the microphone (up to 10 seconds). | | * Release VOICE button to finish recording. | | * Briefly press VOICE button to play back the recording. | | * To disable the voice message, record a blank message (hold VOICE button for 10 seconds without speaking). **(Meal Enabled)** | | | | **6.5 Low Food Level Indicator:** | +-------------------------------------------------------------------------------------------------------------------------------------------+ | ... | +-------------------------------------------------------------------------------------------------------------------------------------------+
Membuat embedding dan melakukan penelusuran vektor
Buat embedding dari data gambar, lalu gunakan embedding untuk menampilkan gambar serupa menggunakan penelusuran vektor.
Dalam skenario produksi, sebaiknya buat indeks vektor sebelum menjalankan penelusuran vektor. Indeks vektor memungkinkan Anda melakukan penelusuran vektor dengan lebih cepat, dengan mengurangi perolehan dan sehingga menampilkan hasil yang lebih mendekati.
Di editor kueri halaman BigQuery, jalankan kueri berikut untuk membuat tabel
products_embeddings:CREATE OR REPLACE TABLE cymbal_pets.products_embedding AS SELECT product_id, ml_generate_embedding_result as embedding, content as image FROM ML.GENERATE_EMBEDDING( MODEL `cymbal_pets.embedding_model`, ( SELECT OBJ.GET_ACCESS_URL(image, 'r') as content, image, product_id FROM cymbal_pets.products_mm ), STRUCT () );
Di editor kueri halaman BigQuery, jalankan kueri berikut untuk menjalankan penelusuran vektor guna menampilkan gambar produk yang mirip dengan gambar input yang diberikan:
SELECT * FROM VECTOR_SEARCH( TABLE cymbal_pets.products_embedding, 'embedding', (SELECT ml_generate_embedding_result as embedding FROM ML.GENERATE_EMBEDDING( MODEL `cymbal_pets.embedding_model`, (SELECT OBJ.FETCH_METADATA(OBJ.MAKE_REF('gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/images/cozy-naps-cat-scratching-post-with-condo.png', 'us.cymbal_conn')) as content) )) );
Hasilnya akan terlihat seperti berikut:
+-----------------+-----------------+----------------+----------------------------------------------+--------------------+-------------------------------+------------------------------------------------+----------------+ | query.embedding | base.product_id | base.embedding | base.image.uri | base.image.version | base.image.authorizer | base.image.details | distance | +-----------------+-----------------+----------------+----------------------------------------------+--------------------+-------------------------------+------------------------------------------------+----------------+ | -0.0112330541 | 181 | -0.0112330541 | gs://cloud-samples-data/bigquery/ | 12345678910 | myproject.region.myconnection | {"gcs_metadata":{"content_type": | 0.0 | | 0.0142525584 | | 0.0142525584 | tutorials/cymbal-pets/images/ | | | "image/png","md5_hash":"21234567hst16555w60j", | | | 0.0135886827 | | 0.0135886827 | cozy-naps-cat-scratching-post-with-condo.png | | | "size":828318,"updated":1742492688982000}} | | | 0.0149955815 | | 0.0149955815 | | | | | | | ... | | ... | | | | | | | | | | | | | | | | | | | | | | | | +-----------------+-----------------+----------------+----------------------------------------------+--------------------+-------------------------------+------------------------------------------------+----------------+ | -0.0112330541 | 187 | -0.0190353896 | gs://cloud-samples-data/bigquery/ | 23456789101 | myproject.region.myconnection | {"gcs_metadata":{"content_type": | 0.4216330832.. | | 0.0142525584 | | 0.0116206668 | tutorials/cymbal-pets/images/ | | | "image/png","md5_hash":"7328728fhakd9937djo4", | | | 0.0135886827 | | 0.0136198215 | cozy-naps-cat-scratching-post-with-bed.png | | | "size":860113,"updated":1742492688774000}} | | | 0.0149955815 | | 0.0173457414 | | | | | | | ... | | ... | | | | | | | | | | | | | | | | | | | | | | | | +---------C--------+-----------------+----------------+----------------------------------------------+--------------------+-------------------------------+------------------------------------------------+----------------+ | ... | ... | ... | ... | ... | ... | ... | ... | +-----------------+-----------------+----------------+----------------------------------------------+--------------------+-------------------------------+------------------------------------------------+----------------+
Memproses data multimodal yang diurutkan menggunakan array nilai ObjectRef
Bagian ini menunjukkan cara menyelesaikan tugas berikut:
- Buat ulang tabel
product_manualssehingga berisi file PDF untuk panduan produkCrittercuisine 5000, dan file PDF untuk setiap halaman panduan tersebut. - Buat tabel yang memetakan manual ke bagian-bagiannya. Nilai
ObjectRefyang mewakili manual lengkap disimpan dalam kolomSTRUCT<uri STRING, version STRING, authorizer STRING, details JSON>>. NilaiObjectRefyang merepresentasikan halaman manual disimpan dalam kolomARRAY<STRUCT<uri STRING, version STRING, authorizer STRING, details JSON>>. - Menganalisis array nilai
ObjectRefsecara bersamaan untuk menampilkan satu nilai yang dihasilkan. - Menganalisis array nilai
ObjectRefsecara terpisah dan menampilkan nilai yang dihasilkan untuk setiap nilai array.
Sebagai bagian dari tugas analisis, Anda mengonversi array nilai ObjectRef menjadi
daftar nilai
ObjectRefRuntime
yang diurutkan, lalu meneruskan daftar tersebut ke model Gemini, dengan menentukan nilai
ObjectRefRuntime sebagai bagian dari perintah. Nilai ObjectRefRuntime
memberikan URL yang ditandatangani yang digunakan model untuk mengakses informasi objek di
Cloud Storage.
Ikuti langkah-langkah berikut untuk memproses data multimodal yang diurutkan menggunakan array nilai ObjectRef:
Buka halaman BigQuery.
Di editor kueri, jalankan kueri berikut untuk membuat ulang tabel
product_manuals:CREATE OR REPLACE EXTERNAL TABLE `cymbal_pets.product_manuals` WITH CONNECTION `us.cymbal_conn` OPTIONS ( object_metadata = 'SIMPLE', uris = [ 'gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/documents/*.pdf', 'gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/document_chunks/*.pdf']);
Di editor kueri, jalankan kueri berikut untuk menulis data PDF ke tabel
map_manual_to_chunks:-- Extract the file and chunks into a single table. -- Store the chunks in the chunks column as array of ObjectRefs (ordered by page number) CREATE OR REPLACE TABLE cymbal_pets.map_manual_to_chunks AS SELECT ARRAY_AGG(m1.ref)[0] manual, ARRAY_AGG(m2.ref ORDER BY m2.ref.uri) chunks FROM cymbal_pets.product_manuals m1 JOIN cymbal_pets.product_manuals m2 ON REGEXP_EXTRACT(m1.uri, r'.*/([^.]*).[^/]+') = REGEXP_EXTRACT(m2.uri, r'.*/([^.]*)_page[0-9]+.[^/]+') GROUP BY m1.uri;
Di editor kueri, jalankan kueri berikut untuk melihat data PDF dalam tabel
map_manual_to_chunks:SELECT * FROM cymbal_pets.map_manual_to_chunks;
Hasilnya akan terlihat seperti berikut:
+-------------------------------------+--------------------------------+-----------------------------------+------------------------------------------------------+-------------------------------------------+---------------------------------+------------------------------------+-------------------------------------------------------+ | manual.uri | manual.version | manual.authorizer | manual.details | chunks.uri | chunks.version | chunks.authorizer | chunks.details | +-------------------------------------+--------------------------------+-----------------------------------+------------------------------------------------------+-------------------------------------------+---------------------------------+------------------------------------+-------------------------------------------------------+ | gs://cloud-samples-data/bigquery/ | 1742492785900455 | myproject.region.myconnection | {"gcs_metadata":{"content_type":"application/pef", | gs://cloud-samples-data/bigquery/ | 1745875761227129 | myproject.region.myconnection | {"gcs_metadata":{"content_type":"application/pdf", | | tutorials/cymbal-pets/documents/ | | | "md5_hash":"c9032b037693d15a33210d638c763d0e", | tutorials/cymbal-pets/documents/ | | | "md5_hash":"5a1116cce4978ec1b094d8e8b49a1d7c", | | crittercuisine_5000_user_manual.pdf | | | "size":566105,"updated":1742492785941000}} | crittercuisine_5000_user_manual_page1.pdf | | | "size":504583,"updated":1745875761266000}} | | | | | +-------------------------------------------+---------------------------------+------------------------------------+-------------------------------------------------------+ | | | | | crittercuisine_5000_user_manual_page1.pdf | 1745875760613874 | myproject.region.myconnection | {"gcs_metadata":{"content_type":"application/pdf", | | | | | | tutorials/cymbal-pets/documents/ | | | "md5_hash":"94d03ec65d28b173bc87eac7e587b325", | | | | | | crittercuisine_5000_user_manual_page2.pdf | | | "size":94622,"updated":1745875760649000}} | | | | | +-------------------------------------------+---------------------------------+------------------------------------+-------------------------------------------------------+ | | | | | ... | ... | ... | ... | +-------------------------------------+--------------------------------+-----------------------------------+------------------------------------------------------+-------------------------------------------+---------------------------------+------------------------------------+-------------------------------------------------------+Di editor kueri, jalankan kueri berikut untuk membuat satu respons dari model Gemini berdasarkan analisis array nilai
ObjectRef:WITH manuals AS ( SELECT OBJ.GET_ACCESS_URL(manual, 'r') AS manual, ARRAY( SELECT OBJ.GET_ACCESS_URL(chunk, 'r') AS chunk FROM UNNEST(m1.chunks) AS chunk WITH OFFSET AS idx ORDER BY idx ) AS chunks FROM cymbal_pets.map_manual_to_chunks AS m1 ) SELECT ml_generate_text_llm_result AS Response FROM ML.GENERATE_TEXT( MODEL `cymbal_pets.gemini`, ( SELECT ( 'Can you provide a page by page summary for the first 3 pages of the attached manual? Only write one line for each page. The pages are provided in serial order', manuals.chunks) AS prompt, FROM manuals ), STRUCT(TRUE AS FLATTEN_JSON_OUTPUT));
Hasilnya akan terlihat seperti berikut:
+-------------------------------------------+ | Response | +-------------------------------------------+ | Page 1: This manual is for the | | CritterCuisine Pro 5000 automatic | | pet feeder. | | Page 2: The manual covers safety | | precautions, what's included, | | and product overview. | | Page 3: The manual covers assembly, | | initial setup, and programming the clock. | +-------------------------------------------+
Di editor kueri, jalankan kueri berikut untuk membuat beberapa respons dari model Gemini berdasarkan analisis array nilai
ObjectRef:WITH input_chunked_objrefs AS ( SELECT row_id, offset, chunk_ref FROM ( SELECT ROW_NUMBER() OVER () AS row_id, * FROM `cymbal_pets.map_manual_to_chunks` ) AS indexed_table LEFT JOIN UNNEST(indexed_table.chunks) AS chunk_ref WITH OFFSET ), get_access_urls AS ( SELECT row_id, offset, chunk_ref, OBJ.GET_ACCESS_URL(chunk_ref, 'r') AS ObjectRefRuntime FROM input_chunked_objrefs ), valid_get_access_urls AS ( SELECT * FROM get_access_urls WHERE ObjectRefRuntime['runtime_errors'] IS NULL ), ordered_output_objrefruntime_array AS ( SELECT ARRAY_AGG(ObjectRefRuntime ORDER BY offset) AS ObjectRefRuntimeArray FROM valid_get_access_urls GROUP BY row_id ) SELECT page1_summary, page2_summary, page3_summary FROM AI.GENERATE_TABLE( MODEL `cymbal_pets.gemini`, ( SELECT ( 'Can you provide a page by page summary for the first 3 pages of the attached manual? Only write one line for each page. The pages are provided in serial order', ObjectRefRuntimeArray) AS prompt, FROM ordered_output_objrefruntime_array ), STRUCT( 'page1_summary STRING, page2_summary STRING, page3_summary STRING' AS output_schema));
Hasilnya akan terlihat seperti berikut:
+-----------------------------------------------+-------------------------------------------+----------------------------------------------------+ | page1_summary | page2_summary | page3_summary | +-----------------------------------------------+-------------------------------------------+----------------------------------------------------+ | This manual provides an overview of the | This section explains how to program | This page covers connecting the feeder to Wi-Fi | | CritterCuisine Pro 5000 automatic pet feeder, | the feeder's clock, set feeding | using the CritterCuisine Connect app, remote | | including its features, safety precautions, | schedules, copy and delete meal settings, | feeding, managing feeding schedules, viewing | | assembly instructions, and initial setup. | manually feed your pet, record | feeding logs, receiving low food alerts, | | | a voice message, and understand | updating firmware, creating multiple pet profiles, | | | the low food level indicator. | sharing access with other users, and cleaning | | | | and maintaining the feeder. | +-----------------------------------------------+-------------------------------------------+----------------------------------------------------+
Pembersihan
- In the Google Cloud console, go to the Manage resources page.
- In the project list, select the project that you want to delete, and then click Delete.
- In the dialog, type the project ID, and then click Shut down to delete the project.