Metadata fields

This page describes access Kafka message metadata in Lenses SQL Studio.

When running queries against Kafka, Snapshot Engine enables you to access the record metadata through the special _meta facet.

These are the available meta fields:

Field	Description
_meta.offset	The offset of the record in its Kafka topic partition
_meta.partition	The Kafka topic partition of the record
_meta.timestamp	The Kafka record timestamp
_meta.__keysize	The length in bytes of the raw key stored in Kafka
_meta.__valuesize	The length in bytes of the raw value stored in Kafka

Select all the meta fields

The following query will select all the meta fields listed above:

SELECT _meta.* FROM topic

View headers

To view the value of a specific header you can run:

SELECT HEADERASSTRING("User") as user
FROM trips
LIMIT 100

Filter on record timestamp

SELECT ...
    FROM topic
WHERE _meta.timestamp > YESTERDAY()

Filter on table partition

To read records from a specific partition, the following query can be used:

SELECT ...
    FROM topic
WHERE _meta.partition = 1
    OR _meta.partition = 8

Search for a record on a specific offset

Here is the query to use when the record offset and partition are known:

SELECT ...
    FROM topic
WHERE _meta.partition = 2
    AND _meta.offset = 8
LIMIT 1

Get the latest N records per partition

This query will get the latest 100 records per partition (assuming the topic is not compacted):

SELECT ...
    FROM topic
WHERE _meta.offset >= LAST_OFFSET() - 100

This instead will get the latest 100 records for a given partition (again assuming the topic is not compacted):

SELECT ...
    FROM topic
WHERE _meta.offset >= LAST_OFFSET() - 100 
    AND _meta.partition = 2