快速入門導覽課程:使用用戶端程式庫

本頁面說明如何透過 Google Cloud 用戶端程式庫,以您偏好的程式設計語言開始使用 Google BigQuery API。

事前準備

  1. 登入您的 Google 帳戶。

    如果您沒有帳戶,請申請新帳戶

  2. 選取或建立 Google Cloud Platform 專案。

    前往「Manage resources」(管理資源) 頁面

  3. 啟用Google BigQuery API。

    啟用 API

  4. 設定驗證:
    1. 在 GCP 主控台中,前往「Create service account key」(建立服務帳戶金鑰) 頁面。

      前往「Create Service Account Key」(建立服務帳戶金鑰) 頁面
    2. 從 [Service account] (服務帳戶) 清單中選取 [New service account] (新增服務帳戶)
    3. 在 [Service account name] (服務帳戶名稱) 欄位中輸入一個名稱。
    4. 從 [Role] (角色) 清單中,選取 [Project] (專案) > [Owner] (擁有者)

      附註:「Role」(角色) 欄位會授權服務帳戶存取資源。以後您可以使用 GCP 主控台查看及變更這個欄位。如果您要開發正式版應用程式,請指定比 [Project] (專案) > [Owner] (擁有者) 更精細的權限。詳情請參閱為服務帳戶授予角色一文。
    5. 點選 [建立]。一個包含您金鑰的 JSON 檔案會下載到電腦中。
  5. 將環境變數 GOOGLE_APPLICATION_CREDENTIALS 設為包含服務帳戶金鑰的 JSON 檔案路徑。 此變數僅適用於您目前的殼層工作階段,所以如果您開啟新的工作階段,請再次設定變數。

安裝用戶端程式庫

C#

如要進一步瞭解如何設定 C# 開發環境,請參閱 C# 開發環境設定指南
Install-Package Google.Cloud.BigQuery.V2 -Pre

Go

go get -u cloud.google.com/go/bigquery

Java

如要進一步瞭解如何設定 Java 開發環境,請參閱 Java 開發環境設定指南。 如果您是使用 Maven,請在 pom.xml 檔案中新增以下指令:
<dependency>
  <groupId>com.google.cloud</groupId>
  <artifactId>google-cloud-bigquery</artifactId>
  <version>1.75.0</version>
</dependency>
如果您是使用 Gradle,請在相依元件中新增以下指令:
compile 'com.google.cloud:google-cloud-bigquery:1.75.0'
如果您是使用 SBT,請在相依元件中新增以下指令:
libraryDependencies += "com.google.cloud" % "google-cloud-bigquery" % "1.75.0"

如果您使用 IntelliJ 或 Eclipse,可以使用以下 IDE 外掛程式將用戶端程式庫加入專案中:

外掛程式還提供其他功能,例如服務帳戶適用的金鑰管理。詳情請參閱各外掛程式的說明文件。

Node.js

如要進一步瞭解如何設定 Node.js 開發環境,請參閱 Node.js 開發環境設定指南
npm install --save @google-cloud/bigquery

PHP

composer require google/cloud-bigquery

Python

如要進一步瞭解如何設定 Python 開發環境,請參閱 Python 開發環境設定指南
pip install --upgrade google-cloud-bigquery

Ruby

如要進一步瞭解如何設定 Ruby 開發環境,請參閱 Ruby 開發環境設定指南
gem install google-cloud-bigquery

匯入程式庫

C#

詳情請參閱 BigQuery C# API 參考說明文件


using System;
using Google.Cloud.BigQuery.V2;

Go

詳情請參閱 BigQuery Go API 參考說明文件

import (
	"context"
	"fmt"
	"io"
	"log"
	"os"

	"cloud.google.com/go/bigquery"
	"google.golang.org/api/iterator"
)

Java

詳情請參閱 BigQuery Java API 參考說明文件


import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.FieldValueList;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobId;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.QueryResponse;
import com.google.cloud.bigquery.TableResult;
import java.util.UUID;

Node.js

詳情請參閱 BigQuery Node.js API 參考說明文件

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');

PHP

詳情請參閱 BigQuery PHP API 參考說明文件

use Google\Cloud\BigQuery\BigQueryClient;

Python

詳情請參閱 BigQuery Python API 參考說明文件

from google.cloud import bigquery

Ruby

詳情請參閱 BigQuery Ruby API 參考說明文件

require "google/cloud/bigquery"

初始化 BigQuery 用戶端

C#

請使用 BigQueryClient.Create() 函式建立 BigQuery 用戶端。

string projectId = "YOUR-PROJECT-ID";
var client = BigQueryClient.Create(projectId);

Go

請使用 bigquery.NewClient() 函式建立 BigQuery 用戶端。

ctx := context.Background()

client, err := bigquery.NewClient(ctx, proj)
if err != nil {
	return nil, err
}

Java

請使用 BigQueryOptions.getDefaultInstance() 函式來利用預設的驗證選項,並使用 BigQueryOptions.getService() 函式建立 BigQuery 用戶端。

BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

Node.js

請將 BigQuery 類別實例化來建立 BigQuery 用戶端。

// Create a client
const bigqueryClient = new BigQuery();

PHP

請將 BigQueryClient 類別實例化來建立 BigQuery 用戶端。

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);

Python

請將 bigquery.Client 類別實例化來建立 BigQuery 用戶端。

client = bigquery.Client()

Ruby

請使用 Google::Cloud::Bigquery.new 函式建立 BigQuery 用戶端。

# This uses Application Default Credentials to authenticate.
# @see https://cloud.google.com/bigquery/docs/authentication/getting-started
bigquery = Google::Cloud::Bigquery.new

執行查詢

查詢 Stack Overflow 公開資料集,找出瀏覽次數最多且標有 google-bigquery 的問題。

SELECT
  CONCAT(
    'https://stackoverflow.com/questions/',
    CAST(id as STRING)) as url,
  view_count
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE tags like '%google-bigquery%'
ORDER BY view_count DESC
LIMIT 10

這個查詢使用標準 SQL 語法,相關說明請參閱查詢參考資料指南。用戶端程式庫預設使用標準 SQL 語法。如要變更 SQL 方言,請參閱切換 SQL 方言相關說明。

執行查詢

使用驗證過的 BigQuery 用戶端執行查詢。

C#

請定義查詢字串,並使用 client.ExecuteQuery() 函式來提交查詢及取得結果。

string query = @"SELECT
    CONCAT(
        'https://stackoverflow.com/questions/',
        CAST(id as STRING)) as url, view_count
    FROM `bigquery-public-data.stackoverflow.posts_questions`
    WHERE tags like '%google-bigquery%'
    ORDER BY view_count DESC
    LIMIT 10";
var result = client.ExecuteQuery(query, parameters: null);

Go

請使用 bigquery.Query() 函式定義查詢,並使用 Query.Read() 函式來提交查詢及取得結果。

query := client.Query(
	`SELECT
		CONCAT(
			'https://stackoverflow.com/questions/',
			CAST(id as STRING)) as url,
		view_count
	FROM ` + "`bigquery-public-data.stackoverflow.posts_questions`" + `
	WHERE tags like '%google-bigquery%'
	ORDER BY view_count DESC
	LIMIT 10;`)
return query.Read(ctx)

Java

請透過 QueryJobConfiguration 執行個體定義查詢,並使用 BigQuery.create() 方法啟動查詢工作。

QueryJobConfiguration queryConfig =
    QueryJobConfiguration.newBuilder(
      "SELECT "
          + "CONCAT('https://stackoverflow.com/questions/', CAST(id as STRING)) as url, "
          + "view_count "
          + "FROM `bigquery-public-data.stackoverflow.posts_questions` "
          + "WHERE tags like '%google-bigquery%' "
          + "ORDER BY favorite_count DESC LIMIT 10")
        // Use standard SQL syntax for queries.
        // See: https://cloud.google.com/bigquery/sql-reference/
        .setUseLegacySql(false)
        .build();

// Create a job ID so that we can safely retry.
JobId jobId = JobId.of(UUID.randomUUID().toString());
Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());

// Wait for the query to complete.
queryJob = queryJob.waitFor();

// Check for errors
if (queryJob == null) {
  throw new RuntimeException("Job no longer exists");
} else if (queryJob.getStatus().getError() != null) {
  // You can also look at queryJob.getStatus().getExecutionErrors() for all
  // errors, not just the latest one.
  throw new RuntimeException(queryJob.getStatus().getError().toString());
}

Node.js

請使用 BigQuery.query() 方法啟動查詢。

// The SQL query to run
const sqlQuery = `SELECT
  CONCAT(
    'https://stackoverflow.com/questions/',
    CAST(id as STRING)) as url,
  view_count
  FROM \`bigquery-public-data.stackoverflow.posts_questions\`
  WHERE tags like '%google-bigquery%'
  ORDER BY view_count DESC
  LIMIT 10`;

const options = {
  query: sqlQuery,
  // Location must match that of the dataset(s) referenced in the query.
  location: 'US',
};

// Run the query
const [rows] = await bigqueryClient.query(options);

PHP

請建立查詢設定並使用 BigQueryClient.startQuery() 方法來啟動查詢。

$query = <<<ENDSQL
SELECT
  CONCAT(
    'https://stackoverflow.com/questions/',
    CAST(id as STRING)) as url,
  view_count
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE tags like '%google-bigquery%'
ORDER BY view_count DESC
LIMIT 10;
ENDSQL;
$queryJobConfig = $bigQuery->query($query);
$queryResults = $bigQuery->runQuery($queryJobConfig);

Python

請使用 Client.query() 方法來啟動查詢。

query_job = client.query("""
    SELECT
      CONCAT(
        'https://stackoverflow.com/questions/',
        CAST(id as STRING)) as url,
      view_count
    FROM `bigquery-public-data.stackoverflow.posts_questions`
    WHERE tags like '%google-bigquery%'
    ORDER BY view_count DESC
    LIMIT 10""")

results = query_job.result()  # Waits for job to complete.

Ruby

請使用 Google::Cloud::Bigquery::Project.query 函式來啟動查詢並等候結果。

sql     = "SELECT " +
          "CONCAT('https://stackoverflow.com/questions/', " +
          "       CAST(id as STRING)) as url, view_count " +
          "FROM `bigquery-public-data.stackoverflow.posts_questions` " +
          "WHERE tags like '%google-bigquery%' " +
          "ORDER BY view_count DESC LIMIT 10"
results = bigquery.query sql

如需更多執行 BigQuery 查詢的範例,請參閱下列文章:

顯示查詢結果

顯示查詢結果。

C#

Console.Write("\nQuery Results:\n------------\n");
foreach (var row in result)
{
    Console.WriteLine($"{row["url"]}: {row["view_count"]} views");
}

Go

請使用 RowIterator.Nextt() 函式,在 struct 指標中載入每一個資料列。

type StackOverflowRow struct {
	URL       string `bigquery:"url"`
	ViewCount int64  `bigquery:"view_count"`
}

// printResults prints results from a query to the Stack Overflow public dataset.
func printResults(w io.Writer, iter *bigquery.RowIterator) error {
	for {
		var row StackOverflowRow
		err := iter.Next(&row)
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}

		fmt.Fprintf(w, "url: %s views: %d\n", row.URL, row.ViewCount)
	}
}

Java

QueryResponse 進行疊代作業,以便在結果中取得所有資料列。疊代器會自動處理分頁。每一個 FieldList 都會以數字索引或資料欄名稱顯示資料欄。

// Get the results.
TableResult result = queryJob.getQueryResults();

// Print all pages of the results.
for (FieldValueList row : result.iterateAll()) {
  String url = row.get("url").getStringValue();
  long viewCount = row.get("view_count").getLongValue();
  System.out.printf("url: %s views: %d%n", url, viewCount);
}

Node.js

查詢結果會以資料列清單形式傳回,其中每個資料列都是一個字典。

console.log('Query Results:');
rows.forEach(row => {
  const url = row['url'];
  const viewCount = row['view_count'];
  console.log(`url: ${url}, ${viewCount} views`);
});

PHP

呼叫 Job.queryResults() 方法以等待查詢作業完成。查詢結果中的每個資料列都是關聯陣列。

if ($queryResults->isComplete()) {
    $i = 0;
    $rows = $queryResults->rows();
    foreach ($rows as $row) {
        printf('--- Row %s ---' . PHP_EOL, ++$i);
        printf('url: %s, %s views' . PHP_EOL, $row['url'], $row['view_count']);
    }
    printf('Found %s row(s)' . PHP_EOL, $i);
} else {
    throw new Exception('The query failed to complete');
}

Python

RowIterator 進行疊代作業,以便取得結果中的所有資料列。疊代器會自動處理分頁。每一個 Row 都會以數字索引、資料欄名稱或 Python 屬性顯示資料欄。

for row in results:
    print("{} : {} views".format(row.url, row.view_count))

Ruby

Google::Cloud::Bigquery::Data 類別會以雜湊值顯示各個資料列。

results.each do |row|
  puts "#{row[:url]}: #{row[:view_count]} views"
end

進一步瞭解如何在 BigQuery 中處理資料列:

完整原始碼

以下是範例的完整原始碼。

C#


using System;
using Google.Cloud.BigQuery.V2;

namespace GoogleCloudSamples
{
    public class Program
    {
        public static void Main(string[] args)
        {
            string projectId = "YOUR-PROJECT-ID";
            var client = BigQueryClient.Create(projectId);
            string query = @"SELECT
                CONCAT(
                    'https://stackoverflow.com/questions/',
                    CAST(id as STRING)) as url, view_count
                FROM `bigquery-public-data.stackoverflow.posts_questions`
                WHERE tags like '%google-bigquery%'
                ORDER BY view_count DESC
                LIMIT 10";
            var result = client.ExecuteQuery(query, parameters: null);
            Console.Write("\nQuery Results:\n------------\n");
            foreach (var row in result)
            {
                Console.WriteLine($"{row["url"]}: {row["view_count"]} views");
            }
        }
    }
}

Go

import (
	"context"
	"fmt"
	"io"
	"log"
	"os"

	"cloud.google.com/go/bigquery"
	"google.golang.org/api/iterator"
)

func main() {
	proj := os.Getenv("GOOGLE_CLOUD_PROJECT")
	if proj == "" {
		fmt.Println("GOOGLE_CLOUD_PROJECT environment variable must be set.")
		os.Exit(1)
	}

	rows, err := query(proj)
	if err != nil {
		log.Fatal(err)
	}
	if err := printResults(os.Stdout, rows); err != nil {
		log.Fatal(err)
	}
}

// query returns a slice of the results of a query.
func query(proj string) (*bigquery.RowIterator, error) {
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, proj)
	if err != nil {
		return nil, err
	}

	query := client.Query(
		`SELECT
			CONCAT(
				'https://stackoverflow.com/questions/',
				CAST(id as STRING)) as url,
			view_count
		FROM ` + "`bigquery-public-data.stackoverflow.posts_questions`" + `
		WHERE tags like '%google-bigquery%'
		ORDER BY view_count DESC
		LIMIT 10;`)
	return query.Read(ctx)
}

type StackOverflowRow struct {
	URL       string `bigquery:"url"`
	ViewCount int64  `bigquery:"view_count"`
}

// printResults prints results from a query to the Stack Overflow public dataset.
func printResults(w io.Writer, iter *bigquery.RowIterator) error {
	for {
		var row StackOverflowRow
		err := iter.Next(&row)
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}

		fmt.Fprintf(w, "url: %s views: %d\n", row.URL, row.ViewCount)
	}
}

Java


import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.FieldValueList;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobId;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.QueryResponse;
import com.google.cloud.bigquery.TableResult;
import java.util.UUID;

public class SimpleApp {
  public static void main(String... args) throws Exception {
    BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
    QueryJobConfiguration queryConfig =
        QueryJobConfiguration.newBuilder(
          "SELECT "
              + "CONCAT('https://stackoverflow.com/questions/', CAST(id as STRING)) as url, "
              + "view_count "
              + "FROM `bigquery-public-data.stackoverflow.posts_questions` "
              + "WHERE tags like '%google-bigquery%' "
              + "ORDER BY favorite_count DESC LIMIT 10")
            // Use standard SQL syntax for queries.
            // See: https://cloud.google.com/bigquery/sql-reference/
            .setUseLegacySql(false)
            .build();

    // Create a job ID so that we can safely retry.
    JobId jobId = JobId.of(UUID.randomUUID().toString());
    Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());

    // Wait for the query to complete.
    queryJob = queryJob.waitFor();

    // Check for errors
    if (queryJob == null) {
      throw new RuntimeException("Job no longer exists");
    } else if (queryJob.getStatus().getError() != null) {
      // You can also look at queryJob.getStatus().getExecutionErrors() for all
      // errors, not just the latest one.
      throw new RuntimeException(queryJob.getStatus().getError().toString());
    }

    // Get the results.
    TableResult result = queryJob.getQueryResults();

    // Print all pages of the results.
    for (FieldValueList row : result.iterateAll()) {
      String url = row.get("url").getStringValue();
      long viewCount = row.get("view_count").getLongValue();
      System.out.printf("url: %s views: %d%n", url, viewCount);
    }
  }
}

Node.js

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');

async function queryStackOverflow() {
  // Queries a public Stack Overflow dataset.

  // Create a client
  const bigqueryClient = new BigQuery();

  // The SQL query to run
  const sqlQuery = `SELECT
    CONCAT(
      'https://stackoverflow.com/questions/',
      CAST(id as STRING)) as url,
    view_count
    FROM \`bigquery-public-data.stackoverflow.posts_questions\`
    WHERE tags like '%google-bigquery%'
    ORDER BY view_count DESC
    LIMIT 10`;

  const options = {
    query: sqlQuery,
    // Location must match that of the dataset(s) referenced in the query.
    location: 'US',
  };

  // Run the query
  const [rows] = await bigqueryClient.query(options);

  console.log('Query Results:');
  rows.forEach(row => {
    const url = row['url'];
    const viewCount = row['view_count'];
    console.log(`url: ${url}, ${viewCount} views`);
  });
}
queryStackOverflow();

PHP

require __DIR__ . '/vendor/autoload.php';

use Google\Cloud\BigQuery\BigQueryClient;

// get the project ID as the first argument
if (2 != count($argv)) {
    die("Usage: php stackoverflow.php YOUR_PROJECT_ID\n");
}

$projectId = $argv[1];

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$query = <<<ENDSQL
SELECT
  CONCAT(
    'https://stackoverflow.com/questions/',
    CAST(id as STRING)) as url,
  view_count
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE tags like '%google-bigquery%'
ORDER BY view_count DESC
LIMIT 10;
ENDSQL;
$queryJobConfig = $bigQuery->query($query);
$queryResults = $bigQuery->runQuery($queryJobConfig);

if ($queryResults->isComplete()) {
    $i = 0;
    $rows = $queryResults->rows();
    foreach ($rows as $row) {
        printf('--- Row %s ---' . PHP_EOL, ++$i);
        printf('url: %s, %s views' . PHP_EOL, $row['url'], $row['view_count']);
    }
    printf('Found %s row(s)' . PHP_EOL, $i);
} else {
    throw new Exception('The query failed to complete');
}

Python

from google.cloud import bigquery

def query_stackoverflow():
    client = bigquery.Client()
    query_job = client.query("""
        SELECT
          CONCAT(
            'https://stackoverflow.com/questions/',
            CAST(id as STRING)) as url,
          view_count
        FROM `bigquery-public-data.stackoverflow.posts_questions`
        WHERE tags like '%google-bigquery%'
        ORDER BY view_count DESC
        LIMIT 10""")

    results = query_job.result()  # Waits for job to complete.

    for row in results:
        print("{} : {} views".format(row.url, row.view_count))

if __name__ == '__main__':
    query_stackoverflow()

Ruby

require "google/cloud/bigquery"

# This uses Application Default Credentials to authenticate.
# @see https://cloud.google.com/bigquery/docs/authentication/getting-started
bigquery = Google::Cloud::Bigquery.new

sql     = "SELECT " +
          "CONCAT('https://stackoverflow.com/questions/', " +
          "       CAST(id as STRING)) as url, view_count " +
          "FROM `bigquery-public-data.stackoverflow.posts_questions` " +
          "WHERE tags like '%google-bigquery%' " +
          "ORDER BY view_count DESC LIMIT 10"
results = bigquery.query sql

results.each do |row|
  puts "#{row[:url]}: #{row[:view_count]} views"
end

恭喜!您已將第一個要求傳送至 BigQuery。

後續步驟

進一步瞭解 Google BigQuery API 用戶端程式庫

本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁
需要協助嗎?請前往我們的支援網頁