HQL Commands for Data Analytics: HiveQL Syntax, Examples, and Best Practices

In this guide, HQL means HiveQL—Apache Hive’s SQL-like language for querying and transforming data in distributed storage. Hibernate also uses “HQL” for Hibernate Query Language, but that Java persistence language is outside this article’s scope. HiveQL resembles SQL while adding Hive-specific DDL, partitioning, loading, distribution, and execution-plan commands.

You will learn how to connect with Beeline, inspect metadata, create and load tables, write analytical queries, use joins and window functions, materialize results, and diagnose expensive or incorrect queries. Syntax and availability can vary by Hive version, vendor distribution, authentication setup, and execution engine, so verify examples in your target environment. Apache’s language manual was updated December 12, 2024 and is the authoritative reference for feature details: Hive language manual.

#	Preview	Product	Price
1		Elebase USB to USB C Adapter for iPhone 18 Pro Max,USBC Car Charger Adapter	$8.99	Buy on Amazon
2		Anker USB-C Hub, 5-in-1 USB Hub for Laptops, 4K HDMI Multiport Adapter	$19.99	Buy on Amazon
3		Anker USB C Hub, 7in1 Multi-Port USB Adapter, 4K@60Hz USBC to HDMI Splitter	$21.99	Buy on Amazon
4		UGREEN USB to USB C Adapter Combo 4-Pack, 10Gbps USB C Converter Space Gray	$7.99	Buy on Amazon
5		Anker USB C Hub, 5-in-1 USBC to HDMI Splitter with 4K Display	$24.99	Buy on Amazon

What HiveQL is—and what it is not

HiveQL is declarative: you describe the result, and Hive plans distributed work across files, tasks, reducers, and the configured execution engine. A valid statement can therefore trigger scans, shuffles, sorting, and substantial infrastructure usage. HiveQL is not interchangeable with PostgreSQL, MySQL, Spark SQL, Trino, or Hibernate HQL. Broad SQL constructs are often portable, while commands such as LOAD DATA, DISTRIBUTE BY, SORT BY, CLUSTER BY, SerDe properties, and some functions are Hive-specific.

Hive tables commonly reside in distributed storage and may use ORC, Parquet, text, or other formats. Performance depends on partitioning, file sizes, compression, statistics, selectivity, and the configured engine—not on SQL syntax alone.

#1 Best Overall

Elebase USB to USB C Adapter for iPhone 18 Pro Max,USBC Car Charger Adapter

• Read Before You Buy — No Video Output: These adapters support charging and USB 2.0 data transfer, but cannot transmit video signals. Except for standard USB webcams (which use USB data only), they are not compatible with HDMI/DisplayPort cables, video-capable USB-C hubs, or docking stations with video output.
• Convert USB-A Ports to USB-C: Designed to connect USB-C earphones, cables, flash drives, card readers, and other USB-C accessories to standard USB-A ports. Plug-and-play with no drivers or software required.
• Aluminum Alloy Housing: Built with a sturdy aluminum alloy shell that aids in heat dissipation and protects against daily wear and scratches. Designed to maintain a stable and secure connection.
• Compact & Travel-Friendly: The ultra-compact design allows the adapter to stay plugged into your device without blocking adjacent ports or adding bulk, reducing wear and tear on your original USB ports.
• 12-Month Warranty: Backed by a 12-month manufacturer warranty for peace of mind. Designed to meet strict quality control standards for reliable everyday performance.

$8.99

Buy on Amazon

Run HiveQL with Beeline

For HiveServer2 deployments, use the newer Beeline client rather than the legacy Hive CLI:

beeline -u 'jdbc:hive2://host:10000/default'

The host, port, authentication, transport mode, and TLS options depend on your deployment. After connecting:

SHOW DATABASES;

USE analytics;

SHOW TABLES;

Beeline displays result sets and server errors; permissions are enforced by the deployment’s authorization layer.

Quick reference: essential commands

The SELECT documentation covers the query clauses and current_database().

Inspect databases, tables, columns, and functions

SHOW TABLES IN analytics;

DESCRIBE database_name.table_name;
DESCRIBE FORMATTED database_name.table_name;
DESCRIBE EXTENDED database_name.table_name;
SHOW CREATE TABLE database_name.table_name;
SHOW PARTITIONS database_name.table_name;
SHOW TABLE EXTENDED IN database_name LIKE 'sales*';

• DESCRIBE gives a quick column and type view.
• DESCRIBE FORMATTED exposes location, storage format, SerDe, partitioning, and table properties.
• DESCRIBE EXTENDED returns still more detailed metadata.
• SHOW PARTITIONS confirms that expected partition values exist.
• SHOW CREATE TABLE provides reproducible DDL.

SHOW FUNCTIONS;
DESCRIBE FUNCTION sum;
DESCRIBE FUNCTION EXTENDED percentile_approx;

Use function discovery instead of assuming that a function or argument form exists in your Hive distribution.

Create analytical tables and views

Create a database and managed table

CREATE DATABASE IF NOT EXISTS analytics
COMMENT 'Business analytics database';

CREATE TABLE IF NOT EXISTS sales (
    order_id       BIGINT,
    customer_id    BIGINT,
    order_date     DATE,
    region         STRING,
    amount         DECIMAL(18,2),
    status         STRING
)
STORED AS ORC;

Managed-table ownership and deletion behavior differ by Hive version and distribution. External tables, ACID tables, and storage formats have additional requirements; confirm them before production changes.

Create a partitioned table

CREATE TABLE sales_partitioned (
    order_id    BIGINT,
    customer_id BIGINT,
    amount      DECIMAL(18,2),
    status      STRING
)
PARTITIONED BY (
    order_date DATE,
    region STRING
)
STORED AS ORC;

Partition columns are separate from the main column list. A partition does not automatically make a query fast: the query needs a usable partition predicate and the optimizer must apply pruning.

Rank #2

Anker USB-C Hub, 5-in-1 USB Hub for Laptops, 4K HDMI Multiport Adapter

• 5-in-1 USB-C Hub: Experience comprehensive connectivity featuring a Power Delivery input, two USB-A 2.0 ports, a USB-A 3.0 port, and an HDMI port. (Note: The USB-C power delivery input port is only for connecting an external wall charger to power your laptop and cannot power peripheral devices.)
• 90W Pass-Through Charging: Achieve optimal charging with 90W pass-through power to your laptop, supported by a total input of 100W, with the hub reserving 10W for operational efficiency. (Note: Wall charger not included.)
• Quick Data Transfers: Accelerate your productivity with rapid data transfers using a high-speed 5Gbps USB 3.0 port and two 480Mbps USB 2.0 ports.
• 4K HDMI Display: Enhance your visual experience with a hub capable of delivering 4K resolution at 30Hz in both mirror and extend modes. Please note that this hub is compatible with MacBook (macOS 12 and newer), Windows 10 and 11, ChromeOS, and laptops equipped with DP Alt Mode and Power Delivery. Note: This device is not compatible with Linux.
• What You Get: Anker USB-C Hub (5-in-1, 4K HDMI), welcome guide, 18-month warranty, and our friendly customer service.

$19.99

Buy on Amazon

Create tables from queries and create views

CREATE TABLE monthly_revenue
STORED AS ORC
AS
SELECT
    year(order_date)  AS year_num,
    month(order_date) AS month_num,
    SUM(amount)       AS revenue
FROM sales
GROUP BY year(order_date), month(order_date);

CREATE VIEW regional_revenue AS
SELECT region, SUM(amount) AS revenue
FROM sales
GROUP BY region;

Alter, truncate, and drop

ALTER TABLE sales RENAME TO sales_archive;
ALTER TABLE sales ADD COLUMNS (sales_channel STRING);
ALTER TABLE sales SET TBLPROPERTIES ('comment' = 'Transactional sales data');

TRUNCATE TABLE staging_sales;
DROP VIEW IF EXISTS regional_revenue;
DROP TABLE IF EXISTS sales_archive;

Test destructive operations against the intended table type. Transactional UPDATE, DELETE, and MERGE are not universally available without ACID configuration and compatible deployment support.

Load and write data safely

Load files

LOAD DATA INPATH '/data/sales.csv'
INTO TABLE sales;

LOAD DATA LOCAL INPATH '/tmp/sales.csv'
INTO TABLE sales;

LOAD DATA INPATH '/data/sales.csv'
OVERWRITE INTO TABLE sales;

LOAD DATA INPATH '/data/sales/2026-08-01.csv'
INTO TABLE sales_partitioned
PARTITION (order_date = '2026-08-01', region = 'US');

LOCAL refers to the client machine. In many Hive versions, LOAD DATA moves or copies files rather than transforming rows; semantics have changed across releases. See the DML manual.

Append versus replace

INSERT INTO TABLE monthly_revenue
SELECT year(order_date), month(order_date), SUM(amount)
FROM sales
GROUP BY year(order_date), month(order_date);

INSERT OVERWRITE TABLE monthly_revenue
SELECT year(order_date), month(order_date), SUM(amount)
FROM sales
GROUP BY year(order_date), month(order_date);

INSERT INTO appends. INSERT OVERWRITE replaces the target, or the relevant partition under partitioned-table semantics. Treat overwrite as a data-replacement operation, not a faster append.

Write a static partition

INSERT OVERWRITE TABLE sales_partitioned
PARTITION (order_date = '2026-08-01', region = 'US')
SELECT order_id, customer_id, amount, status
FROM staging_sales
WHERE order_date = '2026-08-01'
  AND region = 'US';

The selected expressions must align with the target’s non-partition columns. Dynamic partitioning uses different configuration and safety controls; check your distribution before enabling it.

Select and filter rows

SELECT order_id, customer_id, amount
FROM sales
LIMIT 100;

SELECT order_id, amount
FROM sales
WHERE status = 'completed'
  AND amount > 100;

SELECT order_id, amount
FROM sales
WHERE order_date >= '2026-01-01'
  AND order_date <  '2026-02-01';

SELECT DISTINCT region
FROM sales;

Project only needed columns instead of using SELECT *; this clarifies the data contract and can reduce column reads. A half-open date range is usually preferable to wrapping a partition column in functions. DISTINCT may require a costly distributed shuffle on high-cardinality data.

Aggregate data for reports

SELECT
    region,
    COUNT(*)       AS order_count,
    SUM(amount)    AS revenue,
    AVG(amount)    AS average_order_value,
    MIN(amount)    AS smallest_order,
    MAX(amount)    AS largest_order
FROM sales
GROUP BY region;

SELECT region, SUM(amount) AS revenue
FROM sales
GROUP BY region
HAVING SUM(amount) > 100000;

HAVING filters groups after aggregation (supported from Hive 0.7.0). On older releases, aggregate in a subquery and filter its result:

SELECT region, revenue
FROM (
    SELECT region, SUM(amount) AS revenue
    FROM sales
    GROUP BY region
) x
WHERE revenue > 100000;

Conditional aggregation

SELECT
    region,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_orders,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders,
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_revenue
FROM sales
GROUP BY region;

Grouped queries should select grouping keys alongside aggregate expressions; see the GROUP BY documentation.

Rank #3

Sale

Anker USB C Hub, 7in1 Multi-Port USB Adapter, 4K@60Hz USBC to HDMI Splitter

• Sleek 7-in-1 USB-C Hub: Features an HDMI port, two USB-A 3.0 ports, and a USB-C data port, each providing 5Gbps transfer speeds. It also includes a USB-C PD input port for charging up to 100W and dual SD and TF card slots, all in a compact design.
• Flawless 4K@60Hz Video with HDMI: Delivers exceptional clarity and smoothness with its 4K@60Hz HDMI port, making it ideal for high-definition presentations and entertainment. (Note: Only the HDMI port supports video projection; the USB-C port is for data transfer only.)
• Double Up on Efficiency: The two USB-A 3.0 ports and a USB-C port support a fast 5Gbps data rate, significantly boosting your transfer speeds and improving productivity.
• Fast and Reliable 85W Charging: Offers high-capacity, speedy charging for laptops up to 85W, so you spend less time tethered to an outlet and more time being productive.
• What You Get: Anker USB-C Hub (7-in-1), welcome guide, 18-month warranty, and our friendly customer service.

$21.99

Buy on Amazon

Join datasets without corrupting metrics

SELECT s.order_id, s.amount, c.customer_segment
FROM sales s
JOIN customers c
  ON s.customer_id = c.customer_id;

SELECT s.order_id, s.amount, c.customer_segment
FROM sales s
LEFT JOIN customers c
  ON s.customer_id = c.customer_id;

Hive also supports right and full outer joins. A one-to-many join can multiply facts and inflate sums. Null keys do not match under ordinary equality. Filters on the right table belong in the ON clause when unmatched left rows must remain:

SELECT s.order_id, c.customer_segment
FROM sales s
LEFT JOIN customers c
  ON s.customer_id = c.customer_id
 AND c.is_active = true;

Putting c.is_active = true in WHERE removes null matches and effectively makes the result inner-join-like.

Prevent duplicate multiplication

WITH distinct_tags AS (
    SELECT DISTINCT customer_id
    FROM customer_tags
)
SELECT c.customer_id, SUM(o.amount) AS revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN distinct_tags t ON c.customer_id = t.customer_id
GROUP BY c.customer_id;

Filter both inputs before a large join, project only required columns, and validate key uniqueness. Broadcast or map-side strategies help only when the smaller input fits the relevant memory and configuration limits.

Window functions for analytics

Window functions calculate across related rows without collapsing them. PARTITION BY defines groups, ORDER BY defines sequence, and an explicit frame defines which rows contribute. Hive’s windowing enhancements begin in Hive 0.11.0; consult the windowing manual.

Rank rows and find top N per group

SELECT customer_id, order_id, amount,
       RANK() OVER (
           PARTITION BY customer_id
           ORDER BY amount DESC
       ) AS amount_rank
FROM sales;

• ROW_NUMBER() gives every row a unique sequence.
• RANK() leaves gaps after ties.
• DENSE_RANK() does not leave gaps.

WITH ranked_products AS (
    SELECT category, product_id, revenue,
           ROW_NUMBER() OVER (
               PARTITION BY category
               ORDER BY revenue DESC, product_id
           ) AS rn
    FROM product_revenue
)
SELECT category, product_id, revenue
FROM ranked_products
WHERE rn <= 3;

The outer query is needed because a window alias generally cannot be referenced in the same query block’s WHERE clause. Add a deterministic tie-breaker to reproducibly number ties.

Running totals and previous values

SELECT customer_id, order_date, amount,
       SUM(amount) OVER (
           PARTITION BY customer_id
           ORDER BY order_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_spend,
       LAG(amount, 1) OVER (
           PARTITION BY customer_id ORDER BY order_date
       ) AS previous_amount,
       LEAD(amount, 1) OVER (
           PARTITION BY customer_id ORDER BY order_date
       ) AS next_amount
FROM sales;

Duplicate dates, null ordering, and the difference between ROWS and RANGE can change results. Window operations may require repartitioning and sorting.

Period-over-period change

WITH monthly AS (
    SELECT year(order_date) AS year_num,
           month(order_date) AS month_num,
           SUM(amount) AS revenue
    FROM sales
    GROUP BY year(order_date), month(order_date)
)
SELECT year_num, month_num, revenue,
       revenue - LAG(revenue) OVER (
           ORDER BY year_num, month_num
       ) AS revenue_change
FROM monthly;

CTEs, subqueries, and set operations

WITH customer_totals AS (
    SELECT customer_id, SUM(amount) AS lifetime_value
    FROM sales
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT customer_id, lifetime_value
FROM customer_totals
WHERE lifetime_value >= 1000;

CTEs are temporary result sets scoped to one statement and can be used with SELECT, INSERT, CTAS, and view creation from Hive 0.13.0. See Hive CTE syntax.

Rank #4

Sale

UGREEN USB to USB C Adapter Combo 4-Pack, 10Gbps USB C Converter Space Gray

• Dual Converters, Infinite Potential：Includes 2× USB C male to USB A female adapters and 2× USB A male to USB C female adapters. Perfect for a wide range of uses—tablets with Bluetooth keyboards, expand USB ports on macbook, and more. Two different converters for all your daily needs
• Next-Level 10Gbps & 3A Charging: No more slow 480Mbps, this usb to usb c adapter has a transfer speed of up to 10Gbps, allowing you to do more transferring in less time. This usb adapter fits both USB A and USB C charger, supporting up to 3A fast charging
• Upgraded Exquisite Craftsmanship: With an aluminum alloy housing and metal connector, the usbc to usb adapter is extremely durable and sturdy. Rigorously tested to withstand more than 10,000 times of plugging and unplugging, ensuring long-lasting performance
• Broad Compatible: The usb c to usb adapter widely supports all USB C/ USB A devices like laptops, tablets, cellphones, car chargers, and phone chargers. Such as compatible with MacBook Pro/Air 2023/2022, Thunderbolt 4/3 Devices,Apple MagSafe Watch 9/8/7/SE/Ultra, iPad Pro 2022/2021, Samsung Galaxy S23/S20/S10, and iPhone 17/16/15 Pro. Plug and play
• Please Note: To reach 10Gbps speed, keep the cable under 3.3 ft. For USB A Male to USB C adapters, try flipping the USB C connector. USB C Male to USB A adapters support bidirectional 10Gbps transfer within 3.3 ft

$7.99

Buy on Amazon

SELECT customer_id, amount FROM online_sales
UNION ALL
SELECT customer_id, amount FROM store_sales;

Use UNION ALL when duplicates are intentional. Use duplicate-removing UNION or supported UNION DISTINCT syntax only when deduplication is required, because it adds work.

Sort, distribute, and cluster results

SELECT * FROM sales ORDER BY amount DESC LIMIT 100;
SELECT * FROM sales SORT BY region, amount DESC;
SELECT * FROM sales DISTRIBUTE BY region SORT BY region, amount DESC;
SELECT * FROM sales CLUSTER BY region;

Keep ORDER BY when global order is a requirement; otherwise, a local sort may avoid unnecessary coordination.

Function patterns useful in analytics

COUNT(*), COUNT(column_name), COUNT(DISTINCT customer_id)
SUM(amount), AVG(amount), MIN(amount), MAX(amount)

CASE WHEN amount > 100 THEN 'large' ELSE 'small' END
COALESCE(region, 'Unknown')
NULLIF(amount, 0)

LOWER(email), UPPER(region), TRIM(customer_name)
CONCAT(first_name, ' ', last_name)
REGEXP_REPLACE(phone, '[^0-9]', '')

YEAR(order_date), MONTH(order_date), DAY(order_date)
DATE_ADD(order_date, 7)
DATEDIFF(end_date, start_date)

percentile_approx(amount, 0.50)
percentile_approx(amount, array(0.50, 0.90, 0.99))

percentile_approx returns an approximation, not an exact percentile. Date and timestamp behavior can depend on Hive version, time zone, implicit casts, and configuration. Check support with DESCRIBE FUNCTION EXTENDED.

Partition-aware analytics

SELECT region, SUM(amount) AS revenue
FROM sales_partitioned
WHERE order_date >= '2026-08-01'
  AND order_date <  '2026-09-01'
GROUP BY region;

This predicate exposes a range on the partition column. The following pattern may prevent effective pruning depending on the table definition and optimizer:

WHERE YEAR(order_date) = 2026
  AND MONTH(order_date) = 8

For troubleshooting:

SHOW PARTITIONS sales_partitioned;

EXPLAIN
SELECT region, SUM(amount)
FROM sales_partitioned
WHERE order_date = '2026-08-01'
GROUP BY region;

• Confirm that the requested partition exists.
• Use the correct data type and partition value format.
• Keep the partition predicate visible and pushable.
• Check the plan for partition pruning.
• Refresh or collect statistics when metadata is stale.

Inspect plans and improve performance

EXPLAIN
SELECT region, SUM(amount)
FROM sales
GROUP BY region;

EXPLAIN EXTENDED
SELECT * FROM sales
WHERE order_date = '2026-08-01';

EXPLAIN VECTORIZATION
SELECT region, SUM(amount)
FROM sales
GROUP BY region;

Hive documents optional modes such as EXTENDED, CBO, AST, DEPENDENCY, AUTHORIZATION, LOCKS, VECTORIZATION, and ANALYZE, but support varies by version and distribution. See the EXPLAIN manual.

Look for full scans where pruning was expected, large shuffles from joins or aggregations, excessive reducers, missing statistics, non-vectorized stages, skewed keys, unintended cross joins, and repeated scans. Settings such as SET hive.execution.engine=tez; are deployment-specific; inspect current values with SET; rather than assuming Tez, Spark, or MapReduce.

Correctness checks and common failures

Column not found

DESCRIBE table_name;
SHOW CREATE TABLE table_name;

Check spelling, aliases and scope, quoted identifiers, nested fields, and partition-column names.

Best Value

Anker USB C Hub, 5-in-1 USBC to HDMI Splitter with 4K Display

• 5-in-1 Connectivity: Equipped with a 4K HDMI port, a 5 Gbps USB-C data port, two 5 Gbps USB-A ports, and a USB C 100W PD-IN port. Note: The USB C 100W PD-IN port supports only charging and does not support data transfer devices such as headphones or speakers.
• Powerful Pass-Through Charging: Supports up to 85W pass-through charging so you can power up your laptop while you use the hub. Note: Pass-through charging requires a charger (not included). Note: To achieve full power for iPad, we recommend using a 45W wall charger.
• Transfer Files in Seconds: Move files to and from your laptop at speeds of up to 5 Gbps via the USB-C and USB-A data ports. Note: The USB C 5Gbps Data port does not support video output.
• HD Display: Connect to the HDMI port to stream or mirror content to an external monitor in resolutions of up to 4K@30Hz. Note: The USB-C ports do not support video output.
• What You Get: Anker 332 USB-C Hub (5-in-1), welcome guide, our worry-free 18-month warranty, and friendly customer service.

$24.99

Buy on Amazon

Non-grouped column error

This is invalid because status is neither grouped nor aggregated:

SELECT region, status, SUM(amount)
FROM sales
GROUP BY region;

Either group by both columns or choose an aggregate representing the intended business rule.

No rows returned

SHOW PARTITIONS table_name;
SELECT COUNT(*) FROM table_name;
SELECT COUNT(*) FROM table_name
WHERE partition_date = '2026-08-01';

Check partition values, date formats, nulls, stale metadata, and filters on the nullable side of a left join.

Nulls, rates, and division

• COUNT(*) counts rows; COUNT(column) generally excludes null values.
• Aggregates such as SUM and AVG need explicit interpretation when values are null.
• Cast before division to avoid integer-division surprises.

SELECT completed_orders / CAST(total_orders AS DOUBLE) AS completion_rate
FROM metrics;

SELECT CASE WHEN order_count = 0 THEN NULL
            ELSE revenue / CAST(order_count AS DOUBLE)
       END AS average_order_value
FROM daily_metrics;

Slow joins or unexpectedly huge output

1. Compare row counts on both inputs.
2. Check whether join keys are unique.
3. Deduplicate dimensions and filter both sides first.
4. Select only required columns.
5. Test a small date range.
6. Consider broadcast strategies only after validating size and cluster settings.

Global sort stalls

Use SORT BY when a globally ordered result is unnecessary. Keep ORDER BY for a genuine global-order requirement, ideally with a restrictive LIMIT.

End-to-end monthly top-region report

USE analytics;

WITH monthly_region_sales AS (
    SELECT
        YEAR(s.order_date)  AS year_num,
        MONTH(s.order_date) AS month_num,
        s.region,
        COUNT(*)           AS order_count,
        SUM(s.amount)      AS revenue
    FROM sales s
    WHERE s.order_date >= '2026-01-01'
      AND s.order_date <  '2027-01-01'
      AND s.status = 'completed'
    GROUP BY YEAR(s.order_date), MONTH(s.order_date), s.region
),
ranked_regions AS (
    SELECT year_num, month_num, region, order_count, revenue,
           RANK() OVER (
               PARTITION BY year_num, month_num
               ORDER BY revenue DESC
           ) AS revenue_rank
    FROM monthly_region_sales
)
SELECT year_num, month_num, region, order_count, revenue, revenue_rank
FROM ranked_regions
WHERE revenue_rank <= 5
ORDER BY year_num, month_num, revenue_rank;

To materialize the result, create a compatible target table and use one statement with the CTE:

INSERT OVERWRITE TABLE monthly_top_regions
WITH monthly_region_sales AS (
    SELECT YEAR(order_date) AS year_num,
           MONTH(order_date) AS month_num,
           region,
           COUNT(*) AS order_count,
           SUM(amount) AS revenue
    FROM sales
    WHERE order_date >= '2026-01-01'
      AND order_date <  '2027-01-01'
      AND status = 'completed'
    GROUP BY YEAR(order_date), MONTH(order_date), region
),
ranked_regions AS (
    SELECT year_num, month_num, region, order_count, revenue,
           RANK() OVER (
               PARTITION BY year_num, month_num
               ORDER BY revenue DESC
           ) AS revenue_rank
    FROM monthly_region_sales
)
SELECT year_num, month_num, region, order_count, revenue, revenue_rank
FROM ranked_regions
WHERE revenue_rank <= 5;

The CTE exists only for that statement. Materialize it as a table or view if a later statement needs the result.

Version and portability checklist

• CTEs and current_database() require Hive 0.13.0 or later according to the cited documentation.
• HAVING support begins with Hive 0.7.0.
• Windowing features and syntax have version-specific additions from Hive 0.11.0 onward.
• Extended EXPLAIN modes are not guaranteed in every distribution.
• Transactional DML, dynamic partitions, authorization, timestamp semantics, and table behavior depend on configuration and edition.
• Test Hive-specific clauses before moving queries to Spark SQL, Trino, Presto, BigQuery, Snowflake, or relational databases.

For the complete command categories and current syntax, consult Apache Hive’s language manual, introduction, and tutorial.

Quick Recap

Bestseller No. 1

Elebase USB to USB C Adapter for iPhone 18 Pro Max,USBC Car Charger Adapter

$8.99

Bestseller No. 2

Anker USB-C Hub, 5-in-1 USB Hub for Laptops, 4K HDMI Multiport Adapter

$19.99

SaleBestseller No. 3

Anker USB C Hub, 7in1 Multi-Port USB Adapter, 4K@60Hz USBC to HDMI Splitter

$21.99

SaleBestseller No. 4

UGREEN USB to USB C Adapter Combo 4-Pack, 10Gbps USB C Converter Space Gray

$7.99

Bestseller No. 5

Anker USB C Hub, 5-in-1 USBC to HDMI Splitter with 4K Display

$24.99

Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.