1: An error does not occur for any missing file, and data loading continues with the existing files. An error occurs if all files are not available.

0: If any file is missing, an error occurs and data is not loaded.

If you set no value or DEFAULT, the setting is defined by STRICT_LOAD. If no value is set for STRICT_LOAD, the setting is defined by the sql_mode.

Review Table 4.6, “Combinations of Settings for ALLOW_MISSING_FILES and STRICT_LOAD”.

With the FILE_NAME parameter: There is no file with that name.

With the FILE_PATTERN parameter: There are no files that match the pattern.

With the FILE_PREFIX parameter: There are no files with that prefix.

1: Loads the data in strict mode. The data loading stops if there is an error due to missing files, empty columns, formatting errors or parsing errors.

0: Loads the data in non-strict mode. Missing files, empty columns, formatting errors or parsing errors display a warning, and data is loaded.

If you set no value or DEFAULT,, the setting is defined by the sql_mode.

Review Table 4.6, “Combinations of Settings for ALLOW_MISSING_FILES and STRICT_LOAD”.

1: The default value. Lakehouse validates primary key and unique key constraints only during the initial load of the table. If there are subsequent loads or refreshes of the table, validation does not occur.

0: Lakehouse does not validate primary key and unique key constraints.

DEFAULT: Lakehouse does not validate primary key and unique key constraints.

URI: The URL of the URI you specify. Do not provide a pattern, prefix, or name parameter with the URI. These details are inferred by the object_path you define.

The following examples use the URI syntax for OCIFS:

oci://bucket_name@namespace_name/object_path

URL: The PAR URL. Review PAR Recommendations.

The following example loads a table with a single file specified with a PAR.

mysql> CREATE EXTERNAL TABLE table_1(
        col_1 int,
        col_2 int,
        col_3 int)
        FILE_FORMAT = (FORMAT csv)
        FILES = (URL = 'https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenancy_1/b/bucket_1/o/data_file_1.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

FILE_NAME: A specific Object Storage file name. For pre-authenticated requests, the file name provided in the PAR URL is considered.

The following example uses the FILE_NAME parameter to specify a file in an Object Storage bucket. The PAR points to the Object Storage bucket.

mysql> CREATE EXTERNAL TABLE table_1(
        col_1 int,
        col_2 int,
        col_3 int)
        FILE_FORMAT = (FORMAT csv)
        FILES = (URL = 'https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenancy_1/b/bucket_1/o/'
                 FILE_NAME = 'data_files/data_file_1.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

FILE_PATTERN: A regular expression that defines a set of Object Storage files.

The following example uses a pattern to load files that have a numerical suffix.

mysql> CREATE EXTERNAL TABLE table_1(
        col_1 int,
        col_2 int,
        col_3 int)
        FILE_FORMAT = (FORMAT csv)
        FILES = (URL = 'https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenancy_1/b/bucket_1/o/'
                 FILE_PATTERN = 'data_files/data_file_\\\\d+\\\\.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

See the following to learn more:

FILE_PREFIX: The prefix for a set of Object Storage files. The prefix or bucket name present in the PAR URL is considered.

The following example uses a prefix to specify files to load in an Object Storage folder.

mysql> CREATE EXTERNAL TABLE table_1(
        col_1 int,
        col_2 int,
        col_3 int)
        FILE_FORMAT = (FORMAT csv)
        FILES = (URL = 'https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenancy_1/b/bucket_1/o/'
                 FILE_PREFIX = 'data_files/');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

Review additional examples that use PARs to load data manually.

1: If any file is missing, no error occurs and data loading continues with the existing files. An error occurs if all the files are not available.

0: If any file is missing, an error occurs and data is not loaded.

1: Loads the data in strict mode. The data loading stops if there is an error due to missing files, empty columns, formatting errors or parsing errors.

0: Loads the data in non-strict mode. Missing files, empty columns, formatting errors or parsing errors display a warning, and data is loaded.

csv: The default file format.

The following example loads a CSV file into a table with a uniform resource identifier (URI).

mysql> CREATE EXTERNAL TABLE table_1(
        col_1 int,
        col_2 int,
        col_3 int)
        FILE_FORMAT = (FORMAT csv)
        FILES = (URI = 'oci://mybucket@mynamespace/data_files/data_file_1.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

json: Only Newline Delimited JSON files are supported. Tables created with json format must only have a single column that conforms to the JSON data type.

The following example loads a JSON file into a table with a uniform resource identifier (URI).

mysql> CREATE EXTERNAL TABLE table_1(
        col_1 json)
        FILE_FORMAT = (FORMAT json)
        FILES = (URI = 'oci://mybucket@mynamespace/data_files/data_file_1.json');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

parquet: The Parquet data type.

The following example loads a Parquet file into a table with a uniform resource identifier (URI).

mysql> CREATE EXTERNAL TABLE table_1(
        col_1 int,
        col_2 int,
        col_3 int)
        FILE_FORMAT = (FORMAT parquet)
        FILES = (URI = 'oci://mybucket@mynamespace/data_files/data_file_1.parquet');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

avro: The Avro data type.

The following example loads an Avro file into a table with a uniform resource identifier (URI).

mysql> CREATE EXTERNAL TABLE table_1(
        col_1 int,
        col_2 int,
        col_3 int)
        FILE_FORMAT = (FORMAT avro)
        FILES = (URI = 'oci://mybucket@mynamespace/data_files/data_file_1.avro');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

csv: The default value is no compression. You can set compression to zip, gzip, or bzip2.

The following example loads a table from a file specified with a uniform resource identifier (URI) and uses a CSV file compressed in zip format.

mysql> CREATE EXTERNAL TABLE table_1(
        col_1 int,
        col_2 int,
        col_3 int)
        FILE_FORMAT = (FORMAT csv COMPRESSION 'zip')
        FILES = (URI = 'oci://mybucket@mynamespace/data_files/data_file_1.csv.zip');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

json: The default value is no compression. You can set compression to zip, gzip, or bzip2.

The following example loads a table from a file specified with a uniform resource identifier (URI) and uses a JSON file compressed in gzip format.

mysql> CREATE EXTERNAL TABLE table_1(
        col_1 json)
        FILE_FORMAT = (FORMAT json COMPRESSION 'gzip')
        FILES = (URI = 'oci://mybucket@mynamespace/data_files/data_file_1.json.gz');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

avro: The default value is auto. You cannot set any other options for COMPRESSION, as Avro files declare their compression format in the file metadata.

The following example loads a table from a file specified with a uniform resource identifier (URI) and uses an Avro file with the auto compression option.

mysql> CREATE EXTERNAL TABLE table_1(
        col_1 int,
        col_2 int,
        col_3 int)
        FILE_FORMAT = (FORMAT avro COMPRESSION 'auto')
        FILES = (URI = 'oci://mybucket@mynamespace/data_files/data_file_1.avro');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

parquet: The default value is auto. You cannot set any other options for COMPRESSION, as Parquet files declar their compression format in the file metadata.

The following example loads a table from a file specified with a uniform resource identifier (URI) and uses an Parquet file with the auto compression option.

mysql> CREATE EXTERNAL TABLE table_1(
        col_1 int,
        col_2 int,
        col_3 int)
        FILE_FORMAT = (FORMAT parquet COMPRESSION 'auto')
        FILES = (URI = 'oci://mybucket@mynamespace/data_files/data_file_1.parquet');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

Each compressed file must only contain a single original file. You cannot provide a compressed file with archives or folders with multiple files.

Files in zip format must be compressed with the Deflate algorithm.

If loading multiple files, all the files must be in the same compression format.

When loading multiple compressed files, it is best to prepare files of similar size, and in the range of 80-120 MB each. Otherwise, you may experience longer loading times.

DATE FORMAT: The date format, see: date_format. You can also set date formats for each column by using the EXTERNAL_FORMAT statement, which overrides the format in the table parameter.

TIME FORMAT: The time format, see: String and Numeric Literals in Date and Time Context. You can also set time formats for each column by using the EXTERNAL_FORMAT statement, which overrides the format in the table parameter.

DATETIME FORMAT: The datetime format, see: date_format. You can also set datetime formats for each column by using the EXTERNAL_FORMAT statement, which overrides the format in the table parameter.

The following example loads a table from a file specified with a uniform resource identifier (URI) and sets the DATE FORMAT, TIME FORMAT, and DATETIME FORMAT.

mysql> CREATE EXTERNAL TABLE table_1(
        col_1 date,
        col_2 time,
        col_3 timestamp)
        FILE_FORMAT = (FORMAT csv FIELDS DATE FORMAT '%W %M %Y' TIME FORMAT 'hh:mm:ss' DATETIME FORMAT '%Y-%m-%d %H:%i:%s')
        FILES = (URI = 'oci://mybucket@mynamespace/data_files/data_file_1.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

TERMINATED BY: Defines one or more characters used to enclose fields. The maximum field delimiter length is 64 characters. You can add any string as a custom_field_delimiter.

When FIELDS TERMINATED BY is set to auto, Lakehouse automatically detects the field delimiters.

The following field delimiters are detected automatically by Lakehouse:

The following example loads a table from a file specified with a uniform resource identifier (URI) and sets , and sets FIELDS TERMINATED BY and LINES TERMINATED BY.

mysql> CREATE EXTERNAL TABLE table_1(
        col_1 int,
        col_2 int,
        col_3 int)
        FILE_FORMAT = (FORMAT csv FIELDS TERMINATED BY '\r\n' LINES TERMINATED BY ';')
        FILES = (URI = 'oci://mybucket@mynamespace/data_files/data_file_1.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

[OPTIONALLY] ENCLOSED BY: Defines one or more characters used to enclose fields. The default is '\''. You can add any string as a custom_quotation_marks.

ESCAPED BY: Defines one or more characters used to escape special characters. The default is '\'. You might have to use an additional escape character depending on the client you use. You can add any string as a custom_escape_character.

The following example loads a table from a file specified with a uniform resource identifier (URI) and sets ESCAPED BY, and [OPTIONALLY] ENCLOSED BY.

mysql> CREATE EXTERNAL TABLE table_1(
        col_1 int,
        col_2 int,
        col_3 int)
        FILE_FORMAT = (FORMAT csv FIELDS ESCAPED BY '\\' OPTIONALLY ENCLOSED BY '\"')
        FILES = (URI = 'oci://mybucket@mynamespace/data_files/data_file_1.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

TERMINATED BY: Defines one or more characters used to delimit records. The maximum record delimiter length is 64 characters. You can add any string as a delimiter.

The default record delimiter for is '\n'.

For CSV files, if you set LINES TERMINATED BY to auto, Lakehouse automatically detects the record delimiters and composite record delimiters with field delimiters as prefixes.

The following record delimiters detected automatically by Lakehouse: