# Cloudera Hive configurations

## Configuring tables

When materializing a model as `table`, you may include several optional configs that are specific to the dbt-hive plugin, in addition to the standard [model configs](../model-configs.md).

| Option          | Description                                                                                                                      | Required? | Example                              |
| --------------- | -------------------------------------------------------------------------------------------------------------------------------- | --------- | ------------------------------------ |
| partition\_by   | partition by a column, typically a directory per partition is created                                                            | No        | partition\_by=\['name']              |
| clustered\_by   | second level division of a partitioned column                                                                                    | No        | clustered\_by=\['age']               |
| file\_format    | underlying storage format of the table, see <https://cwiki.apache.org/confluence/display/Hive/FileFormats> for supported formats | No        | file\_format='PARQUET'               |
| location        | storage location, typically an hdfs path                                                                                         | No        | LOCATION='/user/etl/destination'     |
| comment         | comment for the table                                                                                                            | No        | comment='this is the cleanest model' |
| external        | is this an external table - true / false                                                                                         | No        | external=true                        |
| tbl\_properties | any metadata can be stored as key/value pair with the table                                                                      | No        | tbl\_properties="('dbt\_test'='1')"  |
| table\_type     | indicates the type of the table                                                                                                  | No        | table\_type="iceberg"                |

## Incremental models

Supported modes for incremental model:

* **`append`** (default): Insert new records without updating or overwriting any existing data.
* **`insert_overwrite`**: For new records, insert data. When used along with partition clause, update data for changed record and insert data for new records.

## Example: Using partition\_by config option

hive\_partition\_by.sql

```sql
{{
    config(
        materialized='table',
        unique_key='id',
        partition_by=['city'],
    )
}}

with source_data as (
     select 1 as id, "Name 1" as name, "City 1" as city,
     union all
     select 2 as id, "Name 2" as name, "City 2" as city,
     union all
     select 3 as id, "Name 3" as name, "City 2" as city,
     union all
     select 4 as id, "Name 4" as name, "City 1" as city,
)

select * from source_data
```

In the above example, a sample table is created with partition\_by and other config options. One thing to note when using partition\_by option is that the select query should always have the column name used in partition\_by option as the last one, as can be seen for the `city` column name used in the above query. If the partition\_by clause is not the same as the last column in select statement, Hive will flag an error when trying to create the model.

## Was this page helpful?

YesNo

[Privacy policy](https://www.getdbt.com/cloud/privacy-policy)[Create a GitHub issue](https://github.com/dbt-labs/docs.getdbt.com/issues)

This site is protected by reCAPTCHA and the Google [Privacy Policy](https://policies.google.com/privacy) and [Terms of Service](https://policies.google.com/terms) apply.