# CLPDECODE

## CLPDECODE

Reconstructs (decodes) the value of a CLP-encoded field from its component columns.

The [CLPLogMessageDecoder](/build-with-pinot/ingestion/stream-ingestion/clp.md) can encode fields into a set of three columns:

* `<field>_logtype`
* `<field>_dictionaryVars`
* `<field>_encodedVars`

where `<field>` is the field's name before encoding. We refer to such a set of columns as a column group.

### Signatures

> CLPDECODE(colGroupName)
>
> CLPDECODE(colGroupName, defaultValue)
>
> CLPDECODE(colGroupName\_logtype, colGroupName\_dictionaryVars, colGroupName\_encodedVars)
>
> CLPDECODE(colGroupName\_logtype, colGroupName\_dictionaryVars, colGroupName\_encodedVars, defaultValue)

* The syntax lets you specify the name of a column group or all columns within the column group.
  * To use the syntax where you only specify the column group's name, you need to enable an additional query rewriter as described [below](#enable-the-column-group-syntax).
* `defaultValue` is optional and used when a column group can't be decoded for some reason (e.g., it's null).

### Usage Examples

Consider a record that contains a "message" field with the following value:

> INFO Task task\_12 assigned to container: \[ContainerID:container\_15], operation took 0.335 seconds. 8 tasks remaining.

[CLPLogMessageDecoder](/build-with-pinot/ingestion/stream-ingestion/clp.md) encodes this information into 3 columns:

| message\_logtype                                                                                              | message\_dictionaryVars        | message\_encodedVars     |
| ------------------------------------------------------------------------------------------------------------- | ------------------------------ | ------------------------ |
| INFO Task \x12 assigned to container: \[ContainerID:\x12], operation took \x13 seconds. \x11 tasks remaining. | \["task\_12", "container\_15"] | \[0x190000000000014f, 8] |

Then we can use `CLPDECODE` as follows:

```sql
SELECT CLPDECODE(message) AS message
FROM myTable
```

| message                                                                                                                  |
| ------------------------------------------------------------------------------------------------------------------------ |
| INFO Task task\_12 assigned to container: \[ContainerID:container\_15], operation took 0.335 seconds. 8 tasks remaining. |

## Enable the column-group syntax

To use the `CLPDECODE` syntax that only specifies the column group name, you must configure the Pinot broker with an additional query rewriter as follows:

```properties
pinot.broker.query.rewriter.class.names=org.apache.pinot.sql.parsers.rewriter.CompileTimeFunctionsInvoker,org.apache.pinot.sql.parsers.rewriter.SelectionsRewriter,org.apache.pinot.sql.parsers.rewriter.PredicateComparisonRewriter,org.apache.pinot.sql.parsers.rewriter.ClpRewriter,org.apache.pinot.sql.parsers.rewriter.AliasApplier,org.apache.pinot.sql.parsers.rewriter.OrdinalsUpdater,org.apache.pinot.sql.parsers.rewriter.NonAggregationGroupByToDistinctQueryRewriter
```

This adds the `ClpRewriter` to the default set of query rewriters. Note that the `ClpRewriter` is placed before the `AliasApplier` so that any aliasing of CLP-encoded fields happens only after the `CLPDECODE` rewrite.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.pinot.apache.org/functions/clp/clpdecode.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.