René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"How to insert into VARIANT column?","articleBody":"As we all know, Databricks has a [VARIANT data type](https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-datatypes) 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.\n\nIf you want to write to a [PostgreSQL JSON column in psycopg2](https://stackoverflow.com/a/31796487), you use json dump. This trick did not seem to work here. I also tried custom escaped JSON and simply dict, but also no.\n\n```python\nimport databricks.sql\nimport json\n\n# Replace with your Databricks SQL warehouse details\nserver_hostname = \"\u003cSERVER_HOSTNAME\u003e\"\nhttp_path = \"\u003cHTTP_PATH\u003e\"\naccess_token = \"\u003cACCESS_TOKEN\u003e\"\n\n# Example data to insert\ncontent = {\n    \"age\": 29,\n    \"city\": \"New York\"\n}\ndata = [\n    (1, \"Alice\", json.dumps(content)),\n    2, \"Bob\", None), # (2, \"Bob\", data) # Raises error\n    (3, \"Charlie\", \"\"\"{\"age\": 29, \"city\": \"New York\"}\"\"\"),\n]\n\n# Connect to Databricks SQL\nwith databricks.sql.connect(\n    server_hostname=server_hostname,\n    http_path=http_path,\n    access_token=access_token\n) as connection:\n    with connection.cursor() as cursor:\n        # Insert data into Delta table\n        cursor.execute(\"CREATE OR REPLACE TABLE test_catalog.ad_hoc.variant_test (id INT, name STRING, content VARIANT) USING DELTA\")\n        cursor.executemany(\n            \"INSERT INTO prod_catalog.ad_hoc.variant_test (id, name, content) VALUES (?, ?, ?)\",\n            data\n        )\n```\n\nIf I query the resulting table,\n```sql\nSELECT *, content:age, parse_json(\"{\\\"age\\\": 29, \\\"city\\\": \\\"New York\\\"}\") FROM ad_hoc.variant_test ORDER BY id;\n```\n\n1 and 3 succeeds, but it seems to end up as a \"flat\"`STRING` (`schema_of_variant(content)`) instead of `OBJECT\u003cage: BIGINT, city: STRING\u003e`.\n\n\u003cimg width=\"815\" height=\"255\" alt=\"Image\" src=\"https://github.com/user-attachments/assets/4bc58cfe-1a69-47d9-af25-4eb728a76c57\" /\u003e\n\nAttempt 2 (insert a dict) raises the error\n\n```databricks.sql.exc.ServerOperationError: [DATATYPE_MISMATCH.CAST_WITHOUT_SUGGESTION] Cannot resolve \"content\" due to data type mismatch: cannot cast \"MAP\u003cVOID, VOID\u003e\" to \"VARIANT\". SQLSTATE: 42K09; line 1 pos 0```\n\nCould 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?","author":{"url":"https://github.com/excavator-matt","@type":"Person","name":"excavator-matt"},"datePublished":"2025-08-20T07:35:42.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":1},"url":"https://github.com/681/databricks-sql-python/issues/681"}