How to write pandas df to snowflake from Azure databricks?-CodePudding

I am having a hard time writing to snowflake from Azure databricks.

I have the following code that I am trying to use to write a pandas df to a snowflake table but I keep getting that my username or password is incorrect.

I have tried the username and password to login in on my browser and it works no problem. Furthermore I have also tried using my email address associated with this account and get the same error. What am I doing wrong?:

import pandas as pd
import sqlalchemy as sa
from sqlalchemy import *
import snowflake.connector
from snowflake.connector.pandas_tools import pd_writer


def savedfToSnowflake(df):
    engine = snowflake.connector.connect(
                user='xxxx',
                password='xxxx',
                role='xxxx',
                account='xxxx',
                warehouse='xxxx',
                database='xxxx',
                schema='xxxx',
                autocommit=xxxx
                )

    try:
        connection = engine.connect()
        print("Connected to Snowflake ")
        df3.to_sql('table_name', con=connection, index=False,
                   if_exists='append')  # make sure index is False, Snowflake doesnt accept indexes
        print("Successfully saved data to snowflake")
    except Exception as ex:
        print("Exception occurred {}".format(ex))
#     finally:
#         # close connection
#         if connection:
#             connection.close()
#         if engine:
#             engine.dispose()


def main():
    savedfToSnowflake(df3)


if __name__ == '__main__':
    main()

CodePudding user response：

If you are logging into Snowflake in your browser via SSO provider i.e. you click 'Sign In using AzureAD', rather than entering a Snowflake username and password, then you don't have a Snowflake Username and Password that can be used with this connection command.

Snowflake has users, and then ways of authenticating as that user. Generally you have 3 methods of authentication:

Snowflake username / password
Snowflake RSA key
SSO federated by Microsoft Azure AD, Okta etc...

You could speak to your Snowflake admin regarding getting a username / password or RSA key setup for your user that would allow you to connect in this way. Alternatively, depending on the programming environment that you are using you could use the 'External Browser' authentication method.

engine = snowflake.connector.connect(
    user="<login_name>", #You can get it by executing in UI: desc user <username>;
    account="<account-name>", #Add all of the account-name between https:// and snowflakecomputing.com
    authenticator="externalbrowser",
)

See here for more information.