Skip to main content

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

OptionDescriptionRequired?Example
partition_bypartition by a column, typically a directory per partition is createdNopartition_by=['name']
clustered_bysecond level division of a partitioned columnNoclustered_by=['age']
file_formatunderlying storage format of the table, see https://cwiki.apache.org/confluence/display/Hive/FileFormats for supported formatsNofile_format='PARQUET'
locationstorage location, typically an hdfs pathNoLOCATION='/user/etl/destination'
commentcomment for the tableNocomment='this is the cleanest model'

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
{{
    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?

0
Edit this page