Add new column with existing partition table

 Use Alter command to add new column with CASCADE.


(1) alter table `db_name`.`table_name` add columns(flag string) CASCADE;

(2)  Please assign database name to both source and target tables to sync metastore with DB.

(3) Add column in schema with partition table.

(4) Then execute below Hive recipe.

insert overwrite table `db_name`.`emp_part1` partition(job)
SELECT 
`empno`
,`ename`
,`hiredate`
,`salary`
,`deptno`
,flag
,`job`
  FROM `prd_tbl_uacolob_hk_nsen`.`Emp_3`








Comments

Post a Comment

Popular posts from this blog

Exponential Value parsing in SQL

 If you have exponential value in you column and and you want to concert into number or string then please use below function. Cast the value in double ( in some sql  double is not supported then please try with float) but if the  length  is higher like Bigint or Long Int the use double. Use format_number function  to convert exponential value to normal number. with precision. Cast again to convert into string to remove precision value. Use replace function to remove comma(',')  between string .     Problem: col -  5.498342022637685E15 Solution: SELECT  replace(cast(format_number(cast(col_name as double),0) as string),',','') as alias   from table_name    Result :- 5498342022637680
Read more

Date format issue with spark sql

 If you have date format like  date_col 1/1/1978 0:00 6/6/1975 0:00 1/1/1978 0:00 1/1/1978 0:00 7/23/1988 0:00 1/1/1978 0:00 1/26/1961 0:00 3/3/1980 0:00 6/2/1984 0:00 1/2/1971 0:00 Solution : substring(TO_DATE(date_col ,'MM/dd/yy'),0,10) from table Result : 1978-01-01 1975-06-06 .. 1988-07-23 ..
Read more

Root path does not exist

Problem : When you execute your query you are getting below error.    Root path does not exist Root path of the dataset does not exist Solution: Please check you query it may be possibility of below cause.  Database.table in query name even you input dataset not synced with database or any metastoredb example : select * from  db1.abc Confirm if abc is table of database db. I will possible that data saved on HDFS only. In that case you need to only table e.g select * from abc 
Read more