ReddRadar
Agent skill
hive
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.
Install this agent skill to your Project
npx add-skill https://github.com/treasure-data/td-skills/tree/main/sql-skills/hive
SKILL.md
TD Hive SQL
TD Time Functions
td_interval (Recommended for relative time)
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
Recommended Agent Skills
Expand your agent's capabilities with these related and highly-rated skills.
treasure-data/td-skills
email-campaign
This skill should be used when the user asks to "create an email", "build an email campaign", "design an email template", "generate an email for a segment", "preview an email", or "push an email to Engage". Generates enterprise-grade HTML email templates with live preview in Treasure Studio and natural language editing, then pushes the final version to Treasure Engage.
treasure-data/td-skills
action-report
YAML format reference for action reports rendered via preview_action_report. MUST be read before writing any action report YAML — defines the report structure (title, summary, actions array) and action item fields (as_is, to_be, reason, priority, category, impact) with incremental build workflow. Required by seo-analysis and any skill that produces prioritized recommendations.
treasure-data/td-skills
grid-dashboard
YAML format reference for grid dashboards rendered via preview_grid_dashboard. MUST be read before writing any dashboard YAML — defines the page structure, 6 cell types (kpi, gauge, scores, table, chart, markdown), grid layout rules, cell merging syntax, and incremental build workflow. Required by seo-analysis and any skill that produces visual data dashboards.
treasure-data/td-skills
seo-analysis
Runs SEO and AEO (Answer Engine Optimization) analysis on websites or specific pages. Use when the user mentions SEO, AEO, search rankings, search optimization, or wants to analyze how their pages perform in search engines and AI answers. Produces a data dashboard and action report with before/after recommendations.
treasure-data/td-skills
aps-doc-core
Core documentation generation patterns and framework for Treasure Data pipeline layers. Provides shared templates, quality validation, testing framework, and Confluence integration used by all layer-specific documentation skills.
treasure-data/td-skills
aps-doc-id-unification
Expert documentation generation for ID unification layers. Documents identity resolution algorithms, merge strategies, match rules, entity graphs, and multi-workflow orchestration. Use when documenting ID unification processes.
Didn't find tool you were looking for?