Unwrapping complex values as primitives

In this example, we will show how Lenses can be used to transform complex data types into simple primitive ones.

Setting up 

We start this tutorial by creating a topic which will hold information regarding visits to our website:

CREATE TABLE lenses_monitoring(
   _key.landing_page string
  , _key.user string
  , time_spent_s int
)
FORMAT(avro, avro);

Firstly we’ll add some data to our newly created topic:

INSERT INTO lenses_monitoring(
    _key.landing_page
    , _key.user
    , time_spent_s
) VALUES
("homepage", "anon_21", 30),
("why-lenses", "anon_32", 45),
("use-cases", "anon_56", 12),
("customers", "anon_36", 12),
("use-cases", "anon_126", 12);          

Exporting the data 

For example, let’s say we’re interested in sending this data to a service that analyses the time spent on a page and how it changes over time.

This system has a caveat though it only accepts data where keys are specified as strings and values are specified as integers.

Rather than having to reimplement our analysis system, we can create a SQL Processor that will continuously send data to a new topic in a format the target system can work with:

SET defaults.topic.autocreate=true;
INSERT INTO analysis_topic 
SELECT STREAM 
    _key.landing_page as _key
    , time_spent_s as _value
FROM lenses_monitoring;

Notice the addition of the as _key and as _value aliases; these tell lenses to “unwrap” the values; effectively making lenses write them as primitive types (string and integer respectively) instead of (in this particular case) Avro objects.

Lenses will also automatically infer the format of each topic facet, in this case it set them to STRING and INT respectively: