Search code examples
azuredatabrickscolumnstoresql-data-warehouse

disable column store in azure sql dataware house

I am writing a dataframe from Azure Databricks onto a sql dataware house with

res.write \
    .format("jdbc") \
    .option("url", url) \
    .option("dbtable", table) \
    .option("user", user) \
    .option("password", password) \
    .save()

with this I am getting an error Column 'username' has a data type that cannot participate in a columnstore index.

How can I either eliminate the column store completely, or change the data type of the columns to fit in the column store?

I have a few columns that will be considered as integer and some that will be varchar.

Solution

  • Add this option clause to your write statement. It takes the place of the with() clause of the CREATE TABLE (AS) statement:

    .option ("tableOptions","heap,distribution=MY_DISTRIBUTION")

    Reference is here:

    https://docs.databricks.com/spark/latest/data-sources/azure/sql-data-warehouse.html

    Choose a value for MY_DISTRIBUTION based on the following guidance:

    https://learn.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-distribute