SNOW-994645: snowflake.connector.errors.ProgrammingError: 001003 (42000): SQL compilation error: syntax error line 1 at position 27 unexpected 'ON'

[jm-nab]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?

3.11

2. What operating system and processor architecture are you using?

ubuntu 22.04

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

@latest

4. What did you do?

alembic upgrade head

5. What did you expect to see?

SUCCESS

6. Can you set logging to DEBUG and collect the logs?

(venv_311) 18:57:14 jm@jm:~/repos/pinecone-langchain-llm/vertexai_loaders:
jm/add-alembic:$ alembic revision --autogenerate -m 'initial migration'
INFO  [alembic.runtime.migration] Context impl SnowflakeImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'history'
INFO  [alembic.autogenerate.compare] Detected added index ''ix_history_id'' on '('id',)'
INFO  [alembic.autogenerate.compare] Detected added table 'prompt_history'
INFO  [alembic.autogenerate.compare] Detected added index ''ix_prompt_history_id'' on '('id',)'
INFO  [alembic.autogenerate.compare] Detected added table 'messages'
INFO  [alembic.autogenerate.compare] Detected added index ''ix_messages_id'' on '('id',)'
INFO  [alembic.autogenerate.compare] Detected added table 'prompts'
INFO  [alembic.autogenerate.compare] Detected added index ''ix_prompts_id'' on '('id',)'
INFO  [alembic.autogenerate.compare] Detected added table 'responses'
INFO  [alembic.autogenerate.compare] Detected added index ''ix_responses_id'' on '('id',)'
INFO  [alembic.autogenerate.compare] Detected type change from _CUSTOM_DECIMAL(precision=38, scale=0) to Integer() on 'users.id'
INFO  [alembic.autogenerate.compare] Detected type change from TIMESTAMP_NTZ() to DateTime(timezone=True) on 'users.created_at'
INFO  [alembic.autogenerate.compare] Detected added index ''ix_users_email'' on '('email',)'
INFO  [alembic.autogenerate.compare] Detected added index ''ix_users_id'' on '('id',)'
<alembic.operations.ops.MigrationScript object at 0xffffba344590>
  Generating /home/jm/repos/pinecone-langchain-llm/vertexai_loaders/alembic/versions/8419d9b288aa_initial_migration.py ...  done
(venv_311) 18:57:50 jm@jm:~/repos/pinecone-langchain-llm/vertexai_loaders:
jm/add-alembic:$ alembic heads
8419d9b288aa (head)
(venv_311) 18:58:01 jm@jm:~/repos/pinecone-langchain-llm/vertexai_loaders:
jm/add-alembic:$ alembic upgrade head --sql
INFO  [alembic.runtime.migration] Context impl SnowflakeImpl.
INFO  [alembic.runtime.migration] Generating static SQL
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
CREATE TABLE alembic_version (
    version_num VARCHAR(32) NOT NULL,
    CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
);

INFO  [alembic.runtime.migration] Running upgrade  -> 8419d9b288aa, initial migration
-- Running upgrade  -> 8419d9b288aa

CREATE TABLE history (
    id INTEGER NOT NULL AUTOINCREMENT,
    created_at datetime,
    user_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY(user_id) REFERENCES users (id)
);

CREATE INDEX ix_history_id ON history (id);

CREATE TABLE prompt_history (
    id INTEGER NOT NULL AUTOINCREMENT,
    created_at datetime,
    user_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY(user_id) REFERENCES users (id)
);

CREATE INDEX ix_prompt_history_id ON prompt_history (id);

CREATE TABLE messages (
    id INTEGER NOT NULL AUTOINCREMENT,
    content VARCHAR,
    created_at datetime,
    history_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY(history_id) REFERENCES history (id)
);

CREATE INDEX ix_messages_id ON messages (id);

CREATE TABLE prompts (
    id INTEGER NOT NULL AUTOINCREMENT,
    content VARCHAR,
    created_at datetime,
    prompt_history_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY(prompt_history_id) REFERENCES prompt_history (id)
);

CREATE INDEX ix_prompts_id ON prompts (id);

CREATE TABLE responses (
    id INTEGER NOT NULL AUTOINCREMENT,
    content VARCHAR,
    created_at datetime,
    history_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY(history_id) REFERENCES history (id)
);

CREATE INDEX ix_responses_id ON responses (id);

Traceback (most recent call last):
  File "/home/jm/repos/pinecone-langchain-llm/vertexai_loaders/venv_311/bin/alembic", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/home/jm/repos/pinecone-langchain-llm/vertexai_loaders/venv_311/lib/python3.11/site-packages/alembic/config.py", line 630, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/home/jm/repos/pinecone-langchain-llm/vertexai_loaders/venv_311/lib/python3.11/site-packages/alembic/config.py", line 624, in main
    self.run_cmd(cfg, options)
  File "/home/jm/repos/pinecone-langchain-llm/vertexai_loaders/venv_311/lib/python3.11/site-packages/alembic/config.py", line 601, in run_cmd
    fn(
  File "/home/jm/repos/pinecone-langchain-llm/vertexai_loaders/venv_311/lib/python3.11/site-packages/alembic/command.py", line 401, in upgrade
    script.run_env()
  File "/home/jm/repos/pinecone-langchain-llm/vertexai_loaders/venv_311/lib/python3.11/site-packages/alembic/script/base.py", line 585, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/jm/repos/pinecone-langchain-llm/vertexai_loaders/venv_311/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 93, in load_python_file
    module = load_module_py(module_id, path)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jm/repos/pinecone-langchain-llm/vertexai_loaders/venv_311/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 109, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<frozen importlib._bootstrap_external>", line 940, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/home/jm/repos/pinecone-langchain-llm/vertexai_loaders/alembic/env.py", line 90, in <module>
    run_migrations_offline()
  File "/home/jm/repos/pinecone-langchain-llm/vertexai_loaders/alembic/env.py", line 64, in run_migrations_offline
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/home/jm/repos/pinecone-langchain-llm/vertexai_loaders/venv_311/lib/python3.11/site-packages/alembic/runtime/environment.py", line 939, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/jm/repos/pinecone-langchain-llm/vertexai_loaders/venv_311/lib/python3.11/site-packages/alembic/runtime/migration.py", line 624, in run_migrations
    step.migration_fn(**kw)
  File "/home/jm/repos/pinecone-langchain-llm/vertexai_loaders/alembic/versions/8419d9b288aa_initial_migration.py", line 67, in upgrade
    existing_type=snowflake.sqlalchemy.custom_types._CUSTOM_DECIMAL(precision=38, scale=0),
                  ^^^^^^^^^
NameError: name 'snowflake' is not defined
(venv_311) 18:58:09 jm@jm:~/repos/pinecone-langchain-llm/vertexai_loaders:
jm/add-alembic:$ alembic upgrade head --sql
INFO  [alembic.runtime.migration] Context impl SnowflakeImpl.
INFO  [alembic.runtime.migration] Generating static SQL
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
CREATE TABLE alembic_version (
    version_num VARCHAR(32) NOT NULL,
    CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
);

INFO  [alembic.runtime.migration] Running upgrade  -> 8419d9b288aa, initial migration
-- Running upgrade  -> 8419d9b288aa

CREATE TABLE history (
    id INTEGER NOT NULL AUTOINCREMENT,
    created_at datetime,
    user_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY(user_id) REFERENCES users (id)
);

CREATE INDEX ix_history_id ON history (id);

CREATE TABLE prompt_history (
    id INTEGER NOT NULL AUTOINCREMENT,
    created_at datetime,
    user_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY(user_id) REFERENCES users (id)
);

CREATE INDEX ix_prompt_history_id ON prompt_history (id);

CREATE TABLE messages (
    id INTEGER NOT NULL AUTOINCREMENT,
    content VARCHAR,
    created_at datetime,
    history_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY(history_id) REFERENCES history (id)
);

CREATE INDEX ix_messages_id ON messages (id);

CREATE TABLE prompts (
    id INTEGER NOT NULL AUTOINCREMENT,
    content VARCHAR,
    created_at datetime,
    prompt_history_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY(prompt_history_id) REFERENCES prompt_history (id)
);

CREATE INDEX ix_prompts_id ON prompts (id);

CREATE TABLE responses (
    id INTEGER NOT NULL AUTOINCREMENT,
    content VARCHAR,
    created_at datetime,
    history_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY(history_id) REFERENCES history (id)
);

CREATE INDEX ix_responses_id ON responses (id);

/home/jm/repos/pinecone-langchain-llm/vertexai_loaders/venv_311/lib/python3.11/site-packages/alembic/operations/toimpl.py:50: UserWarning: autoincrement and existing_autoincrement only make sense for MySQL
  operations.impl.alter_column(
ALTER TABLE users ALTER COLUMN id DROP DEFAULT;

ALTER TABLE users ALTER COLUMN id TYPE INTEGER;

ALTER TABLE users ALTER COLUMN created_at TYPE datetime;

CREATE UNIQUE INDEX ix_users_email ON users (email);

CREATE INDEX ix_users_id ON users (id);

INSERT INTO alembic_version (version_num) VALUES ('8419d9b288aa');

(venv_311) 18:58:52 jm@jm:~/repos/pinecone-langchain-llm/vertexai_loaders:
jm/add-alembic:$ alembic upgrade head
INFO  [alembic.runtime.migration] Context impl SnowflakeImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 8419d9b288aa, initial migration
Traceback (most recent call last):
  File "/home/jm/repos/pinecone-langchain-llm/vertexai_loaders/venv_311/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/home/jm/repos/pinecone-langchain-llm/vertexai_loaders/venv_311/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "/home/jm/repos/pinecone-langchain-llm/vertexai_loaders/venv_311/lib/python3.11/site-packages/snowflake/connector/cursor.py", line 910, in execute
    Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
  File "/home/jm/repos/pinecone-langchain-llm/vertexai_loaders/venv_311/lib/python3.11/site-packages/snowflake/connector/errors.py", line 290, in errorhandler_wrapper
    handed_over = Error.hand_to_other_handler(
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jm/repos/pinecone-langchain-llm/vertexai_loaders/venv_311/lib/python3.11/site-packages/snowflake/connector/errors.py", line 345, in hand_to_other_handler
    cursor.errorhandler(connection, cursor, error_class, error_value)
  File "/home/jm/repos/pinecone-langchain-llm/vertexai_loaders/venv_311/lib/python3.11/site-packages/snowflake/connector/errors.py", line 221, in default_errorhandler
    raise error_class(
snowflake.connector.errors.ProgrammingError: 001003 (42000): SQL compilation error:
syntax error line 1 at position 27 unexpected 'ON'.

sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001003 (42000): SQL compilation error:
syntax error line 1 at position 27 unexpected 'ON'.
[SQL: CREATE INDEX ix_history_id ON history (id)]

[sfc-gh-aalam]

@jm-nab you are trying to create index in snowflake which is not supported. Do you know where create index statement is generated from?

[jm-nab]

@sfc-gh-aalam It's created by adding index=True on a field. Is there any clever or more descriptive ways to make it easier to transition a current models/schemas to snowflake?

For example the use case is where there is an app which has a model working on sqlite/postgres, etc. However, its not intuitive the differences in transitioning between the dialects. I think the case could be made that when using sqlalchemy, the transition code for snowflake handling classical RDBMS paradigms are warned and ignored?

Would it be possible to override/modify the compiler such that the transition between the two would be more intuitive? Such as adding warnings that index creation is being skipped because its not needed/supported? If its not supported it would be nice if there was a way to have it automated.

Thanks for the quick response!

[sfc-gh-aling]

hey @jm-nab , I agree with you that it's the dialect's duty to make sure the gaps are properly addressed. e.g., for the case of yours, a user-friendly error shall be raised or this shall just be a no-op to snowflake.

we have tried to cover most of the APIs, but it's challenging to cover all of them, there are cases like parameters will affect the behavior and our tests don't cover.

Is there any clever or more descriptive ways to make it easier to transition a current models/schemas to snowflake?
there isn't such a way at the moment, I would recommend if you encounter such issue, check whether it's supported in snowflake first, and maintain the list of the gaps you identified and let us know.

Would it be possible to override/modify the compiler such that the transition between the two would be more intuitive?
yes, definitely, this should be way how a dialect works, we have defined our customized compiler logic here: https://github.com/snowflakedb/snowflake-sqlalchemy/blob/main/src/snowflake/sqlalchemy/base.py

with this being said, you're more than welcome to make a PR to help improve the lib!

[jm-nab]

@sfc-gh-aling do you know if this sort of stuff would be resolved by using the currently in private preview "hybrid tables"?

For example, instead of using snowflake OLAP/hybrid tables, i've opted to use postgres and a kafka cluster/kafka-connect, since it is a "production ready", tried, and tested path.

Does Snowflake plan to commit any staffing resources to support big libraries like sqlalchemy for the feature parity between dialects?

It was a big factor for me not adopting "hybrid tables", on the project I am currently working on.

Thanks for the responses! Much appreciated.

[sfc-gh-dszmolka]

Hybrid Tables are not supported yet unfortunately for snowflake-sqlalchemy, and at this moment there is no timeline attached to when it would be.

For the rest of the request, I'd rely on Adam's response from earlier and indeed if resources permit, PRs are more than welcome to address the already-identified gaps.

[sfc-gh-dszmolka]

(Hybrid Table support) released with version 1.7.0 in November 2024 release cycle

[jm-nab]

@sfc-gh-dszmolka @sfc-gh-jvasquezrojas Awesome! Thank you!