Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Cannot create partitioned materialized view in hive

Cannot create partitioned materialized view in hive

New Contributor

I'm getting the following error when creating materialize view with partition in hive. Without partition, it works fine.

 

An error occurred when executing the SQL command:
CREATE MATERIALIZED VIEW partition_mv_1 PARTITIONED ON (deptno)
AS SELECT name, deptno FROM emps WHERE deptno > 100 AND deptno < 200

Error while compiling statement: FAILED: NullPointerException null [SQL State=42000, DB Errorcode=40000]
1 statement failed.

Execution time: 0.16s
CREATE TABLE IF NOT EXISTS tmp.emps(
empid int,
deptno int,
name string,
salary float
)
comment 'sample emp'
stored as orc
TBLPROPERTIES ('transactional'='true');

insert into table tmp.emps
values (10001, 101, 'jane doe', 25000), (10005, 100, 'majinbu', 21000), (10006, 200, 'chris ma', 25000);

CREATE TABLE IF NOT EXISTS tmp.depts(
deptno int,
deptname string,
locationid int
)
comment 'sample dept'
stored as orc
TBLPROPERTIES ('transactional'='true');

insert into table tmp.depts
values (100, 'HR', 10), (101, 'IT', 11),(200, 'Operations', 20);

CREATE MATERIALIZED VIEW partition_mv_1 PARTITIONED ON (deptno)
AS SELECT name, deptno FROM emps WHERE deptno > 100 AND deptno < 200;

 

 Code above is from https://docs.cloudera.com/HDPDocuments/HDP3/HDP-3.1.0/using-hiveql/content/hive_create_partitioned_m...

What could i be missing here? I'm using the following Apache Hive (version 3.1.0.3.1.0.0-78) and Hive JDBC (version 3.1.0.3.1.0.0-78).

Don't have an account?
Coming from Hortonworks? Activate your account here