Hive Data Types: How to Create and Drop Databases in Hive

Data types are very important elements in Hive query language and data modeling. For defining the table column types, we must have to know about the data types and their usage.

The following gives a brief overview of some data types present in Hive:

• Numeric Types
• String Types
• Date/Time Types
• Complex Types

Data types in Hive

Every Hive column is declared with one of the types below. The primitive families come first, followed by the complex types that hold more than one value in a single column.

Hive Numeric Data Types

Numeric columns range from a single byte to eight bytes, so picking the smallest type that fits the values keeps both storage and scan cost down.

Hive String Data Types

Character columns come in three shapes, and the difference is whether Hive enforces a declared length.

Hive Date/Time Data Types

Temporal columns are stored without any time-zone offset, which is worth remembering before comparing values written by different clusters.

Hive Miscellaneous Data Types

Two further primitives sit outside the numeric, string and date families but appear regularly in real schemas.

Hive Complex Data Types

Complex types nest values inside one column, which removes the extra join that a relational design would need.

Hive Complex Data Types Example

The table above gives the declaration form. The statement below puts three of the complex types into one table so the delimiter clauses and the access syntax can be seen together.

CREATE TABLE employees (
  name      STRING,
  skills    ARRAY<STRING>,
  deductions MAP<STRING, FLOAT>,
  address   STRUCT<street:STRING, city:STRING, pin:INT>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

Three separate delimiters are needed here: one between columns, a second between the items of an ARRAY or STRUCT, and a third between a MAP key and its value. Once the table exists, each complex column is read with its own accessor.

SELECT name, skills[0], deductions['tax'], address.city
FROM employees;

The table below summarises which accessor belongs to which type.

Complex types may be nested, so ARRAY<STRUCT<street:STRING, city:STRING>> is a valid column declaration. Once the column layout is settled, the tables themselves are built with the statements covered in Hive create, alter and drop table.

How to Create and Drop Databases in Hive

A database in Hive is simply a namespace that groups tables, so it is the first object to create before any table work begins. Following are the steps on how to create and drop databases in Hive.

Step 1) Create Database in Hive

For creating a database in Hive shell, we have to use the command as shown in the syntax below:

Syntax:

Create database <DatabaseName>

Example: Create database “guru99”

The screenshot below shows the create statement followed by a show command listing every database on the cluster.

From the above screenshot, we are doing two things:

1. Creating database “guru99” in Hive
2. Displaying existing databases using the “show” command

In the same screen, database “guru99” is displayed at the end when we execute the show command, which means database “guru99” is successfully created.

Step 2) Drop Database in Hive

For dropping a database in Hive shell, we have to use the “drop” command as shown in the syntax below:

Syntax:

Drop database <DatabaseName>

Example: Drop database guru99

In the next screenshot the drop statement runs first, and the show command that follows no longer lists the database.

In the above screenshot, we are doing two things:

1. We are dropping database ‘guru99’ from Hive
2. Cross checking the same with the “show” command

In the same screen, after checking databases with the show command, database “guru99” does not appear inside Hive. So we can confirm now that database “guru99” is dropped.

Hive Database Commands: USE, DESCRIBE, SHOW and ALTER DATABASE

The two statements above use their shortest form. The documented syntax carries optional clauses that decide where the files land and what happens when the name is already taken.

CREATE DATABASE [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];

DROP DATABASE [IF EXISTS] database_name [RESTRICT|CASCADE];

The keywords DATABASE and SCHEMA are interchangeable throughout, so CREATE SCHEMA and CREATE DATABASE do exactly the same thing. The remaining commands round out day-to-day work with a namespace.

Two defaults are worth memorising. Without a LOCATION clause the files sit under the warehouse directory, normally /user/hive/warehouse/database_name.db, and without IF EXISTS a drop against a missing name raises an error instead of passing quietly. Both settings are held in the Hive metastore.

Hive Data Type Conversion and Common Errors

Types are not always used exactly as declared. Hive widens a value automatically when no information can be lost, and leaves everything else to an explicit conversion.

• Implicit widening follows the chain TINYINT to SMALLINT to INT to BIGINT to FLOAT to DOUBLE, and a STRING that holds digits is promoted to DOUBLE.
• Narrowing never happens on its own, so a DOUBLE assigned to an INT column needs a written conversion.
• CAST performs that written conversion, and it returns NULL rather than an error when the value cannot be converted.

SELECT CAST(salary AS DECIMAL(10,2)) FROM employees;

The errors below account for most of the surprises beginners meet on their first schema.

Once the types behave, the next step is loading and querying the data itself, which is covered in Hive queries with Order By, Group By and Cluster By.

FAQs