Applies to: Databricks Runtime
Loads the data into a Hive SerDe table from the user specified directory or file. If a directory is specified then all the files from the directory are loaded. If a file is specified then only the single file is loaded. Additionally the
LOAD DATA statement takes an optional partition specification. When a partition is specified, the data files (when input source is a directory) or the single file (when input source is a file) are loaded into the partition of the target table.
If the table is cached, the command clears cached data of the table and all its dependents that refer to it. The cache will be lazily filled when the table or the dependents are accessed the next time.
Path of the file system. It can be either an absolute or a relative path.
Identifies the table to be inserted to. The name must not include a temporal specification. If the table cannot be found Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.
An optional parameter that specifies a target partition for the insert. You may also only partially specify the partition.
If specified, it causes the
INPATHto be resolved against the local file system, instead of the default file system, which is typically a distributed storage.
By default, new data is appended to the table. If
OVERWRITEis used, the table is instead overwritten with new data.
-- Example without partition specification. -- Assuming the students table has already been created and populated. > SELECT * FROM students; name address student_id --------- ---------------------- ---------- Amy Smith 123 Park Ave, San Jose 111111 > CREATE TABLE test_load (name VARCHAR(64), address VARCHAR(64), student_id INT) USING HIVE; -- Assuming the students table is in '/user/hive/warehouse/' > LOAD DATA LOCAL INPATH '/user/hive/warehouse/students' OVERWRITE INTO TABLE test_load; > SELECT * FROM test_load; name address student_id --------- ---------------------- ---------- Amy Smith 123 Park Ave, San Jose 111111 -- Example with partition specification. > CREATE TABLE test_partition (c1 INT, c2 INT, c3 INT) PARTITIONED BY (c2, c3); > INSERT INTO test_partition PARTITION (c2 = 2, c3 = 3) VALUES (1); > INSERT INTO test_partition PARTITION (c2 = 5, c3 = 6) VALUES (4); > INSERT INTO test_partition PARTITION (c2 = 8, c3 = 9) VALUES (7); > SELECT * FROM test_partition; c1 c2 c3 --- --- --- 1 2 3 4 5 6 7 8 9 > CREATE TABLE test_load_partition (c1 INT, c2 INT, c3 INT) USING HIVE PARTITIONED BY (c2, c3); -- Assuming the test_partition table is in '/user/hive/warehouse/' > LOAD DATA LOCAL INPATH '/user/hive/warehouse/test_partition/c2=2/c3=3' OVERWRITE INTO TABLE test_load_partition PARTITION (c2=2, c3=3); > SELECT * FROM test_load_partition; c1 c2 c3 --- --- --- 1 2 3