Review the following examples to see the different ways to use resource principals to specify files when creating external tables manually.
This topic contains the following sections:
- Review the requirements to Access Object Storage with Resource Principals. 
- Review how to Create an External Table Manually. 
To set up resource principals, you configure the following parameters:
- bucket_name: Buckets allow you to storage objects in a compartment. To learn more about buckets, see Object Storage Buckets in Oracle Cloud Infrastructure Documentation.
- namespace_name: This is the top-level container for all buckets and objects. To learn how to view your namespace, see Object Storage Namespaces in Oracle Cloud Infrastructure Documentation.
- 
region_name: You need to define the region identifier for your OCI tenancy. To view the list of available regions and region identifiers, see Regions and Availability Domains in Oracle Cloud Infrastructure Documentation.As of MySQL 9.2.1, if the region is not specified, the instance's associated region is used as the default region. Otherwise, the specified region is used. 
- name: Use this to specify an Object Storage file name.
- pattern: Use this to set a regular expression that defines a set of Object Storage files. The pattern follows the modified Modified ECMAScript regular expression grammar.
- prefix: Use this to define a set of Object Storage folders and files.
The following examples use these parameters to create the external tables and specify the files to load the tables with. Replace the values in the examples with your own.
- dialectdefines the format options of the specified files.
- 
The fileparameters define the resource principal details.- region: The region the tenant resides in.
- namespace: The name of the tenancy.
- bucket: The name of the Object Storage bucket.
- name: The name of the external file to load.
- prefix: The prefix for a set of Object Storage folders and files.
- pattern:The regular expression that defines a set of Object Storage files.
 
To review all syntax options for loading external files, see Lakehouse External Table Syntax.
The following example specifies a single file.
mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
                           "file": [{"region": "regionName", 
                                     "namespace": "tenant_1", 
                                     "bucket": "bucket_1", 
                                     "name": "data_file_1.csv"}]}';To specify multiple external files for one external table, you can do one of the following:
- Create a folder in the Object Storage bucket and upload the required files into that folder. See Managing Folders in an Object Storage Bucket in Oracle Cloud Infrastructure Documentation. 
- Create - fileitems as needed to specify the files to load into the table.
The following example specifies all the files uploaded to a folder in the Object Storage bucket for one external table.
mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
                           "file": [{"region": "regionName", 
                                     "namespace": "tenant_1", 
                                     "bucket": "bucket_1", 
                                     "prefix": "data_files/"}]}';The following example specifies two files for one external table.
mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
                           "file": [{"region": "regionName, 
                                     "namespace": "tenant_1", 
                                     "bucket": "bucket_1", 
                                     "name": "data_files/data_file_1.csv"}, 
                                    {"region": "regionName", 
                                     "namespace": "tenant_1", 
                                     "bucket": "bucket_1", 
                                     "name": "data_files/data_file_2.csv"}]}';
            You can add a pattern parameter to use
            regular expression to specify files.
          
The regular expression syntax requires certain characters to have an escape character.
            The escape character is the backslash character, and it is a
            reserved character in both JSON and MySQL. Therefore, it is
            necessary to escape the backslash character twice, and
            specify \\ for both JSON and MySQL.
          
            However, the regular expression escape sequence depends upon
            the NO_BACKSLASH_ESCAPES
            SQL mode:
          
- Use - \\.to escape a period if- NO_BACKSLASH_ESCAPESis enabled.
- Use - \\\\.to escape a period if- NO_BACKSLASH_ESCAPESis not enabled. The following examples use this sequence because it is the default mode.
See the following to learn more:
            The following example specifies all files in the
            data_files folder that have a numerical
            suffix of one or more digits to load into the external
            table. For example, it uses the files
            data_file_1.csv,
            data_file_2.csv, and
            data_file_3.csv.
          
mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
                           "file": [{"region": "regionName", 
                                     "namespace": "tenant_1", 
                                     "bucket": "bucket_1", 
                                     "pattern": "data_files/data_file_\\\\d+\\\\.csv"}]}';
            The following examples specifies all files in the
            data_files folder that have an
            alphabetical suffix of one or more lowercase characters to
            load into the external table. For example, it uses the files
            data_file_a.csv,
            data_file_b.csv, and
            data_file_c.csv.
          
mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
                           "file": [{"region": "regionName", 
                                     "namespace": "tenant_1", 
                                     "bucket": "bucket_1", 
                                     "pattern": "data_files/data_file_[a-z]+\\\\.csv"}]}';
            To update the previous example to include uppercase and
            lowercase characters, replace [a-z] with
            [A-Za-z].
          
            The following example specifies all files in the
            data_files folder that have a numerical
            suffix that end in 0 with one preceding
            digit. For example, it uses the files
            data_file_10.csv,
            data_file_20.csv, and
            data_file_30.csv.
          
mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
                           "file": [{"region": "regionName", 
                                     "namespace": "tenant_1", 
                                     "bucket": "bucket_1", 
                                     "pattern": "data_files/data_file_\\\\d0\\\\.csv"}]}';
            To update the previous example to load files with one or
            more digits preceding the 0, update
            \\\\d0\\\\ with
            \\\\d+0\\\\.
          
After successfully creating external tables manually and specifying the files to load data into the table, learn how to Load Structured Data Manually.