# CLPDECODE

## CLPDECODE

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

The [CLPLogMessageDecoder](https://docs.pinot.apache.org/build-with-pinot/ingestion/stream-ingestion/clp) 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](https://docs.pinot.apache.org/build-with-pinot/ingestion/stream-ingestion/clp) 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.