To set up resource principals, you configure the following parameters:

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.

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

The following example specifies a single file.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{
        "region": "us-ashburn-1",
        "namespace": "tenant_1",
        "bucket": "bucket_1",
        "name": "data_file_1.csv"}]
    }
  }]
}]';

To load multiple external files into one table, you can do one of the following:

The following example specifies all the files uploaded to a folder in the Object Storage bucket for one table.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{
        "region": "us-ashburn-1",
        "namespace": "tenant_1",
        "bucket": "bucket_1",
        "prefix": "data_files/"}]
    }
  }]
}]';

The following example specifies two files for one table.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{                         
        "region": "us-ashburn-1",
        "namespace": "tenant_1",
        "bucket": "bucket_1",
        "name": "data_file_1.csv"
    },
    {
        "region": "us-ashburn-1",
        "namespace": "tenant_1",
        "bucket": "bucket_1",
        "name": "data_file_2.csv"}]
    }
  }]
}]';

To specify multiple external files for multiple tables with one command, you can create the necessary number of tables items and do one of the following:

The following example specifies two files for two tables.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{
        "region": "us-ashburn-1",
        "namespace": "tenant_1",
        "bucket": "bucket_1",
        "name": "data_file_1.csv"}]
    }
  },
  {
    "table_name": "table_2",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{
        "region": "us-ashburn-1",
        "namespace": "tenant_1",
        "bucket": "bucket_1",
        "name": "data_file_2.csv"}]
    }
  }]
}]';

The following example specifies all the files in the identified folders for the tables.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{
        "region": "us-ashburn-1",
        "namespace": "tenant_1",
        "bucket": "bucket_1",
        "prefix": "data_files_1/"}]
    }
  },
  {
    "table_name": "table_2",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{
        "region": "us-ashburn-1",
        "namespace": "tenant_1",
        "bucket": "bucket_1",
        "prefix": "data_files_2/"}]
    }
  }]
}]';

You can add a pattern parameter to use regular expression to specify specific 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:

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. For example, it uses the files data_file_1.csv, data_file_2.csv, and data_file_3.csv to load the external table.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{
        "region": "us-ashburn-1",
        "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. For example, it uses the files data_file_a.csv, data_file_b.csv, and data_file_c.csv to load the external table.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{
        "region": "us-ashburn-1",
        "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 to load the external table.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{
        "region": "us-ashburn-1",
        "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 and specifying the files to load data into the table, learn how to Load Structured Data Using Auto Parallel Load.