New in BigQuery UI: Table Explorer and Insights Tabs

Getting lost in a sea of tables… or struggling to generate the right queries from the data you’ve just met?

We all have been missing this in BigQuery for years…

Imagine being able to sift through dozens of tables and datasets with ease, crafting queries in just a few clicks…

With new Table Explorer and Data Insights tabs in BigQuery, this is no longer a distant dream, but a reality that data analysts can now experience firsthand and use every day.

In this article, we'll talk about these new functionalities, as well as their limitations, and share best practices applicable to both newbies and seasoned data pros.

We aim to help you leverage these new interface tools in BigQuery to their fullest potential, ensuring your data exploration tasks are effective and straightforward.

BigQuery Table Explorer is a new tool within Google's BigQuery UI that simplifies data exploration and query creation. It allows users to simply pick up to 10 table fields at a time, which are then displayed as interactive cards.

These cards showcase the 10 most common values for each selected field, sorted by the count column, facilitating a visual exploration of table data. Users can interact with these cards by choosing specific fields and values to examine in greater detail. Based on these selections, Table Explorer automatically generates a data exploration query.

This query can be copied to the query editor for further refinement or applied directly within Table Explorer, which executes the query and updates the display with new results.

BigQuery Table Explorer allows users to navigate and interact with their data visually. By providing a graphical interface where users can select table fields and view the most common values, Table Explorer simplifies the task of identifying trends and anomalies within the data.

The real advantage lies in its ability to generate SQL queries based on user interactions with data cards, which display key information about field values.

Furthermore, Table Explorer supports iterative data exploration; users can refine their queries by selecting more fields or values from updated data cards after initial results are displayed, ensuring a more targeted analysis.

Properly preparing your environment before using the BigQuery Table Explorer is important to ensure a seamless and effective data exploration experience.

Setting up your Google Cloud project correctly and configuring the necessary permissions are crucial steps to fully utilizing Table Explorer’s capabilities.

Step 1: Select a Google Cloud Project.

First, navigate to the Google Cloud console. Here, you can either select an existing project or create a new one.

Step 2: Enable Billing.

Ensure that billing is enabled for your Google Cloud project to use BigQuery services without interruption.

Step 3: Enable the BigQuery API.

Access the API management section within your Google Cloud console and enable the BigQuery API to allow the Table Explorer.

Step 4: Configure Required Permissions.

• BigQuery Job User: This role is necessary to execute and manage jobs within BigQuery. Assign the roles/bigquery.jobUser at the project level.
• BigQuery Data Viewer: To view and interact with table data, ensure you have the roles/bigquery.dataViewer role. This should be applied to all tables and views you intend to explore.
• Manage Access: If you do not have the necessary permissions, contact your administrator to grant you the appropriate roles.

Analyzing data with BigQuery Table Explorer is straightforward and efficient. Here, we'll walk through a step-by-step process using an example dataset, such as a public dataset available in BigQuery.

Let's explore a scenario where a company wants to analyze customer transaction data to identify trends and high-value customers.

The dataset is stored in BigQuery under the project owox-analytics, within the dataset customer_transactions and the table transaction_records.

Step #1: Select the Table for Query Creation

1. Project: Navigate to your Google Cloud project, in our case - it’s owox-analytics.
2. Dataset: Choose the dataset. We’ll do it for the customer_transactions.
3. Table: Select the table. We will select the transaction_records table which contains fields like transaction_id, customer_id, amount, and date.

Step #2: Access the Table Explorer Tab:

Open the BigQuery UI and navigate to the transaction_records table. Click on the ‘Table Explorer’ tab. This tab provides a visual interface for exploring the selected table.

Step #3: Add Table Fields for Exploration:

In this step, you’ll add fields such as customer_id, amount, and date to the exploration area. These fields are chosen based on their relevance to your analysis goals, such as identifying spending patterns or customer activity over time.

Step #4: Apply Custom Partitioning Filters:

Here, you can refine the dataset by applying filters that focus on specific subsets of data. For instance, you can filter by date to include only the transactions from the specific date and set an amount filter to concentrate on higher-value transactions.

Step #5: Save and Run the Query:

After configuring your fields and filters, save your setup. Then, proceed to run the query. BigQuery Table Explorer automatically generates the SQL query based on the fields and filters you’ve set up.

Step #6: View and Modify the Generated Query:

Examine the SQL query generated by Table Explorer. It provides an insight into how the query is structured and offers an opportunity to tweak it for more specific needs. You can refine the query in the query editor by sorting transactions by customer_id to identify top spenders.

Use the OWOX: Reports, Charts & Pivots Extension to enhance data reporting and visualization.

Run the refined query through this extension to create detailed reports and visualizations that can be shared with the business team for strategic decision-making.

This example demonstrates how BigQuery Table Explorer can be used efficiently in a real business context to perform complex data analysis tasks, helping companies derive meaningful insights from their data.

When utilizing BigQuery Table Explorer, it's crucial to understand and manage access policies effectively to ensure uninterrupted data exploration and query generation.

Access policies determine the actions users can perform within a project, including the ability to create and run queries. If permissions are misconfigured, it could abruptly halt data analysis, evidenced by errors like

This indicates that the user lacks the necessary permissions to create query jobs because they can view data but cannot operate on it.

An administrator must modify the IAM (Identity and Access Management) settings to address such issues to grant the bigquery.jobs.create permission to the affected user. This permission is critical for allowing users to execute queries and manage jobs within BigQuery, and it is required in addition to any permissions needed to view or access the data itself.

BigQuery Table Explorer is a powerful tool for data exploration within Google’s BigQuery environment. However, like any tool, it has certain limitations that users must consider when integrating it into their data analysis workflows.

Let us look at the limitations:

1. Single Table Exploration: Table Explorer is designed to explore only one table at a time. It does not support simultaneous exploration of multiple tables or the ability to perform cross-table operations like JOINs.

2. Direct SQL Query Generation: The SQL queries generated by Table Explorer directly reflect the selection of table fields and distinct values you choose. While you can execute these queries as is or manually edit them in the query editor, Table Explorer does not offer AI-powered assistance to generate, complete, or explain SQL queries.

3. Access Control Restrictions: To explore table data and generate queries in environments with column-level access control (ACLs) or other forms of restricted user permissions, you must have read access to all selected fields.

4. Lack of AI-powered Assistance: One significant drawback of BigQuery Table Explorer is its lack of AI-powered tools to help generate or complete SQL queries.

The main limitation of BigQuery Table Explorer is its focus on exploring only one table at a time, without offering AI-powered assistance for generating or completing SQL queries.

This can be a challenge when working with complex datasets or trying to identify patterns and insights across multiple tables or data sources.

Data Insights helps mitigate this limitation by automatically generating queries based on metadata and profile scans. This enables analysts to quickly identify patterns, evaluate data quality, and perform statistical analysis, even with limited prior knowledge of the dataset.

By combining Data Insights with Table Explorer, analysts can move beyond the single-table constraint and unlock more comprehensive and actionable insights from their data.

To enhance your data analysis within BigQuery, consider using OWOX BI SQL Copilot. This tool significantly streamlines SQL query development, offering intelligent suggestions and automation that improve query accuracy and efficiency.

The new BigQuery Insights section is a transformative addition to Google's BigQuery platform, designed to automate and simplify data analysis.

This feature uses advanced algorithms to analyze your data automatically, identifying patterns, trends, and anomalies without the need for manual query generation.

It's especially useful for data analysts who need to quickly understand key aspects of new datasets, providing actionable insights that can inform strategic business decisions.

By leveraging metadata and machine learning, the Insights section helps overcome common challenges in data exploration, such as the cold-start problem, where analysts begin with little prior knowledge of the dataset.

It generates queries based on published profile scan data, ensuring that the insights provided are both relevant and reliable.

Generating data insights in BigQuery is a straightforward process designed to help analysts quickly understand and leverage their data. By using BigQuery’s Insights section, you can automate the analysis of tables to uncover valuable patterns and trends without intensive manual effort.

Let's consider a business scenario where a company wants to analyze sales data to identify seasonal trends and customer preferences. Here’s how they would use the BigQuery Insights section to generate these insights:

Step #1: Access BigQuery Studio:

Begin by logging into your Google Cloud console. Once logged in, navigate to the BigQuery section. Here, you will find BigQuery Studio. Open BigQuery Studio to start the process of generating data insights from sales data, which may include transaction volumes, customer buying patterns, and seasonal variations.

Step #2: Navigate to the Insights Tab:

Select the sales data table from your dataset, and click on the Insights tab. This tab will be your gateway to automated data analysis. If it’s your first time generating insights for this table, the tab will appear empty, ready for new analysis.

Step #3: Trigger the Insights Pipeline:

Click on the 'Generate insights' button to start the process. This will analyze the data within the selected table and begin formulating insights. Depending on the complexity of the data, the generation process usually takes 5 to 10 minutes.

Step #4: Utilize Profile Scans for Insights:

If a published profile scan is available, it will be used to enhance the insights' accuracy and relevance. This method ensures that the generated insights are as comprehensive as possible, even if detailed custom data annotations are absent.

Step #5: Explore the Generated Insights:

Review the insights produced, which typically include suggested queries and summaries of key findings. These insights are valuable for identifying underlying trends, such as an increase in specific product sales during holiday seasons or shifts in customer preferences over time.

Step #6: Open and Manage Queries in BigQuery:

You can open any of the suggested queries directly in BigQuery for more detailed exploration or customization. This flexibility allows you to modify the queries, combine them with additional data, or refine the analysis to focus on particular aspects of the sales data.

Step #7: Regenerate Insights:

If you update the table or wish to refine the focus of your analysis, click 'Generate insights' again to refresh the insights. This ensures that your analysis remains up-to-date with the latest available data.

Utilize the OWOX: Reports, Charts & Pivots Extension to visualize the insights from BigQuery. This tool helps automatically create dynamic reports and dashboards, which are instrumental in sharing insights and making data-driven decisions in a visually engaging manner.

By following these steps, the company can efficiently analyze its sales data, gaining actionable insights that help it make informed business decisions and strategize based on consumer behavior and market trends.

Integrating BigQuery's Table Explorer with the Data Insights section provides a powerful solution to the common challenge faced by data analysts: the ‘blank-screen’ challenge.

This issue arises when analysts approach large, unfamiliar datasets without prior knowledge of the data's structure or underlying patterns, making it difficult to formulate meaningful queries or derive insights.

By combining Table Explorer's visual exploration capabilities with Data Insights' automated query generation, analysts can quickly become familiar with new datasets and jumpstart their analysis with greater confidence and efficiency.

The workflow for using both tools together is streamlined and effective.

• Access Table Explorer to visually understand the dataset's structure.
• Explore key tables to grasp the breadth and depth of the available data.
• Identify important fields that could yield significant insights.
• Understand relationships between different data elements within the table.
• Utilize the basic understanding from Table Explorer as a foundation.
• Engage Data Insights to leverage its automated capabilities for query generation.
• Allow Data Insights to generate queries and deeper insights based on the initial exploration.
• Integrate the visual exploration from Table Explorer with the analytical outputs from Data Insights.
• Use the combined insights to create a more detailed and actionable analysis.
• Develop reports or further queries that are informed by a comprehensive view of both tools.

Ultimately, this combination enhances the analytical workflow by making it more efficient and insightful. Data Insights complements the initial explorations made with Table Explorer by providing deeper analyses, that highlight critical trends, patterns, or anomalies identified during the exploration phase.

Exploring insights is not limited to your primary datasets in BigQuery. The Insights tab also extends its capabilities to other types of tables, such as external tables and those stored in BigLake.

Generating insights for a BigQuery external table follows a similar process to that used for standard BigQuery tables, but with specific considerations regarding the data's location.

BigQuery Data Insights supports external tables only if they are located within the same Google Cloud project. If the external table references data stored in Cloud Storage from a different Google Cloud project, the insights generation will not succeed due to cross-project access limitations.

To successfully generate insights for an external table,

• Ensure that all referenced data sources are included in the same project.
• Then, apply the standard process for generating insights as outlined in BigQuery, which involves selecting the table, navigating to the Insights tab, and initiating the insights generation.

This method helps extract meaningful patterns and analytics directly applicable to external data, enhancing your overall data analysis strategy.

Generating insights for a BigLake table involves leveraging BigQuery's capabilities to interact with the scalable and elastic storage provided by BigLake.

Here are the steps to enable and use this feature:

Step #1: Enable the BigQuery Connection API

Before starting, ensure that your Google Cloud project enables the BigQuery Connection API. This API is crucial for creating connections between BigQuery and BigLake.

Step #2: Create a BigQuery Connection

Navigate to the BigQuery section of your Google Cloud Console. Choose to manage connections and create a new connection specifically for BigLake.

Step #3: Grant Necessary Permissions

Identify the service account associated with the BigQuery connection you created. Grant the Storage Object Viewer (roles/storage.objectViewer) IAM role to this service account to allow it to read data from BigLake.

Step #4: Generate Insights

With the connection set up and permissions in place, generate insights by following the same instructions for a standard BigQuery table.

By following these steps, you can efficiently generate insights from BigLake tables, making the most of BigQuery’s powerful data analytics capabilities directly on your BigLake data storage.

BigQuery's Data Insights feature is invaluable for deriving actionable insights from complex datasets. To leverage this feature effectively, ensuring that the queries are well-grounded and supported by accurate data profiles is crucial.

Here are some best practices to follow when using Data Insights in BigQuery:

• Ensure Up-to-date Profile Scans: Regularly update your tables' published profile scan data. This ensures that the insights and queries generated are based on the most current and relevant data.
• Review Generated Queries: Take the time to review and understand the queries that Data Insights generates. Analyzing how these queries utilize profile scan data can help you grasp the underlying logic and relevance, providing deeper insights into your dataset.
• Adjust Profile Scan Settings: If the queries generated do not seem relevant or useful, consider adjusting your table's profile scan settings. You can also provide additional context to Data Insights to refine the queries further, ensuring they meet your specific analytical needs.

When leveraging BigQuery's powerful data analysis tools, such as Table Explorer and Data Insights, it's crucial to understand the associated pricing and cost considerations. While immensely beneficial for data analysis, these features incur charges based on data processing and query execution.

Before executing a query, Table Explorer displays the estimated data to be processed, allowing you to review potential costs.

To manage costs effectively:

• Always review the estimated data volume and associated costs Table Explorer displays before confirming query execution.
• Optimize your queries by selecting only the necessary fields or applying filters to reduce the volume of data processed.
• Consider setting up budget alerts in Google Cloud to monitor and manage your spending on BigQuery operations.

As of the current phase, the BigQuery Data Insights feature is in preview and is not subject to direct billing. This offers a cost-effective opportunity for users to experiment with and evaluate the feature without immediate cost implications.

However, staying updated with Google's pricing policies is important, as this status may change once Data Insights moves out of preview. To prepare for potential future costs:

• Take full advantage of the no-cost preview period to explore and integrate Data Insights into your data analysis workflows.
• Keep abreast of Google updates regarding changes to Data Insights's billing status so that budget adjustments can be planned accordingly.
• Begin to assess the value added by Data Insights to your projects to justify potential future expenses when the feature becomes billable.

To take your data analysis capabilities to the next level, consider integrating the OWOX Reports Extension for Google Sheets with your BigQuery setup. This extension enhances the standard BigQuery UI by adding advanced reporting and visualization tools, simplifying the presentation of complex data insights.

Utilizing this structured approach to BigQuery’s robust features allows data analysts to navigate, analyze, and report data more efficiently. By leveraging the OWOX Reports, analysts are better equipped to drive informed business decisions, optimizing operations and strategy development across various organizational departments.