从Hive中的结果创建列 [英] Creating columns from results in Hive

问题描述

在蜂房中，我试图将特定列中的数据分成几个新列。然后我想要移动一个包含一些值的列并将这些列移动到这些新列中。如果这不明确，这里是我的意思

表名：atable

之前：

名称日期标签值

---

name1 date1 label1 1

---

name2 date2 label2 2

---

name3 date3 label3 3

---

name4 date4 label4 4

---

name5 date5 label5 5

后：

名称日期标签1 Label2 Label3 Label4 Label5

---

name1 date1 1

---

name2 date2 * * ** * ** * ** * * 2

---

name3 DATE3 *的 * ** * ** * ** * ** * ** * ** * * 3

---

name4 date4 * * ** * ** * ** * * * * ** * ** * ** * ** * 4

---

name5 date5 * * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * **** 5

---

1将在列标签1中。值2将在列标签2等中...所有的空白显然都是空的。

另外，如果这太难或不好主意，我也想把它们放到不同的表格中。在这些表格的每一个中，列将是名称，日期和标签列中的每个值。在这最后一栏是价值。

我曾尝试使用动态分区和加入，但它似乎不工作
请帮助！提前致谢。

解决方案

您是否尝试进行交叉制表？如果是这样，请参阅 PostgreSQL tablefunc contrib模块，它提供 交叉表功能。请看这里的例子。

要使用它，您需要能够通过任何Hive调用本地PostgreSQL函数。

In hive I'm trying to take the data in a particular column and then split that up into new columns. Then I want to move a column that contains some values and move those into those new columns. If this isn't clear here's what I mean

Table Name: atable

Before:

Name Date Label Value

---

name1 date1 label1 1

---

name2 date2 label2 2

---

name3 date3 label3 3

---

name4 date4 label4 4

---

name5 date5 label5 5

After:

Name Date Label1 Label2 Label3 Label4 Label5

---

name1 date1 1

---

name2 date2************ 2

---

name3 date3********************* 3

---

name4 date4************************** 4

---

name5 date5****************************************** 5

---

Value 1 would be in column label 1. Value 2 would be in column label 2, etc... All the blanks would obviously be null.

Also if this is too hard or a bad idea, I was also thinking of putting them into separate tables. In each of these tables the columns would be name, date, and each of the values from the label column. Under that last column would be the value.

I have tried using dynamic partitioning and joining, but It doesn't seem to work Please help!! Thanks in advance.

解决方案

Are you attempting to do a cross-tabulation? If so, see the PostgreSQL tablefunc contrib module, which offers the crosstab function. See the examples there.

To use this you need to be able to call native PostgreSQL functions via whatever Hive is.

这篇关于从Hive中的结果创建列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！