CTAS - Create Table As Select Example in Hive

CTAS - Create Table As Select

// We can redirect the output of a Hive statement into a brand new table using CTAS.

// Create table on the fly using the data coming from another table

hive> create table sub_maths_only as select * from ohm.subject where subject_name ='Maths';

hive> select * from sub_maths_only;

OK

100 Maths 10

103 Maths 5

106 Maths 25

109 Maths 11

Time taken: 0.091 seconds, Fetched: 4 row(s) 

hive> create table sub_science_only as select * from ohm.subject where subject_name ='Science';

hive> select * from sub_science_only;

OK

101 Science 20

104 Science 5

107 Science 25

110 Science 21

Time taken: 0.09 seconds, Fetched: 4 row(s)

// Aggregation sum, count example

hive> select subject_name, sum(students_attended), count(students_attended) from sub_maths_only GROUP BY subject_name;

Maths 51 4

hive> select subject_name, sum(students_attended), count(students_attended) from sub_science_only GROUP BY subject_name;

Science 71 4

select subject_name, sum(students_attended), count(students_attended) from sub_maths_only GROUP BY subject_name 

UNION ALL

select subject_name, sum(students_attended), count(students_attended) from sub_science_only GROUP BY subject_name;

Science 71 4

Maths 51 4

//CTAS -- Redirect the output of UNION ALL into a table

// Save the UNION ALL result in a subject_result table 

create table subject_result AS

select subject_name as subject, sum(students_attended) as totalstudents from sub_maths_only GROUP BY subject_name 

UNION ALL

select subject_name  as subject, sum(students_attended) as totalstudents from sub_science_only GROUP BY subject_name;

hive> select * from subject_result;

OK

Science 71

Maths 51

Time taken: 0.08 seconds, Fetched: 2 row(s)