How to join data from different formatsΒΆ

This examples shows how to join a CSV and a JSON file.

We use the files airports.csv and trips.json files that can be found in the S3 bucket raw-tutorial.

Then execute the query:

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

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

SELECT t.reason, t.dates, start, end
FROM trips t, airports start, airports end
WHERE t.origin=start.IATA_FAA
AND t.destination=end.IATA_FAA

Note that:

The result contains a nested structure, where start and end fields contain the corresponding information on the airport. Alternative, to obtain a flat structure (i.e. in tabular form), change the SELECT part of the query to:

SELECT t.reason, t.dates,
       start.Name AS start_name,
       end.Name AS end_name
FROM trips t, airports start, airports end
WHERE t.origin=start.IATA_FAA
AND   t.destination=end.IATA_FAA