使用 DDL 建立處理常式

使用 DDL 查詢建立常式。

程式碼範例

Go

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Go 設定說明操作。詳情請參閱 BigQuery Go API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證作業」。

import (
	"context"
	"fmt"

	"cloud.google.com/go/bigquery"
)

// createRoutineDDL demonstrates creating a new BigQuery UDF using a DDL query.
func createRoutineDDL(projectID, datasetID, routineID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// routineID := "myroutineid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %w", err)
	}
	defer client.Close()

	routineName, err := client.Dataset(datasetID).Routine(routineID).Identifier(bigquery.StandardSQLID)
	if err != nil {
		return fmt.Errorf("couldn't generate identifier: %w", err)
	}

	sql := fmt.Sprintf(`CREATE FUNCTION %s(
        	arr ARRAY<STRUCT<name STRING, val INT64>>
    		) AS (
        	(SELECT SUM(IF(elem.name = "foo",elem.val,null)) FROM UNNEST(arr) AS elem)
    		)`, routineName)

	job, err := client.Query(sql).Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	return nil
}

Java

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Java 設定說明操作。詳情請參閱 BigQuery Java API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證作業」。

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.QueryJobConfiguration;

// Sample to create a routine using DDL
public class CreateRoutineDdl {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetId = "MY_DATASET_ID";
    String routineId = "MY_ROUTINE_ID";
    String sql =
        "CREATE FUNCTION "
            + "`"
            + projectId
            + "."
            + datasetId
            + "."
            + routineId
            + "`"
            + "( arr ARRAY<STRUCT<name STRING, val INT64>>) AS "
            + "( (SELECT SUM(IF(elem.name = \"foo\",elem.val,null)) FROM UNNEST(arr) AS elem))";
    createRoutineDdl(sql);
  }

  public static void createRoutineDdl(String sql) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      QueryJobConfiguration config = QueryJobConfiguration.newBuilder(sql).build();

      // create a routine using query and it will wait to complete job.
      Job job = bigquery.create(JobInfo.of(config));
      job = job.waitFor();
      if (job.isDone()) {
        System.out.println("Routine created successfully");
      } else {
        System.out.println("Routine was not created");
      }
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Routine was not created. \n" + e.toString());
    }
  }
}

後續步驟

如要搜尋及篩選其他 Google Cloud 產品的程式碼範例，請參閱Google Cloud 範例瀏覽工具。