COPY INTO <table>

You can load data from CSV or JSON files that are in a source into an existing Apache Iceberg table.

important:

For information about how the COPY INTO <table> operation works, as well as the supported storage locations and requirements, see Copying Data Into Apache Iceberg Tables.

Syntax

Syntax

COPY INTO <table_name> 
  FROM '@<storage_location_name>[/<path>[/<file_name>] ]'
  [ FILES ( '<file_name>' [ , ... ] ) |
    REGEX '<regex_pattern>' ] 
[ FILE_FORMAT 'csv' | 'json' ]
[ ( [csv_format_options] | [json_format_options] ) ]

csv_format_options

DATE_FORMAT '<string>' | TIME_FORMAT '<string>' | TIMESTAMP_FORMAT '<string>' | TRIM_SPACE [ '<boolean>' ] | NULL_IF ( '<string>' [, ...] ) | RECORD_DELIMITER '<character>' | FIELD_DELIMITER '<character>' | QUOTE_CHAR '<character>' | ESCAPE_CHAR '<escape_character>' | EMPTY_AS_NULL [ '<boolean>' ]  [, ...]

json_format_options

DATE_FORMAT '<string>' | TIME_FORMAT '<string>' | TIMESTAMP_FORMAT '<string>' | TRIM_SPACE [ '<boolean>' ] | NULL_IF ( '<string>' [, ...] ) [, ...]

Parameters

{{< sql-section file="data/sql/copy-into-cloud.json" data="mainSyntax" >}}

CSV Format Options

{{< sql-section file="data/sql/copy-into-cloud.json" data="csvFormatOptions" >}}

JSON Format Options

{{< sql-section file="data/sql/copy-into-cloud.json" data="jsonFormatOptions" >}}

Type Coercion

Source Data Type	Target Dremio Data Type
String	BIGINT, BOOLEAN, DATE, DECIMAL, DOUBLE, FLOAT, INT, TIME, TIMESTAMP, VARCHAR
Integer	BIGINT, DECIMAL, DOUBLE, FLOAT, INT, VARCHAR
Floating-point numbers	DECIMAL, DOUBLE, FLOAT, VARCHAR
Boolean	BOOLEAN, VARCHAR
Object (JSON only)	STRUCT
Array (JSON only)	LIST

note:

Although the TIMESTAMP data type is supported as a target schema data type, TIMESTAMP with time zone is not supported.

Output

The command returns this output: | Column name | Description | | --- | --- | | Rows Inserted | The number of rows loaded from the source data files. |

Examples

Copy all files present in a folder

COPY INTO context.MyTable
  FROM '@SOURCE/bucket/path/folder/' 
  FILE_FORMAT 'json'

Copy a specific file in a directory

COPY INTO context.myTable 
  FROM '@SOURCE/bucket/path/folder/file1.json'

Copying files using a regular expression pattern string matching filename

COPY INTO context.myTable 
  FROM '@SOURCE/bucket/path/folder' 
  REGEX '.*.csv'

Copying files using a regular expression pattern string matching filename and path

COPY INTO 'context.myTable' 
  FROM '@SOURCE/bucket/path/folder' 
  REGEX '^2020-11-1[2-3]/.*/' 
  FILE_FORMAT 'json'

Copying files using a regular expression pattern string in the specified file format

COPY INTO context.myTable 
  FROM '@SOURCE/bucket/path/folder' 
  REGEX 'part.*' 
  FILE_FORMAT 'json'

Copying a list of files present on a Dremio source

COPY INTO context.myTable 
  FROM '@SOURCE/bucket/path/folder'
  FILES ('foo.csv', 'dir/bar.csv')

Copying files containing DATE and TIME types

COPY INTO context.myTable 
  FROM '@SOURCE/bucket/path/folder' 
  FILES ('date_time_data_file1.csv', 'date_time_data_file2.csv') 
  (DATE_FORMAT 'DD-MM-YYYY', TIME_FORMAT 'HH24:MI:SS')

Copying CSV files and replacing certain entries with NULL, if encountered

COPY INTO context.myTable 
  FROM '@SOURCE/bucket/path/folder' 
  FILES ('file1.csv', 'file2.csv', 'file3.csv') 
  (NULL_IF ('None', 'NA'))

Copying with the following transformations: Trimming any leading and trailing whitespaces for each entry, and treating empty values as NULL

COPY INTO context.myTable 
  FROM '@SOURCE/bucket/path/folder' 
  (TRIM_SPACE 'true', EMPTY_AS_NULL 'true')

Copying CSV files into a table with RECORD_DELIMITER and FIELD DELIMITER clauses

COPY INTO context.myTable 
  FROM '@SOURCE/bucket/path/folder' 
  FILE_FORMAT 'csv' 
  (RECORD_DELIMITER '\n', FIELD_DELIMITER '\t')

Copying a CSV file into a table with an ESCAPE_CHAR clause

COPY INTO context.myTable
  FROM '@SOURCE/bucket/path/folder'
  FILES ('fileName.csv')
  (ESCAPE_CHAR '|')