MySQL :: HeatWave User Guide :: 4.3.8.3 Use Resource Principals to Create External Tables

HeatWave User Guide / ... / Use Resource Principals to Create External Tables

4.3.8.3 Use Resource Principals to Create External Tables

Review the following examples to see the different ways to use resource principals and Auto Parallel Load to specify files when creating external tables.

These examples use an input_list variable to set up the creation of the external table and the files to load the table. To create the external table, you need to run the HEATWAVE_LOAD command with the input_list. To learn more, see Load Structured Data Using Lakehouse Auto Parallel Load.

This topic contains the following sections:

Before You Begin

Review the requirements to Access Object Storage with Resource Principals.
Review how to Create an External Table.

Resource Principals Parameters

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.

Resource Principals Examples

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.

SET @input_list creates a session variable that stores all the parameters for creating the external file and loading the table with the specified files.
db_name identifies the database name to store the table. MySQL HeatWave automatically creates the database if it does not exist.
table_name sets the table name to store the data. MySQL HeatWave automatically creates the table if it does not exist.
engine_attribute defines the parameters of the specified files.
dialect defines the format options of the specified files.
The file parameters 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.

Note

If you are on MySQL 9.1.2 and earlier, you need to update dialect with the field delimiter and record delimiter parameters. As of MySQL 9.2.0, MySQL HeatWave Lakehouse can automatically detect these values. See Lakehouse External Table Syntax to learn more.

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

Specify a Single File

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"}]
    }
  }]
}]';

Specify Multiple Files for One External Table

To load multiple external files into one 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 file items as needed to identify 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 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"}]
    }
  }]
}]';

Specify Multiple Files for Multiple Tables

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:

Create folders as necessary 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 file items as needed to specify the files for the table.

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/"}]
    }
  }]
}]';

Specify Files with a Pattern

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:

Use \\. to escape a period if NO_BACKSLASH_ESCAPES is enabled.
Use \\\\. to escape a period if NO_BACKSLASH_ESCAPES is 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. 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\\\\.

What's Next

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.

PREV HOME UP NEXT