Get started with SQL over maps

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.

   sql

   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.

   sql

   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.

   sql

   SELECT * FROM cities;

   You should see the following:

   shell

   +------------+--------------------+--------------------+
   |       __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.

   sql

   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.

   sql

   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.

   note

   This clause does not rename the column in the table.

   sql

   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.

   sql

   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.

   sql

   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.

   sql

   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.

   sql

   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.

   sql

   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.

    sql

    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.

    sql

    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.

    sql

    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.

    sql

    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.

    sql

    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:

• CREATE MAPPING
• SELECT
• Expressions, Functions and Operators.

Explore all available SQL statements.