Writing Job Results into Google BigQuery

This article explains how to write job results directly to your Google BigQuery.

Table of Contents

Prerequisites

  • Basic knowledge of Treasure Data, including the toolbelt.
  • A Google Cloud Platform account

Get Google Cloud Platform Credential

To use this feature, you need 2 information: your Project ID, and JSON Credential.

JSON Credential

The integration with Google BigQuery is based on server-to-server API authentication. First, please visit your Google Developer Console, and click Credentials under APIs & auth at the left menu. Then, please select Service account.



Then, please select JSON based key type which is recommended by Google. The key will be downloaded automatically by the browser.



Project ID

Please visit your Google Developer Console, and click Home at the left menu. It will show you your Project ID.



Usage

Create Dataset and Table on BigQuery

First, please create your Dataset and Table from your BigQuery console.

Use from Web Console

Next, please visit Treasure Data console, go to query editor, click Add for Result Export, and select BigQuery. Please fill out all the information below.



After that, please write the query. The result of query needs to be matched with pre-defined schema on BigQuery. Once you execute, Treasure Data query result will be automatically imported into Google BigQuery.



Use from CLI

The following command is allow to set a sheduled query with Result Output to Bigquery.

Untitled-3
Please designate your json_key and escape newline with backslash.

For example,

$ td sched:create scheduled_bigquery "10 6 14 12 *" \
-d dataconnector_db "SELECT id,account,purchase,comment,time FROM data_connectors" \
-r '{"type":"bigquery","project":"YOUR_PROJECT","dataset":"YOUR_DB","table":"YOUR_TABLE","auto_create_table":true,"max_bad_records":0,"ignore_unknown_values":true,"allow_quoted_newlines":true,"schema_file":"[{\"name\": \"id\", \"type\": \"INTEGER\"}, {\"name\": \"account\", \"type\": \"STRING\"},{\"name\": \"purchase\", \"type\": \"STRING\"}, {\"name\": \"comment\", \"type\": \"STRING\", \"mode\": \"REQUIRED\"}, {\"name\": \"time\", \"type\": \"TIMESTAMP\", \"mode\": \"REQUIRED\"}]", "json_keyfile":"{\"private_key_id\": \"ABDE\", \"private_key\": \"-----BEGIN PRIVATE KEY-----\\nABCDE\\nABCDE\\nABCDE\\n-----END PRIVATE KEY-----\\n\", \"client_email\": \"ABCDE.gserviceaccount.com\", \"client_id\": \"ABCDE.apps.googleusercontent.com\", \"type\": \"service_account\"}"}'

Last modified: Feb 10 2016 08:37:37 UTC

If this article is incorrect or outdated, or omits critical information, please let us know. For all other issues, please see our support channels.