使用 API 建立簡易應用程式

本教學課程說明如何使用 Google BigQuery 用戶端程式庫,透過 C#、Go、Java、Node.js、PHP、Python 或 Ruby 打造簡單的指令列應用程式。該應用程式會查詢 Stack Overflow 公開資料集並顯示結果。

目標

使用 BigQuery 用戶端程式庫執行以下作業:

  • 驗證 BigQuery API
  • 執行查詢
  • 讀取查詢結果

成本

本教學課程中的程式碼範例所執行的查詢會處理約 500 MB 的資料。如要進一步瞭解 BigQuery 的定價,請參閱定價參考資料

事前準備

  1. 瞭解 BigQuery 的基本概念和術語。

    試試 BigQuery 快速入門,熟悉常見的 BigQuery 工作。

  2. 已啟用 BigQuery API 的專案。

    使用 BigQuery 的應用程式必須與已啟用 BigQuery API 的 Google Cloud Platform 主控台專案有所關聯。

  3. 本機開發環境。

    C#

    如要設定本機開發環境,請參閱設定 .NET 開發環境

    Go

    請確定您已安裝最新版本的 Go

    Java

    請確認您已安裝最新版本的 MavenGradle

    Node.js

    如要設定本機開發環境,請參閱設定 Node.js 開發環境

    PHP

    請確認您已安裝最新版本的 PHPComposer

    Python

    如要設定本機開發環境,請參閱設定 Python 開發環境

    Ruby

    請確認您已安裝最新版本的 RubyBundler

下載程式碼範例

請下載指令列應用程式範例的程式碼,然後前往應用程式目錄:

  1. 將範例存放區複製到本機電腦中。

    C#

    git clone https://github.com/GoogleCloudPlatform/dotnet-docs-samples

    您也可以透過 zip 檔案的格式下載範例,然後再解壓縮該檔案。

    Go

    go get -u -d github.com/GoogleCloudPlatform/golang-samples/bigquery/simpleapp

    Java

    git clone https://github.com/GoogleCloudPlatform/java-docs-samples

    您也可以透過 zip 檔案的格式下載範例,然後再解壓縮該檔案。

    Node.js

    git clone https://github.com/googleapis/nodejs-bigquery.git

    您也可以透過 zip 檔案的格式下載範例,然後再解壓縮該檔案。

    PHP

    git clone https://github.com/GoogleCloudPlatform/php-docs-samples

    您也可以透過 zip 檔案的格式下載範例,然後再解壓縮該檔案。

    Python

    git clone https://github.com/GoogleCloudPlatform/python-docs-samples

    您也可以透過 zip 檔案的格式下載範例,然後再解壓縮該檔案。

    Ruby

    git clone https://github.com/GoogleCloudPlatform/ruby-docs-samples

    您也可以透過 zip 檔案的格式下載範例,然後再解壓縮該檔案。

  2. 變更為包含程式碼範例的目錄:

    C#

    按兩下 dotnet-docs-samples\bigquery\api\BigquerySample.sln 以開啟 Visual Studio 2017。

    Go

    cd $GOPATH/src/github.com/GoogleCloudPlatform/golang-samples/bigquery/simpleapp

    Java

    cd java-docs-samples/bigquery/cloud-client

    Node.js

    cd nodejs-bigquery/samples

    PHP

    cd php-docs-samples/bigquery/stackoverflow

    Python

    cd python-docs-samples/bigquery/cloud-client

    Ruby

    cd ruby-docs-samples/bigquery

設定應用程式依附元件

本範例是使用 Google Cloud 用戶端程式庫來對 BigQuery API 進行呼叫。

C#

如要進一步瞭解如何安裝和建立 BigQuery 用戶端,請參閱 BigQuery 用戶端程式庫

範例的解決方案檔案會使用 NuGet 納入必要依附元件。

匯入 BigQuery 程式庫:

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

Go

如要進一步瞭解如何安裝和建立 BigQuery 用戶端,請參閱 BigQuery 用戶端程式庫

go get 指令除了用來下載範本以外,還會下載任何必要的依附元件。

匯入 BigQuery 程式庫:

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

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

	"golang.org/x/net/context"
)

Java

如要進一步瞭解如何安裝和建立 BigQuery 用戶端,請參閱 BigQuery 用戶端程式庫

pom.xml 會定義要在您透過 Maven 建置範例時下載的依附元件。

<dependency>
  <groupId>com.google.cloud</groupId>
  <artifactId>google-cloud-bigquery</artifactId>
  <version>1.32.0</version>
</dependency>

如果您是使用 Gradle,請執行 gradle init,將 pom.xml 自動轉換成 Gradle 建置檔。

匯入 BigQuery 程式庫:

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 用戶端,請參閱 BigQuery 用戶端程式庫

package.json 檔案會定義範例的依附元件。

{
  "name": "nodejs-docs-samples-bigquery",
  "version": "0.0.1",
  "private": true,
  "license": "Apache-2.0",
  "author": "Google LLC",
  "repository": "googleapis/nodejs-bigquery",
  "engines": {
    "node": ">=4"
  },
  "scripts": {
    "test": "repo-tools test run --cmd npm -- run cover",
    "ava": "ava -T 3m --verbose test/*.test.js system-test/*.test.js",
    "cover": "nyc --reporter=lcov --cache ava -T 3m --verbose test/*.test.js system-test/*.test.js && nyc report"
  },
  "dependencies": {
    "@google-cloud/bigquery": "1.2.0",
    "@google-cloud/storage": "1.5.1",
    "yargs": "10.0.3"
  },
  "devDependencies": {
    "@google-cloud/nodejs-repo-tools": "2.1.3",
    "ava": "0.24.0",
    "nyc": "11.3.0",
    "proxyquire": "1.8.0",
    "sinon": "4.1.3",
    "uuid": "3.1.0"
  }
}

安裝依附元件:

npm install

匯入 BigQuery 程式庫:

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

PHP

如要進一步瞭解如何安裝和建立 BigQuery 用戶端,請參閱 BigQuery 用戶端程式庫

composer.json 檔案會定義範例的依附元件。

{
    "require": {
        "google/cloud-bigquery": "^1.0"
    },
    "require-dev": {
        "phpunit/phpunit": "~4.8"
    }
}

安裝依附元件:

composer install

匯入 BigQuery 程式庫:

use Google\Cloud\BigQuery\BigQueryClient;

Python

如要進一步瞭解如何安裝和建立 BigQuery 用戶端,請參閱 BigQuery 用戶端程式庫

請透過 pip 安裝依附元件。

pip install -r requirements.txt

本範例所使用的依附元件是透過 requirements.txt 檔案定義。

google-cloud-bigquery==0.31.0
google-auth-oauthlib==0.2.0
pytz==2018.3
建議 Conda 使用者在 conda-forge 管道中使用由 Conda 社群參與維護的 BigQuery 套件

匯入 BigQuery 程式庫:

from google.cloud import bigquery

Ruby

如要進一步瞭解如何安裝和建立 BigQuery 用戶端,請參閱 BigQuery 用戶端程式庫

Gemfile 檔案會定義範例的依附元件。

# Copyright 2016 Google, Inc
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

source "https://rubygems.org"

gem "google-cloud-bigquery"

group :test do
  gem "rspec"
  gem "rspec-retry"
  gem "google-cloud-storage"
end

安裝依附元件:

bundle install

匯入 BigQuery 程式庫:

require "google/cloud/bigquery"

建立 BigQuery 服務物件

請使用應用程式預設憑證來進行驗證,以及向 BigQuery 發出已獲授權要求

C#

請使用 BigQueryClient.Create() 函式建立 BigQuery 服務物件。

// By default, the Google.Cloud.BigQuery.V2 library client will authenticate
// using the service account file (created in the Google Developers
// Console) specified by the GOOGLE_APPLICATION_CREDENTIALS
// environment variable. If you are running on
// a Google Compute Engine VM, authentication is completely
// automatic.
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 服務物件。

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = "your-project-id";

// Creates a client
const bigquery = new BigQuery({
  projectId: projectId,
});

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

請參閱驗證指南,瞭解還有哪些方式可驗證 BigQuery API。

執行查詢

請查詢 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#

若要進一步瞭解如何安裝和建立 BigQuery 用戶端,請參閱 BigQuery 用戶端程式庫

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`;

// Query options list: https://cloud.google.com/bigquery/docs/reference/v2/jobs/query
const options = {
  query: sqlQuery,
  useLegacySql: false, // Use standard SQL syntax for queries.
};

// Runs the query
bigquery
  .query(options)
  .then(results => {
    const rows = results[0];
    printResult(rows);
  })
  .catch(err => {
    console.error('ERROR:', err);
  });

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#

若要進一步瞭解如何安裝和建立 BigQuery 用戶端,請參閱 BigQuery 用戶端程式庫

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

Go

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

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.
QueryResponse response = bigquery.getQueryResults(jobId);

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(function(row) {
  let url = row['url'];
  let 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#

若要進一步瞭解如何安裝和建立 BigQuery 用戶端,請參閱 BigQuery 用戶端程式庫

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

namespace GoogleCloudSamples
{
    public class BigquerySample
    {
        const string usage = @"Usage:
BigquerySample <project_id>";

        private static void Main(string[] args)
        {
            string projectId = null;
            if (args.Length == 0)
            {
                Console.WriteLine(usage);
            }
            else
            {
                projectId = args[0];
                // By default, the Google.Cloud.BigQuery.V2 library client will authenticate
                // using the service account file (created in the Google Developers
                // Console) specified by the GOOGLE_APPLICATION_CREDENTIALS
                // environment variable. If you are running on
                // a Google Compute Engine VM, authentication is completely
                // automatic.
                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");
                }
            }
            Console.WriteLine("\nPress any key...");
            Console.ReadKey();
        }
    }
}

Go

如需安裝和建立 BigQuery 用戶端的相關資訊,請參閱 BigQuery 用戶端程式庫

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

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

	"golang.org/x/net/context"
)

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

若要進一步瞭解如何安裝和建立 BigQuery 用戶端,請參閱 BigQuery 用戶端程式庫

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.
    QueryResponse response = bigquery.getQueryResults(jobId);

    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

如要進一步瞭解如何安裝和建立 BigQuery 用戶端,請參閱 BigQuery 用戶端程式庫

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

function queryStackOverflow(projectId) {
  // Imports the Google Cloud client library
  const BigQuery = require('@google-cloud/bigquery');

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const projectId = "your-project-id";

  // Creates a client
  const bigquery = new BigQuery({
    projectId: projectId,
  });

  // 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`;

  // Query options list: https://cloud.google.com/bigquery/docs/reference/v2/jobs/query
  const options = {
    query: sqlQuery,
    useLegacySql: false, // Use standard SQL syntax for queries.
  };

  // Runs the query
  bigquery
    .query(options)
    .then(results => {
      const rows = results[0];
      printResult(rows);
    })
    .catch(err => {
      console.error('ERROR:', err);
    });
}

PHP

如需安裝和建立 BigQuery 用戶端的相關資訊,請參閱 BigQuery 用戶端程式庫

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

若要進一步瞭解如何安裝和建立 BigQuery 用戶端,請參閱 BigQuery 用戶端程式庫

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

如需安裝和建立 BigQuery 用戶端的相關資訊,請參閱 BigQuery 用戶端程式庫

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

相關資源

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

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

這個網頁