Version: current [26.x]

Transforming Data on Load

Dremio allows data transformations during loading with the COPY INTO SQL command, simplifying the ETL process by enabling simple transformations as data is ingested.

Common use cases for this functionality include:

Selecting a subset of columns from source files to write into the target table.
Reordering columns in source files to match the order in the target table.
Converting data types from source files into target table type, which is most common with CSVs.
Applying simple elementwise functions, which would require additional processing later.

Supported File Formats

Dremio's COPY INTO SQL command with transformations supports Parquet and CSV file formats.

Select a Subset of Columns

Imagine we receive a file that contains the available inventory in each store every day; however, the file also includes item details that we don't need. We need to load only a subset of the data and exclude information that does not help build our data product. Since the provided CSV file does not include headers, we must set EXTRACT_HEADER to FALSE (see file_column_number for more information). This then allows us to reference the columns by their order in the file using the following query:

COPY INTO inventory(store_id, stock_date,  item_id, amount)
    FROM (SELECT $1, $5, $2, $6 FROM '@mystage/storeinfo.csv')
    FILE_FORMAT   'csv'
    (EXTRACT_HEADER FALSE);

Reorder Columns

Sometimes, the data in the underlying file does not match how you want to materialize the data in Dremio. In the example below, we have a CSV snapshot of our customer data, and the cust_id column is the fifth column in the file. As a best practice, we want to move the table's primary key to the first column of the table. Because the CSV file includes headers, we must reference the columns by their header names as defined in the file. To achieve this, we load the data with the following:

COPY INTO dim_customer(cust_id, name, address, age)
    FROM (SELECT cust_id, name, address, age FROM '@mystage/customer.csv')
    FILE_FORMAT   'csv';

Convert Data Types

If you want to convert the source data types of your files into target types, you can add that to your SELECT statement as well.

COPY INTO dim_customer(cust_id, name, address, age)
    FROM (SELECT CAST(cust_id AS INTEGER), name, address, age FROM '@mystage/customer.csv')
    FILE_FORMAT   'csv';

Supported Functions

Unsupported Capabilities

The SELECT statement used for transforming data in the COPY INTO SQL command has the following restrictions:

WHERE clause
ORDER BY statement
GROUP BY statement
JOIN
aggregate and window functions

Unsupported file types

The COPY INTO with transformations capability does not support JSON, AVRO, or ORC file types.

Supported File Formats​