"Invalid SQL" when creating a SQL Processor with Lenses

General
1

I’m trying to build a SQL Processor with:

SET defaults.topic.autocreate=true;

INSERT INTO auditd_filtered
SELECT STREAM  message as _raw, log.file.path as source, host.name as host, "auditd" as sourcetype  FROM auditd
WHERE agent.name="fol-linxabbk-tefle"

I’m getting the message: SQL is invalid.

When I query the same topic in SQL Studio:

SELECT _raw, log.file.path as source, host.name as host, "auditd" as sourcetype FROM auditd

…it works correct and returns me the data. My the payload is in JSON serialization. I notice that Lenses has not been able to infer the schema from the topic, it says:

Primitive type. No schema available.

image

What am I doing wrong?

3

Seems like it’s due to the field names in the JSON event starting with a special characters which don’t conform to AVRO. In my case I have: @timestamp and _time - this is what is stopping Lenses from inferring the schema automatically.

I was able to solve the problem by generating my own schema based on an example payload event (using chatGPT to generate the schema) and then taking out the fields that start with the special characters. Finally I was able to edit the schema in Lenses in Explore.