CREATE EXTERNAL TABLE

Grammar Description

An external table refers to a table that is not in the database. It is a text file divided into a certain format on the operating system, or other types of tables. For MatrixOne, it is similar to a view. You can query and other operations in the database like a view, but external tables only have a table structure in the database, and the data is stored in the operating system.

This document will tell you how to build tables outside the MatrixOne database.

Grammar Structure

Common syntax

> CREATE EXTERNAL TABLE [IF NOT EXISTS] [db.]table_name;
(
    name1 type1,
    name2 type2,
    ...
)

Syntax Example

## Create an exterior pointing to the local file (specify the compression format)
create external table t(...) localfile{"filepath"='<string>', "compression"='<string>'} FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';

## Create an exterior pointing to the local file (if the compression format is not specified, it will be auto format, and the format of the file will be automatically checked)
create external table t(...) localfile{"filepath"='<string>'} FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';


## Create an exterior pointing to an S3 file (specify the compression format)
create external table t(...) URL s3option{"endpoint"='<string>', "access_key_id"='<string>', "secret_access_key"='<string>', "bucket"='<string>', "filepath"='<string>', "region"='<string>', "compression"='<string>'} FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';

## Create an exterior pointing to an S3 file (if the compression format is not specified, it will be auto format, and the format of the file will be automatically checked)
create external table t(...) URL s3option{"endpoint"='<string>', "access_key_id"='<string>', "secret_access_key"='<string>', "bucket"='<string>', "filepath"='<string>', "region"='<string>'} FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';

Syntax Description

Parameter description

Parameters	Description
endpoint	The endpoint is the URL that serves as the entry point for an AWS Web service. For example: s3.us-west-2.amazonaws.com
access_key_id	Access key ID of S3
secret_access_key	S3's Secret access key
bucket	Bucket to access
filepath	Relative path to access the file
region	Region where s3 is located
compression	The compressed format of S3 file, empty means non-compressed file, and the supported fields or compression formats are "auto", "none", "gzip", "bzip2", "flate", "zlib", "lz4"
auto	Compressed format, indicating that the file's compression format is automatically checked by the file suffix name
none	Compressed format, represented as non-compressed format, the rest represent the compressed format of the file

Example

create external table ex_table_cpk(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255))infile{"filepath"='$resources/external_table_file/cpk_table_1.csv'};

limit

Currently, MatrixOne only supports select operations on external tables, and does not support inserting data on external tables using delete, insert, and update.