#pipesyntax

[govindhtech]

SQL Pipe Syntax, Now Available In BigQuery And Cloud Logging

The revolutionary SQL pipe syntax is now accessible in Cloud Logging and BigQuery.

SQL has emerged as the industry standard language for database development. Its well-known syntax and established community have made data access genuinely accessible to everyone. However, SQL isn’t flawless, let’s face it. Several problems with SQL’s syntax make it more difficult to read and write:

Rigid structure: Subqueries or other intricate patterns are needed to accomplish anything else, and a query must adhere to a specific order (SELECT … FROM … WHERE … GROUP BY).

Awkward inside-out data flow: FROM clauses included in subqueries or common table expressions (CTE) are the first step in a query, after which logic is built outward.

Verbose, repetitive syntax: Are you sick of seeing the same columns in every subquery and repeatedly in SELECT, GROUP BY, and ORDER BY?

For novice users, these problems may make SQL more challenging. Reading or writing SQL requires more effort than should be required, even for experienced users. Everyone would benefit from a more practical syntax.

Numerous alternative languages and APIs have been put forth over time, some of which have shown considerable promise in specific applications. Many of these, such as Python DataFrames and Apache Beam, leverage piped data flow, which facilitates the creation of arbitrary queries. Compared to SQL, many users find this syntax to be more understandable and practical.

Presenting SQL pipe syntax

Google Cloud is to simplify and improve the usability of data analysis. It is therefore excited to provide pipe syntax, a ground-breaking invention that enhances SQL in BigQuery and Cloud Logging with the beauty of piped data flow.

Pipe syntax: what is it?

In summary, pipe syntax is an addition to normal SQL syntax that increases the flexibility, conciseness, and simplicity of SQL. Although it permits applying operators in any sequence and in any number of times, it provides the same underlying operators as normal SQL, with the same semantics and essentially the same syntax.

How it operates:

FROM can be used to begin a query.

The |> pipe sign is used to write operators in a consecutive fashion.

Every operator creates an output table after consuming its input table.

Standard SQL syntax is used by the majority of pipe operators:

LIMIT, ORDER BY, JOIN, WHERE, SELECT, and so forth.

It is possible to blend standard and pipe syntax at will, even in the same query.

Impact in the real world at HSBC

After experimenting with a preliminary version in BigQuery and seeing remarkable benefits, the multinational financial behemoth HSBC has already adopted pipe syntax. They observed notable gains in code readability and productivity, particularly when working with sizable JSON collections.

Benefits of integrating SQL pipe syntax

SQL developers benefit from the addition of pipe syntax in several ways. Here are several examples:

Simple to understand

It can be difficult to learn and accept new languages, especially in large organizations where it is preferable for everyone to utilize the same tools and languages. Pipe syntax is a new feature of the already-existing SQL language, not a new language. Because pipe syntax uses many of the same operators and largely uses the same syntax, it is relatively easy for users who are already familiar with SQL to learn.

Learning pipe syntax initially is simpler for users who are new to SQL. They can utilize those operators to express their intended queries directly, avoiding some of the complexities and workarounds needed when writing queries in normal SQL, but they still need to master the operators and some semantics (such as inner and outer joins).

Simple to gradually implement without requiring migrations

As everyone knows, switching to a new language or system may be costly, time-consuming, and prone to mistakes. You don’t need to migrate anything in order to begin using pipe syntax because it is a part of GoogleSQL. All current queries still function, and the new syntax can be used sparingly where it is useful. Existing SQL code is completely compatible with any new SQL. For instance, standard views defined in standard syntax can be called by queries using pipe syntax, and vice versa. Any current SQL does not become outdated or unusable when pipe syntax is used in new SQL code.

No impact on cost or performance

Without any additional layers (such translation proxies), which might increase latency, cost, or reliability issues and make debugging or tweaking more challenging, pipe syntax functions on well-known platforms like BigQuery.

Additionally, there is no extra charge. SQL’s declarative semantics still apply to queries utilizing pipe syntax, therefore the SQL query optimizer will still reorganize the query to run more quickly. Stated otherwise, the performance of queries written in standard or pipe syntax is usually identical.

For what purposes can pipe syntax be used?

Pipe syntax enables you to construct SQL queries that are easier to understand, more effective, and easier to maintain, whether you’re examining data, establishing data pipelines, making dashboards, or examining logs. Additionally, you may use pipe syntax anytime you create queries because it supports the majority of typical SQL operators. A few apps to get you started are as follows:

Debugging queries and ad hoc analysis

When conducting data exploration, you usually begin by examining a table’s rows (beginning with a FROM clause) to determine what is there. After that, you apply filters, aggregations, joins, ordering, and other operations. Because you can begin with a FROM clause and work your way up from there, pipe syntax makes this type of research really simple. You can view the current results at each stage, add a pipe operator, and then rerun the query to view the updated results.

Debugging queries is another benefit of using pipe syntax. It is possible to highlight a query prefix and execute it, displaying the intermediate result up to that point. This is a good feature of queries in pipe syntax: every query prefix up to a pipe symbol is also a legitimate query.

Lifecycle of data engineering

Data processing and transformation become increasingly difficult and time-consuming as data volume increases. Building, modifying, and maintaining a data pipeline typically requires a significant technical effort in contexts with a lot of data. Pipe syntax simplifies data engineering with its more user-friendly syntax and linear query structure. Bid farewell to the CTEs and highly nested queries that tend to appear whenever standard SQL is used. This latest version of GoogleSQL simplifies the process of building and managing data pipelines by reimagining how to parse, extract, and convert data.

Using plain language and LLMs with SQL

For the same reasons that SQL can be difficult for people to read and write, research indicates that it can also be difficult for large language models (LLMs) to comprehend or produce. Pipe syntax, on the other hand, divides inquiries into separate phases that closely match the intended logical data flow. A desired data flow may be expressed more easily by the LLM using pipe syntax, and the generated queries can be made more simpler and easier for humans to understand. This also makes it much easier for humans to validate the created queries.

Because it’s much simpler to comprehend what’s happening and what’s feasible, pipe syntax also enables improved code assistants and auto-completion. Additionally, it allows for suggestions for local modifications to a single pipe operator rather than global edits to an entire query. More natural language-based operators in a query and more intelligent AI-generated code suggestions are excellent ways to increase user productivity.

Discover the potential of pipe syntax right now

Because SQL is so effective, it has been the worldwide language of data for 50 years. When it comes to expressing queries as declarative combinations of relational operators, SQL excels in many things.

However, that does not preclude SQL from being improved. By resolving SQL’s primary usability issues and opening up new possibilities for interacting with and expanding SQL, pipe syntax propels SQL into the future. This has nothing to do with creating a new language or replacing SQL. Although SQL with pipe syntax is still SQL, it is a better version of the language that is more expressive, versatile, and easy to use.

Read more on Govindhtech.com

[govindhtech]

An Introduction Of Pipe Syntax In BigQuery And Cloud Logging

Organizations looking to improve user experiences, boost security, optimize performance, and comprehend application behavior now find that log data is a priceless resource. However, the sheer amount and intricacy of logs produced by contemporary applications can be debilitating.

Google Cloud is to give you the most effective and user-friendly solutions possible so you can fully utilize your log data. Google Cloud is excited to share with us a number of BigQuery and Cloud Logging advancements that will completely transform how you handle, examine, and use your log data.

Pipe syntax

An improvement to GoogleSQL called pipe syntax allows for a linear query structure that makes writing, reading, and maintaining your queries simpler.

Pipe syntax is supported everywhere in GoogleSQL writing. The operations supported by pipe syntax are the same as those supported by conventional GoogleSQL syntax, or standard syntax, such as joining, filtering, aggregating and grouping, and selection. However, the operations can be applied in any sequence and many times. Because of the linear form of pipe syntax, you may write queries so that the logical steps taken to construct the result table are reflected in the order in which the query syntax is written.

Pipe syntax queries are priced, run, and optimized in the same manner as their standard syntax equivalents. To minimize expenses and maximize query computation, adhere to the recommendations when composing queries using pipe syntax.

There are problems with standard syntax that can make it challenging to comprehend, write, and maintain. The way pipe syntax resolves these problems is illustrated in the following table:

SQL for log data reimagined with BigQuery pipe syntax

The days of understanding intricate, layered SQL queries are over. A new era of SQL is introduced by BigQuery pipe syntax, which was created with the semi-structured nature of log data in mind. The top-down, intuitive syntax of BigQuery’s pipe syntax is modeled around the way you typically handle data manipulations. According to Google’s latest research, this method significantly improves the readability and writability of queries. The pipe sign (|>) makes it very simple to visually distinguish between distinct phases of a query, which makes understanding the logical flow of data transformation much easier. Because each phase is distinct, self-contained, and unambiguous, your questions become easier to understand for both you and your team.

The pipe syntax in BigQuery allows you to work with your data in a more efficient and natural way, rather than merely writing cleaner SQL. Experience quicker insights, better teamwork, and more time spent extracting value rather than wrangling with code.

This simplified method is very effective in the field of log analysis.

The key to log analysis is investigation. Rarely is log analysis a simple question-answer process. Finding certain events or patterns in mountains of data is a common task when analyzing logs. Along the way, you delve deeper, learn new things, and hone your strategy. This iterative process is embraced by pipe syntax. To extract those golden insights, you can easily chain together filters (WHERE), aggregations (COUNT), and sorting (ORDER BY). Additionally, you can simply modify your analysis on the fly by adding or removing phases as you gain new insights from your data processing.

Let’s say you wish to determine how many users in January were impacted by the same faults more than 100 times in total. The data flows through each transformation as demonstrated by the pipe syntax’s linear structure, which starts with the table, filters by dates, counts by user ID and error type, filters for errors more than 100, and then counts the number of users impacted by the same faults.

— Pipe Syntax FROM log_table |> WHERE datetime BETWEEN DATETIME ‘2024-01-01’ AND ‘2024-01-31’ |> AGGREGATE COUNT(log_id) AS error_count GROUP BY user_id, error_type |> WHERE error_count>100 |> AGGREGATE COUNT(user_id) AS user_count GROUP BY

A subquery and non-linear structure are usually needed for the same example in standard syntax.

Currently, BigQuery pipe syntax is accessible in private preview. Please use this form to sign up for a private preview and watch this introductory video.

Beyond syntax: adaptability and performance

BigQuery can now handle JSON with more power and better performance, which will speed up your log analytics operations even more. Since most logs contain json data, it anticipate that most customers will find log analytics easier to understand as a result of these modifications.

Enhanced Point Lookups: Significantly speed up queries that filter on timestamps and unique IDs by use BigQuery’s numeric search indexes to swiftly identify important events in large datasets.

Robust JSON Analysis: With BigQuery’s JSON_KEYS function and JSONPath traversal capability, you can easily parse and analyze your JSON-formatted log data. Without breaking a sweat, extract particular fields, filter on nested data, and navigate intricate JSON structures.

JSON_KEYS facilitates schema exploration and discoverability by removing distinct JSON keys from JSON data.Query Results JSON_KEYS(JSON '{"a":{"b":1}}')["a", "a.b"]JSON_KEYS(JSON '{"a":[{"b":1}, {"c":2}]}', mode => "lax")["a", "a.b", "a.c"]JSON_KEYS(JSON '[[{"a":1},{"b":2}]]', mode => "lax recursive")["a", "b"]

You don’t need to use verbose UNNEST to download JSON arrays when using JSONPath with LAX modes. How to retrieve every phone number from the person field, both before and after, is demonstrated in the example below:

Log Analytics for Cloud Logging: Completing the Picture

Built on top of BigQuery, Log Analytics in Cloud Logging offers a user interface specifically designed for log analysis. By utilizing the JSON capabilities for charting, dashboarding, and an integrated date/time picker, Log Analytics is able to enable complex queries and expedite log analysis. It is also adding pipe syntax to Log Analytics (in Cloud Logging) to make it easier to include these potent features into your log management process. With the full potential of BigQuery pipe syntax, improved lookups, and JSON handling, you can now analyze your logs in Log Analytics on a single, unified platform.

The preview version of Log Analytics (Cloud Logging) now allows the use of pipe syntax.

Unlock log analytics’ future now

The combination of BigQuery and Cloud Logging offers an unparalleled method for organizing, examining, and deriving useful conclusions from your log data. Discover the power of these new skills by exploring them now.

Using pipe syntax for intuitive querying: an introductory video and documentation

Cloud logging’s Log Analytics provides unified log management and analysis.

Lightning-quick lookups using numeric search indexes – Support

JSON_KEYS and JSON_PATH allow for seamless JSON analysis

Read more on Govindhtech.com