Dbt-maxcompute

New Features

- **Micro Related to `apply_grants.sql`:**
- Added micros that supports MaxCompute permission operations, enabling flexible management of data permissions through SQL statements for granting and revoking specific user or group permissions.

- **`create_or_replace_clone`:**
- Implemented a new micro that supports the `dbt clone` operation, allowing users to quickly create modified or variant models from existing ones.

- **`persist_doc`:**
- Introducing a new micro that supports persisting descriptions into a `comments` table in the database, allowing for the permanent storage of relevant document information for future reference.

- **`query_header`:**
- Added support for `query_header`, enabling users to include custom header information during SQL query execution for additional context and improved debugging.

- **Various Bug Fixes:**
- Addressed multiple known issues to enhance system stability and reliability, including error handling improvements and performance optimizations.

New Features

- **Enhanced Type System:**
- Added several MaxCompute type aliases:
- `"INTEGER" -> "INT"`
- `"BOOL" -> "BOOLEAN"`
- `"NUMERIC" -> "DECIMAL"`
- `"REAL" -> "FLOAT"`
- The NUMERIC type is now converted to the DECIMAL type in MaxCompute.
- Fixed implementations of dbt's `is_string`, `is_integer`, and `is_numeric` methods.

- **Updated Constraint Support Matrix:**
- `check`: Not supported
- `unique`: Not supported
- `primary_key`: Not supported
- `foreign_key`: Not supported
- `not_null`: Supported and enforced

- **New Incremental Strategy Support:** Now supports the following three incremental strategies:
- `append`
- `merge` (requires unique key)
- `delete + insert` (requires unique key)
- **Note:** When a unique key is specified, the materialized table will be created as a transactional table.

- **Using DataFrame API for dbt seed Table Operations:** Added a multiple retry mechanism.

- **Enhanced `create_table_as_micro` Feature:** Supports constraint handling and can create Delta Tables by specifying `primary_keys` and `delta_table_bucket_num` in the configuration.

- **Fixed Catalog Creation Accuracy:**
- Column indexing changed from starting at 0 to starting at 1.
- Ability to recognize view and table types.

- **Fixed Logic Issue in `validate_sql`.**

This version has been validated and passed a total of **58** tests.

New Features
- Added support for the following SQL syntax, and add the corresponding 37 tests:
- `validate_sql`
- `any_value`
- `array_append`
- `array_concat`
- `array_construct`
- `bool_or`
- `cast`
- `cast_bool_to_text`
- `concat`
- `date`
- `get_intervals_between`
- `date_spine`
- `date_trunc`
- `dateadd`
- `datediff`
- `escape_single_quotes`
- `hash`
- `last_day`
- `listagg`
- `position`
- `right`
- `split_part`

Changes
- All `string` types are now standardized to lowercase.
- The timezone for date types is set to UTC.
- The method for reading CSV tables with `dbt seed` has been changed from a combination of SQL to the PyOdps DataFrame API, significantly improving the efficiency of loading tables from files.

We encourage users to download and try the new version, and we welcome feedback to help us continue improving!

**Summary:**
We are pleased to announce the release of dbt-maxcompute version 1.8.0-a7, which represents the first planned version of the MC dbt connector. This release successfully passes 10 basic tests as defined by dbt, covering key user scenarios. Please note that the current version does not support specific features of MaxCompute, such as partitioning, clustering, or tiered storage capabilities.

**Links:**
- [GitHub Repository](https://github.com/aliyun/dbt-maxcompute)
- [Python Package Index](https://pypi.org/project/dbt-maxcompute/1.8.0a7/)

Detailed Description
The Basic Tests included in this release encompass the following key scenarios:

- Validation of dbt’s ability to create and transform data under simple materialization settings while ensuring consistency.
- Verification of dbt's handling of simple SQL tests (both successful and failed) to confirm expected outcomes.
- Testing of ephemeral models along with their related SQL testing files to ensure correct creation, execution, and validation.
- Assessment of dbt's effect when running with the `--empty` parameter.
- Validation of outputs from running ephemeral models, including table data, documentation directory, and manifest file results.
- Confirmation of dbt's expected behavior when modifying data within incremental model tables.
- Utilization of pytest's fixture functionality in dbt to validate operations such as seed, run, and test.
- Testing of dbt snapshot functionality, verifying successful creation of snapshots using `run_dbt(["snapshot"])`, and checking the row counts against expectations using `check_relation_rows`.
- Ensuring the correctness of snapshot functions under various data change scenarios.
- Dynamic generation and execution of SQL files, along with validation of consistency in the actual data within MaxCompute.

Usage Limitations
Please be aware of the following limitations in this version:

- Does not support the creation and use of MaxCompute partitioned tables, clustered tables, external tables, or Delta tables.
- For data updates that modify existing records within a table, the tables involved must be transactional tables.
- A new definition for creating transactional tables has been introduced in dbt-maxcompute, as shown below:

sql
{{ config(
materialized='table',
transactional=true
) }}

select c_custkey as customer_id,
c_name as customer_name,
c_phone as customer_phone
from BIGDATA_PUBLIC_DATASET.tpch_10g.customer


We encourage you to try out the new features and provide feedback to help us improve the connector further!

✨ New Features

1. Extended Default SQL Hints
We've added two new parameters to the default MaxCompute configuration:

json
// Newly added configurations
"odps.sql.groupby.position.alias": "true",
"odps.sql.hive.compatible": "true"


Full default configuration list:
json
{
"odps.sql.type.system.odps2": "true",
"odps.sql.decimal.odps2": "true",
"odps.sql.hive.compatible": "true",
"odps.sql.allow.fullscan": "true",
"odps.sql.select.output.format": "csv",
"odps.sql.submit.mode": "script",
"odps.sql.allow.cartesian": "true",
"odps.sql.timezone": "GMT",
"odps.sql.allow.schema.evolution": "true",
"odps.sql.groupby.position.alias": "true"
}


Configuration details:
- `groupby.position.alias`: Enables GROUP BY positional alias support
- `hive.compatible`: Activates enhanced Hive syntax compatibility mode

✨ New Features
1. **Enhanced Relation Querying**
Added `get_relations_by_pattern` and `get_relations_by_prefix` methods to the adapter, enabling filtering of `MaxComputeRelation` objects from the database using wildcard or prefix conditions. This improves flexibility for complex querying scenarios.

---

🛠️ Bug Fixes
1. **Connection Stability Improvement**
Fixed an issue where the `get_odps_client` method could fail during initial startup due to missing connection initialization. The updated logic now attempts to reuse existing connections and automatically creates a new one if none exists, ensuring stability during initialization.

2. **Decimal Type Precision Fix**
Resolved potential precision loss issues with `dbt seed` operations for Decimal types, ensuring numerical precision is preserved during data ingestion to avoid calculation discrepancies.

---

📝 Other Improvements
- Enhanced code robustness with improved exception handling in connection management logic.

---

**Note**: This is an Alpha pre-release version. Please validate in a test environment before deploying to production. Report any issues via [GitHub Issues](https://github.com/aliyun/dbt-maxcompute/issues).