使用远程函数

控制台

1. 转到 BigQuery 页面。

   转到 BigQuery

2. 如需创建连接，请点击 add 添加，然后点击与外部数据源的连接。

3. 在连接类型列表中，选择 Vertex AI 远程模型、远程函数和 BigLake（Cloud 资源）。

4. 在连接 ID 字段中，输入连接的名称。

5. 点击创建连接。

6. 点击转到连接。

7. 在连接信息窗格中，复制服务账号 ID 以在后续步骤中使用。

bq

1. 在命令行环境中，创建连接：

   bq mk --connection --location=REGION --project_id=PROJECT_ID \
       --connection_type=CLOUD_RESOURCE CONNECTION_ID

   --project_id 参数会替换默认项目。

   替换以下内容：

   • REGION：您的连接区域
   • PROJECT_ID：您的 Google Cloud 项目 ID
   • CONNECTION_ID：您的连接的 ID

   当您创建连接资源时，BigQuery 会创建一个唯一的系统服务账号，并将其与该连接相关联。

   问题排查：如果您收到以下连接错误，请更新 Google Cloud SDK：

   Flags parsing error: flag --connection_type=CLOUD_RESOURCE: value should be one of...
   

2. 检索并复制服务账号 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

使用 google_bigquery_connection 资源。

如需向 BigQuery 进行身份验证，请设置应用默认凭据。如需了解详情，请参阅为客户端库设置身份验证。

以下示例会在 US 区域中创建一个名为 my_cloud_resource_connection 的 Cloud 资源连接：

# This queries the provider for project information.
data "google_project" "default" {}

# This creates a cloud resource connection in the US region named my_cloud_resource_connection.
# Note: The cloud resource nested object has only one output field - serviceAccountId.
resource "google_bigquery_connection" "default" {
  connection_id = "my_cloud_resource_connection"
  project       = data.google_project.default.project_id
  location      = "US"
  cloud_resource {}
}

如需在 Google Cloud 项目中应用 Terraform 配置，请完成以下部分中的步骤。

准备 Cloud Shell

1. 启动 Cloud Shell。

2. 设置要在其中应用 Terraform 配置的默认 Google Cloud 项目。

   您只需为每个项目运行一次以下命令，即可在任何目录中运行它。

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   如果您在 Terraform 配置文件中设置显式值，则环境变量会被替换。

准备目录

每个 Terraform 配置文件都必须有自己的目录（也称为“根模块”）。

1. 在 Cloud Shell 中，创建一个目录，并在该目录中创建一个新文件。文件名必须具有 .tf 扩展名，例如 main.tf。在本教程中，该文件称为 main.tf。

   mkdir DIRECTORY && cd DIRECTORY && touch main.tf

2. 如果您按照教程进行操作，可以在每个部分或步骤中复制示例代码。

   将示例代码复制到新创建的 main.tf 中。

   （可选）从 GitHub 中复制代码。如果端到端解决方案包含 Terraform 代码段，则建议这样做。

3. 查看和修改要应用到您的环境的示例参数。
4. 保存更改。
5. 初始化 Terraform。您只需为每个目录执行一次此操作。

   terraform init

   （可选）如需使用最新的 Google 提供程序版本，请添加 -upgrade 选项：

   terraform init -upgrade

应用更改

1. 查看配置并验证 Terraform 将创建或更新的资源是否符合您的预期：

   terraform plan

   根据需要更正配置。

2. 通过运行以下命令并在提示符处输入 yes 来应用 Terraform 配置：

   terraform apply

   等待 Terraform 显示“应用完成！”消息。

3. 打开您的 Google Cloud 项目以查看结果。在 Google Cloud 控制台的界面中找到资源，以确保 Terraform 已创建或更新它们。

SQL

在 BigQuery 中运行以下 CREATE FUNCTION 语句：

1. 在 Google Cloud 控制台中，转到 BigQuery 页面。

   转到 BigQuery

2. 在查询编辑器中，输入以下语句：

   CREATE FUNCTION PROJECT_ID.DATASET_ID.remote_add(x INT64, y INT64) RETURNS INT64
   REMOTE WITH CONNECTION PROJECT_ID.LOCATION.CONNECTION_NAME
   OPTIONS (
     endpoint = 'ENDPOINT_URL'
   )

   请替换以下内容：

   • DATASET_ID：BigQuery 数据集的 ID。
   • ENDPOINT_URL：Cloud Run 函数或 Cloud Run 远程函数端点的网址。

3. 点击 play_circle 运行。

如需详细了解如何运行查询，请参阅运行交互式查询。

BigQuery DataFrame

1. 启用所需的 API 并确保您拥有所需的角色，如远程函数的要求部分所述。

2. 使用 remote_function 修饰器：

   import bigframes.pandas as bpd
   
   # Set BigQuery DataFrames options
   bpd.options.bigquery.project = your_gcp_project_id
   bpd.options.bigquery.location = "us"
   
   # BigQuery DataFrames gives you the ability to turn your custom scalar
   # functions into a BigQuery remote function. It requires the GCP project to
   # be set up appropriately and the user having sufficient privileges to use
   # them. One can find more details about the usage and the requirements via
   # `help` command.
   help(bpd.remote_function)
   
   # Read a table and inspect the column of interest.
   df = bpd.read_gbq("bigquery-public-data.ml_datasets.penguins")
   df["body_mass_g"].head(10)
   
   # Define a custom function, and specify the intent to turn it into a remote
   # function. It requires a BigQuery connection. If the connection is not
   # already created, BigQuery DataFrames will attempt to create one assuming
   # the necessary APIs and IAM permissions are setup in the project. In our
   # examples we will be letting the default connection `bigframes-default-connection`
   # be used. We will also set `reuse=False` to make sure we don't
   # step over someone else creating remote function in the same project from
   # the exact same source code at the same time. Let's try a `pandas`-like use
   # case in which we want to apply a user defined scalar function to every
   # value in a `Series`, more specifically bucketize the `body_mass_g` value
   # of the penguins, which is a real number, into a category, which is a
   # string.
   @bpd.remote_function(
       float,
       str,
       reuse=False,
   )
   def get_bucket(num: float) -> str:
       if not num:
           return "NA"
       boundary = 4000
       return "at_or_above_4000" if num >= boundary else "below_4000"
   
   # Then we can apply the remote function on the `Series`` of interest via
   # `apply` API and store the result in a new column in the DataFrame.
   df = df.assign(body_mass_bucket=df["body_mass_g"].apply(get_bucket))
   
   # This will add a new column `body_mass_bucket` in the DataFrame. You can
   # preview the original value and the bucketized value side by side.
   df[["body_mass_g", "body_mass_bucket"]].head(10)
   
   # The above operation was possible by doing all the computation on the
   # cloud. For that, there is a google cloud function deployed by serializing
   # the user code, and a BigQuery remote function created to call the cloud
   # function via the latter's http endpoint on the data in the DataFrame.
   
   # The BigQuery remote function created to support the BigQuery DataFrames
   # remote function can be located via a property `bigframes_remote_function`
   # set in the remote function object.
   print(f"Created BQ remote function: {get_bucket.bigframes_remote_function}")
   
   # The cloud function can be located via another property
   # `bigframes_cloud_function` set in the remote function object.
   print(f"Created cloud function: {get_bucket.bigframes_cloud_function}")
   
   # Warning: The deployed cloud function may be visible to other users with
   # sufficient privilege in the project, so the user should be careful about
   # having any sensitive data in the code that will be deployed as a remote
   # function.
   
   # Let's continue trying other potential use cases of remote functions. Let's
   # say we consider the `species`, `island` and `sex` of the penguins
   # sensitive information and want to redact that by replacing with their hash
   # code instead. Let's define another scalar custom function and decorate it
   # as a remote function. The custom function in this example has external
   # package dependency, which can be specified via `packages` parameter.
   @bpd.remote_function(
       str,
       str,
       reuse=False,
       packages=["cryptography"],
   )
   def get_hash(input: str) -> str:
       from cryptography.fernet import Fernet
   
       # handle missing value
       if input is None:
           input = ""
   
       key = Fernet.generate_key()
       f = Fernet(key)
       return f.encrypt(input.encode()).decode()
   
   # We can use this remote function in another `pandas`-like API `map` that
   # can be applied on a DataFrame
   df_redacted = df[["species", "island", "sex"]].map(get_hash)
   df_redacted.head(10)