Comments (7)
I can confirm this works. That being said, I am on a mac and I had to run this AFTER the pip install because the dependency is for a non-M1 mac
pip uninstall pyodbc
pip install --no-binary :all: pyodbc
I also had to use a different endpoint
server: xxx.sql.azuresynapse.net
vs
server: xxx.azuresynapse.net
from dbt-synapse.
We have a similar problem. For now, we have rolled back to 1.4.0, which still works.
from dbt-synapse.
hey @noel -- you got a smidge more context to share?
from dbt-synapse.
haha sorry, did not mean to submit that. I was trying to reproduce and get more info before submitting.
Basically I did the following (on Mac)
HOMEBREW_ACCEPT_EULA=Y brew install msodbcsql18 mssql-tools18
also tried
HOMEBREW_NO_ENV_FILTERING=1 ACCEPT_EULA=Y brew install msodbcsql17 mssql-tools
and then install dbt-synapse.
This is my profiles.yml
default:
target: dev
outputs:
dev:
type: synapse
driver: 'ODBC Driver 18 for SQL Server'
server: xxx.azuresynapse.net
port: 1433
database: xxx_sql_pool_synapse
schema: dbo
user: sqladminuser
password:
I am able to connect to that end point using Azure Data Studio, but dbt would just time out.
('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
However, now I cant even get the dbt adapter to show up now. (I had deleted the venv to make sure there wasnt something else)
✗ ls /Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages | grep dbt
dbt
dbt-1.0.0.36.9.dist-info
dbt_core-1.7.9.dist-info
dbt_extractor
dbt_extractor-0.5.1.dist-info
dbt_fabric-1.7.4.dist-info
dbt_semantic_interfaces
dbt_semantic_interfaces-0.4.4.dist-info
dbt_synapse-1.7.0.dist-info
➜ [balboa]balboa (main) ✗ dbt --version
Core:
- installed: 1.7.9
- latest: 1.7.9 - Up to date!
Plugins:
dbt debug throws an error
✗ dbt debug
21:16:51 Running with dbt=1.7.9
21:16:51 dbt version: 1.7.9
21:16:51 python version: 3.8.18
21:16:51 python path: /Users/noel/.virtualenvs/balboa/bin/python
21:16:51 os info: macOS-14.3.1-arm64-arm-64bit
21:16:51 Encountered an error:
dlopen(/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/pyodbc.cpython-38-darwin.so, 0x0002): symbol not found in flat namespace '_SQLAllocHandle'
21:16:51 Traceback (most recent call last):
File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/cli/requires.py", line 91, in wrapper
result, success = func(*args, **kwargs)
File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/cli/requires.py", line 76, in wrapper
return func(*args, **kwargs)
File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/cli/main.py", line 444, in debug
results = task.run()
File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/task/debug.py", line 123, in run
load_profile_status: SubtaskStatus = self._load_profile()
File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/task/debug.py", line 212, in _load_profile
profile: Profile = Profile.render(
File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/config/profile.py", line 436, in render
return cls.from_raw_profiles(
File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/config/profile.py", line 401, in from_raw_profiles
return cls.from_raw_profile_info(
File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/config/profile.py", line 355, in from_raw_profile_info
credentials: Credentials = cls._credentials_from_profile(
File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/config/profile.py", line 165, in _credentials_from_profile
cls = load_plugin(typename)
File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/adapters/factory.py", line 212, in load_plugin
return FACTORY.load_plugin(name)
File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/adapters/factory.py", line 58, in load_plugin
mod: Any = import_module("." + name, "dbt.adapters")
File "/Users/noel/.asdf/installs/python/3.8.18/lib/python3.8/importlib/__init__.py", line 127, in import_module
return _bootstrap._gcd_import(name[level:], package, level)
File "<frozen importlib._bootstrap>", line 1014, in _gcd_import
File "<frozen importlib._bootstrap>", line 991, in _find_and_load
File "<frozen importlib._bootstrap>", line 975, in _find_and_load_unlocked
File "<frozen importlib._bootstrap>", line 671, in _load_unlocked
File "<frozen importlib._bootstrap_external>", line 843, in exec_module
File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/adapters/synapse/__init__.py", line 3, in <module>
from dbt.adapters.synapse.synapse_adapter import SynapseAdapter
File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/adapters/synapse/synapse_adapter.py", line 6, in <module>
from dbt.adapters.fabric import FabricAdapter
File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/adapters/fabric/__init__.py", line 3, in <module>
from dbt.adapters.fabric.fabric_adapter import FabricAdapter
File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/adapters/fabric/fabric_adapter.py", line 24, in <module>
from dbt.adapters.fabric.fabric_connection_manager import FabricConnectionManager
File "/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/dbt/adapters/fabric/fabric_connection_manager.py", line 10, in <module>
import pyodbc
ImportError: dlopen(/Users/noel/.virtualenvs/balboa/lib/python3.8/site-packages/pyodbc.cpython-38-darwin.so, 0x0002): symbol not found in flat namespace '_SQLAllocHandle'
from dbt-synapse.
I got past that error by running
pip uninstall pyodbc
pip install --no-binary :all: pyodbc
now dbt debug shows
21:21:27 1 check failed:
21:21:27 dbt was unable to connect to the specified database.
The database returned the following error:
>Database Error
('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
from dbt-synapse.
hi @noel @deepskydata !
raised this Pull Request to fix this issue
i.e. failing to connect to synapse using authentication: sql
and user: sqladminuser
.
Before merging PR, please help test if fix works:
-
in
requirements.txt
, specifydbt-synapse
package as a Github commit, by adding this line:
dbt-synapse @ git+https://github.com/microsoft/dbt-synapse@d26c475fb8101b62f456f40f20cd0235e3604aed
(instead of usingdbt-synapse v1.7.0
from PyPI) -
in a fresh virtual environment, install
requirements.txt
(e.g. runpip install -r requirements.txt
) -
in
profiles.yml
, addauthentication: sql
.
user: sqladminuser
and everything else remains the same.
default:
target: dev
outputs:
dev:
type: synapse
driver: 'ODBC Driver 18 for SQL Server'
server: xxx.azuresynapse.net
port: 1433
database: xxx_sql_pool_synapse
schema: dbo
authentication: sql
user: sqladminuser
password:
- run
dbt debug
, gotAll checks passed!
on my Windows
-----------optional explanation-------------------
dbt-synapse v1.4.1
onwards, dependency was switched fromdbt-sqlserver
todbt-fabric
, where synapse_connection_manager inherits fabric_connection_manager
problem: dbt-fabric does not support "sql" as authentication method, so fabric_connection_manager line 317 throws error when trying to authenticate using authentication: sql
and user: sqladminuser
in profiles.yml
fix: in synapse_connection_manager, overwrite the error-throwing method to accept authentication: sql
, and handle it by adding specified user: sqladminuser
and password
to connection string, to successfully connect with synapse
from dbt-synapse.
thanks @noel !
fix is released in Github v1.7.1, also released on PyPI
cc @prdpsvs
from dbt-synapse.
Related Issues (20)
- Changing the distribution and index types in existing snapshots don't have any effect HOT 1
- upgrade to support dbt-core v1.4.0 HOT 2
- Stand up new CI-testing pool HOT 1
- upgrade to support dbt-core v1.5.0
- This repo is missing important files
- upgrade to support dbt-core v1.6.0
- Incremental table creation has unneccessary indexes, write temp table as a HEAP HOT 1
- Satellite problem when using case sensitive collations HOT 1
- upgrade to support dbt-core v1.7.0
- Version of adapter is not visible for `dbt --version` command HOT 2
- upgrade to support dbt-core v1.8.0
- `get_limit_subquery()` does not work with queries containing CTEs HOT 1
- Synapse sql login not working HOT 3
- Set `database=False` in `SynapseRelation.include_policy` instead of overriding `ref` macro HOT 3
- dbt-synapse `1.8.0rc1` broken by dbt-fabric `1.8.3` HOT 3
- `'tmp_relation' is undefined` when altering a column's datatype on an incremental model HOT 1
- Docker image for dbt-synapse HOT 1
- SQLAuth does not work in 1.8 HOT 1
- dbt-utils generate_surrogate_key macro fails for versions 1.4+ when only one field is supplied
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.
from dbt-synapse.