Skip to main content
Last updated on

Variant type support

Preview

This feature is in Public Preview.

The VARIANT data type stores semi-structured data. For examples on working with VARIANT, see Query variant data.

You must use Databricks Runtime 15.3 or above to read and write tables with variant support enabled.

Enable variant on a table​

To enable variant, create a new table with a VARIANT type column, for example:

SQL
CREATE TABLE table_name (variant_column VARIANT)

To enable support for VARIANT on an existing table, use the following syntax:

SQL
ALTER TABLE table_name SET TBLPROPERTIES('delta.feature.variantType-preview' = 'supported')
warning

When you enable variant, Databricks upgrades the table protocol. Clients that don't support variant can't read the table. See Delta Lake feature compatibility and protocols.

Limitations​

The following limitations exist:

  • You can't use variant columns to partition a table.
  • A variant column can't be a clustering key for a table.
  • You can't use variant columns with GROUP BY or ORDER BY clauses.
  • You can't call DISTINCT on a variant column.
  • You can't use SQL set operators (INTERSECT, UNION, EXCEPT) with variant columns.
  • You can't use column generation to create a variant column.
  • Variant columns don't support minValues or maxValues statistics.
  • A variant column can't contain a value larger than 128 MiB (16 MiB in Databricks Runtime 17.1 and below).