Creating databases and tables in Hadoop - Visual Walkthrough

Concept Flow - Creating databases and tables

Start

↓

Create Database

↓

Use Database

↓

Create Table

↓

Verify Table

↓

End

The flow shows creating a database, selecting it, creating a table inside it, and then verifying the table.

Execution Sample

Hadoop

CREATE DATABASE IF NOT EXISTS sales_db;
USE sales_db;
CREATE TABLE IF NOT EXISTS customers (
  id INT,
  name STRING,
  age INT
);

This code creates a database named sales_db, switches to it, and creates a customers table with three columns.

Execution Table

Step	Command	Action	Result
1	CREATE DATABASE IF NOT EXISTS sales_db;	Check if sales_db exists; create if not	Database sales_db created or already exists
2	USE sales_db;	Set current database context	Current database set to sales_db
3	CREATE TABLE IF NOT EXISTS customers (id INT, name STRING, age INT);	Check if customers table exists; create if not	Table customers created in sales_db
4	SHOW TABLES;	List tables in current database	customers listed
5	DESCRIBE customers;	Show table schema	Columns: id INT, name STRING, age INT
6	END	No more commands	Process complete

💡 All commands executed successfully; database and table created or confirmed existing.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	Final
Current Database	None	None	sales_db	sales_db	sales_db	sales_db	sales_db
Databases	[]	[sales_db]	[sales_db]	[sales_db]	[sales_db]	[sales_db]	[sales_db]
Tables in sales_db	[]	[]	[]	[customers]	[customers]	[customers]	[customers]
customers Table Schema	None	None	None	id INT, name STRING, age INT	id INT, name STRING, age INT	id INT, name STRING, age INT	id INT, name STRING, age INT

Key Moments - 3 Insights

Why do we use 'IF NOT EXISTS' in CREATE DATABASE and CREATE TABLE?

Using 'IF NOT EXISTS' prevents errors if the database or table already exists, as shown in steps 1 and 3 of the execution_table.

What does the USE command do?

The USE command sets the current database context so that subsequent commands apply to it, as seen in step 2 where Current Database changes to sales_db.

How do we check if the table was created correctly?

We use SHOW TABLES and DESCRIBE commands (steps 4 and 5) to list tables and see the table schema, confirming creation.

Visual Quiz - 3 Questions

Look at the execution_table at step 2. What is the current database after the USE command?

A customers

B None

C sales_db

D default

💡 Hint

Check the 'Result' column in step 2 of the execution_table.

At which step is the customers table created?

A Step 1

B Step 3

C Step 4

D Step 5

💡 Hint

Look at the 'Action' and 'Result' columns in the execution_table for table creation.

If we remove 'IF NOT EXISTS' from CREATE DATABASE, what happens if sales_db already exists?

A Error occurs and execution stops

B Database is ignored silently

C Database is recreated

D Table is created instead

💡 Hint

Refer to key_moments about 'IF NOT EXISTS' usage and step 1 behavior.

Concept Snapshot

CREATE DATABASE db_name;  -- creates a new database
USE db_name;               -- selects the database
CREATE TABLE table_name (...);  -- creates a table in current database
Use IF NOT EXISTS to avoid errors if already present
Verify with SHOW TABLES and DESCRIBE commands

Full Transcript

This visual execution shows how to create a database and a table in Hadoop. First, the CREATE DATABASE command checks if the database exists and creates it if not. Then, the USE command sets the current database context. Next, CREATE TABLE creates a table with specified columns if it does not exist. SHOW TABLES lists tables in the current database, and DESCRIBE shows the table schema. Variables like current database and tables update step-by-step. Key points include using IF NOT EXISTS to avoid errors and verifying creation with SHOW and DESCRIBE commands.