bug: ibis-snowflake 8.0.0 insert failure

[yazad3]

What happened?

(Copied from https://ibis-project.zulipchat.com/#narrow/stream/405263-general/topic/ibis-snowflake.208.2E0.2E0.20insert.20failures/near/441896844)

Thus far I have been using create_table without issues in ibis 8.0.0. But I am unable to find examples for insert and all attempts thus far have failed.

Snippets & Errors

Inserting list of dicts

backend.insert(table_name=table_name, obj=[{"ID": 10000,
     "NAME": "....",
     "SCORE_1": 0.2,
     "SCORE_2": 0.5,
     "SCORE_3": 0.75,
     "AGE": 1000,
}])

Fails with,

Error
Traceback (most recent call last):
  File "/Users/.../.../.../.../tests_.../io/test_..._persistence.py", line 33, in test_create_or_append
    backend.insert(table_name=table_name, obj=[
  File "/Users/.../.../.../.../.../lib/python3.10/site-packages/ibis/backends/snowflake/__init__.py", line 1004, in insert
    query = sg.exp.insert(
  File "/Users/.../.../.../.../.../lib/python3.10/site-packages/sqlglot/expressions.py", line 6038, in insert
    this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
  File "/Users/.../.../.../.../.../lib/python3.10/site-packages/sqlglot/expressions.py", line 6038, in <listcomp>
    this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
  File "/Users/.../.../.../.../.../lib/python3.10/site-packages/sqlglot/expressions.py", line 6213, in to_identifier
    raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
ValueError: Name needs to be a string or an Identifier, got: <class 'sqlglot.expressions.Column'>

Same issue with trying to insert a memtable.

records: list[dict[str, Any]] = [
    {
        "ID": i,
        "NAME": str(uuid4()),
        "SCORE_1": random(),
        "SCORE_2": random(),
        "SCORE_3": random(),
        "AGE": randint(10, 20),
    }
    for i in range(0, size)
]

df: Table = ibis.memtable(records)

backend.insert(table_name=table_name, obj=df)

Error
Traceback (most recent call last):
  File "/Users/.../.../.../.../tests_.../io/test_..._persistence.py", line 33, in test_create_or_append
    backend.insert(table_name=table_name, obj=df)
  File "/Users/.../.../.../.../.../lib/python3.10/site-packages/ibis/backends/snowflake/__init__.py", line 1004, in insert
    query = sg.exp.insert(
  File "/Users/.../.../.../.../.../lib/python3.10/site-packages/sqlglot/expressions.py", line 6038, in insert
    this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
  File "/Users/.../.../.../.../.../lib/python3.10/site-packages/sqlglot/expressions.py", line 6038, in <listcomp>
    this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
  File "/Users/.../.../.../.../.../lib/python3.10/site-packages/sqlglot/expressions.py", line 6213, in to_identifier
    raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
ValueError: Name needs to be a string or an Identifier, got: <class 'sqlglot.expressions.Column'>

Inserting just one (record)

backend.insert(table_name=table_name, obj={"ID": 10000,
     "NAME": "....",
     "SCORE_1": 0.2,
     "SCORE_2": 0.5,
     "SCORE_3": 0.75,
     "AGE": 1000,
     })

Error
Traceback (most recent call last):
  File "/Users/.../.../.../.../tests_.../io/test_..._persistence.py", line 33, in test_create_or_append
    backend.insert(table_name=table_name, obj={"ID": 10000,
  File "/Users/.../.../.../.../.../lib/python3.10/site-packages/ibis/backends/snowflake/__init__.py", line 1000, in insert
    obj = ibis.memtable(obj)
  File "/Users/.../.../.../.../.../lib/python3.10/site-packages/ibis/expr/api.py", line 436, in memtable
    return _memtable(data, name=name, schema=schema, columns=columns)
  File "/Users/.../.../.../.../.../lib/python3.10/site-packages/ibis/common/dispatch.py", line 88, in call
    return dispatch(type(arg))(arg, *args, **kwargs)
  File "/Users/.../.../.../.../.../lib/python3.10/site-packages/ibis/expr/api.py", line 472, in _memtable_from_dataframe
    df = pd.DataFrame(data, columns=columns)
  File "/Users/.../.../.../.../.../lib/python3.10/site-packages/pandas/core/frame.py", line 664, in __init__
    mgr = dict_to_mgr(data, index, columns, dtype=dtype, copy=copy, typ=manager)
  File "/Users/.../.../.../.../.../lib/python3.10/site-packages/pandas/core/internals/construction.py", line 493, in dict_to_mgr
    return arrays_to_mgr(arrays, columns, index, dtype=dtype, typ=typ, consolidate=copy)
  File "/Users/.../.../.../.../.../lib/python3.10/site-packages/pandas/core/internals/construction.py", line 118, in arrays_to_mgr
    index = _extract_index(arrays)
  File "/Users/.../.../.../.../.../lib/python3.10/site-packages/pandas/core/internals/construction.py", line 656, in _extract_index
    raise ValueError("If using all scalar values, you must pass an index")
ValueError: If using all scalar values, you must pass an index

What version of ibis are you using?

8.0.0

What backend(s) are you using, if any?

snowflake

Relevant log output

Please see "What happened?" section.

Code of Conduct

• I agree to follow this project's Code of Conduct

[cpcloud]

Can you try with Ibis 9?

[yazad3]

Thank you @cpcloud, I can try to reproduce this with Ibis 9.x.y in a few days and update here.

[yazad3]

Thank you @cpcloud!