Introduction
A community venue keeps its confirmed visits in a Hive archive. An approved correction replaces the entire confirmed set for a date. It is not a patch to append to yesterday's rows. One approved date now has two confirmed visits; another has none. The other dates, including a later addition, must survive unchanged.
You will compare append and replacement on disposable tables, then save and run a narrowly scoped SQL file against the real ORC archive. You will also repeat that file and read the result again. The environment already runs Hive and HDFS and contains independent raw records, candidate corrections, and the archive.
You should already know external Hive tables, SELECT with WHERE, partition columns, ORC, and basic HDFS paths. This lab introduces static destination partitions and full-date replacement. All work uses the Terminal tab. The 65-minute duration is an author estimate including reading and ordinary mistakes.
Compare Append and Replacement Safely
In this step, you will read a normal archive day, then compare three write scopes on disposable practice tables. Keep the real archive unchanged during this comparison.
Open Terminal. Use the prepared workspace throughout this lab:
cd /home/labex/project
The local venue directory contains reference CSV files and an approval list. cat displays a file without editing it:
cat venue/approved-days.txt
2026-10-02
2026-10-03
These are the only real archive dates you may replace. First read an ordinary day. Beeline sends SQL to HiveServer2. In the command below, -u supplies the JDBC connection address, -n labex supplies the client username, and -e supplies SQL text. Double quotes keep that text together as one shell argument; single quotes inside it delimit SQL values. DATE '...' makes the date type explicit.
beeline -u jdbc:hive2://localhost:10000/default -n labex -e "SELECT * FROM venue_archive.archive WHERE visit_date=DATE '2026-10-04' ORDER BY visit_id;"
Read the data rows beneath any startup notices. The date has v4 / Music / 4 and v5 / LateWalk / 2. The later visit v5 is valid and must remain. The image may print SLF4J notices about multiple logging bindings; these are separate from SQL errors. Wait for the command to return to the prompt before continuing.
Now inspect the complete candidate source, including cancelled records:
beeline -u jdbc:hive2://localhost:10000/default -n labex -e "SELECT * FROM venue_archive.candidate ORDER BY visit_date,visit_id;"
October 2 has c21 / PrintWorkshop / 5 and c22 / Bookbinding / 2, both confirmed. October 3 has a cancelled row. The archive stores only confirmed rows, so October 3's replacement is legitimately empty. A missing date in a filtered result does not mean “leave that old archive date alone.” Approval and the confirmed-row filter answer different questions.
Check where the three real tables read and write. DESCRIBE FORMATTED adds storage metadata to the column definitions: find Table Type, Location, and InputFormat in each result. The long output also contains engine details; focus on these fields before any write.
beeline -u jdbc:hive2://localhost:10000/default -n labex -e "DESCRIBE FORMATTED venue_archive.raw; DESCRIBE FORMATTED venue_archive.candidate; DESCRIBE FORMATTED venue_archive.archive;"
All three are EXTERNAL_TABLE objects. The raw location is hdfs://localhost:9000/user/labex/venue-archive/raw; the candidate location ends in /user/labex/venue-archive/candidate. Both use text input. The archive location is hdfs://localhost:9000/warehouse/external/venue_archive.db/archive and its input format ends in OrcInputFormat. Local venue/*.csv files are reference copies, not the archive. Preserve the supplied mappings and source files.
Before changing the real archive, use two disposable tables: practice_days has date partitions; practice_whole stores the date as an ordinary column. They begin with the raw confirmed visits. The next commands deliberately demonstrate incorrect outcomes on these practice tables only.
INSERT INTO appends rows. PARTITION(visit_date='2026-10-02') is a static destination partition: the literal date chooses where every selected row goes. The SELECT must therefore return the three nonpartition columns only. Its separate WHERE condition chooses the source rows. Naming the destination does not filter the source automatically.
beeline -u jdbc:hive2://localhost:10000/default -n labex -e "INSERT INTO TABLE venue_archive.practice_days PARTITION(visit_date='2026-10-02') SELECT visit_id,activity,visitors FROM venue_archive.candidate WHERE visit_date=DATE '2026-10-02' AND record_status='confirmed'; SELECT * FROM venue_archive.practice_days WHERE visit_date=DATE '2026-10-02' ORDER BY visit_id;"
The result has c21, c22, and old v2. The SQL succeeded, but the replacement contract failed. Appending the same correction again would add more duplicates.
INSERT OVERWRITE replaces the destination's existing rows. On the nonpartitioned practice_whole table, the destination is the whole table. Watch the other dates disappear:
beeline -u jdbc:hive2://localhost:10000/default -n labex -e "INSERT OVERWRITE TABLE venue_archive.practice_whole SELECT visit_id,activity,visitors,visit_date FROM venue_archive.candidate WHERE visit_date=DATE '2026-10-02' AND record_status='confirmed'; SELECT * FROM venue_archive.practice_whole ORDER BY visit_date,visit_id;"
Only the two October 2 rows remain. A successful whole-table write can silently lose unrelated dates. Restore this disposable table from its complete raw source:
beeline -u jdbc:hive2://localhost:10000/default -n labex -e "INSERT OVERWRITE TABLE venue_archive.practice_whole SELECT visit_id,activity,visitors,visit_date FROM venue_archive.raw WHERE record_status='confirmed'; SELECT * FROM venue_archive.practice_whole ORDER BY visit_date,visit_id;"
All five original rows return. Finally combine OVERWRITE with a static date partition on practice_days:
beeline -u jdbc:hive2://localhost:10000/default -n labex -e "INSERT OVERWRITE TABLE venue_archive.practice_days PARTITION(visit_date='2026-10-02') SELECT visit_id,activity,visitors FROM venue_archive.candidate WHERE visit_date=DATE '2026-10-02' AND record_status='confirmed'; SELECT * FROM venue_archive.practice_days ORDER BY visit_date,visit_id;"
There are six rows: original October 1, corrected October 2's two rows, original October 3, and both October 4 visits. Old v2 is gone. This is the write scope needed for the real archive.
If Beeline reports an invalid table name, the SQL did not find the intended object. Check spelling with the already familiar read-only command below, then rerun your corrected command. A file-path error can be investigated with pwd and ls venue; return with the cd command above. Do not recreate tables or rerun environment setup to fix a typo.
beeline -u jdbc:hive2://localhost:10000/default -n labex -e "SHOW TABLES IN venue_archive;"
Before checking this step, confirm the practice partition contains exactly c21 and c22 on October 2, the whole-table practice has its five original rows again, and the real archive still has its original data.
Replace the Approved Nonempty Day
In this step, you will save and execute the real October 2 replacement. Continue in Terminal, in /home/labex/project.
The real table venue_archive.archive is an external, nontransactional ORC table with visit_date DATE partitions. Its raw and candidate sources are independent external comma-delimited text tables. You may replace only approved archive dates, not edit source files, change table definitions or locations, remap partitions, create extra dates, or rebuild the whole archive. October 1 and October 4 must retain their original data and existing HDFS files. Rewriting identical rows is still outside the approved scope.
Inspect the real partition list and column layout before writing. SHOW PARTITIONS lists registered date partitions; DESCRIBE shows normal columns followed by partition information:
beeline -u jdbc:hive2://localhost:10000/default -n labex -e "SHOW PARTITIONS venue_archive.archive; DESCRIBE venue_archive.archive;"
The four dates are October 1–4. The normal columns are visit_id STRING, activity STRING, and visitors INT; visit_date DATE is the partition column. Keep this mapping and ORC format intact.
Save your SQL as an editable file so that you can inspect, correct, and repeat the same operation. nano is a text editor inside Terminal. The following command opens replace-approved-days.sql in the current workspace; because this is a new file, its editing area is initially empty:
nano replace-approved-days.sql
Type the following five SQL lines into nano, rather than at the shell prompt. Single quotes delimit SQL values, and the semicolon ends the statement. Stop after the final semicolon without adding an extra empty line; nano supplies the terminating newline when it saves:
INSERT OVERWRITE TABLE venue_archive.archive
PARTITION(visit_date='2026-10-02')
SELECT visit_id,activity,visitors
FROM venue_archive.candidate
WHERE visit_date=DATE '2026-10-02' AND record_status='confirmed';
Use the arrow keys to move to a character that needs correction; Home and End move to the beginning and end of the current line. Backspace deletes the character to the left of the cursor; Delete deletes the character at the cursor. Type the corrected text in its place. nano's footer uses ^ to mean Ctrl. Press Ctrl+O to save: at File Name to Write, keep the displayed filename and press Enter. Then press Ctrl+X to exit. Wait until the shell prompt returns; SQL belongs in the editor, while the following commands belong at that prompt.
Read what you saved before executing it:
cat replace-approved-days.sql
Check both dates in the statement: the static destination and source filter are October 2. The correction is the complete confirmed set, including both c21 and c22. There is no need to match old visit IDs or update individual rows.
Beeline's -f reads SQL from a file. Execute your saved file:
beeline -u jdbc:hive2://localhost:10000/default -n labex -f replace-approved-days.sql
Then read all four dates as a consumer would:
beeline -u jdbc:hive2://localhost:10000/default -n labex -e "SELECT visit_date,visit_id,activity,visitors FROM venue_archive.archive ORDER BY visit_date,visit_id;"
Expect six rows. October 2 contains c21 / PrintWorkshop / 5 and c22 / Bookbinding / 2, with no v2. October 3 still has old v3 for now. October 1's v1 and October 4's v4 and v5 remain unchanged.
The Beeline connection identifies you as labex. HiveServer2 performs the storage writes as its service user hive, so new ORC files can be owned by hive:bigdata. You do not need to change their ownership or permissions. HDFS's NameNode runs under its own hdfs service identity.
If a SQL statement has a syntax error, inspect the saved file with cat, then reopen it with nano replace-approved-days.sql. Use the arrow keys to locate the statement named in the Beeline error. Correct the table name, dates, quotes, or semicolon as needed, keeping the other SQL lines. Save with Ctrl+O, Enter, then exit with Ctrl+X. Read the file again before rerunning -f. If you accidentally appended to this approved date, the same correct partition overwrite removes the obsolete and duplicate rows. This recovery applies to the approved date; it does not authorize rebuilding a protected date.
Apply an Empty Replacement and Repeat the File
In this step, you will clear the approved October 3 partition, then run your complete correction file twice and verify that the archive does not accumulate rows. Continue in Terminal, in /home/labex/project.
A cancellation is still a correction. The approval list includes October 3, but its candidate has no confirmed visits. Run the source selection first:
beeline -u jdbc:hive2://localhost:10000/default -n labex -e "SELECT visit_id,activity,visitors FROM venue_archive.candidate WHERE visit_date=DATE '2026-10-03' AND record_status='confirmed';"
There are no data rows. The correct final archive must also have no rows for that date. Skipping the write would leave old v3 in place.
Reopen the same file. Keep the existing October 2 statement; you will append one statement, not copy the first one again:
nano replace-approved-days.sql
Use the Down Arrow to reach the empty line after the first statement's semicolon, and End if you need to reach the end of that line. At that empty line, press Enter once to leave one blank line between statements. Then type only the five new lines below. Stop after its final semicolon without adding an extra empty line. If you are resuming an interrupted edit, first check whether this October 3 statement is already there; correct it instead of adding a duplicate.
INSERT OVERWRITE TABLE venue_archive.archive
PARTITION(visit_date='2026-10-03')
SELECT visit_id,activity,visitors
FROM venue_archive.candidate
WHERE visit_date=DATE '2026-10-03' AND record_status='confirmed';
Save with Ctrl+O, Enter, then exit with Ctrl+X. Read the complete saved file at the shell prompt:
cat replace-approved-days.sql
Check that it contains exactly two statements: the original October 2 statement followed by the new October 3 statement. In each one, both the static destination and source filter must name that statement's approved date. If anything is wrong, reopen the same file in nano, correct only the affected lines, and save and inspect it again.
Run this complete saved file for the first time:
beeline -u jdbc:hive2://localhost:10000/default -n labex -f replace-approved-days.sql
A static overwrite with an empty selection removes the destination partition's old rows. It leaves that date registered. The storage engine decides whether an empty partition has any physical files; do not create placeholder CSV or ORC files yourself.
Read the registered dates and complete result:
beeline -u jdbc:hive2://localhost:10000/default -n labex -e "SHOW PARTITIONS venue_archive.archive; SELECT visit_date,visit_id,activity,visitors FROM venue_archive.archive ORDER BY visit_date,visit_id; SELECT COUNT(*) FROM venue_archive.archive WHERE visit_date=DATE '2026-10-03';"
All four partitions are still listed. The full result has five rows: v1, c21, c22, v4, and v5. The October 3 count is 0. An approval list containing two dates does not imply two nonempty partitions.
Now execute the same saved file again, without changing it:
beeline -u jdbc:hive2://localhost:10000/default -n labex -f replace-approved-days.sql
Repeat the consumer read:
beeline -u jdbc:hive2://localhost:10000/default -n labex -e "SELECT visit_date,visit_id,activity,visitors FROM venue_archive.archive ORDER BY visit_date,visit_id; SELECT COUNT(*) FROM venue_archive.archive;"
The same five rows remain and the total is 5. This is a repeatable replacement for these supplied sources: it does not append duplicates. The approved partition files may be recreated by a repeat; the non-target dates' original files must remain untouched.
You can inspect the original source using familiar HDFS commands. Here, -cat reads the exact source file; it does not read the derived ORC archive:
hdfs dfs -cat /user/labex/venue-archive/raw/raw.csv
It still contains original v2 and v3. Changing a derived archive does not rewrite its raw history. The candidate and approval list must also remain as supplied. Keep your SQL file in the workspace. The checks observe current Hive data, definitions, scope, and protected files; they do not execute arbitrary saved SQL. Your actual repeat and consumer reads are the self-check of the saved operation.
Summary
You compared append, whole-table replacement, and static partition replacement. You then replaced a complete approved day, applied a legitimate empty replacement, and repeated the same saved SQL without accumulating rows. The independent raw history and the other archive dates remained intact.
