HIVE DATAFORMAT

Hive is a data warehouse infrastructure built on top of Hadoop. It provides an interface for querying and analyzing large datasets stored in Hadoop Distributed File System (HDFS). One of the key features of Hive is its ability to process data in different formats, known as data formats. In this article, we will explore the various data formats supported by Hive and provide code examples to demonstrate their usage.

Data Formats in Hive

Hive supports several data formats for both input and output operations. These data formats include:

1. Text File: The default data format in Hive is the text file format. It stores data in plain text format, with each record separated by a newline character. Text files are easy to read and write, making them suitable for most use cases.

2. Sequence File: Sequence files are binary files that store key-value pairs. They are widely used in Hadoop ecosystem and provide a compact storage format. Sequence files are useful when the data needs to be compressed and serialized.

3. ORC (Optimized Row Columnar): ORC is a columnar storage format that provides high compression and efficient data retrieval. It stores data in a highly optimized way, enabling faster query performance. ORC is recommended for large datasets and complex queries.

4. Parquet: Parquet is another columnar storage format that provides similar benefits as ORC. It is designed to work efficiently with large datasets and supports advanced features like predicate pushdown and schema evolution.

5. Avro: Avro is a row-based data serialization system that is used for efficient data exchange. It provides a compact binary format and supports both schema evolution and schema resolution.

6. JSON: JSON (JavaScript Object Notation) is a popular data format for representing structured data. Hive supports both reading and writing data in JSON format.

Code Examples

Creating Table with Different Data Formats

Here is an example of creating a table in Hive with different data formats:

CREATE TABLE users_text
(
    id INT,
    name STRING,
    email STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

CREATE TABLE users_orc
(
    id INT,
    name STRING,
    email STRING
)
STORED AS ORC;

CREATE TABLE users_parquet
(
    id INT,
    name STRING,
    email STRING
)
STORED AS PARQUET;

CREATE TABLE users_avro
(
    id INT,
    name STRING,
    email STRING
)
STORED AS AVRO;

Loading Data into Different Data Formats

Once the tables are created, data can be loaded into them using the respective data formats. Here is an example of loading data into the tables:

LOAD DATA INPATH '/path/to/users.csv' INTO TABLE users_text;
LOAD DATA INPATH '/path/to/users.csv' INTO TABLE users_orc;
LOAD DATA INPATH '/path/to/users.csv' INTO TABLE users_parquet;
LOAD DATA INPATH '/path/to/users.csv' INTO TABLE users_avro;

Querying Data in Different Data Formats

Once the data is loaded, you can query the tables using HiveQL. Here is an example of querying data from the tables:

SELECT * FROM users_text;
SELECT * FROM users_orc;
SELECT * FROM users_parquet;
SELECT * FROM users_avro;

Conclusion

In this article, we explored the various data formats supported by Hive. We learned about the text file, sequence file, ORC, Parquet, Avro, and JSON formats. We also provided code examples to demonstrate how to create tables and load data using different data formats in Hive. By leveraging the appropriate data format, you can optimize your data processing and improve the performance of your Hive queries.