Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 36 ยท HiveQL Basics

Loading and Inserting Data

Learn every way to get data into and out of Hive tables: LOAD DATA, INSERT VALUES, INSERT SELECT, INSERT OVERWRITE, multi-insert, CTAS, and exporting results to directories.

A Hive table is a folder of files, so getting data into one is a question of how the files arrive. Hive gives you a few distinct tools: LOAD DATA simply moves files; INSERT ... SELECT runs a query and writes new files; INSERT OVERWRITE replaces content. Choosing the right one avoids slow jobs, tiny files and accidental data loss.

Load statements need a Hive server and a file system, so they are shown read-only. The runnable editors are SQLite stand-ins for the SQL-standard statements (INSERT VALUES, INSERT ... SELECT, replacing rows); SQLite has no LOAD DATA or INSERT OVERWRITE, and we say how we imitate them.

LOAD DATA: move files

SQL
LOAD DATA LOCAL INPATH '/home/alice/sales_2025_01.csv'
INTO TABLE sales;

LOAD DATA INPATH '/landing/sales_2025_02.csv'
OVERWRITE INTO TABLE sales;

LOAD DATA INPATH '/landing/2025-03/'
INTO TABLE sales_partitioned PARTITION (month = '2025-03');
  • LOCAL means the path is on the local file system of the machine running HiveServer2, and the file is copied to the table folder. Without LOCAL, the path is in HDFS (or the table's file system) and the file is moved; it disappears from the source.
  • OVERWRITE deletes the existing files of the table or partition first.
  • LOAD DATA does no conversion. The file must already be in the format the table expects (for an ORC table, a CSV file loads without complaint and then queries fail). For a format change, load into a text staging table first, then INSERT ... SELECT into the target.
  • For a partitioned table you must name the partition.

This staging pattern is very common:

SQL
CREATE EXTERNAL TABLE staging_sales (...) STORED AS TEXTFILE LOCATION '/landing/sales';

INSERT OVERWRITE TABLE sales PARTITION (month = '2025-03')
SELECT id, customer_id, total
FROM staging_sales;

INSERT ... VALUES

SQL
INSERT INTO TABLE customers VALUES
  (1, 'Ana',  'Lisbon'),
  (2, 'Ben',  'Oslo');

Fine for testing or a handful of rows. Every statement creates at least one new file, so thousands of small inserts leave thousands of tiny files and slow down every later query.

SQL Stand-in: INSERT VALUES then query (SQLite, not Hive)

Press Run (or Ctrl + Enter) to see the result here.

INSERT ... SELECT

The workhorse of ETL. It reads from any query and writes the result into a table:

SQL
INSERT INTO TABLE daily_totals
SELECT order_date, SUM(total)
FROM orders
GROUP BY order_date;

The columns are matched by position, not by name. If the select list is in a different order from the table, values land in the wrong columns without an error (when the types fit). List columns explicitly and in order.

SQL Stand-in: INSERT ... SELECT (SQLite, not Hive) ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

INSERT OVERWRITE

SQL
INSERT OVERWRITE TABLE daily_totals
SELECT order_date, SUM(total) FROM orders GROUP BY order_date;

INSERT OVERWRITE TABLE sales PARTITION (month = '2025-03')
SELECT id, customer_id, total FROM staging_sales;

OVERWRITE replaces all existing data in the table or the named partition with the query result. That makes jobs idempotent: running yesterday's load twice gives the same result, rather than duplicating rows as INSERT INTO would. It is the preferred pattern for daily batch loads. On a partitioned table, only the partitions you write are replaced, unless you overwrite the whole table with dynamic partitioning (see Dynamic Partitioning).

To imitate it in SQLite, clear and refill inside one transaction:

SQL Stand-in: overwrite by delete + insert (SQLite, not Hive)

Press Run (or Ctrl + Enter) to see the result here.

Multi-insert: read once, write many

Hive lets one scan feed several tables:

SQL
FROM orders o
INSERT OVERWRITE TABLE paid_orders     SELECT o.* WHERE o.status = 'paid'
INSERT OVERWRITE TABLE refunded_orders SELECT o.* WHERE o.status = 'refunded';

The source is read only once, which can halve the cost compared to two separate statements.

CTAS

CREATE TABLE ... AS SELECT creates and fills a new table in one step; see Databases and DDL.

Exporting data

Write query results to files with INSERT OVERWRITE DIRECTORY:

SQL
INSERT OVERWRITE DIRECTORY '/exports/daily'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
SELECT * FROM daily_totals;

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/daily'
SELECT * FROM daily_totals;

Add LOCAL to write to the server's local disk. The directory's previous content is deleted. For whole tables, EXPORT TABLE t TO '/path' and IMPORT TABLE t FROM '/path' move data plus metadata between clusters.

UPDATE, DELETE and MERGE

Row-level changes exist only on transactional (ACID) tables, covered in UDFs and Transactions. On ordinary tables, to change data, rewrite it with INSERT OVERWRITE.

Choosing the right tool

You want to... Use
Add files already in the right format LOAD DATA INPATH
Convert format or transform INSERT ... SELECT into the target table
Replace a partition's content safely INSERT OVERWRITE ... PARTITION
Create a table from a query CTAS
Feed several tables from one scan multi-insert
Export results INSERT OVERWRITE [LOCAL] DIRECTORY

Common mistakes

  • Loading a CSV with a header row and counting the header as data. Skip it with TBLPROPERTIES ('skip.header.line.count'='1').
  • Using INSERT INTO in a nightly job that is later rerun, producing duplicates.
  • Loading a file of the wrong format into an ORC or Parquet table.
  • Using INSERT OVERWRITE DIRECTORY with a path that holds other files: they are deleted.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens to the source file when you run LOAD DATA INPATH (no LOCAL)?

    Loading from HDFS moves the file; LOCAL loads copy from the server's local disk.
  2. 2. Why is INSERT OVERWRITE good for daily batch jobs?

    Overwriting replaces the old content, so reruns do not duplicate data.
  3. 3. How does INSERT ... SELECT match columns?

    Position decides, so list the select columns in the table's order.
  4. 4. How do you load a CSV into an ORC table?

    LOAD DATA does no conversion; INSERT ... SELECT writes new files in the target's format.

Summary

  • LOAD DATA moves or copies files without checking or converting them.
  • INSERT ... SELECT transforms and writes in the table's format; columns match by position.
  • INSERT OVERWRITE replaces data and makes batch loads repeatable; multi-insert scans once.
  • Export with INSERT OVERWRITE DIRECTORY; avoid many tiny single-row inserts.

Next: SELECT, Filter and Sort in Hive