使用 Gemini 模型和 ML.GENERATE_TEXT 函数生成文本
本教程介绍了如何创建基于 gemini-1.0-pro-002 模型的远程模型，然后介绍了如何将该模型与 ML.GENERATE_TEXT 函数搭配使用，以便从 bigquery-public-data.imdb.reviews 公共表中提取关键字并对电影评价执行情感分析：
所需权限
- 如需创建数据集，您需要拥有
bigquery.datasets.createIdentity and Access Management (IAM) 权限。 如需创建连接资源，您需要以下 IAM 权限：
bigquery.connections.createbigquery.connections.get
如需向连接的服务账号授予权限，您需要以下权限：
resourcemanager.projects.setIamPolicy
如需创建模型，您需要以下权限：
bigquery.jobs.createbigquery.models.createbigquery.models.getDatabigquery.models.updateDatabigquery.connections.delegate
如需运行推理，您需要以下权限：
bigquery.models.getDatabigquery.jobs.create
费用
在本文档中，您将使用 Google Cloud 的以下收费组件：
- BigQuery ML: You incur costs for the data that you process in BigQuery.
- Vertex AI: You incur costs for calls to the Vertex AI service that's represented by the remote model.
您可使用价格计算器根据您的预计使用情况来估算费用。
如需详细了解 BigQuery 价格，请参阅 BigQuery 文档中的 BigQuery 价格。
如需详细了解 Vertex AI 价格，请参阅 Vertex AI 价格页面。
准备工作
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
-
Enable the BigQuery, BigQuery Connection, and Vertex AI APIs.
创建数据集
创建 BigQuery 数据集以存储您的机器学习模型：
在 Google Cloud 控制台中，转到 BigQuery 页面。
在探索器窗格中，点击您的项目名称。
点击 查看操作 > 创建数据集。
在创建数据集页面上，执行以下操作：
在数据集 ID 部分，输入
bqml_tutorial。在位置类型部分，选择多区域，然后选择 US (multiple regions in United States)（美国[美国的多个区域]）。
公共数据集存储在
US多区域中。为简单起见，请将数据集存储在同一位置。保持其余默认设置不变，然后点击创建数据集。
创建连接
创建 Cloud 资源连接并获取连接的服务账号。 在与上一步中创建的数据集相同的位置创建连接。
从下列选项中选择一项：
控制台
转到 BigQuery 页面。
如需创建连接，请点击 添加，然后点击与外部数据源的连接。
在连接类型列表中，选择 Vertex AI 远程模型、远程函数和 BigLake（Cloud 资源）。
在连接 ID 字段中，输入连接的名称。
点击创建连接。
点击转到连接。
在连接信息窗格中，复制服务账号 ID 以在后续步骤中使用。
bq
在命令行环境中，创建连接：
bq mk --connection --location=REGION --project_id=PROJECT_ID \ --connection_type=CLOUD_RESOURCE CONNECTION_ID--project_id参数会替换默认项目。替换以下内容：
REGION：您的连接区域PROJECT_ID：您的 Google Cloud 项目 IDCONNECTION_ID：您的连接的 ID
当您创建连接资源时，BigQuery 会创建一个唯一的系统服务账号，并将其与该连接相关联。
问题排查：如果您收到以下连接错误，请更新 Google Cloud SDK：
Flags parsing error: flag --connection_type=CLOUD_RESOURCE: value should be one of...
检索并复制服务账号 ID 以在后续步骤中使用：
bq show --connection PROJECT_ID.REGION.CONNECTION_ID
输出类似于以下内容：
name properties 1234.REGION.CONNECTION_ID {"serviceAccountId": "connection-1234-9u56h9@gcp-sa-bigquery-condel.iam.gserviceaccount.com"}
Terraform
将以下部分附加到 main.tf 文件中。
## This creates a cloud resource connection.
## Note: The cloud resource nested object has only one output only field - serviceAccountId.
resource "google_bigquery_connection" "connection" {
connection_id = "CONNECTION_ID"
project = "PROJECT_ID"
location = "REGION"
cloud_resource {}
}
替换以下内容：
CONNECTION_ID：您的连接的 IDPROJECT_ID：您的 Google Cloud 项目 IDREGION：您的连接区域
向连接的服务账号授予权限
如需向连接的服务账号授予适当的角色以访问 Vertex AI 服务，请执行以下步骤：
前往 IAM 和管理页面。
点击 授予访问权限。
在新的主账号字段中，输入您之前复制的服务账号 ID。
在选择角色字段中，选择 Vertex AI，然后选择 Vertex AI User 角色。
点击保存。
创建远程模型
创建一个代表托管式 Vertex AI 模型的远程模型：
在 Google Cloud 控制台中，转到 BigQuery 页面。
在查询编辑器中，运行以下语句：
CREATE OR REPLACE MODEL `bqml_tutorial.gemini_model` REMOTE WITH CONNECTION `LOCATION.CONNECTION_ID` OPTIONS (ENDPOINT = 'gemini-1.0-pro-002');
替换以下内容：
LOCATION：连接位置CONNECTION_ID：BigQuery 连接的 ID当您在 Google Cloud 控制台中查看连接详情时，它是连接 ID 中显示的完全限定连接 ID 的最后一部分中的值，例如
projects/myproject/locations/connection_location/connections/myconnection
查询需要几秒钟才能完成，之后模型 gemini_model 会显示在探索器窗格的 bqml_tutorial 数据集中。由于查询使用 CREATE MODEL 语句来创建模型，因此没有查询结果。
执行关键字提取
使用远程模型和 ML.GENERATE_TEXT 函数对 IMDB 电影评论执行关键字提取：
在 Google Cloud 控制台中，转到 BigQuery 页面。
在查询编辑器中，输入以下语句，对五项电影评论执行关键字提取：
SELECT ml_generate_text_result['candidates'][0]['content'] AS generated_text, ml_generate_text_result['candidates'][0]['safety_ratings'] AS safety_ratings, * EXCEPT (ml_generate_text_result) FROM ML.GENERATE_TEXT( MODEL `bqml_tutorial.gemini_model`, ( SELECT CONCAT('Extract the key words from the text below: ', review) AS prompt, * FROM `bigquery-public-data.imdb.reviews` LIMIT 5 ), STRUCT( 0.2 AS temperature, 100 AS max_output_tokens));输出类似于以下内容，为清楚起见，省略了非生成的列：
+----------------------------------------+---------------------------------------------+-------------------------+----------------------------+-----+ | generated_text | safety_ratings | ml_generate_text_status | prompt | ... | +----------------------------------------+---------------------------------------------+-------------------------+----------------------------+-----+ | {"parts":[{"text":"## Key words:\n\n* | [{"category":1,"probability":1, | | Extract the key words from | | | **Negative sentiment:** \"terribly | "probability_score":0.28856909, | | the text below: I had to | | | bad acting\", \"dumb story\", \"not | "severity":1,"severity_score":0.1510278}, | | see this on the British | | | even a kid would enjoy this\", | {"category":2,"probability":1, | | Airways plane. It was | | | \"something to switch off\"\n* | "probability_score":0.062445287, | | terribly bad acting and | | | **Context:** \"British Airways plane\" | "severity":1,"severity_score":0.10393038}, | | a dumb story. Not even | | | \n* **Genre:** \"movie\" (implied)... | {"category":3,"probability":2,... | | a kid would enjoy this... | | +----------------------------------------+---------------------------------------------+-------------------------+----------------------------+-----+ | {"parts":[{"text":"## Key words:\n\n* | [{"category":1,"probability":1, | | Extract the key words from | | | **Movie:** The Real Howard Spitz\n* | "probability_score":0.2995148,"severity":2, | | the text below: This is | | | **Genre:** Family movie\n* | "severity_score":0.22354652}, | | a family movie that was | | | **Broadcast:** ITV station, 1.00 am\n* | {"category":2,"probability":1," | | broadcast on my local | | | **Director:** Vadim Jean\n* | probability_score":0.13072868, | | ITV station at 1.00 am a | | | **Main character:** Howard Spitz, | "severity":1,"severity_score":0.07030385}, | | couple of nights ago. | | | a children's author who hates... | {"category":3,"probability":2," ... | | This might be a strange... | | +----------------------------------------+---------------------------------------------+-------------------------+----------------------------+-----+结果包括以下列：
generated_text：生成的文本。safety_ratings：安全属性，以及关于内容是否因某个屏蔽类别而被屏蔽的信息。如需详细了解安全属性，请参阅 Vertex PaLM API。ml_generate_text_status：相应行的 API 响应状态。如果操作成功，则此值为空。prompt：用于情感分析的提示。bigquery-public-data.imdb.reviews表中的所有列。
可选：使用
flatten_json_output参数在单独的列中返回生成的文本和安全属性，而不是像在上一步中那样手动解析函数返回的 JSON。在查询编辑器中，运行以下语句：
SELECT * FROM ML.GENERATE_TEXT( MODEL `bqml_tutorial.gemini_model`, ( SELECT CONCAT('Extract the key words from the text below: ', review) AS prompt, * FROM `bigquery-public-data.imdb.reviews` LIMIT 5 ), STRUCT( 0.2 AS temperature, 100 AS max_output_tokens, TRUE AS flatten_json_output));输出类似于以下内容，为清楚起见，省略了非生成的列：
+----------------------------------------+----------------------------------------------+-------------------------+----------------------------+-----+ | ml_generate_text_llm_result | ml_generate_text_rai_result | ml_generate_text_status | prompt | ... | +----------------------------------------+----------------------------------------------+-------------------------+----------------------------+-----+ | ## Keywords: | [{"category":1,"probability":1, | | Extract the key words from | | | | "probability_score":0.29391089,"severity":1, | | the text below: I had to | | | * **Negative sentiment:** | "severity_score":0.15584777},{"category":2, | | see this on the British | | | "terribly bad acting", "dumb | "probability":1,"probability_score": | | Airways plane. It was | | | story", "not even a kid would | 0.061311536,"severity":1,"severity_score": | | terribly bad acting and | | | enjoy this", "switch off" | 0.10320505},{"category":3,"probability":2, | | a dumb story. Not even | | | * **Context:** "British | "probability_score":0.60340... | | a kid would enjoy this... | | +----------------------------------------+----------------------------------------------+-------------------------+----------------------------+-----+ | ## Key words: | [{"category":1,"probability":1, | | Extract the key words from | | | | "probability_score":0.16968086,"severity":1, | | the text below: This is | | | * **Movie:** The Real Howard Spitz | "severity_score":0.13386749},{"category":2, | | a family movie that was | | | * **Genre:** Family movie | "probability":1,"probability_score": | | broadcast on my local | | | * **Broadcast:** ITV, 1.00 | 0.14841709,"severity":1,"severity_score": | | ITV station at 1.00 am a | | | am | 0.062674366},{"category":3,"probability":1, | | couple of nights ago. | | | - ... | "probability_score":0.38116196,... | | This might be a strange... | | +----------------------------------------+----------------------------------------------+-------------------------+----------------------------+-----+结果包括以下列：
ml_generate_text_llm_result：生成的文本。ml_generate_text_rai_result：安全属性，以及关于内容是否因某个屏蔽类别而被屏蔽的信息。如需详细了解安全属性，请参阅 Vertex PaLM API。ml_generate_text_status：相应行的 API 响应状态。如果操作成功，则此值为空。prompt：用于关键字提取的提示。bigquery-public-data.imdb.reviews表中的所有列。
执行情感分析
使用远程模型和 ML.GENERATE_TEXT 函数对 IMDB 电影评论执行情感分析：
在 Google Cloud 控制台中，转到 BigQuery 页面。
在查询编辑器中，运行以下语句，对五项电影评价执行情感分析：
SELECT ml_generate_text_result['candidates'][0]['content'] AS generated_text, ml_generate_text_result['candidates'][0]['safety_ratings'] AS safety_ratings, * EXCEPT (ml_generate_text_result) FROM ML.GENERATE_TEXT( MODEL `bqml_tutorial.gemini_model`, ( SELECT CONCAT( 'perform sentiment analysis on the following text, return one the following categories: positive, negative: ', review) AS prompt, * FROM `bigquery-public-data.imdb.reviews` LIMIT 5 ), STRUCT( 0.2 AS temperature, 100 AS max_output_tokens));输出类似于以下内容，为清楚起见，省略了非生成的列：
+--------------------------------------------+---------------------------------------------+-------------------------+----------------------------+-----+ | generated_text | safety_ratings | ml_generate_text_status | prompt | ... | +--------------------------------------------+---------------------------------------------+-------------------------+----------------------------+-----+ | {"parts":[{"text":"## Sentiment Analysis: | [{"category":1,"probability":1, | | perform sentiment analysis | | | Negative \n\nThis text expresses a | "probability_score":0.33895186, | | on the following text, | | | strongly negative sentiment towards the | "severity":1,"severity_score":0.10521054}, | | return one the following | | | movie. Here's why:\n\n* **Negative | {"category":2,"probability":1, | | negative: I had to see | | | like \"terribly,\" \"dumb,\" and | "probability_score":0.069163561,"severity" | | this on the British | | | \"not even\" to describe the acting... | :1,"severity_score":0.093512312},... | | Airways plane. It was... | | +--------------------------------------------+---------------------------------------------+-------------------------+----------------------------+-----+ | {"parts":[{"text":"## Sentiment Analysis: | [{"category":1,"probability":1, | | perform sentiment analysis | | | Negative \n\nThis review expresses a | "probability_score":0.35644665, | | on the following text, | | | predominantly negative sentiment towards | "severity":1,"severity_score":0.15253653}, | | return one the following | | | the movie \"The Real Howard Spitz.\" | {"category":2,"probability":1, | | categories: positive, | | | Here's why:\n\n* **Criticism of the film's | "probability_score":0.063948415,"severity" | | negative: This is a family | | | premise:** The reviewer finds it strange | :1,"severity_score":0.047249716}, | | movie that was broadcast | | | that a film about a children's author... | {"category":3,"probability":2,... | | on my local ITV station... | | +--------------------------------------------+---------------------------------------------+-------------------------+----------------------------+-----+结果包括为执行关键字提取记录的相同列。
清理
- 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.