Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 36 ยท HiveQL Basics

Hive Data Types

Learn all Hive data types: numeric, string, date and time, boolean, plus ARRAY, MAP, STRUCT and UNIONTYPE. Understand implicit and explicit casting and how NULL appears on bad data.

Choosing the right column type in Hive matters more than in most databases. Because Hive applies the schema when data is read, a wrong type does not cause an error: it quietly gives you NULL. And the type chosen also affects file size, speed and how joins behave. This lesson tours every type and the rules for converting between them.

Hive types are written in read-only blocks. Runnable editors are SQLite stand-ins that practise CAST, NULL and arithmetic rules that are portable; SQLite has no ARRAY, MAP or STRUCT, so those are only shown as Hive code.

Numeric types

Type Size Range / notes
TINYINT 1 byte -128 to 127
SMALLINT 2 bytes -32,768 to 32,767
INT / INTEGER 4 bytes about ยฑ2.1 billion
BIGINT 8 bytes about ยฑ9.2 quintillion; the usual type for ids
FLOAT 4 bytes single precision, approximate
DOUBLE 8 bytes double precision, approximate
DECIMAL(p,s) variable exact; up to 38 digits; use for money

Literals can carry a suffix: 100Y is a TINYINT, 100S SMALLINT, 100L BIGINT, 1.5BD a DECIMAL. A plain integer literal is INT unless it needs to be larger.

Never store money in FLOAT or DOUBLE: binary floating point cannot represent 0.1 exactly. Use DECIMAL(12,2). The stand-in shows the problem:

SQL Stand-in: floating-point rounding (SQLite, not Hive)

Press Run (or Ctrl + Enter) to see the result here.

In Hive the same sum in DOUBLE also gives 0.30000000000000004, while CAST(0.1 AS DECIMAL(5,2)) + CAST(0.2 AS DECIMAL(5,2)) is exactly 0.30.

String types

  • STRING: any length; use this by default.
  • VARCHAR(n): truncated silently at n characters.
  • CHAR(n): fixed length, padded with spaces (max 255).

There is no performance benefit to VARCHAR in Hive, and it can truncate data, so prefer STRING unless a downstream tool needs lengths.

Date and time

Type Example Notes
DATE DATE '2025-03-01' year-month-day, no time
TIMESTAMP TIMESTAMP '2025-03-01 14:30:00' no time zone; nanosecond precision
TIMESTAMP WITH LOCAL TIME ZONE stored in UTC, shown in the session zone
INTERVAL INTERVAL '3' DAY for date arithmetic

Text such as 2025/03/01 does not parse as a DATE (the format must be yyyy-MM-dd), so the column reads as NULL. If your files use other formats, load into STRING and convert with to_date(from_unixtime(unix_timestamp(col, 'dd/MM/yyyy'))).

Boolean and binary

BOOLEAN holds TRUE, FALSE or NULL. In text files the strings true and false (any case) are read as booleans; 1 and 0 are not. BINARY stores raw bytes.

Complex types

Hive can keep nested data in a single column, which is handy for JSON-like records.

SQL
CREATE TABLE users (
  id       BIGINT,
  name     STRING,
  phones   ARRAY<STRING>,
  settings MAP<STRING, STRING>,
  address  STRUCT<street:STRING, city:STRING, zip:STRING>
);

SELECT name,
       phones[0]            AS first_phone,
       settings['language'] AS language,
       address.city         AS city,
       size(phones)         AS phone_count
FROM users;
  • ARRAY<T>: ordered list, zero-based index.
  • MAP<K,V>: key-value pairs, accessed with ['key']; a missing key gives NULL.
  • STRUCT<a:T, b:U>: named fields, accessed with a dot.
  • UNIONTYPE<...>: one of several types; rarely used.

Complex types can be nested (ARRAY<STRUCT<...>>). To turn an array into rows, use LATERAL VIEW explode(phones) t AS phone (covered with built-in functions). Text tables need delimiters for each nesting level: COLLECTION ITEMS TERMINATED BY '|' and MAP KEYS TERMINATED BY ':'.

Casting

Convert explicitly with CAST(expr AS type):

SQL
SELECT CAST('42' AS INT),          -- 42
       CAST('abc' AS INT),         -- NULL, not an error
       CAST(3.99 AS INT),          -- 3 (truncates)
       CAST(1 AS BOOLEAN);         -- true

A failed cast returns NULL; Hive does not stop. In SQLite, casting also never fails, so we can observe it:

SQL Stand-in: CAST behavior (SQLite, not Hive); results differ for bad strings

Press Run (or Ctrl + Enter) to see the result here.

(SQLite turns 'abc' into 0, whereas Hive returns NULL: do not assume engines agree on bad input.)

Implicit conversion happens in expressions: INT + DOUBLE gives DOUBLE, and a STRING compared to a number is converted to DOUBLE. Implicit rules only widen types (TINYINT to SMALLINT to INT to BIGINT to FLOAT to DOUBLE). Comparing a BIGINT id to a string like '123' may work, but it prevents partition pruning and can lose precision for large ids, so cast deliberately.

NULL: the universal "didn't fit"

NULL means "unknown". Rules to remember:

  • NULL = NULL is NULL (not true); use IS NULL and IS NOT NULL.
  • Arithmetic with NULL gives NULL; aggregates such as SUM and AVG skip NULLs; COUNT(*) counts rows but COUNT(col) counts non-NULL values.
  • Out-of-range values and unparsable text in a table become NULL. If a column is unexpectedly all NULL, the type or the delimiter is almost always wrong.
  • Use COALESCE(col, 0) or nvl(col, 0) to substitute defaults.

Type-choice checklist

  1. Ids: BIGINT. Money: DECIMAL. Counts: INT or BIGINT.
  2. Dates: DATE; instants: TIMESTAMP. Avoid storing dates as strings when you can.
  3. Text: STRING.
  4. Use complex types only when the data is naturally nested and you will query it that way.
  5. Keep join keys the same type on both sides.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does Hive return for CAST('abc' AS INT)?

    Hive returns NULL when a value cannot be converted; it does not raise an error.
  2. 2. Which type should you use for money amounts?

    DECIMAL is exact; FLOAT and DOUBLE are binary approximations.
  3. 3. How do you read the second element of an ARRAY column phones?

    Arrays are zero-based, so the second element has index 1.
  4. 4. A DATE column is NULL for the text '2025/03/01'. Why?

    Load such data into STRING and convert it with a format pattern.
  5. 5. What is COUNT(col) counting?

    COUNT(col) skips NULLs; COUNT(*) counts every row.

Summary

  • Numeric: TINYINT to BIGINT, FLOAT/DOUBLE (approximate), DECIMAL (exact). Text: STRING by default. Time: DATE and TIMESTAMP.
  • Complex types: ARRAY, MAP, STRUCT (and UNIONTYPE) store nested data.
  • Casts and bad data yield NULL instead of errors; check types and delimiters when a column is all NULL.
  • Use matching types for join keys and avoid implicit conversions in filters.

Next: Loading and Inserting Data