View is a virtual table that represent a result-set of an SQL statement.
How to create view?
CREATE VIEW view_name AS (SELECT something FROM table_name);
How to see the records from view
SELECT * FROM view_name;
Drop view
DROP VIEW view_name;
employee table
|
Id |
name |
|
|
|
|
|
|
|
|
|
employee_address table
|
id |
emp_id |
street |
city |
country |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Let’s create employee and employee_address tables by executing below statements.
CREATE TABLE employee (id INT, name STRING);
CREATE TABLE employee_address(id INT, emp_id INT, street STRING, city STRING, country STRING);
INSERT INTO employee VALUES
(1, 'Ravi'),
(2, 'Ram'),
(3, 'Siva'),
(4, 'Sailu'),
(5, 'Gopi');
INSERT INTO employee_address VALUES
(11, 1, 'Chowdeswari street', 'Bangalore', 'India'),
(12, 2, 'Devendra Nagar', 'Hyderabad', 'India'),
(13, 3, 'Rajaji Nagar', 'Bangalore', 'India'),
(13, 4, 'Hitech city', 'Hyderabab', 'India'),
(13, 5, 'Ratan street', 'Bangalore', 'India');
hive> SELECT * FROM employee;
OK
1 Ravi
2 Ram
3 Siva
4 Sailu
5 Gopi
Time taken: 0.473 seconds, Fetched: 5 row(s)
hive> ;
hive> SELECT * FROM employee_address;
OK
11 1 Chowdeswari street Bangalore India
12 2 Devendra Nagar Hyderabad India
13 3 Rajaji Nagar Bangalore India
13 4 Hitech city Hyderabab India
13 5 Ratan street Bangalore India
Time taken: 0.039 seconds, Fetched: 5 row(s)
Create a view to see the employees in Bangalore location
CREATE VIEW bangalore_emps_view AS
SELECT e.id, e.name, ea.street, ea.city FROM employee e JOIN employee_address ea
WHERE e.id = ea.emp_id AND ea.city='Bangalore';
hive> CREATE VIEW bangalore_emps_view AS
> SELECT e.id, e.name, ea.street, ea.city FROM employee e JOIN employee_address ea
> WHERE e.id = ea.emp_id AND ea.city='Bangalore';
OK
Time taken: 0.107 seconds
Let’s
execute the command ‘SHOW TABLES’ to see whether the view is created or not.
hive> SHOW TABLES;
OK
bangalore_emps_view
employee
employee_address
user
Time taken: 0.023 seconds, Fetched: 4 row(s)
Even though ‘bangalore_emps_view’ is displayed in ‘SHOW TABLES’ output, it is not a physical table. View is a virtual table which do not hold any data itself.
Let’s get the metadata of view ‘bangalore_emps_view’ by executing below command.
hive> DESCRIBE FORMATTED bangalore_emps_view;
OK
# col_name data_type comment
id int
name string
street string
city string
# Detailed Table Information
Database: default
Owner: cloudera
CreateTime: Sun Apr 17 06:42:12 PDT 2022
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Table Type: VIRTUAL_VIEW
Table Parameters:
transient_lastDdlTime 1650202932
# Storage Information
SerDe Library: null
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
# View Information
View Original Text: SELECT e.id, e.name, ea.street, ea.city FROM employee e JOIN employee_address ea
WHERE e.id = ea.emp_id AND ea.city='Bangalore'
View Expanded Text: SELECT `e`.`id`, `e`.`name`, `ea`.`street`, `ea`.`city` FROM `default`.`employee` `e` JOIN `default`.`employee_address` `ea`
WHERE `e`.`id` = `ea`.`emp_id` AND `ea`.`city`='Bangalore'
Time taken: 0.045 seconds, Fetched: 32 row(s)
hive>
As you see above snippet, ‘Table Type’ is ‘VIRTUAL_VIEW’ for the view.
Query the view
Execute the command ‘SELECT * FROM bangalore_emps_view;’.hive> SELECT * FROM bangalore_emps_view;
Query ID = cloudera_20220417064848_752e2a6e-ab64-4d84-81ba-73e98091b8ab
Total jobs = 1
Execution log at: /tmp/cloudera/cloudera_20220417064848_752e2a6e-ab64-4d84-81ba-73e98091b8ab.log
2022-04-17 06:48:24 Starting to launch local task to process map join; maximum memory = 1013645312
2022-04-17 06:48:25 Dump the side-table for tag: 0 with group count: 5 into file: file:/tmp/cloudera/779db7a9-1669-4797-ac9b-7572b4c760b1/hive_2022-04-17_06-48-20_508_5265578296290072833-1/-local-10003/HashTable-Stage-3/MapJoin-mapfile00--.hashtable
2022-04-17 06:48:26 Uploaded 1 File to: file:/tmp/cloudera/779db7a9-1669-4797-ac9b-7572b4c760b1/hive_2022-04-17_06-48-20_508_5265578296290072833-1/-local-10003/HashTable-Stage-3/MapJoin-mapfile00--.hashtable (380 bytes)
2022-04-17 06:48:26 End of local task; Time Taken: 1.097 sec.
Execution completed successfully
MapredLocal task succeeded
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1649172504056_0030, Tracking URL = http://quickstart.cloudera:8088/proxy/application_1649172504056_0030/
Kill Command = /usr/lib/hadoop/bin/hadoop job -kill job_1649172504056_0030
Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 0
2022-04-17 06:48:34,981 Stage-3 map = 0%, reduce = 0%
2022-04-17 06:48:42,525 Stage-3 map = 100%, reduce = 0%, Cumulative CPU 1.46 sec
MapReduce Total cumulative CPU time: 1 seconds 460 msec
Ended Job = job_1649172504056_0030
MapReduce Jobs Launched:
Stage-Stage-3: Map: 1 Cumulative CPU: 1.46 sec HDFS Read: 7330 HDFS Write: 96 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 460 msec
OK
1 Ravi Chowdeswari street Bangalore
3 Siva Rajaji Nagar Bangalore
5 Gopi Ratan street Bangalore
Time taken: 23.114 seconds, Fetched: 3 row(s)
hive>
Drop the view
Execute the command ‘DROP VIEW bangalore_emps_view;’ to drop the view.hive> SHOW TABLES;
OK
bangalore_emps_view
employee
employee_address
user
Time taken: 0.038 seconds, Fetched: 4 row(s)
hive> ;
hive> ;
hive> DROP VIEW bangalore_emps_view;
OK
Time taken: 0.122 seconds
hive> ;
hive> ;
hive> SHOW TABLES;
OK
employee
employee_address
user
Time taken: 0.01 seconds, Fetched: 3 row(s)
No comments:
Post a Comment