[Feature Request]: Add yaml sql example using calcite_connection_properties, and document this in the transform catalog

[damccorm]

What would you like to happen?

Right now, any SQL options have to be passed through as pipeline options. This is somewhat awkward for yaml/xlang - for example:

pipeline:
  transforms:
    - name: Source
      type: Create
      config:
        elements:
        - {a: "x", b: 1}
        - {a: "x", b: 2}
        - {a: "x", b: 3}
        - {a: "y", b: 10}
    - name: Transform
      type: Sql
      config:
        query: "SELECT STRING_TO_ARRAY('abc def g', ' ') as col_name"
      input: Source
    - name: Sink
      type: LogForTesting
      input: Transform
      config:
        level: INFO
options:
  calcite_connection_properties: {"fun": "postgresql"}

works, but you need to know how to specify the calcite_connection_properties (which is tricky to get right). It would be nice to have some examples of this and a callout in the transform catalog - https://beam.apache.org/releases/yamldoc/current/#sql

Issue Priority

Priority: 2 (default / most feature requests should be filed as P2)

Issue Components

• Component: Python SDK
• Component: Java SDK
• Component: Go SDK
• Component: Typescript SDK
• Component: IO connector
• Component: Beam YAML
• Component: Beam examples
• Component: Beam playground
• Component: Beam katas
• Component: Website
• Component: Infrastructure
• Component: Spark Runner
• Component: Flink Runner
• Component: Samza Runner
• Component: Twister2 Runner
• Component: Hazelcast Jet Runner
• Component: Google Cloud Dataflow Runner

[damccorm]

cc/ @chamikaramj @derrickaw - a nice-to-have backlog issue

[Arunodoy18]

HI, i think i would love to solve this issue .Thank you.
-take-issue