Introduction
A query displayed in a terminal is useful once. A report that other queries can reuse needs a stored result. You will turn the retailer's daily paid-sales query into a small Hive table and rebuild that table safely when the same report is requested again.
The source is readable CSV text. ORC, Optimized Row Columnar, is a binary columnar storage format: it organizes values by column within its file structure and can support efficient analytical reads. It is not another SQL language or a replacement for the table's schema. The small fixture here demonstrates correct storage and reuse, rather than proving a speed improvement.
This lab independently prepares the six clean orders in an external text table. Hive is ready. You should understand external tables, SELECT, paid-sales aggregation, and date grouping. You will learn the result-writing statements here, using explicitly nontransactional tables supported by this prepared environment. No programming, installation, or previous lab files are required. Use the Terminal.
Define a Typed ORC Report Table
In this step, you will create an empty destination whose schema matches the intended report. Like the CSV source, the destination is an explicitly external table with a known HDFS location. Its format is ORC instead of text. External describes the relationship between metadata and files; it does not mean the table is read-only. Hive can write a SELECT result into it. The result location is separate from the source, so rebuilding the report will not replace the original orders.
Start in the workspace:
cd /home/labex/project
The output has one date, one count, and one monetary total per row. COUNT(*) produces a BIGINT count. DECIMAL(14,2) reserves fourteen total decimal digits, two after the decimal point; it is sufficient for this small report. The explicit schema helps prevent accidentally storing amounts as text.
Save and execute the definition. The here-document writes SQL literally between its markers; Beeline's -f runs that file against the prepared service:
cat > /home/labex/project/queries/create-orc-report.sql <<'SQL'
CREATE EXTERNAL TABLE IF NOT EXISTS retail_orc.daily_sales (
order_date DATE,
paid_orders BIGINT,
revenue DECIMAL(14,2)
)
STORED AS ORC
LOCATION 'hdfs://localhost:9000/warehouse/external/retail_orc.db/daily_sales'
TBLPROPERTIES ('transactional'='false', '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-orc-report.sql
STORED AS ORC selects the format. LOCATION names a new result directory beneath the prepared database's writable warehouse directory. external.table.purge=false retains files if the external definition is dropped; it does not prevent an explicit overwrite from replacing report rows. transactional=false requests a nontransactional table. Hive may omit this false property from displayed metadata; only a true value would mark it transactional. This lab does not teach ACID updates or deletes. Inspect the metadata and the empty row count:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "DESCRIBE FORMATTED retail_orc.daily_sales;" > /home/labex/project/output/orc-definition.txt
cat /home/labex/project/output/orc-definition.txt
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "SELECT COUNT(*) FROM retail_orc.daily_sales;" > /home/labex/project/output/empty-report.tsv
cat /home/labex/project/output/empty-report.tsv
Find the three column types, the external table type, and ORC input/output format names. Expect 0 rows: defining a destination has not run the sales calculation or copied any CSV data. Next, you will populate it explicitly.
Write and Read Back the Daily Report
In this step, you will run the paid-sales SELECT and persist its output. INSERT OVERWRITE TABLE replaces the destination table's current contents with the SELECT result. It does not overwrite the external orders table being read. The selected values are assigned to destination columns in order: date, count, revenue.
Save a complete rebuild script. CREATE TABLE IF NOT EXISTS permits rerunning the definition when the correctly defined table already exists; it does not repair a wrong schema. The following INSERT performs the actual report build:
cat > /home/labex/project/queries/build-sales-report.sql <<'SQL'
CREATE EXTERNAL TABLE IF NOT EXISTS retail_orc.daily_sales (
order_date DATE,
paid_orders BIGINT,
revenue DECIMAL(14,2)
)
STORED AS ORC
LOCATION 'hdfs://localhost:9000/warehouse/external/retail_orc.db/daily_sales'
TBLPROPERTIES ('transactional'='false', 'external.table.purge'='false');
INSERT OVERWRITE TABLE retail_orc.daily_sales
SELECT order_date, COUNT(*) AS paid_orders,
SUM(quantity * unit_price) AS revenue
FROM retail_orc.orders
WHERE order_status = 'paid'
GROUP BY order_date;
SQL
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/build-sales-report.sql
Successful completion means the write finished. Confirm by querying the stored ORC table, rather than merely rerunning the original source SELECT:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "SELECT order_date, paid_orders, revenue FROM retail_orc.daily_sales ORDER BY order_date;" > /home/labex/project/output/stored-report.tsv
cat /home/labex/project/output/stored-report.tsv
Expect October 14 / 2 / 45.00 and October 15 / 2 / 80.00. Monetary display may include trailing zeroes. These are two stored result rows summarizing four paid source orders; they are not six copies of the original CSV records.
ORC files are binary, so using cat to view their bytes would not produce a readable CSV. Hive interprets the table's schema and file format when you SELECT from it. The source files remain ordinary text and retain all six orders, including the two excluded statuses.
Rebuild Without Accumulating Duplicate Days
In this step, you will test repeatability. Predict the row count if you execute the same overwrite script again. The destination should still have two dates, rather than four copied summary rows:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/build-sales-report.sql
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "SELECT order_date, paid_orders, revenue FROM retail_orc.daily_sales ORDER BY order_date;" > /home/labex/project/output/rebuilt-report.tsv
cat /home/labex/project/output/rebuilt-report.tsv
The report should still be October 14 / 2 / 45.00 and October 15 / 2 / 80.00. Check a compact reconciliation directly from the result table:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "SELECT COUNT(*), SUM(paid_orders), SUM(revenue) FROM retail_orc.daily_sales;" > /home/labex/project/output/report-reconciliation.tsv
cat /home/labex/project/output/report-reconciliation.tsv
Expect 2 stored report rows, 4 paid source orders, and 125.00 revenue. The result's row count and its paid_orders sum describe different levels of detail.
Compare the saved before-and-after data locally. Since both SELECT statements use ORDER BY, the data row order is stable. Beeline may include client messages, so the meaningful comparison is the dated result rows and the numeric reconciliation, rather than message timestamps.
This rebuild is idempotent for unchanged input: repeating it leaves the same report contents. Next, a disposable destination will show why the write mode matters.
Compare Append and Overwrite on a Disposable Copy
In this step, you will intentionally duplicate a small derived report in a separate table. Your main daily_sales table stays the correct report. Create the disposable table with the same explicit schema:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "CREATE EXTERNAL TABLE IF NOT EXISTS retail_orc.append_demo (
order_date DATE,
paid_orders BIGINT,
revenue DECIMAL(14,2)
)
STORED AS ORC
LOCATION 'hdfs://localhost:9000/warehouse/external/retail_orc.db/append_demo'
TBLPROPERTIES ('transactional'='false', 'external.table.purge'='false');"
INSERT INTO TABLE appends SELECT results to existing contents. Append the two-row main report twice:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "INSERT INTO TABLE retail_orc.append_demo SELECT order_date, paid_orders, revenue FROM retail_orc.daily_sales;"
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "INSERT INTO TABLE retail_orc.append_demo SELECT order_date, paid_orders, revenue FROM retail_orc.daily_sales;"
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "SELECT COUNT(*), SUM(paid_orders), SUM(revenue) FROM retail_orc.append_demo;" > /home/labex/project/output/append-observation.tsv
cat /home/labex/project/output/append-observation.tsv
Expect 4 rows, 8 summed paid orders, and 250.00 revenue. The extra rows represent duplicate report copies, not new sales. No new orders arrived; appending a complete rebuild gave the wrong business result.
Replace the disposable contents with one copy using the write mode learned earlier:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "INSERT OVERWRITE TABLE retail_orc.append_demo SELECT order_date, paid_orders, revenue FROM retail_orc.daily_sales;"
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "SELECT order_date, paid_orders, revenue FROM retail_orc.append_demo ORDER BY order_date;" > /home/labex/project/output/overwrite-observation.tsv
cat /home/labex/project/output/overwrite-observation.tsv
It returns to the two correct daily rows. For a small practice check, repeat that overwrite once more:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "INSERT OVERWRITE TABLE retail_orc.append_demo SELECT order_date, paid_orders, revenue FROM retail_orc.daily_sales;"
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "SELECT COUNT(*), SUM(revenue) FROM retail_orc.append_demo;"
Expect 2 rows and 125.00 again. Append is useful when genuinely adding new, nonoverlapping data; rebuilding a complete snapshot needs replacement semantics. Your saved main build script uses overwrite, so it can rebuild the full report without silently accumulating duplicate dates.
Summary
You defined a typed nontransactional ORC result table, wrote daily paid-sales totals, read them back, and tested a repeatable rebuild. A disposable append experiment demonstrated how duplicate report copies inflate revenue and how overwrite restores a complete snapshot. Next, you will diagnose common file-to-table mapping mistakes before the final reporting challenge.
