10.11 Google BigQuery (Experimental)

10.11.3 Authentication

Authentication to the Google BigQuery service is via OAuth 2.0. Is possible to authenticate with either user account credentials or service account credentials.

Authenticating with user account credentials is as simple as following the prompts in a browser window which will be automatically opened for you. You will be authenticated to the specified BigQuery account using the product name pandas GBQ. It is only possible on local host. The remote authentication using user account credentials is not currently supported in Pandas. Additional information on the authentication mechanism can be found here.

Authentication with service account credentials is possible via the ‘private_key’ parameter. This method is particularly useful when working on remote servers (eg. jupyter iPython notebook on remote host). Additional information on service accounts can be found here.

You will need to install an additional dependency: oauth2client.

Authentication via application default credentials is also possible. This is only valid if the parameter private_key is not provided. This method also requires that the credentials can be fetched from the environment the code is running in. Otherwise, the OAuth2 client-side authentication is used. Additional information on application default credentials.

10.11.4 Querying

Suppose you want to load all data from an existing BigQuery table : test_dataset.test_table into a DataFrame using the read_gbq() function.

# Insert your BigQuery Project ID Here
# Can be found in the Google web console
projectid = "xxxxxxxx"

data_frame = pd.read_gbq('SELECT * FROM test_dataset.test_table', projectid)

You can define which column from BigQuery to use as an index in the destination DataFrame as well as a preferred column order as follows:

data_frame = pd.read_gbq('SELECT * FROM test_dataset.test_table',
                          index_col='index_column_name',
                          col_order=['col1', 'col2', 'col3'], projectid)

10.11.5 Writing DataFrames

Assume we want to write a DataFrame df into a BigQuery table using to_gbq().

In [1]: df = pd.DataFrame({'my_string': list('abc'),
   ...:                    'my_int64': list(range(1, 4)),
   ...:                    'my_float64': np.arange(4.0, 7.0),
   ...:                    'my_bool1': [True, False, True],
   ...:                    'my_bool2': [False, True, False],
   ...:                    'my_dates': pd.date_range('now', periods=3)})
   ...: 

In [2]: df
Out[2]: 
  my_bool1 my_bool2                   my_dates  my_float64  my_int64 my_string
0     True    False 2016-09-30 13:51:25.040464         4.0         1         a
1    False     True 2016-10-01 13:51:25.040464         5.0         2         b
2     True    False 2016-10-02 13:51:25.040464         6.0         3         c

In [3]: df.dtypes
Out[3]: 
my_bool1                bool
my_bool2                bool
my_dates      datetime64[ns]
my_float64           float64
my_int64               int64
my_string             object
dtype: object

df.to_gbq('my_dataset.my_table', projectid)

The if_exists argument can be used to dictate whether to 'fail', 'replace' or 'append' if the destination table already exists. The default value is 'fail'.

For example, assume that if_exists is set to 'fail'. The following snippet will raise a TableCreationError if the destination table already exists.

df.to_gbq('my_dataset.my_table', projectid, if_exists='fail')

Writing large DataFrames can result in errors due to size limitations being exceeded. This can be avoided by setting the chunksize argument when calling to_gbq(). For example, the following writes df to a BigQuery table in batches of 10000 rows at a time:

df.to_gbq('my_dataset.my_table', projectid, chunksize=10000)

You can also see the progress of your post via the verbose flag which defaults to True. For example:

In [8]: df.to_gbq('my_dataset.my_table', projectid, chunksize=10000, verbose=True)

        Streaming Insert is 10% Complete
        Streaming Insert is 20% Complete
        Streaming Insert is 30% Complete
        Streaming Insert is 40% Complete
        Streaming Insert is 50% Complete
        Streaming Insert is 60% Complete
        Streaming Insert is 70% Complete
        Streaming Insert is 80% Complete
        Streaming Insert is 90% Complete
        Streaming Insert is 100% Complete

10.11.6 Creating BigQuery Tables

As of 0.15.2, the gbq module has a function generate_bq_schema() which will produce the dictionary representation schema of the specified pandas DataFrame.

In [10]: gbq.generate_bq_schema(df, default_type='STRING')

Out[10]: {'fields': [{'name': 'my_bool1', 'type': 'BOOLEAN'},
         {'name': 'my_bool2', 'type': 'BOOLEAN'},
         {'name': 'my_dates', 'type': 'TIMESTAMP'},
         {'name': 'my_float64', 'type': 'FLOAT'},
         {'name': 'my_int64', 'type': 'INTEGER'},
         {'name': 'my_string', 'type': 'STRING'}]}