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.
|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'|
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
with source_data as (
select 1 as id, "Name 1" as name, "City 1" as city,
select 2 as id, "Name 2" as name, "City 2" as city,
select 3 as id, "Name 3" as name, "City 2" as city,
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.