LOAD

Syntax

LOAD DATA INPATH filepath [OVERWRITE] INTO TABLE tablename

[PARTITION(partcol1=value1,partcol2=values2...)]

Description

This statement is used to load data from a file or folder to a table.

Filepath: Enter the absolute path of the file or directory.

OVERWRITE: If this keyword is used, the data in the target table (or partition) is deleted and replaced with the data read from the file.

Remarks

To load data to a specified partition, you must list all fields of the table in the partition clause.

Complex data types, such as Array and Map, are not supported.

External tables are not supported.

The format of the data file must be the same as that of the target table.

When creating the target table, specify the separator of the file and ensure that the separator is the same as that in the data file.

Example

Create the f1.txt file, enter three lines of numbers, and upload the file to the /opt/load_test/ directory by using HDFS.

-- Read data from the f1.txt file and fill in the f1 table.
CREATE TABLE tb_load_f1(id int) with (format='TEXTFILE');
 
LOAD DATA INPATH '/opt/load_test/f1.txt' into table tb_load_f1;
 
select * from tb_load_f1;
 id 
----
  1 
  2 
  3 
(3 rows)
 
-- Read files in the /opt/load_test/ directory and fill in the f2 table.
CREATE TABLE tb_load_f2(id int) with (format='TEXTFILE');
 
LOAD DATA INPATH '/opt/load_test/' into table tb_load_f2;
 
select * from tb_load_f2;
 id 
----
  1 
  2 
  3 
(3 rows)
 
-- Read the f3.txt file and fill in the f3 table (use hyphens (-) to separate multiple fields), and upload the f3.txt file to the /opt/load_test/ directory by using HDFS. The content of the f3.txt file is as follows:
1  1-n1
2  2-n2
-- Create the tb_load_f3 table.
CREATE TABLE tb_load_f3(id int,name varchar) with(format='TEXTFILE',textfile_field_separator='-');
 
Load data inpath '/opt/load_test/f3.txt' to table tb_load_f3;
 
Select * from tb_load_f3;
id | name 
----|------
  1 | n1   
  2 | n2   
(2 rows)