Types of Tables in Apache Hive – A Quick Overview

Overview

• Apache Hive is a must-know tool for anyone interested in data science and data engineering
• Learn about the different types of tables un Apache Hive

Introduction

I’ve spent over half a decade working with the Big Data Technology stack and consulting with clients across various domains. One thing I have noticed is how frequently Hive is used as a warehousing solution across business domains.

You simply can’t ignore Apache Hive when you are learning Apache Hadoop.

Hive is a part of the large Hadoop Ecosystem that lets you provide a schema to large data residing in HDFS. Most of you will be aware of RDBMS and its tables. We use them so often that it has become a part of our lives now. And here’s the thing – tables in Hive are no different.

Have you ever wondered what might be the different types of tables in Hive? That’s what we’ll discuss in this article!

Table of Contents

1. What is Apache Hive?
2. Types of Table in Apache Hive #1: Managed Tables
3. Types of Table in Apache Hive #2: External Tables
4. Managed vs External Table – What’s the Difference?
5. Identify the Type of Apache Hive Table

What is Apache Hive?

Apache Hive is a data warehouse system for Apache Hadoop. It provides SQL-like access for data in HDFS so that Hadoop can be used as a warehouse structure. Hive allows you to provide structure on largely unstructured data. After you define the structure, you can use Hive to query the data without knowledge of Java or Map Reduce.

The Hive Query Language (HQL) has similar semantics and functions as standard SQL in the relational database so that experienced database analysts can easily access the data.

What are the features provided by Hive?

Apache Hive provides the following features:

1. Apache Hive provides a simpler query model with less coding than Map Reduce
2. HQL and SQL have similar syntax
3. It provides lots of functions that lead to easier analytics usage
4. The response time is typically much faster than other types of queries on the same of huge datasets
5. Apache Hive supports running on different computing frameworks
6. It supports ad hoc querying data on HDFS
7. Apache Hive supports user-defined functions, scripts, and a customized I/O format to extend its functionality
8. Is scalable and extensible to various types of data and bigger datasets
9. Matured JDBC and ODBC drivers allow many applications to pull Hive data for seamless reporting
10. Hive allows users to read data in arbitrary formats, using SerDes and Input/Output formats
11. Hive has a well-defined architecture for metadata management, authentication, and query optimizations
12. There is a big community of practitioners and developers working on and using Hive

Types of Tables in Apache Hive

Here are the types of tables in Apache Hive:

Managed Tables

In a managed table, both the table data and the table schema are managed by Hive. The data will be located in a folder named after the table within the Hive data warehouse, which is essentially just a file location in HDFS.

The location is user-configurable when Hive is installed. By managed or controlled we mean that if you drop (delete) a managed table, then Hive will delete both the Schema (the description of the table) and the data files associated with the table. Default location is /user/hive/warehouse).

Syntax to Create Managed Table

CREATE TABLE IF NOT EXISTS stocks (exchange STRING,
symbol STRING,
price_open FLOAT,
price_high FLOAT,
price_low FLOAT,
price_adj_close FLOAT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

As for managed tables, you can also copy the schema (but not the data) of an existing table:

CREATE EXTERNAL TABLE IF NOT EXISTS mydb.employees3
LIKE mydb.employees
LOCATION '/path/to/data';

External Tables
An external table is one where only the table schema is controlled by Hive. In most cases, the user will set up the folder location within HDFS and copy the data file(s) there. This location is included as part of the table definition statement. When an external table is deleted, Hive will only delete the schema associated with the table. The data files are not affected.

Syntax to Create External Table

CREATE EXTERNAL TABLE IF NOT EXISTS stocks (exchange STRING,
symbol STRING,
price_open FLOAT,
price_high FLOAT,
price_low FLOAT,
price_adj_close FLOAT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/data/stocks';

Managed vs. External Table – What’s the Difference?

Identify the Type of Apache Hive Table

You can tell whether or not a table is managed or external using the output of DESCRIBE EXTENDED table name.

Near the end of the Detailed Table Information output, you will see the following for managed tables:

... tableType: MANAGED_TABLE)

For external tables, you will see the following:

... tableType: EXTERNAL_TABLE)

Note: If you omit the EXTERNAL keyword and the original table is external, the new table will also be external. If you omit EXTERNAL and the original table is managed, the new table will also be managed. However, if you include the EXTERNAL keyword and the original table is managed, the new table will be external. Even in this scenario, the LOCATION clause will still be optional.

End Notes

In this article, we learned about Apache Hive and its table types. Hopefully, you might have got a good overview of the types of tables in Hive. The differences stated are not exhaustive. Please feel free to add more in the comment section below.

The following are some additional Data Engineering resources that I recommend you explore-

• What are the differences between Data Lake and Data Warehouse?
• SQL vs NoSQL Databases – A Key Concept Every Data Engineer Should Know
• 5 Popular NoSQL Databases Every Data Science Professional Should Know About
• A Beginner’s Guide to CAP Theorem for Data Engineering

I hope you might have liked the article. If you have any questions related to this article do let me know in the comments section below.

Recommended reading-

https://cwiki.apache.org/confluence/display/Hive/Home