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. |
|
A connection to the SQL shell |
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.
-
In the SQL shell, configure the map connector to set up a SQL connection to a new map called
cities
. -
Add some countries and their capital cities to the map.
The
VALUES
clause in the example above contains multiple rows, each with 3 fields. Fields appear in the order they were defined in theCREATE MAPPING
command. The__key
field is the map’s key.
Step 2. Run Ad-Hoc Queries
-
Use the
SELECT
statement to query all data in the map.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 | +------------+--------------------+--------------------+
-
Query the countries by selecting only the
countries
column. -
Query only the cities by filtering on the
cities
column. -
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. +--------------------+--------------------+ |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 | +--------------------+--------------------+
-
Use a filter to display only countries where the name of the city is at least 11 characters long.
-
Use another filter to display only cities beginning with the letter 'L' where the length is greater than 6.
-
Configure the map connector to create a new map table called
population2020
. -
Add the 2020 populations of the following cities.
-
Use the
JOIN
clause to merge results from thecities
andpopulation2020
tables so you can see which countries had the most populated capital cities in 2020.+--------------------+--------------------+------------+ |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| +--------------------+--------------------+------------+
-
Use the
ORDER BY
clause to order the results by population, largest first.+--------------------+--------------------+------------+ |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| +--------------------+--------------------+------------+
-
Use the
SUM()
function to find the total population of all the cities in 2020.You should see the following:
-
Filter for cities that had a population of more than 5,000,000 in 2020.
-
Display the names of countries and the sum of the city populations. Order by population in ascending order.
-
Display the names of countries and the sum of the city populations where the sum is > 15000000.
The
HAVING
clause allows you to filter aggregations like you would with theWHERE
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.