Apache Hive serves as a data warehouse infrastructure built on top of Hadoop, enabling the summarization, querying, and analysis of large datasets. A critical aspect of managing this data involves transferring it efficient between Hive and external systems. This guide outlines the primary methods for importing data into Hive tables and exporting results for downstream use.

Importing Data into Hive

Populating Hive tables with data from external sources can be achieved through direct file loading or by defining external tables that map to existing data locations.

1. Loading Data from File Systems

Hive provides the LOAD DATA command to move data from the local file system or HDFS into Hive tables. This operation is efficient as it essentially moves the file rather than copying the data content.

From Local File System:

To import a file from the local machine into a Hive table, use the LOCAL keyword. The following example loads a comma-separated values file into the employee_records table:

LOAD DATA LOCAL INPATH '/var/data/input/employees.csv' 
OVERWRITE INTO TABLE employee_records;

From HDFS:

When the source file already resides in HDFS, omit the LOCAL keyword. It is crucial to note that this operation moves the file from its original HDFS location to the Hive table's warehouse directory, meening the source file will no longer exist at its original path.

LOAD DATA INPATH '/user/hdfs/staging/sales_data.csv' 
INTO TABLE sales_transactions;

2. Utilizing External Tables

Rather than moving data, Hive allows the creation of External Tables that point directly to a specific HDFS directory. This method is ideal when the data is managed by external processes or needs to be shared across different applicasions.

CREATE EXTERNAL TABLE server_logs (
    log_id INT,
    log_message STRING,
    event_time TIMESTAMP
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION '/data/archives/system_logs';

Exporting Data from Hive

Extracting data from Hive involves writing query results to a file system or using specialized commands for table backups.

1. Writing Query Results to File Systems

The INSERT OVERWRITE DIRECTORY statement allows the results of a query to be saved directly to HDFS or the local file system.

Export to HDFS:

The following command writes the contents of the sales_transactions table to a specified HDFS directory:

INSERT OVERWRITE DIRECTORY '/user/hdfs/output/processed_sales'
SELECT * FROM sales_transactions;

Export to Local File System:

By including the LOCAL keyword, the output is written to a directory on the local disk where the Hive client is running:

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/hive_exports/employee_data'
SELECT emp_id, emp_name, department FROM employee_records;

2. Using EXPORT and IMPORT Commands

For portability and backup purposes, Hive offers the EXPORT and IMPORT functionalities. These commands handle both the data and the metadata (table definition), making them suitable for migrating tables between different clusters.

Exporting a Table:

EXPORT TABLE sales_transactions TO '/user/hdfs/backups/sales_backup';

Importing a Table:

To restore or migrate the table, the IMPORT command is used:

IMPORT TABLE sales_restored FROM '/user/hdfs/backups/sales_backup';