Get query for methods in DBApi hook from files

[JavierLopezT]

Currently (as far as I understand reading the code base, the docs in pandas etc and making some tests in my code) you can only have a string (or list of them) as query in the methods related to queries in DBApi: get_pandas_df, run, get_records and get_first.

It would be nice being able to read the queries from a file instead of a string in the script. The code for getting the query could be something like the following:

with open(f'{filename}') as f:
        raw_query = f.read().rstrip("\n")
 sql_query = eval(f'f"""{raw_query}"""')

And after that, you just have to pass sql_query to any method.

In a regular python script, considering a file called 'test_query.sql' with "SELECT * FROM {table}", the following will work:

table = 'orders'
with open(f'{filename}') as f:
        raw_query = f.read().rstrip("\n")
 sql_query = eval(f'f"""{raw_query}"""')

And sql_query will be: "SELECT * FROM orders"

However, I am having troubles finding out a way to have this working correctly in the methods of DBApi hook, mainly for passing the parameters. I don't come up with any solution that doesn't involve to pass every parameter that you have in the query, which can be very painful if you have a very long query with a lot of parameters.

So, ideally (considering the same file as above), the following will work:

table = 'orders'
redshift = PostgresHook('redshift_conn')
df = redshift.get_pandas_df('test_query.sql')

Do you have any ideas? I will open the PR and work on it if a good idea arises.

[boring-cyborg]

Thanks for opening your first issue here! Be sure to follow the issue template!

[eladkal]

The ability to render query from file exists in BaseOperator hence can be used by all operators by specifying template_ext. This is not available in Hooks as hooks don't get templated.
Reading query from file is just one step. What about replacing parameters with their run time values?

I think your use case as presented here is incomplete. df is in memory this can't be the final step of your ETL so the question is what are you going to do with the df? You are probably writing the result somewhere.

Consider writing your custom operator that utilises PostgresHook add what ever steps you wish on the df and then dump it to what ever you want. By doing so your problem is solved as you will be able to use template_ext

[potiuk]

Agree with @eladkal - using ext and custom operator is the right ting to do. template_ext is the mechanism foreseen to do do similar thing and it's better to utilise it.