syslog-kvp-to-json.ksql

-- Priority
-- 1. Grab the number between lt/gt brackets (https://stackoverflow.com/questions/1454913/regular-expression-to-find-a-string-included-between-two-characters-while-exclud)
-- Message
-- 1. HIDE the spaces in a quoted string (https://stackoverflow.com/questions/36705436/regular-expression-to-select-all-whitespace-that-is-in-quotes)
-- 2. Convert spaces to commas
-- 3. Revert the HIDDEN spaces to spaces
-- Prepare the parts
-- 1. Concat the extracted priority with a key and add a comma before concat'ing with the rest of the Message
-- Finalize
-- 1. Split_to_map to get the key-value pairs

-- Sample Message
--- <42>key=1234 another="foo" spaced="some space" date=2021-10-31 time=12:45:15 srcip=192.168.100.4

SELECT SPLIT_TO_MAP(
        CONCAT(
            'priority=',
            REGEXP_EXTRACT('(?<=\<)(.*?)(?=\>)', message, 1),
            ',',
            REGEXP_REPLACE(
            REGEXP_REPLACE(
                REGEXP_REPLACE(
                    REGEXP_REPLACE(
                        REGEXP_EXTRACT('>(.*)', message, 1),
                        '\s+(?=(?:(?:[^"]*"){2})*[^"]*"[^"]*$)',
                        'SPACE'
                    ),
                    '\s',
                    ','
                ),
                'SPACE',
                ' '
            ), '"', '')
        ),
        ',',
        '='
    ) AS msg
FROM SAMPLE_MESSAGE EMIT CHANGES
LIMIT 1;