ensure_db function SSH connectivity issues

[ramkumar233]

Tried running the 'to_snowflake' module which in turn invokes 'ensure_db' module to check object existence. SQL Alchemy create engine doesn't execute successfully when we use SSH Keypair authentication for login to Snowflake. Is there a space for enhancement to add SSH keypair authentication than specifying the direct password for Snowflake account? Error is returned as a SQL Alchemy Database error which says 'Failed to connect to DB' Incorrect username or password was specified.

Read snowflake works as it doesn't use the create_engine specific module

[jrbourbeau]

Thanks for raising an issue @ramkumar233! The use of pd_writer + SQL Alchemy engine was so we could both conveniently create the table if it doesn't exist and make sure the table has the correct DataFrame schema. That said, we should be able to accomplish the same thing using snowflake.connector.connect + write_pandas like is done here

dask-snowflake/dask_snowflake/core.py

Lines 21 to 44 in e7977e5

	@delayed
	def write_snowflake(
	df: dd.DataFrame,
	name: str,
	connection_kwargs: Dict,
	):
	# TODO: Remove the `use_new_put_get` logic below once the known PUT issue with
	# `snowflake-connector-python` is resolved
	if "use_new_put_get" not in connection_kwargs:
	connection_kwargs["use_new_put_get"] = False
	with snowflake.connector.connect(**connection_kwargs) as conn:
	# NOTE: Use a process-wide lock to avoid a `boto` multithreading issue
	# https://github.com/snowflakedb/snowflake-connector-python/issues/156
	with SerializableLock(token="write_snowflake"):
	write_pandas(
	conn=conn,
	df=df,
	schema=connection_kwargs.get("schema", None),
	# NOTE: since ensure_db_exists uses uppercase for the table name
	table_name=name.upper(),
	parallel=1,
	quote_identifiers=False,
	)

Just to clarify, would moving to snowflake.connector.connect + write_pandas, instead of the SQL Alchemy engine approach, help resolve the SSH Keypair authentication issue you're running into?

[cginther-snowflake]

@jrbourbeau Thanks James - I believe this will resolve the issue as I have no issues using read_snowflake with key pair auth as read_snowflake only uses Snowflake native connector.