Apache Airflow Bash Operator with environment variable

Question

I have environment variable configured in /etc/sysconfig/airflow

PASSWORD=pass123

I am hoping to be able to use this in the Bash command within BashOperator so that the password will not be visible in the Airflow UI or log. The variable will then be used in the DAG task as below.

con = 'username/$PASSWORD@//ex01-db.mybiz.com:1521/dwh'

sqlcl_cmd = f"""/oracle/sqlcl/bin/sql -s {con} > /data/tmp/extract.csv <<EOF
SET SQLFORMAT CSV
SET FEEDBACK OFF
SET TERMOUT OFF
SET ECHO OFF
SELECT * FROM ORDER.DAILY_SALES;
EXIT
EOF
"""

BashOperator(
    task_id = 'extract_order',
    bash_command = sqlcl_cmd,
    dag = dag
)

However, it's observed that the variable $PASSWORD is not expanded by Bash, resulting in error. How do I correctly use environment variable to be passed to bash_command in BashOperator.

You probably need to remove the spaces around the `=` operator in the assignation of `con='...'` (first line of code) and write `${con}` instead of `{con}` in your script. — Pierre François, Mar 24 '21 at 16:56
If you are running with systemd, https://stackoverflow.com/questions/47195896/airflow-configuration-in-environment-variable-not-working? If you are not running with systemd, https://airflow.apache.org/docs/apache-airflow/stable/howto/variable.html#storing-variables-in-environment-variables? does this help? — Emma, Mar 24 '21 at 17:43
@Emma that will hide the password for Airflow UI for the code, but it will still be visible in the log — idazuwaika, Mar 24 '21 at 18:20
env variable will show in logs. Masking in logs is not an out-of-the-box feature but can work around. Here is the work around example: https://stackoverflow.com/questions/48380452/mask-out-sensitive-information-in-python-log. Otherwise, I guess you would need aws kms kind of things. — Emma, Mar 24 '21 at 18:35
The original question was env variable is not expanding. If you would like to ask about masking in logs, I would suggest you to edit the title and description. — Emma, Mar 24 '21 at 18:38

score 0 · Answer 1 · answered Mar 24 '21 at 17:32

First, test out if your airflow user has access to that variable. 'echo $PASSWORD'. You can change the $PASSWORD temporarily if you do not want to show that in the log.

If it does then you can pass the con with a parameter in the bash task as you can see it here: https://airflow.apache.org/docs/apache-airflow/1.10.2/tutorial.html

con = 'username/$PASSWORD@//ex01-db.mybiz.com:1521/dwh'

sqlcl_cmd = f"""/oracle/sqlcl/bin/sql -s {{params.con}} > /data/tmp/extract.csv <<EOF
SET SQLFORMAT CSV
SET FEEDBACK OFF
SET TERMOUT OFF
SET ECHO OFF
SELECT * FROM ORDER.DAILY_SALES;
EXIT
EOF
"""

BashOperator(
    task_id = 'extract_order',
    params={'con': con},
    bash_command = sqlcl_cmd,
    dag = dag
)

Apache Airflow Bash Operator with environment variable

1 Answers1