Create Table in Hive

INTRODUCTION

Hive is a Meta-store where we can query and see the data which is stored in HDFS (Hadoop Distributed File System). In this article, we are going to see how we can create tables in Hive.

There are two types tables can be created in Hive. They are

1. Internal Tables
2. External Tables

Before we see more details on these external and internal tables, let me create a database. The syntax of Hive commands is same as MySQL.

CREATE DATABASE IF NOT EXISTS ranjith; USE ranjith;

INTERNAL TABLES

• Internal tables are created as a directory into the warehouse.

Example

CREATE TABLE emp(empid STRING, firstname STRING,    lastname STRING, designation STRING, salary STRING,    temporary_address STRING, permanent_address STRING,    phone STRING, mobileno1 STRING, mobileno2 STRING);

EXTERNAL TABLES

1. External tables are created by specifying the location in HDFS.
2. Table will be created with ‘EXTERNAL’ keyword

Example: Use HDFS Location

CREATE EXTERNAL TABLE emp(empid STRING, firstname STRING,    lastname STRING, designation STRING, salary STRING,    temporary_address STRING, permanent_address STRING,    phone STRING, mobileno1 STRING, mobileno2 STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' STORED AS TEXTFILE LOCATION "/user/ranjith/hive/data/09Nov2015/";

Example: Use AWS S3 Location

... LOCATION "s3://user/ranjith/hive/data/09Nov2015/"; or ... LOCATION "s3n://user/ranjith/hive/data/09Nov2015/";