SQL + flatten (or .*) quality docs location?

[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

[Fabian Hueske-2]

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 <[hidden email]>:

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

[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 <[hidden email]> 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 <[hidden email]>:

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

[Fabian Hueske-2]

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 <[hidden email]>:

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 <[hidden email]> 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 <[hidden email]>:

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