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.

Practice

1. What is the main purpose of creating a database in Hadoop?

easy

A. To run queries faster

B. To store data in rows and columns

C. To organize data into separate groups

D. To delete old data automatically

Solution

1. Step 1: Understand the role of a database

   A database is used to organize data logically into separate groups or collections.

2. Step 2: Differentiate database from tables

   Tables store data in rows and columns inside a database, but the database itself is for grouping.

3. Final Answer:

   To organize data into separate groups -> Option C

4. Quick Check:

   Database = Organize data groups [OK]

Hint: Databases group data; tables hold the data [OK]

Common Mistakes:

• Confusing database with table
• Thinking database stores data directly
• Assuming database speeds up queries automatically

2. Which of the following is the correct syntax to create a database named sales_db in Hive?

easy

A. CREATE DATABASE sales_db;

B. MAKE DATABASE sales_db;

C. CREATE DB sales_db;

D. NEW DATABASE sales_db;

Solution

1. Step 1: Recall Hive syntax for database creation

   The correct command to create a database in Hive is CREATE DATABASE database_name;.

2. Step 2: Check each option

   Only CREATE DATABASE sales_db; matches the correct syntax exactly.

3. Final Answer:

   CREATE DATABASE sales_db; -> Option A

4. Quick Check:

   CREATE DATABASE = correct syntax [OK]

Hint: Use CREATE DATABASE, not MAKE or NEW [OK]

Common Mistakes:

• Using MAKE or NEW instead of CREATE
• Abbreviating DATABASE as DB
• Missing semicolon at end

3. What will be the result of running this Hive query?

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

medium

A. Creates a table named employees with three columns

B. Throws a syntax error due to missing database name

C. Creates a database named employees

D. Creates a table with no columns

Solution

1. Step 1: Analyze the CREATE TABLE statement

   The query uses correct syntax to create a table named employees with columns id, name, and salary.

2. Step 2: Check for errors or missing parts

   Database name is optional if default database is used; no syntax error here.

3. Final Answer:

   Creates a table named employees with three columns -> Option A

4. Quick Check:

   CREATE TABLE with columns = creates table [OK]

Hint: CREATE TABLE defines columns inside parentheses [OK]

Common Mistakes:

• Thinking database name is mandatory
• Confusing table and database creation
• Assuming syntax error without checking carefully

4. Identify the error in this Hive query:

CREATE TABLE users id INT, name STRING;

medium

A. Table name is invalid

B. Missing parentheses around column definitions

C. Data types are incorrect

D. CREATE TABLE keyword is misspelled

Solution

1. Step 1: Review the syntax for CREATE TABLE

   Column definitions must be enclosed in parentheses after the table name.

2. Step 2: Check the given query

   The query misses parentheses around id INT, name STRING, causing syntax error.

3. Final Answer:

   Missing parentheses around column definitions -> Option B

4. Quick Check:

   Columns need parentheses [OK]

Hint: Always use parentheses for columns in CREATE TABLE [OK]

Common Mistakes:

• Forgetting parentheses
• Assuming table name is wrong
• Thinking data types are invalid

5. You want to create a database company_db and inside it a table departments with columns dept_id INT and dept_name STRING. Which sequence of commands is correct?

hard

A. CREATE TABLE company_db.departments (dept_id INT, dept_name STRING);

B. CREATE DATABASE company_db; CREATE TABLE departments dept_id INT, dept_name STRING;

C. USE company_db; CREATE DATABASE company_db; CREATE TABLE departments (dept_id INT, dept_name STRING);

D. CREATE DATABASE company_db; USE company_db; CREATE TABLE departments (dept_id INT, dept_name STRING);

Solution

1. Step 1: Create the database first

   Use CREATE DATABASE company_db; to make the database.

2. Step 2: Switch to the new database

   Use USE company_db; to set the context for table creation.

3. Step 3: Create the table with correct syntax

   Use CREATE TABLE departments (dept_id INT, dept_name STRING); inside the database.

4. Final Answer:

   CREATE DATABASE company_db; USE company_db; CREATE TABLE departments (dept_id INT, dept_name STRING); -> Option D

5. Quick Check:

   Create DB, switch DB, then create table [OK]

Hint: Create DB, then USE it before creating tables [OK]

Common Mistakes:

• Trying to create table before database
• Wrong order of USE and CREATE DATABASE
• Missing parentheses in CREATE TABLE