trino-to-hive-migration

Convert Trino queries to Hive when memory errors occur. Covers syntax differences (td_time_string→TD_TIME_FORMAT, REGEXP_LIKE→RLIKE, ARRAY_AGG→COLLECT_LIST) and engine selection.

⭐3stars🔀4forks📁View on GitHub🕐Updated Dec 31, 2025

When & Why to Use This Skill

This Claude skill automates the migration of SQL queries from Trino to Hive, specifically designed to resolve memory limit errors in big data environments. It provides comprehensive syntax mapping for functions like time formatting, regular expressions, and aggregations, ensuring seamless data processing for large-scale datasets that exceed Trino's distributed memory capacity.

Use Cases

  • Resolving 'Query exceeded per-node memory limit' errors by migrating Trino queries to Hive's disk-based execution engine.
  • Converting complex SQL queries involving multi-way JOINs and high-cardinality GROUP BY operations from Presto/Trino to Hive syntax.
  • Automating the update of Digdag workflow configurations and TD Toolbelt commands when switching execution engines for large-scale data processing.
  • Processing historical datasets spanning over a month where Trino's in-memory engine fails due to data volume.
nametrino-to-hive-migration
descriptionConvert Trino queries to Hive when memory errors occur. Covers syntax differences (td_time_string→TD_TIME_FORMAT, REGEXP_LIKE→RLIKE, ARRAY_AGG→COLLECT_LIST) and engine selection.

Trino to Hive Migration

When to Migrate

Use Hive when Trino fails with:

  • Query exceeded per-node memory limit
  • Query exceeded distributed memory limit
  • Processing > 1 month of data, complex multi-way JOINs, high cardinality GROUP BY

Syntax Conversion

Trino Hive
td_time_string(time, 'd!', 'JST') TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST')
approx_distinct(col) approx_distinct(col) (Hivemall - compatible!)
approx_percentile(col, 0.95) percentile(col, 0.95)
array_agg(col) collect_list(col)
string_agg(col, ',') concat_ws(',', collect_list(col))
regexp_like(col, 'pattern') col rlike 'pattern'
Automatic small table join /*+ MAPJOIN(t) */

Example Migration

Trino (fails with memory error):

select
  td_time_string(time, 'd!', 'JST') as date,
  approx_distinct(session_id) as sessions
from events
where td_time_range(time, '2024-01-01', '2024-12-31')
group by td_time_string(time, 'd!', 'JST')

Hive (works):

select
  TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST') as date,
  approx_distinct(session_id) as sessions
from events
where td_time_range(time, '2024-01-01', '2024-12-31', 'JST')
group by TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST')

Engine Selection

In Digdag:

+query_trino:
  td>: queries/analysis.sql
  engine: presto

+query_hive:
  td>: queries/analysis.sql
  engine: hive

In TD Toolbelt:

td query -d database_name -T presto "SELECT ..."
td query -d database_name -T hive "SELECT ..."

Performance Tips for Hive

-- MAPJOIN hint for small tables
select /*+ MAPJOIN(small_table) */ *
from large_table l
join small_table s on l.id = s.id

-- Process in chunks for very large datasets
where td_interval(time, '-1d', 'JST')  -- Daily instead of yearly

Typical Timeline

  • Trino: Fails after 5 min with memory error
  • Hive: Completes in 20-30 min successfully