Re: SQL + flatten (or .*) quality docs location?
Hi Stu, thanks for reporting back. I tried to reproduce the "method flatten() not found" error but did not succeed. It would be great if you could open a JIRA issue and describe how to reproduce the problem. Thank you, Fabian 2017-03-17 16:42 GMT+01:00 Stu Smith: > Thank you! Just in case someone else stumbles onto this, I figured what > was giving me trouble. > The object I wanted to flattened happened to be null at times, at which > point it would error out and give some exception along the lines of: > > "method flatten() not found" > > (Sorry, I'll try to follow up with the actual trace to help people with > their searches later) > > which made it sound more like I was using it incorrectly altogether, > rather than that the object was null... I think even just letting NPE would > have been a little more helpful... > > I figured it out, btw, but trying equivalent programs in Flink and Esper > (doing an eval of both). Esper gave a clearer error, and then I went back > and fixed the flink program. > > Take care, > -stu > > On Thu, Mar 16, 2017 at 3:27 AM, Fabian Hueske wrote: > >> Hi Stu, >> >> there is only one page of documentation for the Table API and SQL [1]. >> I agree the structure could be improved and split into multiple pages. >> >> Regarding the flatting of a Pojo have a look at the "Built-In Functions" >> section [2]. >> If you select "SQL" and head to the "Value access functions", you'll find >> >> > tableName.compositeType.* : Converts a Flink composite type (such as >> Tuple, POJO, etc.) and all of its direct subtypes into a flat >> representation where every subtype is a separate field. >> >> >> The following program works returns the correct result: >> >> // POJO definition >> class MyPojo(var x: Int, var y: Int) { >> def this() = this(0, 0) >> } >> >> // SQL query >> val env = ExecutionEnvironment.getExecutionEnvironment >> val tEnv = TableEnvironment.getTableEnvironment(env, config) >> >> val ds = env.fromElements((0, new MyPojo(1, 2)), (1, new MyPojo(2, 3)), >> (2, new MyPojo(3, 4)) ) >> tEnv.registerDataSet("Pojos", ds, 'id, 'pojo) >> >> val result = tEnv.sql("SELECT id, Pojos.pojo.* FROM Pojos") // you need >> to include the table name to flatten a Pojo >> >> val results = result.toDataSet[Row].collect() >> println(results.mkString("\n")) >> >> // Result >> 0,1,2 >> 1,2,3 >> 2,3,4 >> >> Best, Fabian >> >> [1] https://ci.apache.org/projects/flink/flink-docs-release-1.2/ >> dev/table_api.html >> [2] https://ci.apache.org/projects/flink/flink-docs-release-1.2/ >> dev/table_api.html#built-in-functions >> >> 2017-03-15 21:31 GMT+01:00 Stu Smith : >> >>> The documentation seems to indicate that there is a flatten method >>> available in the sql language interface (in the table of available >>> methods), or, alternatively using the '*' character somehow (in the text >>> above the table). >>> >>> Yet I cannot flatten a POJO type, nor can I find any sufficient >>> documentation in the official docs, searching the mailing list via >>> markmail, looking through the examples in the source, or looking for >>> through the SQL tests in the source. >>> >>> Can someone point me to the correct location for some solid flink SQL >>> examples and docs? >>> >>> Take care, >>> -stu >>> >> >> >
Re: SQL + flatten (or .*) quality docs location?
Thank you! Just in case someone else stumbles onto this, I figured what was giving me trouble. The object I wanted to flattened happened to be null at times, at which point it would error out and give some exception along the lines of: "method flatten() not found" (Sorry, I'll try to follow up with the actual trace to help people with their searches later) which made it sound more like I was using it incorrectly altogether, rather than that the object was null... I think even just letting NPE would have been a little more helpful... I figured it out, btw, but trying equivalent programs in Flink and Esper (doing an eval of both). Esper gave a clearer error, and then I went back and fixed the flink program. Take care, -stu On Thu, Mar 16, 2017 at 3:27 AM, Fabian Hueskewrote: > Hi Stu, > > there is only one page of documentation for the Table API and SQL [1]. > I agree the structure could be improved and split into multiple pages. > > Regarding the flatting of a Pojo have a look at the "Built-In Functions" > section [2]. > If you select "SQL" and head to the "Value access functions", you'll find > > > tableName.compositeType.* : Converts a Flink composite type (such as > Tuple, POJO, etc.) and all of its direct subtypes into a flat > representation where every subtype is a separate field. > > > The following program works returns the correct result: > > // POJO definition > class MyPojo(var x: Int, var y: Int) { > def this() = this(0, 0) > } > > // SQL query > val env = ExecutionEnvironment.getExecutionEnvironment > val tEnv = TableEnvironment.getTableEnvironment(env, config) > > val ds = env.fromElements((0, new MyPojo(1, 2)), (1, new MyPojo(2, 3)), > (2, new MyPojo(3, 4)) ) > tEnv.registerDataSet("Pojos", ds, 'id, 'pojo) > > val result = tEnv.sql("SELECT id, Pojos.pojo.* FROM Pojos") // you need to > include the table name to flatten a Pojo > > val results = result.toDataSet[Row].collect() > println(results.mkString("\n")) > > // Result > 0,1,2 > 1,2,3 > 2,3,4 > > Best, Fabian > > [1] https://ci.apache.org/projects/flink/flink-docs- > release-1.2/dev/table_api.html > [2] https://ci.apache.org/projects/flink/flink-docs- > release-1.2/dev/table_api.html#built-in-functions > > 2017-03-15 21:31 GMT+01:00 Stu Smith : > >> The documentation seems to indicate that there is a flatten method >> available in the sql language interface (in the table of available >> methods), or, alternatively using the '*' character somehow (in the text >> above the table). >> >> Yet I cannot flatten a POJO type, nor can I find any sufficient >> documentation in the official docs, searching the mailing list via >> markmail, looking through the examples in the source, or looking for >> through the SQL tests in the source. >> >> Can someone point me to the correct location for some solid flink SQL >> examples and docs? >> >> Take care, >> -stu >> > >
Re: SQL + flatten (or .*) quality docs location?
Hi Stu, there is only one page of documentation for the Table API and SQL [1]. I agree the structure could be improved and split into multiple pages. Regarding the flatting of a Pojo have a look at the "Built-In Functions" section [2]. If you select "SQL" and head to the "Value access functions", you'll find > tableName.compositeType.* : Converts a Flink composite type (such as Tuple, POJO, etc.) and all of its direct subtypes into a flat representation where every subtype is a separate field. The following program works returns the correct result: // POJO definition class MyPojo(var x: Int, var y: Int) { def this() = this(0, 0) } // SQL query val env = ExecutionEnvironment.getExecutionEnvironment val tEnv = TableEnvironment.getTableEnvironment(env, config) val ds = env.fromElements((0, new MyPojo(1, 2)), (1, new MyPojo(2, 3)), (2, new MyPojo(3, 4)) ) tEnv.registerDataSet("Pojos", ds, 'id, 'pojo) val result = tEnv.sql("SELECT id, Pojos.pojo.* FROM Pojos") // you need to include the table name to flatten a Pojo val results = result.toDataSet[Row].collect() println(results.mkString("\n")) // Result 0,1,2 1,2,3 2,3,4 Best, Fabian [1] https://ci.apache.org/projects/flink/flink-docs-release-1.2/dev/table_api.html [2] https://ci.apache.org/projects/flink/flink-docs-release-1.2/dev/table_api.html#built-in-functions 2017-03-15 21:31 GMT+01:00 Stu Smith: > The documentation seems to indicate that there is a flatten method > available in the sql language interface (in the table of available > methods), or, alternatively using the '*' character somehow (in the text > above the table). > > Yet I cannot flatten a POJO type, nor can I find any sufficient > documentation in the official docs, searching the mailing list via > markmail, looking through the examples in the source, or looking for > through the SQL tests in the source. > > Can someone point me to the correct location for some solid flink SQL > examples and docs? > > Take care, > -stu >
SQL + flatten (or .*) quality docs location?
The documentation seems to indicate that there is a flatten method available in the sql language interface (in the table of available methods), or, alternatively using the '*' character somehow (in the text above the table). Yet I cannot flatten a POJO type, nor can I find any sufficient documentation in the official docs, searching the mailing list via markmail, looking through the examples in the source, or looking for through the SQL tests in the source. Can someone point me to the correct location for some solid flink SQL examples and docs? Take care, -stu