Get Started with SQL Over Maps

6.0-SNAPSHOT 5.5 5.4 5.3 5.2 5.1 5.0

In this tutorial, you learn the basics of querying maps in SQL by running ad-hoc and batch queries on some test data.

Before You Begin

To complete this tutorial, you need the following:

Prerequisites Useful resources

A Hazelcast cluster in client/server mode and an instance of Management Center running on your local network.

Start a Local Cluster.

A connection to the SQL shell

SQL

If you want more details about any of the SQL statements used in this tutorial, see Next Steps.

Step 1. Create a Mapping to a Map

Maps don’t inherently have an associated schema or serialization format. So, before you can query data in a map, you need to create a mapping to it, using the IMap connector.

  1. In the SQL shell, configure the map connector to set up a SQL connection to a new map called cities.

    CREATE MAPPING cities (
__key INT,
countries VARCHAR,
cities VARCHAR)
TYPE IMap
OPTIONS('keyFormat'='int', 'valueFormat'='json-flat');

  2. Add some countries and their capital cities to the map.

    INSERT INTO cities VALUES
(1, 'United Kingdom','London'),
(2, 'United Kingdom','Manchester'),
(3, 'United States', 'New York'),
(4, 'United States', 'Los Angeles'),
(5, 'Turkey', 'Ankara'),
(6, 'Turkey', 'Istanbul'),
(7, 'Brazil', 'Sao Paulo'),
(8, 'Brazil', 'Rio de Janeiro');

    The VALUES clause in the example above contains multiple rows, each with 3 fields. Fields appear in the order they were defined in the CREATE MAPPING command. The __key field is the map’s key.

Step 2. Run Ad-Hoc Queries

  1. Use the SELECT statement to query all data in the map.

    SELECT * FROM cities;

    You should see the following:

    +------------+--------------------+--------------------+
|       __key|countries           |cities              |
+------------+--------------------+--------------------+
|           2|United Kingdom      |Manchester          |
|           6|Turkey              |Ankara              |
|           1|United Kingdom      |London              |
|           7|Brazil              |Sao Paulo           |
|           8|Brazil              |Rio de Janeiro      |
|           5|Turkey              |Istanbul            |
|           4|United States       |Los Angeles         |
|           3|United States       |New York            |
+------------+--------------------+--------------------+

  2. Query the countries by selecting only the countries column.

    SELECT countries FROM cities;
    +--------------------+
|countries           |
+--------------------+
|United Kingdom      |
|Turkey              |
|United Kingdom      |
|Brazil              |
|Brazil              |
|Turkey              |
|United States       |
|United States       |
+--------------------+

  3. Query only the cities by filtering on the cities column.

    SELECT cities FROM cities;
    +--------------------+
|cities              |
+--------------------+
|Manchester          |
|Ankara              |
|London              |
|Sao Paulo           |
|Rio de Janeiro      |
|Istanbul            |
|Los Angeles         |
|New York            |
+--------------------+

  4. Change the output to display cities first in alphabetical order. The AS command renames the columns to the given aliases.

    This clause does not rename the column in the table. 
    SELECT cities AS City, countries AS Country
FROM cities
ORDER BY cities;
    +--------------------+--------------------+
|City                |Country             |
+--------------------+--------------------+
|Ankara              |Turkey              |
|Istanbul            |Turkey              |
|London              |United Kingdom      |
|Los Angeles         |United States       |
|Manchester          |United Kingdom      |
|New York            |United States       |
|Rio de Janeiro      |Brazil              |
|Sao Paulo           |Brazil              |
+--------------------+--------------------+

  5. Use a filter to display only countries where the name of the city is at least 11 characters long.

    SELECT countries FROM cities WHERE LENGTH(cities) >= 11;
    +--------------------+
|countries           |
+--------------------+
|Brazil              |
|United States       |
+--------------------+

  6. Use another filter to display only cities beginning with the letter 'L' where the length is greater than 6.

    SELECT cities AS City
FROM cities
WHERE cities LIKE 'L%' AND LENGTH(cities) > 6;
    +--------------------+
|City                |
+--------------------+
|Los Angeles         |
+--------------------+

  7. Configure the map connector to create a new map table called population2020.

    CREATE MAPPING population2020 (
__key INT,
cities VARCHAR,
population INT)
TYPE IMap OPTIONS ('keyFormat'='int', 'valueFormat'='json-flat');

  8. Add the 2020 populations of the following cities.

    INSERT INTO population2020 VALUES
(1, 'London', 9304016),
(2, 'Manchester', 2730076),
(3, 'New York', 8622357),
(4, 'Los Angeles', 4085014),
(5, 'Sao Paulo', 12396372),
(6, 'Rio de Janeiro', 6775561),
(7, 'Istanbul', 14804116),
(8, 'Ankara', 3517182);

  9. Use the JOIN clause to merge results from the cities and population2020 tables so you can see which countries had the most populated capital cities in 2020.

    SELECT cities.countries AS country, cities.cities AS city, population2020.population AS population
FROM cities
JOIN population2020
ON cities.cities = population2020.cities;
    +--------------------+--------------------+------------+
|country             |city                |  population|
+--------------------+--------------------+------------+
|United Kingdom      |Manchester          |     2730076|
|Turkey              |Ankara              |     3517182|
|United Kingdom      |London              |     9304016|
|Brazil              |Sao Paulo           |    12396372|
|Brazil              |Rio de Janeiro      |     6775561|
|Turkey              |Istanbul            |    14804116|
|United States       |Los Angeles         |     4085014|
|United States       |New York            |     8622357|
+--------------------+--------------------+------------+

  10. Use the ORDER BY clause to order the results by population, largest first.

    SELECT cities.countries AS country, cities.cities AS city, population2020.population AS population
FROM cities
JOIN population2020
ON cities.cities = population2020.cities
ORDER BY population2020.population DESC;
    +--------------------+--------------------+------------+
|country             |city                |  population|
+--------------------+--------------------+------------+
|Turkey              |Istanbul            |    14804116|
|Brazil              |Sao Paulo           |    12396372|
|United Kingdom      |London              |     9304016|
|United States       |New York            |     8622357|
|Brazil              |Rio de Janeiro      |     6775561|
|United States       |Los Angeles         |     4085014|
|Turkey              |Ankara              |     3517182|
|United Kingdom      |Manchester          |     2730076|
+--------------------+--------------------+------------+

  11. Use the SUM() function to find the total population of all the cities in 2020.

    SELECT SUM(population2020.population) AS total_population
FROM population2020;

    You should see the following:

    +--------------------+
|    total_population|
+--------------------+
|            62234694|
+--------------------+

  12. Filter for cities that had a population of more than 5,000,000 in 2020.

    SELECT population2020.cities AS large_cities
FROM population2020
WHERE population2020.population > 5000000;
    +--------------------+
|large_cities        |
+--------------------+
|New York            |
|Rio de Janeiro      |
|London              |
|Istanbul            |
|Sao Paulo           |
+--------------------+

  13. Display the names of countries and the sum of the city populations. Order by population in ascending order.

    SELECT cities.countries AS country, SUM(population2020.population) AS total_population
FROM cities
JOIN population2020
ON cities.cities = population2020.cities
GROUP BY cities.countries
ORDER by sum(population2020.population);
    +--------------------+--------------------+
|country             |    total_population|
+--------------------+--------------------+
|United Kingdom      |            12034092|
|United States       |            12707371|
|Turkey              |            18321298|
|Brazil              |            19171933|
+--------------------+--------------------+

  14. Display the names of countries and the sum of the city populations where the sum is > 15000000.

    SELECT cities.countries AS country, SUM(population2020.population) AS total_population
FROM cities
JOIN population2020
ON cities.cities = population2020.cities
GROUP BY cities.countries
HAVING SUM(population2020.population) > 15000000;
    +--------------------+--------------------+
|country             |    total_population|
+--------------------+--------------------+
|Turkey              |            18321298|
|Brazil              |            19171933|
+--------------------+--------------------+

    The HAVING clause allows you to filter aggregations like you would with the WHERE clause for non-aggregated queries.

Next Steps

Learn more about querying maps with SQL.

Find out more about the statements used in this tutorial:

Explore all available SQL statements.

lifebuoy

Help and support