SQL transform supported data types

This page provides details on the three data types supported as input to and output from a SQL transform: LOG_EVENT, VARIANT, and COMPLETE_SPAN. These are not scalar data types. Instead, they are structured or semi-structured data formats.

The LOG_EVENT data type

The LOG_EVENT type is a structured format representing a log event, and has the following schema:

ROW<
  id                STRING,
  eventtimestamp    TIMESTAMP_LTZ(3),
  receivedtimestamp TIMESTAMP_LTZ(3),
  message           STRING,
  severity          INT,
  tags              MAP<STRING, ARRAY<STRING>>,
  attributes        VARIANT
>

Add tags to a LOG_EVENT using a special column syntax

The SQL transform provides a special column-naming syntax to add tags to output log events. When you define a column name that starts with the tags. prefix, such as tags.service or tags.environment, the SQL transform automatically adds that tag to the output log event’s tags field.

The SQL statement in the following example uses the column-naming syntax to add these tags to the output log event:

• A tag named environment with the value production.
• A service tag set to the first value from the input log event service tag.
• A priority tag based on the severity level of the input event.

SELECT *,
  'production' as `tags.environment`,
  tags['service'][1] as `tags.service`,
  CASE WHEN severity >= 17 THEN 'high' ELSE 'normal' END as `tags.priority`
FROM logs

The following can be used to add tags with the column-naming syntax:

• String values can be assigned directly. For example 'production' as `tags.environment`.
• Any SQL expression that returns a string or array.
• To add multiple tag values, you can use a SQL array. For example, ARRAY['web', 'api'] as `tags.service`. The values in the SQL array are converted to multiple tag values.
• To add values to an existing tag, use ARRAY_APPEND. For example, ARRAY_APPEND(tags['service'], 'billing') as `tags.service` .

For more examples of using the tags column syntax, see Add tags to a log event.

Create custom log events

You can use a transformation to construct new LOG_EVENT records with specific fields. For example:

-- Explicitly construct a new LogEvent with specific fields.
SELECT
  UPPER(message) as message,
  CASE WHEN severity > 16 THEN 21 ELSE severity END as severity,
  MAP['transformed', ARRAY['true']] as tags,
  PARSE_JSON(JSON_OBJECT('original_message' VALUE message)) as attributes
FROM logs

Grepr automatically adds default values for fields that are not specified:

• id: When not specified or NULL, defaults to a new random UUID.
• eventtimestamp: When not specified or NULL, defaults to the current timestamp in milliseconds.
• receivedtimestamp: When not specified or NULL, defaults to the current timestamp in milliseconds.
• severity: When not specified or NULL, defaults to 9 (INFO level).
• message: When not specified or NULL, defaults to an empty string.
• tags: When not specified or NULL, defaults to an empty map.
• attributes: When not specified or NULL, defaults to an empty VARIANT.

The following shows an example input event to the above transformation and the resulting output event:

Input:

{
  "id": "evt1",
  "eventtimestamp": 1724214074062,
  "receivedtimestamp": 1724214074188,
  "severity": 17,
  "message": "user login successful",
  "tags": {"service": ["auth"]},
  "attributes": {}
}

Output:

{
  "id": "12345678-1234-5678-1234-567812345678", // A new generated UUID
  "eventtimestamp": 174000000000,               // Current timestamp in milliseconds
  "receivedtimestamp": 174000000002,            // Current timestamp in milliseconds
  "severity": 21,                               // Transformed severity
  "message": "USER LOGIN SUCCESSFUL",           // Transformed message
  "tags": {"transformed": ["true"]},            // New tags
  "attributes": {                               // New attributes
    "original_message": "user login successful"
  }
}

The VARIANT data type

A VARIANT data type contains timestamped, semi-structured data with flexible JSON content:

ROW<
  receivedtimestamp TIMESTAMP_LTZ(3),
  eventtimestamp TIMESTAMP_LTZ(3),
  data VARIANT
>

The COMPLETE_SPAN data type

The COMPLETE_SPAN data type represents an OpenTelemetry span with complete resource and instrumentation scope context. See Spans  in the OpenTelemetry documentation.

The related REST API object is CompleteSpan, with nested Resource, InstrumentationScope, and Span schemas. The REST API schemas use JSON property names, while SQL uses the row field names shown here.

ROW<
  receivedtimestamp TIMESTAMP_LTZ(3),
  eventtimestamp TIMESTAMP_LTZ(3),
  servicename STRING,
  resource ROW<
    attributes VARIANT,
    dropped_attributes_count INT,
    entity_refs ARRAY<ROW<
      schema_url STRING,
      type STRING,
      id_keys ARRAY<STRING>,
      description_keys ARRAY<STRING>
    >>
  >,
  instrumentationscope ROW<
    name STRING,
    version STRING,
    attributes VARIANT,
    dropped_attributes_count INT
  >,
  span ROW<
    traceIdHigh BIGINT NOT NULL,
    traceIdLow BIGINT NOT NULL,
    spanId BIGINT NOT NULL,
    traceState STRING,
    parentSpanId BIGINT,
    operationName STRING,
    spanKind STRING,
    startTimeNanos BIGINT NOT NULL,
    endTimeNanos BIGINT NOT NULL,
    attributes VARIANT,
    droppedAttributesCount INT NOT NULL,
    events ARRAY<ROW<
      timeNanos BIGINT,
      name STRING,
      attributes VARIANT,
      droppedAttributesCount INT
    >>,
    droppedEventsCount INT NOT NULL,
    links ARRAY<ROW<
      traceIdHigh BIGINT,
      traceIdLow BIGINT,
      spanId BIGINT,
      traceState STRING,
      attributes VARIANT,
      droppedAttributesCount INT,
      flags INT
    >>,
    droppedLinksCount INT NOT NULL,
    status ROW<
      message STRING,
      code STRING
    >,
    flags INT NOT NULL,
    spanKindByte TINYINT NOT NULL
  >,
  tracesignature STRING,
  duration BIGINT,
  haserror BOOLEAN,
  percentilebucket STRING,
  samplingtype INT,
  crit_path_ns BIGINT,
  crit_path_pct DOUBLE
>

When a SQL output has outputType set to COMPLETE_SPAN, Grepr maps result columns by these SQL field names. Nullable top-level metadata columns that are omitted from the query result, such as tracesignature, duration, haserror, percentilebucket, samplingtype, crit_path_ns, and crit_path_pct, are set to NULL. This lets older SQL transforms keep working when new nullable top-level span metadata fields are added.

Extra columns are rejected, except for VARIANT columns named resource.attributes, instrumentationscope.attributes, or span.attributes; those columns are merged into the corresponding nested attributes field. If you rebuild a nested resource, instrumentationscope, or span row, include every nested field in the current order. Grepr validates nested row shape before the pipeline starts, so an outdated nested row fails during pipeline validation rather than after events begin flowing.