Parameterized SQL in Presto on Presto CLI

Question

Is there any option to provide parameters on Presto CLI.

I am trying to change my impala-shell command to Presto where my HQL file gets parameter from command line of impala like below.

impala-shell -f ot_full.hql --var=date_next=${date_next_para} --var=yrmth=${yrmth_para} --var=yrmth_L12=${yrmth_L12_para} --var=pyrmth=${pyrmth_para}

WITH clause in presto is not that much helpful.

How can we convert it to Presto command line.

I did not find any documentation/example on this in https://prestodb.io/docs/current/

Answer 1

The Presto .CLI doesn't support this, so you'll need to substitute the variables in the SQL query before passing it to the CLI. One way is to do this directly in shell:

presto --execute "SELECT * FROM table WHERE ds >= '${date_next_para}'"

For longer queries, using a here document is a good option.