techindicium / dbt-oracle Goto Github PK

View Code? Open in Web Editor NEW

38.0 38.0 18.0 91 KB

A dbt adapter for oracle db backend

License: Other

Python 88.08% Makefile 11.92%

dbt-oracle's People

Contributors

Stargazers

Watchers

Forkers

joaoheron fabrice-etanchaud lewisdavies jjengla rittmananalytics emacha elgabito amychen1776 vijaybalebail bvancilku bryanherger mschweyen fredrikhgrelland bowenisdum eredzik dangtranphuong patped dataders

dbt-oracle's Issues

feat: implement query status

Oracle DBT version:
Python version:
Operating System:

Description

The query status from the cursor should be implemented in dbt/adapter/oracle/connections.py

Dbt docs generate not implemented

Dbt docs is still not implemented for the oracle driver:

Implement surrogate_key macro

Oracle DBT version:
Python version:
Operating System:

Description

This is a common function used by datawarehouses.
https://github.com/dbt-labs/dbt-utils/blob/master/macros/sql/surrogate_key.sql

What I Did

Paste the command(s) you ran and the output.
If there was a crash, please include the traceback here.

Error on dbt debug

When testing the connection on dbt debug, this error appears. Nevertheless, the connection is sucessful.

Oracle error: ORA-00923: FROM keyword not found where expected
Connection test: ERROR

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

Database Error
ORA-00923: FROM keyword not found where expected

Profile target schema

I'm not sure if profile target schema is beeing used, it seems that only the User is beeing considered as the schema

Full-refresh doesn't drop table

Oracle DBT version: 0.4.3
Python version: 3.7.3
Operating System: Linux

I have a table materialization that works file, but if I do 'dbt run --full-refresh, I get an error from Oracle saying "name is already used by an existing object."

Any idea if full-refresh should be working?

An dbt-oracle Macro does not execute multiple statements (separated by semicolon)

Oracle DBT version: 0.19.2
Python version: 3.8.10
Operating System: Ubuntu 20.04.2 LTS

Description

This macro works:

{% macro grant_select() %}

{% set sql %}
    grant select on orders to psofa
{% endset %}

{% do run_query(sql) %}
{% do log("Privileges granted", info=True) %}

{% endmacro %}

This macro does not work:

{% macro grant_select() %}

{% set sql %}
    grant select on orders to psofa;
    grant select on orders to psofa;
{% endset %}

{% do run_query(sql) %}
{% do log("Privileges granted", info=True) %}

{% endmacro %}

and gives the following error:

Oracle error: ORA-00933: SQL command not properly ended
Rolling back transaction.
Encountered an error while running operation: Database Error
  ORA-00933: SQL command not properly ended

Implement macro quote

Oracle DBT version:
Python version:
Operating System:

Description

Common macro used to get columns created with quote.
See example implemented by mysql adapter
https://github.com/dbeatty10/dbt-mysql/blob/dev/0.19.0/dbt/adapters/mysql/column.py

What I Did

Paste the command(s) you ran and the output.
If there was a crash, please include the traceback here.

Hanging up on ctrl+c

Oracle DBT version:
dbt-core 0.19.2
dbt-oracle 0.4.3
Python version:
3.9.6
Operating System:
Linux f034a66acec7 3.10.0-957.el7.x86_64 #1 SMP Thu Nov 8 23:39:32 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

Description

dbt cli always hangs when try to interrupt execution by ctrl+c

What I Did

Executed dbt project by:
dbt --debug run
and
pressed ctrl+c a few times after execution of some SQL-query started

dbt only ends after I manually kill some of ORACLE sessions being executed the query.

from x
^C2021-07-27 16:49:39.349302 (MainThread): Acquiring new oracle connection "master".
2021-07-27 16:49:39.349622 (MainThread): Cancelling query 'model.my_new_project.table1'
^C^C^C^C^C2021-07-27 16:50:25.722387 (Thread-1): Oracle error: DPI-1002: invalid OCI handle
2021-07-27 16:51:16.768438 (Thread-1): On model.my_new_project.table1: ROLLBACK
2021-07-27 16:51:16.769097 (MainThread): Connection 'master' was properly closed.
2021-07-27 16:51:16.769974 (MainThread): Connection 'model.my_new_project.table1' was left open.
2021-07-27 16:51:16.770296 (MainThread): On model.my_new_project.table1: ROLLBACK
2021-07-27 16:51:16.769617 (Thread-1): Failed to rollback model.my_new_project.table1
Traceback (most recent call last):
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/oracle/connections.py", line 153, in exception_handler
    yield
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/oracle/connections.py", line 210, in add_query
    cursor.execute(sql, bindings)
cx_Oracle.DatabaseError: DPI-1002: invalid OCI handle

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/base/connections.py", line 221, in _rollback_handle
    connection.handle.rollback()
cx_Oracle.OperationalError: ORA-03114: not connected to ORACLE
2021-07-27 16:51:16.773197 (Thread-1): On model.my_new_project.table1: Close
2021-07-27 16:51:16.770619 (MainThread): Failed to rollback model.my_new_project.table1
Traceback (most recent call last):
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/task/runnable.py", line 339, in execute_nodes
    self.run_queue(pool)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/task/runnable.py", line 264, in run_queue
    self.job_queue.join()
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/graph/queue.py", line 173, in join
    self.inner.join()
  File "/home/user1/.conda/envs/sql/lib/python3.9/queue.py", line 90, in join
    self.all_tasks_done.wait()
  File "/home/user1/.conda/envs/sql/lib/python3.9/threading.py", line 312, in wait
    waiter.acquire()
KeyboardInterrupt

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/oracle/connections.py", line 134, in cancel
    Connection.close(oracle_connection)
cx_Oracle.OperationalError: ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 2976 Serial number: 56963

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/task/runnable.py", line 384, in execute_with_hooks
    res = self.execute_nodes()
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/task/runnable.py", line 347, in execute_nodes
    self._cancel_connections(pool)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/task/runnable.py", line 315, in _cancel_connections
    for conn_name in adapter.cancel_open_connections():
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/base/impl.py", line 1069, in cancel_open_connections
    return self.connections.cancel_open()
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/sql/connections.py", line 47, in cancel_open
    self.cancel(connection)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/oracle/connections.py", line 134, in cancel
    Connection.close(oracle_connection)
KeyboardInterrupt

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/base/connections.py", line 221, in _rollback_handle
    connection.handle.rollback()
cx_Oracle.OperationalError: ORA-03114: not connected to ORACLE
2021-07-27 16:51:16.775293 (MainThread): On model.my_new_project.table1: Close
2021-07-27 16:51:16.775648 (MainThread): Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fe375709250>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fe36186e700>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fe36186e9a0>]}
2021-07-27 16:51:16.777113 (MainThread): Flushing usage events
2021-07-27 16:51:16.776313 (Thread-1): Database Error in model table1 (models/app/bod1/table1.sql)
  DPI-1002: invalid OCI handle
  compiled SQL at target/run/my_new_project/models/app/bod1/table1.sql
Traceback (most recent call last):
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/oracle/connections.py", line 153, in exception_handler
    yield
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/oracle/connections.py", line 210, in add_query
    cursor.execute(sql, bindings)
cx_Oracle.DatabaseError: DPI-1002: invalid OCI handle

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/task/base.py", line 344, in safe_run
    result = self.compile_and_execute(manifest, ctx)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/task/base.py", line 287, in compile_and_execute
    result = self.run(ctx.node, manifest)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/task/base.py", line 389, in run
    return self.execute(compiled_node, manifest)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/task/run.py", line 248, in execute
    result = MacroGenerator(materialization_macro, context)()
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/clients/jinja.py", line 332, in __call__
    return self.call_macro(*args, **kwargs)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/clients/jinja.py", line 259, in call_macro
    return macro(*args, **kwargs)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/jinja2/runtime.py", line 675, in __call__
    return self._invoke(arguments, autoescape)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/jinja2/runtime.py", line 679, in _invoke
    rv = self._func(*arguments)
  File "<template>", line 63, in macro
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/jinja2/sandbox.py", line 462, in call
    return __context.call(__obj, *args, **kwargs)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/jinja2/runtime.py", line 290, in call
    return __obj(*args, **kwargs)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/clients/jinja.py", line 332, in __call__
    return self.call_macro(*args, **kwargs)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/clients/jinja.py", line 259, in call_macro
    return macro(*args, **kwargs)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/jinja2/runtime.py", line 675, in __call__
    return self._invoke(arguments, autoescape)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/jinja2/runtime.py", line 679, in _invoke
    rv = self._func(*arguments)
  File "<template>", line 41, in macro
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/jinja2/sandbox.py", line 462, in call
    return __context.call(__obj, *args, **kwargs)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/jinja2/runtime.py", line 290, in call
    return __obj(*args, **kwargs)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/base/impl.py", line 227, in execute
    return self.connections.execute(
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/sql/connections.py", line 124, in execute
    _, cursor = self.add_query(sql, auto_begin)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/oracle/connections.py", line 218, in add_query
    return connection, cursor
  File "/home/user1/.conda/envs/sql/lib/python3.9/contextlib.py", line 135, in __exit__
    self.gen.throw(type, value, traceback)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/oracle/connections.py", line 165, in exception_handler
    raise dbt.exceptions.DatabaseException(str(e).strip()) from e
dbt.exceptions.DatabaseException: Database Error in model table1 (models/app/bod1/table1.sql)
  DPI-1002: invalid OCI handle
  compiled SQL at target/run/my_new_project/models/app/bod1/table1.sql
2021-07-27 16:51:16.783620 (Thread-1): Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '35c93ca4-bdb7-4114-b5b9-814d780b095b', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fe3757092e0>]}
2021-07-27 16:51:16.860326 (MainThread): Error sending message, disabling tracking
2021-07-27 16:51:16.860570 (MainThread): Connection 'master' was properly closed.
2021-07-27 16:51:16.861145 (Thread-1): 16:51:16 | 3 of 4 ERROR creating table model schema1.table1..... [ERROR in 105.74s]
2021-07-27 16:51:16.861467 (MainThread): Connection 'model.my_new_project.table1' was properly closed.
2021-07-27 16:51:16.861878 (Thread-1): Finished running node model.my_new_project.table1
2021-07-27 16:51:16.862228 (MainThread): Encountered an error:
2021-07-27 16:51:16.862758 (MainThread): not connected
2021-07-27 16:51:16.863655 (MainThread): Traceback (most recent call last):
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/task/runnable.py", line 339, in execute_nodes
    self.run_queue(pool)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/task/runnable.py", line 264, in run_queue
    self.job_queue.join()
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/graph/queue.py", line 173, in join
    self.inner.join()
  File "/home/user1/.conda/envs/sql/lib/python3.9/queue.py", line 90, in join
    self.all_tasks_done.wait()
  File "/home/user1/.conda/envs/sql/lib/python3.9/threading.py", line 312, in wait
    waiter.acquire()
KeyboardInterrupt

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/oracle/connections.py", line 134, in cancel
    Connection.close(oracle_connection)
cx_Oracle.OperationalError: ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 2976 Serial number: 56963

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/task/runnable.py", line 384, in execute_with_hooks
    res = self.execute_nodes()
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/task/runnable.py", line 347, in execute_nodes
    self._cancel_connections(pool)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/task/runnable.py", line 315, in _cancel_connections
    for conn_name in adapter.cancel_open_connections():
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/base/impl.py", line 1069, in cancel_open_connections
    return self.connections.cancel_open()
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/sql/connections.py", line 47, in cancel_open
    self.cancel(connection)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/oracle/connections.py", line 134, in cancel
    Connection.close(oracle_connection)
KeyboardInterrupt

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/main.py", line 125, in main
    results, succeeded = handle_and_check(args)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/main.py", line 203, in handle_and_check
    task, res = run_from_args(parsed)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/main.py", line 256, in run_from_args
    results = task.run()
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/task/runnable.py", line 426, in run
    result = self.execute_with_hooks(selected_uids)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/task/runnable.py", line 390, in execute_with_hooks
    adapter.cleanup_connections()
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/base/impl.py", line 175, in cleanup_connections
    self.connections.cleanup_all()
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/base/connections.py", line 198, in cleanup_all
    self.close(connection)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/base/connections.py", line 275, in close
    cls._close_handle(connection)
  File "/home/user1/.conda/envs/sql/lib/python3.9/site-packages/dbt/adapters/base/connections.py", line 234, in _close_handle
    connection.handle.close()
cx_Oracle.InterfaceError: not connected```

Relationship schema test not implemented

Example test:

Result:

Running with different materilizations leaves dirty objects

Oracle DBT version: 0.1.4
Python version: 3.6
Operating System: ubuntu 18

Description

Not sure the sequence of actions, but I think I ran dbt with models materialized as table, and then the same models materialized as views, it seems that the adapter is not cleaning the tables properly, and when the run try to create a temp view it fails because there is a table with the same name and view code only tries to delete views.

Maybe if the weird oracle procedure attempts to delete both table and view it would be safer

18:30:43 | 1 of 1 START view model BILABELLAMAFIA2.s_prd_class.................. [RUN]
/* {"app": "dbt", "dbt_version": "0.17.0", "profile_name": "lbm", "target_name": "prod", "node_id": "model.lbm.s_prd_class"} */
DECLARE
     dne_942    EXCEPTION;
     PRAGMA EXCEPTION_INIT(dne_942, -942);
  BEGIN
     EXECUTE IMMEDIATE 'DROP view BILABELLAMAFIA2.s_prd_class__dbt_tmp cascade constraint';
  EXCEPTION
     WHEN dne_942 THEN
        NULL; -- if it doesn't exist, do nothing .. no error, nothing .. ignore.
  END;
/* {"app": "dbt", "dbt_version": "0.17.0", "profile_name": "lbm", "target_name": "prod", "node_id": "model.lbm.s_prd_class"} */
DECLARE
     dne_942    EXCEPTION;
     PRAGMA EXCEPTION_INIT(dne_942, -942);
  BEGIN
     EXECUTE IMMEDIATE 'DROP view BILABELLAMAFIA2.s_prd_class__dbt_backup cascade constraint';
  EXCEPTION
     WHEN dne_942 THEN
        NULL; -- if it doesn't exist, do nothing .. no error, nothing .. ignore.
  END;
/* {"app": "dbt", "dbt_version": "0.17.0", "profile_name": "lbm", "target_name": "prod", "node_id": "model.lbm.s_prd_class"} */

  create view BILABELLAMAFIA2.s_prd_class__dbt_tmp as
  with renamed as (

    select 
    
    CAST(cd_tipoclas as int) as cd_tipoclas
,
    trim(cd_classificacao) as cd_classificacao,
    u_version, 
    
    CAST(cd_operador as int) as cd_operador
,
    dt_cadastro,
    ds_classificacao
    from 
    ulabellamafia.prd_classificacao
)


select * from renamed

Oracle error: ORA-00955: name is already used by an existing object
18:30:46 | 1 of 1 ERROR creating view model BILABELLAMAFIA2.s_prd_class......... [ERROR in 2.10s]

Recommend Projects

React

A declarative, efficient, and flexible JavaScript library for building user interfaces.
Vue.js

🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript

TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
TensorFlow

An Open Source Machine Learning Framework for Everyone
Django

The Web framework for perfectionists with deadlines.
Laravel

A PHP framework for web artisans
D3

Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

javascript

JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
web

Some thing interesting about web. New door for the world.
server

A server is a program made to process requests and deliver data to clients.
Machine learning

Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Visualization

Some thing interesting about visualization, use data art
Game

Some thing interesting about game, make everyone happy.

Recommend Org

Facebook

We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft

Open source projects and samples from Microsoft.
Google

Google ❤️ Open Source for everyone.
Alibaba

Alibaba Open Source for everyone
D3

Data-Driven Documents codes.
Tencent

China tencent open source team.