- BigQuery - Home
- BigQuery - Overview
- BigQuery - Initial Setup
- BigQuery vs Local SQL Engines
- BigQuery - Google Cloud Console
- BigQuery - Google Cloud Hierarchy
- What is Dremel?
- What is BigQuery Studio?
- BigQuery - Datasets
- BigQuery - Tables
- BigQuery - Views
- BigQuery - Create Table
- BigQuery - Basic Schema Design
- BigQuery - Alter Table
- BigQuery - Copy Table
- Delete and Recover Table
- BigQuery - Populate Table
- Standard SQL vs Legacy SQL
- BigQuery - Write First Query
- BigQuery - CRUD Operations
- Partitioning & Clustering
- BigQuery - Data Types
- BigQuery - Complex Data Types
- BigQuery - STRUCT Data Type
- BigQuery - ARRAY Data Type
- BigQuery - JSON Data Type
- BigQuery - Table Metadata
- BigQuery - User-defined Functions
- Connecting to External Sources
- Integrate Scheduled Queries
- Integrate BigQuery API
- BigQuery - Integrate Airflow
- Integrate Connected Sheets
- Integrate Data Transfers
- BigQuery - Materialized View
- BigQuery - Roles & Permissions
- BigQuery - Query Optimization
- BigQuery - BI Engine
- Monitoring Usage & Performance
- BigQuery - Data Warehouse
- Challenges & Best Practices
BigQuery Standard SQL vs Legacy SQL
While SQL doesn't have underlying dependencies like Python (or any other scripting language), there are differences in the BigQuery SQL version you choose.
Since Google Cloud Platform recognizes that developers are familiar with different dialects of SQL, they have provided options for those who may be accustomed to working with a more traditional or "legacy" SQL version.
Difference Between Standard SQL and Legacy SQL
The main difference between standard SQL and legacy SQL is the mapping of types. While legacy SQL supports types that align more closely with universal data types, standard SQL types are more specific to BigQuery. For instance, BigQuery standard SQL supports a much narrower range for timestamp values.
Other differences include −
- Using backticks rather than brackets to escape special characters.
- Table references use colons rather than dots.
- Wildcard operators are not supported.
Toggling between standard and legacy SQL within the BigQuery SQL environment is easy. Before writing and executing your SQL query, simply add a comment: "#legacy" on line 1.
Standard SQL Advantages of BigQuery
Standard SQL empowers SQL developers to write queries with more flexibility and efficiency than the derivative legacy SQL dialect. Standard SQL offers more "real world" utility by providing functions and frameworks that are helpful when dealing with the "messy" data one would encounter in a professional environment.
Standard SQL facilitates the following −
- A more flexible WITH clause which enables users to reuse subqueries and CTEs multiple times within a script.
- User-defined functions that can be written in either SQL or JavaScript.
- Subqueries in the SELECT clause.
- Correlated subqueries.
- Complex data types (ARRAY & STRUCT types).
Incompatibility Between Standard and Legacy SQL
Generally, there won't be many situations in which incompatibility between standard and legacy SQL operations will cause issues. However, there is one scenario that is applicable to AirFlow users.
If using the BigQueryExecuteQuery operator, it is possible to specify whether or not to use legacy SQL. To use standard SQL, set "use_legacy_sql = FALSE".
However, if a developer fails to do this and uses a function only compatible with standard SQL, like TIMESTAMP_MILLIS() (a timestamp conversion function), it is possible that the entire query will fail.