Skip to main content

Load Data into Tables

Dremio stores data in Apache Iceberg tables. You have three options for getting data in: define a schema and load data into it, upload a file and let Dremio infer the schema, or set up continuous ingestion from S3.

Iceberg v3​

Dremio creates Iceberg v2 tables by default and automatically upgrades to v3 when your CREATE TABLE statement includes a v3 feature. v3 introduces the VARIANT data type, nanosecond support, default column values, and deletion vectors for faster DELETE, UPDATE, and MERGE performance.

For details on v3 features and configuration options, see Iceberg v3.

Copy Data into Tables​

Querying large datasets stored in CSV or JSON files can be inefficient. For faster performance and scalability, load your data into Apache Iceberg tables, which use the columnar Parquet format for optimized storage and retrieval. Even queries on Parquet files perform faster when the data is stored in Iceberg tables, enabling you to take full advantage of Dremio’s Iceberg capabilities.

Prerequisites​

  • At least one column in the target table must match a column in every data file.
  • Files cannot contain duplicate column names.
  • CSV data files must have a header line at the start of the file.
  • Supported storage locations: Azure Storage or Amazon S3.

Copy Operation​

Use the COPY INTO SQL command to load data from CSV, JSON, and Parquet files into existing Iceberg tables. The operation matches columns in the files to columns in the target table and loads data accordingly.

The copy operation supports Iceberg tables in the Open Catalog, AWS Glue Data Catalog, and catalogs that implement the Iceberg REST Catalog specification.

The copy operation verifies that at least one column in the target table matches a column represented in the data files. It then follows these rules:

  • If a match is found, the values in the data files are loaded into the column or columns.
  • If additional non-matching columns are present in the data files, the values in these columns are not loaded.
  • If additional non-matching columns are present in the target table, the operation inserts NULL values into these columns.
  • If no column in the target table matches any column represented in the data files, the operation fails.

The copy operation ignores case when comparing column names. Default values do not apply to COPY INTO operations. The existing values in the data will be what is written.

Column Nullability Constraints​

A column's nullability constraint defines whether the column can contain NULL values, because you can specify that each column is either:

  • NULL — Allows NULL values, which is useful for optional or unknown data.
  • NOT NULL — Requires a value for every row; NULL values are not allowed.

When running COPY INTO with ON_ERROR set to 'continue' or 'skip_file', the command will not fail on nullability conflicts. Instead, it skips the problematic file or record.

However, if ON_ERROR is set to 'abort' (or left unspecified), the command will fail if any row violates the table’s NOT NULL constraints.

Upload Local Files​

You can upload an individual local file to Dremio if the file is 500 MB or smaller and in CSV, JSON, or Parquet format. During the upload process, Dremio formats the file into an Iceberg table.

To upload a file:

  1. In the Dremio console, click This is the icon that represents the Datasets page. in the side navigation bar to go to the Datasets page.

  2. Click Add Data in the bottom left corner of the Datasets page.

  3. Upload the file either by:

    a. Dragging the file from your local machine and dropping it in the Add Data dialog.

    b. Clicking Choose file to upload and navigating to the file on your local machine.

    If the file is large, it may take a few moments to upload, depending on your connection speed.

  4. (Optional) During the upload process, configure the file settings. For example, configure how the file is delimited.

  5. Click Save.

Limits​

  • Uploaded files are copies of your local file. Updates to your local file are not automatically reflected in Dremio.
  • Bulk upload of multiple files is not supported.
  • Maximum file size is 500 MB, single file only. For all resource limits, see Limits.

Case Sensitivity​

Dremio does not support case-sensitive data file names, table names, or column names.

For example, if you have three file names that have the same name, but with different cases (such as, MARKET, Market, and market), Dremio is unable to discern the case differences, resulting in unanticipated data results.

For column names, if two columns have the same name using different cases (such as Trip_Pickup_DateTime and trip_pickup_datetime) exist in the table, one of the columns may disappear when the header is extracted.