Showing NULL values after adding column in hive

Question

i am using hive-version 1.2.1. i m newbie to hive.
i have added a column to TABLE_2 and shows NULL value. i want to put DATE part from timestamp column to newly created column. i tried with below query:

ALTER TABLE table_2 ADD COLUMNS(DATE_COL string);
INSERT INTO table_2 (DATE_COL) AS SELECT SUBSTRING(TIMESTAMP_COL,-19,10) FROM table_1 ;

this is working bt still it shows NULL values in newly created DATE_COL. i want just date in DATE_COL.

table_1 has 13 columns, table_2 has 14 columns (13 + DATE_COL).

TIMESTAMP_COL :- STRING.

DATE_COL - STRING.

please tell me how to solve this problem.

Are you using substring(string|binary A, int start, int len) function properly? What makes you to use start index -19. Can you share sample records in your TIMESTAMP column in table_1? — Sachin Gaikwad
@SachinGaikwad i think sustring() starts to count from last value. 19 is total length. so if i say -19 it means it will start from first. thats what how i m doing. if u knw actual technical reason behind this then please share. — shinchaan
i would suggest to first run this query with select clause with same function and check if the values are true as per your need then go for update or insert. — Vikas Hardia

Jack Jack · Accepted Answer · 2016-02-23T07:55:25

Use UPDATE command : Syntax:

UPDATE tablename SET column = value [, column = value ...] [WHERE expression]

Hive version 0.14.0: INSERT...VALUES, UPDATE, and DELETE are now available with full ACID support.

Showing NULL values after adding column in hive

1 Answers