Sign in

1. Products
2. Google Cloud Platform
3. Google BigQuery

Google BigQuery

Feedback on this document

Avro format

BigQuery expresses Avro formatted data in the following ways:

• The resulting export files are Avro container files.
• Each BigQuery row is represented as an Avro Record. Nested data is represented by nested Record objects.
• REQUIRED fields are represented as the corresponding Avro types. For example, a BigQuery INTEGER type maps to an Avro LONG type.
• NULLABLE fields are represented as an Avro Union of the corresponding type and "null".
• REPEATED fields are represented as Avro arrays.
• TIMESTAMP data types are represented as Avro LONG types.

Back to top

Extract configuration example

The following code example shows the configuration of a job that exports data to single newline-delimited JSON file.

jobData = {
    'projectId': projectId,
    'configuration': {
      'extract': {
        'sourceTable': {
           'projectId': projectId,
           'datasetId': datasetId,
           'tableId': tableId
         },
        'destinationUris': ['gs://<bucket>/<file>'],
        'destinationFormat': 'NEWLINE_DELIMITED_JSON'
       }
     }
   }

The destinationUris property indicates the location(s) and file name(s) where BigQuery should export your files to. Usually, you'll pass a single value so that BigQuery exports to a single file, but you can alternately pass one or more wildcard URIs. For more information on what to specify for the destinationUris property, see exporting data into one or more files .

Back to top

Exporting data into one or more files

The destinationUris property indicates the location(s) and file name(s) where BigQuery should export your files to.

BigQuery supports a single wildcard operator (*) in each URI. Using the wildcard operator instructs BigQuery to create multiple sharded files based on the supplied pattern. The wildcard operator is replaced with a file number (starting at 0), left-padded to 12 digits. For example, 000000000001 for the second file.

The following table describes several possible options for the destinationUris property:

gs://my-bucket/file-name.json

gs://my-bucket/file-name-000000000000.json
gs://my-bucket/file-name-000000000001.json
gs://my-bucket/file-name-000000000002.json
...

['gs://my-bucket/file-name-1-*.json',
'gs://my-bucket/file-name-2-*.json', 
'gs://my-bucket/file-name-3-*.json']

gs://my-bucket/file-name-1-000000000000.json
gs://my-bucket/file-name-1-000000000001.json
...
gs://my-bucket/file-name-1-000000000080.json
gs://my-bucket/file-name-2-000000000000.json
gs://my-bucket/file-name-2-000000000001.json
...
gs://my-bucket/file-name-2-000000000080.json
gs://my-bucket/file-name-3-000000000000.json
gs://my-bucket/file-name-3-000000000001.json
...
gs://my-bucket/file-name-3-000000000080.json

Back to top

Python code example

The following code example shows how to export data in CSV format, in Python.

import sys
from apiclient.discovery import build
from oauth2client.file import Storage
from oauth2client.client import AccessTokenRefreshError
from oauth2client.client import OAuth2WebServerFlow
from oauth2client.tools import run
from apiclient.errors import HttpError
import httplib2

FLOW = OAuth2WebServerFlow(
    client_id='xxxxxxx.apps.googleusercontent.com',
    client_secret='shhhhhhhhhhhh',
    scope='https://www.googleapis.com/auth/bigquery',
    user_agent='my-program-name/1.0')

def exportTable(http, service):
  projectId = raw_input("Choose your project ID: ")
  datasetId = raw_input("Choose a dataset ID: ")
  tableId = raw_input("Choose a table name to copy: ")

  url = "https://www.googleapis.com/bigquery/v2/projects/" + projectId + "/jobs"

  jobCollection = service.jobs()
  jobData = {
    'projectId': projectId,
    'configuration': {
      'extract': {
        'sourceTable': {
           'projectId': projectId,
           'datasetId': datasetId,
           'tableId': tableId
         },
        'destinationUris': ['gs://<bucket>/<file>'],
       }
     }
   }
  insertJob = jobCollection.insert(projectId=projectId, body=jobData).execute()
  import time
  while True:
    status =jobCollection.get(projectId=projectId, jobId=insertJob['jobReference']['jobId']).execute()
    print status
    if 'DONE' == status['status']['state']:
      print "Done exporting!"
      return
    print 'Waiting for export to complete..'
    time.sleep(10)

def main(argv):
  # If the credentials don't exist or are invalid, run the native client
  # auth flow. The Storage object will ensure that if successful the good
  # credentials will get written back to a file.
  storage = Storage('bigquery2.dat') # Choose a file name to store the credentials.
  credentials = storage.get()
  if credentials is None or credentials.invalid:
    credentials = run(FLOW, storage)

  # Create an httplib2.Http object to handle our HTTP requests and authorize it
  # with our good credentials.
  http = httplib2.Http()
  http = credentials.authorize(http)

  service = build('bigquery','v2', http=http)
  exportTable(http, service)

if __name__ == '__main__':
  main(sys.argv)

Back to top