Menu
Does Hive have a dynamic pivot functionality? I'm able to find regular pivoting (ie here) but they appear to be hard coded pivots (all values known at runtime) not dynamic (all values determined at runtime).
If it exists or someone has user defined code that they could share that would be appreciated.
922
Use of PIVOT / UNPIVOTYou can use the PIVOT and UNPIVOT operators in standard SQL, Hive, and Presto.
A dynamic range will automatically expand or contract, if new columns or rows of data are added, or data is removed. You can base a pivot table on the dynamic range. Then, when you refresh the pivot table, it will include all of the data in the range, even if new rows or columns have been added.
Apache Hive does not have direct standard UDF for transposing rows into columns. Transpose & Pivot in Hive Query can be achieved using multi-stage process. You can use collect_list() or collect_set() function and merge the multiple rows into columns and then get the result.
HiveQL doesn't support dynamic pivot like you describe. If you want to do something like this you need to use straight dynamic SQL and construct the query in whatever orchestration tool you're using.
I haven't used it, but this is apparently supported in HPL/SQL through use of the EXEC keyword. This will allow execution of strings of dynamic SQL and provides a more transactional layer on top of traditional Hive. HPL/SQL is included in Hive 2.0. If you use Hive 2.0 it should be available for you.
Personally I have always done this type of query construction in Python, which I use to drive all my operations.
184
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
