Old tables data becomes NULL after adding a new column with a SELECT in IMPLA/HIVE

Question

I'm trying to add data to new column in Impala with a (SELECT,JOIN) query, once I add the data to the new column, I lose all the other column's data (they become NULL).

Here I create the first table:

CREATE TABLE mng_exp.KPI_LATENCE_JOUR
(
    CODEINSEE       INT,
    IMEI            BIGINT,
    SEMAINE         INT,
    MOYENNE_LATENCE INT,
    MAXIMUM_LATENCE INT,
    MINIMUM_LATENCE INT
)

I add data into the table:

INSERT INTO mng_exp.KPI_LATENCE_JOUR (CODEINSEE,IMEI, SEMAINE, MOYENNE_LATENCE,MAXIMUM_LATENCE,MINIMUM_LATENCE,TRANCHE_DE_LATENCE) 
SELECT codeinsee, device_dim__imei as IMEI,weekofyear(jour) as SEMAINE, cast(round(avg(rtt_avg_ms)) as integer) as MOYENNE_LATENCE, 
cast(round(avg(rtt_max_ms)) as integer) as MAXIMUM_LATENCE, cast(round(avg(rtt_min_ms)) as integer) as MINIMUM_LATENCE , 
CASE WHEN ( round(avg(rtt_avg_ms)) > 0 and round(avg(rtt_avg_ms)) <= 10 ) THEN 0 
WHEN ( round(avg(rtt_avg_ms)) > 10 and round(avg(rtt_avg_ms)) <= 20 ) THEN 1 
WHEN ( round(avg(rtt_avg_ms)) > 20 and round(avg(rtt_avg_ms)) <= 30 ) THEN 2 
WHEN ( round(avg(rtt_avg_ms)) > 30 ) THEN 3 END AS Tranche_de_latence

FROM mscore.mscore where operateur = 'BT_HZ' and year(jour) = 2019 group by device_dim__imei,weekofyear(jour),codeinsee 



# I  Add a new column

ALTER TABLE mng_exp.kpi_latence_jour ADD COLUMNS (srv_id BIGINT)

#Here data is good and new column srv_id is NULL 

I add data to new column:

INSERT INTO mng_exp.KPI_LATENCE_jour (srv_id) 
    SELECT CAST(dng_fai_cli_eqt_iad.srv_id AS BIGINT)
    FROM msf_exploratoire.dng_fai_cli_eqt_iad
    INNER JOIN mng_exp.kpi_latence_jour ON (dng_fai_cli_eqt_iad.num_serie = kpi_latence_jour.imei);

Here is the problem: srv_id is OK, and the old columns become NULL.

I have no query error but I lose all the old data

Answer 1

You inserting only one column. Use INSERT OVERWRITE and add all other columns:

INSERT OVERWRITE TABLE mng_exp.KPI_LATENCE_jour (CODEINSEE,IMEI, SEMAINE, MOYENNE_LATENCE,MAXIMUM_LATENCE,MINIMUM_LATENCE,TRANCHE_DE_LATENCE,srv_id) 
    SELECT b.CODEINSEE,
           b.IMEI,
           b.SEMAINE,
           b.MOYENNE_LATENCE,
           b.MAXIMUM_LATENCE,
           b.MINIMUM_LATENCE,
           b.TRANCHE_DE_LATENCE,
           CAST(a.srv_id AS BIGINT) srv_id
      FROM msf_exploratoire.dng_fai_cli_eqt_iad a
           INNER JOIN mng_exp.kpi_latence_jour  b ON (a.num_serie = b.imei)
      ;