How to insert into VARIANT column?

Bắt đầu bằng cách tái hiện ví dụ Python databricks-sql-connector 4.0.5 bằng executemany với các chuỗi JSON và một dict cho cột VARIANT. Ghi lại dạng tham số nào được hỗ trợ và các insert đã hoàn tất sẽ hiển thị gì đối với schema_of_variant(content), bao gồm hành vi SQL liên quan.

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>.

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?