能详细说明一下oceanbase外表如何创建与使用？
OceanBase的外表（External Table）是一个强大的功能，可以让你像查询普通数据库表一样，直接查询存储在外部系统中的数据文件，比如本地文件、HDFS或阿里云OSS等对象存储。
使用它的核心步骤是创建、查询和维护。下面我为你详细拆解。
第一步：创建外表
创建外表的语法是 CREATE EXTERNAL TABLE，你需要定义表结构、指定文件路径和格式。
- 定义列和映射
列定义：外表的列和普通表一样需要指定名称和数据类型，但不能定义任何约束（如 NOT NULL, PRIMARY KEY 等）。
列映射：这是关键点，外表支持两种方式映射到文件中的列：
默认映射：如果你的表列顺序和文件列顺序完全一致，直接按顺序定义列即可。例如，表的第一列对应文件的第一列。
手动映射：如果文件列顺序不匹配，或者你只想读取部分列，可以使用 metadata$filecol{N} 伪列来显式指定。N代表文件中列的位置，从1开始。注意：一旦使用手动映射，表中所有列都必须显式指定映射关系。
sql
– 示例：手动映射，表的第一列对应文件的第二列，表的第二列对应文件的第四列
CREATE EXTERNAL TABLE ext_t1 (
c1 INT AS (metadata$filecol2),
c2 INT AS (metadata$filecol4)
)
– … LOCATION, FORMAT 等选项
2. 指定 LOCATION（文件路径）
LOCATION 用来指定数据文件存放的路径，OceanBase支持两种格式：
本地路径（Local）：格式为 ‘file:///your/local/path’。路径必须是一个目录，外表会读取该目录下的所有文件。前提是必须设置系统变量 secure_file_priv 来允许数据库访问这个路径。
远程对象存储：支持阿里云OSS、腾讯云COS和S3。格式为 ‘{oss|cos|S3}://$ACCESS_ID:$ACCESS_KEY@$HOST/remote_file_path’，其中需要提供你的访问密钥（Access Key ID/Secret）和主机信息。
- 指定 FORMAT（文件格式）
FORMAT 用于告诉数据库如何解析文件，目前主要支持CSV格式。你可以在这里指定丰富的参数来应对各种CSV变体：
TYPE：目前只能为 ‘CSV’。
FIELD_DELIMITER：列分隔符，默认为 ‘\t’ (Tab)。
LINE_DELIMITER：行分隔符，默认为 ‘\n’。
FIELD_OPTIONALLY_ENCLOSED_BY：字段值的包裹符号，如双引号 ‘"’。
SKIP_HEADER：跳过文件头部的行数，常用于跳过标题行。
NULL_IF：指定哪些字符串将被视为 NULL 值。
ENCODING：文件字符集，默认为 UTF8MB4，也支持 GBK 等。
COMPRESSION：支持 GZIP, ZSTD 等压缩格式。
- 高级选项
PATTERN：如果你只想读取 LOCATION 目录下的部分文件，可以用正则表达式来过滤，例如 ‘PATTERN’ = ‘.*.csv’ 只读取CSV文件。
PARTITION BY：如果你的文件目录是按日期等维度分层的，可以创建外部分区表来提升查询性能。
第二步：查询与使用
创建好后，外表就像一个只读的普通表。你只能对它执行 SELECT 查询，不支持 INSERT、UPDATE、DELETE 等DML操作。
它的典型应用场景包括：
日志分析：直接查询OSS或本地服务器上的日志文件。
数据湖分析：作为查询数据湖中原始数据的入口。
数据导入：常与 INSERT INTO … SELECT … 结合使用，将外部数据快速导入到OceanBase内部表中，实现高性能的“湖仓加速”。
第三步：使用示例
这里有两个完整的创建示例，供你参考。
示例一：创建访问OSS上CSV文件的外表
sql
CREATE EXTERNAL TABLE employee (
emp_no int(120),
birthday date,
name varchar(120)
)
LOCATION = ‘oss://your-access-id:your-access-key@your-bucket.oss-cn-hangzhou.aliyuncs.com/path/to/data/’
FORMAT = (
TYPE = ‘CSV’,
FIELD_DELIMITER = ‘,’,
FIELD_OPTIONALLY_ENCLOSED_BY = ‘"’,
ENCODING = ‘utf8mb4’,
SKIP_HEADER = 1
)
PATTERN = ‘employee.*.csv’;
这个例子创建了一个名为 employee 的外表，它指向OSS上的一个目录，只读取以 employee 开头、.csv 结尾的文件，并假定这些文件是逗号分隔、字段用双引号包裹，且第一行是标题。
示例二：创建访问本地文件的CSV外表
sql
– 首先，确保secure_file_priv允许访问该路径
SET GLOBAL secure_file_priv = ‘/data/external_files/’;
– 然后创建外表
CREATE EXTERNAL TABLE students_csv (
id INT,
name VARCHAR(50),
grade INT
)
LOCATION = ‘file:///data/external_files/student_data/’
FORMAT = (
TYPE = ‘CSV’,
FIELD_DELIMITER = ‘,’,
LINE_DELIMITER = ‘\n’
);
这个例子创建了一个指向本地目录 /data/external_files/student_data/ 的外表，假设其中的CSV文件用逗号分隔列、换行符分隔行。
提醒：secure_file_priv 的修改需要较高的数据库权限，且通常只能通过本地Unix Socket连接进行设置，操作时请注意安全规范
1 个赞