How to insert into VARIANT column?

未关闭
  • 哪里不对？
#681 1 条评论 0 个 reaction 已指派 0 人 在 GitHub 查看

维护者通常 1 天内回复

还没有人认领这个 Issue。

评估

难度
2/5
预计耗时
1-3 小时
新手友好度
35/100
Issue 类型
文档
描述清晰度
基本清楚
活跃度
停滞
技术栈
python, sql
领域
databases, documentation

调研方向

首先，使用 executemany、JSON 字符串和用于 VARIANT 列的 dict，复现 Python databricks-sql-connector 4.0.5 示例。记录支持哪种参数形式，以及已完成的插入对于 schema_of_variant(content) 应显示什么，包括相关的 SQL 行为。

由索引模型根据 Issue 内容生成。

描述

As we all know, Databricks has a VARIANT data type that is more performant than standard JSON strings for nested data. However, I don't understand how I am supposed to insert into such a column using databricks-sql-connector==4.0.5 though.

If you want to write to a PostgreSQL JSON column in psycopg2, you use json dump. This trick did not seem to work here. I also tried custom escaped JSON and simply dict, but also no.

import databricks.sql
import json

# Replace with your Databricks SQL warehouse details
server_hostname = "<SERVER_HOSTNAME>"
http_path = "<HTTP_PATH>"
access_token = "<ACCESS_TOKEN>"

# Example data to insert
content = {
    "age": 29,
    "city": "New York"
}
data = [
    (1, "Alice", json.dumps(content)),
    2, "Bob", None), # (2, "Bob", data) # Raises error
    (3, "Charlie", """{"age": 29, "city": "New York"}"""),
]

# Connect to Databricks SQL
with databricks.sql.connect(
    server_hostname=server_hostname,
    http_path=http_path,
    access_token=access_token
) as connection:
    with connection.cursor() as cursor:
        # Insert data into Delta table
        cursor.execute("CREATE OR REPLACE TABLE test_catalog.ad_hoc.variant_test (id INT, name STRING, content VARIANT) USING DELTA")
        cursor.executemany(
            "INSERT INTO prod_catalog.ad_hoc.variant_test (id, name, content) VALUES (?, ?, ?)",
            data
        )

If I query the resulting table,

SELECT *, content:age, parse_json("{\"age\": 29, \"city\": \"New York\"}") FROM ad_hoc.variant_test ORDER BY id;

1 and 3 succeeds, but it seems to end up as a "flat"STRING (schema_of_variant(content)) instead of OBJECT<age: BIGINT, city: STRING>.

Image

Attempt 2 (insert a dict) raises the error

databricks.sql.exc.ServerOperationError: [DATATYPE_MISMATCH.CAST_WITHOUT_SUGGESTION] Cannot resolve "content" due to data type mismatch: cannot cast "MAP<VOID, VOID>" to "VARIANT". SQLSTATE: 42K09; line 1 pos 0

Could we add documentation for how this is supposed to work? I guess MAP, STRUCT and VARIANT all map to Python type dict in some sense. It is only that VARIANT don't have a predefined schema like MAP or STRUCT, and unlike MAP can be nested?

主要语言
Python
星标
236
派生
162
平均合并
13 小时 13 分钟
30 天内合并 PR
23

环境准备

从这里开始

  1. 先读完整个 Issue，再读项目的贡献指南。
  2. 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
  3. Fork 仓库，在一个分支上完成修改。
  4. 提交 Pull Request，并在描述里引用这个 Issue 编号。

databricks/databricks-sql-python 的其他 Issue

查看 databricks/databricks-sql-python 的全部 Issue

相似的 Issue

更多 Python Issue

把新 issue 发到你的邮箱

精选适合新手参与的 GitHub issue 摘要。