Snowflake Python Connector: Copy Command Status and Error Handling

0
votes

According to the Snowflake docs, when a user executes a copy command it will return 1 of 3 status values:

  • loaded
  • load failed
  • partially loaded

My question is if I use the Python Snowflake Connector (see example code below) to execute a copy command is an exception raised if the status returned is load failed or partially loaded?

Thank you!

copy_dml = 'copy into database.schema.table ' \
'from @fully_qualified_stage pattern = \'.*'+ table_name +'.*[.]json\' ' \
'file_format = (format_name = fully_qualified_json_format) ' \
'force = true;'

 try:
        import snowflake.connector
#-------------------------------------------------------------------------------------------------------------------------------  
        #snowflake variables
        snowflake_warehouse = credentials.iloc[0]['snowflake_warehouse']
        snowflake_account = credentials.iloc[0]['snowflake_account']
        snowflake_role = credentials.iloc[0]['snowflake_role']
        snowflake_username = credentials.iloc[0]['Username']
        snowflake_password = credentials.iloc[0]['Password']
        snowflake_connection = ''
        cs = ''#snowflake connection cursor
        exec_copy_dml = ''
        copy_result_field_metadata = ''
        copy_result = ''
        snowflake_copy_result_df = ''
#-------------------------------------------------------------------------------------------------------------------------------
        # load JSON file(s) into Snowflake 
        snowflake_connection = snowflake.connector.connect(
        user = snowflake_username,
        password = snowflake_password,
        account = snowflake_account,
        warehouse = snowflake_warehouse,
        role = snowflake_role)
        cs = snowflake_connection.cursor()
        exec_copy_dml = cs.execute(copy_dml)
        copy_result =  exec_copy_dml.fetchall()
        copy_result_field_metadata = cs.description
        snowflake_copy_result_df = snowflake_results_df(copy_result_field_metadata,copy_result)
    except snowflake.connector.errors.ProgrammingError as copy_error:
        copy_exception_message = "There was a problem loading JSON files to Snowflake," + \
        "a snowflake.connector.errors.ProgrammingError exception was raised."
        print(copy_exception_message)
        raise
    except Exception as error_message:
        raise 
    finally:
        snowflake_connection.close()
2
snowflake-cloud-data-platform

2 Answers

1
votes

I believe it won't raise exception for load status, you have to check the load status and take necessary action if required.

0
votes

After you issue your COPY INTO dml, you can run the following query - 

SELECT * FROM TABLE(VALIDATE(TABLE_NAME, job_id => '_last'))

This will give you details on the files that you were trying to load. It will normally return empty, unless you encountered issues upload.

You can save this save results in an object and make necessary control adjustments.