### Install dbt-maxcompute Plugin Source: https://github.com/aliyun/dbt-maxcompute/blob/master/README.md Instructions for installing the dbt-maxcompute plugin using conda and pip. It demonstrates setting up a Python 3.9 environment and installing dbt-core and dbt-maxcompute. ```bash conda create --name dbt-maxcompute-example python=3.9 conda activate dbt-maxcompute-example pip install dbt-core pip install dbt-maxcompute ``` -------------------------------- ### Set Up Development Environment Source: https://github.com/aliyun/dbt-maxcompute/blob/master/README.md Installs the necessary dependencies to set up the development environment for the dbt-maxcompute adapter. This command should be run from the project's root directory. ```bash pip install -r dev-requirements.txt ``` -------------------------------- ### dbt-maxcompute Configuration Source: https://github.com/aliyun/dbt-maxcompute/blob/master/docs/authentication.md Example dbt_project.yml configuration for MaxCompute, specifying project, schema, endpoint, and authentication type. ```yaml jaffle_shop: # this needs to match the profile in your dbt_project.yml file target: dev outputs: dev: type: maxcompute project: dbt-example # Replace this with your project name schema: default # Replace this with schema name, e.g. dbt_bilbo endpoint: http://service.cn-shanghai.maxcompute.aliyun.com/api # Replace this with your maxcompute endpoint auth_type: chain ``` -------------------------------- ### dbt MaxCompute Profile Configuration Source: https://github.com/aliyun/dbt-maxcompute/blob/master/README.md Example configuration for the profiles.yml file to connect dbt to Aliyun MaxCompute. This includes project details, schema, endpoint, and authentication credentials. ```yaml jaffle_shop: # this needs to match the profile in your dbt_project.yml file target: dev outputs: dev: type: maxcompute project: dbt-example # Replace this with your project name schema: default # Replace this with schema name, e.g. dbt_bilbo endpoint: http://service.cn-shanghai.maxcompute.aliyun.com/api # Replace this with your maxcompute endpoint auth_type: access_key access_key_id: XXX # Replace this with your accessId(ak) access_key_secret: XXX # Replace this with your accessKey(sk) ``` -------------------------------- ### Environment Variable Credential Loading (Python Example) Source: https://github.com/aliyun/dbt-maxcompute/blob/master/docs/authentication.md Illustrates how the credentials-python library loads credentials from environment variables like ALIBABA_CLOUD_ACCESS_KEY_ID, ALIBABA_CLOUD_ACCESS_KEY_SECRET, and ALIBABA_CLOUD_SECURITY_TOKEN. ```python # Example of how environment variables are checked (conceptual) # from alibabacloud_credentials.client import Client # access_key_id = os.environ.get('ALIBABA_CLOUD_ACCESS_KEY_ID') # access_key_secret = os.environ.get('ALIBABA_CLOUD_ACCESS_KEY_SECRET') # security_token = os.environ.get('ALIBABA_CLOUD_SECURITY_TOKEN') # if access_key_id and access_key_secret: # # Use Access Key or STS credentials ``` -------------------------------- ### Install Latest dbt Adapter Changes Source: https://github.com/aliyun/dbt-maxcompute/blob/master/dev-requirements.txt Installs the latest development versions of dbt-adapters, dbt-common, and dbt-core from GitHub. This is useful for testing the newest features and bug fixes. ```git git+https://github.com/dbt-labs/dbt-adapters.git#subdirectory=dbt-adapters git+https://github.com/dbt-labs/dbt-adapters.git#subdirectory=dbt-tests-adapter git+https://github.com/dbt-labs/dbt-common.git git+https://github.com/dbt-labs/dbt-core.git@1.9.latest#egg=dbt-core&subdirectory=core ``` -------------------------------- ### Access Key Authentication Configuration Source: https://github.com/aliyun/dbt-maxcompute/blob/master/docs/authentication.md Configuration example for authenticating with dbt-maxcompute using an Access Key. This method requires `access_key_id` and `access_key_secret`. ```yaml jaffle_shop: # this needs to match the profile in your dbt_project.yml file target: dev outputs: dev: type: maxcompute project: dbt-example # Replace this with your project name schema: default # Replace this with schema name, e.g. dbt_bilbo endpoint: http://service.cn-shanghai.maxcompute.aliyun.com/api # Replace this with your maxcompute endpoint auth_type: access_key # credential type, Optional, default is 'access_key' access_key_id: accessKeyId # AccessKeyId access_key_secret: accessKeySecret # AccessKeySecret ``` -------------------------------- ### MaxCompute Specific Dependencies Source: https://github.com/aliyun/dbt-maxcompute/blob/master/dev-requirements.txt Installs core packages for interacting with MaxCompute, including data manipulation with pandas, the pyodps SDK, and Alibaba Cloud credentials management. ```python pandas~=2.2.3 pyodps~=0.12.0 alibabacloud_credentials~=0.3.6 ``` -------------------------------- ### Build Dependencies Source: https://github.com/aliyun/dbt-maxcompute/blob/master/dev-requirements.txt Installs packages required for building and distributing the dbt-maxcompute project. Includes version management and packaging tools. ```python bumpversion~=0.6.0 twine~=5.1 wheel~=0.43 ``` -------------------------------- ### Development Dependencies Source: https://github.com/aliyun/dbt-maxcompute/blob/master/dev-requirements.txt Installs essential packages for local development and testing of the dbt-maxcompute project. Includes tracing, pre-commit hooks, and various pytest plugins for enhanced testing capabilities. ```python ddtrace==2.3.0 pre-commit~=3.7.0 pytest~=7.4 pytest-csv~=3.0 pytest-dotenv~=0.5.2 pytest-logbook~=1.2 pytest-xdist~=3.6 tox~=4.11 ``` -------------------------------- ### STS Authentication Configuration Source: https://github.com/aliyun/dbt-maxcompute/blob/master/docs/authentication.md Configuration example for authenticating with dbt-maxcompute using Security Token Service (STS). This method requires `access_key_id`, `access_key_secret`, and `security_token`. ```yaml jaffle_shop: # this needs to match the profile in your dbt_project.yml file target: dev outputs: dev: type: maxcompute project: dbt-example # Replace this with your project name schema: default # Replace this with schema name, e.g. dbt_bilbo endpoint: http://service.cn-shanghai.maxcompute.aliyun.com/api # Replace this with your maxcompute endpoint auth_type: sts # credential type access_key_id: accessKeyId # AccessKeyId access_key_secret: accessKeySecret # AccessKeySecret security_token: securityToken # STS Token ``` -------------------------------- ### RAM Role ARN Authentication Configuration Source: https://github.com/aliyun/dbt-maxcompute/blob/master/docs/authentication.md Configuration example for authenticating with dbt-maxcompute using a RAM Role ARN. This method supports automatic STS Token renewal and optional policy and session name configurations. ```yaml jaffle_shop: # this needs to match the profile in your dbt_project.yml file target: dev outputs: dev: type: maxcompute project: dbt-example # Replace this with your project name schema: default # Replace this with schema name, e.g. dbt_bilbo endpoint: http://service.cn-shanghai.maxcompute.aliyun.com/api # Replace this with your maxcompute endpoint auth_type: ram_role_arn # credential type access_key_id: accessKeyId # AccessKeyId access_key_secret: accessKeySecret # AccessKeySecret security_token: securityToken # STS Token role_arn: roleArn # Format: acs:ram::USER_ID:role/ROLE_NAME role_session_name: roleSessionName # Role Session Name auth_policy: policy # Not required, limit the permissions of STS Token role_session_expiration: 3600 # Not required, limit the Valid time of STS Token ``` -------------------------------- ### dbt Run Execution Output Source: https://github.com/aliyun/dbt-maxcompute/blob/master/docs/Tutorial.md Simulates the output of running `dbt run`, showing the successful creation of tables/views for `stg_customers`, `stg_orders`, and `customers`, demonstrating dbt's automatic dependency management and execution order. ```bash 1 of 3 START sql table model dbt_project.default.stg_customers ................. [RUN] 1 of 3 OK created sql table model dbt_project.default.stg_customers ............ [OK in 9.39s] 2 of 3 START sql table model dbt_project.default.stg_orders .................... [RUN] 2 of 3 OK created sql table model dbt_project.default.stg_orders ............... [OK in 13.49s] 3 of 3 START sql table model dbt_project.default.customer ...................... [RUN] 3 of 3 OK created sql table model dbt_project.default.customer ................. [OK in 12.50s] Finished running 3 table models in 0 hours 0 minutes and 47.17 seconds (47.17s). Completed successfully Done. PASS=3 WARN=0 ERROR=0 SKIP=0 TOTAL=3 ``` -------------------------------- ### DBT Run Output for Customer Model Source: https://github.com/aliyun/dbt-maxcompute/blob/master/docs/Tutorial.md This output shows the result of running the DBT project, specifically the creation of the 'customer' model as a SQL view in MaxCompute. It indicates the successful execution and completion time. ```bash 1 of 1 START sql view model dbt_project.default.customer ....................... [RUN] 1 of 1 OK created sql view model dbt_project.default.customer .................. [OK in 2.78s] Finished running 1 view model in 0 hours 0 minutes and 14.53 seconds (14.53s). Completed successfully Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1 ``` -------------------------------- ### Core Model: customers.sql Source: https://github.com/aliyun/dbt-maxcompute/blob/master/docs/Tutorial.md Creates a core model that joins customer and order data to derive insights like the first and most recent order dates, and the total number of orders per customer. It utilizes staging models via `{{ ref() }}`. ```sql with customers as ( select * from {{ ref('stg_customers') }} ), orders as ( select * from {{ ref('stg_orders') }} ), customer_orders as ( select customer_id, min(order_date) as first_order_date, max(order_date) as most_recent_order_date, count(order_id) as number_of_orders from orders group by customer_id ), final as ( select customers.customer_id, customers.customer_name, customers.customer_phone, customer_orders.first_order_date, customer_orders.most_recent_order_date, coalesce(customer_orders.number_of_orders, 0) as number_of_orders from customers left join customer_orders on customers.customer_id=customer_orders.customer_id ) select * from final ``` -------------------------------- ### dbt-MaxCompute SQL Hints Source: https://github.com/aliyun/dbt-maxcompute/blob/master/README.md Configures SQL hints for MaxCompute queries to control behavior and optimize performance. Custom hints merge with system defaults. ```APIDOC dbt-MaxCompute SQL Hints: - sql_hints: Map[String,String] SQL hints applied to all queries for optimization or compatibility. Default SQL Hints: ```yaml odps.sql.type.system.odps2: "true" odps.sql.decimal.odps2: "true" odps.sql.allow.fullscan: "true" odps.sql.select.output.format: "csv" odps.sql.submit.mode: "script" odps.sql.allow.cartesian: "true" odps.sql.allow.schema.evolution: "true" odps.table.append2.enable": "true" ``` Custom hints override defaults. Specify only values you wish to change. ``` -------------------------------- ### Create Customer Model in MaxCompute SQL Source: https://github.com/aliyun/dbt-maxcompute/blob/master/docs/Tutorial.md This SQL script defines a customer model by joining customer information with their order history from the TPCH dataset. It calculates the first and last order dates and the total number of orders for each customer. The output is a view named 'customer'. ```sql -- models/customer.sql with customers as ( select c_custkey as customer_id, c_name as customer_name, c_phone as customer_phone from BIGDATA_PUBLIC_DATASET.tpch_10g.customer ), orders as ( select o_orderkey as order_id, o_custkey as customer_id, o_orderdate as order_date, o_orderstatus as status from BIGDATA_PUBLIC_DATASET.tpch_10g.orders ), customer_orders as ( select customer_id, min(order_date) as first_order_date, max(order_date) as most_recent_order_date, count(order_id) as number_of_orders from orders group by customer_id ), final as ( select customers.customer_id, customers.customer_name, customers.customer_phone, customer_orders.first_order_date, customer_orders.most_recent_order_date, coalesce(customer_orders.number_of_orders, 0) as number_of_orders from customers left join customer_orders on customers.customer_id=customer_orders.customer_id ) select * from final ``` -------------------------------- ### dbt Model Documentation Configuration Source: https://github.com/aliyun/dbt-maxcompute/blob/master/docs/Tutorial.md Enhances the dbt schema YAML file by adding descriptions to models and their columns. This metadata is used by `dbt docs generate` to create project documentation. ```yaml version: 2 models: - name: customers description: One record per customer columns: - name: customer_id description: Primary key tests: - unique - not_null - name: first_order_date description: NULL when a customer has not yet placed an order. - name: stg_customers description: This model cleans up customer data columns: - name: customer_id description: Primary key tests: - unique - not_null - name: stg_orders description: This model cleans up order data columns: - name: order_id description: Primary key tests: - unique - not_null - name: status tests: - accepted_values: values: ['F', 'O', 'P'] - name: customer_id tests: - not_null - relationships: to: ref('stg_customers') field: customer_id ``` -------------------------------- ### dbt-MaxCompute Table Configurations Source: https://github.com/aliyun/dbt-maxcompute/blob/master/README.md Configures MaxCompute tables within dbt, including properties like transactional support, primary keys, partitioning, and lifecycle management. ```APIDOC dbt-MaxCompute Table Configurations: - tblproperties: Map[String,String] Additional table properties. Example: `{'table.format.version'='2'}` creates an Append2 table. - transactional: Boolean (default: `false`) Equivalent to `tblproperties ('transactional' = 'true')`. Indicates whether to create a transactional table. - delta: Boolean (default: `false`) Same to `transactional`, with additional primary key validation. - primary_keys: List[String] List of primary key column names (e.g., `['c1']`). Required when `delta=true`. - delta_table_bucket_num: Integer (default: `16`) Equivalent to `tblproperties ('write.bucket.num' = 'xx')`. Controls bucket count for Delta tables. - partition_by: Map Defines partitioning strategy with two fields: • `fields`: Comma-separated partition columns • `data_types`: Optional data types (default: `string`). When specifying time types (`date`, `datetime`, `timestamp`), creates auto-partitioned tables. Example: `{"fields": "name,some_date", "data_types": "string,string"}` - lifecycle: Integer Table retention period in days (e.g., `30` for 30-day lifecycle). ``` -------------------------------- ### dbt MaxCompute Connection Parameters Source: https://github.com/aliyun/dbt-maxcompute/blob/master/README.md Reference for parameters used in the dbt profiles.yml file for MaxCompute connections. Details include the field name, its description, and default values or requirements. ```APIDOC dbt MaxCompute Connection Parameters: - type: The type of database connection. Must be set to "maxcompute" for MaxCompute connections. Default Value: "maxcompute" - project: The name of your MaxCompute project. Required (no default) - endpoint: The endpoint URL used to connect to MaxCompute. Required (no default) - schema: The namespace schema that the models will use in MaxCompute. Required (no default) - auth_type: Authentication method for accessing MaxCompute. Default Value: "access_key" - access_key_id: Access ID used for authentication. Required if using access key auth - access_key_secret: Access Key Secret used for authentication. Required if using access key auth - timezone: The Timezone used for MaxCompute. Default Value: "GMT" - tunnel_endpoint: The tunnel endpoint URL used to fetch result from MaxCompute. Default Value: Auto detected by endpoint - Other auth options: Alternative authentication methods such as STS. See [Authentication Configuration](docs/authentication.md). Varies by auth type ``` -------------------------------- ### MaxCompute Credentials File Configuration Source: https://github.com/aliyun/dbt-maxcompute/blob/master/docs/authentication.md Configuration options for the MaxCompute credentials file (~/.alibabacloud/credentials.ini), supporting various authentication types like access_key, ecs_ram_role, ram_role_arn, and oidc_role_arn. ```ini [default] # default setting enable = true # Enable，Enabled by default if this option is not present type = access_key # Certification type: access_key access_key_id = foo # Key access_key_secret = bar # Secret [client1] # configuration that is named as `client1` type = ecs_ram_role # Certification type: ecs_ram_role role_name = EcsRamRoleTest # Role Name [client2] # configuration that is named as `client2` enable = false # Disable type = ram_role_arn # Certification type: ram_role_arn region_id = cn-test policy = test # optional Specify permissions access_key_id = foo access_key_secret = bar role_arn = role_arn role_session_name = session_name # optional [client3] # configuration that is named as `client3` enable = false # Disable type = oidc_role_arn # Certification type: oidc_role_arn region_id = cn-test policy = test # optional Specify permissions access_key_id = foo # optional access_key_secret = bar # optional role_arn = role_arn oidc_provider_arn = oidc_provider_arn oidc_token_file_path = /xxx/xxx # can be replaced by setting environment variable: ALIBABA_CLOUD_OIDC_TOKEN_FILE role_session_name = session_name # optional ``` -------------------------------- ### Staging Model: stg_customers.sql Source: https://github.com/aliyun/dbt-maxcompute/blob/master/docs/Tutorial.md Defines the staging model for customer data, selecting and aliasing relevant columns from the source table. This model serves as a clean layer for raw customer information. ```sql select c_custkey as customer_id, c_name as customer_name, c_phone as customer_phone from BIGDATA_PUBLIC_DATASET.tpch_10g.customer ``` -------------------------------- ### dbt Test Execution Output Source: https://github.com/aliyun/dbt-maxcompute/blob/master/docs/Tutorial.md Shows the output of running `dbt test`, indicating the status of each data quality test executed against the models. Tests can pass or fail based on the defined criteria. ```bash 1 of 7 START test accepted_values_stg_orders_status__F__O__P ................... [RUN] 1 of 7 PASS accepted_values_stg_orders_status__F__O__P ......................... [PASS in 4.83s] 2 of 7 START test not_null_stg_customers_customer_id ........................... [RUN] 2 of 7 PASS not_null_stg_customers_customer_id ................................. [PASS in 3.05s] 3 of 7 START test not_null_stg_orders_customer_id .............................. [RUN] 3 of 7 PASS not_null_stg_orders_customer_id .................................... [PASS in 2.23s] 4 of 7 START test not_null_stg_orders_order_id ................................. [RUN] 4 of 7 PASS not_null_stg_orders_order_id ....................................... [PASS in 1.56s] 5 of 7 START test relationships_stg_orders_customer_id__customer_id__ref_stg_customers_ [RUN] 5 of 7 PASS relationships_stg_orders_customer_id__customer_id__ref_stg_customers_ [PASS in 6.71s] 6 of 7 START test unique_stg_customers_customer_id ............................. [RUN] 6 of 7 PASS unique_stg_customers_customer_id ................................... [PASS in 2.89s] 7 of 7 START test unique_stg_orders_order_id ................................... [RUN] 7 of 7 PASS unique_stg_orders_order_id ......................................... [PASS in 8.48s] Finished running 7 data tests in 0 hours 0 minutes and 40.23 seconds (40.23s). Completed successfully Done. PASS=7 WARN=0 ERROR=0 SKIP=0 TOTAL=7 ``` -------------------------------- ### Staging Model: stg_orders.sql Source: https://github.com/aliyun/dbt-maxcompute/blob/master/docs/Tutorial.md Defines the staging model for order data, selecting and aliasing relevant columns from the source table. This model provides a clean layer for raw order information. ```sql select o_orderkey as order_id, o_custkey as customer_id, o_orderdate as order_date, o_orderstatus as status from BIGDATA_PUBLIC_DATASET.tpch_10g.orders ``` -------------------------------- ### dbt Model Tests Configuration Source: https://github.com/aliyun/dbt-maxcompute/blob/master/docs/Tutorial.md Defines data quality tests for dbt models, including uniqueness, non-nullability, accepted values, and referential integrity. These tests are specified in a YAML schema file. ```yaml version: 2 models: - name: customers columns: - name: customer_id tests: - unique - not_null - name: stg_customers columns: - name: customer_id tests: - unique - not_null - name: stg_orders columns: - name: order_id tests: - unique - not_null - name: status tests: - accepted_values: values: ['F', 'O', 'P'] - name: customer_id tests: - not_null - relationships: to: ref('stg_customers') field: customer_id ``` -------------------------------- ### Credentials URI Authentication for MaxCompute Source: https://github.com/aliyun/dbt-maxcompute/blob/master/docs/authentication.md Configures dbt-maxcompute to retrieve credentials from a specified URI. This allows for dynamic credential management from local or remote sources, ensuring credentials are kept up-to-date. ```yaml jaffle_shop: target: dev outputs: dev: type: maxcompute project: dbt-example schema: default endpoint: http://service.cn-shanghai.maxcompute.aliyun.com/api auth_type: credentials_uri credentials_uri: http://local_or_remote_uri/ ``` -------------------------------- ### ECS RAM Role Authentication for MaxCompute Source: https://github.com/aliyun/dbt-maxcompute/blob/master/docs/authentication.md Sets up dbt-maxcompute to authenticate using an ECS instance's RAM role. Credentials are automatically obtained from the instance metadata service. Supports hardened mode (IMDSv2) and an option to disable IMDSv1. ```yaml jaffle_shop: target: dev outputs: dev: type: maxcompute project: dbt-example schema: default endpoint: http://service.cn-shanghai.maxcompute.aliyun.com/api auth_type: ecs_ram_role role_name: roleName disable_imds_v1: True ``` -------------------------------- ### Bearer Token Authentication for MaxCompute Source: https://github.com/aliyun/dbt-maxcompute/blob/master/docs/authentication.md Enables dbt-maxcompute to authenticate using a Bearer Token, typically required for specific services like Cloud Call Centre (CCC). The Bearer Token must be obtained separately. ```yaml jaffle_shop: target: dev outputs: dev: type: maxcompute project: dbt-example schema: default endpoint: http://service.cn-shanghai.maxcompute.aliyun.com/api auth_type: bearer bearer_token: bearerToken ``` -------------------------------- ### OIDC Role ARN Authentication for MaxCompute Source: https://github.com/aliyun/dbt-maxcompute/blob/master/docs/authentication.md Configures dbt-maxcompute to use OIDC Role ARN for authentication. This method allows automatic STS Token maintenance by specifying the role and OIDC provider ARNs. It supports limiting STS Token permissions via an optional policy. ```yaml jaffle_shop: target: dev outputs: dev: type: maxcompute project: dbt-example schema: default endpoint: http://service.cn-shanghai.maxcompute.aliyun.com/api auth_type: oidc_role_arn access_key_id: accessKeyId access_key_secret: accessKeySecret security_token: securityToken role_arn: roleArn oidc_provider_arn: oidcProviderArn oidc_token_file_path: /Users/xxx/xxx role_session_name: roleSessionName auth_policy: policy role_session_expiration: 3600 ``` === COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.