A BigQuery solution for analyzing text in multiple languages

Generate Embeddings for Source data: <br /> <br /> CREATE OR REPLACE TABLE `xxxxxxx.reviews.multilingual_texas_reviews_Bakery_embedding` AS <br /> (SELECT * <br /> FROM ML.GENERATE_EMBEDDING( <br /> MODEL `xxxxxxx.reviews.multilingual_embedding`, <br /> (SELECT CONCAT(extracted_text,’,’,rating,’,’,category) AS content <br /> FROM `xxxxxxx.reviews.multilingual_texas_reviews` ) <br /> ) <br /> ); <br /> <br /> <br /> Create Vector Index for Vector Search: <br /> CREATE OR REPLACE VECTOR INDEX multilingual_review_index <br /> ON `xxxxxxx.reviews.multilingual_texas_reviews_Bakery_embedding`(ml_generate_embedding_result) <br /> OPTIONS(index_type = ‘IVF’, <br /> distance_type = ‘COSINE’, <br /> ivf_options = ‘{“num_lists”:500}’) <br /> <br /> <br /> Check information schema that vector indexes are created <br /> SELECT table_name, index_name, index_status, <br /> coverage_percentage, last_refresh_time, disable_reason <br /> FROM `xxxxxxx.reviews.INFORMATION_SCHEMA.VECTOR_INDEXES` <br /> <br /> <br /> Vector Search for your question <br /> SELECT query.query, base.content, base.rating, base.category <br /> FROM VECTOR_SEARCH( <br /> TABLE `xxxxxxx.reviews.multilingual_texas_reviews_Bakery_embedding`, ‘ml_generate_embedding_result’, <br /> ( <br /> SELECT ml_generate_embedding_result, content AS query <br /> FROM ML.GENERATE_EMBEDDING( <br /> MODEL `xxxxxxx.reviews.multilingual_embedding`, <br /> (SELECT “休士頓哪裡有正宗的葡式蛋撻和港式麵包？” AS content)) <br /> ), <br /> top_k => 10, options => ‘{“fraction_lists_to_search”: 0.08}’) <br /> <br /> <br /> Translation API to detect source language: <br /> SELECT <br /> ml_translate_result.languages[0].language_code AS target_language_code <br /> FROM <br /> ML.TRANSLATE(MODEL `xxxxxxx.reviews.model_cloud_translate`, ( <br /> SELECT “休士頓哪裡有正宗的葡式蛋撻和港式麵包？” AS text_content), <br /> STRUCT(“detect_language” AS translate_mode)) <br /> <br /> <br /> Translation API to translate reviews: <br /> SELECT <br /> text_content AS `Original Text`, <br /> “zh-CN” AS `Destination Language`, <br /> STRING(ml_translate_result.translations[0].translated_text) AS Translation <br /> FROM ML.TRANSLATE( <br /> MODEL `xxxxxxx.reviews.model_cloud_translate`, <br /> (select ‘{txt_}’ as text_content), <br /> STRUCT(‘translate_text’ AS translate_mode, ‘{lang_}’ AS target_language_code))