Overview
- Apache HiveHive is a decentralized social media platform that allows its users to share content and connect with others without the intervention of a central authority. Uses blockchain technology to ensure data security and ownership. Unlike other social networks, Hive allows users to monetize their content through crypto rewards, which encourages the creation and active exchange of information.... es una herramienta imprescindible para cualquier persona interesada en la ciencia y la ingeniería de datos.
- Learn about the different types of Apache Hive tables
Introduction
I have spent more than half a decade working with the Big Data technology stack and consulting with clients in various domains. One thing I have noticed is how often Hive is used as a storage solution across all business domains.
You just can't ignore Apache Hive when you are learning Apache Hadoop.
Hive es parte del gran ecosistema de Hadoop que le posibilita proporcionar un esquema para grandes datos que residen en HDFSHDFS, o Hadoop Distributed File System, It is a key infrastructure for storing large volumes of data. Designed to run on common hardware, HDFS enables data distribution across multiple nodes, ensuring high availability and fault tolerance. Its architecture is based on a master-slave model, where a master node manages the system and slave nodes store the data, facilitating the efficient processing of information... Most of you will know RDBMS and its tables. We use them so often that it has now become part of our lives. And here is the question: tables in Hive are no different.
Ever wonder what the different types of tables could be in Hive? That is what we will discuss in this post!!
Table of Contents
- What is Apache Hive?
- Table types in Apache Hive # 1: managed tables
- Table types in Apache Hive # 2: External tables
- Managed vs external table: what is the difference?
- Identify the Apache Hive table type
What is Apache Hive?
Apache Hive is a data storage system for Apache Hadoop. Provides SQL equivalent access to data in HDFS so that Hadoop can be used as a warehouse structure. Hive enables you to provide structure over largely unstructured data. After establishing the structure, you can use Hive to query the data without knowledge of Java or Map Reduce.
Hive Query Language (HQL) cuenta con una semántica y funciones similares a las de SQL estándar en la databaseA database is an organized set of information that allows you to store, Manage and retrieve data efficiently. Used in various applications, from enterprise systems to online platforms, Databases can be relational or non-relational. Proper design is critical to optimizing performance and ensuring information integrity, thus facilitating informed decision-making in different contexts.... relacional para que los analistas de bases de datos experimentados puedan ingresar fácilmente a los datos.
What are the features offered by Hive?
Apache Hive provides the following features:
- Apache Hive provides a simpler query model with less coding than Map Reduce
- HQL and SQL have an equivalent syntax
- Provides many features that make analytics easy to use.
- Response time is usually much faster than other types of queries on the same huge data sets
- Apache Hive supports running on different computing frameworks
- Supports ad hoc data queries on HDFS
- Apache Hive supports user-defined functions, scripts and an E format / S customized to extend its functionality.
- It is scalable and extensible to various types of data and larger data sets.
- Mature JDBC and ODBC drivers enable many applications to pull data from Hive for smooth reporting
- Hive enables users to read data in arbitrary formats, using SerDes and input formats / Exit
- Hive has a well-established architecture for managing metadata, authentication and query optimization.
- There is a large community of professionals and developers who work and use Hive.
Table types in Apache Hive
These are the types of tables in Apache Hive:
Managed tables
In a managed table, Hive manages both the data and the table schema. The data will be located in a folder with the name of the table within the Hive data store, which is simply a file location in HDFS.
Location is user configurable when Hive is installed. By managed or controlled we mean that if you delete (remove) a managed table, Hive will remove both the schema (the table description) as the data files associated with the table. The default location is / Username / hive / stock).
Syntax for creating a 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 ',' ;
how to managed tables, you can also copy the schematic (but not the data) from 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 in which Hive only controls the table schema. In most cases, user will set folder location within HDFS and copy data files there. This location is included as part of the table definition declaration. When an external table is dropped, Hive will only delete the schema associated with the table. Data files are not affected.
Syntax for creating an 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 table vs. external table: what is the difference?
| Managed table | External Table |
| Hive assumes that has data from managed tables. | For external tables, Hive assumes yes no manage data. |
| If a table or managed partition is deleted, the data and metadata associated with that table or partition is deleted. | Leaving the table does not delete the data, even though the table metadata will be removed. |
| To Managed tables, Hive stores data in your warehouse directory | To External Tables, Hive stores the data in the LOCATION specified during the creation of the mesa(generally not in the warehouse directory) |
| Managed table provides ACID support / transnational action. | The external table does not provide support for ACID actions / transactional. |
| Declarations: ARCHIVE, UNARCHIVE, TRUNCATE, MERGE, CONCATENATE compatible | Not supported. |
| Query result caching is supported (saves the results of a executed Hive query for reuse) | Not supported |
Identify the Apache Hive table type
You can tell if a table is managed or external using the output of DESCRIBE EXTENDED table name.
Near the end of the detailed information output of the table, you will see the following for administered tables:
... tableType: MANAGED_TABLE)
To external boards, 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, also the new table will be managed. Despite this, if it includes the keyword EXTERNAL and the original table is managed, the new table will be external. Even in this scenario, the LOCATION clause will remain optional.
Final notes
In this post, we learned about Apache Hive and its table types. Hopefully, you may have gotten a good overview of table types in Hive. The differences indicated are not exhaustive. Feel free to add more in the comment section below.
The following are some additional data engineering resources that I recommend you explore:
I hope you liked the post. If you have any questions related to this post, let me know in the comment section below.
Recommended reading-
https://cwiki.apache.org/confluence/display/Hive/Home
