The implementation of Hive Row-to-column
This article mainly talks about "the implementation of Hive row transfer". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn "the implementation of Hive row transfer"!
Preface
In traditional relational database, no matter Oracle (after 11g) or SQLserver (after 2005), Pivot function is provided to realize row-to-column function. This paper mainly describes two ways to realize row-to-column function in Hive.
Traditional database mode
This approach is based on the way Oracle or SQLserver implemented row swapping before supporting the Pivot function. In fact, there is no change in syntax, only Hive.
With testtable (
Select 1 id,'k1' key,123 value
Union all
Select 1 minute K2' key,124 value
Union all
Select 2, K1, 2, 3, 3, 4, 2, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 4, 4, 4, 4, 4, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6,
)
Select id
Max (case when key='k1' then value else null end) K1
Max (case when key='k2' then value else null end) K2
From testtable
Group by idMap mode
The idea of this approach is to splice the fields that need to be transferred and their value fields into the map data of key-value on Hive. The specific Sql is as follows:
With testtable (
Select 1 id,'k1' key,123 value
Union all
Select 1 minute K2' key,124 value
Union all
Select 2, K1, 2, 3, 3, 4, 2, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 4, 4, 4, 4, 4, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6,
)
Select id,kv ['K1'], kv [' K2']
From (
Select id,str_to_map (concat_ws (',', collect_set (concat (key,'-', value),'-') kv
From testtable
Group by id) t Summary
Both methods can achieve the function of row-to-column, the traditional way is easy to understand, but when there are many values of key, it will be quite tedious to write, and the way of map is relatively simple. But the map way all the data are collect together, the memory requirements will be higher, while the traditional way through the aggregate function directly reduce, you can run while evaluating.
At this point, I believe that you have a deeper understanding of "the implementation of Hive row transfer". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!
