Could variables used in sql files be evaluated ?

[fabrice-etanchaud]

Hi,
From my tests, it seems that in sql files used as streams, variables are not evaluated.
Am I doing something wrong ?
Thank you !

[flarco]

Please share your configurations + debug level logs.

[fabrice-etanchaud]

Here is my little test :

variable_evaluation_in_sql_files.zip

Thank you !

[flarco]

Thanks. I was able to reproduce what you're experiencing.
It's because the env vars were not being passed to the custom SQL from file:///data/test.sql.
Not sure if you noticed in your debug.log, it is working for stream streams.dummy.1, but not for stream file:///data/test.sql.

Pushed this commit to fix it!
Works now. Will release 1.1.14 soon.

[fabrice-etanchaud]

once again thank you, @flarco.
By the way, I have another question : when using custom sql in the replication file, how does sling recognize that the stream's name is not a real source dataset ? Because there is a sql element ? Can we set the stream name to anything, or is there a dedicated syntax ?

[flarco]

For custom sql, it can be set to whatever. Ideally, you should name it the main table source of the SQL (something like schema.main_table). You actually put file:///data/test.sql as the name, which was novel (haven't seen that before). I was surprised that sling auto read from the proper file, actually :). Typically, you'd put custom sql like this:

streams:
  schema.main_table:
    sql: |
      select main_table.*, other_table.col1
      from schema.main_table
      join schema.other_table ...

[fabrice-etanchaud]

Indeed, I extrapolated this feature from the --src-stream CLI argument...
I should have read the documentation til the end :

source: MY_SOURCE_DB
target: MY_TARGET_DB

defaults:
  mode: full-refresh

streams:
  source_schema.source_table.1:
    sql: |
      select *
      from my_schema.my_table
      where col1 is not null
    object: target_schema.target_table

  source_schema.source_table.2:
    sql: file:///path/to/query.sql
    object: target_schema.target_table