Create a BigQuery sync job

About BigQuery

A BigQuery job in Local Hero entails uploading data from a source CSV file into a destination table within the BigQuery service, which is a paid, petabyte-scale data warehousing and analytics technology within the Google Cloud Platform (GCP). BigQuery is ideal for data sets that are likely to be too large for Google Sheets, however, it is not a transactional database, so it tends to work best for data sets transacted upon elsewhere, like in an SIS, but where extremely high-performance querying, analysis, and custom dashboard visualizations may be a requirement.

By our estimates, using BigQuery for most SIS-scale use-cases will end up being free or extremely inexpensive, however, Google does require that a billing credit card be set up for you to use it. 

Big Query and Local Hero setup

For BigQuery sync to function properly, you'll need to preconfigure a few things. First, a target dataset and table need to exist in BigQuery. Next, the data set and table must be accessible with a minimum of BigQuery Data Editor permission to the user who is authorizing Local Hero. Visit Google Cloud Platform to preconfigure BigQuery.

Creating a BigQuery sync job

Now that you’ve set up your BigQuery project, you can create a sync job in Local Hero.

Step 1: Job name & type

  1. Open Local Hero.
  2. Click + Create your first sync job.
  3. Enter the Job Name in the field provided.
  4. From the Load Target drop-down, select Big Query.
    Step1BigQueryHL.png
  5. Click Next to advance to Source data & Target.

Step 2: Source Data & Target

Next, you’ll select the source CSV file that contains your SIS data and the Google Sheet you want to contain the data. The Google Sheet is your target. 

To select a data source, do the following:

  1. From Source data & Target, click CHOOSE FILE.
  2. Navigate to a source file to push to a destination table in BigQuery.
  3. Select the file.

    Note: Additional files can be joined to the first file by selecting CHOOSE FILE, provided the additional files have a unique primary key that matches a value within the first file.

  4. To identify a target table in BigQuery, click Load Projects. All GCP projects where BigQuery privileges have been granted to the user who has authorized Local Hero load.

    Note: Click the gear icon to see the user who authorized Local Hero.

    Note: If projects do not load, be sure you’ve added the correct Local Hero user to the GCP project.

  5. Select the Project from the drop-down.
  6. Select the Dataset from the drop-down.
  7. Select the Table from the drop-down.
    Step2BigQueryHL.png
  8. Click Next to advance to Target mappings.

Step 3: Target Mappings

  1. From Target Mappings click the gear icon next to a field to map it to the correct source data field in your CSV file.
    Step3TargetMappingHL.png
  2. Within the mapping dialog for a given field, you can concatenate fields, insert text, and utilize a small set of date, string, and anonymization functions. Also note the validation section, which can help guide the data type requirements in BigQuery. The sync job will also fail if validation fails within the preview.
    Step3Expression2HL.png
  3. Click Save
  4. Continue until all fields are mapped. 
  5. Click Next to advance to Choose Action.

Step 4: Choose Action

  1. From Choose Action select an option. Because BigQuery is a non-transactional data store, appending and clear/replace are the only options available.
    Step4ChooseActionHL.png

    Note: For record-level update functionality, see Sheets sync jobs.

    Setting Description
    Append source records to Big Query table New data will be appended below the last data row in the Big Query table each time the app runs.
    Clear and replace data in Big Query table All data below the header row in the Big Query table will be cleared and replaced each time the app runs.
  2. Click Next to advance to Automation settings.

Step 5: Automation settings

This step lets you specify how the job is run. Options include manually, automatically at a specific time of day, or every hour. You can also opt to send an email notification to a designated list of email addresses.

  1. Select automation settings for this job.
    Screen_Shot_2018-08-27_at_5.43.08_PM.png
  2. (Optional) In the Admin Notifications filed, enter an email address to send sync notifications. Setting admin notifications email address(es) will send an email that looks like this each time the job runs.
  3. Click Save.
  4. At the saved successfully prompt, click View Job List to view jobs.
    Step5JobsListHL.png