Use data from Google BigQuery with the BigQuery data source

The following sections describe how to use data from Google BigQuery with the BigQuery data source:

Note: You can use data from Google BigQuery with the BigQuery data source (as described in this topic) or with Connected Sheets. To compare the two options, see Use data from Google BigQuery with AppSheet.

Overview

The BigQuery data source provides app creators with read-write access to BigQuery datasets.

 Watch the following video for an introduction to using data from BigQuery with the BigQuery data source.
(Note: Since the date this video was created, the BigQuery data source now provides app creators with read-write access to BiqQuery datasets. The video incorrectly states access is read-only.)

Connecting AppSheet to BigQuery

Licensing requirements for a BigQuery data source

BigQuery is a Google Cloud service that many enterprise customers use for data reporting. Because the volume of data that must be processed can be significant, BigQuery is classified as an Advanced Data Connector and requires an Enterprise-level plan.

The following AppSheet subscription plans include access to a BigQuery data source:

  • AppSheet Enterprise Standard
  • AppSheet Enterprise Plus

Limits when using a BigQuery data source

Because BigQuery has the potential to have massive amounts of data (potentially tens of millions of rows or more and up to 10,000 columns), we have put some product limits in place to ensure your apps will be able to load data and perform well.

AppSheet enforces the following limits for a BigQuery data source:

  • Maximum of 100,000 rows - BigQuery datasets exceeding the maximum number of rows will be truncated.
  • Partitioned tables are not supported - Any BigQuery tables that have partitioned columns will report an error during configuration in AppSheet. Use a BigQuery View that omits the partitioned column to work around this limitation.
  • Google Cloud access required - Service accounts are the only access method AppSheet supports. You will need to have access to create service accounts and keys in your Google Cloud project, or you will need to request assistance from an IT administrator with the appropriate level of access. The IT administrator can create the data source as a team data source and share it with you.

In addition, there are complex types that are not supported for writing to BiqQuery. These types are Array, Bytes, Geography, and Struct.

The next section provides best practices for setting up your app to work within the product limits.

Best practices for setting up your app to use a BigQuery data source

1. Use security filters.

Include AppSheet expressions in the security filters, which will be translated to BigQuery SQL and executed on the server before the data is downloaded to the app.

Security filters are pushed to the SQL Provider only for AND() expressions. OR() and NOT() aren't supported for Provider-level querying before the row limit is applied. For BigQuery, the row limit of 100K is applied on that Provider query, so you can get too few rows if you use a security filter that isn't purely an AND() expression. As a workaround, use the IN() operator as a substitute for OR() if you wish to execute the query before the row limit is applied.

Examples:

  • Filter by USEREMAIL() to get rows owned or viewable by the currently logged-in app user.
  • Filter by DateTime values in the last week or month to get only the most recent data. For example, assuming there is a column in the dataset called DateCreated, you could use security filter expressions like:
    • For the last week: [DateCreated] >= TODAY() - 7
    • For the last month: [DateCreated] >= TODAY() - 30
  • Use a combination of user settings and security filters to allow your app user to limit the data. For example, using the user setting column named Year and a column in the dataset called StartTime, you can get data from the year 2018 or later (assuming the user setting Year is set to 2018), as follows: [StartTime] >= DATETIME("1/1/" & USERSETTINGS("YEAR"))

2. Use BigQuery Views

You can create specific views for AppSheet in BigQuery that have potentially complex SQL queries to create a limited number of rows from the dataset. When setting-up the BigQuery data source as a table in your app, select from the list of views in your project as well as from the base datasets.

3. Create a new BigQuery Table

BigQuery allows you to set up a copy of a table that can be re-created periodically based on a scheduled query. This is similar to a view, but it is a physical table that contains the subset of data. This can also be used to reduce the number of columns in order to simplify the table structure.

Configure BigQuery in Google Cloud

In order to connect AppSheet to BigQuery, a service account must be created in Google Cloud with the correct role to access BigQuery data. A service account is a special kind of account that is used to grant system-to-system level access, rather than grant access to individual users. AppSheet (a system) will use the service account user to access BigQuery (a system) which will establish the system-to-system communications. Additional user-level access can be configured in AppSheet through security filters on the data source.

Accessing a public dataset only requires a BigQuery Job User role. Accessing a private dataset requires a custom role in Google Cloud, as described in Access private datasets using custom roles. (You can skip this section if you only plan to use a public dataset. )

Create a service account

The following steps describe how to create a service account using the Google Cloud console.

  1. Go to the Google Cloud console.

  2. In the navigation menu, select IAM & Admin > Service Accounts to access the Service accounts page.

  3. Click + Create Service Account.

    Create a service account in the Google Cloud Console

  4. Create service account with the BigQuery Job User role

    1. Enter the a Service account name and Service account description and click Create.

      Add service account details

    2. Type in BigQuery Job in the text box to filter to the list of roles and select BigQuery Job User.

      Grant BigQuery Job User role to service account

    3. Click Done.

      Click Done to create your service account

Alternatively, the BigQuery Job User role can be set with the gcloud command-line interface, as shown in the following example. Change PROJECTID and SERVICE_ACCOUNT_ID to the values appropriate for your account.

gcloud projects add-iam-policy-binding PROJECTID \
--member= "serviceAccount:SERVICE_ACCOUNT_ID" \
--role="roles/bigquery.jobUser"

Create a JSON key for the service account

  1. Click the More menu (three dots) in the Actions column of the new service account you created, and select Manage keys.

    Manage keys in your service account

  2. Click Add Key and select Create new key.

    Add key > Create new key in your service account

  3. Accept the default selection of JSON and click Create.

    Create private key for the service account in JSON format (default)

  4. Note the file name. You will find this in your browser’s download folder. Depending on your browser type, you may see the downloaded file in the bottom bar of your browser window. Click Close.

    View and copy the private key that is saved to your computer

  5. Open the file with your favorite file editor on your desktop so that you can cut and paste contents when Add a BigQuery data source to your AppSheet app.

Access private datasets using custom roles

To access a private dataset, you need to create a custom role in Google Cloud IAM. If you do not need to access a private datasets (that is, you only need to access public datasets), skip to Add a BigQuery data source to your AppSheet app.

Each Google Cloud account will have different security policies set up according to the security needs of the customer account. The instructions in this section should work for most Google Cloud projects with a highly restrictive set of policies. You may not need all of the permissions in the custom role in your specific Google Cloud project, so you can also try removing some of the permissions if you would like, then add them back in until it works for your project.

Google Cloud provides a shell feature in the Google Cloud Console called Cloud Shell which allows you to use a command0line interface. This is the quickest way to create a custom role.

  1. Click the Cloud Shell icon in the upper right of your Google Cloud Console window.

    Cloud Shell icon in the upper right of your Google Cloud Console window

  2. Click Continue when prompted, and then wait for the Terminal window to boot up in the bottom portion of your screen.

  3. Click the Open Editor icon in the Cloud Shell Navigation bar (or use vi if you prefer).

    Click Open Editor in the Cloud Shell Navigation barb

  4. Click the icon and select New File in the menu.

    Select .. > New File in the editor

  5. Enter a file name of your choice with a .yaml extension, like: BigQuery.AppSheet.Roles.yaml

    New file name, BigQuerye.AppSheet.Roles.yaml

  6. Cut and paste the following text into the file (modify the title and description values as needed for your use case).

    title: "bigquery.appsheet"
    description: "BigQuery ro role for AppSheet"
    stage: "GA"
    includedPermissions:
    - bigquery.datasets.get
    - bigquery.jobs.create
    - bigquery.routines.get
    - bigquery.routines.list
    - bigquery.tables.get
    - bigquery.tables.getData
    - bigquery.tables.list
    - resourcemanager.projects.get

  7. Remove any blank lines at the end of the file by using the Backspace or Delete keys on your keyboard. Your file should contain 12 lines, as shown below:

  8. Click on the x in the File tab to close the file (it will be saved automatically). Then, click Open Terminal to return to the shell.

    Close the file in the editor by clicking x and open the terminal by clicking Open Terminal

  9. The terminal window reappears at the bottom of your screen.

  10. Cut and paste the following commands, one at a time. Replace the <bracketed> text with your own values. The backslash (\) tells the shell that the command will continue on the next line. The --file option tells the command to read the role definitions from the .yaml file you created above.

    gcloud iam roles create <your new role name> \
    --project=<your-project-id> \
    --file=<your-YAML-File-Name.yaml>


    Below is an example using actual values:

    gcloud iam roles create bigquery.appsheet3 \
    --project=appsheet-scott \
    --file=BigQuery.AppSheet.Roles.yaml

  11. Click Authorize to authorize the shell to call the API which will create the role.

    Click Authorize to authorize the Cloud Shell

    You should see the resulting text which confirms that the role was successfully created:

  12. Now, you need to share your private dataset with the role that you created so that all the permissions are available to the AppSheet BigQuery data source. You need the user name that was assigned to your service account. Navigate to the Service Accounts page of Google Cloud Console using the navigation menu or going to this URL: https://console.cloud.google.com/iam-admin/serviceaccounts

  13. Copy the email address from the Email column in the BigQuery service account that you created earlier. You will need this information in the next few steps. (You may want to open a new tab for the next step so that you can re-copy the service account email, if necessary.)

    Copy email address from the BigQuery service account

  14. Open a new tab in your browser and go to the BigQuery service in the Google Cloud Console. Use the navigation menu or go to this URL: https://console.cloud.google.com/bigquery

  15. Find your private dataset and click the More menu (three vertical dots) and select Open.

    Open private dataset by selecting More > Open

  16. Click Share Dataset in the BigQuery navigation bar:

    Share private dataset by clicking Share Dataset

  17. Paste the service account email address (copied in step 13) into the Add members text box.

  18. Select the Select a role menu and select Custom.

  19. Select the new custom role you created above, like bigquery.appsheet.

  20. Click Add.

    Click Add to add the new role permissions to the BigQuery account

  21. Note the new role now has a member, which is your service account. Click Done.

    Confirm service account is added as a new member for the role on the Dataset Permissions tab

  22. Now, you are ready to add the BigQuery data source to an AppSheet app.


Add the BigQuery data source to an AppSheet app

  1. Sign in to AppSheet.
  2. Go to the My Account > Sources.
  3. Click + New Data Source.
  4. Select Cloud Database and enter the data source name to use for your new data source in the text box, such as BigQuery-NYC-Citibikes.

    Enter data source name as BigQuery-NYC-Citibikes and click Cloud Database
  5. Choose BigQuery from the drop-down list of available database types.

    Select BigQuery from available database types

  6. Next, you need to obtain the following three values from the Google Cloud Console and the key file you downloaded previously: the BigQuery DataSet ID, the Google Cloud Project ID, and the Service Account key

    DataSet ID, Google Cloud Project ID, and Service Account key fields are all highlighted in the Add database connection information dialog

  7. To obtain the BigQueryDataSet ID, go to the Google Cloud Console and select BigQuery under the Big Data section in the navigation menu. You may need to scroll down to find it.

    Select BigQuery under the Big Data section in the navigation menu of the Cloud Console

  8. Find the BigQuery dataset that you’d like to use. We’ll use a public dataset called bigquery-public-data:new_york_citibike in our demo, but you should select the dataset that you would like to use in your app. If you don’t see the dataset you want, click + Add Data and add it.

  9. Open the dataset by clicking on the More menu (three vertical dots) to the right of the dataset name. Make sure you are at the top level. That is, the dataset level, indicated by this icon:

    Not on a table level, indicated by this icon:

    Click More > Open to open the dataset

  10. Copy the Dataset ID so that you can paste it into AppSheet.

    Copy the contents of the Dataset ID field

  11. Paste the Dataset ID into the DataSet ID field in AppSheet's Add database connection information window.

    If the Dataset ID uses a dot separator, replace it with a colon.

    Past value in Dataset ID field in Add database connection information dialog

  12. To obtain the Google Cloud Project ID, go back to the Google Cloud Console. Click on the project name in the top navigation bar, and then copy the Project ID in the Select a project dialog so that you can paste it into AppSheet.

    Obtain the Google Cloud Project ID

  13. Paste the Project Id into the Google Cloud Project ID field in AppSheet's Add database connection information window.

    Paste project ID in Add database connection information dialog

  14. To obtain the Service Account key, go to the JSON file that was downloaded when you created the key on the Service Account in Google Cloud Console (IAM & Admin). Find the file that was downloaded (it will be in your browser’s download directory) and open it with any text editor (if it is not already open). Copy the entire contents of the file.

    Obtain the service account key from the JSON file that was downloaded when you created the key by copying the entire contents of the file

  15. Paste the contents of the file into the Service Account key field in AppSheet's Add database connection information window.

  16. Click Test and then Authorize Access.

    Click Test, then Authorize in the Add database connection information dialog

 

Add a BigQuery table to your AppSheet app

  1. In AppSheet, go to Data  and click + in the top header of the Data panel.
    We've made some improvements to the app editor, currently in preview.
    You are opted in to the new editor by default, but you can switch back to the legacy editor at any time.

    If you are using the legacy navigation

    Go to Data > Tables and click + New Table.  
  2. Select the data store you created (like big query NYC citibikes).
  3. Select Tables or Views. (In this demo, we select Tables.) Then, select the table that you want for your App. (In this demo, we select citibike_stations.)

    Select tables and then select the citibike_stations table
  4. Mark the table as Read-Only to improve performance as the app will not try to recache as often. Click Add This Table.

    Click Read Only and then Add this Table to create a new table in your app based on the citibike_stations data source
  5. Now, your app will have access to this data as if it were any other table.
  6. Important: Don’t forget to add security filters to limit the number of rows that will be used with AppSheet.
Was this helpful?
How can we improve it?

Need more help?

Try these next steps:

Search
Clear search
Close search
Google apps
Main menu
Search Help Center
true
true
false
false
false