Introduction
The retailer receives a new file every day. Earlier reports filtered records by a date stored inside each row. A partitioned table also records a date-based organization in its metadata, so a query can select the registered data locations associated with the requested date.
Two things must agree: the files in HDFS and the partition records in Hive's Metastore, the catalog that stores table and partition definitions. Creating a directory does not, by itself, register a Hive partition. You will see this distinction directly by adding a new day's files before registering them.
This lab prepares two days of files and a local third-day delivery independently. You will create the partitioned external table, register locations, read the partition column, and inspect a filtered query plan. Hive is ready; no installation or previous VM state is needed. Familiarity with external tables and simple date filters is enough. Use the Terminal.
Separate File Columns from the Partition Column
In this step, you will define a date-partitioned table without repeating the date inside each record. Inspect a prepared file:
cd /home/labex/project
cat /home/labex/project/data/orders-2026-10-14.csv
There are six file columns. Unlike the earlier seven-column CSV, this file has no order_date field: one file contains only one date's orders. Check the already prepared daily HDFS directories:
hdfs dfs -ls /user/labex/hive-partitions/orders
A directory name such as order_date=2026-10-14 makes the organization readable to people. Hive still needs a registered partition value and location to use it.
The following table declares its six file fields normally and declares order_date DATE separately in PARTITIONED BY. Hive will supply that seventh queryable column from partition metadata. Do not add it to both declarations or insert a seventh field into these files.
Save and execute the table definition. The here-document writes the SQL text literally between the SQL markers. Beeline's -f executes the saved script; the connection URL and username refer to the prepared local Hive service:
cat > /home/labex/project/queries/create-partitioned-orders.sql <<'SQL'
CREATE EXTERNAL TABLE retail_partitions.orders (
order_id STRING, customer_id STRING, product_id STRING,
quantity INT, unit_price DECIMAL(10,2), order_status STRING
)
PARTITIONED BY (order_date DATE)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 'hdfs://localhost:9000/user/labex/hive-partitions/orders'
TBLPROPERTIES ('skip.header.line.count'='1', 'external.table.purge'='false');
SQL
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/create-partitioned-orders.sql
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "DESCRIBE retail_partitions.orders;"
DESCRIBE includes the ordinary fields and the partition information. Now count rows before any partitions are registered:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "SELECT COUNT(*) FROM retail_partitions.orders;" > /home/labex/project/output/before-registration.tsv
cat /home/labex/project/output/before-registration.tsv
Expect 0. The table definition exists, and files exist under its base directory, but this new table has no partition records yet. This is a metadata boundary, not evidence that the CSV was deleted.
Register the Two Existing Daily Locations
In this step, you will connect each date value to its actual directory. ALTER TABLE ... ADD PARTITION adds partition records to the Metastore. Each PARTITION (...) supplies a date value, and its LOCATION supplies the corresponding files. These are static partitions: you name each value explicitly.
Save both registrations in one script:
cat > /home/labex/project/queries/register-first-dates.sql <<'SQL'
ALTER TABLE retail_partitions.orders ADD
PARTITION (order_date='2026-10-14') LOCATION 'hdfs://localhost:9000/user/labex/hive-partitions/orders/order_date=2026-10-14'
PARTITION (order_date='2026-10-15') LOCATION 'hdfs://localhost:9000/user/labex/hive-partitions/orders/order_date=2026-10-15';
SQL
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/register-first-dates.sql
List the registered values and read the date column:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "SHOW PARTITIONS retail_partitions.orders;" > /home/labex/project/output/registered-dates.tsv
cat /home/labex/project/output/registered-dates.tsv
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "SELECT order_id, order_date FROM retail_partitions.orders ORDER BY order_id;" > /home/labex/project/output/registered-orders.tsv
cat /home/labex/project/output/registered-orders.tsv
SHOW PARTITIONS should include October 14 and 15. The query returns o1–o3 with October 14 and o4–o6 with October 15. The date came from each registered partition, not from a hidden seventh CSV field.
The registration points at the existing data; it does not copy or rewrite the source files. The table now exposes all six orders, including pending and refunded records. A paid-sales report still needs its status filter.
Add Files First, Then Register a New Date
In this step, you will prove that a new directory and a new partition record are separate actions. Inspect the prepared local October 16 delivery:
cat /home/labex/project/data/orders-2026-10-16.csv
Create its HDFS directory and upload the file. These familiar commands affect storage only:
hdfs dfs -mkdir -p /user/labex/hive-partitions/orders/order_date=2026-10-16
hdfs dfs -put /home/labex/project/data/orders-2026-10-16.csv /user/labex/hive-partitions/orders/order_date=2026-10-16/orders.csv
Before registering it, ask Hive for that date's row count:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "SELECT COUNT(*) FROM retail_partitions.orders WHERE order_date = DATE '2026-10-16';" > /home/labex/project/output/new-date-before.tsv
cat /home/labex/project/output/new-date-before.tsv
Expect 0 despite the uploaded file. Add the missing metadata and repeat the date-specific query:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "ALTER TABLE retail_partitions.orders ADD PARTITION (order_date='2026-10-16') LOCATION 'hdfs://localhost:9000/user/labex/hive-partitions/orders/order_date=2026-10-16';"
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "SELECT order_id, customer_id, order_status, order_date FROM retail_partitions.orders WHERE order_date = DATE '2026-10-16' ORDER BY order_id;" > /home/labex/project/output/new-date-orders.tsv
cat /home/labex/project/output/new-date-orders.tsv
Now o7/c1/paid and o8/c2/pending appear with October 16. Hive can associate this partition value with the uploaded directory. This deliberate before-and-after check explains why a newly delivered file may be present in HDFS while missing from a report.
Read a Partition-Filtered Query and Its Plan
In this step, you will select one registered date and inspect how Hive plans that request. Save the query using the partition column in WHERE:
cat > /home/labex/project/queries/query-sales-partition.sql <<'SQL'
SELECT order_id, quantity, order_date
FROM retail_partitions.orders
WHERE order_date = DATE '2026-10-15'
ORDER BY order_id;
SQL
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/query-sales-partition.sql > /home/labex/project/output/selected-partition.tsv
cat /home/labex/project/output/selected-partition.tsv
Expect o4/2, o5/1, and o6/2, all with October 15. This is a date selection, not yet a paid-sales filter: refunded o5 correctly remains in this particular query.
EXPLAIN describes a query without returning its data rows. Its DEPENDENCY option focuses on the tables and partitions the query will read. This compact view lets you inspect input selection without first learning execution-stage internals. Prefix the same query with EXPLAIN DEPENDENCY and save the result:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "EXPLAIN DEPENDENCY SELECT order_id, quantity, order_date
FROM retail_partitions.orders
WHERE order_date = DATE '2026-10-15'
ORDER BY order_id;" > /home/labex/project/output/partition-plan.txt
cat /home/labex/project/output/partition-plan.txt
The result is JSON, a text format of named fields and lists. Find input_tables, which names retail_partitions@orders, and input_partitions, whose single partitionName ends in order_date=2026-10-15. The other two dates are absent from the input list. The date predicate lets Hive select the relevant registered partition before reading its records; this is called partition pruning. The tiny fixture is for understanding that selection, so query timing is not a meaningful performance comparison.
Finally, list the registered partitions again:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "SHOW PARTITIONS retail_partitions.orders;"
All three dates should still be registered. Selecting one date changes the query's input selection; it does not drop other partition records or delete their files. You now have both storage-level and catalog-level evidence for explaining which daily data a query can see.
Summary
You separated file fields from the partition column, registered daily HDFS locations in Hive, and observed that a new directory remained invisible until its partition was added. You then selected one date and inspected its query plan while preserving all dates. Next, you will save query results in a reusable columnar ORC table.
