|
算术运算符
^ * % / + - ( )
select (int(_1)+int(_2))*int(_9) from s3object;
算术运算符
% modulo
select count cycles from s3object where cast (_1 as int)%2 = 0;
算术运算符
^ power-of
从 s3object 选择 cast（2^10，为 int）；
比较运算符
> < >= ⇐ == !=
select _1,_2 from s3object where (int(_1)+int(_3))>int(_5);
逻辑运算符
AND OR NOT
select count(*) from s3object where not (int(1)>123 and int(_5)<200);
逻辑运算符
is null
为表达式中的 null 返回 true/false
逻辑运算符和 NULL
is not null
为表达式中的 null 返回 true/false
逻辑运算符和 NULL
查看 null-handle，并观察使用 NULL 的逻辑操作的结果。查询返回
0
。
select count(*) from s3object where null and (3>2);
带有 NULL 的算术运算符
查看 null-handle，并观察使用 NULL 的二进制操作的结果。查询返回
0
。
select count(*) from s3object where (null+1) and (3>2);
与 NULL 进行比较
回顾空客户端并观察与 NULL 比较操作的结果。查询返回
0
。
select count(*) from s3object where (null*1.5) != 3;
select count(*) from s3object where _1 is null;
projection 列
与 if or then or else 类似
选择问题单 when (1+1==(2+1)*3) then 'case_1' when
4*3）==（12 then 'case_2' else 'case_else' end, age*2 from s3object;
projection 列
与 switch/case 类似
选择 case cast (_1 as int)+ 1 when 2 then "a" when 3 then "b" other "c" end from s3object;
逻辑运算符
coalesce
返回第一个非null 参数
select coalesce(nullif(5,5),nullif(1,1.0),age+12) from s3object;
逻辑运算符
如果两个参数都相等，则
nullif
返回 null，否则第一个参数为
nullif (1,1)=NULL nullif (null,1)=NULL nullif (2,1)=2
select nullif(cast(_1 as int),cast(_2 as int)) from s3object;
逻辑运算符
{expression} in ( .. {expression} ..)
select count cycles from s3object where 'ben' in (trim (_5),substring （_1,char_length (_1)-3,3),last_name）;
逻辑运算符
{expression} 和 {expression} 之间的 {expression}
select _1 from s3object where cast (_1 as int) between 800 和 900
;
select count cycles from stdin where substring (_3,char_length (_3),1) between "x" and trim (_1)和 substring (_3,char_length (_3)-1,1)= ":";
逻辑运算符
{expression} like {match-pattern}
select count (
) from s3object where first_name like '%de_'; select count (
) from s3object where _1 like "%a[r-s];
casting operator
select cast(123 as int)%2 from s3object;
casting operator
select cast(123.456 as float)%2 from s3object;
casting operator
select cast ('ABC0-9' as string),cast (substr ('ab12cd',3,2) as int)*4 from s3object;
casting operator
select cast (substring ('publish on 2007-01-01',12,10) as timestamp) from s3object;
非 AWS casting operator
select int(_1),int( 1.2 + 3.4) from s3object;
非 AWS casting operator
select float(1.2) from s3object;
非 AWS casting operator
select to_timestamp ('1999-10-10T12:23:44Z') from s3object;
select sum(int(_1)) from s3object;
select avg (cast (_1 as float)+ cast (_2 as int)) from s3object;
select avg(cast(_1 a float) + cast(_2 as int)) from s3object;
select max(float(_1)),min(int(_5)) from s3object;
select count(*) from s3object where (int(1)+int(_3))>int(_5);
时间戳功能
extract
select count cycles from s3object where extract (year from to_timestamp (_2))> 1950 and extract (year from to_timestamp (_1))< 1960;
时间戳功能
dateadd
select count (0) from s3object where date_diff (year,to_timestamp (_1),date_add （day,366,to_timestamp (_1))）= 1;
时间戳功能
datediff
select count (0) from s3object where date_diff (month,to_timestamp (_1),to_timestamp (_2))= 2;
时间戳功能
utcnow
select count (0) from s3object where date_diff (hour,utcnow （),date_add (day,1,utcnow （)））= 24
时间戳功能
to_string
select to_string (to_timestamp ("2009-09-17T17:56:06.234567Z"), "yyyyMMdd-H:m:s") from s3object;
字符串函数
select count(0) from s3object where int(substring(_1,1,4))>1950 and int(substring(_1,1,4))<1960;
字符串函数
来自负数的子字符串被视为第一个有效
select substring ("123456789" from -4) from s3object;
字符串函数
来自零的子字符串用于越界数字，就像(first,last)一样有效。
select substring ("123456789" from 0 for 100) from s3object;
字符串函数
select trim (' foobar ') from s3object;
字符串函数
select trim (trailing from ' foobar ') from s3object;
字符串函数
select trim (leading from ' foobar ') from s3object;
字符串函数
select trim (both '12' from '1112211foobar22211122') from s3object;
字符串函数
lower 或 upper
select lower ('ABcD12#$e') from s3object;
字符串函数
char_length, character_length
select count cycles from s3object where char_length (_3)=3;
复杂的查询
select sum (cast (_1 as int)),max (cast (_3 as int)), substring ('abcdefghijklm',(2-1)*3+sum （cast (_1 as int))/sum (cast (_1 as int))+1,(count （)+ count (0)）/count (0)）from s3object;
select int(_1) as a1, int(_2) as a2 , (a1+a2) as a3 from s3object where a3>100 and a3<300;
如需了解更多详细信息，请参阅
Amazon 的 S3 Select Object Content API
。
S3 选择支持以下功能：.Timestamp
-
to_timestamp(string)
-
-
描述
-
将字符串转换为时间戳基本类型。在字符串格式中，任何缺少的 'time' 值都会填充零；对于缺少的月份和天值，1 是默认值。'timezone' 格式是 +/-H:mm 或 Z，其中字母 'Z' 表示协调通用时间(UTC)。timezone 的值可以包括 - 12:00 和 +14:00。
目前，它可以将以下字符串格式转换为时间戳：
YYYY-MM-DDTHH:mm:ss.SSSSSS+/-HH:mm
YYYY-MM-DDTHH:mm:ss.SSSSSSZ
YYYY-MM-DDTHH:mm:ss+/-HH:mm
YYYY-MM-DDTHH:mm:ssZ
YYYY-MM-DDTHH:mm+/-HH:mm
YYYY-MM-DDTHH:mmZ
YYYY-MM-DDT
YYYYT
to_string (timestamp, format_pattern)
-
描述
-
以给定的输入字符串格式返回输入时间戳的字符串。
-
参数
-
|
格式
|
示例
|
描述
|
|
2 年数字.
4 年数字。
零添加的 4 位数年。
每年的零添加月数.
每年名称的缩写月。
年全月名称。
MMMMM
年前一个字母的月。与
to_timestamp
函数一起使用无效。
日期(1-31)。
每月零添加一天(01-31)。
每天的 AM 或 PM。
天中的小时(1-12)。
零添加小时的一天(01-12)。
天中的小时(0-23)。
每天的零添加小时(00-23)。
小时的分钟(0-59)。
小时的零添加分钟(00-59)。
第二分钟(0-59)。
分钟的零添加秒(00-59)。
第二部分（精度： 0.1，范围： 0.0-0.9）。
第二部分（精度： 0.01，范围： 0.0-0.99）。
第二部分（精度： 0.01，范围： 0.0-0.999）。
第二部分（精度： 0.001，范围： 0.0-0.9999）。
SSSSSS
123456
第二部分（最大精度）：1纳秒，范围： 0.0-0.999999）。
60000000
second 的 nano。
+07 或 Z
如果偏移为 0，则以小时或"Z"表示偏移量。
XX 或 XXXX
+0700 或 Z
如果偏移为 0，则以小时和"Z"表示偏移量。
XXX 或 XXXXX
+07:00 或 Z
如果偏移为 0，则以小时和"Z"表示偏移量。
以小时为单位进行偏移。
XX 或 xxxx
以小时和分钟为单位的偏移。
xxx 或 xxxxx
+07:00
以小时和分钟为单位的偏移。
|
-
extract (date-part from timestamp)
-
-
描述
-
根据 date-part 从输入时间戳中提取的整数。
year, month, week, day, hour, minute, second, timezone_hour, timezone_minute.
-
date_add(date-part ,integer,timestamp)
-
-
描述
-
返回时间戳，根据输入时间戳和日期部分的结果计算。
年，月份、天、小时、分钟、秒。
-
date_diff(date-part,timestamp,timestamp)
-
-
描述
-
返回整数，根据 date-part 在两个时间戳之间计算的结果。
年，月份、天、小时、分钟、秒。
-
utcnow()
-
聚合
-
count()
-
-
sum(expression)
-
-
avg(expression)
-
-
描述
-
如果出现某个条件，每行中返回一个平均表达式。
-
max(expression)
-
-
描述
-
如果出现某个条件，则返回与条件匹配的所有表达式的最大结果。
-
min(expression)
-
字符串
-
子字符串（字符串、from、for）
-
-
描述
-
为输入返回字符串从输入字符串中提取的字符串。
-
Char_length
-
-
描述
-
返回字符串中的多个字符。Character_length 也实现相同的目的。
-
trim ([[leading | trailing | both remove_chars] from] string)
-
-
描述
-
从目标字符串中修剪前/尾部（或两者）字符。默认值为空白字符。
-
Upper\lower
-
-
描述
-
将字符转换为大写或小写。
NULL
值缺失或未知，即
NULL
无法为任何算术操作生成一个值。这同样适用于算术比较，对
NULL
的任何比较都是未知的
NULL
。
表 3.4. NULL 用例
|
A is NULL
|
result (NULL=UNKNOWN)
|
|
A 或 False
A or True
A 或 A
A 和 False
False
A 和 True
A 和 A
如需了解更多详细信息，请参阅
Amazon 的 S3 Select Object Content API
。
别名编程结构是 s3 选择语言的重要组成部分，因为它可以为包含许多列或复杂查询的对象启用更好的编程。当解析带有别名结构的声明时，它会将别名替换为对右投射列和查询执行的引用，该引用将象任何其他表达式一样评估。别名维护结果缓存，如果别名被多次使用，则不会评估相同的表达式，并返回相同的结果，因为使用了缓存的结果。目前，红帽支持列别名。
select int(_1) as a1, int(_2) as a2 , (a1+a2) as a3 from s3object where a3>100 and a3<300;")
S3 选择引擎具有所有三个文件格式的解析器：CSV、Brquet 和 JSON，后者将命令划分为更多可处理组件，然后附加到定义每个组件的标签中。
带有输入序列化的 CSV 定义使用以下默认值：
将
{\n}'
用于 row-delimiter。
使用
{"}
括起内容。
使用
{\}
转义字符。
csv-header-info
会在 AWS-CLI 中显示
USE
时解析，这是包含该模式的输入对象中的第一行。目前，不支持输出序列化和压缩类型。S3 选择引擎具有 CSV 解析器，它解析 S3-objects：
每行以 row-delimiter 结尾。
field-separator 会分离相邻的列。
successive 字段分隔符定义
NULL
列。
quote-character 覆盖 field-separator；即，字段分隔符是引号之间的任何字符。
转义字符禁用除行分隔符之外的任何特殊字符。
以下是 CSV 解析规则的示例：
表 3.5. CSV 解析
|
功能
|
描述
|
输入(Tokens)
|
|
successive 字段分隔符
,,1,,2, =⇒ {null}{null}{1}{null}{2}{null}
QUOTE
quote 字符覆盖字段分隔符。
11,22,”a,b,c,d”,last =⇒ {11}{22}{“a,b,c,d”}{last}
Escape
转义字符覆盖了 元字符。
对象所有者
ID
和
DisplayName
的容器
没有关闭的引号；行分隔符是右行。
11,22,a=”str,44,55,66 =⇒ {11}{22}{a=”str,44,55,66}
CSV 标头信息
FileHeaderInfo 标签
USE 值表示第一行中的每个令牌都是列名称；IGNORE 值意味着跳过第一行。
如需了解更多详细信息，请参阅
Amazon 的 S3 Select Object Content API
。
Apache Parquet 是一个开源列式数据文件格式，旨在高效数据存储和检索。
S3 选择引擎的 Parquet 解析器解析 S3-objects，如下所示：
4-byte magic number "PAR1"
<Column 1 Chunk 1 + Column Metadata>
<Column 2 Chunk 1 + Column Metadata>
<Column N Chunk 1 + Column Metadata>
<Column 1 Chunk 2 + Column Metadata>
<Column 2 Chunk 2 + Column Metadata>
<Column N Chunk 2 + Column Metadata>
<Column 1 Chunk M + Column Metadata>
<Column 2 Chunk M + Column Metadata>
<Column N Chunk M + Column Metadata>
File Metadata
4-byte length in bytes of file metadata
4-byte magic number "PAR1"
在上例中，此表中有 N 列，被分成 M 行组。文件元数据包含所有列元数据启动位置。
元数据在数据后写入，以允许进行一次传递写入。
所有列块都可以在文件元数据中找到，之后应按顺序读取。
格式被明确设计为将元数据与数据分开。这允许将列分成多个文件，并有一个元数据文件引用多个 parquet 文件。
JSON 文档启用在没有限制的情况下的对象或数组中嵌套值。在 S3 选择引擎的 JSON 文档中查询特定值时，该值的位置通过
SELECT
语句中的路径指定。
JSON 文档的通用结构没有 CSV 和 Parquet 等行和列结构。相反，SQL 语句本身是查询 JSON 文档时定义行和列的 SQL 语句本身。
S3 选择引擎的 JSON 解析器解析 S3-objects，如下所示：
SELECT
语句中的
FROM
子句定义行边界。
JSON 文档中的一行与如何为 CSV 对象定义行的分隔符类似，以及如何使用行组定义 Parquet 对象的行
考虑以下示例：
"firstName": "Joe",
"lastName": "Jackson",
"gender": "male",
"age": "twenty"
"firstName": "Joe_2",
"lastName": "Jackson_2",
"gender": "male",
"age": 21
"phoneNumbers":
{ "type": "home1", "number": "734928_1","addr": 11 },
{ "type": "home2", "number": "734928_2","addr": 22 }
"key_after_array": "XXX",
"description" :
"main_desc" : "value_1",
"second_desc" : "value_2"
# the from-clause define a single row.
# _1 points to root object level.
# _1.age appears twice in Documnet-row, the last value is used for the operation.
query = "select _1.firstname,_1.key_after_array,_1.age+4,_1.description.main_desc,_1.description.second_desc from s3object[*].aa.bb.cc;";
expected_result = Joe_2,XXX,25,value_1,value_2
语句指示读取器搜索路径
aa.bb.cc
，并根据此路径的发生定义行边界。
当读取器遇到路径时，行开始，当读取器退出路径的最顶层部分时结束，本例中为对象
cc
。
3.5.5. 将 Ceph 对象网关与 Trino 集成
将 Ceph 对象网关与 Trino 集成，这是一个重要的实用程序，允许用户在 S3 对象上更快地运行 SQL 查询 9x。
以下是使用 Trino 的一些优点：
Trino 是一个完整的 SQL 引擎。
推送 S3 选择 Trino 引擎中的请求标识在服务器端运行具有成本效益的 SQL 语句的一部分。
使用 Ceph/S3select 的优化规则来提高性能。
利用 Red Hat Ceph Storage 可扩展性，将原始对象划分为多个等部分，执行 S3 选择请求并合并请求。
如果
s3select
语法在查询 trino 时无法正常工作，请使用 SQL 语法。
正在运行的 Red Hat Ceph Storage 集群安装有 Ceph 对象网关。
安装了 Docker 或 Podman。
bucket 已创建。
对象已上传。
部署 Trino 和 hive。
[cephuser@host01 ~]$ git clone https://github.com/ceph/s3select.git
[cephuser@host01 ~]$ cd s3select
使用 S3 端点、访问密钥和 secret 密钥修改
hms_trino.yaml
文件。
[cephuser@host01 s3select]$ cat container/trino/hms_trino.yaml
version: '3'
services:
image: galsl/hms:dev
container_name: hms
environment:
# S3_ENDPOINT the CEPH/RGW end-point-url
- S3_ENDPOINT=http://rgw_ip:port
- S3_ACCESS_KEY=abc
- S3_SECRET_KEY=abc
# the container starts with booting the hive metastore
command: sh -c '. ~/.bashrc; start_hive_metastore'
ports:
- 9083:9083
networks:
- trino_hms
trino:
image: trinodb/trino:405
container_name: trino
volumes:
# the trino directory contains the necessary configuration
- ./trino:/etc/trino
ports:
- 8080:8080
networks:
- trino_hms
networks:
trino_hm
使用 S3 端点、访问密钥和 secret 密钥修改
hive.properties
文件。
[cephuser@host01 s3select]$ cat container/trino/trino/catalog/hive.properties
connector.name=hive
hive.metastore.uri=thrift://hms:9083
#hive.metastore.warehouse.dir=s3a://hive/
hive.allow-drop-table=true
hive.allow-rename-table=true
hive.allow-add-column=true
hive.allow-drop-column=true
hive.allow-rename-column=true
hive.non-managed-table-writes-enabled=true
hive.s3select-pushdown.enabled=true
hive.s3.aws-access-key=abc
hive.s3.aws-secret-key=abc
# should modify per s3-endpoint-url
hive.s3.endpoint=http://rgw_ip:port
#hive.s3.max-connections=1
#hive.s3select-pushdown.max-connections=1
hive.s3.connect-timeout=100s
hive.s3.socket-timeout=100s
hive.max-splits-per-second=10000
hive.max-split-size=128MB
启动 Trino 容器，以集成 Ceph 对象网关。
[cephuser@host01 s3select]$ sudo docker compose -f ./container/trino/hms_trino.yaml up -d
验证集成。
[cephuser@host01 s3select]$ sudo docker exec -it trino /bin/bash
trino@66f753905e82:/$ trino
trino> create schema hive.csvbkt1schema;
trino> create table hive.csvbkt1schema.polariondatacsv(c1 varchar,c2 varchar, c3 varchar, c4 varchar, c5 varchar, c6 varchar, c7 varchar, c8 varchar, c9 varchar) WITH ( external_location = 's3a://csvbkt1/',format = 'CSV');
|
|
|
