4.3.9.1 Create an External Table Manually

If you choose not to Create an External Table Using Lakehouse Auto Parallel Load, you can create the table manually.

This topic contains the following sections:

• Before You Begin

• Create External Table Manually Example

• What's Next

Before You Begin

• Prepare to create external tables by reviewing the following:

  • Additional MySQL HeatWave Lakehouse Requirements

  • MySQL HeatWave Lakehouse Privileges

  • Supported File Format

  • Access Object Storage

• Create the external tables that will ingest the loaded data by either using SQL syntax or JSON syntax.

  • CREATE TABLE Statement for SQL syntax. Also review relevant examples showing how to create external tables.

  • CREATE TABLE Statement for JSON syntax. Also review relevant examples showing how to create external tables.

Create External Table Manually Example

To demonstrate how to create an external table manually, the following example specifies a single file for the table by using an OCIFS URI.

The CSV file in this example is from Bank Marketing. To use this file, visit Bank Marketing and download the bank+marketing.zip file. Unzip the file, and then unzip the bank.zip file. Refer to the bank.csv file.

1. Prepare the files to load in the proper format. See Supported File Formats.

2. Upload the files to load into Object Storage. See Uploading an Object Storage Object to a Bucket in Oracle Cloud Infrastructure Documentation.

3. Select the method to specify the files for the external table: uniform resource identifier (URI), resource principal, or PAR. To learn more about each method, see the following:

   • Access Object Storage with Uniform Resource Identifiers and URI load examples.

   • Access Object Storage with Resource Principals and resource principal load examples.

   • Access Object Storage with Pre-Authenticated Requests and PAR load examples.

4. Connect to your MySQL HeatWave Database System.

5. In the terminal window, create and use the database to store the table.

   mysql> CREATE DATABASE bank_data;
   mysql> USE DATABASE bank_data;

6. Set up the CREATE EXTERNAL TABLE statement and the ENGINE_ATTRIBUTE options to specify the parameters needed to process the external files. In versions earlier than MySQL 9.4.0, you must use the CREATE TABLE statement and set ENGINE to lakehouse, and SECONDARY_ENGINE to rapid.

   Ensure that the table has the correct data type for each column. For this example, columns are defined according to the data in the bank.csv file. See the following to learn more:

   • CREATE TABLE

   • Data Types

   • Lakehouse External Table Syntax

   As of MySQL 9.4.0, you can use SQL syntax to set options for external tables. For earlier versions, you must set options using JSON syntax. To learn more, see Lakehouse External Table JSON Syntax and Lakehouse External Table SQL Syntax.

   The following example uses SQL syntax:

   mysql> CREATE EXTERNAL TABLE bank_marketing(
           age int,
           job varchar(255),
           marital varchar(255),
           education varchar(255),
           default1 varchar(255),
           balance float,
           housing varchar(255),
           loan varchar(255),
           contact varchar(255),
           day int,
           month varchar(255),
           duration float,
           campaign int,
           pdays float,
           previous float,
           poutcome varchar(255),
           y varchar(255)
       )
           FILE_FORMAT = (FORMAT csv HEADER ON)
           FILES = (URI = 'oci://mybucket@mynamespace/bucket_1/bank.csv');

   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.

   Where:

   • The CREATE EXTERNAL TABLE statement creates the table bank_marketing and automatically sets ENGINE to lakehouse, and SECONDARY_ENGINE to rapid.

   • Each column for the table is defined according to the bank.csv file.

   • FORMAT defines the format of the external file: csv.

   • HEADER identifies a header in the external file.

   • URI sets the URI link to access the file. Replace the link in the example with your own.

   If you are on a version earlier than MySQL 9.4.0, you must use the following JSON syntax:

   mysql> CREATE EXTERNAL TABLE bank_marketing(
           age int,
           job varchar(255),
           marital varchar(255),
           education varchar(255),
           default1 varchar(255),
           balance float,
           housing varchar(255),
           loan varchar(255),
           contact varchar(255),
           day int,
           month varchar(255),
           duration float,
           campaign int,
           pdays float,
           previous float,
           poutcome varchar(255),
           y varchar(255)
       )
           ENGINE_ATTRIBUTE='{"dialect": {"format": "csv", "has_header": true},
                              "file": [{ "uri": "oci://mybucket@mynamespace/bucket_1/bank.csv"}]}';

7. When you are ready to load the external table with data from the specified files, use the SECONDARY_LOAD clause in an ALTER TABLE statement. See Load Structured Data Manually.

What's Next

• Review different ways to specify files when creating external tables manually:

  • Using Uniform Resource Identifiers

  • Using Resource Principals

  • Using Pre-Authenticated Requests

• Review how to Load Structured Data Manually.