How to replace ${param} by :param

[ZD]

Hello,

We previously used ${param} in our SQL queries:

SELECT * FROM json.`${source_path}/file.json`

However, this syntax is now deprecated. The recommended approach is to use :param instead.

But when I attempt to replace ${param} with :param, I encounter the below error:

SELECT * FROM json.`:source_path/file.json`

java.net.URISyntaxException: Relative path in absolute URI: :source_path/file.json

How can I correctly reference the parameter in this case?

[radothede]

Hi @ZD 

Please try this syntax in Your notebook for SQL:

%sql
declare _my_path = 'some_path';
select _my_path;

[ZD]

The question is how to use this declared variable between backticks `_my_path` ?

SELECT * FROM json.`_my_path/file.json`

This does not work!

[szymon_dybczak]

Hi @ZD ,

Try something like that:

SELECT *
FROM identifier(:source_path)

[ZD]

Hi @szymon_dybczak 

Thank you for the proposed solution. However, this does not meet my business needs as it does not offer a dynamic approach for replacing the base path when having multiple files and formats.

[szymon_dybczak]

Hi @ZD ,

But it does, you can use it in following way. Define 3 parameters:

- file_format

- base_path

- file_name

And then concatenate those parameters inside identifier clause:

SELECT *
FROM identifier(:file_format || :base_path || :file_name)

Or maybe I didn't understand your requirments properly.