云数据库 SelectDB 版：文件分析

S3表函数

S3表函数可以让您像访问关系型数据库一样，读取并访问S3兼容的对象存储上的文件内容。目前支持CVS、CSV_with_names、CVS_with_names_and_types、Json、Parquet、ORC文件格式。

语法

s3(
  "uri" = "..",
  "s3.access_key" = "...",
  "s3.secret_key" = "...",
  "s3.region" = "...",
  "format" = "csv",
  "keyn" = "valuen",
  ...
);

参数说明

S3 TVF中的每一个参数都是一个"key"="value"键值对。常用参数如下：

使用示例

1. 读取并访问S3兼容的对象存储上的CSV格式文件，示例如下。

   SELECT * FROM s3("uri" = "http://127.0.0.1:9312/test2/student1.csv",
                   "s3.access_key"= "minioadmin",
                   "s3.secret_key" = "minioadmin",
                   "format" = "csv",
                   "use_path_style" = "true") ORDER BY c1;  

   配合DESC FUNCTION使用，示例如下。

   MySQL [(none)]> Desc FUNCTION s3("uri" = "http://127.0.0.1:9312/test2/student1.csv",
                    "s3.access_key"= "minioadmin",
                    "s3.secret_key" = "minioadmin",
                    "format" = "csv",
                    "use_path_style" = "true");

2. OSS的场合，需采用Virtual-hosted Style方式访问，示例如下。

   SELECT * FROM s3(
       "uri" = "http://example-bucket.oss-cn-beijing.aliyuncs.com/your-folder/file.parquet",
       "s3.access_key"= "ak",
       "s3.secret_key" = "sk",
       "format" = "parquet",
       "use_path_style" = "false");

3. 设置了"use_path_style"="true"的场合，将采用path style方式访问S3，示例如下。

   SELECT * FROM s3(
       "uri" = "https://endpoint/bucket/file/student.csv",
       "s3.access_key"= "ak",
       "s3.secret_key" = "sk",
       "format" = "csv",
       "use_path_style"="true");

4. 设置了"use_path_style"="false"的场合，将采用virtual-hosted style方式访问S3，示例如下。

   SELECT * FROM s3(
       "uri" = "https://bucket.endpoint/bucket/file/student.csv",
       "s3.access_key"= "ak",
       "s3.secret_key" = "sk",
       "format" = "csv",
       "use_path_style"="false");

HDFS表函数

HDFS表函数可以让您像访问关系表格式数据一样，读取并访问HDFS上的文件内容。目前支持CVS、CSV_with_names、CVS_with_names_and_types、Json、Parquet、ORC文件格式。

语法

hdfs(
  "uri" = "..",
  "fs.defaultFS" = "...",
  "hadoop.username" = "...",
  "format" = "csv",
  "keyn" = "valuen" 
  ...
);

参数说明

HDFS TVF中的每一个参数都是一个"key"="value"键值对。常用参数如下：

使用示例

读取并访问HDFS存储上的CSV格式文件，示例如下。

MySQL [(none)]> SELECT * FROM hdfs(
            "uri" = "hdfs://127.0.0.1:842/user/doris/csv_format_test/student.csv",
            "fs.defaultFS" = "hdfs://127.0.0.1:8424",
            "hadoop.username" = "doris",
            "format" = "csv");
            
-- 返回示例   
+------+---------+------+
| c1   | c2      | c3   |
+------+---------+------+
| 1    | alice   | 18   |
| 2    | bob     | 20   |
| 3    | jack    | 24   |
| 4    | jackson | 19   |
| 5    | liming  | 18   |
+------+---------+------+

读取并访问HA模式的HDFS存储上的CSV格式文件，示例如下。

MySQL [(none)]> SELECT * FROM hdfs(
            "uri" = "hdfs://127.0.0.1:842/user/doris/csv_format_test/student.csv",
            "fs.defaultFS" = "hdfs://127.0.0.1:8424",
            "hadoop.username" = "doris",
            "format" = "csv",
            "dfs.nameservices" = "my_hdfs",
            "dfs.ha.namenodes.my_hdfs" = "nn1,nn2",
            "dfs.namenode.rpc-address.my_hdfs.nn1" = "nanmenode01:8020",
            "dfs.namenode.rpc-address.my_hdfs.nn2" = "nanmenode02:8020",
            "dfs.client.failover.proxy.provider.my_hdfs" = "org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider");

-- 返回示例
+------+---------+------+
| c1   | c2      | c3   |
+------+---------+------+
| 1    | alice   | 18   |
| 2    | bob     | 20   |
| 3    | jack    | 24   |
| 4    | jackson | 19   |
| 5    | liming  | 18   |
+------+---------+------+

配合DECS FUNCTION使用，示例如下。

MySQL [(none)]> DECS FUNCTION hdfs(
            "uri" = "hdfs://127.0.0.1:8424/user/doris/csv_format_test/student_with_names.csv",
            "fs.defaultFS" = "hdfs://127.0.0.1:8424",
            "hadoop.username" = "doris",
            "format" = "csv_with_names");

使用方法

本章节将通过S3 TVF举例说明如何进行文件分析。

自动推断文件列类型​

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

> 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  |
+---------------+--------------+------+-------+---------+-------+

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

CSV Schema

默认情况下，CSV格式文件的所有列类型均会被SelectDB视为String。您可以通过csv_schema属性单独指定列名和列类型。SelectDB会使用指定的列类型进行文件读取。格式如下：

name1:type1;name2:type2;...

对于格式不匹配的列（例如文件中实际为字符串，您定义为int），或缺失列（比如文件中实际存在4列，您定义了5列），则这些列将返回null。当前支持的列类型为：

示例如下。

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语句，对TVF进行分析，示例如下。

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     |
+-----------+------------------------------------------+----------------+----------+-------------------------+--------+-------------+---------------+---------------------+

TVF可以出现在SQL中Table能出现的任意位置，如CTE的WITH子句中或者FROM子句中，您可以把文件当做一张普通的表进行任意分析。

您也可以通过CREATE VIEW语句为TVF创建一个逻辑视图。这样您可以像其他视图一样，对这个TVF进行访问、权限管理等操作，也可以让其他用户访问这个TVF。

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语法，您可以方便将文件导入到SelectDB表中进行更快速的分析，示例如下。

-- 1. 创建SelectDB内部表
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");

注意事项

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

• 如果指定TVF的format为CSV，所读文件不为空文件但文件第一行为空，则会产生报误The first line is empty, can not parse column numbers。