SQL Compatibility

Thus far we have focused on RQL (RAW Query Language) queries.

However, RAW users are not forced to use RQL. In fact, RAW also provides SQL as a first-class query language.

This SQL mode is provided for strict compatibility with SQL standards. It is also helpful when submitting queries to RAW from external tools that only "speak" SQL.

In Jupyter, this is available using the %%sql magic.

In SQL mode, however, none of the extensions seen so far are available. Therefore, to use SQL, users have first to define views in RQL, and then consume them from SQL. That's because keywords like READ, to read data from source, are not part of the SQL specification.

(In later notebooks we will see how to define materialized views and tables in RAW, which provides additional ways to make data available for the SQL mode.)

We start by defining a view over a CSV file in RQL as before:

Now that airports is available, we can query it using SQL.

For this we use %%sql instead of %%rql:

If we filter airports by city, we see the SQL syntax being used:

In RQL, we use "" for strings instead, as in:

SQL Limitations

The SQL layer is meant to conform to the SQL 2003 standard.

The major limitation with the SQL layer is that only RAW entities that are tables are made available to the SQL layer.

A "table" is, in RAW, a collection of records, whose fields are all primitive types.

The view airports is compatible with SQL.

Let's define a new view that is not SQL compatible:

This type is not SQL compatible, and therefore, not made available to query from the SQL layer.

Next: Caching