将多个列合并为一个配置单元 [英] Concatenate multiple columns into one in hive

查看:74
本文介绍了将多个列合并为一个配置单元的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我需要将列值连接到单个列中。
我在变量中有列名,如 colnames = col1,col2,col3
我正在从unix外壳编写以下查询,并调用配置单元。但是,当我这样做时,我得到的只是列名,而不是那些列的值。

I need to concatenate column values into a single column. I have column names in a variable as colnames=col1,col2,col3 . I am writing the below query from a unix shell and calling the hive. But when I do this, I am getting only the column names concatenated not the values of those columns.

select concat('regexp_replace("${colnames}",",","^")) as result from table;

我希望输出为:


ABCD ^ 10 ^ XYZ

ABCD^10^XYZ

ABCD 10 XYZ 是列值)

推荐答案

您需要 concat_ws 函数以 ^ 作为分隔符来连接值。

You need concat_ws function to concatenate values with ^ as a delimiter.

带有常量的示例:

hive> select concat_ws('^','ABCD','10', 'XYZ');
OK
ABCD^10^XYZ

在shell变量后带有列名的命令替换应如下所示:

Command with column names after shell variable substitution should look like this:

 select concat_ws('^',col1,col2,col3) as result from table;

在外壳中看起来像这样:

In the shell it will look like this:

colnames=col1,col2,col3
hive -e "select concat_ws('^',${colnames}) as result from table"

如果列不是字符串,则使用shell将它们包装为字符串,这将允许concat_ws使用字符串和非字符串列。

If columns are not string, wrap them with cast as string using shell, this will allow concat_ws work with strings and not-string columns.

示例

colnames=col1,col2,col3
colnames2=$(echo "cast( $colnames as string)" | sed "s/,/ as string), cast( /g")
echo "$colnames2"

输出:

cast( col1 as string), cast( col2 as string), cast( col3 as string)

使用新变量传递像前面的示例一样进行配置。

Use new variable to pass to hive as in the previous example.

这篇关于将多个列合并为一个配置单元的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆