Designing a schema for observability

We can use `hasAnyTokens` also without text index but the query will perform a slow full scan of the Body column:

```
SELECT count()
FROM otel_logs
WHERE hasAllTokens(Body, ['Connection', 'accepted'])

Query id: ff0b866c-6df7-47be-9e36-795ef3888169

   ┌─count()─┐
1. │   27281 │
   └─────────┘

1 row in set. Elapsed: 0.584 sec. Processed 19.95 million rows, 3.08 GB (34.15 million rows/s., 5.27 GB/s.)
```

#### Adding a text index[​](#adding-a-text-index "Direct link to Adding a text index")

A text index on the Body column can be added during table creation:

```
CREATE TABLE otel_logs_index_body
(
        `Body` String,
        `Timestamp` DateTime,
        `ServiceName` LowCardinality(String),
        `Status` UInt16,
        `RequestProtocol` LowCardinality(String),
        `RunTime` UInt32,
        `Size` UInt32,
        `UserAgent` String,
        `Referer` String,
        `RemoteUser` String,
        `RequestType` LowCardinality(String),
        `RequestPath` String,
        `RemoteAddress` IPv4,
        `RefererDomain` String,
        `RequestPage` String,
        `SeverityText` LowCardinality(String),
        `SeverityNumber` UInt8,
         INDEX idx_body Body TYPE text(tokenizer = splitByNonAlpha) GRANULARITY 100000000
)
ENGINE = MergeTree
ORDER BY Timestamp
SETTINGS index_granularity = 8192
```

or added later using `ALTER TABLE`:

```
ALTER TABLE otel_logs ADD INDEX idx_body Body TYPE text(tokenizer = splitByNonAlpha) GRANULARITY 100000000;
ALTER TABLE otel_logs MATERIALIZE INDEX idx_body;
```

If we run the same SELECT query again, it will do a text index lookup.
The amount of accessed data reduces from gigabytes to megabytes and performance improves by approximately 45x.

```
SELECT count()
FROM otel_logs_index_body
WHERE hasAllTokens(Body, ['Connection', 'accepted'])

Query id: ebc31a94-92b3-48aa-860a-939d7e788ef4

   ┌─count()─┐
1. │   27281 │
   └─────────┘

1 row in set. Elapsed: 0.013 sec. Processed 20.41 million rows, 20.41 MB (1.59 billion rows/s., 1.59 GB/s.)
Peak memory usage: 15.23 MiB.
```

#### Using a preprocessor[​](#using-a-preprocessor "Direct link to Using a preprocessor")

In this dataset, the Body column contains a JSON-formatted string with multiple key-value pairs (e.g., `msg`, `id`, `ctx`, `attr`, etc.).

Assume we are only interested in searching within the `msg` field.
Instead of indexing the entire JSON string, we can define a preprocessor to extract only the `msg` value before tokenization.

For example:

```
 INDEX idx_text Body TYPE text(tokenizer = splitByNonAlpha,
                               preprocessor = JSONExtract(Body, 'msg', 'String'))
```

In this example the preprocessor:

- reduces the amount of text that is tokenized and indexed,
- decreases index size,
- reduces the probability of false positives, and
- improves query performance.

```
SELECT count()
FROM otel_logs_text_body_preprocessed
WHERE hasAllTokens(Body, ['Connection', 'accepted'])

Query id: f6a5cd9c-665f-4e4f-82f2-d6a4408a68a8

   ┌─count()─┐
1. │   27281 │
   └─────────┘

1 row in set. Elapsed: 0.006 sec. Processed 13.54 million rows, 13.54 MB (2.45 billion rows/s., 2.45 GB/s.)
Peak memory usage: 1.95 MiB.
```

Compared to the non-preprocessed index, performance improves by approximately 2x.

Using a preprocessor also reduces index size from gigabytes to a few hundred kilobytes, approximately 0.01% of the original size

```
SELECT
    `table`,
    formatReadableSize(data_compressed_bytes) AS compressed_size,
    formatReadableSize(data_uncompressed_bytes) AS uncompressed_size
FROM system.data_skipping_indices
WHERE startsWith(`table`, 'otel_logs')

Query id: 730e4b77-e697-40b3-a24d-67219ec42075

   ┌─table───────────────────────────────────┬─compressed_size─┬─uncompressed_size─┐
1. │ otel_logs_text_index_body_preprocessed  │ 423.98 KiB      │ 424.29 KiB        │
2. │ otel_logs_text_index_body               │ 2.76 GiB        │ 2.78 GiB          │
   └─────────────────────────────────────────┴─────────────────┴───────────────────┘
```

\*\*Other indexes for text search

Further details on secondary skip indices can be found [here](/docs/optimize/skipping-indexes#skip-index-functions).

Bloom filters for text search

Note

`ngrambf_v1` and `tokenbf_v1` indexes are no longer recommended for full text search.

The ngram and token-based bloom filter indexes [`ngrambf_v1`](/docs/optimize/skipping-indexes#bloom-filter-types) and [`tokenbf_v1`](/docs/optimize/skipping-indexes#bloom-filter-types) can be used to accelerate searches over String columns with the operators `LIKE`, `IN`, and hasToken. Importantly, the token-based index generates tokens using non-alphanumeric characters as a separator. This means only tokens (or whole words) can be matched at query time. For more granular matching, the [N-gram bloom filter](/docs/optimize/skipping-indexes#bloom-filter-types) can be used. This splits strings into ngrams of a specified size, thus allowing sub-word matching.

To evaluate the tokens that will be produced and therefore, matched, the `tokens` function can be used:

```
SELECT tokens('https://www.zanbil.ir/m/filter/b113')

┌─tokens────────────────────────────────────────────┐
│ ['https','www','zanbil','ir','m','filter','b113'] │
└───────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.008 sec.
```

The `ngram` function provides similar capabilities, where an `ngram` size can be specified as the second parameter:

```
SELECT ngrams('https://www.zanbil.ir/m/filter/b113', 3)

┌─ngrams('https://www.zanbil.ir/m/filter/b113', 3)────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ ['htt','ttp','tps','ps:','s:/','://','//w','/ww','www','ww.','w.z','.za','zan','anb','nbi','bil','il.','l.i','.ir','ir/','r/m','/m/','m/f','/fi','fil','ilt','lte','ter','er/','r/b','/b1','b11','113'] │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.008 sec.
```

For the purposes of this example we use the structured logs dataset. Suppose we wish to count logs where the `Referer` column contains `ultra`.

```
SELECT count()
FROM otel_logs_v2
WHERE Referer LIKE '%ultra%'

┌─count()─┐
│  114514 │
└─────────┘

1 row in set. Elapsed: 0.177 sec. Processed 10.37 million rows, 908.49 MB (58.57 million rows/s., 5.13 GB/s.)
```

Here we need to match on an ngram size of 3. We therefore create an `ngrambf_v1` index.

```
CREATE TABLE otel_logs_bloom
(
        `Body` String,
        `Timestamp` DateTime,
        `ServiceName` LowCardinality(String),
        `Status` UInt16,
        `RequestProtocol` LowCardinality(String),
        `RunTime` UInt32,
        `Size` UInt32,
        `UserAgent` String,
        `Referer` String,
        `RemoteUser` String,
        `RequestType` LowCardinality(String),
        `RequestPath` String,
        `RemoteAddress` IPv4,
        `RefererDomain` String,
        `RequestPage` String,
        `SeverityText` LowCardinality(String),
        `SeverityNumber` UInt8,
        INDEX idx_span_attr_value Referer TYPE ngrambf_v1(3, 10000, 3, 7) GRANULARITY 1
)
ENGINE = MergeTree
ORDER BY (Timestamp)
```

The index `ngrambf_v1(3, 10000, 3, 7)` here takes four parameters. The last of these (value 7) represents a seed. The others represent the ngram size (3), the value `m` (filter size), and the number of hash functions `k` (7). `k` and `m` require tuning and will be based on the number of unique ngrams/tokens and the probability the filter results in a true negative - thus confirming a value isn't present in a granule. We recommend [these functions](/docs/engines/table-engines/mergetree-family/mergetree#bloom-filter) to help establish these values.

If tuned correctly, the speedup here can be significant:

```
SELECT count()
FROM otel_logs_bloom
WHERE Referer LIKE '%ultra%'
┌─count()─┐
│   182   │
└─────────┘