How to send parameters to redshift queries

[Ramnath Medikonda]

Has anyone got a chance to pass parameters to a SQL query when implementing RedshiftQuery class in luigi?

I have this query which needs to read parameters:


delete from "dev"."device_engagement_staging" where batch_id = %(batch_id)s; where batch_id will have to be read from command line(luigi parameters).

Any suggestion would be appreciated.

[ch...@thekensta.com]

In your class, try something like this:

class MyRedShiftTask(RedshiftQuery):

batch_id = luigi.IntParameter()

@property
def query(self):
template = "delete from dev.device_engagement_staging where batch_id = {batch_id}"
sql = template.format(batch_id=self.batch_id)
return sql

Or for more sophisticated formatting you can try jinja2 templates.
you can also put in a schema and and table as params then read from config, e.g.

schema = luigi.Parameter(default="dev")
table = luigi.Parameter(default="device_engagement_staging")
...
template = "delete from {schema}.{table} where batch_id = {batch_id}"

and then in the production config do:

[MyRedShiftTask]
schema=production
table=device_engagement

Or at least, this is what is working for me :-)