pivot

Aggregates records in a pivot table format by specifying row and column group fields.

Command properties

Syntax

Options

parallel=BOOL

Whether to use parallel aggregation. Specify t or f.

Target

AGG_FUNC [as ALIAS], ...

Aggregate functions and optional aliases. Separate multiple aggregate functions with commas (,).

{rows|by} FIELD, ...

Row group fields. Use the rows or by keyword. Separate multiple fields with commas (,).

{cols|for} FIELD, ...

Column group fields. Use the cols or for keyword. Separate multiple fields with commas (,).

Error codes

Parse errors

Runtime errors

N/A

Description

The pivot command groups input records by combinations of row and column group fields and applies aggregate functions to each group. The unique values of the column group field become the output field names, producing a pivot table result.

An error occurs when the number of unique values in the column group field exceeds 1,000.

rows and by, and cols and for, are equivalent keywords respectively. The order can be specified freely.

In a distributed environment, the final aggregation is performed on the Control Node.

The pivot command accumulates aggregation results in an in-memory buffer while receiving input records. The default buffer size is 50,000 records when column group fields are present, or 100,000 records when they are absent. When the buffer exceeds this threshold, intermediate results are flushed to disk. You can change this value using the system property araqne.logdb.aggregate_flush_size in the format with-cols:without-cols. Completed aggregation results are output in batches of 2,000 records.

Examples

1. Pivot by row and column

   table duration=1d web_logs
   | pivot count rows method cols status
   

   Outputs the request count for each combination of HTTP method (rows) and status code (columns) as a pivot table.

2. Using the by and for keywords

   table duration=1d web_logs
   | pivot sum(bytes) as total_bytes by method for status
   

   Uses the by and for keywords to output the total transmitted bytes per HTTP method and status code as a pivot table.

3. Pivoting inline data

   json "[{'dept': 'sales', 'quarter': 'Q1', 'amount': 100}, {'dept': 'sales', 'quarter': 'Q2', 'amount': 200}, {'dept': 'dev', 'quarter': 'Q1', 'amount': 150}, {'dept': 'dev', 'quarter': 'Q2', 'amount': 180}]"
   | pivot sum(amount) rows dept cols quarter
   

   Outputs the total amount for each combination of department (rows) and quarter (columns) as a pivot table.

4. Using multiple aggregate functions

   table duration=1d web_logs
   | pivot count, avg(bytes) as avg_bytes rows method cols status
   

   Outputs the request count and average bytes for each combination of HTTP method (rows) and status code (columns) as a pivot table.