Creating databases and tables in Hadoop - Try It Yourself

Creating databases and tables

📖 Scenario: You are working with Hadoop's Hive to organize data for a small online bookstore. You want to create a database and tables to store information about books and authors.

🎯 Goal: Create a Hive database called bookstore. Then create two tables inside this database: authors and books. The authors table should have columns author_id (int) and author_name (string). The books table should have columns book_id (int), title (string), and author_id (int) to link to the authors.

📋 What You'll Learn

Create a Hive database named bookstore

Create a table named authors with columns author_id (int) and author_name (string)

Create a table named books with columns book_id (int), title (string), and author_id (int)

Use the bookstore database before creating tables

💡 Why This Matters

🌍 Real World

Organizing data in Hive databases and tables helps manage large datasets in big data projects like online bookstores.

💼 Career

Knowing how to create and manage Hive databases and tables is essential for data engineers and analysts working with Hadoop ecosystems.

1

Create the bookstore database

Write a Hive query to create a database called bookstore.

Hadoop

# Write the Hive query to create the database 'bookstore'
# Your code here

Need a hint?

Use the CREATE DATABASE statement followed by the database name.

2

Select the bookstore database

Write a Hive query to use the database bookstore before creating tables.

Hadoop

CREATE DATABASE bookstore;
# Write the Hive query to use the database 'bookstore'
# Your code here

Need a hint?

Use the USE statement to select the database.

3

Create the authors and books tables

Write Hive queries to create the authors table with columns author_id (int) and author_name (string), and the books table with columns book_id (int), title (string), and author_id (int).

Hadoop

CREATE DATABASE bookstore;
USE bookstore;
# Write the Hive query to create the 'authors' table
# Your code here

# Write the Hive query to create the 'books' table
# Your code here

Need a hint?

Use CREATE TABLE with column names and types inside parentheses.

4

Display the tables in the bookstore database

Write a Hive query to show all tables in the bookstore database.

Hadoop

CREATE DATABASE bookstore;
USE bookstore;
CREATE TABLE authors (
  author_id INT,
  author_name STRING
);

CREATE TABLE books (
  book_id INT,
  title STRING,
  author_id INT
);
# Write the Hive query to show all tables in 'bookstore'
# Your code here

Need a hint?

Use the SHOW TABLES; command to list tables in the current database.