Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ


Choose your language

InfoQ Homepage News Google Logica Aims to Make SQL Queries More Reusable and Readable

Google Logica Aims to Make SQL Queries More Reusable and Readable

This item in japanese

Logica is a Datalog-like declarative logic programming language for database querying. It supports the creation of reusable abstractions to build complex queries and compiles to SQL, thus making it suitable for wide application, in addition to Google's own BigQuery.

Writing SQL queries, explain Google engineers and Logica creators Konstantin Tretyakov and Evgeny Skvortsov, can be nightmarish:

Despite the widespread adoption, SQL is not flawless. Constructing statements from long chains of English words (which are often capitalized to keep the old-fashioned COBOL spirit of the 70s alive!) can be very verbose—a single query spanning hundreds of lines is a routine occurrence.

This state of things is the direct consequence of the lack of advanced abstraction mechanisms built into SQL, besides the very basic notions of views and functions. In particular, say Tretyakov and Skvortsov, SQL does not offer any facilities to define or import packages.

Logica attempts to provide an alternative to writing SQL using the principles of logic programming. In particular, it extends propositional logic with additional constructs, for example aggregation, which, as its name implies, makes it possible to aggregate the results of several queries together, similar to what you do with the SQL GROUP BY statement.

The basic notion in Logica is that of a logic predicate, which replaces SQL relations. In fact, Logica predicates describe the logical condition that rows of a relations must satisfy. Predicates can use structures, variables, negation, conjunction (join), disjunction (union), and, as mentioned, aggregation. Most importantly, Logica predicates can use functions, which do not add any query capabilities but make syntax cleaner and predicate composition easier.

Coming from Google, Logica is well integrated with its BigQuery database. Here is a short example of Logica syntax showing how you can use BigQuery to select popular baby names:

# Connecting predicate to a table.
BabyNames(..r) :- `bigquery-public-data.usa_names.usa_1910_current`(..r);

# Extracting an arbitrary sample from the table.
@Limit(BabyNamesSample, 11);
BabyNamesSample(..r) :- BabyNames(..r);  

NameCountByYear(name: , year:) += number :-
  BabyNames(name:, year:, number:);

# Finding most popular name for each year.
@OrderBy(TopNameByYear, "year");
TopNameByYear(year) ArgMax= name -> NameCountByYear(name:, year:);

Logica's syntax is strongly informed by Datalog's, hence Prolog's, which can make it appear rather abstruse. According to Tretyakov and Skvortsov, though, it can be a good option if you use complex SQL queries that end up being scarcely readable and would like to express them in a more manageable way. It must be noticed, though, that SQL is not just a query definition language, since it also includes data manipulation features to create new data properties, such as adding columns to a table, creating new tables, and so on.

As mentioned, currently Logica can be translated to BigQuery SQL, but support for PostgreSQL and SQLite is on the roadmap and already available experimentally.

Rate this Article