KB484328: Google BigQuery Billing Project

Description 

Billing projects help with distributing the cost of the queries among certain departments. To learn more about this feature, please see the BigQuery Cloud Billing documentation.
 

Billing Project Example

This article assumes that you have two Strategy groups created and each of these groups has at least one user with data import privilege. In this example, I have two groups, Developers and Managers, where the Developers group has a Developer user, while the Managers group has a Manager user associated with it. We are going to create one database instance “GBQ JDBC Database Instance;" two database connections “GBQ JDBC Developers Database connection” and “GBQ JDBC Managers Database connection;” and each of these database connections will use connection string with a different billing project. Lastly, we are going to use Strategy connection mapping to map the Developers group to “GBQ JDBC Developers Database connection” and the Managers group to “GBQ JDBC Managers Database connection." When the Developer user logs in, query the “GBQ JDBC Database Instance," and the billing project set up in “GBQ JDBC Developers Database connection” connection string will be used for billing. On the other hand, when the Manager user logs in, query the “GBQ JDBC Database Instance," and the billing project set up in “GBQ JDBC Managers Database connection” connection string will be used for billing.
 

• If you do not have an OAuth Refresh Token yet, please refer to Generate Refresh Token to Access Google BigQuery.
• Launch Strategy Developer and log in.
  

• Expand the appropriate project source, then the Administration node, and click on the Database Instances node. All your database instances should show in the main window.
  

• Right-click on the Database Instances node > New > Database Instance.
  

• The Database Instances creation wizard displays.
  

• Fill out the Database instance name and Database connection type fields, and click New.
  

• The Database Connections creation wizard displays. Fill out the Database connection name field. This connection will be mapped to one of your groups. Then, click on the New to create a Database login.
  

• The Database Logins creation wizard displays. Fill out the Login ID and Password fields, and click OK. Please note that the Login ID and Password can be any value because the driver will use the OAuth Refresh Token, and not the credentials to access Google BigQuery.
  

• You are returned to the Database Connections wizard screen.
  

• Go to the Advanced tab.
  

• Please note that for this step, you need to have your project ID, which will also be your billing project and OAuth Refresh Token. If you do not have the refresh toke, please refer to the steps in Generate Refresh Token to access Google BigQuery (Linux or Windows) first. Only then, modify the following connection string template with your Project ID (the first billing Project ID), as well as OAuth Refresh Token, and copy the following string into the Additional connection string parameters field. Click OK.
  For example,

  “JDBC;DRIVER={com.Strategy.googlebigquery.jdbc42.Driver};URL={jdbc:bigquery://https://www.googleapis.com/bigquery/v2;ProjectId=PROJECT_ID;OAuthType=2;OAuthRefreshToken=OAUTH_REFRESH_TOKEN;OAuthClientId=977385342095.apps.googleusercontent.com;OAuthClientSecret=wbER7576mc_1YOII0dGk7jEE;Timeout=600;EnableHighThroughputAPI=1};”

  
  

• Click OK.
  

• Click OK.
  

• The database Instances wizard window displays.
  

• Click New and the Database Connections wizard window displays.
  

• Fill out the Database connection name field. This connection will be mapped to the second group. Select the Database Login you created in Step 8.
  

• Switch to the Advanced tab. Modify the following connection string template with your Project ID (the second billing Project ID), as well as OAuth Refresh Token, and copy the following string into the Additional connection string parameters field. Click OK.
  For example,

  “JDBC;DRIVER={com.Strategy.googlebigquery.jdbc42.Driver};URL={jdbc:bigquery://https://www.googleapis.com/bigquery/v2;ProjectId=PROJECT_ID;OAuthType=2;OAuthRefreshToken=OAUTH_REFRESH_TOKEN;OAuthClientId=977385342095.apps.googleusercontent.com;OAuthClientSecret=wbER7576mc_1YOII0dGk7jEE;Timeout=600;EnableHighThroughputAPI=1};”

  
  

• Click OK.
  

• Click OK.
  

• Click OK.
  

• Click OK.
  

• The main window with all your Database Instances, including the new one, display.
  

• Now, we are going to create the connection mapping, so that queries of each group is billed to the appropriate project.
• Right-click on the project, which should have the connection mapping and select Project Configuration.
  

• The Project Configuration window displays.
  

• Expand the Database Instances > Connection mapping.
  

• Right-click inside of the connection mapping window and select the New.
  

• A new connection mapping displays in the list.
  

• Click on the Database Instance selector of the new connection mapping and select the Database Instance you created in Step 6.
  

• The Database Instance field changes.
  

• Click on the User selector of the new connection mapping and the Add members window displays.
  

• Select the first Strategy group you would like to be mapped to the first Database connection created in Step 7, and click OK.
  

• The User field is changed.
  

• Click on the Database Connection selector and choose the first database connection you created in Step 7.
  

• The Database Connection field is changed.
  

• Click on the Database Login selector and choose the database login you created in Step 8.
  

• The Database Login field is changed. You are done creating the first connection mapping.
  

• Right-click inside of the connection mapping window and select the New option again.
  

• New connection mapping displays in the list.
  

• Click on the Database Instance selector of the new connection mapping and select the Database Instance you created in Step 6.
  

• The Database Connection field is changed.
  

• Click on the User selector of the new connection mapping and the Add members window displays.
  

• Select the second Strategy group you would like to be mapped to the second Database connection created in Step 16, then click OK.
  

• The User field is changed.
  

• Click on the Database Connection selector and choose the first database connection you created in Step 16.
  

• The Database Connection field is changed.
  

• Click on the Database Login selector and choose the database login you created in Step 8. 
  

• The Database Login field is changed. You are done creating the second connection mapping. Click OK.
  

• Click OK.
  

• You are back in the main window of the Database Instances.