hive

treasure-data's avatarfrom treasure-data

TD Hive SQL with TD-specific functions and Hive-only features (lateral view, explode, MAPJOIN hint). Use when Trino memory errors occur or for Hive-specific syntax.

⭐3stars🔀4forks📁View on GitHub🕐Updated Dec 31, 2025
[SQL and Databases]

When & Why to Use This Skill

This Claude skill provides comprehensive support for TD Hive SQL, enabling users to leverage Treasure Data-specific functions and Hive-exclusive features like lateral views, explode, and MAPJOIN hints. It is specifically optimized for large-scale batch ETL processes and serves as a robust solution for complex data transformations where standard engines might encounter memory limitations.

Use Cases

  • case 1: Resolving OutOfMemory (OOM) errors in Trino by switching to Hive for memory-intensive joins using MAPJOIN hints.
  • case 2: Processing complex nested data structures such as arrays and JSON objects using 'lateral view explode' and 'get_json_object' functions.
  • case 3: Managing time-series data efficiently with specialized TD time functions like td_interval and td_time_range for precise windowing.
  • case 4: Implementing automated data organization in batch ETL pipelines through dynamic partitioning and TD_TIME_FORMAT.
namehive
descriptionTD Hive SQL with TD-specific functions and Hive-only features (lateral view, explode, MAPJOIN hint). Use when Trino memory errors occur or for Hive-specific syntax.

TD Hive SQL

TD Time Functions

where td_interval(time, '-1d', 'JST')      -- Yesterday
where td_interval(time, '-1w', 'JST')      -- Previous week
where td_interval(time, '-1M', 'JST')      -- Previous month
where td_interval(time, '-1d/-1d', 'JST')  -- 2 days ago

Note: Cannot use TD_SCHEDULED_TIME() as first arg. Include TD_SCHEDULED_TIME() elsewhere to establish reference date.

td_time_range (Explicit dates)

where td_time_range(time, '2024-01-01', '2024-01-31', 'JST')
where td_time_range(time, '2024-01-01', null, 'JST')  -- Open-ended

TD_TIME_FORMAT

TD_TIME_FORMAT(time, 'yyyy-MM-dd HH:mm:ss', 'JST')

TD_TIME_PARSE

TD_TIME_PARSE('2024-01-01', 'JST')  -- String to Unix timestamp

TD_DATE_TRUNC

TD_DATE_TRUNC('day', time, 'JST')
TD_DATE_TRUNC('hour', time, 'UTC')

Hive-Specific Features

MAPJOIN Hint

select /*+ MAPJOIN(small_table) */ *
from large_table l
join small_table s on l.id = s.id
where td_time_range(l.time, '2024-01-01')

lateral view with explode

select user_id, tag
from user_profiles
lateral view explode(tags) tags_table as tag
where td_time_range(time, '2024-01-01')

get_json_object

select
  get_json_object(json_column, '$.user.id') as user_id,
  get_json_object(json_column, '$.event.type') as event_type
from raw_events

Dynamic Partitioning

set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = nonstrict;

insert overwrite table target_table partition(dt)
select *, TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST') as dt
from source_table
where td_time_range(time, '2024-01-01', '2024-01-31')

Differences from Trino

Feature Hive Trino
Approx distinct count(distinct x) approx_distinct(x)
Time format TD_TIME_FORMAT() td_time_string()
Small table join /*+ MAPJOIN(t) */ Automatic
Flatten array lateral view explode() unnest()

Common Errors

Error Fix
OutOfMemoryError Reduce time range, use MAPJOIN
Too many dynamic partitions Reduce partition count

When to Use Hive vs Trino

  • Use Hive: Memory errors in Trino, batch ETL, Hive-specific UDFs
  • Use Trino: Interactive queries, faster execution, approx functions

Resources