Snowflake Python Pandas Connector - Unknown error using fetch_pandas_all

Question

I am trying to connect to snowflake using the python pandas connector.

I use the anaconda distribution on Windows, but uninstalled the existing connector and pyarrow and reinstalled using instructions on this page: https://docs.snowflake.com/en/user-guide/python-connector-pandas.html

I have the following versions

pandas 1.0.4 py37h47e9c7a_0

pip 20.1.1 py37_1

pyarrow 0.17.1 pypi_0 pypi

python 3.7.7 h81c818b_4

snowflake-connector-python 2.2.7 pypi_0 pypi

When running step 2 of this document: https://docs.snowflake.com/en/user-guide/python-connector-install.html, I get: 4.21.2

On attempting to use fetch_pandas_all() I get an error: NotSupportedError: Unknown error

The code I am using is as follows:

import snowflake.connector
import pandas as pd

SNOWFLAKE_DATA_SOURCE = '<DB>.<Schema>.<VIEW>'

query = '''
select * 
from table(%s)
LIMIT 10;
'''
def create_snowflake_connection():
    conn = snowflake.connector.connect(
            user='MYUSERNAME',
            account='MYACCOUNT',
            authenticator = 'externalbrowser',
            warehouse='<WH>',
            database='<DB>',
            role='<ROLE>',
            schema='<SCHEMA>'
    )
    
    return conn

con = create_snowflake_connection()

cur = con.cursor()
temp = cur.execute(query, (SNOWFLAKE_DATA_SOURCE)).fetch_pandas_all()
cur.close()

I am wondering what else I need to install/upgrade/check in order to get fetch_pandas_all() to work?

Edit: After posting an answer below, I have realised that the issue is with the SSO (single sign on) with authenticator='externalbrowser'. When using a stand-alone account I can fetch.

Answer 1

What happens when you run this code?

from snowflake import connector
import time

import logging
for logger_name in ['snowflake.connector', 'botocore', 'boto3']:
    logger = logging.getLogger(logger_name)
    logger.setLevel(logging.DEBUG)
    ch = logging.FileHandler('test.log')
    ch.setLevel(logging.DEBUG)
    ch.setFormatter(logging.Formatter('%(asctime)s - %(threadName)s %(filename)s:%(lineno)d - %(funcName)s() - %(levelname)s - %(message)s'))
    logger.addHandler(ch)

from snowflake.connector.cursor import CAN_USE_ARROW_RESULT

import  pyarrow
import pandas as pd

print('CAN_USE_ARROW_RESULT', CAN_USE_ARROW_RESULT)

This will output whether CAN_USE_ARROW_RESULT is true and if it's not true, then pandas won't work. When you did the pip install, which of these did you run?

pip install snowflake-connector-python pip install snowflake-connector-python[pandas]

Also, what OS are you running on?

Answer 2

I have this working now, but am not sure which part helps - the following steps were taken:

1. Based on comment by @Kirby, I tried pip3 install --upgrade snowflake-connector-python .. this is based on a historic screenshot .. I should have have [pandas] in brackets, i.e. pip3 install --upgrade snowflake-connector-python[pandas], but regardless, I got the following error message:

Error: Microsoft Visual C++ 14.0 is required. Get it with "Build Tools for Visual Studio": https://visualstudio.microsoft.com/downloads

I therefore downloaded (exact filename: vs_buildtools__121011638.1587963829.exe) and installed VS Build Tools.

2. This is the tricky part .. I subsequently got admin access to my machine (so hoping it is the visual studio build tools that helped, and not admin access)

3. I then followed the Snowflake Documentation Python Connector API instructions originally referred to:

   a. Anaconda Prompt (opened as admin): pip install snowflake-connector-python[pandas]

   b. Python:

import snowflake.connector
import pandas as pd

ctx = snowflake.connector.connect(
          user=user,
          account=account,
          password= 'password',
          warehouse=warehouse,
          database=database,
          role = role,
          schema=schema)

# Create a cursor object.
cur = ctx.cursor()

# Execute a statement that will generate a result set.
sql = "select * from t"
cur.execute(sql)

# Fetch the result set from the cursor and deliver it as the Pandas DataFrame.
df = cur.fetch_pandas_all()

Edit I have since realised that I still have the error when executing df = cur.fetch_pandas_all() when using my Okta (single sign on) account, i.e. when I use my username and authenticator = 'externalbrowser'. When I use a different account, I no longer get the error (with password).
NOTE: That I am still able to connect with externalbrowser (and I see the query has executed successfully in Snowflake history); I am just not able to fetch.