Use of JSON

[kirkholmes]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using (python --version)? Python 3.7.6

2. What operating system and processor architecture are you using (python -c 'import platform; print(platform.platform())')? Windows-10-10.0.18362-SP0

3. What are the component versions in the environment (pip list)?

   
   -qlalchemy                 1.3.0
   alembic                    1.4.0
   appdirs                    1.4.3
   asn1crypto                 1.3.0
   azure-common               1.1.24
   azure-storage-blob         2.1.0
   azure-storage-common       2.1.0
   Babel                      2.8.0
   bcrypt                     3.1.4
   blinker                    1.4
   boto3                      1.11.17
   botocore                   1.14.17
   certifi                    2019.11.28
   cffi                       1.11.5
   chardet                    3.0.4
   click                      6.7
   coverage                   4.3.4
   cryptography               2.8
   docutils                   0.15.2
   Flask                      1.0.2
   Flask-Babel                0.12.2
   Flask-HTTPAuth             3.2.4
   Flask-Login                0.4.1
   Flask-Mail                 0.9.1
   Flask-Migrate              2.2.1
   Flask-PageDown             0.2.2
   Flask-Principal            0.4.0
   Flask-Script               2.0.6
   Flask-SQLAlchemy           2.4.1
   Flask-WTF                  0.14.2
   idna                       2.8
   ijson                      2.6.1
   itsdangerous               0.24
   Jinja2                     2.10.1
   jmespath                   0.9.5
   Mako                       1.0.7
   Markdown                   3.0
   MarkupSafe                 1.0
   marshmallow                2.15.6
   marshmallow-jsonapi        0.20.3
   mimesis                    2.1.0
   oscrypto                   1.2.0
   packaging                  16.8
   pip                        20.0.2
   plotly                     4.2.1
   pycparser                  2.17
   pycryptodomex              3.9.7
   PyJWT                      1.7.1
   PyMySQL                    0.9.2
   pyOpenSSL                  19.1.0
   pyparsing                  2.2.1
   python-dateutil            2.8.1
   python-editor              1.0.3
   pytz                       2019.3
   requests                   2.22.0
   retrying                   1.3.3
   s3transfer                 0.3.3
   setuptools                 45.2.0
   six                        1.10.0
   snowflake-connector-python 2.2.1
   snowflake-sqlalchemy       1.2.1
   SQLAlchemy                 1.3.0
   urllib3                    1.25.8
   Werkzeug                   0.16.0
   WTForms                    2.2.1

4. What did you do?
If possible, provide a recipe for reproducing the error.
A complete runnable program is good.
I have some Flask-Migrate (built on Alembic) code and I'm getting an error because types.JSON is not currently supported with Snowflake. I was hoping that code could be added to allow for this and/or when this is attempted it will replace with something that will work. Here is the code for the migration:

"""new action model

Revision ID: 36c91aa9b3b5 Revises: fe0f77ef3f46 Create Date: 2019-10-20 21:23:05.959617

""" from alembic import op import sqlalchemy as sa

revision identifiers, used by Alembic.

revision = '36c91aa9b3b5' down_revision = 'fe0f77ef3f46' branch_labels = None depends_on = None

def upgrade():

get connection

conn = op.get_bind()

# check if it is possible to migrate
r = conn.execute('SELECT COUNT(id) FROM flicket_ticket_action WHERE (assigned IS NOT NULL) '
        'AND (claimed IS NOT NULL) AND (released IS NOT NULL) AND (closed IS NOT NULL) '
        'AND (opened IS NOT NULL) AND (status IS NOT NULL) AND (priority IS NOT NULL) != 1')
assert r.first()[0] == 0, 'Automatic migration not possible, do it manually!'
import json

# add new columns
op.add_column('flicket_ticket_action', sa.Column('action', sa.String(length=30), nullable=True))
op.add_column('flicket_ticket_action', sa.Column('data', sa.JSON(none_as_null=True), nullable=True))

# transform data into action column
conn.execute(sa.text('UPDATE flicket_ticket_action SET action=:action WHERE opened IS NOT NULL'), action='open')
conn.execute(sa.text('UPDATE flicket_ticket_action SET action=:action WHERE assigned IS NOT NULL'), action='assign')
conn.execute(sa.text('UPDATE flicket_ticket_action SET action=:action WHERE claimed IS NOT NULL'), action='claim')
conn.execute(sa.text('UPDATE flicket_ticket_action SET action=:action WHERE status IS NOT NULL'), action='status')
conn.execute(sa.text('UPDATE flicket_ticket_action SET action=:action WHERE priority IS NOT NULL'), action='priority')
conn.execute(sa.text('UPDATE flicket_ticket_action SET action=:action WHERE released IS NOT NULL'), action='release')
conn.execute(sa.text('UPDATE flicket_ticket_action SET action=:action WHERE closed IS NOT NULL'), action='close')

# update data column for status action
r = conn.execute('SELECT status FROM flicket_ticket_action WHERE status IS NOT NULL GROUP BY status')
for rr in r:
    data = {
            'status': rr[0],
            'note': 'migrated during 0.2.1 release',
            }
    s = conn.execute(sa.text('SELECT id FROM flicket_status WHERE status=:status'), status=rr[0]).fetchall()
    if len(s) == 1:
        data['status_id'] = s[0][0]
    conn.execute(sa.text('UPDATE flicket_ticket_action SET data=:data WHERE status=:status'),
            data=json.dumps(data), status=rr[0])

# update data column for priority action
r = conn.execute('SELECT priority FROM flicket_ticket_action WHERE priority IS NOT NULL GROUP BY priority')
for rr in r:
    data = {
            'priority': rr[0],
            'note': 'migrated during 0.2.1 release',
            }
    s = conn.execute(sa.text('SELECT id FROM flicket_priorities WHERE priority=:priority'), priority=rr[0]).fetchall()
    if len(s) == 1:
        data['priority_id'] = s[0][0]
    conn.execute(sa.text('UPDATE flicket_ticket_action SET data=:data WHERE priority=:priority'),
            data=json.dumps(data), priority=rr[0])

# update ticket_id
conn.execute('UPDATE flicket_ticket_action '
        'SET ticket_id=(SELECT ticket_id FROM flicket_post WHERE id=flicket_ticket_action.post_id) '
        'WHERE post_id IS NOT NULL')

# drop old columns
op.drop_column('flicket_ticket_action', 'status')
op.drop_column('flicket_ticket_action', 'claimed')
op.drop_column('flicket_ticket_action', 'opened')
op.drop_column('flicket_ticket_action', 'released')
op.drop_column('flicket_ticket_action', 'priority')
op.drop_column('flicket_ticket_action', 'assigned')
op.drop_column('flicket_ticket_action', 'closed')

def downgrade():

DOWNGRADE CAN BE DANGEROUS AND RESULT IN DATA LOST

# get connection
conn = op.get_bind()

# check first if possible
r = conn.execute(sa.text('SELECT COUNT(id) FROM flicket_ticket_action WHERE action NOT IN :actions'),
        actions=('open', 'assign', 'claim', 'status', 'priority', 'release', 'close'))
assert r.first()[0] == 0, 'Automatic downgrade not possible, do it manually!'

# add old columns
op.add_column('flicket_ticket_action', sa.Column('closed', sa.Boolean(), nullable=True))
op.add_column('flicket_ticket_action', sa.Column('assigned', sa.Boolean(), nullable=True))
op.add_column('flicket_ticket_action', sa.Column('priority', sa.String(length=12), nullable=True))
op.add_column('flicket_ticket_action', sa.Column('released', sa.Boolean(), nullable=True))
op.add_column('flicket_ticket_action', sa.Column('opened', sa.Boolean(), nullable=True))
op.add_column('flicket_ticket_action', sa.Column('claimed', sa.Boolean(), nullable=True))
op.add_column('flicket_ticket_action', sa.Column('status', sa.String(length=20), nullable=True))

# transform data from action and data columns
conn.execute(sa.text('UPDATE flicket_ticket_action SET opened=:value WHERE action=:action'),
        value=True, action='open')
conn.execute(sa.text('UPDATE flicket_ticket_action SET assigned=:value WHERE action=:action'),
        value=True, action='assign')
conn.execute(sa.text('UPDATE flicket_ticket_action SET claimed=:value WHERE action=:action'),
        value=True, action='claim')
conn.execute(sa.text('UPDATE flicket_ticket_action SET status=JSON_VALUE(data, :path) WHERE action=:action'),
        path='$.status', action='status')
conn.execute(sa.text('UPDATE flicket_ticket_action SET priority=JSON_VALUE(data, :path) WHERE action=:action'),
        path='$.priority', action='priority')
conn.execute(sa.text('UPDATE flicket_ticket_action SET released=:value WHERE action=:action'),
        value=True,  action='release')
conn.execute(sa.text('UPDATE flicket_ticket_action SET closed=:value WHERE action=:action'),
        value=True, action='close')

# set ticket_id NULL if post_id
conn.execute('UPDATE flicket_ticket_action SET ticket_id=NULL WHERE post_id IS NOT NULL')

# drop new columns
op.drop_column('flicket_ticket_action', 'data')
op.drop_column('flicket_ticket_action', 'action')

5. What did you expect to see?
It to work :)

6. What did you see instead?

INFO [alembic.runtime.migration] Context impl SnowflakeImpl. INFO [alembic.runtime.migration] Will assume non-transactional DDL. INFO [alembic.runtime.migration] Running upgrade -> fe0f77ef3f46, migrations before source code control INFO [alembic.runtime.migration] Running upgrade fe0f77ef3f46 -> 36c91aa9b3b5, new action model Traceback (most recent call last): File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\sqlalchemy\sql\visitors.py", line 87, in _compiler_dispatch meth = getter(visitor) AttributeError: 'SnowflakeTypeCompiler' object has no attribute 'visit_JSON'

During handling of the above exception, another exception occurred:

Traceback (most recent call last): File "manage.py", line 31, in manager.run() File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\flask_script__init.py", line 417, in run result = self.handle(argv[0], argv[1:]) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\flask_script__init__.py", line 386, in handle res = handle(*args, **config) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\flask_script\commands.py", line 216, in call return self.run(*args, kwargs) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\flask_migrate__init__.py", line 95, in wrapped f(args, kwargs) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\flask_migrate__init__.py", line 280, in upgrade command.upgrade(config, revision, sql=sql, tag=tag) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\alembic\command.py", line 298, in upgrade script.run_env() File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\alembic\script\base.py", line 489, in run_env util.load_python_file(self.dir, "env.py") File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\alembic\util\pyfiles.py", line 98, in load_python_file module = load_module_py(module_id, path) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\alembic\util\compat.py", line 173, in load_module_py spec.loader.exec_module(module) File "", line 728, in exec_module File "", line 219, in _call_with_frames_removed File "migrations\env.py", line 91, in run_migrations_online() File "migrations\env.py", line 84, in run_migrations_online context.run_migrations() File "", line 8, in run_migrations File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\alembic\runtime\environment.py", line 846, in run_migrations self.get_context().run_migrations(kw) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\alembic\runtime\migration.py", line 518, in run_migrations step.migration_fn(kw) File "C:\Users\kirk\Projects\flicket\migrations\versions\36c91aa9b3b5_new_action_model.py", line 31, in upgrade op.add_column('flicket_ticket_action', sa.Column('data', sa.JSON(none_as_null=True), nullable=True)) File "", line 8, in add_column File "", line 3, in add_column File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\alembic\operations\ops.py", line 1927, in add_column return operations.invoke(op) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\alembic\operations\base.py", line 374, in invoke return fn(self, operation) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\alembic\operations\toimpl.py", line 132, in add_column operations.impl.add_column(table_name, column, schema=schema, kw) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\alembic\ddl\impl.py", line 237, in add_column self._exec(base.AddColumn(table_name, column, schema=schema)) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\alembic\ddl\impl.py", line 140, in _exec return conn.execute(construct, multiparams, params) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\sqlalchemy\engine\base.py", line 988, in execute return meth(self, multiparams, params) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\sqlalchemy\sql\ddl.py", line 72, in _execute_on_connection return connection._execute_ddl(self, multiparams, params) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1043, in _execute_ddl else None, File "", line 1, in File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\sqlalchemy\sql\elements.py", line 462, in compile return self._compiler(dialect, bind=bind, kw) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\sqlalchemy\sql\ddl.py", line 29, in _compiler return dialect.ddl_compiler(dialect, self, kw) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\sqlalchemy\sql\compiler.py", line 319, in init__ self.string = self.process(self.statement, compile_kwargs) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\sqlalchemy\sql\compiler.py", line 350, in process return obj._compiler_dispatch(self, kwargs) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\sqlalchemy\ext\compiler.py", line 436, in lambda *arg, *kw: existing(arg, kw), File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\sqlalchemy\ext\compiler.py", line 478, in call return fn(element, compiler, kw) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\alembic\ddl\base.py", line 116, in visit_add_column add_column(compiler, element.column, kw), File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\alembic\ddl\base.py", line 219, in add_column return "ADD COLUMN %s" % compiler.get_column_specification(column, kw) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\snowflake\sqlalchemy\base.py", line 320, in get_column_specification column.type, typeexpression=column) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\sqlalchemy\sql\compiler.py", line 400, in process return type._compiler_dispatch(self, **kw) File "C:\Users\kirk\Projects\flicket\venv\lib\site-packages\sqlalchemy\sql\visitors.py", line 89, in _compiler_dispatch raise exc.UnsupportedCompilationError(visitor, cls) sqlalchemy.exc.UnsupportedCompilationError: Compiler <snowflake.sqlalchemy.base.SnowflakeTypeCompiler object at 0x000001B84E167C08> can't render element of type <class 'sqlalchemy.sql.sqltypes.JSON'>

7. Can you set logging to DEBUG and collect the logs?
Teach me how! and where to put it :)

import logging import os

for logger_name in ['snowflake.sqlalchemy', 'snowflake.connector', 'botocore']: logger = logging.getLogger(logger_name) logger.setLevel(logging.DEBUG) ch = logging.StreamHandler() 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)

[keller00]

Thanks, @kirkholmes I will look into supporting JSONs!

[kirillal]

@kirkholmes Did you try using VARIANT data type to handle JSON fields? https://docs.snowflake.net/manuals/sql-reference/data-types-semistructured.html#variant

from snowflake.sqlalchemy.custom_types import VARIANT
op.add_column('flicket_ticket_action', sa.Column('data', VARIANT, nullable=True))

[kirkholmes]

@kirillal I did not. The code was actually autogenerated as part of the migration script. I would think there would be a way that if JSON isn't supported that it would swap sa.JSON for something like sa.VARIANT to address it.

[github-actions[bot]]

To clean up and re-prioritize bugs and feature requests we are closing all issues older than 6 months as of Apr 1, 2023. If there are any issues or feature requests that you would like us to address, please re-create them. For urgent issues, opening a support case with this link Snowflake Community is the fastest way to get a response