Dbt-maxcompute

New Features
Raw Materialization Enhancements
* Added new configuration options:
- `schema`: Specifies the default schema for the current query
- `sql_hints`: Allows users to specify SQL hints for the current query

**Note**: Other materialization methods only support schema specification in the dbt.yaml file

Example Usage:
sql
{{ config(
materialized="raw",
schema="new_schema",
sql_hints={"odps.sql.submit.mode": "script"}
) }}

create schema new_schema;
create table table_in_new_schema(c1 bigint);


Bug Fixes
* Fixed logical issues in the bq_insert_overwrite incremental materialization strategy and improved execution efficiency

New Features
- **Compatibility with dbt-core 1.9.x**: This version has been adapted to work seamlessly with dbt-core version 1.9.x.
- **New Incremental Materialization Strategies**:
- **microbatch**
- **bq_insert_overwrite**
- **bq_microbatch**

- **New Materialization Method**:
- **raw**: A new materialization method that supports manual SQL execution by users.

Note
Due to the distinct nature of the incremental materialization methods offered by `dbt-bigquery`, the micro provided in the `dbt-adapters` has been deeply customized to reduce the costs associated with using incremental materialization on partitioned tables. As a result, `bq_insert_overwrite` and `bq_microbatch` have been added to mimic the materialization method found in `dbt-bigquery`.

Key Differences
- **insert_overwrite**: This method removes duplicates based on specified `unique_keys`.
- **bq_insert_overwrite**: This method removes duplicates based on the `partition_by` condition and allows for specifying which `partitions` to work with.

Usage Examples

`bq_insert_overwrite` (Static)
sql
{{ config(
materialized='incremental',
partition_by={"fields": "some_date", "data_types": "timestamp"},
partitions=["TIMESTAMP'2024-10-10 00:00:00'", "TIMESTAMP'2025-01-01 00:00:00'"],
incremental_strategy='bq_insert_overwrite'
) }}
select * from {{ source('raw', 'seed') }}
{% if is_incremental() %}
where {{ dbt.datediff("some_date", "TIMESTAMP'2000-01-01 00:00:00'", 'day') }} > 0
{% endif %}


`bq_insert_overwrite` (Dynamic)
sql
{{ config(
materialized='incremental',
partition_by={"fields": "some_date", "data_types": "timestamp"},
incremental_strategy='bq_insert_overwrite'
) }}
select * from {{ source('raw', 'seed') }}
{% if is_incremental() %}
where {{ dbt.datediff("some_date", "TIMESTAMP'2000-01-01 00:00:00'", 'day') }} > 0
{% endif %}


Raw Materialization Example
sql
{{ config(
materialized='raw'
) }}
create table test(c1 bigint) lifecycle 1;


This release enhances the functionality of dbt-maxcompute significantly by providing improved incremental strategies and a new raw materialization method, making it easier for users to manage their data transformations effectively.

New Features

1. Support for Creating Ordinary Partition Tables via `partition_py`
Users can now utilize `partition_py` to create ordinary partition tables for better data management and querying.

2. Support for Creating Materialized Views
Support for materialized views has been introduced, enabling users to query and manage data more efficiently.

Usage

Creating an Ordinary Partition Table
To create an ordinary partition table, you can use the following configuration:

sql
{{ config(
materialized='table',
partition_by={"fields": "name,some_date", "data_types": "string,string"}
) }}
select id, name, some_date from {{ source('raw', 'seed') }}

Schema of the materialized table like:
sql
create table model(id bigint) partitioned by(name string, some_date string)


Creating an Auto-Partitioned Table
Here is an example configuration for creating an auto-partitioned table:

sql
{{ config(
materialized='table',
partition_by={"fields": "some_date", "data_types": "timestamp", "granularity": "day"}
) }}
select id, name, some_date from {{ source('raw', 'seed') }}

Schema of the materialized table like:
sql
create table model(id bigint, name string, some_date timestamp) auto partitioned by trunc_time(some_date, "day");


Creating a Materialized View
The configuration for creating a materialized view is as follows:

sql
{{ config(
materialized='materialized_view',
lifecycle=1,
build_deferred=True,
columns=["id", "name", "some_date"],
column_comment={"id": "this is id.", "name": "this is name."},
disable_rewrite=True,
table_comment="this is a materialized view.",
tblProperties={"compressionstrategy":"normal"}
) }}
select * from {{ source('raw', 'seed') }}

For the meanings of each field, please refer to the [Alibaba Cloud MaxCompute User Guide](https://help.aliyun.com/zh/maxcompute/user-guide/materialized-view-operation).

Limitations

1. The current version does not support specifying partitions when refreshing materialized views.
2. Materialized views do not support the `rename` operation.
3. It is not possible to switch to another materialization method without dropping the materialized table.

New Features
- Support for specifying `partition_py` during table materialization to automatically create auto-partitioned tables.
- Introduced a new `date_spine` macro for generating date dimension tables.
- Modified the `dateadd` macro to add support for hour and week partitions.
- Refactored the `datediff` macro to simplify it and support more date intervals.

**Full Changelog**: https://github.com/aliyun/dbt-maxcompute/compare/v1.8.0-a11...v1.8.0-a12

New Features
* Add support for `insert_overwrite` strategy to support the creation of auto-partition tables