Introduction
In the first Hive lab, a prepared table let you query order rows immediately. Now the retailer has supplied CSV files, but a file sitting in storage is not yet a table Hive knows how to query. You need to describe its fields and identify the directory holding its data.
An external table is a table definition referring to data managed separately from that definition. Its schema specifies column names and types, and its LOCATION points to stored files. Creating the definition does not upload the files or rewrite their contents. Hive keeps table metadata in its Metastore, while the records remain in storage.
You will upload two simple CSV sources, map the first with an explicit schema, switch the mapping to the second, and drop only a disposable external-table definition. The final experiment uses explicit data-retention properties on this course's Hive version; it is not a promise that dropping every possible Hive table preserves files. Services and local inputs are prepared independently, and no Hadoop course or programming language is required. Use the Terminal.
Inspect and Upload the Source Files
In this step, you will inspect the CSV format and copy the two supplied files into separate HDFS directories.
Start in your local project:
cd /home/labex/project
A CSV file separates fields with commas. These teaching files contain simple values without embedded commas, quoted multiline text, or escaped separators. The simple table format used here is chosen for those inputs, not as a universal parser for every CSV dialect.
Local head -n 4 displays the first four lines:
head -n 4 /home/labex/project/data/orders-a.csv
You should see a header followed by three order records:
order_id,customer_id,product_id,quantity,unit_price,order_status,order_date
o1,c1,p1,2,10.00,paid,2026-10-14
o2,c2,p2,1,25.00,paid,2026-10-14
o3,c1,p1,1,10.00,pending,2026-10-14
Count newline-terminated lines with wc -l:
wc -l /home/labex/project/data/orders-a.csv /home/labex/project/data/orders-b.csv
Source A has seven lines but only six business records; source B has three lines but only two records. The header must not become an order row.
HDFS is a separate filesystem used by the prepared Hive service. The hdfs dfs client manages its files. -mkdir -p creates the named HDFS directories and missing parents:
hdfs dfs -mkdir -p /user/labex/hive-mapping/source-a /user/labex/hive-mapping/source-b
The -put operation copies a local source to an HDFS destination, leaving the original intact:
hdfs dfs -put /home/labex/project/data/orders-a.csv /user/labex/hive-mapping/source-a/orders.csv
Upload the second source to its own directory:
hdfs dfs -put /home/labex/project/data/orders-b.csv /user/labex/hive-mapping/source-b/orders.csv
A successful upload normally prints nothing. An already-existing destination should be inspected before any overwrite decision; do not delete raw data just to repeat a command. Read the first HDFS file with -cat:
hdfs dfs -cat /user/labex/hive-mapping/source-a/orders.csv
Its header and six records are still present. Uploading preserves bytes; a table definition will tell Hive how to interpret them.
Define the File-to-Table Mapping
In this step, you will create an external table with explicit fields, types, a directory location, and header handling.
The source's field order must match the table's column order. Use STRING for identifiers and status, INT for a whole-number quantity, DECIMAL(10,2) for a fixed-point price, and DATE for the ISO-formatted date. Decimal prices avoid binary floating-point approximations in later money calculations.
CREATE DATABASE IF NOT EXISTS creates the namespace if needed. CREATE EXTERNAL TABLE then registers a table definition. The format clauses specify comma-separated text; LOCATION names the directory, so keep unrelated files and backups outside it. The two properties skip one header line per file and explicitly disable external-table data purging on DROP in this environment.
Save the complete SQL file. As in the first lab, the shell here-document writes literal lines up to the closing SQL delimiter:
cat > /home/labex/project/queries/create-orders.sql <<'SQL'
CREATE DATABASE IF NOT EXISTS retail_mapping;
USE retail_mapping;
CREATE EXTERNAL TABLE raw_orders (
order_id STRING,
customer_id STRING,
product_id STRING,
quantity INT,
unit_price DECIMAL(10,2),
order_status STRING,
order_date DATE
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 'hdfs://localhost:9000/user/labex/hive-mapping/source-a'
TBLPROPERTIES ('skip.header.line.count'='1', 'external.table.purge'='false');
SQL
Run it with Beeline. The connection and -f file option are the same as in the first lab:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true -f /home/labex/project/queries/create-orders.sql
These are DDL statements (Data Definition Language): SQL statements that define objects such as tables. Successful DDL statements create metadata, rather than returning order rows. If the table already exists from a completed run, inspect it instead of dropping it to silence the message.
DESCRIBE FORMATTED includes the table's type and location as well as its columns:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex -e 'DESCRIBE FORMATTED retail_mapping.raw_orders;'
Find EXTERNAL_TABLE and the location ending in /hive-mapping/source-a. These are metadata observations, not evidence that every record parsed correctly. Use a query too. COUNT(*) counts the table's rows, so it provides a quick mapping check. AS order_count gives that calculated result a column name, called an alias; it does not rename a source field or change the stored records:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex -e 'SELECT COUNT(*) AS order_count FROM retail_mapping.raw_orders; SELECT order_id, quantity, unit_price, order_date FROM retail_mapping.raw_orders ORDER BY order_id LIMIT 2;'
The count should be 6, not the file's seven text lines. The first two records should contain IDs o1, o2, quantities 2, 1, prices numerically equal to 10.00, 25.00, and date 2026-10-14. You have verified both header handling and meaningful typed columns.
Point the Same Table at a New Source
In this step, you will change the table's location and predict how that changes its rows without moving either source file.
Source B has the same fields but contains orders o7 and o8. Read the stored file before changing metadata:
hdfs dfs -cat /user/labex/hive-mapping/source-b/orders.csv
You should see its header and two records. Predict the table's row count after pointing it at this directory: two, rather than six. ALTER TABLE ... SET LOCATION changes the directory reference in the table definition. It does not copy the old data into the new directory or merge the two deliveries.
Save that metadata change:
cat > /home/labex/project/queries/map-second-source.sql <<'SQL'
ALTER TABLE retail_mapping.raw_orders SET LOCATION 'hdfs://localhost:9000/user/labex/hive-mapping/source-b';
SQL
Execute it:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true -f /home/labex/project/queries/map-second-source.sql
Inspect both metadata and data again:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex -e 'DESCRIBE FORMATTED retail_mapping.raw_orders; SELECT COUNT(*) AS order_count FROM retail_mapping.raw_orders; SELECT order_id, quantity, unit_price FROM retail_mapping.raw_orders ORDER BY order_id;'
Find the new source-b location, count 2, and the rows for o7 and o8. Their quantities are 1 and 2, and their prices are numerically 50.00 and 15.00. The column definitions have not changed; only the files read through them have changed.
Read the old file to check that changing the mapping did not remove it:
hdfs dfs -cat /user/labex/hive-mapping/source-a/orders.csv
Its six records and header should still be present. File existence and table visibility are different facts: source A still exists even though this table now reads source B.
Drop Only a Disposable Table Definition
In this step, you will create and remove an external-table definition over the retained first source while keeping the data and the main table.
Deleting a table definition and deleting its data are different operations. For this experiment, use a separate name, disposable_orders, with the explicit external-data-retention properties. Keep raw_orders, which now maps source B, available throughout.
Create the disposable definition over source A using the same known schema:
cat > /home/labex/project/queries/create-disposable.sql <<'SQL'
CREATE EXTERNAL TABLE retail_mapping.disposable_orders (
order_id STRING,
customer_id STRING,
product_id STRING,
quantity INT,
unit_price DECIMAL(10,2),
order_status STRING,
order_date DATE
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 'hdfs://localhost:9000/user/labex/hive-mapping/source-a'
TBLPROPERTIES ('skip.header.line.count'='1', 'external.table.purge'='false');
SQL
Run the definition:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true -f /home/labex/project/queries/create-disposable.sql
Check that both table names exist:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex -e 'SHOW TABLES IN retail_mapping;'
You should see disposable_orders and raw_orders. DROP TABLE removes a table definition. Here it targets only the disposable external table, whose purge property is explicitly false:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex -e 'DROP TABLE retail_mapping.disposable_orders; SHOW TABLES IN retail_mapping;'
Only raw_orders should remain. Now inspect the source file independently:
hdfs dfs -cat /user/labex/hive-mapping/source-a/orders.csv
The header and six records should remain. That observed behavior applies to this external-table configuration. A managed table or an external table with different purge properties can remove data when dropped; do not generalize this experiment into a universal deletion rule.
Confirm the main table still reads source B:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex -e 'SELECT order_id FROM retail_mapping.raw_orders ORDER BY order_id;'
You should still see o7 and o8. You have separated three operations that beginners often confuse: uploading bytes, registering or changing metadata, and removing a definition. Next, you will use those correctly mapped rows to filter conditions and handle missing values.
Summary
You uploaded simple CSV sources, declared their field order and types, skipped headers, and registered an external table at an explicit HDFS directory. You checked both metadata and actual records, then changed the table's location without moving either source file.
Finally, you removed a disposable external-table definition under explicit retention settings and confirmed that the files and main table remained available. Next, you will build on a trustworthy mapping to choose rows and handle missing values.
