External vs managed tables in Hadoop - Hands-On Comparison

External vs Managed Tables in Hadoop

📖 Scenario: You are working with Hadoop and Hive to manage data tables. You want to understand the difference between external and managed tables by creating examples of each type.

🎯 Goal: Create one managed table and one external table in Hive. Then, query both tables to see their contents.

📋 What You'll Learn

Create a managed table called managed_employees with columns id (int) and name (string).

Create an external table called external_employees with the same columns, pointing to a specific HDFS location /user/hive/external_employees.

Insert sample data into the managed table.

Query both tables to display their data.

💡 Why This Matters

🌍 Real World

Data engineers use managed and external tables to organize and control data storage in Hadoop environments.

💼 Career

Understanding table types is essential for managing data lifecycle and storage in big data jobs.

1

Create a managed table

Write a Hive query to create a managed table called managed_employees with columns id (int) and name (string).

Hadoop

# Your Hive query to create the managed table

Need a hint?

A managed table is created with CREATE TABLE without EXTERNAL.

2

Create an external table

Write a Hive query to create an external table called external_employees with columns id (int) and name (string), stored at HDFS location /user/hive/external_employees.

Hadoop

CREATE TABLE managed_employees (id INT, name STRING);
# Your Hive query to create the external table

Need a hint?

Use CREATE EXTERNAL TABLE and specify the LOCATION for external tables.

3

Insert data into the managed table

Write a Hive query to insert two rows into the managed_employees table: (1, 'Alice') and (2, 'Bob').

Hadoop

CREATE TABLE managed_employees (id INT, name STRING);
CREATE EXTERNAL TABLE external_employees (id INT, name STRING) LOCATION '/user/hive/external_employees';
# Your Hive query to insert data into managed_employees

Need a hint?

Use INSERT INTO TABLE with VALUES to add rows.

4

Query both tables

Write two Hive queries to select all data from managed_employees and external_employees tables and print the results.

Hadoop

CREATE TABLE managed_employees (id INT, name STRING);
CREATE EXTERNAL TABLE external_employees (id INT, name STRING) LOCATION '/user/hive/external_employees';
INSERT INTO TABLE managed_employees VALUES (1, 'Alice'), (2, 'Bob');
# Your Hive queries to select and display data from both tables

Need a hint?

Use SELECT * FROM table_name to see all rows in a table.