You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@flink.apache.org by vtygoss <vt...@126.com> on 2021/11/10 03:52:23 UTC
Re: How to express the datatype of sparksql collect_list(named_struct(...))inflinksql?
Hi, JING ZHANG!
I tried not to define the `annotation` of eval method as possible, but it didn't work with exception below. Then I found a way
to make it work by defining `annotation`.
- 1. is there a way to avoid to define `annotation`? It's very important for generality of UDF.
- 2. I am using Flink 1.12.0, which version do you use to make it work without `annotation`?
Best Regards!
```
def eval(data: java.util.Map[Row, Integer]):String = {
if (data == null || data.size() == 0) {
return ""
}
data.keySet().toArray().mkString(",")
}
```
在 2021年11月10日 11:33,JING ZHANG<be...@gmail.com> 写道:
Hi vtygoss,
I'm a little confused.
The UDF could already work well without defining `DataTypeHint `annotation.
Why do you define `DataTypeHint `annotation before input parameter of `eval `method?
Best,
JING ZHANG
vtygoss <vt...@126.com> 于2021年11月9日周二 下午8:17写道:
Hi, JING ZHANG!
Thanks for your many times of help.
I already try to use COLLECT(ROW(id, name)) and store the result with type String(for POC test). So I try to define an UDF, and the annotation of function eval must be defined as "MULTISET<ROW<...>>" as below, otherwise exception "..RAW/MAP expected. but MULTISET<ROW<`EXPR$0` STRING NOT NULL, `EXPR$1` STRING, `EXPR$2` STRING, `EXPR$3` STRING, `EXPR$4` STRING> NOT NULL> NOT NULL passed" thrown.
And i think this way of UDF's annotation maybe not a convenient way for general scenario. I can't define many UDFs for all RowData structures, such as Row<id>, Row<id, name>, Row<.....>
Is there any way to define the annotation for dynamic RowData structure?
Thanks for your suggestions again.
Best Regards!
```
def eval(@DataTypeHint("MULTISET<ROW<" +
"vital_sign_id STRING, cdt_vital_sign_index STRING, " +
"unit_name STRING, parameter_value STRING, measure_datetime STRING>" +
">") data: JMAP[Row, Integer]): String = {
if (data == null || data.size() == 0) {
return ""
}
data.keySet().toArray().mkString(",")
}
```
在 2021年11月8日 21:26,JING ZHANG<be...@gmail.com> 写道:
Hi Vtygoss,
You could try the following SQL:
```
select COLLECT(ROW(id, name)) as info
from table
group by ...;
```
In the above sql, the result type of `COLLECT(ROW(id, name))` is MULTISET<ROW>.
`CollectAggFunction` would store the data in a MapState. key is element type, represent the row value. value is Integer type, represents the count of row.
If you need to define a UDF which handles the result from `COLLECT(ROW(id, name))`, you could use Map<Row, Integer> as input parameter type.
The following code is a demo. Hope it helps.
tEnv.registerFunction("TestFunc", TestFunc)
tEnv.sqlQuery("select TestFunc(COLLECT(ROW(id, name))) as info from table group by ...")
....
@SerialVersionUID(1L)
object TestFunc extends ScalarFunction {
def eval(s: java.util.Map[Row, Integer]): String = s.keySet().mkString("\n")
}
Best regards,
JING ZHANG
vtygoss <vt...@126.com> 于2021年11月8日周一 下午7:00写道:
Hi, flink community!
I am working on migrating data production pipeline from SparkSQL to FlinkSQL(1.12.0). And i meet a problem about MULTISET<STRUCT<....>>.
```
Spark SQL
select COLLECT_LIST(named_struct('id', id, 'name', name)) as info
from table
group by ...;
```
- 1. how to express and store this data structure in flink?
I tried to express by MULTISET<ROW<id long, name string>> in FlinkSQL. But it seems that ORC / JSON / AVRO format cann't store this type.
- 2. How to read MULTISET<Row<id long, name string>> in FlinkSQL?
If i need to define a function, which type should be briged to for MultiSet<Row<id long, name string>>?
Is there any other way more convenient to solute this problem?
Thanks very much for your any suggestions or replies.
Best Regards!
Re: How to express the datatype of sparksql collect_list(named_struct(...))inflinksql?
Posted by JING ZHANG <be...@gmail.com>.
Hi, vtygoss
+1 on Timo's solutions.
I tried those two solutions in 1.12. Both them could work well.
Thanks @Timo for good suggestions.
Best,
JING ZHANG
Re: How to express the datatype of sparksql
collect_list(named_struct(...))inflinksql?
Posted by Timo Walther <tw...@apache.org>.
There are multiple ways of having a more generic UDF. I will use pseudo
code here:
// supports any input
def eval(@DataTypeHint(inputGroup = ANY) Object o): String = {
}
// or you use no annotations at all and simply define a strategy
// default input strategy is wildcard
def eval(Map[Row, Integer]): String = {
...
}
override def getTypeInference() = {
TypeInference.newBuilder()
.outputStrategy(callContext => Optional.of(DataTypes.STRING()))
.build();
}
You can also check the logical type using the callContext in
outputStrategy().
Regards,
Timo
On 10.11.21 07:25, JING ZHANG wrote:
> Hi, vtygoss
> I add the UDF in my latest email in `AggregateITCase` of Flink master
> branch, it works well.
> I need some time to find out why it doesn't work in branch 1.12.0. I
> would give you response as soon as possible.
>
> Best regards,
> JING zhang
>
>
> vtygoss <vtygoss@126.com <ma...@126.com>> 于2021年11月10日周三
> 上午11:53写道:
>
> Hi, JING ZHANG!
>
> I tried not to define the `annotation` of eval method as possible,
> but it didn't work with exception below. Then I found a way
>
> to make it work by defining `annotation`.
>
>
> - 1. is there a way to avoid to define `annotation`? It's very
> important for generality of UDF.
>
> - 2. I am using Flink 1.12.0, which version do you use to make it
> work without `annotation`?
>
>
>
> Best Regards!
>
>
> ```
>
> def eval(data: java.util.Map[Row, Integer]):String = {
> if (data ==null || data.size() ==0) {
> return ""
> }
> data.keySet().toArray().mkString(",")
> }
>
> ```
>
>
>
>
>
>
> 在 2021年11月10日 11:33,JING ZHANG<beyond1920@gmail.com
> <ma...@gmail.com>> 写道:
>
> Hi vtygoss,
> I'm a little confused.
> The UDF could already work well without defining `DataTypeHint
> `annotation.
> Why do you define `DataTypeHint `annotation before input parameter
> of `eval `method?
>
> Best,
> JING ZHANG
>
> vtygoss <vtygoss@126.com <ma...@126.com>> 于2021年11月9日周
> 二 下午8:17写道:
>
> Hi, JING ZHANG!
>
> Thanks for your many times of help.
>
>
> I already try to use COLLECT(ROW(id, name)) and store the result
> with type String(for POC test). So I try to define an UDF, and
> the annotation of function eval must be defined as
> "MULTISET<ROW<...>>" as below, otherwise exception "..RAW/MAP
> expected. but MULTISET<ROW<`EXPR$0` STRING NOT NULL, `EXPR$1`
> STRING, `EXPR$2` STRING, `EXPR$3` STRING, `EXPR$4` STRING> NOT
> NULL> NOT NULL passed" thrown.
>
>
> And i think this way of UDF's annotation maybe not a convenient
> way for general scenario. I can't define many UDFs for all
> RowData structures, such as Row<id>, Row<id, name>, Row<.....>
>
>
> Is there any way to define the annotation for dynamic RowData
> structure?
>
> Thanks for your suggestions again.
>
>
> Best Regards!
>
>
> ```
>
> def eval(@DataTypeHint("MULTISET<ROW<" +
> "vital_sign_id STRING, cdt_vital_sign_index STRING, " +
> "unit_name STRING, parameter_value STRING, measure_datetime
> STRING>" +
> ">") data: JMAP[Row, Integer]):String = {
> if (data ==null || data.size() ==0) {
> return ""
> }
> data.keySet().toArray().mkString(",")
> }
>
> ```
>
>
>
>
>
> 在 2021年11月8日 21:26,JING ZHANG<beyond1920@gmail.com
> <ma...@gmail.com>> 写道:
>
> Hi Vtygoss,
> You could try the following SQL:
> ```
>
> select COLLECT(ROW(id, name)) as info
>
> from table
>
> group by ...;
>
> ```
>
> In the above sql, the result type of `COLLECT(ROW(id, name))` is
> MULTISET<ROW>.
>
> `CollectAggFunction` would store the data in a MapState. key is
> element type, represent the row value. value is Integer type,
> represents the count of row.
>
>
> If you need to define a UDF which handles the result from
> `COLLECT(ROW(id, name))`, you could use Map<Row, Integer> as
> input parameter type.
>
> The following code is a demo. Hope it helps.
>
> tEnv.registerFunction("TestFunc", TestFunc)
> tEnv.sqlQuery("select TestFunc(COLLECT(ROW(id, name))) as info from table
> group by ...")
>
> ....
>
> @SerialVersionUID(1L)
> object TestFuncextends ScalarFunction {
> def eval(s: java.util.Map[Row, Integer]):String = s.keySet().mkString("\n")
> }
>
> Best regards,
>
> JING ZHANG
>
>
> vtygoss <vtygoss@126.com <ma...@126.com>> 于2021年11月8
> 日周一 下午7:00写道:
>
> Hi, flink community!
>
>
> I am working on migrating data production pipeline from
> SparkSQL to FlinkSQL(1.12.0). And i meet a problem about
> MULTISET<STRUCT<....>>.
>
>
> ```
>
> Spark SQL
>
>
> select COLLECT_LIST(named_struct('id', id, 'name', name)) as
> info
>
> from table
>
> group by ...;
>
>
> ```
>
>
> - 1. how to express and store this data structure in flink?
>
> I tried to express by MULTISET<ROW<id long, name string>> in
> FlinkSQL. But it seems that ORC / JSON / AVRO format cann't
> store this type.
>
> - 2. How to read MULTISET<Row<id long, name string>> in
> FlinkSQL?
>
> If i need to define a function, which type should be briged
> to for MultiSet<Row<id long, name string>>?
>
>
> Is there any other way more convenient to solute this problem?
>
> Thanks very much for your any suggestions or replies.
>
>
> Best Regards!
>
Re: How to express the datatype of sparksql collect_list(named_struct(...))inflinksql?
Posted by JING ZHANG <be...@gmail.com>.
Hi, vtygoss
I add the UDF in my latest email in `AggregateITCase` of Flink master
branch, it works well.
I need some time to find out why it doesn't work in branch 1.12.0. I would
give you response as soon as possible.
Best regards,
JING zhang
vtygoss <vt...@126.com> 于2021年11月10日周三 上午11:53写道:
> Hi, JING ZHANG!
>
> I tried not to define the `annotation` of eval method as possible, but it
> didn't work with exception below. Then I found a way
>
> to make it work by defining `annotation`.
>
>
> - 1. is there a way to avoid to define `annotation`? It's very important
> for generality of UDF.
>
> - 2. I am using Flink 1.12.0, which version do you use to make it work
> without `annotation`?
>
>
>
> Best Regards!
>
>
> ```
>
> def eval(data: java.util.Map[Row, Integer]):String = {
> if (data == null || data.size() == 0) {
> return ""
> }
> data.keySet().toArray().mkString(",")
> }
>
> ```
>
>
>
>
>
>
> 在 2021年11月10日 11:33,JING ZHANG<be...@gmail.com> 写道:
>
> Hi vtygoss,
> I'm a little confused.
> The UDF could already work well without defining `DataTypeHint `annotation.
> Why do you define `DataTypeHint `annotation before input parameter of
> `eval `method?
>
> Best,
> JING ZHANG
>
> vtygoss <vt...@126.com> 于2021年11月9日周二 下午8:17写道:
>
>> Hi, JING ZHANG!
>>
>> Thanks for your many times of help.
>>
>>
>> I already try to use COLLECT(ROW(id, name)) and store the result with
>> type String(for POC test). So I try to define an UDF, and the annotation
>> of function eval must be defined as "MULTISET<ROW<...>>" as below,
>> otherwise exception "..RAW/MAP expected. but MULTISET<ROW<`EXPR$0` STRING
>> NOT NULL, `EXPR$1` STRING, `EXPR$2` STRING, `EXPR$3` STRING, `EXPR$4`
>> STRING> NOT NULL> NOT NULL passed" thrown.
>>
>>
>> And i think this way of UDF's annotation maybe not a convenient way for
>> general scenario. I can't define many UDFs for all RowData structures, such
>> as Row<id>, Row<id, name>, Row<.....>
>>
>>
>> Is there any way to define the annotation for dynamic RowData structure?
>>
>> Thanks for your suggestions again.
>>
>>
>> Best Regards!
>>
>>
>> ```
>>
>> def eval(@DataTypeHint("MULTISET<ROW<" +
>> "vital_sign_id STRING, cdt_vital_sign_index STRING, " +
>> "unit_name STRING, parameter_value STRING, measure_datetime STRING>" +
>> ">") data: JMAP[Row, Integer]): String = {
>> if (data == null || data.size() == 0) {
>> return ""
>> }
>> data.keySet().toArray().mkString(",")
>> }
>>
>> ```
>>
>>
>>
>>
>>
>> 在 2021年11月8日 21:26,JING ZHANG<be...@gmail.com> 写道:
>>
>> Hi Vtygoss,
>> You could try the following SQL:
>> ```
>>
>> select COLLECT(ROW(id, name)) as info
>>
>> from table
>>
>> group by ...;
>>
>> ```
>>
>> In the above sql, the result type of `COLLECT(ROW(id, name))` is
>> MULTISET<ROW>.
>>
>> `CollectAggFunction` would store the data in a MapState. key is element
>> type, represent the row value. value is Integer type, represents the count
>> of row.
>>
>>
>> If you need to define a UDF which handles the result from
>> `COLLECT(ROW(id, name))`, you could use Map<Row, Integer> as input
>> parameter type.
>>
>> The following code is a demo. Hope it helps.
>>
>> tEnv.registerFunction("TestFunc", TestFunc)
>> tEnv.sqlQuery("select TestFunc(COLLECT(ROW(id, name))) as info from table group by ...")
>>
>> ....
>>
>> @SerialVersionUID(1L)
>> object TestFunc extends ScalarFunction {
>> def eval(s: java.util.Map[Row, Integer]): String = s.keySet().mkString("\n")
>> }
>>
>> Best regards,
>>
>> JING ZHANG
>>
>> vtygoss <vt...@126.com> 于2021年11月8日周一 下午7:00写道:
>>
>>> Hi, flink community!
>>>
>>>
>>> I am working on migrating data production pipeline from SparkSQL to
>>> FlinkSQL(1.12.0). And i meet a problem about MULTISET<STRUCT<....>>.
>>>
>>>
>>> ```
>>>
>>> Spark SQL
>>>
>>>
>>> select COLLECT_LIST(named_struct('id', id, 'name', name)) as info
>>>
>>> from table
>>>
>>> group by ...;
>>>
>>>
>>> ```
>>>
>>>
>>> - 1. how to express and store this data structure in flink?
>>>
>>> I tried to express by MULTISET<ROW<id long, name string>> in FlinkSQL.
>>> But it seems that ORC / JSON / AVRO format cann't store this type.
>>>
>>> - 2. How to read MULTISET<Row<id long, name string>> in FlinkSQL?
>>>
>>> If i need to define a function, which type should be briged to for
>>> MultiSet<Row<id long, name string>>?
>>>
>>>
>>> Is there any other way more convenient to solute this problem?
>>>
>>> Thanks very much for your any suggestions or replies.
>>>
>>>
>>> Best Regards!
>>>
>>