文件分析

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

使用方式

更多使用方式可参阅 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");
  9. DESC v1;
  11. SELECT * FROM v1;
  13. 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");
  11. // 2. 使用 S3 Table Value Function 插入数据
  12. INSERT INTO test_table (id,name,age)
  13. SELECT cast(id as INT) as id, name, cast (age as INT) as age
  14. FROM s3(
  15.     "uri" = "http://127.0.0.1:9312/test2/test.snappy.parquet",
  16.     "s3.access_key"= "ak",
  17.     "s3.secret_key" = "sk",
  18.     "format" = "parquet",
  19.     "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。