chop-analytics / dbt-netezza Goto Github PK

Background

The latest version of dbt Core,dbt-core==1.4.0, was published on January 25, 2023 (PyPI | Github). In fact, a patch, dbt-core==1.4.1 (PyPI | Github), was also released on the same day.

How to upgrade

dbt-labs/dbt-core#6624 is an open discussion with more detailed information. If you have questions, please put them there! dbt-labs/dbt-core#6849 is for keeping track of the community's progress on releasing 1.4.0

The above linked guide has more information, but below is a high-level checklist of work that would enable a successful 1.4.0 release of your adapter.

• support Python 3.11 (only if your adapter's dependencies allow)
• Consolidate timestamp functions & macros
• Replace deprecated exception functions
• Add support for more tests

the next minor release: 1.5.0

FYI, dbt-core==1.5.0 is expected to be released at the end of April. Please plan on allocating a more effort to upgrade support compared to previous minor versions. Expect to hear more in the middle of April.

At a high-level expect much greater adapter test coverage (a very good thing!), and some likely heaving renaming and restructuring as the API-ification of dbt-core is now well underway. See https://github.com/dbt-labs/dbt-core/milestone/82 for more information.

Hi Folks,

We are testing dbt with the dbt-netezza adapter at one of our client sites. The target Netezza appliance is schema-aware and has various different schema in each database to organise content. Hence I've tried to use dbt custom schema features in some of the models to apply them to the correct target database and schema.

For table and view materializations, when dbt calls the "list_relations_without_caching macro", it appears to apply the target schema (from the profile.yml) to the where clause of the INFROMATION_SCHEMA lookup rather than the custom schema. As a result, dbt thinks that the table or view does not exist and subsequently fails when it tries to rename the temporary object to the existing object (whenever the target schema is different from the custom schema).

I've been able to work around this by overriding the macro in my own project to use {{ schema_relation.schema }} instead of {{ schema }} in the where clause.

To clarify, the query in the log is something like this:

SELECT 
	....
FROM 
	INFROMATION_SCHEMA._v_table
WHERE
	DATABASE = 'TARGET_DB'
	AND SCHEMA = 'ADMIN'
UNION 
SELECT 
	....
FROM 
	INFROMATION_SCHEMA._v_view
WHERE
	DATABASE = 'TARGET_DB'
	AND SCHEMA = 'ADMIN'

ADMIN being the default schema I have set in profiles.yml (i.e not the custom schema). However, I need this to take value of the custom schema set in the model config.

Just wanted to make you aware of this and check if this was intended or if this should be adjusted to allow custom schema to work with the netezza adapter. Of course, this will not be a problem if the Netezza installation does not implement schema. Ours does in this application.

Thanks for creating and sharing the Netezza adapter by the way, it has been very useful.

Regards,
David

when upgrading dbt to last version (0.19.1), every command returns this error (even a simple dbt ls):

'int' object has no attribute '_serialize'

Background

Minor version v1.7 is targeted for final release on Ocotber 26, 2023. As a maintainer of a dbt adapter, we strongly encourage you to release a corresponding minor version increment to ensure users of your adapter can make use of this new minor version.

How to upgrade

dbt-labs/dbt-core#8307 is an open discussion with more detailed information. If you have questions, please put them there!

Background

The latest release cut for 1.3.0, dbt-core==1.3.0rc2 was published on October 3, 2022 (PyPI | Github). We are targeting releasing the official cut of 1.3.0 in time for the week of October 16 (in time for Coalesce conference).

We're trying to establish a following precedent w.r.t. minor versions:
Partner adapter maintainers release their adapter's minor version within four weeks of the initial RC being released. Given the delay on our side in notifying you, we'd like to set a target date of November 7 (four weeks from today) for maintainers to release their minor version

How to upgrade

dbt-labs/dbt-core#6011 is an open discussion with more detailed information, and dbt-labs/dbt-core#6040 is for keeping track of the community's progress on releasing 1.2.0

Below is a checklist of work that would enable a successful 1.2.0 release of your adapter.

• Python Models (if applicable)
• Incremental Materialization: cleanup and standardization
• More functional adapter tests to inherit

Do you have any plan to support dbt 1.0?

Background

The latest release cut for 1.3.0, dbt-core==1.3.0rc2 was published on October 3, 2022 (PyPI | Github). We are targeting releasing the official cut of 1.3.0 in time for the week of October 16 (in time for Coalesce conference).

We're trying to establish a following precedent w.r.t. minor versions:
Partner adapter maintainers release their adapter's minor version within four weeks of the initial RC being released. Given the delay on our side in notifying you, we'd like to set a target date of November 7 (four weeks from today) for maintainers to release their minor version

How to upgrade

dbt-labs/dbt-core#6011 is an open discussion with more detailed information, and dbt-labs/dbt-core#6040 is for keeping track of the community's progress on releasing 1.2.0

Below is a checklist of work that would enable a successful 1.2.0 release of your adapter.

• Python Models (if applicable)
• Incremental Materialization: cleanup and standardization
• More functional adapter tests to inherit

We've just published the release cut of dbt-core 1.2.0, dbt-core 1.2.0rc1 (PyPI | GitHub release notes).

dbt-labs/dbt-core#5468 is an open discussion with more detailed information, and dbt-labs/dbt-core#5474 is for keeping track of the communities progress on releasing 1.2.0

Below is a checklist of work that would enable a successful 1.2.0 release of your adapter.

• migrate necessary cross-db macros into adapter and ensure they're tested accordingly
• remove any copy-and-pasted materialization (if your adapter inherits from another adapter)
• add new basic tests BaseDocsGenerate and BaseDocsGenReferences
• consider checking and testing support for Python 3.10

dbt-labs/dbt-core#5432 might make it into the second release cut in the next week, in which case, you'll also might want to:

• implement method and tests for connection retry logic

The Available Adapters page is one of the dbt community's most-visited docs pages. It would be of great benefit for first-time visitors to the dbt docs to see:

1. that this adapter is a possible option for using dbt-core, and
2. how many large the dbt ecosystem of support databases is.

dbt-labs/docs.getdbt.com#1489 exists to address this with all as-of-yet undocumented adapters.

We just released Documenting a new adapter, a new guide on how to add an adapter to the Available Adapters page. I'd love to see this adapter on that page, so feel free to reach out with any questions/blockers by either replying to this issue, or posting in the #adapter-ecosystem channel of the dbt Community Slack.

Looking forward to the contribution!

In some situations I found that errors from Netezza are not showing (i.e. model results as completed successfully, but transaction has been rolled back or partially committed).
The issue regards incremental models when using delete+insert strategy.

I had to override the get_delete_insert_merge_sql default macro, because the syntax used in the delete statement is not compatible with Netezza (at least not with the NPS version I'm using); I applied really l.ight modifications to make it work properly:

{% macro netezza__get_delete_insert_merge_sql(target, source, unique_key, dest_columns) -%}

    {%- set dest_cols_csv = get_quoted_csv(dest_columns | map(attribute="name")) -%}

    {% if unique_key %}
        {% if unique_key is sequence and unique_key is not string %}
            delete from {{target }}
            where ({{ unique_key | join(',') }}) in (
                select {{ unique_key | join(',') }}
                from {{ source }}
            );
        {% else %}
            delete from {{ target }}
            where (
                {{ unique_key }}) in (
                select {{ unique_key }}
                from {{ source }}
            );

        {% endif %}
        {% endif %}

    insert into {{ target }} ({{ dest_cols_csv }})
    (
        select {{ dest_cols_csv }}
        from {{ source }}
    )
{% endmacro %}

If I run an incremental model everything works as expects:

{{
  config(
    materialized = 'incremental',
    unique_key = 'id'
  )
}}

select 
1 as id,
'aaa' as text,
current_timestamp as load_time

If I then manually add a column to the generated table (alter table test add newattr int) and re-run the incremental model, I'd expect an error (the new column is not present in the model definition, and thus the INSERT statement should fail), but the models results as completed successfully.
Looking at the data in Netezza, I can see that the whole transaction has been rolled back (you can easily verify it checking the load_time column); in other cases (which I've not been able to reproduce), the DELETE part has been committed but not the INSERT part.

The rollback can be confirmed looking at pg.log on NZ server:

2022-11-30 12:03:55.359100 CET [98500] DEBUG: QUERY: delete from GRADWH_TEST.ADMIN.TEST where ( id) in ( select id from GRADWH_TEST.ADMIN.TEST__dbt_tmp ); insert into GRADWH_TEST.ADMIN.TEST ("ID", "TEXT", "LOAD_TIME", "NEWATTR") ( select "ID", "TEXT", "LOAD_TIME", "NEWATTR" from GRADWH_TEST.ADMIN.TEST__dbt_tmp )
2022-11-30 12:03:55.368534 CET [98500] DEBUG: transaction 205630955 started dbos txid 0x54a8454
2022-11-30 12:03:55.395208 CET [98500] DEBUG: 0 rows deleted
2022-11-30 12:03:55.442219 CET [98500] DEBUG: 1 rows deleted
2022-11-30 12:03:55.459206 CET [98500] DEBUG: 0 rows deleted
2022-11-30 12:03:55.460414 CET [98500] ERROR: Attribute 'NEWATTR' not found
2022-11-30 12:03:55.460962 CET [98500] DEBUG: aborted transaction 205630955 (dbos txid 0x54a8454)

I found this issue with both adapter versions 1.1.0 and 0.6.0.

I am trying to install dbt-netezza and create a new dbt project. However, I get an error that no adapters are found.

I manage to install dbt-netezza through the following command:
pip install git+https://github.com/chop-analytics/dbt-netezza
This installs dbt-netezza (v0.60) and dbt-core (v1.2.0). However, when running dbt init dbt_test, I receive the following error:
No adapters available. Go to https://docs.getdbt.com/docs/available-adapters
I have tried with other adapters (e.g. dbt-postgres), and that works well.
I'm using Python v3.8.9 and pip v22.2.2.

Background

The latest version of dbt Core,dbt-core==1.5.0rc1, was published on April 13, 2023 (PyPI | Github).

How to upgrade

dbt-labs/dbt-core#7213 is an open discussion with more detailed information. If you have questions, please put them there!

The above linked guide has more information, but below is a high-level checklist of work that would enable a successful 1.5.0 release of your adapter.

• Add support Python 3.11 (if you haven't already)
• Add support for relevant tests (there's a lot of new ones!)
• Add support model contracts
• Add support for materialized views (this likely will be bumped to 1.6.0)

the next minor release: 1.6.0

FYI, dbt-core==1.6.0 is expected to be released at the end of July, with a release cut at least two weeks prior.

I have had mixed success and I will be the first to admit a lack of expertise on this topic, but the process by which this adapter is supposed to be installed is a little confusing.

So far I have had success by looking at a previous iteration of the README.md file that specified a pip install via the git repo link.

I wasn't sure if this is the right method or if it would be preferable to utilize the standard dbt package installation method specified (here.

dbt applies a default prefix dbt__CTE to the cte generated by ephemeral models.
Netezza throws an error when trying to use these ctes joined with others ("WITH Clause syntax not support for system catalog queries"), which is only due to the __ at the beginning of the cte name.
This default prefix can be overridden in the Relation class redefining the add_ephemeral_prefix methid (see Exasol adapter for reference at https://github.com/tglunde/dbt-exasol/blob/e06adb301c6d6865088b60bdcca042e39c4596ab/dbt/adapters/exasol/relation.py#L54).
I'd suggest to override it with a simpler "dbt__CTE__" which should give good uniqueness guarantees and avoid this Netezza compiler error.

First, thank you for sharing this adapter. I've been using it for a couple of years now, and it helped me so much.

Recently, I needed to gather some meta data (auditability purpose) and I needed to use dbt_artifacts package. I performed little changes on the code. Do you consider PR & contributions?

By the way the adapter works fine with dbt-core1.3.5, I upgraded a couple of files to make it work (for my "little" needs) with version 1.6.1 but I am still working on it.

When calling column.is_string() macro, the result is always False (tested with VARCHAR and NVARCHAR columns).
This makes much harder using macros like dbt_utils.union_relations, that leverages this to assign the larger datatype to columns resulting unioning multiple relations.

We've just published the release cut of dbt-core 1.2.0, dbt-core 1.2.0rc1 (PyPI | GitHub release notes).

dbt-labs/dbt-core#5468 is an open discussion with more detailed information, and dbt-labs/dbt-core#5474 is for keeping track of the communities progress on releasing 1.2.0

Below is a checklist of work that would enable a successful 1.2.0 release of your adapter.

• migrate necessary cross-db macros into adapter and ensure they're tested accordingly
• remove any copy-and-pasted materialization (if your adapter inherits from another adapter)
• add new basic tests BaseDocsGenerate and BaseDocsGenReferences
• consider checking and testing support for Python 3.10

dbt-labs/dbt-core#5432 might make it into the second release cut in the next week, in which case, you'll also might want to:

• implement method and tests for connection retry logic

Background

Minor version v1.6 is targeted for final release on July 27, 2023. As a maintainer of a dbt adapter, we strongly encourage you to release a corresponding minor version increment to ensure users of your adapter can make use of this new minor version.

How to upgrade

dbt-labs/dbt-core#7958 is an open discussion with more detailed information. If you have questions, please put them there!

The above linked guide has more information, but below is a high-level checklist of work that would enable a successful 1.6.0 release of your adapter:

### Tasks
- [ ] SUPPORT: materialized views
- [ ] SUPPORT: new `clone` command
- [ ] BEHIND THE SCENES: Drop support for Python 3.7 (if you haven't already)
- [ ] BEHIND THE SCENES: new arg for `adapter.execute()`
- [ ] BEHIND THE SCENES: ensure support for revamped `dbt debug``
- [ ] BEHIND THE SCENES: Add support for new/modified relevant tests

the next minor release: 1.7.0

FYI, dbt-core==1.7.0 is expected to be released on October 12, 2023 in time for Coalesce, the annual analytics engineering conference!

We've just published the release cut of dbt-core 1.2.0, dbt-core 1.2.0rc1 (PyPI | GitHub release notes).

dbt-labs/dbt-core#5468 is an open discussion with more detailed information, and dbt-labs/dbt-core#5474 is for keeping track of the communities progress on releasing 1.2.0

Below is a checklist of work that would enable a successful 1.2.0 release of your adapter.

• migrate necessary cross-db macros into adapter and ensure they're tested accordingly
• remove any copy-and-pasted materialization (if your adapter inherits from another adapter)
• add new basic tests BaseDocsGenerate and BaseDocsGenReferences
• consider checking and testing support for Python 3.10

dbt-labs/dbt-core#5432 might make it into the second release cut in the next week, in which case, you'll also might want to:

• implement method and tests for connection retry logic