How to add a new column in a view?-CodePudding

I have this view:

CREATE VIEW VW_my_table_test AS
select
extract_dt,
path,
split_part(`path`, '/', 5) `DataBAse`,
modificationtime,
to_timestamp(accesstime, 'yyyy-MM-dd HH:mm') as accesstime ,
"replication",
datediff(from_unixtime(unix_timestamp(now()),'yyyy-MM-dd'),to_timestamp(accesstime, 'yyyy-MM-dd HH:mm'))AS QT_DIAS_SEM_ACESSO,
CASE WHEN datediff(from_unixtime(unix_timestamp(now()),'yyyy-MM-dd'),to_timestamp(accesstime, 'yyyy-MM-dd HH:mm')) < 31 then '1' ELSE '0' END AS IN_INF_HOT_ACESSO,
CASE WHEN datediff(from_unixtime(unix_timestamp(now()),'yyyy-MM-dd'),to_timestamp(accesstime, 'yyyy-MM-dd HH:mm')) > 30 and datediff(from_unixtime(unix_timestamp(now()),'yyyy-MM-dd'),to_timestamp(accesstime, 'yyyy-MM-dd HH:mm')) < 121 then '1' ELSE '0' END AS IN_INF_COLD_ACESSO,
CASE WHEN datediff(from_unixtime(unix_timestamp(now()),'yyyy-MM-dd'),to_timestamp(accesstime, 'yyyy-MM-dd HH:mm')) > 120 then '1' ELSE '0' END AS IN_INF_FROZEN_ACESSO,
CASE WHEN datediff(from_unixtime(unix_timestamp(now()),'yyyy-MM-dd'),to_timestamp(modificationtime, 'yyyy-MM-dd HH:mm')) < 31 then '1' ELSE '0' END AS IN_INF_HOT_MODIFICACAO,
CASE WHEN datediff(from_unixtime(unix_timestamp(now()),'yyyy-MM-dd'),to_timestamp(modificationtime, 'yyyy-MM-dd HH:mm')) > 30 and datediff(from_unixtime(unix_timestamp(now()),'yyyy-MM-dd'),to_timestamp(accesstime, 'yyyy-MM-dd HH:mm')) < 121 then '1' ELSE '0' END AS IN_INF_COLD_IN_INF_HOT_MODIFICACAO,
CASE WHEN datediff(from_unixtime(unix_timestamp(now()),'yyyy-MM-dd'),to_timestamp(modificationtime, 'yyyy-MM-dd HH:mm')) > 120 then '1' ELSE '0' END AS IN_INF_FROZEN_IN_INF_HOT_MODIFICACAO,

from_unixtime(unix_timestamp(now()),'yyyy-MM-dd')
from my_table_test

And I wanted to add a new column with the following replication code:

datediff(from_unixtime(unix_timestamp(now()),'yyyy-MM-dd'),to_timestamp(accesstime, 'yyyy-MM-dd HH:mm'))AS QT_DIAS_SEM_MODIFICACAO,

but he returns the error:

AnalysisException: Table already exists: dbdl_sbox_dados.VW_my_table_test

I thought about deleting this table that already exists, but I don't know if it would be the best way to do it.

How can I do it?

CodePudding user response：

I managed to solve it using DROP:

DROP VIEW VW_my_table_test

and then I ran the code already with the change.

CodePudding user response：

you can use alter view. Its better because it will fail if script isnt correct. but if you drop and miss something in script and cant resolve issue fast, some other process may fail.

ALTER VIEW dbdl_sbox_dados.VW_my_table_test 
AS
SQL WITH NEW COL