iceberg 的使用记录

_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")))
                    );
                }
            }
        }
    }
}