MAP data type for key-value pairs

Concepts covered: Maps

Client attributes change faster than schemas. iOS sends app_version and Android adds network. Meanwhile a test build ships flags that nobody will read next month. A column per attribute means a schema change for each new key and rows that are NULL in most columns. A MAP column holds a set of key-value pairs on each row, so every row carries exactly the keys it has.

MAP(VARCHAR, VARCHAR) names a key type and a value type. All keys in a map share the first, all values share the second, and a key appears at most once per map. You build a map from 2 arrays whose positions pair up: MAP(ARRAY['os', 'network'], ARRAY['android', 'wifi']) holds os as android and network as wifi.

Subscript or element_at

A key can be read 2 ways, and they part company on a missing key. The subscript client_props['os'] fails the whole query when any row lacks the key. element_at(client_props, 'network') returns NULL for that row and carries on. Predict which views get a NULL network, then run it.

SELECT
  view_id,
  client_props['os'] AS os,
  element_at(client_props, 'network') AS network,
  cardinality(client_props) AS prop_count,
  map_keys(client_props) AS prop_keys
FROM reel_views
ORDER BY view_id

Views 101 and 103 send no network key, so element_at returns NULL for them. Written as client_props['network'], the same query would fail on the first view lacking the key with a key-not-present error, which is why subscripts belong only on keys every row is guaranteed to carry, such as os here. cardinality counts the pairs in each map and map_keys lists their keys, a fast way to see which attributes each client sends.

For a row whose client_props holds only the key os, what does buffer_ms come out as?

SELECT
  CAST(element_at(client_props, 'buffer_ms') AS INTEGER) AS buffer_ms
FROM reel_views

• NULL: element_at returns NULL, and CAST passes NULL through
• An error: the key is missing, so the query stops at that row
• Zero: a missing number is cast to INTEGER's default of 0
• An error: CAST cannot turn a NULL into an INTEGER value

The price of flexibility

A MAP column

• A new key needs no schema change
• Every row stores its key names, so app_version costs 11 bytes plus a length on each row
• Values share a single type: a number arrives as text and needs a CAST
• A misspelled key read with element_at returns NULL, with no error

A column per attribute

• Each new attribute is a schema change
• The name lives in the schema, so rows hold values only
• Each column has its own type, checked when data is written
• A misspelled column fails the query at once

The second row of that comparison is a cost you can price with the storage method: the key app_version costs 15 bytes on every row, 11 for the characters and 4 for the length, so at 10 billion rows a day it adds 150 GB of decoded key text daily, before any value is counted. Repeated keys compress well on disk, but every query that opens the map handles them again.

About this lesson

This section is part of the Data Types: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.