[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
• [x] 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