SPLIT_TO_MAP()

Concepts covered: String Parsing

Raw logs often pack many fields into a single string. A URL query string reads utm_source=mail&utm_campaign=spring&ref=42, a logfmt line reads level=warn status=502, and a signature header reads t=1710513000,v1=9f86d081. All 3 share the same layout: entries separated by a delimiter, and inside each entry a key and a value separated by a second delimiter. SPLIT_TO_MAP(text, entry_delimiter, key_value_delimiter) reads that layout and returns a MAP(VARCHAR, VARCHAR), the key-value type from the data types track. In a map each key appears once and holds one value, and the subscript m['key'] returns that value, so each field becomes a lookup by name.

SELECT
  SPLIT_TO_MAP('utm_source=mail&utm_campaign=spring&ref=42', '&', '=') AS params,
  SPLIT_TO_MAP('utm_source=mail&utm_campaign=spring&ref=42', '&', '=')['ref'] AS ref_value,
  CAST(SPLIT_TO_MAP('utm_source=mail&utm_campaign=spring&ref=42', '&', '=')['ref'] AS INTEGER) + 1 AS ref_plus_one

Order matters: the delimiter between entries comes second, and the delimiter between a key and its value comes third. Every value comes back as VARCHAR, so ref_value is the string '42', and arithmetic on it needs a CAST first, as ref_plus_one shows. Keys are case-sensitive: ['Ref'] and ['ref'] are different lookups.

Parsing a header column row by row

A webhook log keeps each delivery's whole signature header in a single VARCHAR column. The query below keeps merchant m_7, then reads the t entry as the signing timestamp and the v1 entry as the digest. The digests here are cut to 8 characters to fit the frame; real ones run 64.

SELECT
  event_id,
  CAST(SPLIT_TO_MAP(sig_header, ',', '=')['t'] AS BIGINT) AS signed_at,
  SPLIT_TO_MAP(sig_header, ',', '=')['v1'] AS v1_digest
FROM webhook_log
WHERE merchant_id = 'm_7'

Execution order puts WHERE ahead of SELECT, so evt_103 is gone before any parsing happens. The query also calls SPLIT_TO_MAP twice per row, once per field. A CTE that parses once and names the map keeps every field a single subscript away:

WITH parsed AS (
  SELECT
    event_id,
    SPLIT_TO_MAP(sig_header, ',', '=') AS sig
  FROM webhook_log
)
SELECT
  event_id,
  sig['t'] AS signed_at,
  sig['v1'] AS v1_digest
FROM parsed

Inputs that break the parse

SPLIT_TO_MAP is strict about layout. It returns NULL for a NULL input and accepts empty values, but any entry it cannot split into exactly 1 key and 1 value fails the entire query, every good row included. With & between entries and = inside them:

What happens when this query runs?

SELECT
  SPLIT_TO_MAP('plan=pro;seats=5;plan=team', ';', '=')['plan'] AS plan

• It returns 'pro', because the first entry for a key is kept
• It returns 'team', because a later entry overwrites an earlier one
• It fails with a duplicate key error, and no rows come back
• It returns NULL, because the key plan is ambiguous

Spacing causes the quietest failures. Hand-written log lines often put a space after each ;, and the split keeps that space as part of the next key. Run each tab against the same 2 lines:

Raw ;

Splitting on ';' leaves the following space at the front of every later key, so this read has to ask for ' status', space first.

SELECT
  line_id,
  SPLIT_TO_MAP(line, ';', '=')[' status'] AS status
FROM app_log

REPLACE

REPLACE turns every '; ' into ';' before the split, so the keys come out clean and ['status'] works.

SELECT
  line_id,
  SPLIT_TO_MAP(REPLACE(line, '; ', ';'), ';', '=')['status'] AS status
FROM app_log

Wider delim

A delimiter can be several characters long. Splitting on '; ' works while every line puts exactly 1 space after each semicolon.

SELECT
  line_id,
  SPLIT_TO_MAP(line, '; ', '=')['status'] AS status
FROM app_log

Whichever version the data calls for, the arguments keep their jobs: the text first, then the delimiter between entries, then the one between each key and its value. Swapping the last 2 on 'page=2&size=50' makes it split on = instead. Ahead of 2&size and 50 comes the entry page, which holds no &, so the query fails. Every value still comes back as VARCHAR, so a number read from the map needs a CAST before any arithmetic.

About this lesson

This section is part of the String Functions: 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.