Hive

From stored files to checked SQL results

01 / 61

Topic overview

Follow movie-rating records from stored files to SQL results whose meaning and row counts can be checked.

TopicContent
1. Understand HiveSQL, the metastore, execution and storage
2. Register the dataFiles, schemas, table locations and lifecycle
3. Write the queryFilters, counts, averages, grouping and ranking
4. Join the tablesMissing matches, duplicate keys and row counts
5. Select the inputNested data, partitions, pruning and plan evidence
6. Apply and reviewLab 10, preserving results and a worked final case
02 / 61

Movie ratings

A rating row records one user’s score for one movie at a time. We want the most-rated movies and their average scores.

We use six invented ratings to trace the queries; Lab 10 uses the larger MovieLens dataset.

UserMovieRating
1105
2103
3204
4205
5203
6305
03 / 61

File-to-result workflow

Hive exposes stored data through named tables and HiveQL, its SQL query language. A table definition tells the engine how to interpret the files.

The result depends on both correct metadata and correct query logic.

Stored fileTable definitionQueryResultratings.tsvmovie101020202030rating534535ratingsmovie_id: INTrating: INTTab-separated fieldsLocation in HDFSGroup by movieCount ratingsAverage ratingSort: count descending,then movie IDTake the first 2MovieCountAvg2010324.04.0
04 / 61

Hive and Spark SQL

Hive and Spark SQL can both express analytical queries over distributed data. Their execution and catalog configuration may differ.

Choose by the environment and workflow; a SQL expression does not imply that Hive runs on Spark.

HiveSpark SQLHiveQL queryHive plannerConfigured execution engineSQL query / DataFrame APISpark SQL plannerSpark executionStored dataEither system can query compatible files; shared table metadata requires configuration.
05 / 61

Hive architecture

The driver coordinates query compilation and execution. The metastore supplies table metadata; execution workers read and process the actual data.

The metastore describes the files; it does not contain all their rating rows.

SQL clientSubmit a queryHive driverResolve + planCoordinate executionMetastoreSchema + locationExecution workersRead + computeAggregate resultsHDFS / object storageActual records
06 / 61

Metastore

The metastore is a catalog of table definitions, schemas, storage locations and partition metadata.

A table name is useful only when its metadata agrees with the stored records.

Metadata fieldRatings examplePurpose
Table namemovielens.ratingsResolve the SQL name
Columnsuser_id, movie_id, rating, rating_tsInterpret fields
FormatTab-delimited textDecode each record
Location/user/hive/data/movielens/Find the dataset
PartitionsNone in this first tableDescribe optional subdivisions
07 / 61

Query execution

Hive resolves the query using metadata, builds an execution plan and asks the configured engine to run it.

Workers read the relevant files, compute partial results and combine them as the plan requires.

Resolveratings + movie_idCatalog lookupPlanScan + group +countExecution operatorsExecuteRead filepartitionsCombine by movieReturn20 → 310 → 2 · 30 → 1
08 / 61

Execution engine

Hive’s execution engine depends on its release and configuration. Amazon EMR has used Tez as the Hive default since release 5.x.

Read the actual setting rather than assuming that the presence of Spark determines how Hive executes.

SET hive.execution.engine;

Record with the result

Hive version

EMR release

Reported engine setting

09 / 61

Schema-on-read

Schema-on-read applies a declared structure when existing data is read. Registering a table does not clean or rewrite those records.

Correct column names alone cannot fix a wrong delimiter, invalid value or wrong file location.

Stored record1<TAB>10<TAB>5<TAB>1000Existing bytesRead using metadataINT, INT, INT, BIGINTTab delimiterInterpreted rowuser_id 1 · movie_id 10rating 5 · timestamp 1000
10 / 61

Table registration

A queryable table needs compatible files, a matching schema and format, and a catalog entry pointing to the right location.

Validate the interpreted rows before trusting an aggregate.

StagePlace dataset inHDFSCheck filesDeclareColumns + delimiterTable locationInspectMetadata + samplerowsCounts + rangesQueryVerifiedinterpretationMeaningfulaggregation
11 / 61

Local files and HDFS

The primary node’s local filesystem and HDFS are different storage locations. Downloading a file locally does not place it in HDFS.

The table location must point to the distributed dataset that workers can read.

Primary-node local diskratings.tsvCreated by downloadLocal pathhadoop fs -putHDFS dataset/user/hive/data/movielens/ratings.tsvWorkers read this location
12 / 61

Staging ratings

These commands create the HDFS directory, upload ratings.tsv and list the destination. The local file must already exist.

Check for an existing upload before repeating the operation so the dataset is not duplicated.

hadoop fs -mkdir -p /user/hive/data/movielens
hadoop fs -put ratings.tsv /user/hive/data/movielens/
hadoop fs -ls /user/hive/data/movielens/

Expected destination

HDFS directory: movielens

Contains: ratings.tsv

Tab-separated records; no header

13 / 61

Database and table names

A Hive database provides a namespace for tables. USE selects the default namespace for unqualified names.

movielens.ratings identifies the table explicitly, even if the current database changes.

CREATE DATABASE IF NOT EXISTS movielens;
USE movielens;
SHOW TABLES;

Name resolution

ratings → current database

movielens.ratings → explicit database

Database name ≠ one data file

14 / 61

External table definition

CREATE EXTERNAL TABLE registers the ratings fields, tab delimiter and storage directory. The four fields follow the file’s column order.

BIGINT provides room for the timestamp value; the table reads compatible records from its directory.

CREATE EXTERNAL TABLE ratings (
  user_id INT, movie_id INT,
  rating INT, rating_ts BIGINT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
LOCATION '/user/hive/data/movielens/';

Four fields

user → movie → rating → time

No header row

Tab-separated text

15 / 61

Column order and types

For these delimited records, the first field becomes user_id, the second movie_id, the third rating and the fourth rating_ts.

A swapped declaration can produce plausible numbers with the wrong meaning.

1user_idINT10movie_idINT5ratingINT1000rating_tsBIGINT
16 / 61

File format

The reader needs a format and delimiter that match the bytes. Tab-separated values are not comma-separated values.

A mismatched delimiter may turn several intended fields into one field and leave the remaining fields invalid or null.

Actual TSV record1<TAB>10<TAB>5<TAB>1000Four tab-separated fieldsWrong comma readerNo comma boundaries foundFields are misinterpretedCorrect tab reader1 / 10 / 5 / 1000Four declared values
17 / 61

Dataset location

A table location identifies a dataset directory that may contain multiple compatible files.

Keep unrelated files and duplicate uploads out of that directory; the catalog does not know which copies you intended to count.

Table location/data/ratings/Read compatible data filespart-000.tsvIntended recordspart-001.tsvIntended recordscopy-of-part-000.tsvDuplicate copy
18 / 61

Table validation

Validate both the table definition and the interpreted values. A successful CREATE TABLE only confirms registration.

Check row count and valid rating range; investigate nulls and unexpected identifiers.

DESCRIBE FORMATTED ratings;
SELECT * FROM ratings LIMIT 5;
SELECT COUNT(*) FROM ratings;
SELECT MIN(rating), MAX(rating) FROM ratings;
SELECT COUNT(*) FROM ratings WHERE rating IS NULL;

For the six-row example

Count = 6

Rating range = 3 to 5

Null rating count = 0

19 / 61

Managed and external tables

A managed table normally includes data in Hive’s lifecycle. An external table normally separates catalog registration from the existing files.

DROP behaviour also depends on release and properties; external does not mean that data is protected from every deletion path.

OperationManaged table, normallyExternal table, normally
CREATEHive manages table data lifecycleRegister an existing location
DROP TABLERemove metadata and table dataRemove metadata; retain files
Important exceptionPurge and storage settings matterExternal purge can remove files
ProtectionUse appropriate access and recovery controlsUse appropriate access and recovery controls
20 / 61

Deletion behaviour

An external table points to an important dataset. Its release supports external.table.purge=true, and that property is enabled.

Dropping this table can delete its data too; inspect the actual metadata and release before changing it.

DESCRIBE FORMATTED ratings;
SHOW TBLPROPERTIES ratings;

Inspect before a destructive action

Table type

Storage location

Purge properties + release support

21 / 61

Query workflow

SQL describes the required relation. For this analysis, rows are filtered, grouped by movie, aggregated, filtered by group result, then ordered.

This is the logical meaning of the query; the physical engine may rearrange equivalent work.

WHEREAccept rating rowsRow-level ruleGROUP BYCollect equal movieIDsCOUNT + AVGHAVINGAccept groupresultsMinimum countORDER + LIMITRank eligiblegroupsReturn requestedrows
22 / 61

Projection and filtering

SELECT chooses output expressions; WHERE keeps rows for which its condition is true.

For rating >= 4, the six-row example keeps four ratings: 5, 4, 5 and 5.

SELECT user_id, movie_id, rating
FROM ratings
WHERE rating >= 4;

Rows that survive

User 1 · movie 10 · rating 5

User 3 · movie 20 · rating 4

User 4 · movie 20 · rating 5

User 6 · movie 30 · rating 5

23 / 61

COUNT and NULL

COUNT(*) counts rows. COUNT(column) counts rows where that column is non-null.

In a variation with ratings 5, NULL and 3, the two counts are 3 and 2 respectively.

Input ratingCOUNT(*) contributionCOUNT(rating) contribution
511
NULL10
311
Total32
24 / 61

Grouped counts

GROUP BY movie_id forms one group for each distinct movie identifier. COUNT(*) gives the number of rating rows in each group.

The six input rows become three groups with counts 2, 3 and 1.

Six rating rows10 → 5, 320 → 4, 5, 330 → 5GROUP BYMovie 10Count = 2 · Average = 4Movie 20Count = 3 · Average = 4Movie 30Count = 1 · Average = 5
25 / 61

Average ratings

AVG(rating) computes the mean of non-null rating values within each group.

Movie 10 averages 4, movie 20 averages 4, and movie 30 averages 5; a higher average can come from fewer ratings.

MovieRatingsCount and average
105, 32 ratings; average 4
204, 5, 33 ratings; average 4
3051 rating; average 5
26 / 61

Most-rated movies

“Most rated” means largest rating count. It does not mean highest mean score.

Order by count and add movie_id as a tie-breaker so the requested top rows are well defined.

SELECT movie_id, COUNT(*) AS rating_count,
       AVG(rating) AS average_rating
FROM ratings
GROUP BY movie_id
ORDER BY rating_count DESC, movie_id
LIMIT 2;

Top two in the example

20 · count 3 · average 4

10 · count 2 · average 4

27 / 61

HAVING

HAVING filters aggregated groups. WHERE filters source rows before aggregation.

A minimum review count is a group condition; putting the same idea in a row predicate cannot test the completed group.

SELECT movie_id, COUNT(*) AS n, AVG(rating) AS avg_r
FROM ratings
WHERE rating BETWEEN 1 AND 5
GROUP BY movie_id
HAVING COUNT(*) >= 2 AND AVG(rating) >= 4
ORDER BY avg_r DESC, movie_id;

Eligible groups

10 · n = 2 · avg = 4

20 · n = 3 · avg = 4

30 excluded: only one rating

28 / 61

Rows and groups

A row-level rating threshold changes the values available for each average. An average threshold tests the group after its values have been combined.

Filtering out low ratings before calculating an average can inflate that average and answer a different question.

WHERE rating >= 4Movie 10 keeps only 5Average becomes 5Lower rating discardedHAVING AVG(rating) >= 4Movie 10 uses 5 and 3Average remains 4Whole group qualifies
29 / 61

ORDER BY and LIMIT

ORDER BY establishes the requested result order. LIMIT restricts the number of returned rows.

LIMIT alone does not define the “top” movies; the ordering expression and tie-breaker define that choice.

LIMIT 2 aloneTwo returned rowsNo promised rankingCannot claim most ratedORDER BY n DESC, movie_idLargest counts firstTies use movie IDLIMIT 2 selects that order
30 / 61

Query exercise

Using the six original ratings, return movies with at least two ratings and an average of at least 4.

Write the GROUP BY and HAVING clauses, predict the result, and explain why movie 30 is excluded.

MovieAll ratingsCondition to evaluate
105, 3Count ≥ 2 and average ≥ 4
204, 5, 3Count ≥ 2 and average ≥ 4
305Count ≥ 2 and average ≥ 4
31 / 61

Eligible movies

Movies 10 and 20 both have an average of 4 and enough ratings. Movie 30 has only one rating.

The HAVING conditions apply to the completed groups, so every valid rating contributes to the average.

SELECT movie_id, COUNT(*) AS n, AVG(rating) AS avg_r
FROM ratings
GROUP BY movie_id
HAVING COUNT(*) >= 2 AND AVG(rating) >= 4
ORDER BY movie_id;

Answer

10 · 2 · 4.0

20 · 3 · 4.0

30 fails COUNT(*) >= 2

32 / 61

Ratings and movie titles

A join matches rows from two relations using a condition. Here ratings.movie_id links events to movie metadata.

The number of matching metadata rows determines whether rating rows are preserved, removed or multiplied.

RatingsMany events per moviemovie_id + ratingMatch on movie_idCheck lookup uniquenessChoose join typeEnriched rowsEvent + titleValidate row count
33 / 61

Join inputs

Use the original six ratings and a movie lookup containing (10, Alpha) and (20, Beta). Movie 30 has no lookup row.

A unique lookup has at most one match per rating; a missing key still needs an explicit join policy.

RatingsMovie 10: two ratingsMovie 20: three ratingsMovie 30: one ratingMovie lookup10 → Alpha20 → Beta30 → no matching row
34 / 61

Inner join

An inner join returns matching row pairs. A left rating without a movie match does not appear in the result.

With the current lookup, the six ratings become five joined rows; movie 30 is lost.

SELECT r.movie_id, m.title, r.rating
FROM ratings r
JOIN movies m ON r.movie_id = m.movie_id;

Result structure

10 / Alpha: 2 rows

20 / Beta: 3 rows

30: no output row

35 / 61

Left join

A left join retains every left row, adding null right-side fields when no match exists.

The unmatched movie-30 rating remains, with a null title; the six-row input still has six rows in this unique-lookup case.

Movie 102 rating rowsTitle: Alpha2 output rowsMovie 203 rating rowsTitle: Beta3 output rowsMovie 301 rating rowsTitle: NULL1 output rows
36 / 61

Duplicate lookup keys

If movie 10 appears twice in the lookup, each of its two ratings matches both entries.

The left join now has 4 + 3 + 1 = 8 rows, so later counts and sums can be inflated.

Movie 10 ratingsUser 1 → rating 5User 2 → rating 3Two lookup rows10 → Alpha10 → Alpha copyFour matching pairsUser 1 + AlphaUser 1 + Alpha copyUser 2 + AlphaUser 2 + Alpha copy
37 / 61

Filtering after a left join

WHERE is applied to the joined result. A condition on a right-side column can reject the null fields of unmatched rows.

WHERE m.title = 'Alpha' removes movie 30 as well as other titles, despite the left join.

SELECT r.movie_id, m.title, r.rating
FROM ratings r
LEFT JOIN movies m ON r.movie_id = m.movie_id
WHERE m.title = 'Alpha';

With the unique lookup

Movie 10: two rows remain

Movie 20: title is not Alpha

Movie 30: null does not pass

38 / 61

Conditions in ON

An ON condition controls which right rows qualify as matches. A left join still retains left rows that have no qualifying match.

Moving a right-side condition from WHERE to ON changes the result when unmatched left rows must remain.

SELECT r.movie_id, m.title, r.rating
FROM ratings r
LEFT JOIN movies m
  ON r.movie_id = m.movie_id AND m.title = 'Alpha';

With the unique lookup

Movie 10: title Alpha

Movie 20: null title, rows retained

Movie 30: null title, row retained

39 / 61

Join validation

Check lookup-key uniqueness, unmatched keys and before/after row counts before aggregating joined data.

Group by stable identifiers as well as titles when different movies could share the same title.

SELECT movie_id, COUNT(*) AS n
FROM movies
GROUP BY movie_id
HAVING COUNT(*) > 1;

Evidence to compare

Duplicate lookup keys

Unmatched rating count

Input and joined row counts

40 / 61

Nested data

Nested records contain fields within fields. Hive complex types, such as STRUCT and ARRAY, describe those relationships.

A matching serializer/deserializer, or SerDe, tells Hive how records are decoded.

Postid: 1text: Helloauthor: nested objectauthor STRUCTname: Anafollowers: 1200000
41 / 61

STRUCT and JSON SerDe

A STRUCT holds named fields within a column. This example reads an author object containing name and followers.

The JSON SerDe class and expected record layout must be supported by the installed Hive release.

CREATE EXTERNAL TABLE posts (
  id BIGINT, text STRING,
  author STRUCT<name:STRING, followers:INT>
)
ROW FORMAT SERDE
  'org.apache.hive.hcatalog.data.JsonSerDe'
LOCATION '/data/teaching/posts/';

Example JSON record

id: 1

text: "Hello"

author: {name: "Ana", followers: 1200000}

42 / 61

Nested field access

Dot notation selects a field inside a STRUCT. A nested input can still produce an ordinary flat SQL result.

For Ana with 1,200,000 followers and Ben with 500, the predicate retains only Ana’s post.

SELECT text, author.name, author.followers
FROM posts
WHERE author.followers > 1000000;

Result

Hello · Ana · 1200000

Ben’s record does not qualify

43 / 61

Partitioned tables

A Hive partition maps a partition-key value to a storage location. Date partitioning lets date predicates exclude other locations.

A storage partition is different from a Spark task partition or a SQL grouping key.

sales tableorder_id · amountPartition key: dtdt=2026-10-01Parquet part filesdt=2026-10-02Parquet part filesdt=2026-10-03Parquet part files
44 / 61

Partition definition

PARTITIONED BY declares the directory-level keys separately from the fields stored inside each record.

Here the Parquet rows contain order_id and amount; dt is supplied by the registered partition value.

CREATE EXTERNAL TABLE sales (
  order_id STRING, amount DOUBLE
)
PARTITIONED BY (dt STRING)
STORED AS PARQUET
LOCATION '/data/teaching/sales/';

Stored row vs partition

Row: o1, 10.0

Partition: dt=2026-10-01

Query row includes both

45 / 61

Partition registration

An existing directory needs a corresponding catalog partition entry before Hive can reliably query it as a table partition.

Creating the table does not automatically register every pre-existing directory.

ALTER TABLE sales ADD IF NOT EXISTS
PARTITION (dt='2026-10-01')
LOCATION '/data/teaching/sales/dt=2026-10-01/';
SHOW PARTITIONS sales;

Check both sides

Directory exists and contains data

Catalog entry has correct dt

Registered location matches files

46 / 61

Partition pruning

Partition pruning excludes table partitions that cannot match a predicate on the partition key.

A query for October 1 can skip October 2 and 3; predicates on amount still need data-level evaluation.

sales tableorder_id · amountPartition key: dtdt=2026-10-01Read this datedt=2026-10-02Skip this datedt=2026-10-03Skip this date
47 / 61

Plan evidence

EXPLAIN shows the planned operators and selected input structure. Runtime evidence is needed to confirm actual work and cost.

Check that the date predicate selects the intended partition, then inspect measured input and execution behaviour.

EXPLAIN
SELECT SUM(amount)
FROM sales
WHERE dt = '2026-10-01';

Inspect

Partition predicate

Selected paths or partitions

Scan and aggregation operators

48 / 61

Layout trade-offs

Partition keys should support frequent selective filters. Very many tiny partitions can add metadata, listing and small-file overhead.

Parquet column pruning and row-group statistics complement directory pruning; none changes the required query result.

Layout choicePotential benefitTrade-off
Date partitionSkip unrelated datesToo fine a layout makes many files
Parquet columnsRead needed columnsDepends on reader and layout
Row-group statisticsSkip impossible matchesSome groups still need reading
Compacted filesReduce small-file overheadRequires controlled rewrite work
49 / 61

Shared catalog

A shared catalog lets compatible engines resolve the same table definitions and storage locations.

Shared metadata does not guarantee identical SQL semantics, permissions, format support or execution plans.

Shared catalogDefinitions + locationsHiveOwn execution pathSpark SQLOwn execution pathCompatible SQL toolOwn execution pathShared metadata + shared storage · verify reader and semantic compatibility
50 / 61

Data lake

A data lake stores datasets in shared storage, often in several formats. Catalog metadata makes selected datasets discoverable and interpretable.

External tables provide structure over files; data quality, access controls and lifecycle policies remain separate responsibilities.

StorageRaw and curated datasetsIndependent durable locationCatalogNames, schemas and locationsDiscoverable definitionsUse and governanceCompatible query enginesQuality, access and retention
51 / 61

Lab 10

Lab 10 stages MovieLens ratings, registers an external table, and runs counts, averages and grouped filters.

Use the explicit task goal: at least 100 ratings and average above 4.0, despite the “few reviews” heading.

Lab evidenceInterpretation
COUNT(*) = 100000Expected for one intact MovieLens 100K file
Top 10 most ratedRank by count, include average if requested
At least 100 and avg > 4HAVING conditions on groups
Counts by ratingGROUP BY rating; order by rating
Reflection and cleanupExplain path from SQL to files; terminate cluster
52 / 61

Preserve the result

Cluster HDFS is tied to the cluster lifecycle. An external table does not make those files survive cluster termination.

Save required query evidence and data to the approved independent location, verify them, then terminate the cluster.

Complete queriesCheck rows and aggregatesCapture required evidencePreserveIndependent storageVerify saved copyTerminateEnd cluster in consoleConfirm final state
53 / 61

Files, metadata and results

A reliable Hive result requires correct files, matching metadata and a query that expresses the intended population and aggregation.

Trace each result back through the query, table definition and stored records.

FilesCorrect datasetNo duplicate uploadMetadataSchema + formatLocation +partitionsQueryRows + groups +joinsRequired rankingEvidenceChecked resultSaved independently
54 / 61

Component roles

The catalog supplies definitions, the driver coordinates the query, the engine executes work, and storage contains the data.

A table name is metadata, a query plan is computation, and the record bytes remain in storage.

ComponentRoleExample
MetastoreDescribe tablesratings schema and location
DriverResolve and coordinate queryPlan the grouped count
Execution engineRun distributed workScan and combine movie counts
StorageHold record bytesHDFS ratings.tsv
55 / 61

Schema and lifecycle

Schema-on-read interprets existing records. Managed and external table types govern lifecycle defaults, with release and property exceptions.

Validate format and values, and inspect deletion settings before changing a valuable table.

InterpretationColumn order + typesDelimiter or SerDeLocation + compatible filesLifecycleManaged vs external defaultsRelease + purge propertiesIndependent storage protection
56 / 61

Query meanings

WHERE selects rows; GROUP BY and aggregates form group results; HAVING selects groups; ORDER BY defines ranking.

The six ratings yield counts 2, 3 and 1, averages 4, 4 and 5, and a most-rated winner of movie 20.

Business quantitySQL ideaExample answer
Number of rating eventsCOUNT(*)6
Number of movie groupsGROUP BY movie_id3
Most-rated movieOrder group counts descending20, with 3 ratings
Highest mean scoreOrder group averages descending30, with average 5
57 / 61

Join consequences

An inner join can remove unmatched ratings. A left join preserves them with null right fields, but duplicate keys can multiply them.

A right-side WHERE predicate can remove unmatched rows after a left join; an ON predicate changes which rows match.

Case with original six ratingsOutput effect
Unique lookup for 10 and 20; inner join5 rows; movie 30 removed
Same lookup; left join6 rows; movie 30 title is null
Duplicate movie 10 lookup; left join8 rows; movie 10 events multiply
WHERE title = Alpha after left joinOnly qualifying matched rows survive
58 / 61

Selective input

A partition entry links a key value to a location. A matching predicate can exclude other locations before their rows are read.

The catalog and directory layout must agree; plan inspection and runtime evidence support performance claims.

Registered datedt = Oct 01Correct directoryPredicateQuery requests Oct 01Other dates excludedRead + aggregateProcess needed valuesValidate the result
59 / 61

Final SQL case

Use the six ratings and the unique lookup for movies 10 and 20. Return every movie’s count and average, including missing titles.

Choose the join, grouping keys and order for the two most-rated movies; then state what must be preserved before cluster shutdown.

Required populationAll six rating eventsMissing movie titles allowedRequired resultOne row per movieCount + averageTop two by rating count
60 / 61

Checked analytical result

A left join with the unique lookup retains all six ratings. Grouping by movie ID and title produces three groups; ranking counts returns movies 20 and 10.

Verify row multiplicity and the saved result before ending the cluster.

SELECT r.movie_id, m.title, COUNT(*) AS n,
       AVG(r.rating) AS avg_r
FROM ratings r LEFT JOIN movies m
  ON r.movie_id = m.movie_id
GROUP BY r.movie_id, m.title
ORDER BY n DESC, r.movie_id
LIMIT 2;

Top two

20 · Beta · 3 · 4.0

10 · Alpha · 2 · 4.0

Preserve query + checked output