Databricks SQL Tutorial: Load Cloud Files Incrementally with COPY INTO

This tutorial demonstrates how to load data from cloud files into a Databricks SQL table incrementally using the `COPY INTO` command. We’ll focus on the key aspects of this process and build three scripts to illustrate the concepts.

Script 1: Initial Load and Verification

This script performs the initial load of data into the target table. It assumes the source file exists and contains comma-separated values.

CREATE TABLE IF NOT EXISTS my_incremental_table (
    id INT,
    name STRING,
    value DOUBLE
);
COPY INTO my_incremental_table
FROM 'dbfs:/data/initial_data.csv'
FILE_FORMAT = CSV
FIELD_DELIMITER = ','
HEADER = TRUE;
SELECT COUNT() FROM my_incremental_table;

Explanation:

• `CREATE TABLE IF NOT EXISTS my_incremental_table (…)`: This statement creates the target table `my_incremental_table` if it doesn’t already exist. The table has three columns: `id`, `name`, and `value`, with appropriate data types.
• `COPY INTO my_incremental_table FROM ‘dbfs:/data/initial_data.csv’`: This command copies data from the file located at `dbfs:/data/initial_data.csv` into the `my_incremental_table`. `dbfs:/data/` is the Databricks File System (DBFS) path where the CSV file resides.
• `FILE_FORMAT = CSV`: Specifies that the source file is in CSV format.
• `FIELD_DELIMITER = ‘,’`: Specifies that the fields within the CSV file are separated by commas.
• `HEADER = TRUE`: Indicates that the first row of the CSV file contains the column headers.
• `SELECT COUNT() FROM my_incremental_table`: This query counts the number of rows in the `my_incremental_table` and returns the result. This is our final validation step for this initial load.

Let’s assume `initial_data.csv` contains the following data:

id,name,value
1,Alice,10.5
2,Bob,20.0
3,Charlie,15.75

Output

1

Script 2: Incremental Load – Adding a New Row

This script demonstrates how to load a new row of data into the table incrementally. We’ll first add a new file containing only the new data.

-- Create a new data file
-- (This is simulated.  In a real scenario, this would involve a new CSV file)
COPY INTO my_incremental_table (id, name, value)
VALUES (4, 'David', 25.25);

SELECT COUNT() FROM my_incremental_table;

Explanation:

• `COPY INTO my_incremental_table (id, name, value) VALUES (4, ‘David’, 25.25)`: This command appends a new row with the specified values to the `my_incremental_table`. Note that we explicitly specify the columns in the `COPY INTO` statement.
• `SELECT COUNT() FROM my_incremental_table`: This query counts the number of rows in the `my_incremental_table` and returns the result. This is our final validation step.

After executing this script, the `my_incremental_table` should contain the following data:

id,name,value
1,Alice,10.5
2,Bob,20.0
3,Charlie,15.75
4,David,25.25

Output

4

Script 3: Incremental Load – Adding a New File with Multiple Rows

This script demonstrates loading multiple rows from a new file into the `my_incremental_table`.

-- Create a new data file
-- (This is simulated. In a real scenario, this would involve a new CSV file)
COPY INTO my_incremental_table (id, name, value)
VALUES
(5, 'Eve', 30.0),
(6, 'Frank', 12.75);

SELECT COUNT() FROM my_incremental_table;

Explanation:

• `COPY INTO my_incremental_table (id, name, value) VALUES …`: This command appends multiple rows to the `my_incremental_table`. We use a series of `VALUES` clauses to specify the data for each new row.
• `SELECT COUNT() FROM my_incremental_table`: This query counts the number of rows in the `my_incremental_table` and returns the result. This is our final validation step.

After executing this script, the `my_incremental_table` should contain the following data:

id,name,value
1,Alice,10.5
2,Bob,20.0
3,Charlie,15.75
4,David,25.25
5,Eve,30.0
6,Frank,12.75

Output

6