Your First Hive Query

Introduction

Imagine opening the retailer's order records as a spreadsheet. Each row describes one order and each column describes an attribute, such as its date or payment status. You want to answer a simple question: Which paid orders should the team review first? Reading every row by hand becomes inconvenient as files grow.

Apache Hive lets you describe file data as tables and ask questions using SQL, Structured Query Language. A table definition describes the columns and where the underlying files live; the files contain the actual records. Hive uses that definition to interpret data and execute a query. HDFS supplies storage in this prepared environment, while Hive supplies the table-and-query layer. Uploading a file alone does not automatically make it a Hive table.

Hive is useful for analytical queries over stored data, such as batch reports. It is not the same as editing cells in a spreadsheet, and this course does not present it as a replacement for every application's transactional database. Here you will begin with six readable records so you can predict the right answer before working with larger inputs.

You will use Beeline, Hive's terminal client, to connect to a running HiveServer2 service. The service and a sample table are prepared for you. You do not need to install software, initialize metadata, or know Java, Python, Hadoop, or SQL in advance. Basic terminal use and the ability to read a table are enough. For additional terminal or database practice, see the Linux and Database paths.

Your learning journey is: see real rows, discover the table's structure, save a query that selects paid orders, and change one condition to test your understanding.

See Your First Hive Rows

In this step, you will connect to Hive and read a prepared table before learning how to narrow a query.

Use the Terminal tab. Move to the local project directory with cd:

cd /home/labex/project

Beeline is a client: it sends SQL to HiveServer2, which handles the query. In the command below, -u supplies the connection URL, -n supplies the session username, and -e supplies SQL to execute before exiting back to the terminal. The provided URL connects to this machine's Hive service on port 10000, initially in the default database. You do not need an external account or a password for this teaching service.

The SQL SELECT * FROM retail_intro.orders; asks for all columns (*) from the orders table in the retail_intro database. A semicolon ends the statement. Single quotes keep the whole SQL statement together as one shell argument:

beeline -u 'jdbc:hive2://localhost:10000/default' -n labex -e 'SELECT * FROM retail_intro.orders;'

Look for six order rows and column names such as order_id, quantity, and order_status. Connection messages and table borders may appear around the results. Find order o1: it has quantity 2, unit price 10.00, and status paid. Order o3 is pending; o5 is refunded. Each row is one simplified order containing one product type.

A query retrieves data; this SELECT does not change payment status or update the source file. Without an ordering clause, do not assume SQL promises a particular row order.

Capture the same query output in a local transcript. The shell's > operator creates the file or replaces its previous contents:

beeline -u 'jdbc:hive2://localhost:10000/default' -n labex -e 'SELECT * FROM retail_intro.orders;' > /home/labex/project/output/first-look.txt

Read it locally:

cat /home/labex/project/output/first-look.txt

The order rows should still be present. The terminal ran the Beeline client, Hive read the table definition and files, and the shell saved the displayed result locally. Next you will discover the objects behind that query.

Discover the Database, Table, and Columns

In this step, you will explore the database and table definition instead of guessing column names.

A database is a namespace grouping tables. SHOW DATABASES lists available databases; it does not read all their data files:

beeline -u 'jdbc:hive2://localhost:10000/default' -n labex -e 'SHOW DATABASES;'

Find default and retail_intro. Use USE to select a database, followed by SHOW TABLES in the same invocation:

beeline -u 'jdbc:hive2://localhost:10000/default' -n labex -e 'USE retail_intro; SHOW TABLES;'

You should find orders. Every -e command opens a new Beeline session, so a USE in one invocation does not silently carry into the next. Either select the database in each SQL file or use its qualified name, retail_intro.orders.

DESCRIBE lists the table's columns and types. For example, STRING stores text, INT stores whole numbers, DECIMAL(10,2) stores fixed-point numbers with two decimal places, and DATE represents a calendar date:

beeline -u 'jdbc:hive2://localhost:10000/default' -n labex -e 'DESCRIBE retail_intro.orders;'

Match the fields with the first query's rows. A quantity and a price have different meanings and types even though both look numeric. You will calculate monetary values in a later lab; today you only read the provided fields.

For saved query results, Beeline can use a simple text format. --outputformat=tsv2 selects tab-separated fields, --showHeader=false omits the header row, and --silent=true reduces client chatter. Save the described schema using those output options:

beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e 'DESCRIBE retail_intro.orders;' > /home/labex/project/output/table-schema.txt

The transcript may also include runtime messages. The important schema rows name all seven columns and their types. The table definition is metadata: it tells Hive how to interpret stored files. The next lab will show you how to create that mapping yourself.

Save a Query for the First Paid Orders

In this step, you will select only the columns and paid rows needed by the review team, with a predictable result order.

A query can narrow data in several independent ways:

You will request order_id, order_status, and quantity, keep only paid rows, sort by order ID, and return the first two. Predict which rows survive: among the six provided orders, o1 and o2 are the first paid IDs in that order.

Save SQL in a local file so it can be inspected and run again. In the next shell command, cat > file <<'SQL' starts a here-document: the lines up to the closing SQL are written literally into the file. They are SQL text, not shell commands. Copy the complete block, including the delimiter lines:

cat > /home/labex/project/queries/first-query.sql <<'SQL'
USE retail_intro;
SELECT order_id, order_status, quantity
FROM orders
WHERE order_status = 'paid'
ORDER BY order_id
LIMIT 2;
SQL

The quoted 'paid' inside the SQL is a text value. The = compares each row's status with that value; it does not assign a new status. The USE statement makes the file independent of a previous client session.

Beeline's -f option reads a local SQL file. Execute your file and save its output with the text-format options you just learned:

beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/first-query.sql > /home/labex/project/output/first-query.txt

Read the saved transcript:

cat /home/labex/project/output/first-query.txt

The result rows should be:

o1 paid 2
o2 paid 1

Any surrounding runtime messages are not order records. LIMIT 2 did not mean two arbitrary rows: the explicit ordering determined which matching rows came first. You now have a reusable query file and an observable result for the team's question.

Predict the Effect of One Condition Change

In this step, you will change only the status condition and check whether the returned rows match your prediction.

Keep the paid-order query intact. A pending order exists in the same table: o3, with quantity 1. If the status condition changes from paid to pending, how many rows should a limit of two return? A limit is a maximum, so the answer is one.

Write the same query with only that condition changed:

cat > /home/labex/project/queries/pending-query.sql <<'SQL'
USE retail_intro;
SELECT order_id, order_status, quantity
FROM orders
WHERE order_status = 'pending'
ORDER BY order_id
LIMIT 2;
SQL

Execute it independently:

beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/pending-query.sql > /home/labex/project/output/pending-query.txt

Read the captured result:

cat /home/labex/project/output/pending-query.txt

The only order row should be:

o3 pending 1

Changing a filter selected a different row without changing the table's stored records. You have now used the same basic query structure to answer two questions. Next, you will connect an existing CSV file to a table definition instead of relying on a prepared table.

Summary

You placed Hive in a file-to-table-to-query workflow, connected using Beeline, and explored a database, table, and schema. You wrote a reusable SQL file using selection, filtering, explicit ordering, and a row limit, then predicted and checked a one-condition change.

The table was prepared for this first experience. In the next lab, you will map a CSV file to your own external table and learn how its metadata and underlying files relate.