In the previous segment, you learnt how to create internal tables. In this segment, you will learn how to create external tables and how they are stored in the HDFS.
The syntax to create an external table is almost the same as that for creating an internal table; you just need to specify the keyword “EXTERNAL” while creating an external table. You can see the query below to create an external table and load data into it:
Example
create external table if not exists user_info_external (
id int,
age int,
gender string,
profession string,
reviews int )
row format delimited fields terminated by "|"
lines terminated by "\n" stored as textfile;load data local inpath '/root/movie_ratings/u.user' into table user_info_external;Output
Once you have created an external table, you can use the following query to see its location in the HDFS:
So, there are three databases, namely, demo, demo2 and demo3, that have been created in the Hadoop warehouse. And the database ‘demo’ contains the internal (user_info) and external (user_info_external) tables.
Now, let us learn how to drop the internal and external tables.
So, in the video, Vishwa wrote the following query to see the tables available in the demo database:
Example
use demo;
show tables;Output
You can use the “drop table table_name” command to drop the tables, as shown in the snapshot below:
Next, you will learn about storage location analysis of internal and external tables in the HDFS:
You can see that although the internal table data has been deleted from the HDFS, the external table data is still there, even after you dropped the external table.
In the next segment, you will learn how to perform alter table operations and how you can copy the data of one table to another table using HQL commands.