Connect to Google BigQuery

Important Nexla Help Center Update:

Nexla's Zendesk Help Center pages are being deprecated and will soon no longer be available.

Nexla Documentation is now the home for Nexla's User Guides, with improved formatting and categories that are easier to navigate, providing a better overall user experience.

Please update any bookmarks to the new Nexla Documentation site (docs.nexla.com/user-guides).

_______________________________________________

This article describes how to add a new Google BigQuery data source in Nexla.

For the version of this article pertaining to the previous Nexla UI, click here.

Important: During data movement to/from BigQuery, Nexla will write temporary data in a location within your GCS. Therefore, the user associated with the credential provided in Section 2 will need the following roles in the associated Google Cloud Project:
Storage Object Creator
Storage Object Viewer

Contents:

1. Begin Adding a Google BigQuery Data Source
2. Input Your Credential
3. Configure the Database Source
     3.1 Table Mode
     3.2 Query Mode
4. Schedule Data Source Scanning
5. Finish Creating the BigQuery Data Source

1. Begin Adding a Google BigQuery Data Source

1. Log into Nexla with your provided credentials.
   

   If you need credentials, contact [e-mail].

2. Navigate to the Integrate section by selecting  from the platform menu on the left side of the screen. 
   
   
3. Click  at the top of the Integrate toolbar on the left.
   
   
   
   
4. Select , and click in the top-right corner of the screen.
   
   

2. Input Your Credential

Important: During data movement to/from BigQuery, Nexla will write temporary data in a location within your GCS. Therefore, the user associated with the credential provided in Step 2 below will need the following roles in the Google Cloud Project:
Storage Object Creator
Storage Object Viewer

1. Select to open the Add a New Credential window and begin adding a new Google BigQuery credential.
   

   To use a credential that has already been added, select that credential, click in the top-right corner of the screen, and skip to Section 3.

2. Enter a name for the credential in the Credential Name field.
   
   
   
   
3. Optional: Enter a description for the credential in the Credential Description field.
   
   
   
   
4. Select the method that Nexla should use to authenticate the BigQuery account from the Authentication Type pulldown menu.
   

   The System User Authentication method is recommended, as it is best-suited for accessing your own data. It is also tied to the service account instead of individual user accounts.

   
   
   End User Authentication
   
   1. Click .
      
      
   2. In the pop-up window that appears, select the Google account associated with the BigQuery account.
      
      
   3. Click to allow Nexla to access the account.
      
      
   4. Enter the project ID to the BigQuery database from which files should be read in the Project ID field.
      
      
      
      
   5. Optional: Click at the bottom of the Add New Credential window to access the following additional credential settings:
      
      
      • Before moving data to/from BigQuery, Nexla sometimes stages the data in a temporary GCS location, which is automatically created by the platform if the user account associated with the credential has bucket/path creation permissions. To override bucket creation and specify a GCS location where Nexla will create temporary staging files, enter the location in the GCS Location for Staging Data field.
        
        
        
        
      • Before moving data to/from BigQuery, Nexla sometimes stages the data in a temporary dataset, which is automatically created by the platform if the user account associated with the credential has dataset creation permissions. To override dataset creation and specify an existing dataset for use, enter that dataset in the Temporary Dataset field.
        
        
        
        
   System User Authentication
   
   1. Click .
      
      
   2. Select and upload the service account credentials JSON file generated by Google Cloud IAM.
      
      
5. Click  at the bottom of the Add New Credential window.
   
   

3. Configure the Database Source

In Nexla, the Google BigQuery database source can be selected using either Table Mode or Query Mode.

Table Mode allows users to specify the database source through a simple selection method. This mode is equivalent to running a simple, optimized SELECT operation on any database table. To use this mode for configuration, see Section 3.1.

Query Mode allows users to perform a complex query to specify the database source. This mode provides a free-form query editor that can be used to perform any complex query written using the syntax and convention supported by the underlying database and/or warehouse. To use this mode for configuration, see Section 3.2.

3.1 Table Mode

1. To configure the BigQuery source using Table Mode, ensure that the tab is selected.
   
   
   
   
2. Find the database location from which Nexla should read data. Expand files as necessary by clicking the icon next to each.
   
   
3. Select the location from which data should be read by hovering over it and clicking the button that appears to the right.
   

   The button should now display , and the path of the selected location will be shown at the top of the list.
   
   
   

4. Optional: Click the button to the right of the mode-selection tabs to generate preview samples of data from the selected source at the bottom of the screen.
   
   
   
   

3.2 Query Mode

1. To configure the BigQuery source using Query Mode, select the  tab.
   
   
   
   
2. Enter the query specifying the database location from which Nexla should read data in the Custom Query to Fetch Data field, adhering to the Google BigQuery SQL syntax and convention.
   

   In this mode, Nexla supports any query that can be written following the BigQuery syntax and convention, regardless of complexity.
   
   For more information about Google BigQuery SQL syntax, see this BigQuery document.
   

   
   
   
3. Optional: Click the button to the right of the mode-selection tabs to generate preview samples of the data selected according to the entered query at the bottom of the screen.
   
   
   
   

4. Schedule Data Source Scanning

1. In the Advanced Settings menu on the right, use the Fetch Data pulldown menu to specify how often Nexla should fetch data from the source.
   

   The default setting configures Nexla to fetch any new data from the source once every day.
   

   
   
   
   • For options such as "Every N Hours" and "Every N Days", use the additional pulldown menu that appears when these options are selected to specify the value of N defining the fetching frequency.
     
     
     
     
2. Optional: To set a specific time at which Nexla should fetch any new data from the source, check the box, and type or use the pulldown menus to enter the desired time.
   
   
   
   

5. Finish Creating the BigQuery Data Source

1. Once all of the above steps have been completed, click in the upper right corner of the screen to save and create the new BigQuery data source.
   
   
2. The confirmation page indicates that the Google BigQuery database has been successfully created as a data source.
   
   
   
   
3. Optional: Edit the name of the newly added data source by clicking on the name field and entering the desired text.
   
   
   
   
4. Optional: Add a description of the data source by clicking on the  field below the data source name and entering the desired text.
   
   
   
   
   • To return to My Data Sources, click in the upper right corner of the screen.
     
     
   • To view the newly created data source, click .
     
     
   • To view datasets detected from the newly added source, click .