BigQuery's Query Editor offers several Query Settings that can help you to optimize performance, control costs, and debug queries efficiently. Understanding these settings will enable you to run queries more effectively while keeping resource usage in check.
1. How to Customize Query Settings?
To adjust query settings in BigQuery:
· Open BigQuery Query Editor.
· Click MORE → Query Settings.
2. Query Settings Form
It opens a Query Settings form like below.
2.1 Destination section
Destination Section has two options:
· Save query results in a temporary table
· Set a destination table for query results
2.1.1 Save query results in a temporary table
Stores the query results in a temporary table managed by BigQuery. The results are automatically deleted after 24 hours. No manual cleanup is required.
Use this option to
· Run quick ad-hoc queries for analysis.
· Explore data without needing to store results permanently.
· Avoiding storage costs for short-term queries.
Limitations:
· Data is lost after 24 hours.
· Cannot be shared with other users.
· Not suitable for long-term storage or scheduled queries.
2.1.2 Set a Destination Table for Query Results
It allows you to save the query results permanently in a specified table. You can choose an existing table or create a new one.
When you enable the 'Set a destination table for query results' option in BigQuery Query Settings, you must specify where the results should be stored. This requires providing:
· Dataset: The dataset in which the table will be created or updated.
· Table ID: The name of the table where the query results will be saved.
2.1.2.1 Destination table write preference
After specifying the destination table, you must select one of the following write modes to determine how the results should be handled:
Write if Empty (Default Option): If the table does not exist, it will be created, and the results will be stored. If the table already exists and contains data, the query fails instead of overwriting or appending.
Use this option to
· Ensure that an existing table is not accidentally modified.
· Run queries where results should be written only once to a fresh table.
Append to Table
Adds new query results to the existing table data without deleting or modifying previous records.
Best to:
· Maintain historical data by adding new records over time.
· Run queries that incrementally update tables.
Example Use Case:
· Storing daily transaction data by appending new transactions each day.
Overwrite Table
Deletes existing data in the table and replaces it with the new query results.
Best for:
· Refreshing a table with new data instead of keeping old records.
· Running queries where only the latest data matters.
Example Use Case:
· Overwriting a table with daily aggregated sales data instead of keeping previous reports.
2.1.2.2 Result Size Option: Allow Large Results (no size limit)
If enabled, large query results can be written to the destination table without size restrictions. If disabled (default setting), queries that return large result sets may fail due to size limitations.
Best for:
· Running complex queries that return billions of rows or large datasets.
· Storing detailed analytics without breaking queries due to size constraints.
Example Use Case:
· Generating a full customer purchase history that spans several years.
2.2 Resource Management
2.2.1 Job priority
When running a query in BigQuery, you can set the Job Priority to control how the query is executed based on urgency, resource availability, and cost. BigQuery provides three query job types:
· Interactive (default)
· Batch
2.2.1.1 Interactive
Queries execute immediately as soon as resources are available. Uses on-demand pricing based on the amount of data processed. Higher quota restrictions compared to batch queries.
When to Use:
· When you need instant results for analysis or debugging.
· For exploratory queries that require quick execution.
· When working on a time-sensitive task (e.g., real-time reporting).
Limitations:
· Consumes resources immediately and may be costly for large queries.
· Subject to strict quota limits, which may restrict execution during peak hours.
2.2.1.2 Batch Queries
Queries are queued and executed when idle resources are available. Usually starts within a few minutes but can take up to 24 hours. Lower quota restrictions than interactive queries.
More cost-effective, as Google may offer discounts for batch processing.
When to Use:
· When query results don’t need to be immediate.
· For large analytical queries (e.g., monthly reports, data processing pipelines).
· When working with scheduled queries.
Limitations:
· Queries do not start immediately, so they are unsuitable for time-sensitive tasks.
· Execution time depends on system load; delays are possible.
2.2.2 Cache preference (Use cached results)
"Use cached results" is a performance- and cost-optimization feature in BigQuery that is enabled by default. It allows BigQuery to reuse results from a previous query execution.
How Cached Results Work?
· If you run the exact same query multiple times and the referenced tables are unmodified, BigQuery retrieves the results from cache instead of executing the query again.
· Cached results are stored in a temporary table for approximately 24 hours.
· No billing occurs when cached results are used, making it an effective way to save query costs.
If a destination table is specified, caching cannot be used.
How BigQuery Uses Cached Results?
· Query Execution: When a query runs for the first time, BigQuery executes it and stores the results in a temporary cached table.
· Subsequent Queries: If the same query is executed again within 24 hours and the underlying tables are unchanged, BigQuery returns cached results instead of reprocessing the query.
· Per-User, Per-Project Caching: Cached results are typically user-specific and project-specific. In some editions, users may share cached results within the same project.
· Cost Savings: Since queries are not re-executed, no additional cost is incurred for retrieving cached results.
Note
· Cached results work only if the result set fits within the maximum response size.
· Cached results cannot be modified using DML (INSERT, UPDATE, DELETE, MERGE) statements.
· The query must be identical (including whitespace and formatting) to retrieve cached results. Even a small change (e.g., adding a comment or whitespace) prevents caching.
2.2.3 Job timeout
Job timeout in milliseconds. If this time limit is exceeded, BigQuery might attempt to stop the job.
2.3 Session Management
Session Management in BigQuery allows users to run multiple queries within a single session and share temporary tables across those queries. This improves query efficiency and enables workflows where intermediate results can be reused without writing them to permanent tables.
This option is disabled by default, if you enable it
· Groups multiple queries into a session for better organization.
· Enables temporary table sharing across queries within the session.
· Reduces data movement by allowing intermediate results to be reused.
2.4 Advanced Options
2.4.1 Encryption
BigQuery provides encryption options to secure query results when they are written to a destination table or temporary table. These options are found under Query Settings → Encryption and allow users to choose how query results are encrypted.
2.4.2 Maximum bytes billed
BigQuery allows users to set a billing limit on query execution to control costs. This feature ensures that if a query is expected to process more data than the specified limit, it fails before running, without incurring any cost.
2.4.3 SQL Dialect
BigQuery allows users to choose between different SQL dialects when writing queries. The SQL Dialect setting lets you specify which syntax style BigQuery should use.
BigQuery use Standard SQL dialect by default, it is a modern SQL dialect, which follows ANSI SQL standards. Supports advanced features like Common Table Expressions (CTEs), window functions, and JSON functions.
You can still enable Legacy dialect, but it is Deprecated and Not Recommended. It is missing advanced features available in Standard SQL.
2.4.4 Automatic Location Selection
BigQuery datasets are stored in specific regions (e.g., US, EU, Asia), and queries must execute in a compatible location. The Automatic Location Selection option in Query Settings allows BigQuery to automatically choose the processing location based on the datasets involved in the query.
BigQuery allows you to manually select a specific region for query execution. If your dataset is in asia-southeast1, choosing the same region ensures faster execution and lower latency.
Previous Next Home
No comments:
Post a Comment