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

分析 S3/HDFS 上的文件 - 图1提示

使用方式

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

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

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

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

自动推断文件列类型

  1. > DESC FUNCTION s3 (
  2. "URI" = "http://127.0.0.1:9312/test2/test.snappy.parquet",
  3. "s3.access_key"= "ak",
  4. "s3.secret_key" = "sk",
  5. "format" = "parquet",
  6. "use_path_style"="true"
  7. );
  8. +---------------+--------------+------+-------+---------+-------+
  9. | Field | Type | Null | Key | Default | Extra |
  10. +---------------+--------------+------+-------+---------+-------+
  11. | p_partkey | INT | Yes | false | NULL | NONE |
  12. | p_name | TEXT | Yes | false | NULL | NONE |
  13. | p_mfgr | TEXT | Yes | false | NULL | NONE |
  14. | p_brand | TEXT | Yes | false | NULL | NONE |
  15. | p_type | TEXT | Yes | false | NULL | NONE |
  16. | p_size | INT | Yes | false | NULL | NONE |
  17. | p_container | TEXT | Yes | false | NULL | NONE |
  18. | p_retailprice | DECIMAL(9,0) | Yes | false | NULL | NONE |
  19. | p_comment | TEXT | Yes | false | NULL | NONE |
  20. +---------------+--------------+------+-------+---------+-------+

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

  1. s3(
  2. "URI" = "http://127.0.0.1:9312/test2/test.snappy.parquet",
  3. "s3.access_key"= "ak",
  4. "s3.secret_key" = "sk",
  5. "format" = "parquet",
  6. "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

示例:

  1. s3 (
  2. "URI" = "https://bucket1/inventory.dat",
  3. "s3.access_key"= "ak",
  4. "s3.secret_key" = "sk",
  5. "format" = "csv",
  6. "column_separator" = "|",
  7. "csv_schema" = "k1:int;k2:int;k3:int;k4:decimal(38,10)",
  8. "use_path_style"="true"
  9. )

查询分析

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

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

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

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

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

  1. CREATE VIEW v1 AS
  2. SELECT * FROM s3(
  3. "URI" = "http://127.0.0.1:9312/test2/test.snappy.parquet",
  4. "s3.access_key"= "ak",
  5. "s3.secret_key" = "sk",
  6. "format" = "parquet",
  7. "use_path_style"="true");
  8. DESC v1;
  9. SELECT * FROM v1;
  10. GRANT SELECT_PRIV ON db1.v1 TO user1;

数据导入

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

  1. // 1. 创建doris内部表
  2. CREATE TABLE IF NOT EXISTS test_table
  3. (
  4. id int,
  5. name varchar(50),
  6. age int
  7. )
  8. DISTRIBUTED BY HASH(id) BUCKETS 4
  9. PROPERTIES("replication_num" = "1");
  10. // 2. 使用 S3 Table Value Function 插入数据
  11. INSERT INTO test_table (id,name,age)
  12. SELECT cast(id as INT) as id, name, cast (age as INT) as age
  13. FROM s3(
  14. "uri" = "http://127.0.0.1:9312/test2/test.snappy.parquet",
  15. "s3.access_key"= "ak",
  16. "s3.secret_key" = "sk",
  17. "format" = "parquet",
  18. "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。