Hive Create Table from Another Table and Store Format as Parquet

Introduction

Hive is a data warehousing and SQL-like query language for Hadoop, providing a way to manage and analyze large datasets stored in Hadoop Distributed File System (HDFS). One of the key features of Hive is its ability to create tables from existing data sources, such as other tables or external files. In this article, we will explore how to create a table from another table in Hive and store the format of the resulting table as Parquet.

Understanding Parquet Format

Parquet is a columnar storage format that is widely used for storing large datasets in Hadoop. It provides several benefits, including:

• Efficient storage: Parquet stores data in a compressed and encoded format, reducing storage requirements.
• Faster query performance: Parquet’s columnar structure allows for faster query performance, as only the columns needed are retrieved from disk.
• Schema flexibility: Parquet supports schema evolution, making it easy to adapt to changing data structures.

Hive Create Table Query

The Hive create table query is used to create a new table based on an existing data source. The general syntax for this query is:

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '<delimit>'' Stored As <storage_format>;

However, in the provided question, the user encountered an error with the STORED AS PARQUET clause. To understand why this is happening, let’s first explore what the STORED AS clause does.

Understanding STORED AS Clause

The STORED AS clause specifies the storage format for a table created in Hive. In this case, the user wants to store the table as Parquet. However, there seems to be an issue with how this is being specified.

Hive 0.13.x and Earlier

In earlier versions of Hive (0.13.x and earlier), the STORED AS clause does not support the PARQUET format. Instead, you would need to specify a different storage format, such as TEXTFILE or CSV.

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '<delimit>'' STORED AS TEXTFILE;

Hive 0.14.x and Later

In later versions of Hive (0.14.x and later), the STORED AS clause has been updated to support more storage formats, including Parquet.

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...
) ROW FORMAT PARQUET STORED AS PARQUET;

However, even in these newer versions of Hive, the user encountered an error with the original query. To understand why this is happening, let’s take a closer look at the query itself.

Original Query Analysis

The original query was:

CREATE TABLE my_db.test_table
AS (select * from my_db.my_table
where partition_date >= '2019-06-01')
STORED AS PARQUET;

Here are some issues with this query:

• The AS keyword is not valid in the context of the CREATE TABLE statement. Instead, you would need to use a subquery or a derived table.
• Even if the AS keyword were used correctly, the STORED AS PARQUET clause would still be invalid.

Presto DB Specific Query

The correct query for creating a Parquet-formatted table in Hive is:

CREATE TABLE my_db.test_table
WITH (format = 'PARQUET')
as (select * from my_db.my_table
where partition_date >= '2019-06-01');

In this corrected query, the WITH clause specifies the storage format as Parquet. The subquery creates a new table based on the original data source.

Converting RCBINARY to PARQUET

The user mentioned that they were working with an RCDBINARY-formatted table and wanted to convert it to Parquet format. To do this, you would need to:

• Export the RCDBINARY table as a CSV file.
• Use Hive’s CREATE TABLE statement to create a new table in Parquet format.

Here is an example of how you might accomplish this:

-- Export the RCDBINARY table as a CSV file
LOAD DATA INPATH '/path/to/my_table.rcdbinary' INTO TABLE my_db.my_table;

-- Create a new table in Parquet format
CREATE TABLE my_db.test_table
WITH (format = 'PARQUET')
as (select * from my_db.my_table);

This process can be repeated for each RCDBINARY-formatted table you want to convert.

Conclusion

In this article, we explored how to create a table from another table in Hive and store the format of the resulting table as Parquet. We also discussed some common issues and limitations with the STORED AS clause and provided examples of how to convert RCDBINARY-formatted tables to Parquet format.

Additional Considerations

When working with large datasets, it’s essential to consider storage efficiency and query performance. Hive’s columnar storage formats, such as Parquet, can provide significant benefits in these areas.

However, there are also some potential drawbacks to consider:

• Schema evolution: Hive’s schema evolution capabilities allow you to adapt to changing data structures without altering the underlying storage format.
• Storage requirements: While Parquet can reduce storage requirements, it can also increase them if not properly compressed or encoded.

Last modified on 2023-07-02