Migration to StandardSQL

[appflowpersiste]

I am developing an integration with GoogleBigQuery and I having few queries :
1. Will LegacySQL be ever deprecated, if so then will be supporting StandardSQL only else we will be supporting both. What is your suggestion on this, is it right to support standard SQL with LegacySQL?
2. Is there any posiblitlity Google Big Query customer customer will still be using Legacy and not StandardSQL and as a result face difference in user experience.
3. Is there any reason GoogleBigQuery is still supporting legacy SQL even though it has fewer features ? And is there a way to support both as currently in the request body we have to send the #standardSQL which gives error if we are using LegacySQL dialect.

[ms4446]

Please see responses below to answers to your questions:

1. Will LegacySQL ever be deprecated?

Google Cloud has not officially announced plans to deprecate LegacySQL. However, they have emphasized that StandardSQL is the preferred SQL dialect for BigQuery. StandardSQL is a modern and robust SQL dialect that encompasses a broader range of features, including support for nested and repeated data types, subqueries in both the SELECT list and WHERE clause, and correlated subqueries.

2. Is there a possibility that Google BigQuery customers will still be using LegacySQL and not StandardSQL, leading to a difference in user experience?

Yes, there is a possibility that some  BigQuery customers might continue to use LegacySQL, especially those with existing applications or scripts that are dependent on the LegacySQL syntax. However, as Google Cloud consistently enhances StandardSQL with new features, it's anticipated that an increasing number of customers will transition to StandardSQL.

3. Why does Google BigQuery continue to support LegacySQL despite its limited features?

Google Cloud continues to support LegacySQL primarily for backward compatibility reasons. LegacySQL was BigQuery's original SQL dialect, and a significant number of customers have applications and integrations built around it. Google Cloud has committed to supporting LegacySQL as long as there's a demand for it among its user base.

Can both StandardSQL and LegacySQL be supported in the same request body?

No, it's not feasible to support both SQL dialects in a single request. Queries written in StandardSQL require the #standardSQL prefix. For queries in LegacySQL, this prefix should be excluded.

Suggestion:

I recommend transitioning to StandardSQL at your earliest convenience. Being the preferred SQL dialect for BigQuery, StandardSQL offers numerous advantages over LegacySQL. If you have existing LegacySQL queries, you can utilize the BigQuery SQL migration tool to facilitate the transition.

For integrations, consider offering two distinct endpoints: one dedicated to StandardSQL queries and another for LegacySQL queries.

Additional Benefits of StandardSQL:

• StandardSQL is more aligned with other SQL dialects, such as MySQL and PostgreSQL, simplifying the migration process to BigQuery from other database platforms.
• It supports an extended range of data types, including nested and repeated data types, enabling more intricate data structures in BigQuery.
• StandardSQL introduces several new capabilities, like subqueries in the SELECT list and WHERE clause, and correlated subqueries, making complex query formulation more straightforward.