<h3 dir="auto">Apache Airflow Provider(s)</h3>
<p dir="auto">snowflake</p>
<h3 dir="auto">Versions of Apache Airflow Providers</h3>
<p dir="auto">apache-airflow-providers-snowflake | 2.4.0</p>
<h3 dir="auto">Apache Airflow version</h3>
<p dir="auto">2.2.3 (latest released)</p>
<h3 dir="auto">Operating System</h3>
<p dir="auto">Ubuntu 20.04</p>
<h3 dir="auto">Deployment</h3>
<p dir="auto">Virtualenv installation</p>
<h3 dir="auto">Deployment details</h3>
<p dir="auto">Python 3.8.10</p>
<p dir="auto">alembic==1.7.5<br>
antiorm==1.2.1<br>
anyio==3.5.0<br>
apache-airflow==2.2.3<br>
apache-airflow-providers-ftp==2.0.1<br>
apache-airflow-providers-http==2.0.2<br>
apache-airflow-providers-imap==2.1.0<br>
apache-airflow-providers-snowflake==2.4.0<br>
apache-airflow-providers-sqlite==2.0.1<br>
apispec==3.3.2<br>
argcomplete==1.12.3<br>
asn1crypto==1.4.0<br>
attrs==20.3.0<br>
Babel==2.9.1<br>
blinker==1.4<br>
cattrs==1.5.0<br>
certifi==2021.10.8<br>
cffi==1.15.0<br>
charset-normalizer==2.0.10<br>
click==7.1.2<br>
clickclick==20.10.2<br>
colorama==0.4.4<br>
colorlog==5.0.1<br>
commonmark==0.9.1<br>
croniter==1.0.15<br>
cryptography==3.4.8<br>
db==0.1.1<br>
db-sqlite3==0.0.1<br>
defusedxml==0.7.1<br>
dill==0.3.4<br>
dnspython==2.2.0<br>
docutils==0.16<br>
email-validator==1.1.3<br>
Flask==1.1.4<br>
Flask-AppBuilder==3.4.3<br>
Flask-Babel==2.0.0<br>
Flask-Caching==1.10.1<br>
Flask-JWT-Extended==3.25.1<br>
Flask-Login==0.4.1<br>
Flask-OpenID==1.3.0<br>
Flask-SQLAlchemy==2.5.1<br>
Flask-WTF==0.14.3<br>
graphviz==0.19.1<br>
gunicorn==20.1.0<br>
h11==0.12.0<br>
httpcore==0.13.7<br>
httpx==0.19.0<br>
idna==3.3<br>
importlib-metadata==4.10.1<br>
importlib-resources==5.4.0<br>
inflection==0.5.1<br>
iso8601==1.0.2<br>
isodate==0.6.1<br>
itsdangerous==1.1.0<br>
Jinja2==2.11.3<br>
jsonschema==3.2.0<br>
lazy-object-proxy==1.7.1<br>
lockfile==0.12.2<br>
Mako==1.1.6<br>
Markdown==3.3.6<br>
MarkupSafe==2.0.1<br>
marshmallow==3.14.1<br>
marshmallow-enum==1.5.1<br>
marshmallow-oneofschema==3.0.1<br>
marshmallow-sqlalchemy==0.26.1<br>
openapi-schema-validator==0.1.6<br>
openapi-spec-validator==0.3.2<br>
oscrypto==1.2.1<br>
packaging==21.3<br>
pendulum==2.1.2<br>
pkg_resources==0.0.0<br>
prison==0.2.1<br>
psutil==5.9.0<br>
pycparser==2.21<br>
pycryptodomex==3.12.0<br>
Pygments==2.11.2<br>
PyJWT==1.7.1<br>
pyOpenSSL==20.0.1<br>
pyparsing==3.0.7<br>
pyrsistent==0.16.1<br>
python-daemon==2.3.0<br>
python-dateutil==2.8.2<br>
python-nvd3==0.15.0<br>
python-slugify==4.0.1<br>
python3-openid==3.2.0<br>
pytz==2021.3<br>
pytzdata==2020.1<br>
PyYAML==6.0<br>
requests==2.27.1<br>
rfc3986==1.5.0<br>
rich==11.0.0<br>
setproctitle==1.2.2<br>
six==1.16.0<br>
sniffio==1.2.0<br>
snowflake-connector-python==2.7.1<br>
snowflake-sqlalchemy==1.2.4<br>
SQLAlchemy==1.3.24<br>
SQLAlchemy-JSONField==1.0.0<br>
SQLAlchemy-Utils==0.38.2<br>
swagger-ui-bundle==0.0.9<br>
tabulate==0.8.9<br>
tenacity==8.0.1<br>
termcolor==1.1.0<br>
text-unidecode==1.3<br>
unicodecsv==0.14.1<br>
urllib3==1.26.8<br>
Werkzeug==1.0.1<br>
WTForms==2.3.3<br>
zipp==3.7.0</p>
<h3 dir="auto">What happened</h3>
<p dir="auto">After creating a new Snowflake connection in the Airflow web portal, the Extras parameter is automatically set to the boolean value false (see example below).</p>
<p dir="auto">{"extra__snowflake__account": "******<em>", "extra__snowflake__database": "", "extra__snowflake__insecure_mode": false, "extra__snowflake__region": "canada-central.azure", "extra__snowflake__role": "<strong>", "extra__snowflake__warehouse": "</strong></em>"}</p>
<p dir="auto">When running or testing a dag, an error is thrown in SnowflakeHook._get_conn_params when calling airflow.utils.strings.to_boolean on the bool value while evaluating insecure_mode. See end of stack trace below.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="conn_config = self._get_conn_params()"><pre class="notranslate"><code class="notranslate">conn_config = self._get_conn_params()
</code></pre></div>
<p dir="auto">File "/home/gdorval/airflow/lib/python3.8/site-packages/airflow/providers/snowflake/hooks/snowflake.py", line 170, in _get_conn_params<br>
insecure_mode = to_boolean(<br>
File "/home/gdorval/airflow/lib/python3.8/site-packages/airflow/utils/strings.py", line 30, in to_boolean<br>
return False if astring is None else astring.lower() in ['true', 't', 'y', 'yes', '1']<br>
AttributeError: 'bool' object has no attribute 'lower'</p>
<h3 dir="auto">What you expected to happen</h3>
<p dir="auto">SnowflakeHook._get_conn_params should expect a boolean value for "extra__snowflake__insecure_mode".  There is no need to convert it from a string to boolean.</p>
<h3 dir="auto">How to reproduce</h3>
<ol dir="auto">
<li>Create a new Snowflake connection.</li>
<li>Create a simple dag with one task using the snowflake connection (example below).</li>
<li>Test the task</li>
</ol>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="query_test = &quot;&quot;&quot;select current_date() as date;&quot;&quot;&quot;

with DAG(
    'example_snowflake',
    start_date=datetime(2021, 1, 1),
    default_args=DEFAULT_ARGS,
    tags=['snowflake'],
    catchup=False,
) as dag:

  snowflake_op_sql_str = SnowflakeOperator(
      task_id='snowflake_op_sql_str',
      dag=dag,
      sql=query_test,
      snowflake_conn_id=SNOWFLAKE_CONN_ID,
      warehouse=SNOWFLAKE_WAREHOUSE,
      database=SNOWFLAKE_DATABASE,
      schema=SNOWFLAKE_SCHEMA,
      role=SNOWFLAKE_ROLE,
  )

snowflake_op_sql_str"><pre class="notranslate"><code class="notranslate">query_test = """select current_date() as date;"""

with DAG(
    'example_snowflake',
    start_date=datetime(2021, 1, 1),
    default_args=DEFAULT_ARGS,
    tags=['snowflake'],
    catchup=False,
) as dag:

  snowflake_op_sql_str = SnowflakeOperator(
      task_id='snowflake_op_sql_str',
      dag=dag,
      sql=query_test,
      snowflake_conn_id=SNOWFLAKE_CONN_ID,
      warehouse=SNOWFLAKE_WAREHOUSE,
      database=SNOWFLAKE_DATABASE,
      schema=SNOWFLAKE_SCHEMA,
      role=SNOWFLAKE_ROLE,
  )

snowflake_op_sql_str
</code></pre></div>
<h3 dir="auto">Anything else</h3>
<p dir="auto"><em>No response</em></p>
<h3 dir="auto">Are you willing to submit PR?</h3>
<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Yes I am willing to submit a PR!</li>
</ul>
<h3 dir="auto">Code of Conduct</h3>
<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> I agree to follow this project's <a href="https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md">Code of Conduct</a></li>
</ul>
<h3 dir="auto">Apache Airflow version</h3>
<p dir="auto">2.2.3 (latest released)</p>
<h3 dir="auto">What happened</h3>
<p dir="auto">Error thrown when using SnowflakeOperator with a Snowflake Connection.</p>
<p dir="auto">After creating a Snowflake Connection, the "Extra" field was automatically filled with a dictionary containing the values entered in the other input fields. Note: the value for the key "extra__snowflake__insecure_mode" is a boolean.</p>
<p dir="auto"><a target="_blank" rel="noopener noreferrer nofollow" href="https://user-images.githubusercontent.com/82085639/150988536-002c78d7-b405-49c8-af10-ddf3d050ce09.png"><img src="https://user-images.githubusercontent.com/82085639/150988536-002c78d7-b405-49c8-af10-ddf3d050ce09.png" alt="image" style="max-width: 100%;"></a></p>
<p dir="auto">A task using SnowflakeOperator fails, throwing following error:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="[2022-01-25, 14:39:54 UTC] {taskinstance.py:1700} ERROR - Task failed with exception
Traceback (most recent call last):
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/models/taskinstance.py&quot;, line 1329, in _run_raw_task
    self._execute_task_with_callbacks(context)
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/models/taskinstance.py&quot;, line 1455, in _execute_task_with_callbacks
    result = self._execute_task(context, self.task)
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/models/taskinstance.py&quot;, line 1511, in _execute_task
    result = execute_callable(context=context)
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/operators/snowflake.py&quot;, line 129, in execute
    execution_info = hook.run(self.sql, autocommit=self.autocommit, parameters=self.parameters)
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/hooks/snowflake.py&quot;, line 293, in run
    with closing(self.get_conn()) as conn:
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/hooks/snowflake.py&quot;, line 236, in get_conn
    conn_config = self._get_conn_params()
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/hooks/snowflake.py&quot;, line 170, in _get_conn_params
    insecure_mode = to_boolean(
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/utils/strings.py&quot;, line 30, in to_boolean
    return False if astring is None else astring.lower() in ['true', 't', 'y', 'yes', '1']
AttributeError: 'bool' object has no attribute 'lower'
[2022-01-25, 14:39:54 UTC] {taskinstance.py:1267} INFO - Marking task as FAILED. dag_id=test, task_id=snowflake_task, execution_date=20220123T000000, start_date=20220125T133954, end_date=20220125T133954
[2022-01-25, 14:39:55 UTC] {standard_task_runner.py:89} ERROR - Failed to execute job 4 for task snowflake_task
Traceback (most recent call last):
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/task/task_runner/standard_task_runner.py&quot;, line 85, in _start_by_fork
    args.func(args, dag=self.dag)
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/cli/cli_parser.py&quot;, line 48, in command
    return func(*args, **kwargs)
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/utils/cli.py&quot;, line 92, in wrapper
    return f(*args, **kwargs)
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/cli/commands/task_command.py&quot;, line 298, in task_run
    _run_task_by_selected_method(args, dag, ti)
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/cli/commands/task_command.py&quot;, line 107, in _run_task_by_selected_method
    _run_raw_task(args, ti)
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/cli/commands/task_command.py&quot;, line 180, in _run_raw_task
    ti._run_raw_task(
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/utils/session.py&quot;, line 70, in wrapper
    return func(*args, session=session, **kwargs)
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/models/taskinstance.py&quot;, line 1329, in _run_raw_task
    self._execute_task_with_callbacks(context)
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/models/taskinstance.py&quot;, line 1455, in _execute_task_with_callbacks
    result = self._execute_task(context, self.task)
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/models/taskinstance.py&quot;, line 1511, in _execute_task
    result = execute_callable(context=context)
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/operators/snowflake.py&quot;, line 129, in execute
    execution_info = hook.run(self.sql, autocommit=self.autocommit, parameters=self.parameters)
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/hooks/snowflake.py&quot;, line 293, in run
    with closing(self.get_conn()) as conn:
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/hooks/snowflake.py&quot;, line 236, in get_conn
    conn_config = self._get_conn_params()
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/hooks/snowflake.py&quot;, line 170, in _get_conn_params
    insecure_mode = to_boolean(
  File &quot;/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/utils/strings.py&quot;, line 30, in to_boolean
    return False if astring is None else astring.lower() in ['true', 't', 'y', 'yes', '1']
AttributeError: 'bool' object has no attribute 'lower'
[2022-01-25, 14:39:55 UTC] {local_task_job.py:154} INFO - Task exited with return code 1
[2022-01-25, 14:39:55 UTC] {local_task_job.py:264} INFO - 0 downstream tasks scheduled from follow-on schedule check
"><pre class="notranslate"><code class="notranslate">[2022-01-25, 14:39:54 UTC] {taskinstance.py:1700} ERROR - Task failed with exception
Traceback (most recent call last):
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/models/taskinstance.py", line 1329, in _run_raw_task
    self._execute_task_with_callbacks(context)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/models/taskinstance.py", line 1455, in _execute_task_with_callbacks
    result = self._execute_task(context, self.task)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/models/taskinstance.py", line 1511, in _execute_task
    result = execute_callable(context=context)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/operators/snowflake.py", line 129, in execute
    execution_info = hook.run(self.sql, autocommit=self.autocommit, parameters=self.parameters)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/hooks/snowflake.py", line 293, in run
    with closing(self.get_conn()) as conn:
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/hooks/snowflake.py", line 236, in get_conn
    conn_config = self._get_conn_params()
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/hooks/snowflake.py", line 170, in _get_conn_params
    insecure_mode = to_boolean(
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/utils/strings.py", line 30, in to_boolean
    return False if astring is None else astring.lower() in ['true', 't', 'y', 'yes', '1']
AttributeError: 'bool' object has no attribute 'lower'
[2022-01-25, 14:39:54 UTC] {taskinstance.py:1267} INFO - Marking task as FAILED. dag_id=test, task_id=snowflake_task, execution_date=20220123T000000, start_date=20220125T133954, end_date=20220125T133954
[2022-01-25, 14:39:55 UTC] {standard_task_runner.py:89} ERROR - Failed to execute job 4 for task snowflake_task
Traceback (most recent call last):
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/task/task_runner/standard_task_runner.py", line 85, in _start_by_fork
    args.func(args, dag=self.dag)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/cli/cli_parser.py", line 48, in command
    return func(*args, **kwargs)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/utils/cli.py", line 92, in wrapper
    return f(*args, **kwargs)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/cli/commands/task_command.py", line 298, in task_run
    _run_task_by_selected_method(args, dag, ti)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/cli/commands/task_command.py", line 107, in _run_task_by_selected_method
    _run_raw_task(args, ti)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/cli/commands/task_command.py", line 180, in _run_raw_task
    ti._run_raw_task(
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/utils/session.py", line 70, in wrapper
    return func(*args, session=session, **kwargs)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/models/taskinstance.py", line 1329, in _run_raw_task
    self._execute_task_with_callbacks(context)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/models/taskinstance.py", line 1455, in _execute_task_with_callbacks
    result = self._execute_task(context, self.task)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/models/taskinstance.py", line 1511, in _execute_task
    result = execute_callable(context=context)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/operators/snowflake.py", line 129, in execute
    execution_info = hook.run(self.sql, autocommit=self.autocommit, parameters=self.parameters)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/hooks/snowflake.py", line 293, in run
    with closing(self.get_conn()) as conn:
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/hooks/snowflake.py", line 236, in get_conn
    conn_config = self._get_conn_params()
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/hooks/snowflake.py", line 170, in _get_conn_params
    insecure_mode = to_boolean(
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/utils/strings.py", line 30, in to_boolean
    return False if astring is None else astring.lower() in ['true', 't', 'y', 'yes', '1']
AttributeError: 'bool' object has no attribute 'lower'
[2022-01-25, 14:39:55 UTC] {local_task_job.py:154} INFO - Task exited with return code 1
[2022-01-25, 14:39:55 UTC] {local_task_job.py:264} INFO - 0 downstream tasks scheduled from follow-on schedule check

</code></pre></div>
<p dir="auto">This error seems to arise because the boolean value of extra__snowflake__insecure_mode is interpreted as a string.<br>
Manually modifying the boolean extra__snowflake__insecure_mode value to be string in the "Extra" dictionary solves this problem:<br>
<strong>false-&gt;"false"</strong><br>
<a target="_blank" rel="noopener noreferrer nofollow" href="https://user-images.githubusercontent.com/82085639/150990538-b58a641e-cc16-4c49-88a6-1d32c4a3fc79.png"><img src="https://user-images.githubusercontent.com/82085639/150990538-b58a641e-cc16-4c49-88a6-1d32c4a3fc79.png" alt="image" style="max-width: 100%;"></a></p>
<h3 dir="auto">What you expected to happen</h3>
<p dir="auto">Be able to create a usable Snowflake Connection, by filling in fields other than "Extra". The "Extra" field should be automatically filled with a correct/usable connection dictionary.<br>
I can then use this Snowflake Connection for SnowflakeOperators in DAGs.</p>
<h3 dir="auto">How to reproduce</h3>
<p dir="auto">Create a new Connection of type <strong>Snowflake</strong>, set an arbitrary Connection ID. The rest of the fields can be left empty (doesn't affect error).<br>
<a target="_blank" rel="noopener noreferrer nofollow" href="https://user-images.githubusercontent.com/82085639/150990006-87c9e388-872b-4323-b34b-3ea816f024bb.png"><img src="https://user-images.githubusercontent.com/82085639/150990006-87c9e388-872b-4323-b34b-3ea816f024bb.png" alt="image" style="max-width: 100%;"></a></p>
<p dir="auto">Create a DAG with a SnowflakeOperator task, which uses the created Snowflake Connection:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from airflow import DAG
from airflow.providers.snowflake.operators.snowflake import SnowflakeOperator
from airflow.utils.dates import days_ago

with DAG('test', start_date=days_ago(2)) as dag:
    
    snowflake_task = SnowflakeOperator(task_id='snowflake_task',
                                            sql='select 1;',
                                            snowflake_conn_id='snowflake_conn')"><pre class="notranslate"><code class="notranslate">from airflow import DAG
from airflow.providers.snowflake.operators.snowflake import SnowflakeOperator
from airflow.utils.dates import days_ago

with DAG('test', start_date=days_ago(2)) as dag:
    
    snowflake_task = SnowflakeOperator(task_id='snowflake_task',
                                            sql='select 1;',
                                            snowflake_conn_id='snowflake_conn')
</code></pre></div>
<p dir="auto">Execute the DAG, the task will fail and throw the above error.</p>
<h3 dir="auto">Operating System</h3>
<p dir="auto">Ubuntu 20.04.2 LTS</p>
<h3 dir="auto">Versions of Apache Airflow Providers</h3>
<p dir="auto">apache-airflow-providers-snowflake==2.4.0</p>
<h3 dir="auto">Deployment</h3>
<p dir="auto">Virtualenv installation</p>
<h3 dir="auto">Deployment details</h3>
<p dir="auto"><em>No response</em></p>
<h3 dir="auto">Anything else</h3>
<p dir="auto"><em>No response</em></p>
<h3 dir="auto">Are you willing to submit PR?</h3>
<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox"> Yes I am willing to submit a PR!</li>
</ul>
<h3 dir="auto">Code of Conduct</h3>
<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> I agree to follow this project's <a href="https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md">Code of Conduct</a></li>
</ul>