How can you prevent the Snowflake Python connector from doing json formatting on ARRAY/OBJECT/VARIANT type columns?

user_1410394 0
Viewed 1623

I am working on a service that pulls VARIANT data from Snowflake and exposes for consumption elsewhere, and I want to prevent the Snowflake connector from escaping and adding string formatting. One approach I am trying is using the converter_class option mentioned here.

Digging in a bit more, I found that the default SnowflakeConverter class doesn't do any action for VARIANT/ARRAY/OBJECT conversion at all: https://github.com/snowflakedb/snowflake-connector-python/blob/79a106ba70355249ea0eff16977bafe774846d90/converter.py#L315. Since that's the case, I extended the class to use this as the _ARRAY_to_python converter (working with just ARRAY for a start):

def convert_array_to_python(self, ctx):
    logger.info('trying to convert')
    return lambda x: [s.strip('\"\n\t ') for s in x.strip('[\n\t ]').split(',')]

And used conn = connect(user=...., converter_class=CustomConverter()) where CustomConverter looks like this and does something similar to the DefaultConverterClass function here:

def CustomConverterClass():
    converter = sf_lib.connection.DefaultConverterClass()
    converter._ARRAY_to_python = convert_array_to_python
    return converter

I've checked the value of conn.converter_class()._ARRAY_to_python and getattr(conn.converter_class(), '_ARRAY_to_python') after the connection is made, and they both match the convert_array_to_python function defined earlier. And yet, when I execute the query and examine the cursor for the VARIANT column:

cursor = conn.cursor(sf_lib.DictCursor)
data = cursor.execute(query)
for row in data:
    ...

The values are still escaped. Moreover, it doesn't look like the ARRAY converter is called. I have control over the schema of the table I'm querying, and have confirmed that the column is of type ARRAY.

The Snowflake connector seems to do its json conversion using JsonResult. As far as I can tell digging through the source code, the call stack of the _ARRAY_to_python call should be something like: next(data) -> JsonDictResult.__next__() -> JsonDictResult._row_to_python(row) -> _convert_ARRAY_to_python(col) (from https://github.com/snowflakedb/snowflake-connector-python/blob/d4f04f20949e2bcb70b0ea0927a8a362ba272389/json_result.py#L178)

Why isn't the converter being called here, and how else can I prevent the json formatting from being applied?

snowflake-cloud-data-platform python
1 Answers

I want to prevent the Snowflake connector from escaping and adding string formatting

Actually, it is not Snowflake that escapes and adds string formatting. snowflake-connector receives strings and then converts back to their type. For Variant, it does not do any conversion so that is why you have strings instead.

To answer the question why does Snowflake does not call the converter_class methods, it is because Snowflake is using now Apache Arrow to fetch query results https://www.snowflake.com/blog/fetching-query-results-from-snowflake-just-got-a-lot-faster-with-apache-arrow/?lang=fr

So what does it change ? The method _json_result_class is not called anymore https://github.com/snowflakedb/snowflake-connector-python/blob/79a106ba70355249ea0eff16977bafe774846d90/cursor.py#L624

if self._query_result_format == 'arrow':
    self.check_can_use_arrow_resultset()
    self._result = ArrowResult(data, self, use_dict_result=self._use_dict_result)
else:
    self._result = self._json_result_class(data, self)

And actually it is this method that was used to serialize/deserialize the results using the converter class https://github.com/snowflakedb/snowflake-connector-python/blob/79a106ba70355249ea0eff16977bafe774846d90/json_result.py#L132

row[idx] = col if conv is None or col is None else conv(col)

In short : converter_class is not used anymore, documentation is not up-to-date

To test if your version of snowflake-connector uses Arrow, you can do :

from snowflake.connector.cursor import CAN_USE_ARROW_RESULT
print(CAN_USE_ARROW_RESULT)

I didn't find a proper way to handle how Arrow de-serialize the result, I guess you will need to handle that by converting the variant columns yourself to dict or list while iterating over the result.

Quick example :

import json

import snowflake.connector
con = snowflake.connector.connect(
...
)
cursor = con.cursor()
data = cursor.execute("""...""")

def deserialize_sf_value(value):
    if isinstance(value, str) and value[0].strip() in '{[':
        return json.loads(value)
    return value

results = [[deserialize_sf_value(value) for value in row] for row in data]
print(results)
user_42893800
Related