Support Questions

Find answers, ask questions, and share your expertise

Want to Blank out a column of a Hive Table

CREATE TABLE IF NOT EXISTS employee ( eid int, name String, salary String, destination String) COMMENT ‘Employee details’ partitioned by (batch_date date)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ STORED AS ORC;


I want to blank out Salary column.

Update isn't working. Update employee set salary="";


While creating a copy, blanking out the salary but this doesn't seem to work.

create table employee_bkup ( eid int, name String, salary String, destination String) partitioned by (batch_date date)

as

select eid, name, "", destination from employee;


I get an Error: Create Table as SELECT command cannot specify the list of columns for the target table.

2 REPLIES 2

Mentor

@PK

You can achieve that dynamically with Apache Ranger, after enabling the hive plugin for ranger. You don't have a better tool than Ranger. Masking hive columns

Ranger has policies and UDF that can help you resolve this issue using Ranger Mask Hive columns for users

HTH

Expert Contributor

You can try below query to replace empty results instead of actual value

select regexp_replace(salary, ".*", "") from employee;

If the intension is to mask a column, you can use ranger masking policy for the table column or use mask udf's

eg., select mask(salary) from employee;
Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.