[Failing Test]: Integrating SQL file with YAML for transformation

[ujjwalrajanand]

What happened?

Able to run the SQL query directly in YAML- https://github.com/vaishnavipandey-vp/Apache-Beam-YAML/blob/c467c59f5f13a00514e90be1cfd505982577524b/Apache-Beam-YAML/Pipelines/sql_pipeline.yaml#L14C4-L16C43

Trying to integrate SQL file with YAML for transformation but facing challenges in doing so - https://github.com/vaishnavipandey-vp/Apache-Beam-YAML/blob/c467c59f5f13a00514e90be1cfd505982577524b/Apache-Beam-YAML/Pipelines/new.yaml#L1C1-L22C25

Issue Failure

Failure: Test is continually failing

Issue Priority

Priority: 1 (unhealthy code / failing or flaky postcommit so we cannot be sure the product is healthy)

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

[robertwb]

There is no ReadSqlFromFile or ExecuteSql transform. Also not that transform outputs can only be used as other transform inputs (as they're deferred, whereas configs need to be known before the pipeline is launched).

Apache Beam Yaml does support jinja templatization, which could probably give you what you want: https://beamsummit.org/slides/2024/BeamYAML_Advancedtopics.pdf

We should document this better: #32873