Skip to main content
Last updated on

Get started using COPY INTO to load data

The COPY INTO SQL command lets you load data from a file location into a Delta table. This is a re-triable and idempotent operation. Files in the source location that have already been loaded are skipped.

COPY INTO offers these capabilities:

  • Easily configurable file or folder filters from cloud storage, including S3, ADLS, ABFS, GCS, and Unity Catalog volumes.
  • Support for multiple source file formats: CSV, JSON, XML, Avro, ORC, Parquet, text, and binary files.
  • Exactly-once (idempotent) file processing by default.
  • Target table schema inference, mapping, merging, and evolution.
warning

COPY INTO respects the workspace setting for deletion vectors. If enabled, deletion vectors are enabled on the target table when COPY INTO runs on a SQL warehouse or compute running Databricks Runtime 14.0 or above. After deletion vectors are enabled, they block queries against a table in Databricks Runtime 11.3 LTS and below. See Deletion vectors in Databricks and Auto-enable deletion vectors.

Before you begin​

An account admin must configure access to data in cloud object storage before users can load data using COPY INTO.

Example: Load data into a schemaless Delta Lake table​

note

Databricks Runtime 11.3 LTS and above is required for this feature.

You can create empty placeholder Delta tables so that the schema is inferred during a COPY INTO command by setting mergeSchema to true in COPY_OPTIONS:

SQL
CREATE TABLE IF NOT EXISTS my_table
[COMMENT <table-description>]
[TBLPROPERTIES (<table-properties>)];

COPY INTO my_table
FROM '/path/to/files'
FILEFORMAT = <format>
FORMAT_OPTIONS ('mergeSchema' = 'true')
COPY_OPTIONS ('mergeSchema' = 'true');

The SQL statement is idempotent and can be scheduled to run to ingest data exactly once into a Delta table.

note

The empty Delta table is not usable outside COPY INTO. INSERT INTO and MERGE INTO are not supported to write data into schemaless Delta tables. After data is inserted into the table with COPY INTO, the table becomes queryable.

See Create target tables for COPY INTO.

Example: Set schema and load data into a Delta Lake table​

The following example creates a Delta table and uses the COPY INTO SQL command to load sample data from Databricks datasets into the table. You can run the example Python, R, Scala, or SQL code from a notebook attached to a Databricks cluster. You can also run the SQL code from a query associated with a SQL warehouse in Databricks SQL.

SQL
DROP TABLE IF EXISTS default.loan_risks_upload;

CREATE TABLE default.loan_risks_upload (
  loan_id BIGINT,
  funded_amnt INT,
  paid_amnt DOUBLE,
  addr_state STRING
);

COPY INTO default.loan_risks_upload
FROM '/databricks-datasets/learning-spark-v2/loans/loan-risks.snappy.parquet'
FILEFORMAT = PARQUET;

SELECT * FROM default.loan_risks_upload;

-- Result:
-- +---------+-------------+-----------+------------+
-- | loan_id | funded_amnt | paid_amnt | addr_state |
-- +=========+=============+===========+============+
-- | 0       | 1000        | 182.22    | CA         |
-- +---------+-------------+-----------+------------+
-- | 1       | 1000        | 361.19    | WA         |
-- +---------+-------------+-----------+------------+
-- | 2       | 1000        | 176.26    | TX         |
-- +---------+-------------+-----------+------------+
-- ...

To clean up, run the following code that deletes the table.

Python
spark.sql("DROP TABLE " + table_name)

Clean up metadata files​

You can run VACUUM to clean up unreferenced metadata files created by COPY INTO in Databricks Runtime 15.2 and above.

Reference​

Learn more​