在配置单元中创建和更新新列 [英] Creating and updating a new column in hive

问题描述

我是SQL和Hive的新手。我在蜂巢中有一张桌子，需要添加2列。一个是row_id，另一个是cto_id。我已经使用hive函数和一个名为cto_id的新列添加了行标识。

I am new to SQL and Hive. I have a table in hive where I need to add 2 columns. One is "row_id" and other is "cto_id". I have added the row id using hive function and a new column called "cto_id".

我想更新cto_id列中的值，例如它包含像CTO1101+ row_id

I want to updated the values in "cto_id" column such as it contains values like "CTO1101"+row_id

我该怎么做？下面是我的代码。

how can I do it? Below is my code.

-- assigning row number to each record in mu_temp_trials table
select *, row_number() over() as row_id from mu_temp_trials;

--adding new column for primary key in mu_temp_trials
alter table mu_temp_trials add columns(cto_id string);
//update mu_temp_trials set cto_id = "CTO_1101"+row_id; - I want to write this code in hive

推荐答案

帮助！

#let's say table mu_temp_trials has two columns - col1 & col2    
ALTER TABLE mu_temp_trials ADD COLUMNS(cto_id STRING, row_id STRING);


INSERT OVERWRITE TABLE mu_temp_trials
SELECT  a.col1, a.col2, concat_ws('','CTO1101',cast(a.row_id as string)) AS cto_id, cast(a.row_id as string) AS row_id
FROM (SELECT  col1, col2, row_number() over() AS row_id FROM mu_temp_trials) a;

不要忘记让我们知道它是否解决了你的问题：）

这篇关于在配置单元中创建和更新新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！