External vs managed tables in Hadoop - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a managed table in Hive.

Hadoop

CREATE TABLE employees (id INT, name STRING) [1] PARQUET;

Drag options to blanks, or click blank then click option'

ASTORED AS

BEXTERNAL

CLOCATION

DPARTITIONED BY

Attempts:

3 left

💡 Hint

Common Mistakes

Using EXTERNAL keyword when creating a managed table.

Specifying LOCATION without EXTERNAL keyword.

The STORED AS clause specifies the file format for a managed table in Hive.

2fill in blank

medium

Complete the code to create an external table in Hive.

Hadoop

CREATE [1] TABLE logs (event STRING) LOCATION '/data/logs';

Drag options to blanks, or click blank then click option'

AGLOBAL

BMANAGED

CTEMPORARY

DEXTERNAL

Attempts:

3 left

💡 Hint

Common Mistakes

Using MANAGED keyword for external tables.

Omitting EXTERNAL keyword but specifying LOCATION.

The EXTERNAL keyword creates an external table that points to data stored outside Hive's control.

3fill in blank

hard

Fix the error in the code to drop an external table without deleting data.

Hadoop

DROP TABLE [1] logs;

Drag options to blanks, or click blank then click option'

APURGE

BIF EXISTS

CEXTERNAL

DCASCADE

Attempts:

3 left

💡 Hint

Common Mistakes

Using EXTERNAL keyword in DROP TABLE statement.

Using PURGE which deletes data files.

Using IF EXISTS avoids errors if the table does not exist. Dropping an external table does not delete data by default.

4fill in blank

hard

Fill both blanks to create an external table with a specified location.

Hadoop

CREATE [1] TABLE sales (id INT, amount FLOAT) [2] '/user/data/sales';

Drag options to blanks, or click blank then click option'

AEXTERNAL

BSTORED AS

CLOCATION

DPARTITIONED BY

Attempts:

3 left

💡 Hint

Common Mistakes

Using STORED AS instead of LOCATION for data path.

Omitting EXTERNAL keyword for external tables.

External tables require the EXTERNAL keyword and the LOCATION clause to specify data path.

5fill in blank

hard

Fill the blanks to create a managed table stored as ORC format.

Hadoop

CREATE TABLE inventory (item STRING, qty INT) [1] ORC [2];

Drag options to blanks, or click blank then click option'

AMANAGED

BSTORED AS

CTBLPROPERTIES ('transactional'='true')

DEXTERNAL

Attempts:

3 left

💡 Hint

Common Mistakes

Using EXTERNAL keyword for managed tables.

Omitting TBLPROPERTIES for transactional tables.

Managed tables are created using CREATE TABLE. The format is set with STORED AS. Table properties can enable transactions.

Practice

1. What is the main difference between a managed table and an external table in Hadoop?

easy

A. Managed tables do not store any data, external tables store all data inside Hadoop.

B. External tables store data inside Hadoop, managed tables store data outside Hadoop.

C. Managed tables store and manage data files within Hadoop, while external tables only manage metadata and keep data files outside Hadoop.

D. There is no difference; both manage data files the same way.

Solution

1. Step 1: Understand managed table behavior

   Managed tables store data files inside Hadoop's warehouse directory and Hadoop controls their lifecycle.

2. Step 2: Understand external table behavior

   External tables only manage metadata in Hadoop but keep data files at an external location you specify.

3. Final Answer:

   Managed tables store and manage data files within Hadoop, while external tables only manage metadata and keep data files outside Hadoop. -> Option C

4. Quick Check:

   Managed = data + metadata, External = metadata only [OK]

Hint: Managed = Hadoop controls data; External = you control data location [OK]

Common Mistakes:

• Confusing where data files are stored
• Thinking external tables delete data on drop
• Assuming managed tables don't store data

2. Which of the following is the correct HiveQL syntax to create an external table?

easy

A. CREATE TABLE my_table (id INT, name STRING) LOCATION '/user/data' EXTERNAL;

B. CREATE TABLE my_table EXTERNAL (id INT, name STRING) LOCATION '/user/data';

C. CREATE TABLE my_table (id INT, name STRING) EXTERNAL LOCATION '/user/data';

D. CREATE EXTERNAL TABLE my_table (id INT, name STRING) LOCATION '/user/data';

Solution

1. Step 1: Recall correct syntax for external table creation

   The keyword EXTERNAL comes immediately after CREATE and before TABLE.

2. Step 2: Check location clause placement

   The LOCATION clause specifies the external data path and comes after the column definitions.

3. Final Answer:

   CREATE EXTERNAL TABLE my_table (id INT, name STRING) LOCATION '/user/data'; -> Option D

4. Quick Check:

   EXTERNAL after CREATE, LOCATION after columns [OK]

Hint: EXTERNAL goes right after CREATE in HiveQL [OK]

Common Mistakes:

• Placing EXTERNAL after TABLE keyword
• Misplacing LOCATION clause
• Omitting EXTERNAL keyword for external tables

3. Consider the following Hive commands:

CREATE EXTERNAL TABLE ext_table (id INT) LOCATION '/data/ext';
CREATE TABLE man_table (id INT);

What happens if you drop ext_table and man_table respectively?

medium

A. Dropping ext_table deletes data files; dropping man_table deletes only metadata.

B. Dropping ext_table deletes only metadata; dropping man_table deletes both metadata and data files.

C. Both drops delete only metadata, data files remain.

D. Both drops delete metadata and data files.

Solution

1. Step 1: Understand drop behavior for external tables

   Dropping an external table removes only metadata; data files at '/data/ext' remain untouched.

2. Step 2: Understand drop behavior for managed tables

   Dropping a managed table removes metadata and deletes data files stored in Hadoop's warehouse.

3. Final Answer:

   Dropping ext_table deletes only metadata; dropping man_table deletes both metadata and data files. -> Option B

4. Quick Check:

   External drop = metadata only, Managed drop = metadata + data [OK]

Hint: External drop keeps data; managed drop deletes data [OK]

Common Mistakes:

• Assuming external drop deletes data files
• Thinking managed drop keeps data files
• Confusing metadata and data deletion

4. You created an external table but accidentally omitted the LOCATION clause. What will happen when you try to query this table?

medium

A. Query will fail because LOCATION is mandatory for external tables.

B. Query will succeed using default warehouse location, behaving like a managed table.

C. Query will succeed but return no data.

D. Hive will automatically assign a random external location.

Solution

1. Step 1: Check requirement of LOCATION for external tables

   External tables require LOCATION to specify where data files reside outside Hadoop's warehouse.

2. Step 2: Understand consequence of missing LOCATION

   Without LOCATION, Hive cannot find external data, so query fails with an error.

3. Final Answer:

   Query will fail because LOCATION is mandatory for external tables. -> Option A

4. Quick Check:

   External tables need LOCATION [OK]

Hint: External tables must have LOCATION clause [OK]

Common Mistakes:

• Assuming default location is used for external tables
• Thinking query returns empty data silently
• Believing Hive auto-assigns external location

5. You have important data files stored in HDFS at /user/hadoop/data. You want to create a table in Hive to query this data but ensure that deleting the table does NOT delete the data files. Which table type should you create and what is the correct approach?

hard

A. Create an external table with LOCATION '/user/hadoop/data' so Hive manages metadata only and leaves data intact on drop.

B. Create a managed table and copy data files into Hive warehouse; data will be deleted on drop.

C. Create a managed table with LOCATION '/user/hadoop/data' to keep data files safe on drop.

D. Create an external table without specifying LOCATION; Hive will manage data safely.

Solution

1. Step 1: Identify requirement to keep data files safe on table drop

   Only external tables keep data files untouched when the table is dropped.

2. Step 2: Use external table with explicit LOCATION

   Specify LOCATION '/user/hadoop/data' so Hive knows where data files are stored externally.

3. Step 3: Understand managed table behavior

   Managed tables store data in warehouse and delete data files on drop, so not suitable here.

4. Final Answer:

   Create an external table with LOCATION '/user/hadoop/data' so Hive manages metadata only and leaves data intact on drop. -> Option A

5. Quick Check:

   External + LOCATION = safe data on drop [OK]

Hint: Use external table with LOCATION to protect data files [OK]

Common Mistakes:

• Using managed table and losing data on drop
• Omitting LOCATION for external table
• Assuming managed table can protect external data