How to Create a Table in Apache Hive

Apache Hive is a data warehousing tool used to perform queries and analyze structured data in Apache Hadoop. Hive gives an SQL-like interface to query data stored in various databases and file systems that integrate with Hadoop. Traditional SQL queries must be implemented in the MapReduce Java API to execute SQL applications and queries over distributed data. Hive provides the necessary SQL abstraction to integrate SQL-like queries (HiveQL) into the underlying Java without the need to implement queries in the low-level Java API.

In this article, learn how to create a table in Hive and load data. We will also show you crucial HiveQL commands to display data.

A table in Hive is a set of data that uses a schema to sort the data by given identifiers. The general syntax for creating a table in Hive is:

[sourcecode lang="sql"]CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
(col_name data_type [COMMENT 'col_comment'],, ...)
[COMMENT 'table_comment']
[ROW FORMAT row_format]
[FIELDS TERMINATED BY char]
[STORED AS file_format];
[/sourcecode]

1. Create database in Apache Hive,

create database example;

1. Verify your database, is it already created?

show example;

1. Open your database

use example;

1. Create the 'products' table:

create table products (id int, name string, sku string, price string, quantity int)