跳到主要内容
跳到主要内容

分析 S3/HDFS 上的文件

通过 Table Value Function 功能,Doris 可以直接将对象存储或 HDFS 上的文件作为 Table 进行查询分析。并且支持自动的列类型推断。

提示

使用方式

更多使用方式可参阅 Table Value Function 文档:

  • S3:支持 S3 兼容的对象存储上的文件分析。

  • HDFS:支持 HDFS 上的文件分析。

这里我们通过 S3 Table Value Function 举例说明如何进行文件分析。

自动推断文件列类型

> DESC FUNCTION s3 (
"URI" = "http://127.0.0.1:9312/test2/test.snappy.parquet",
"s3.access_key"= "ak",
"s3.secret_key" = "sk",
"format" = "parquet",
"use_path_style"="true"
);
+---------------+--------------+------+-------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-------+---------+-------+
| p_partkey | INT | Yes | false | NULL | NONE |
| p_name | TEXT | Yes | false | NULL | NONE |
| p_mfgr | TEXT | Yes | false | NULL | NONE |
| p_brand | TEXT | Yes | false | NULL | NONE |
| p_type | TEXT | Yes | false | NULL | NONE |
| p_size | INT | Yes | false | NULL | NONE |
| p_container | TEXT | Yes | false | NULL | NONE |
| p_retailprice | DECIMAL(9,0) | Yes | false | NULL | NONE |
| p_comment | TEXT | Yes | false | NULL | NONE |
+---------------+--------------+------+-------+---------+-------+

这里我们定义了一个 S3 Table Value Function:

s3(
"URI" = "http://127.0.0.1:9312/test2/test.snappy.parquet",
"s3.access_key"= "ak",
"s3.secret_key" = "sk",
"format" = "parquet",
"use_path_style"="true")

其中指定了文件的路径、连接信息、认证信息等。

之后,通过 DESC FUNCTION 语法可以查看这个文件的 Schema。

可以看到,对于 Parquet 文件,Doris 会根据文件内的元信息自动推断列类型。

目前支持对 Parquet、ORC、CSV、JSON 格式进行分析和列类型推断。

CSV Schema

在默认情况下,对 CSV 格式文件,所有列类型均为 String。可以通过 csv_schema 属性单独指定列名和列类型。Doris 会使用指定的列类型进行文件读取。格式如下:

name1:type1;name2:type2;...

对于格式不匹配的列(比如文件中为字符串,用户定义为 int),或缺失列(比如文件中有 4 列,用户定义了 5 列),则这些列将返回 null。

当前支持的列类型为:

名称映射类型
tinyinttinyint
smallintsmallint
intint
bigintbigint
largeintlargeint
floatfloat
doubledouble
decimal(p,s)decimalv3(p,s)
datedatev2
datetimedatetimev2
charstring
varcharstring
stringstring
booleanboolean

示例:

s3 (
"URI" = "https://bucket1/inventory.dat",
"s3.access_key"= "ak",
"s3.secret_key" = "sk",
"format" = "csv",
"column_separator" = "|",
"csv_schema" = "k1:int;k2:int;k3:int;k4:decimal(38,10)",
"use_path_style"="true"
)

查询分析

你可以使用任意的 SQL 语句对这个文件进行分析

SELECT * FROM s3(
"URI" = "http://127.0.0.1:9312/test2/test.snappy.parquet",
"s3.access_key"= "ak",
"s3.secret_key" = "sk",
"format" = "parquet",
"use_path_style"="true")
LIMIT 5;
+-----------+------------------------------------------+----------------+----------+-------------------------+--------+-------------+---------------+---------------------+
| p_partkey | p_name | p_mfgr | p_brand | p_type | p_size | p_container | p_retailprice | p_comment |
+-----------+------------------------------------------+----------------+----------+-------------------------+--------+-------------+---------------+---------------------+
| 1 | goldenrod lavender spring chocolate lace | Manufacturer#1 | Brand#13 | PROMO BURNISHED COPPER | 7 | JUMBO PKG | 901 | ly. slyly ironi |
| 2 | blush thistle blue yellow saddle | Manufacturer#1 | Brand#13 | LARGE BRUSHED BRASS | 1 | LG CASE | 902 | lar accounts amo |
| 3 | spring green yellow purple cornsilk | Manufacturer#4 | Brand#42 | STANDARD POLISHED BRASS | 21 | WRAP CASE | 903 | egular deposits hag |
| 4 | cornflower chocolate smoke green pink | Manufacturer#3 | Brand#34 | SMALL PLATED BRASS | 14 | MED DRUM | 904 | p furiously r |
| 5 | forest brown coral puff cream | Manufacturer#3 | Brand#32 | STANDARD POLISHED TIN | 15 | SM PKG | 905 | wake carefully |
+-----------+------------------------------------------+----------------+----------+-------------------------+--------+-------------+---------------+---------------------+

Table Value Function 可以出现在 SQL 中,Table 能出现的任意位置。如 CTE 的 WITH 子句中,FROM 子句中。

这样,你可以把文件当做一张普通的表进行任意分析。

你也可以用过 CREATE VIEW 语句为 Table Value Function 创建一个逻辑视图。这样,你可以想其他视图一样,对这个 Table Value Function 进行访问、权限管理等操作,也可以让其他用户访问这个 Table Value Function。

CREATE VIEW v1 AS 
SELECT * FROM s3(
"URI" = "http://127.0.0.1:9312/test2/test.snappy.parquet",
"s3.access_key"= "ak",
"s3.secret_key" = "sk",
"format" = "parquet",
"use_path_style"="true");

DESC v1;

SELECT * FROM v1;

GRANT SELECT_PRIV ON db1.v1 TO user1;

数据导入

配合 INSERT INTO SELECT 语法,我们可以方便将文件导入到 Doris 表中进行更快速的分析:

// 1. 创建doris内部表
CREATE TABLE IF NOT EXISTS test_table
(
id int,
name varchar(50),
age int
)
DISTRIBUTED BY HASH(id) BUCKETS 4
PROPERTIES("replication_num" = "1");

// 2. 使用 S3 Table Value Function 插入数据
INSERT INTO test_table (id,name,age)
SELECT cast(id as INT) as id, name, cast (age as INT) as age
FROM s3(
"uri" = "http://127.0.0.1:9312/test2/test.snappy.parquet",
"s3.access_key"= "ak",
"s3.secret_key" = "sk",
"format" = "parquet",
"use_path_style" = "true");

注意事项

  1. 如果 S3 / hdfs tvf 指定的 uri 匹配不到文件,或者匹配到的所有文件都是空文件,那么 S3 / hdfs tvf 将会返回空结果集。在这种情况下使用DESC FUNCTION查看这个文件的 Schema,会得到一列虚假的列__dummy_col,可忽略这一列。

  2. 如果指定 tvf 的 format 为 csv,所读文件不为空文件但文件第一行为空,则会提示错误The first line is empty, can not parse column numbers, 这因为无法通过该文件的第一行解析出 schema。