s3
S3
Name
s3
description
S3表函数(table-valued-function,tvf),可以让用户像访问关系表格式数据一样,读取并访问 S3 兼容的对象存储上的文件内容。目前支持csv/csv_with_names/csv_with_names_and_types/json/parquet/orc
文件格式。
syntax
s3(
"uri" = "..",
"access_key" = "...",
"secret_key" = "...",
"format" = "csv",
"keyn" = "valuen",
...
);
参数说明
S3 tvf中的每一个参数都是一个 "key"="value"
对。
访问S3相关参数:
uri
: (必填) 访问S3的uri,S3表函数会根据use_path_style
参数来决定是否使用 path style 访问方式,默认为 virtual-hosted style 方式access_key
: (必填)secret_key
: (必填)use_path_style
:(选填) 默认为false
。S3 SDK 默认使用 virtual-hosted style 方式。但某些对象存储系统可能没开启或没支持virtual-hosted style 方式的访问,此时我们可以添加 use_path_style 参数来强制使用 path style 方式。比如minio
默认情况下只允许path style
访问方式,所以在访问minio时要加上use_path_style=true
。
注意:uri目前支持三种schema:http://, https:// 和 s3://
- 如果使用http://或https://, 则会根据 'use_path_style' 参数来决定是否使用'path style'方式访问s3
- 如果使用s3://, 则都使用 'virtual-hosted style' 方式访问s3, 'use_path_style'参数无效。
详细使用案例可以参考最下方 Best Practice。
文件格式参数:
format
:(必填) 目前支持csv/csv_with_names/csv_with_names_and_types/json/parquet/orc
column_separator
:(选填) 列分割符, 默认为,
。line_delimiter
:(选填) 行分割符,默认为\n
。下面6个参数是用于json格式的导入,具体使用方法可以参照:Json Load
read_json_by_line
: (选填) 默认为"true"
strip_outer_array
: (选填) 默认为"false"
json_root
: (选填) 默认为空json_paths
: (选填) 默认为空num_as_string
: (选填) 默认为false
下面2个参数是用于csv格式的导入fuzzy_parse
: (选填) 默认为false
trim_double_quotes
: 布尔类型,选填,默认值为false
,为true
时表示裁剪掉 csv 文件每个字段最外层的双引号skip_lines
: 整数类型,选填,默认值为0,含义为跳过csv文件的前几行。当设置format设置为csv_with_names
或csv_with_names_and_types
时,该参数会失效
Example
读取并访问 S3 兼容的对象存储上的csv格式文件
select * from s3("uri" = "http://127.0.0.1:9312/test2/student1.csv",
"ACCESS_KEY"= "minioadmin",
"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",
"ACCESS_KEY"= "minioadmin",
"SECRET_KEY" = "minioadmin",
"format" = "csv",
"use_path_style" = "true");
Keywords
s3, table-valued-function, tvf
Best Practice
不同url schema的写法 http:// 、https:// 使用示例:
// 注意URI bucket写法以及use_path_style参数设置,http同理。
// 由于设置了"use_path_style"="true", 所以将采用path style方式访问s3。
select * from s3(
"URI" = "https://endpoint/bucket/file/student.csv",
"ACCESS_KEY"= "ak",
"SECRET_KEY" = "sk",
"FORMAT" = "csv",
"use_path_style"="true");
// 注意URI bucket写法以及use_path_style参数设置,http同理。
// 由于设置了"use_path_style"="false", 所以将采用virtual-hosted style方式访问s3。
select * from s3(
"URI" = "https://bucket.endpoint/file/student.csv",
"ACCESS_KEY"= "ak",
"SECRET_KEY" = "sk",
"FORMAT" = "csv",
"use_path_style"="false");
// 阿里云oss和腾讯云cos采用virtual-hosted style方式访问s3。
// OSS
select * from s3(
"URI" = "http://example-bucket.oss-cn-beijing.aliyuncs.com/your-folder/file.parquet",
"ACCESS_KEY" = "ak",
"SECRET_KEY" = "sk",
"REGION" = "oss-cn-beijing",
"FORMAT" = "parquet",
"use_path_style" = "false");
// COS
select * from s3(
"URI" = "https://example-bucket.cos.ap-hongkong.myqcloud.com/your-folder/file.parquet",
"ACCESS_KEY" = "ak",
"SECRET_KEY" = "sk",
"REGION" = "ap-hongkong",
"FORMAT" = "parquet",
"use_path_style" = "false");
s3:// 使用示例:
// 注意URI bucket写法, 无需设置use_path_style参数。
// 将采用virtual-hosted style方式访问s3。
select * from s3(
"URI" = "s3://bucket.endpoint/file/student.csv",
"ACCESS_KEY"= "ak",
"SECRET_KEY" = "sk",
"FORMAT" = "csv");
csv foramt 由于S3 table-valued-function事先并不知道table schema,所以会先读一遍文件来解析出table schema。
csv
格式: S3 table-valued-function 读取S3上的文件并当作csv文件来处理,读取文件中的第一行用于解析table schema。文件第一行的列个数n
将作为table schema的列个数,table schema的列名则自动取名为c1, c2, ..., cn
,列类型都设置为 String
, 举例:
student1.csv文件内容为:
1,ftw,12
2,zs,18
3,ww,20
使用S3 tvf
MySQL [(none)]> select * from s3("uri" = "http://127.0.0.1:9312/test2/student1.csv",
-> "ACCESS_KEY"= "minioadmin",
-> "SECRET_KEY" = "minioadmin",
-> "format" = "csv",
-> "use_path_style" = "true") order by c1;
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| 1 | ftw | 12 |
| 2 | zs | 18 |
| 3 | ww | 20 |
+------+------+------+
可以配合 desc function S3()
来查看table schema
MySQL [(none)]> Desc function s3("uri" = "http://127.0.0.1:9312/test2/student1.csv",
-> "ACCESS_KEY"= "minioadmin",
-> "SECRET_KEY" = "minioadmin",
-> "format" = "csv",
-> "use_path_style" = "true");
+-------+------+------+-------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-------+---------+-------+
| c1 | TEXT | Yes | false | NULL | NONE |
| c2 | TEXT | Yes | false | NULL | NONE |
| c3 | TEXT | Yes | false | NULL | NONE |
+-------+------+------+-------+---------+-------+
csv_with_names foramt
csv_with_names
格式:解析文件的第一行作为table schema的列个数和列名,列类型则都设置为 String
, 举例:
student_with_names.csv文件内容为
id,name,age
1,ftw,12
2,zs,18
3,ww,20
使用S3 tvf
MySQL [(none)]> select * from s3("uri" = "http://127.0.0.1:9312/test2/student_with_names.csv",
-> "ACCESS_KEY"= "minioadmin",
-> "SECRET_KEY" = "minioadmin",
-> "format" = "csv_with_names",
-> "use_path_style" = "true") order by id;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | ftw | 12 |
| 2 | zs | 18 |
| 3 | ww | 20 |
+------+------+------+
同样配合desc function S3()
可查看table schema
MySQL [(none)]> Desc function s3("uri" = "http://127.0.0.1:9312/test2/student_with_names.csv",
-> "ACCESS_KEY"= "minioadmin",
-> "SECRET_KEY" = "minioadmin",
-> "format" = "csv_with_names",
-> "use_path_style" = "true");
+-------+------+------+-------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-------+---------+-------+
| id | TEXT | Yes | false | NULL | NONE |
| name | TEXT | Yes | false | NULL | NONE |
| age | TEXT | Yes | false | NULL | NONE |
+-------+------+------+-------+---------+-------+
csv_with_names_and_types foramt
csv_with_names_and_types
格式:目前暂不支持从csv文件中解析出column type。使用该format时,S3 tvf会解析文件的第一行作为table schema的列个数和列名,列类型则都设置为 String,同时将忽略该文件的第二行。
student_with_names_and_types.csv文件内容为
id,name,age
INT,STRING,INT
1,ftw,12
2,zs,18
3,ww,20
使用S3 tvf
MySQL [(none)]> select * from s3("uri" = "http://127.0.0.1:9312/test2/student_with_names_and_types.csv",
-> "ACCESS_KEY"= "minioadmin",
-> "SECRET_KEY" = "minioadmin",
-> "format" = "csv_with_names_and_types",
-> "use_path_style" = "true") order by id;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | ftw | 12 |
| 2 | zs | 18 |
| 3 | ww | 20 |
+------+------+------+
同样配合desc function S3()
可查看table schema
MySQL [(none)]> Desc function s3("uri" = "http://127.0.0.1:9312/test2/student_with_names_and_types.csv",
-> "ACCESS_KEY"= "minioadmin",
-> "SECRET_KEY" = "minioadmin",
-> "format" = "csv_with_names_and_types",
-> "use_path_style" = "true");
+-------+------+------+-------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-------+---------+-------+
| id | TEXT | Yes | false | NULL | NONE |
| name | TEXT | Yes | false | NULL | NONE |
| age | TEXT | Yes | false | NULL | NONE |
+-------+------+------+-------+---------+-------+
json foramt
json
格式:json格式涉及到较多的可选参数,各个参数的意义可以参考:Json Load。 S3 tvf查询json格式文件时根据 json_root
和 jsonpaths
参数定位到一个json对象,将该对象的中的key
作为table schema的列名,列类型都设置为String。举例:
data.json文件
[{"id":1, "name":"ftw", "age":18}]
[{"id":2, "name":"xxx", "age":17}]
[{"id":3, "name":"yyy", "age":19}]
使用S3 tvf查询
MySQL [(none)]> select * from s3(
"URI" = "http://127.0.0.1:9312/test2/data.json",
"ACCESS_KEY"= "minioadmin",
"SECRET_KEY" = "minioadmin",
"Format" = "json",
"strip_outer_array" = "true",
"read_json_by_line" = "true",
"use_path_style"="true");
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | ftw | 18 |
| 2 | xxx | 17 |
| 3 | yyy | 19 |
+------+------+------+
MySQL [(none)]> select * from s3(
"URI" = "http://127.0.0.1:9312/test2/data.json",
"ACCESS_KEY"= "minioadmin",
"SECRET_KEY" = "minioadmin",
"Format" = "json",
"strip_outer_array" = "true",
"jsonpaths" = "[\"$.id\", \"$.age\"]",
"use_path_style"="true");
+------+------+
| id | age |
+------+------+
| 1 | 18 |
| 2 | 17 |
| 3 | 19 |
+------+------+
parquet foramt
parquet
格式:S3 tvf支持从parquet文件中解析出table schema的列名、列类型。举例:
MySQL [(none)]> select * from s3(
"URI" = "http://127.0.0.1:9312/test2/test.snappy.parquet",
"ACCESS_KEY"= "minioadmin",
"SECRET_KEY" = "minioadmin",
"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 |
+-----------+------------------------------------------+----------------+----------+-------------------------+--------+-------------+---------------+---------------------+
MySQL [(none)]> desc function s3(
"URI" = "http://127.0.0.1:9312/test2/test.snappy.parquet",
"ACCESS_KEY"= "minioadmin",
"SECRET_KEY" = "minioadmin",
"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 |
+---------------+--------------+------+-------+---------+-------+
orc foramt
orc
格式:和parquet
format使用方法一致, 将format
参数设置为orc。
MySQL [(none)]> select * from s3(
"URI" = "http://127.0.0.1:9312/test2/test.snappy.orc",
"ACCESS_KEY"= "minioadmin",
"SECRET_KEY" = "minioadmin",
"Format" = "orc",
"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 |
+-----------+------------------------------------------+----------------+----------+-------------------------+--------+-------------+---------------+---------------------+
uri包含通配符
uri可以使用通配符来读取多个文件。注意:如果使用通配符要保证各个文件的格式是一致的(尤其是csv/csv_with_names/csv_with_names_and_types算做不同的格式),S3 tvf用第一个文件来解析出table schema。 如下两个csv文件:
// file1.csv
1,aaa,18
2,qqq,20
3,qwe,19
// file2.csv
5,cyx,19
6,ftw,21
可以在uri上使用通配符来导入。
MySQL [(none)]> select * from s3(
"URI" = "http://127.0.0.1:9312/test2/file*.csv",
"ACCESS_KEY"= "minioadmin",
"SECRET_KEY" = "minioadmin",
"ForMAT" = "csv",
"use_path_style"="true");
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| 1 | aaa | 18 |
| 2 | qqq | 20 |
| 3 | qwe | 19 |
| 5 | cyx | 19 |
| 6 | ftw | 21 |
+------+------+------+
配合 insert into
和 cast
使用 S3
tvf
// 创建doris内部表
CREATE TABLE IF NOT EXISTS ${testTable}
(
id int,
name varchar(50),
age int
)
COMMENT "my first table"
DISTRIBUTED BY HASH(id) BUCKETS 32
PROPERTIES("replication_num" = "1");
// 使用S3插入数据
insert into ${testTable} (id,name,age)
select cast (id as INT) as id, name, cast (age as INT) as age
from s3(
"uri" = "${uri}",
"ACCESS_KEY"= "${ak}",
"SECRET_KEY" = "${sk}",
"format" = "${format}",
"strip_outer_array" = "true",
"read_json_by_line" = "true",
"use_path_style" = "true");