Creating databases and tables in Hadoop - Practice Exercises

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this Hive query?

Consider the following Hive query to create a database and then list all databases. What will be the output of the SHOW DATABASES; command?

Hadoop

CREATE DATABASE IF NOT EXISTS sales_db;
SHOW DATABASES;

A ["default"]

B ["default", "sales_db"]

C SyntaxError

D []

💡 Hint

Think about what databases exist by default and what happens when you create a new one.

The CREATE DATABASE IF NOT EXISTS sales_db; command creates a new database named 'sales_db' if it does not already exist. The SHOW DATABASES; command lists all databases including the default one and the newly created 'sales_db'.

❓ data_output

intermediate

How many tables are in the database after these commands?

Given the following Hive commands, how many tables will be listed in sales_db?

Hadoop

CREATE DATABASE IF NOT EXISTS sales_db;
USE sales_db;
CREATE TABLE IF NOT EXISTS customers (id INT, name STRING);
CREATE TABLE IF NOT EXISTS orders (order_id INT, customer_id INT);
SHOW TABLES;

A SyntaxError

B 0

C 1

D 2

💡 Hint

Count how many tables you created in the database.

Two tables, 'customers' and 'orders', are created in the 'sales_db' database. The SHOW TABLES; command lists both tables, so the count is 2.

🔧 Debug

advanced

Identify the error in this Hive table creation command

What error will this Hive query produce?

Hadoop

CREATE TABLE employees (id INT, name STRING, salary);

A SyntaxError: Missing data type for column 'salary'

B No error, table created successfully

C TypeError: Invalid column name

D RuntimeError: Table already exists

💡 Hint

Check if all columns have data types specified.

The column 'salary' does not have a data type specified, which causes a syntax error in Hive table creation.

🧠 Conceptual

advanced

What is the difference between EXTERNAL and MANAGED tables in Hive?

Choose the correct statement about EXTERNAL and MANAGED tables in Hive.

A Dropping a MANAGED table deletes both metadata and data; dropping an EXTERNAL table deletes only metadata.

B Dropping an EXTERNAL table deletes both metadata and data; dropping a MANAGED table deletes only metadata.

C Both EXTERNAL and MANAGED tables delete data when dropped.

D Neither EXTERNAL nor MANAGED tables delete data when dropped.

💡 Hint

Think about who manages the data files for each table type.

MANAGED tables store data in Hive's warehouse directory and dropping them deletes data and metadata. EXTERNAL tables point to data stored outside Hive's control, so dropping them deletes only metadata, leaving data intact.

🚀 Application

expert

Which command creates a partitioned table in Hive?

You want to create a table named sales partitioned by year and month. Which Hive command correctly creates this partitioned table?

A CREATE TABLE sales PARTITIONED BY (year INT, month INT) (id INT, amount FLOAT);

B CREATE TABLE sales (id INT, amount FLOAT, year INT, month INT) PARTITIONED BY ();

C CREATE TABLE sales (id INT, amount FLOAT) PARTITIONED BY (year INT, month INT);

D CREATE TABLE sales (id INT, amount FLOAT) PARTITION (year INT, month INT);

💡 Hint

Remember the syntax for partitioned tables in Hive: columns go first, then PARTITIONED BY clause.

The correct syntax places the regular columns first, then uses PARTITIONED BY to specify partition columns separately.