Using BigQuery with Rows

Introduction

Rows' BigQuery integration allows you to access and manipulate your data stored in BigQuery using the =QUERY_BIGQUERY() & =IMPORT_TABLE_BIGQUERY()functions.

QUERY_BIGQUERY Function Syntax

=QUERY_BIGQUERY(project, query)

Parameters

project: The name of the BigQuery project. For example: "acme-prod".

query: The SQL query to apply. For example: "select * from analytics.monthly-sales".

Example

1=QUERY_BIGQUERY("acme-prod", "SELECT name, email, phone FROM `analytics.users`")

This example returns a list with name, email, and phone from the table users in the dataset analytics.

IMPORT_TABLE_BIGQUERY Function Syntax

=IMPORT_TABLE_BIGQUERY(project, dataset, table)

Parameters

project: The name of the BigQuery project. For example: "acme-prod".

dataset: The datasets from the BigQuery project. For example: "sales-monthly".

table: The tables from the BigQuery dataset. For example: "users".

Example

1=IMPORT_TABLE_BIGQUERY("acme-prod", "sales-monthly", "city-breakdown")

This example returns a data table with the contents of the city-breakdown table from the sales-monthly dataset in the acme-prod project in BigQuery.

How to import a table from a dataset

1. With any spreadsheet open, click on the Action button in the top menu bar.
2. The search input field is automatically in focus so you can immediately start typing "Big" and the BigQuery integration will show up in the results as well as the available actions.
3. Click on "Imports a table from a BigQuery dataset"

5. We automatically pull in all Projects that are connected to your BigQuery account and list them in the dropdown menu for "Project". Go ahead and select one of your Projects from your account.
6. We now automatically populate the Dataset dropdown menu with the Datasets related to the Project you selected in the previous step. Select a Dataset that you would like to pull data from.
7. Again, we automatically populate the Table dropdown menu with Tables that are related to the Dataset that you selected in the previous step. Select a Table that you want to retrieve the data from and then click "Next step".

8. We automatically load in all the data from the table and provide a preview of this data in your current spreadsheet as a new Data Table. You can now customise this by adding or removing the Table columns. You can also change the table orientation and how the data in this table should behave.

9. Lastly, you can also toggle on the "Schedule refresh" automation where you can automatically refresh the data at a time or frequency of your choosing.

Using SQL to query a table

1. With any spreadsheet open, click on the Action button in the top menu bar.
2. The search input field is automatically in focus so you can immediately start typing "Big" and the BigQuery integration will show up in the results as well as the available actions.
3. Click on "Use SQL to query your BigQuery tables"

4. We automatically pull in all Projects that are connected to your BigQuery account and list them in the dropdown menu for "Project". Go ahead and select one of your Projects from your account that you want to query.
5. Add the SQL command inside the text input field for "Query".

• This SQL editor uses the same line count as BigQuery's own editor.
• The SQL editor will automatically suggest fixes in the SQL code.
• The SQL editor can be expanded for easier editing.

• You can use cell references and functions inside the SQL command. For example:

  select * from embed_views where date = '"&TO_TEXT('Input'!B1-1)&"' order by workspace_name"

6. Click Next step
7. We automatically load in all the data from the table and display it in your current spreadsheet as a new Data Table. You can now customise this by adding or removing the Table columns. You can also change the table orientation and how the data in this table should behave.
8. Lastly, you can also toggle on the "Schedule refresh" automation where you can automatically refresh the data at a time or frequency of your choosing.

Using the function formulas instead of the Action Wizard

Open a Rows spreadsheet where you'd like to query your data and enter the QUERY_BIGQUERY() function in a cell, providing the project and query parameters. For example:

1=QUERY_BIGQUERY("acme-prod", "SELECT name, email, phone FROM `analytics.users`")

Press Enter to execute the query.

The queried data will appear in the Rows spreadsheet. You can use the data in conjunction with Rows' other functions to analyze and manipulate the information further.

How to make the most of the BigQuery integration

When using these functions, keep the following limitations and best practices in mind:

1. Large datasets: Be mindful of the volume of data being imported, as large datasets can slow down the performance of your Rows spreadsheet. Consider using filters and aggregate functions within your SQL query to limit the data retrieved from BigQuery.

2. Data privacy and security: Ensure that sensitive data is protected by applying appropriate access controls to your project and Rows spreadsheets.

3. Rate limits: Google BigQuery has rate limits and quotas that apply to queries. Be mindful of these limits when using the function frequently or with large datasets. For more information, refer to Google's BigQuery Quotas & Limits documentation.

4. Error handling: In case of an error, the function will return an error message. Check your SQL query syntax and parameters to resolve any issues.

5. Syntax: Always ensure that your SQL queries follow BigQuery's SQL syntax and best practices to avoid errors.

6. Values not formulas: The functions only retrieves the output values from your BigQuery tables. Formulas or calculations won't be imported.

Conclusion

This function provides a powerful way to integrate your data into Rows spreadsheets, enabling you to perform advanced analysis and data manipulation. By following the steps and best practices outlined in this article, you can effectively utilize the function to unlock the full potential of your BigQuery data within Rows.

Take it a step further by using Rows's Charts to bring your data to life!