You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@impala.apache.org by ja...@apache.org on 2026/10/26 15:15:00 UTC
(impala) 01/02: IMPALA-15210: [DOCS] Documenation for PIVOT clause
This is an automated email from the ASF dual-hosted git repository.
asf-gitbox-commits pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/impala.git
commit 9c8bcc958563327f5d2492377a42d80716fd6d0f
Author: m-sanjana19 <ma...@gmail.com>
AuthorDate: Thu Oct 22 10:10:32 2026 +0530
IMPALA-15210: [DOCS] Documenation for PIVOT clause
Change-Id: Ibc715201700b24f104704a4175944d044e1c6e09
Reviewed-on: http://gerrit.cloudera.org:8080/25181
Tested-by: Impala Public Jenkins <im...@cloudera.com>
Reviewed-by: Xuebin Su <xs...@cloudera.com>
Reviewed-by: Zoltan Borok-Nagy <bo...@cloudera.com>
---
docs/impala.ditamap | 1 +
docs/shared/impala_common.xml | 3 +
docs/topics/impala_pivot.xml | 156 ++++++++++++++++++++++++++++++++++++++++++
3 files changed, 160 insertions(+)
diff --git a/docs/impala.ditamap b/docs/impala.ditamap
index 25eff2bf6f..9fe5a8a7a5 100644
--- a/docs/impala.ditamap
+++ b/docs/impala.ditamap
@@ -181,6 +181,7 @@ under the License.
<topicref href="topics/impala_subqueries.xml"/>
<topicref rev="IMPALA-5309" href="topics/impala_tablesample.xml"/>
<topicref href="topics/impala_with.xml"/>
+ <topicref href="topics/impala_pivot.xml"/>
<topicref href="topics/impala_distinct.xml"/>
</topicref>
<topicref href="topics/impala_set.xml">
diff --git a/docs/shared/impala_common.xml b/docs/shared/impala_common.xml
index 31ee972d4c..35abe85f67 100644
--- a/docs/shared/impala_common.xml
+++ b/docs/shared/impala_common.xml
@@ -3487,6 +3487,9 @@ flight_num: INT32 SNAPPY DO:83456393 FPO:83488603 SZ:10216514/11474301
<p rev="2.0.0" id="added_in_20">
<b>Added in:</b> <keyword keyref="impala200"/>
</p>
+ <p id="added_in_50" rev="5.0">
+ <b>Added in:</b>
+ <keyword keyref="impala50"/></p>
<p rev="2.0.0" id="enhanced_in_20">
<b>Added in:</b> Available in earlier Impala releases, but new capabilities were added
diff --git a/docs/topics/impala_pivot.xml b/docs/topics/impala_pivot.xml
new file mode 100644
index 0000000000..40a768960a
--- /dev/null
+++ b/docs/topics/impala_pivot.xml
@@ -0,0 +1,156 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+<!DOCTYPE concept PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd">
+<concept rev="5.0" id="impala_pivot">
+ <title>PIVOT Clause</title>
+ <prolog>
+ <metadata>
+ <data name="Category" value="Impala"/>
+ <data name="Category" value="SQL"/>
+ <data name="Category" value="Querying"/>
+ <data name="Category" value="Developers"/>
+ <data name="Category" value="Data Analysts"/>
+ </metadata>
+ </prolog>
+ <conbody>
+ <p>Impala SQL queries can use the <codeph>PIVOT</codeph> clause to transform unique column
+ values into multiple new columns for horizontal data aggregation.</p>
+ <p>The <codeph>PIVOT</codeph> clause is a query operator that allows you to reorganize data by
+ turning unique values from one column into multiple new columns. This process involves
+ applying an aggregate function to the data and redistributing the results across these new
+ column headers.</p>
+ <p>You can use the <codeph>PIVOT</codeph> clause to summarize data into a grid format such as a
+ calendar or a spreadsheet with totals calculated at the intersection of rows and columns.
+ While a standard <codeph>GROUP BY</codeph> statement aggregates data vertically into a single
+ column of rows, the <codeph>PIVOT</codeph> clause splits aggregation results horizontally
+ across multiple columns. This operation transforms a flat, repeating table into a
+ multi-dimensional summary or cross-tab report.</p>
+ <p>When tracking team sales, calculating total revenue requires evaluating two dimensions, such
+ as the team member who completed the sale (Employees) and the corresponding time period
+ (Months). You can represent this scenario using either of the following approaches:<ul>
+ <li>Standard <codeph>GROUP BY</codeph> approach (vertical) - This operation returns a long,
+ single column of results. You must scroll down through repeating names to compare
+ different months.</li>
+ <li><codeph>PIVOT</codeph> approach (grid) - This operation places the employees down the
+ left side and the months across the top header, which allows for immediate side-by-side
+ comparison.</li>
+ </ul></p>
+ <p>When you apply the <codeph>PIVOT</codeph> clause to a table reference, the following changes occur:<ul>
+ <li>The operation identifies unique values in a designated header column.</li>
+ <li>Each identified value becomes a separate column in the final output.</li>
+ <li>The result table includes the newly generated columns and all original columns from the
+ source table, excluding the header column.</li>
+ <li>The aggregation process calculates values for each intersection of the remaining rows
+ and the new columns.</li>
+ </ul></p>
+ <p conref="../shared/impala_common.xml#common/syntax_blurb"/>
+ <p>The following syntax block shows the structure of a <codeph>PIVOT</codeph>
+ operation:<codeblock>SELECT <varname>columns</varname> FROM <varname>table_name</varname>
+ PIVOT (
+ <varname>aggregate_function_1</varname>(<varname>target_column</varname>) [as <varname>alias_1</varname>], <varname>aggregate_function_2</varname>(<varname>target_column</varname>) [as <varname>alias_2</varname>], ...
+ FOR <varname>header_column</varname> IN (<varname>value_1</varname> [AS <varname>alias_1</varname>], <varname>value_2</varname> [AS <varname>alias_2</varname>], ...)
+ ) [AS <varname>result_alias</varname>];</codeblock></p>
+ <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+ <p>When you use the <codeph>PIVOT</codeph> clause, keep the following details in mind:</p>
+ <ul>
+ <li>You must provide aliases when you specify multiple aggregate expressions in a single
+ <codeph>PIVOT</codeph> clause.</li>
+ <li>The query uses all columns that are not part of the <codeph>PIVOT</codeph> definition to
+ group the rows.</li>
+ <li>The header column is removed from the final display because its data is represented in the
+ new column headers.</li>
+ <li>If the FOR column contains <codeph>NULL</codeph> values, the resulting pivot columns for
+ those specific rows display <codeph>NULL</codeph> unless the aggregation handles them
+ otherwise.</li>
+ </ul>
+ <p conref="../shared/impala_common.xml#common/example_blurb"/>
+ <p>The following example shows a <codeph>PIVOT</codeph> operation that uses a
+ <codeph>count</codeph>
+ aggregation:<codeblock>SELECT year, v1, v2 FROM functional_parquet.alltypestiny PIVOT (
+count(distinct month) FOR month IN (1 AS v1, 2 AS v2)
+) AS t;</codeblock></p>
+ <p>The following example shows how to use <codeph>PIVOT</codeph> within a Common Table
+ Expression (CTE) to handle values from a <codeph>UNION</codeph> node:</p>
+ <codeblock>WITH t1 (a, b) AS (
+VALUES (1, 2), (3, 4)
+)
+SELECT a, v2, v4 FROM t1 PIVOT (
+min(b) FOR b IN (2 AS v2, 4 AS v4)
+) AS t;</codeblock>
+ <p conref="../shared/impala_common.xml#common/added_in_50"/>
+ <p><b>Limitations:</b></p>
+ <p>The <codeph>PIVOT</codeph> clause contains specific technical restrictions and functional
+ constraints when running query operations.</p>
+ <p><b>Multiple header columns</b></p>
+ <p>The <codeph>PIVOT</codeph> clause does not support the use of multiple header columns.</p>
+ <p><b>Workaround:</b> Combine the multiple columns into a new column in a CTE, then use the
+ combined column as the header column.</p>
+ <p conref="../shared/impala_common.xml#common/example_blurb"/>
+ <codeblock>with combine_year_month as (
+ select concat(concat(cast(year as string), '_'), cast(month as string)) as year_month
+ from functional_parquet.alltypestiny
+)
+select * from combine_year_month pivot (
+ count(*) for year_month in ('2009_1', '2009_2', '2009_3', '2009_4')
+) as t;
+
++--------+--------+--------+--------+
+| 2009_1 | 2009_2 | 2009_3 | 2009_4 |
++--------+--------+--------+--------+
+| 2 | 2 | 2 | 2 |
++--------+--------+--------+--------+
+Fetched 1 row(s) in 0.23s</codeblock>
+ <p><b>Complex source table columns</b></p>
+ <p>Referencing complex columns in the source table within a <codeph>PIVOT</codeph> clause is not
+ supported.</p>
+ <p><b>Workaround:</b> None.</p>
+ <p><b>Root cause:</b> A <codeph>GROUP BY</codeph> expression cannot be used on complex types
+ without specifying a field.</p>
+ <p><b>Nested fields as header columns</b></p>
+ <p>Specifying a nested field as the header column is not supported.</p>
+ <p><b>Workaround:</b> Select the field into a new column in a CTE, then use the new column as
+ the header column.</p>
+ <p conref="../shared/impala_common.xml#common/example_blurb"/>
+ <codeblock>with select_ti as (
+ select alltypes.ti as ti
+ from functional_parquet.complextypes_structs
+)
+select * from select_ti pivot (
+ count(*) for ti in (90, 100, 123, 127)
+) as t;
+
++----+-----+-----+-----+
+| 90 | 100 | 123 | 127 |
++----+-----+-----+-----+
+| 1 | 1 | 1 | 1 |
++----+-----+-----+-----+
+Fetched 1 row(s) in 0.11s</codeblock>
+ <p><b>Constant expressions in header values</b></p>
+ <p>Specifying constant expressions other than literals in the header value list is not
+ supported.</p>
+ <p><b>Workaround:</b> Evaluate the constant expressions before writing the SQL statement in the
+ host language, such as Python, and then use the result directly in SQL.</p>
+ <p><b>Non-SELECT statements</b></p>
+ <p>Using the <codeph>PIVOT</codeph> clause in statements other than <codeph>SELECT</codeph> is
+ not supported.</p>
+ <p><b>Workaround:</b> Fetch the result of <codeph>PIVOT</codeph> using the client and then
+ insert it into a table with another SQL statement.</p>
+ </conbody>
+</concept>