_event_time: TIMESTAMP(6) ,到毫秒级
_event_time BETWEEN FROM_UNIXTIME(1679939825) and FROM_UNIXTIME(1680544625); # TIMESTAMP 的时间范围过滤方法
double_timestamp BETWEEN SYMMETRIC cast(1681117200 as double) and cast(1681120800 as double) # double 类型的时间范围过滤方法
select UNIX_TIMESTAMP(CAST(_event_time AS STRING)); # 转换为 BIGINT, 小心时区的设置;https://blog.csdn.net/qq_36908872/article/details/127111288
select TO_TIMESTAMP(CAST(_event_time AS STRING)); # TO_TIMESTAMP 计算出来的还是 TIMESTAMP 类型,不能 * int
select UNIX_TIMESTAMP(CAST(_event_time AS STRING)) * 1000 from xraydb.xrayMeta limit 5; # 正确,返回类型: BIGINT
select (UNIX_TIMESTAMP(CAST(obstacle._event_time AS STRING))) * 1000 + EXTRACT(MILLISECOND FROM _event_time); # 不丢失毫秒级数据
字段中如果和函数名冲突的话,要加上``,比如 position
SELECT _xray_id, current_perception_pose_3d, _event_time, T.intent, T.perception_obstacle, T.perception_obstacle.type, T.perception_obstacle.sub_type FROM xrayData.\`@xurban@prediction\`, LATERAL TABLE(TableFunc(prediction_obstacle)) as T; # 把 表中的 array 展开
public class QueryUDF {
public static class TableFunc extends TableFunction<Row> {
@FunctionHint(
input = @DataTypeHint(QueryUDFDataTypeHint.TableFuncInputKmsg),
output = @DataTypeHint(QueryUDFDataTypeHint.TableFuncOutputKmsg)
)
@FunctionHint(
input = @DataTypeHint(QueryUDFDataTypeHint.TableFuncInputPerceptionObstacle),
output = @DataTypeHint(QueryUDFDataTypeHint.TableFuncOutputPerceptionObstacle)
)
public void eval(Row[] values) {
for (Row value: values) {
Set<String> fieldsNames = value.getFieldNames(true);
if (fieldsNames.contains("message")) {
collect(Row.of(value.getField("message")));
} else {
Row obstacle = (Row) value.getField("perception_obstacle");
Row velocity = (Row) obstacle.getField("velocity");
Row acceleration_filtered = (Row) obstacle.getField("acceleration_filtered");
Row position = (Row) obstacle.getField("position");
Row intent = (Row) value.getField("intent");
collect(Row.of(
Row.of(
obstacle.getField("unique_id"), obstacle.getField("type"),
obstacle.getField("sub_type"),
Row.of(velocity.getField("x"), velocity.getField("y"), velocity.getField("z")),
Row.of(acceleration_filtered.getField("x"), acceleration_filtered.getField("y"), acceleration_filtered.getField("z")),
Row.of(position.getField("x"), position.getField("y"), position.getField("z")),
obstacle.getField("theta")),
Row.of(intent.getField("type")))
);
}
}
}
}
}