dbt-labs / dbt-presto Goto Github PK

pip install dbt-presto throws an exception because the package doesn't seem available on PyPI. See https://pypi.org/search/?q=dbt-presto

presto --version
Presto CLI 0.230-3b9af8c

I encountered this error while doing some end-to-end testing of dbt-presto using the Presto memory connector. If create [or replace] view as wraps its SQL in parentheses, that SQL cannot contain CTEs.

PrestoUserError(type=USER_ERROR, name=SYNTAX_ERROR, message="line 2:5: mismatched input 'with'. Expecting: '(', 'SELECT', 'TABLE', 'VALUES'", query_id=20200225_062356_00144_xeruh)

If I run an identical create [or replace] view as statement without parentheses, it works. I recall that we encountered a similar issue on Spark. Interestingly, I get this error when creating views in Presto, but not when creating tables.

I think a fix would be as simple as implementing:

{% macro presto__create_view_as(relation, sql) -%}
  create view {{ relation }} as
    {{ sql }}
{% endmacro %}

I have the same problem as this one issues/38 :)

but I'm using dbt-presto

This is the environment I installed:

I tried to make the following changes。

path : dbt/include/presto/macros/materializations/table.sql

then it is ok !

I hope you can give me some advice on whether it's useful or not ~~

thanks a lot !

Hi,

After upgrading to 0.14, I'm getting this error:

sh-4.2$ dbt debug
Running with dbt=0.14.0
dbt version: 0.14.0
python version: 3.7.3
python path: /usr/bin/python3
os info: Linux-4.9.125-linuxkit-x86_64-with-glibc2.2.5
Using profiles.yml file at /home/dbt/.dbt/profiles.yml

Configuration:
  profiles.yml file [OK found and valid]
  dbt_project.yml file [OK found and valid]
  profile: default [OK found]
  target: data_lake [OK found]

Required dependencies:
 - git [OK found]

Connection:
  host: presto_server.cluster.svc.local
  port: 8080
  database: data_lake
  Connection test: ERROR

dbt was unable to connect to the specified database.
The database returned the following error:

  >'PrestoConnectionManager' object has no attribute 'get'

Check your database credentials and try again. For more information, visit:
https://docs.getdbt.com/docs/configure-your-profile

Do you have any tips on how to debug this issue?

Thanks

Hello—

The recommended settings from the README are:

hive.metastore-cache-ttl=0s
hive.metastore-refresh-interval = 5s

I believe the hive.metastore-cache-ttl set to 0 tells Presto NOT to cache. Then the second setting says to asynchronously refresh the cache every 5, though I think it won't be used. This can put a lot of load on the Presto server + Metastore. We've seen rate limiting using the AWS Glue Catalog that I suspect are because of these settings.

I'm trying to understand the implications of using caching to speed things up, with or without a refresh interval. Something like this:

hive.metastore-cache-ttl=120s
hive.metastore-refresh-interval = 60s

So long as it takes less than 60 seconds to refresh to metastore, users doing interactive dbt develoment should never experience slowness related to the Metastore. At the same time we won't be thrashing the Metastore every 5 seconds.

But I feel like I might be missing something, so just wanted to see what the reasoning behind that catalog setting for hive would be.

Any plan to add PrestoSQL support?
I believe currently this supports PrestoDB, which is similar to PrestoSQL. But they may diverge in the future.
https://github.com/fishtown-analytics/dbt-presto/blob/master/dbt/adapters/presto/connections.py#L16

seems that the basic_load_csv_rows is not there in the dbt-core (0.21.0) anymore. Is there anyone has the same issue with seeding for 0.21.0 ?

https://github.com/dbt-labs/dbt-presto/blob/master/dbt/include/presto/macros/adapters.sql#L155

Publish package once #42 provides support for dbt-core 0.19.2rc2.

• from the release branch created in #42, bump the package version
• build and publish new version of package

Trying to use source_freshness feature with dbt-presto returns

Runtime Error in source test (models/schema.yml)
  ERROR: current_timestamp macro not implemented for adapter presto

but this is not documented anywhere.

I would like to execute dbt-presto with Treasure Data.
It's not require ldap nor kerberos but do https.
https://docs.treasuredata.com/display/public/PD/JDBC+Driver+for+Presto

I succeeded in connecting with Treasure Data by rewriting http -> https in this line.
https://github.com/dbt-labs/dbt-presto/blob/master/dbt/adapters/presto/connections.py#L163

If you edit /etc/config.properties you can set a regex-library flag to a value of JONI (the default) or RE2J. Setting it to RE2J and doing dbt run results in:

  Runtime Error
    PrestoUserError(type=USER_ERROR, name=INVALID_FUNCTION_ARGUMENT, message="error parsing regexp: invalid escape sequence: `\Z`", query_id=20200403_133843_00004_agst6)

There are some extremely vague docs here and the project github (I assume!) is here. The presto docs are a little incomplete, at best - they don't really make any effort to explain that some valid JONI patterns aren't allowed. The presto page gives this list of caveats. I believe the issue is with Matcher.requireEnd(), but regardless that's quite a list!

I think we should enable RE2J in our test image, because I think RE2J's regex pattern support is a strict subset of JONI's. Then we should fix the bugs, there's at least one!

@WangSleep commented on Thu Nov 19 2020

When i excute "dbt docs generate" on CLI .it dosen't work .

it's error message :
PrestoQueryError(type=INTERNAL_ERROR, name=OPTIMIZER_TIMEOUT, message="The optimizer exhausted the time limit of 180000 ms", query_id=20201110_082631_11221_7vjyv)

i can excute "dbt deug" , "dbt test" ,"dbt run" on CLI . but "dbt docs generate" alone can't

Can you solve my problem?

thanks a lot ~~~😊

@WangSleep commented on Thu Nov 19 2020

I found out when I performed "dbt docs generate" , it will excute this sql

select table_catalog as database, table_name as name, table_schema as schema, case when table_type = 'BASE TABLE' then 'table' when table_type = 'VIEW' then 'view' else table_type end as table_type from "hive".INFORMATION_SCHEMA.tables where regexp_like(table_schema, '(?i)\ADatebase\Z')

Too many resources are required, and execution time is too long to cause a timeout 。

Could you tell me what can be solved? thanks a lot

@jtcohen6 commented on Thu Nov 19 2020

Hey @WangSleep, which database / adapter plugin are you using? It sounds like the catalog query—which dbt uses to grab metadata, populate catalog.json, and thereby feed information into the docs site—may be inefficient as currently implemented on this adapter.

@WangSleep commented on Thu Nov 19 2020

Hey @WangSleep, which database / adapter plugin are you using? It sounds like the catalog query—which dbt uses to grab metadata, populate catalog.json, and thereby feed information into the docs site—may be inefficient as currently implemented on this adapter.

yeh .

We query by configuring presto-python-client, which I know is for docs, but python_presto query "hive". INFORMATION_SCHEMA is slow. This causes a timeout. Later I found out that I used lore-zeppelin, which was based on jdbc, and I could query. It takes about 5 minutes.

thanks a lot ~

@jtcohen6 commented on Fri Nov 20 2020

I'm still a bit confused. Are you using dbt-presto? What's the output when you run dbt --version?

@WangSleep commented on Sun Nov 22 2020

I'm still a bit confused. Are you using dbt-presto? What's the output when you run dbt --version?

hi , bro !
yes .
I'm using it . dbt --version : 0.18.1

@jtcohen6 commented on Mon Nov 23 2020

We haven't released dbt-presto v0.18.1 yet (which is on us), and presto__get_catalog as implemented in that plugin looks different from the query snippet you pasted above. (In fact, your snippet doesn't look like any of the get_catalog queries familiar to me.)

So... I'm still confused as to which dbt plugin you're using :)

@WangSleep commented on Mon Nov 23 2020

We haven't released dbt-presto v0.18.1 yet (which is on us), and presto__get_catalog as implemented in that plugin looks different from the query snippet you pasted above. (In fact, your snippet doesn't look like any of the get_catalog queries familiar to me.)

So... I'm still confused as to which dbt plugin you're using :)

hi . @jtcohen6

FYI

This is the version I installed :)

@jtcohen6 commented on Tue Nov 24 2020

Ok, this makes much more sense now. Appreciate the clarification!

The snippet you included comes from presto__list_relations_without_caching. I'm not sure why it only times out on dbt docs generate, since that's a query that should run at the start of all invocations.

I'm going to transfer this issue to the dbt-presto repo, where we can discuss if there is a more performant way to write that metadata introspection. Out of curiosity, how many objects are in your "hive" catalog?

Seed files with date values don't work in dbt-presto.

I created following CSV seed file:

ID,DATE
1,2021-05-12

when I run dbt seed I get following error:

Runtime Error in seed test (data/test.csv)
  Cannot escape <class 'datetime.date'>

Pointing dbt to postgres instead of presto works and correctly creates the table:

 select * from test;
 id |    date    
----+------------
  1 | 2021-05-12 

As a workaround, also setting the column to string in project.yml works, but if you want that to be date, you have to cast it when using the seed file. Seed files with just strings and number work corrently.

We don't currently have any integration tests. Until we do, we need to rely on manual end-to-end testing and the (currently small) community of Presto users to validate proposed changes.

There's much more to think about here:

• How can we set up a reasonable testing environment for Presto? Running everything via the memory catalog is a fine way to check syntax, but it's probably not a good representation of how people run Presto in the wild.
• The dbt-spark plugin uses a modified version of https://github.com/fishtown-analytics/dbt-integration-tests. The dbt-presto plugin does not support incremental models. How might dbt-integration-tests (or dbt-core) define a matrix of available features and associated tests, such that each adapter can declare which ones it does (or doesn't) support?

As far as I can tell, there is no way to pass in a path to a certificate for an SSL connection to the API. Is this something that can be passed in the http_headers position?

• We've released dbt v0.19.0rc1, we should release dbt-presto v0.19.0rc1 for testing as well
• We still need to make a (final) release of dbt-presto v0.18.1 (#33) !

With the rename of prestosql to Trino I think this adapter will stop to work for the ones not using PrestoDB (developed by facebook).

Should a new one be created for the ones using Trino?

When creating a table in Presto or Trino, you can define table properties (example from docs):

CREATE TABLE hive.web.page_views (
  view_time timestamp,
  user_id bigint,
  page_url varchar,
  ds date,
  country varchar
)
WITH (
  format = 'ORC',
  partitioned_by = ARRAY['ds', 'country'],
  bucketed_by = ARRAY['user_id'],
  bucket_count = 50
)

It would be helpful to be able to define those for table models, similar to how you can configure distkey and sortkey in dbt-redshift.

{{
config(materialized='table',
       partitioned_by=['ds', 'country'],
       format='ORC',
       bucketed_by='user_id',
       bucket_count=50)
}}

It would allow the users to define storage formats, partitioning and similar. Since different Presto connectors use different properties, this option should be generic and allow any properties.

Description

Support dbt core 0.20.0rc1 in order to release dbt-spark 0.20.0rc1

• bump dbt-core version to 0.20.0rc1
• update code to support dbt-core 0.20.0rc1 (https://github.com/fishtown-analytics/dbt/releases/tag/v0.20.0rc1)

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

I have a hive DBT project configured to use hive via presto. We are using AWS EMR and AWS Glue Catalogue. I have added recommended configuration for presto

hive.metastore-cache-ttl=0s hive.metastore-refresh-interval = 5s hive.allow-drop-table=true hive.allow-rename-table=true

When I'm running dbt run I'm getting PrestoUserError(type=USER_ERROR, name=NOT_SUPPORTED, message="Table rename is not yet supported by Glue service")

Is there a way to configure dbt or dbt-presto to run different queries instead of renaming tables?

During dbt docs generate, dbt scans all source schemas (via https://github.com/dbt-labs/dbt-presto/blob/master/dbt/include/presto/macros/catalog.sql) which on larger Presto / Hive installs times out.

Is there a way to just have it scan the tables specified in the source.yml files?

We have been running into the following errors when using table materialization on Trino queries which ran for a certain period of time (cca over 320 seconds):
failed to commit transaction 8c6a8ab8-6458-47dc-956f-8195c33e8cc1: PrestoUserError(type=USER_ERROR, name=UNKNOWN_TRANSACTION, message="Unknown transaction ID: 8c6a8ab8-6458-47dc-956f-8195c33e8cc1. Possibly expired? Commands ignored until end of transaction block", query_id=20210811_123350_12052_t27c7)

The problematic part is the following 'main' statement block in the materialization macro:

As the create statement doesn't need transaction block around it, we've tried to turn it off by customizing the materialization macro by adding the "auto_begin=False" to the main statement block. This doesn't work with dbt, it complains that the transaction is not open. We were able to work around this behaviour by running the create statement before the 'main' statement block and inserting a dummy select into the block but that alters the outputs in "target/run/" folder.

We thought that the issue is related to the used Presto/Trino driver but we've tried running the code also using the dbt-trino package with trino-python-client but the output error is the same.

What could be the cause of this issue? Can it be fixed from dbt-presto side or is this affecting the dbt-core? Is there a cleaner solution to this problem than the one I described above, possibly allowing the 'main' statement block to run without transaction?

We have a use case at Shopify for specifying custom http headers to our Presto cluster for routing queries to specific clusters. Additionally, supporting a configurable http_scheme would be useful in combination with http_headers - for example, using the {'Authorization': <token>} header in combination with https as the http_scheme.

It looks like this change would mostly be a matter of plumbing the configurations through to the prestodb.dbapi.connect call in connections.py.

Fix this in Presto via dbt-labs/dbt-core#1338

Hi—I have a requirement to do this following

SET SESSION hive.insert_existing_partitions_behavior = 'OVERWRITE'"

prior to running a query. I am trying to do this in a custom materialisation, so tried this:

{% call statement() %}
SET SESSION hive.insert_existing_partitions_behavior = 'OVERWRITE'";

INSERT INTO … SELECT … -- my long query here
{% endcall %}

This fails as I believe statement doesn't support multiple statements.

I also tried:

{% do run_query("SET SESSION hive.insert_existing_partitions_behavior = 'OVERWRITE'";) %}
{% set results = run_query("SHOW SESSION") %}
{% do results.print_table(max_rows=none,max_column_width=100) %}

But can see in the results variable that insert_existing_partitions_behavior hasn't changed from APPEND, its default value.

Publish package once #43 provides support for dbt-core 0.20.0rc1.

• bump the package version
• build and publish new version of package

continue the discussion in #53, #58

Table property dict defined in project level is overwritten by config in model level. Merging the property might be better. It will help to share the common properties between tables and preserve detail properties for models

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 should release dbt-presto v0.16.0

• release a beta
• release a final 0.16.0 version

cc @jtcohen6

Description

Support dbt core 0.19.2rc2 in order to release dbt-presto 0.19.2rc2

• bump dbt-core version to 0.19.2rc2
• update code to support dbt-core 0.19.2rc2 (https://github.com/fishtown-analytics/dbt/releases/tag/v0.19.2rc2)
• create a release branch for 0.19.* to follow up with support for the 0.19.2 final release of dbt core

Sibling to dbt-labs/dbt-spark#61

CI

We don't currently run any automated CI on the Presto adapter. We should run at least:

• flake8
• unit tests

The quality of CI will improve significantly as we add integration tests (#12).

Releases

• Same as Spark

Issue

Issue description

I'm testing dbt for presto.
Just ran:

$ dbt init test
$ cd test && dbt run

Results

Running with dbt=0.13.0
Found 1 models, 0 tests, 0 archives, 0 analyses, 96 macros, 0 operations, 0 seed files, 0 sources

13:18:07 | Concurrency: 4 threads (target='dev')
13:18:07 | 
13:18:07 | 1 of 1 START view model data_warehouse.my_first_dbt_model............ [RUN]
13:18:09 | 1 of 1 ERROR creating view model data_warehouse.my_first_dbt_model... [ERROR in 1.05s]
13:18:09 | 
13:18:09 | Finished running 1 view models in 7.50s.

Completed with 1 errors:

Runtime Error in model my_first_dbt_model (models/example/my_first_dbt_model.sql)
  PrestoUserError(type=USER_ERROR, name=NOT_SUPPORTED, message="Unsupported combination of operations in a single transaction", query_id=20190414_131810_00009_qvqzx)

Done. PASS=0 ERROR=1 SKIP=0 TOTAL=1

System information

The output of dbt --version:

installed version: 0.13.0
   latest version: 0.13.0

Up to date!

The operating system you're running on:
docker image: amazonlinux

The python version you're using (probably the output of python --version)

$ python3 --version
Python 3.7.2

Presto version: 0.215 (AWS EMR)

dbt.log

2019-04-17 11:00:52,473 (MainThread): 11:00:52 | Concurrency: 1 threads (target='dev')
2019-04-17 11:00:52,473 (MainThread): 11:00:52 | 
2019-04-17 11:00:52,479 (Thread-1): 11:00:52 | 1 of 1 START view model data_warehouse.my_first_dbt_model............ [RUN]
2019-04-17 11:00:52,479 (Thread-1): Compiling model.my_new_package.my_first_dbt_model
2019-04-17 11:00:52,486 (Thread-1): Writing injected SQL for node "model.my_new_package.my_first_dbt_model"
2019-04-17 11:00:52,529 (Thread-1): Acquiring new presto connection "my_first_dbt_model".
2019-04-17 11:00:52,530 (Thread-1): Re-using an available connection from the pool.
2019-04-17 11:00:52,530 (Thread-1): Using presto connection "my_first_dbt_model".
2019-04-17 11:00:52,530 (Thread-1): On my_first_dbt_model: drop view if exists "data_lake"."data_warehouse"."my_first_dbt_model__dbt_tmp"
2019-04-17 11:00:52,717 (Thread-1): SQL status: OK in 0.19 seconds
2019-04-17 11:00:52,726 (Thread-1): Using presto connection "my_first_dbt_model".
2019-04-17 11:00:52,727 (Thread-1): On my_first_dbt_model: drop view if exists "data_lake"."data_warehouse"."my_first_dbt_model__dbt_backup"
2019-04-17 11:00:52,876 (Thread-1): SQL status: OK in 0.15 seconds
2019-04-17 11:00:52,878 (Thread-1): Writing runtime SQL for node "model.my_new_package.my_first_dbt_model"
2019-04-17 11:00:53,013 (Thread-1): Using presto connection "my_first_dbt_model".
2019-04-17 11:00:53,013 (Thread-1): On my_first_dbt_model: create view "data_lake"."data_warehouse"."my_first_dbt_model__dbt_tmp" as (
    -- Welcome to your first dbt model!
-- Did you know that you can also configure models directly within
-- the SQL file? This will override configurations stated in dbt_project.yml

-- Try changing 'view' to 'table', then re-running dbt



select 1 as id
  )
2019-04-17 11:00:53,205 (Thread-1): SQL status: OK in 0.19 seconds
2019-04-17 11:00:53,210 (Thread-1): Using presto connection "my_first_dbt_model".
2019-04-17 11:00:53,210 (Thread-1): On my_first_dbt_model: alter table "data_lake"."data_warehouse"."my_first_dbt_model__dbt_tmp" rename to "data_lake"."data_warehouse"."my_first_dbt_model"
2019-04-17 11:00:53,356 (Thread-1): Error while running:
alter table "data_lake"."data_warehouse"."my_first_dbt_model__dbt_tmp" rename to "data_lake"."data_warehouse"."my_first_dbt_model"
2019-04-17 11:00:53,356 (Thread-1): PrestoUserError(type=USER_ERROR, name=NOT_SUPPORTED, message="Unsupported combination of operations in a single transaction", query_id=20190417_100053_14524_n2dpt)
2019-04-17 11:00:53,357 (Thread-1): On my_first_dbt_model: ROLLBACK
2019-04-17 11:00:53,486 (Thread-1): Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '21b2a51d-7aad-49f7-93ad-10774f7455f8', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f1a4d510320>]}
2019-04-17 11:00:54,285 (Thread-1): 11:00:54 | 1 of 1 ERROR creating view model data_warehouse.my_first_dbt_model... [ERROR in 1.01s]
2019-04-17 11:00:54,294 (MainThread): 11:00:54 | 
2019-04-17 11:00:54,294 (MainThread): 11:00:54 | Finished running 1 view models in 3.01s.
2019-04-17 11:00:54,300 (MainThread): 
2019-04-17 11:00:54,301 (MainThread): Completed with 1 errors:
2019-04-17 11:00:54,301 (MainThread): 
2019-04-17 11:00:54,301 (MainThread): Runtime Error in model my_first_dbt_model (models/example/my_first_dbt_model.sql)
2019-04-17 11:00:54,301 (MainThread):   PrestoUserError(type=USER_ERROR, name=NOT_SUPPORTED, message="Unsupported combination of operations in a single transaction", query_id=20190417_100053_14524_n2dpt)
2019-04-17 11:00:54,301 (MainThread): 
Done. PASS=0 ERROR=1 SKIP=0 TOTAL=1
2019-04-17 11:00:54,302 (MainThread): Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f1a4d6369b0>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f1a4d60d198>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f1a4d632940>]}
2019-04-17 11:00:54,900 (MainThread): Flushing usage events
2019-04-17 11:00:54,926 (MainThread): sys:1: ResourceWarning: unclosed <socket.socket fd=5, family=AddressFamily.AF_INET, type=SocketKind.SOCK_STREAM, proto=6, laddr=('<IP_ADDRESS>', 42350), raddr=('<IP_ADDRESS>, 8889)>

Steps to reproduce

Run the commands above.

This is one of the killer DBT features and I think that it should be available in Presto as well. I will try to create a PR for this one but it might take a few weeks.

If there's anyone who is willing to implement this feature in less than 2 weeks, feel free to comment.