Ask AI

BigQuery

Use BigQuery as a data source for your Softr app or dashboard.

Softr's BigQuery connection lets you unlock the power of your data warehouse like never before. Build custom dashboards, reports, and even full-fledged apps, all tailored to your business's specific needs.

👉
BigQuery is available on Business and Enterprise plans.

Prerequisites

  • An active Softr account
  • A Google Cloud Platform project with BigQuery enabled
  • A dataset within your BigQuery project
  • At least read-only access permissions for Softr to access your BigQuery dataset

Connecting Softr to BigQuery

  1. Navigate to your data sources from your Softr dashboard.
  1. Select "BigQuery" and initiate OAuth Flow:
      • Click "Connect data source"
      • Choose "BigQuery" and follow the on-screen prompts to authenticate with Google using OAuth.
      • Grant Softr the necessary read-only permissions to your BigQuery datasets.

Building Apps and Dashboards

  1. Select your data source
      • When creating a new dashboard or app block, select "BigQuery" as your data source.
      • Select the specific dataset and table you'd like to visualize.
  1. Customize your visualization
      • Softr offers a wide range of charts and block types.
      • Build dashboards with multiple visualization blocks tailored to different needs.
  1. Advanced: Write custom queries (optional)
      • For more complex data analysis, use Softr's built-in query editor to write SQL queries directly against your BigQuery data.
      • Connect the results of your queries to dynamic blocks to display custom visualizations or tables.
  1. Control access
      • Use Softr's built-in user management and permissions to determine who can see which dashboards and data.

Key use cases

Tailored reporting

Empower users with intuitive dashboards tailored to their specific roles. This enables them to track key metrics like user activity, application performance, and payment trends directly within the Softr workspace. For example, your team can easily build a comprehensive users and applications dashboard without complex analytics software.

External data sharing

Securely provide strategic insights to partners and vendors. For example think of yourself as an ecommerce platform that can now extract product performance data from your BigQuery warehouse and offer partners valuable dashboards highlighting how their products are performing.

Important notes

  • The current integration offers read-only access to BigQuery.
  • Consider BigQuery's pricing structure, as costs are based on data volume analyzed.
  • BigQuery excels at analyzing massive datasets and powering complex reports. It's not a replacement for core SQL database (like PostgreSQL, MySQL) or structured database products like Airtable or SmartSuite.
 

Troubleshooting

This guide applies in case following errors or similar have been seen while users try to connect their BigQuery datasources to lists or charts:

Important part of error in text: "Access Denied:
BigQuery BigQuery: Permission denied while globbing file pattern.",
Important part of error in text: "Access Denied: BigQuery BigQuery: Permission denied while globbing file pattern.",
Important part of error in text: "Access Denied:
BigQuery BigQuery: Permission denied while getting Drive credentials.",
Important part of error in text: "Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.",

Problem

The reason for these errors is that working with “external tables” in BigQuery requires additional permissions (OAuth scopes) that should be requested by us and will be prompted to user during authentication flow. However as we have already pretty broad permissions and adding more on top of it as a third party application might not create a good impression with customers, we decided not to ask for these additional permissions and instead present customers with some workarounds and other ways to fix this.

Internal vs external tables

In BigQuery, while creating a table in some cases user can decide or has only the option to create an external datasource.

user might have the option to or be forced to create an “external table”. Table creation dialog, BigQuery console.
user might have the option to or be forced to create an “external table”. Table creation dialog, BigQuery console.

External tables are forced or available when user creates a table from:

  • A file on Google Drive with supported format (CSV, Avro, …)
  • A Google Sheet document
  • A file on Google Cloud Storage

if user goes for an external table in these cases, trying to integrate such table with Softr will give them an error message similar to those mentioned above.

To identify an existing table as either internal or external, one could take a look at table details and would see a “External data configuration” section for external tables similar to following:

Notion image

Workaround: enforce internal datasource

One option as a workaround here is to make sure table type is set to Native table in the creation phase. This option is not available for all sources.

This will however mean that there will be no syncing happening with the original source and table will not be updated if/when that external source changes.

Workaround: copy table

In case user has no other option, they can also create a second table that is native and copies all the data from the external table. User then might use that copied table in Softr. A pseudo SQL to do this is: create table native_table_copy as select * from external_table

This has the disadvantage that the space required for data will be duplicated.

Current limitations

  • Read-only access: BigQuery is read-only, so you can’t add, update, or delete data via Softr. This makes blocks like Forms unusable, and you can’t manage or store Softr user data in BigQuery. However, you can still manage user data within Softr.
  • No auto-sync options: We currently do not support auto-syncing dropdown or quick select options.
  • No field compatibility mapping: We don’t currently specify which fields can and can’t be mapped, but you can still map fields as per normal.
  • No auto-mapping: Fields will not be auto-mapped when connecting a block to BigQuery.
  • No querying support for charts: Currently, you cannot connect to BigQuery using queries for chart blocks.
Did this answer your question?
😞
😐
🤩

Last updated on August 30, 2024