11. Advanced Language Features

This notebook introduces advanced language features in RAW, which are useful for structuring more complex RQL programs.

[5]:
%load_ext raw_magic

Declarations

Declarations are used to organize RQL expressions.

Given the query:

[6]:
%%rql

SELECT *
FROM READ("https://raw-tutorial.s3.amazonaws.com/airports.csv") a,
     READ("https://raw-tutorial.s3.amazonaws.com/trips.json") t
WHERE t.origin=a.IATA_FAA
[6]:
AirportIDNameCityCountryIATA_FAAICAOLatitudeLongitudeAltitudeTimezoneDSTTZreasonorigindestinationdates
departurearrival
1665Geneve CointrinGenevaSwitzerlandGVALSGG46.2380646.1089514111.0EEurope/ParisHolidaysGVAKEF2016/02/272016/03/06
1665Geneve CointrinGenevaSwitzerlandGVALSGG46.2380646.1089514111.0EEurope/ParisWorkGVASFO2016/04/102016/04/17
1665Geneve CointrinGenevaSwitzerlandGVALSGG46.2380646.1089514111.0EEurope/ParisHolidaysGVALCY2016/05/222016/05/29

… we can instead structure it using declarations as follows:

[8]:
%%rql

airports := READ("https://raw-tutorial.s3.amazonaws.com/airports.csv");

trips := READ("https://raw-tutorial.s3.amazonaws.com/trips.json");

SELECT *
FROM airports a,
     trips t
WHERE t.origin=a.IATA_FAA
[8]:
AirportIDNameCityCountryIATA_FAAICAOLatitudeLongitudeAltitudeTimezoneDSTTZreasonorigindestinationdates
departurearrival
1665Geneve CointrinGenevaSwitzerlandGVALSGG46.2380646.1089514111.0EEurope/ParisHolidaysGVAKEF2016/02/272016/03/06
1665Geneve CointrinGenevaSwitzerlandGVALSGG46.2380646.1089514111.0EEurope/ParisWorkGVASFO2016/04/102016/04/17
1665Geneve CointrinGenevaSwitzerlandGVALSGG46.2380646.1089514111.0EEurope/ParisHolidaysGVALCY2016/05/222016/05/29

The queries are equivalent in terms of execution.

Each declaration can be bound to any RAW expression/query.

[12]:
%%rql

airports := SELECT City, Name, IATA_FAA FROM READ("https://raw-tutorial.s3.amazonaws.com/airports.csv");

trips := READ("https://raw-tutorial.s3.amazonaws.com/trips.json");

SELECT *
FROM airports a,
     trips t
WHERE t.origin=a.IATA_FAA
[12]:
CityNameIATA_FAAreasonorigindestinationdates
departurearrival
GenevaGeneve CointrinGVAHolidaysGVAKEF2016/02/272016/03/06
GenevaGeneve CointrinGVAWorkGVASFO2016/04/102016/04/17
GenevaGeneve CointrinGVAHolidaysGVALCY2016/05/222016/05/29

Functions

Functions are similar to declarations, but take arguments as input:

[15]:
%%rql

airports_by_city(city: string) := SELECT * FROM READ("https://raw-tutorial.s3.amazonaws.com/airports.csv") WHERE City = city;

SELECT * FROM airports_by_city("Lisbon")
[15]:
AirportIDNameCityCountryIATA_FAAICAOLatitudeLongitudeAltitudeTimezoneDSTTZ
1638LisboaLisbonPortugalLISLPPT38.781311-9.1359193740.0EEurope/Lisbon
7752Lisbon Cruise TerminalLisbonPortugalN38.712606-9.12248300.0EEurope/Lisbon

Functions can have default arguments and can be overridden.

[21]:
%%rql

airports_by_city(city: string, n: int := 100) :=
    SELECT * FROM READ("https://raw-tutorial.s3.amazonaws.com/airports.csv") WHERE City = city LIMIT n;

SELECT * FROM airports_by_city("Lisbon")
[21]:
AirportIDNameCityCountryIATA_FAAICAOLatitudeLongitudeAltitudeTimezoneDSTTZ
1638LisboaLisbonPortugalLISLPPT38.781311-9.1359193740.0EEurope/Lisbon
7752Lisbon Cruise TerminalLisbonPortugalN38.712606-9.12248300.0EEurope/Lisbon
[22]:
%%rql

airports_by_city(city: string, n: int := 100) :=
    SELECT * FROM READ("https://raw-tutorial.s3.amazonaws.com/airports.csv") WHERE City = city LIMIT n;

SELECT * FROM airports_by_city("Lisbon", 1)
[22]:
AirportIDNameCityCountryIATA_FAAICAOLatitudeLongitudeAltitudeTimezoneDSTTZ
1638LisboaLisbonPortugalLISLPPT38.781311-9.1359193740.0EEurope/Lisbon

Anonymous functions

Anonymous functions, or functions without names, can also be defined.

These use the syntax:

\(<list of arguments>) -> <body>

For instance:

[24]:
%%rql

f := \(a: int) -> a + 1;

f(1)
[24]:
int
2

Of course defining an anonymous function then binding it to the name f isn’t very useful.

Instead, we could have done:

[25]:
%%rql

f(a: int) := a + 1;

f(1)
[25]:
int
2

However, anonymous functions have a useful feature: they can be the final expression on a virtual view, which effectively makes this into a parameterized view:

[28]:
%%view view1

\(city: String) -> SELECT * FROM READ("https://raw-tutorial.s3.amazonaws.com/airports.csv") WHERE City = city

We can ask the type of this view:

[29]:
%query_validate view1
function(
    string,
    collection(
        record(
            AirportID: int,
            Name: string,
            City: string,
            Country: string,
            IATA_FAA: string,
            ICAO: string,
            Latitude: double,
            Longitude: double,
            Altitude: int,
            Timezone: double,
            DST: string,
            TZ: string)))

The view types as a function. So to use it, we can “call it”:

[31]:
%%rql

SELECT * FROM view1("Lisbon")
[31]:
AirportIDNameCityCountryIATA_FAAICAOLatitudeLongitudeAltitudeTimezoneDSTTZ
1638LisboaLisbonPortugalLISLPPT38.781311-9.1359193740.0EEurope/Lisbon
7752Lisbon Cruise TerminalLisbonPortugalN38.712606-9.12248300.0EEurope/Lisbon