#questions
Title
# questions
z
Ziren Lin
03/28/2023, 3:44 PMHi team, do we support customized input from parameters into the SQL query/file? I tried the following the codes file but Kedro couldn't read the parameters and input into sql query.
Copy code
#globals.yml
order_number: 'abc' Copy code
#catalog.yml
sql:
type: pandas.SQLQueryDataset
sql: "SELECT * FROM table WHERE column = ${order_number}"n
Nok Lam Chan
03/28/2023, 3:57 PMhttps://github.com/kedro-org/kedro/discussions/1782
Are you on 0.18.x? You may need a custom config loader.
đź‘Ť 1
But please be aware of the risk of SQL injection
z
Ziren Lin
03/28/2023, 4:03 PMIt works!! Amazing! Thanks for the help! i will pay attention to the risks. Thanks for reminding
one more question, can i pass yml files not in
conf/base/conf/base/parametersand if I put the SQL query in a seperate
.sqlfilepathn
Nok Lam Chan
03/28/2023, 4:09 PMSure! By default it recognise this pattern, it’s also customisable since
0.18.5 Copy code
"parameters": ["params*", "params*/**", "**/params*"]It’s also how modular pipeline works by default
conf/base/parameters/<pipeline_name>.ymlYou can create the pipeline structure by doing
kedro pipeline create <pipeline_name>If you used the
filepathđź‘Ť 1
z
Ziren Lin
03/28/2023, 4:14 PMI tried
Copy code
CONFIG_LOADER_ARGS = {
"globals_pattern": "*pipeline_XXX.yml",
}n
Nok Lam Chan
03/28/2023, 4:39 PMYou don’t need to pass in any custom argument in this case.
Copy code
"parameters": ["params*", "params*/**", "**/params*"]đź‘Ť 1
https://docs.kedro.org/en/stable/kedro_project_setup/configuration.html#template-configuration
I suggest have a look at the docs
🙌 1
z
Ziren Lin
03/28/2023, 4:42 PMThat works!! Thanks so much for the support. BTW if i do want to inject runtime parameters into my
.sqlfilepathK 1
n
Nok Lam Chan
03/28/2023, 4:47 PMJinja should be fine, but this may be quite advance and not so straightforward.
I think the easiest way is to overload the
SQLQueryDataSet_loadz
Ziren Lin
03/28/2023, 4:51 PMif I compile the sql query in the
_load.sqln
Nok Lam Chan
03/28/2023, 4:53 PMWith the current implementation, there are two way you can pass in the SQL
1. using the
sqlfilepathThe idea of this CustomSQLDataSet is you add addition logic for (2), after loading the file, you further compile it with Jinja + the template variable -> then you execute the compile version of it
z
Ziren Lin
03/28/2023, 4:55 PMthat's to say if I want to go with option 2, I must use Jinja to further compile the query?
n
Nok Lam Chan
03/28/2023, 4:55 PM Copy code
def _load(self) -> pd.DataFrame:
load_args = copy.deepcopy(self._load_args)
engine = self.engines[self._connection_str].execution_options(
**self._execution_options
) # type: ignore
if self._filepath:
load_path = get_filepath_str(PurePosixPath(self._filepath), self._protocol)
with self._fs.open(load_path, mode="r") as fs_file:
load_args["sql"] = fs_file.read()
## Add your Jinja stuff here
return pd.read_sql_query(con=engine, **load_args)z
Ziren Lin
03/28/2023, 4:56 PMoh I see!! Let me try to see how Jinja could help with option 2
n
Nok Lam Chan
03/28/2023, 5:00 PMIt’s just what’s top of my head. You can also use the normal SQL bind variable, which avoid the SQL injection problem.
z
Ziren Lin
03/28/2023, 5:04 PMBut we can't have bind variables defined from the
.yml8 Views