storebrand / target-mssql Goto Github PK

View Code? Open in Web Editor NEW

3.0 4.0 14.0 289 KB

Singer.io compatible Target for SQL Server. Created with the Meltano SDK.

License: Apache License 2.0

Python 100.00%

target-mssql's Introduction

target-mssql

target-mssql is a Singer target for Microsoft SQL Server databases.

Build with the Meltano Target SDK.

Known limitations

Objects and arrays are converted to strings, as writing json/arrays isn't supported in the underlying library that is used.
Does not handle encoded strings

Installation

Install from Meltano:

meltano add loader target-mssql

Install from PyPi:

pipx install target-mssql

Install from GitHub:

pipx install git+https://github.com/storebrand/target-mssql.git@main

Configuration

Accepted Config Options

Regarding connection info, either the sqlalchemy_url or username, password, host, and database needs to be specified. If the sqlalchemy_url is set, the other connection parameters are ignored.

Capabilities

about
stream-maps
schema-flattening

Settings

Setting	Required	Default	Description
sqlalchemy_url	False	None	SQLAlchemy connection string
username	False	None	SQL Server username
password	False	None	SQL Server password
host	False	None	SQL Server host
port	False	1433	SQL Server port
database	False	None	SQL Server database
default_target_schema	False	None	Default target schema to write to
table_prefix	False	None	Prefix to add to table name
prefer_float_over_numeric	False	0	Use float data type for numbers (otherwise number type is used)
stream_maps	False	None	Config object for stream maps capability. For more information check out Stream Maps.
stream_map_config	False	None	User-defined config values to be used within map expressions.
flattening_enabled	False	None	'True' to enable schema flattening and automatically expand nested properties.
flattening_max_depth	False	None	The max depth to flatten schemas.

A full list of supported settings and capabilities for this target is available by running:

target-mssql --about

Configure using environment variables

This Singer target will automatically import any environment variables within the working directory's .env if the --config=ENV is provided, such that config values will be considered if a matching environment variable is set either in the terminal context or in the .env file.

Source Authentication and Authorization

Usage

You can easily run target-mssql by itself or in a pipeline using Meltano.

Executing the Target Directly

target-mssql --version
target-mssql --help
# Test using the "Carbon Intensity" sample:
tap-carbon-intensity | target-mssql --config /path/to/target-mssql-config.json

Developer Resources

Follow these instructions to contribute to this project.

Initialize your Development Environment

pipx install poetry
poetry install

Create and Run Tests

Create tests within the target_mssql/tests subfolder and then run:

poetry run pytest

You can also test the target-mssql CLI interface directly using poetry run:

poetry run target-mssql --help

Testing with Meltano

Note: This target will work in any Singer environment and does not require Meltano. Examples here are for convenience and to streamline end-to-end orchestration scenarios.

Next, install Meltano (if you haven't already) and any needed plugins:

# Install meltano
pipx install meltano
# Initialize meltano within this directory
cd target-mssql
meltano install

Now you can test and orchestrate using Meltano:

# Test invocation:
meltano invoke target-mssql --version
# OR run a test `elt` pipeline with the Carbon Intensity sample tap:
meltano elt tap-carbon-intensity target-mssql

SDK Dev Guide

See the dev guide for more instructions on how to use the Meltano SDK to develop your own Singer taps and targets.

target-mssql's People

Contributors

Stargazers

Watchers

Forkers

radbrt sagedata-ood marcelopio pnadolny13 autoidm anna-nylander smartr-ai fxtransparency symon-ai peliqan-io papalas matatika crugroup

target-mssql's Issues

JSON Schema "multipleOf" validation error - numeric

Getting the below error when trying to extract from a field that is of type numeric(11,7) in MSSQL

jsonschema.exceptions.ValidationError: 5.32 is not a multiple of 1e-07

Clarification on config for meltano

I am a little confused about the correct config keys to set in meltano, I am trying to connect to local SQLEXPRESS on Windows 11.

Should I provide only the sqlalchemy_url? Do you have an example connection string?

In the tests the separate user/pass/host/dbname are specified.

target-mssql/target_mssql/tests/test_core.py

Line 21 in 513b6cf

"schema": "dbo",

)

Which approach is correct?

EDIT: I think I managed to get this working with:

      username: sa
      password: hello
      host: localhost
      port: '1433'
      database: master
      schema: dbo

License

Could we get a license added to this repo. I just noticed one doesn't' exist and I can't put much work in if it's not permissive, guessing that was just missed on accident :D

sqlalchemy_url config doesn't work

Tried to pass in sqlalchemy_url but it didn't seem to work for me, looks like it's just ignored?

Windows Authentication support

If you remove username and password from https://github.com/storebrand/target-mssql/blob/main/target_mssql/connector.py#L65-L66 and you run on Windows this will connect to the SQL server just fine!

Arithmetic overflow error converting expression to data type int

I got this error trying to use this target with tap-microsoft-dataverse

2023-02-03T13:31:48.700704Z [info     ] 2023-02-03 10:31:48,700 Target 'target-mssql' completed reading 20 lines of input (18 records, (0 batch manifests, 1 state messages). cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:31:48.706271Z [info     ] 2023-02-03 10:31:48,706 Preparing table new_indicecorrecao cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.271696Z [info     ] 2023-02-03 10:32:01,271 Creating temp table new_indicecorrecao cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.441728Z [info     ] 2023-02-03 10:32:01,441 Inserting with SQL: INSERT INTO #new_indicecorrecao cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.441952Z [info     ] (createdonbehalfbyyominame, _modifiedonbehalfby_value, statecode, _createdonbehalfby_value, mrv_integracao_temporaria, importsequencenumber, organizationidname, modifiedbyyominame, versionnumber, utcconversiontimezonecode, createdbyyominame, modifiedbyname, new_name, _modifiedby_value, _createdby_value, timezoneruleversionnumber, new_indicecorrecaoid, new_descricao, modifiedon, modifiedonbehalfbyyominame, statuscode, createdbyname, createdon, _organizationid_value, createdonbehalfbyname, new_valormulta, modifiedonbehalfbyname, new_valorjuros, new_observao, overriddencreatedon, _ab_cdc_updated_at, _ab_cdc_deleted_at) cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.442065Z [info     ] VALUES (:createdonbehalfbyyominame, :_modifiedonbehalfby_value, :statecode, :_createdonbehalfby_value, :mrv_integracao_temporaria, :importsequencenumber, :organizationidname, :modifiedbyyominame, :versionnumber, :utcconversiontimezonecode, :createdbyyominame, :modifiedbyname, :new_name, :_modifiedby_value, :_createdby_value, :timezoneruleversionnumber, :new_indicecorrecaoid, :new_descricao, :modifiedon, :modifiedonbehalfbyyominame, :statuscode, :createdbyname, :createdon, :_organizationid_value, :createdonbehalfbyname, :new_valormulta, :modifiedonbehalfbyname, :new_valorjuros, :new_observao, :overriddencreatedon, :_ab_cdc_updated_at, :_ab_cdc_deleted_at) cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.562132Z [info     ] Traceback (most recent call last): cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.562487Z [info     ]   File "src/pymssql/_pymssql.pyx", line 461, in pymssql._pymssql.Cursor.execute cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.562673Z [info     ]   File "src/pymssql/_mssql.pyx", line 1087, in pymssql._mssql.MSSQLConnection.execute_query cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.562795Z [info     ]   File "src/pymssql/_mssql.pyx", line 1118, in pymssql._mssql.MSSQLConnection.execute_query cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.562897Z [info     ]   File "src/pymssql/_mssql.pyx", line 1251, in pymssql._mssql.MSSQLConnection.format_and_run_query cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.562999Z [info     ]   File "src/pymssql/_mssql.pyx", line 1789, in pymssql._mssql.check_cancel_and_raise cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.563094Z [info     ]   File "src/pymssql/_mssql.pyx", line 1835, in pymssql._mssql.raise_MSSQLDatabaseException cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.563188Z [info     ] pymssql._mssql.MSSQLDatabaseException: (8115, b'Arithmetic overflow error converting expression to data type int.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n') cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.563281Z [info     ]                                cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.563386Z [info     ] During handling of the above exception, another exception occurred: cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.563482Z [info     ]                                cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.563587Z [info     ] Traceback (most recent call last): cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.563718Z [info     ]   File "/home/marcelopio/repos/mrv/meltano-test/dataverse/.meltano/loaders/target-mssql/venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1880, in _execute_context cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.563920Z [info     ]     self.dialect.do_executemany( cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.564024Z [info     ]   File "/home/marcelopio/repos/mrv/meltano-test/dataverse/.meltano/loaders/target-mssql/venv/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 733, in do_executemany cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.564121Z [info     ]     cursor.executemany(statement, parameters) cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.564227Z [info     ]   File "src/pymssql/_pymssql.pyx", line 487, in pymssql._pymssql.Cursor.executemany cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.564321Z [info     ]   File "src/pymssql/_pymssql.pyx", line 479, in pymssql._pymssql.Cursor.execute cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2023-02-03T13:32:01.564413Z [info     ] pymssql._pymssql.OperationalError: (8115, b'Arithmetic overflow error converting expression to data type int.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n') cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql

Column name as Keyword

Hi,
The MSSQL DB has a column name which is called index, which is of course a keyword.
Error is pymssql._mssql.mssqldatabaseexception Incorrect syntax near ‘index’ . If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required ...
I use tap-mssql --variant buzzcutnorman as the extractor and it works as is expected.

Full Error message -> sqlalchemy.exc.OperationalError: (pymssql.exceptions.OperationalError) (1018, b”Incorrect syntax near ‘index’. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See See SQL Server Books Online for proper synatx.DB-Lib error message 20018, serverity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n”)
I think the problem is because the index has single quotation. The T-SQL syntax should be [index] for that case.

How I could sort this out, please?

Unable to install target-mssql

Hello,

I'm getting this error when trying to install this loader:

(meltano-ve-py3.10) (base) nancychelaru@MA0339L meltano_example % meltano install loader target-mssql
Installing 1 plugins...
Installing loader 'target-mssql'...
Loader 'target-mssql' could not be installed: failed to install plugin 'target-mssql'.
  Running command git clone --filter=blob:none --quiet https://github.com/storebrand/target-mssql.git /private/var/folders/p2/gtzckytd56xdwykp7s891_rm0000gp/T/pip-req-build-0l6z22ny
  error: subprocess-exited-with-error
  
  × Building wheel for pymssql (pyproject.toml) did not run successfully.
  │ exit code: 1
  ╰─> [43 lines of output]
      setup.py: platform.system() => Darwin
      setup.py: platform.architecture() => ('64bit', '')
      setup.py: platform.libc_ver() => ('', '')
      setup.py: include_dirs => []
      setup.py: library_dirs => []
      running bdist_wheel
      running build
      running build_py
      creating build
      creating build/lib.macosx-10.9-x86_64-cpython-310
      creating build/lib.macosx-10.9-x86_64-cpython-310/pymssql
      copying src/pymssql/__init__.py -> build/lib.macosx-10.9-x86_64-cpython-310/pymssql
      running build_ext
      warning: src/pymssql/_mssql.pyx:27:0: The 'DEF' statement is deprecated and will be removed in a future Cython version. Consider using global variables, constants, and in-place literals instead. See https://github.com/cython/cython/issues/4310
      warning: src/pymssql/_mssql.pyx:28:0: The 'DEF' statement is deprecated and will be removed in a future Cython version. Consider using global variables, constants, and in-place literals instead. See https://github.com/cython/cython/issues/4310
      warning: src/pymssql/_mssql.pyx:29:0: The 'DEF' statement is deprecated and will be removed in a future Cython version. Consider using global variables, constants, and in-place literals instead. See https://github.com/cython/cython/issues/4310
      warning: src/pymssql/_mssql.pyx:30:0: The 'DEF' statement is deprecated and will be removed in a future Cython version. Consider using global variables, constants, and in-place literals instead. See https://github.com/cython/cython/issues/4310
      warning: src/pymssql/_mssql.pyx:31:0: The 'DEF' statement is deprecated and will be removed in a future Cython version. Consider using global variables, constants, and in-place literals instead. See https://github.com/cython/cython/issues/4310
      warning: src/pymssql/_mssql.pyx:32:0: The 'DEF' statement is deprecated and will be removed in a future Cython version. Consider using global variables, constants, and in-place literals instead. See https://github.com/cython/cython/issues/4310
      warning: src/pymssql/_mssql.pyx:35:0: The 'DEF' statement is deprecated and will be removed in a future Cython version. Consider using global variables, constants, and in-place literals instead. See https://github.com/cython/cython/issues/4310
      warning: src/pymssql/_mssql.pyx:36:0: The 'DEF' statement is deprecated and will be removed in a future Cython version. Consider using global variables, constants, and in-place literals instead. See https://github.com/cython/cython/issues/4310
      warning: src/pymssql/_mssql.pyx:38:0: The 'DEF' statement is deprecated and will be removed in a future Cython version. Consider using global variables, constants, and in-place literals instead. See https://github.com/cython/cython/issues/4310
      warning: src/pymssql/_mssql.pyx:82:0: The 'IF' statement is deprecated and will be removed in a future Cython version. Consider using runtime conditions or C macros instead. See https://github.com/cython/cython/issues/4310
      warning: src/pymssql/_mssql.pyx:230:0: The 'DEF' statement is deprecated and will be removed in a future Cython version. Consider using global variables, constants, and in-place literals instead. See https://github.com/cython/cython/issues/4310
      warning: src/pymssql/_mssql.pyx:264:4: The 'IF' statement is deprecated and will be removed in a future Cython version. Consider using runtime conditions or C macros instead. See https://github.com/cython/cython/issues/4310
      warning: src/pymssql/_mssql.pyx:333:4: The 'IF' statement is deprecated and will be removed in a future Cython version. Consider using runtime conditions or C macros instead. See https://github.com/cython/cython/issues/4310
      warning: src/pymssql/_mssql.pyx:831:8: The 'IF' statement is deprecated and will be removed in a future Cython version. Consider using runtime conditions or C macros instead. See https://github.com/cython/cython/issues/4310
      warning: src/pymssql/_mssql.pyx:919:8: The 'IF' statement is deprecated and will be removed in a future Cython version. Consider using runtime conditions or C macros instead. See https://github.com/cython/cython/issues/4310
      warning: src/pymssql/_mssql.pyx:1005:12: The 'IF' statement is deprecated and will be removed in a future Cython version. Consider using runtime conditions or C macros instead. See https://github.com/cython/cython/issues/4310
      warning: src/pymssql/_mssql.pyx:1379:16: The 'IF' statement is deprecated and will be removed in a future Cython version. Consider using runtime conditions or C macros instead. See https://github.com/cython/cython/issues/4310
      warning: src/pymssql/_mssql.pyx:1713:8: The 'IF' statement is deprecated and will be removed in a future Cython version. Consider using runtime conditions or C macros instead. See https://github.com/cython/cython/issues/4310
      Compiling src/pymssql/_mssql.pyx because it changed.
      [1/1] Cythonizing src/pymssql/_mssql.pyx
      building 'pymssql._mssql' extension
      creating build/temp.macosx-10.9-x86_64-cpython-310
      creating build/temp.macosx-10.9-x86_64-cpython-310/src
      creating build/temp.macosx-10.9-x86_64-cpython-310/src/pymssql
      clang -Wno-unused-result -Wsign-compare -Wunreachable-code -DNDEBUG -fwrapv -O2 -Wall -fPIC -O2 -isystem /Users/nancychelaru/kedro_migration/miniconda3_python310/include -fPIC -O2 -isystem /Users/nancychelaru/kedro_migration/miniconda3_python310/include -I/Users/nancychelaru/meltano_example/.meltano/loaders/target-mssql/venv/include -I/Users/nancychelaru/kedro_migration/miniconda3_python310/include/python3.10 -c src/pymssql/_mssql.c -o build/temp.macosx-10.9-x86_64-cpython-310/src/pymssql/_mssql.o -DMSDBLIB
      src/pymssql/_mssql.c:1127:10: fatal error: 'sqlfront.h' file not found
      #include "sqlfront.h"
               ^~~~~~~~~~~~
      1 error generated.
      error: command '/usr/bin/clang' failed with exit code 1
      [end of output]
  
  note: This error originates from a subprocess, and is likely not a problem with pip.
  ERROR: Failed building wheel for pymssql
ERROR: Could not build wheels for pymssql, which is required to install pyproject.toml-based projects

Need help fixing this problem? Visit http://melta.no/ for troubleshooting steps, or to
join our friendly Slack community.

Failed to install plugin(s)

column names and table names create with underscores..?

Hello,
thanks for this great plugin , we are definitely planning to use this in a project.
I wanted to try the functionality in an easy example: read from CSV ( with header, comma sep., "" ) , write into a SQL server table.
The data is kind of garbled, it creates columns with underscores like c_c_u_s_t_k_e_y instead of c_custkey, same for schema and table name. Is there an easy fix ?
Thanks!

2022-11-30T17:09:59.245174Z [info ] 2022-11-30 18:09:59,244 Inserting with SQL: INSERT INTO #customer cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-30T17:09:59.247173Z [info ] (c_c_u_s_t_k_e_y, c_n_a_m_e, c_a_d_d_r_e_s_s, c_n_a_t_i_o_n_k_e_y, c_p_h_o_n_e, c_a_c_c_t_b_a_l, c_m_k_t_s_e_g_m_e_n_t, c_c_o_m_m_e_n_t) cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql
2022-11-30T17:09:59.250183Z [info ] VALUES (:c_c_u_s_t_k_e_y, :c_n_a_m_e, :c_a_d_d_r_e_s_s, :c_n_a_t_i_o_n_k_e_y, :c_p_h_o_n_e, :c_a_c_c_t_b_a_l, :c_m_k_t_s_e_g_m_e_n_t, :c_c_o_m_m_e_n_t) cmd_type=elb consumer=True name=target-mssql producer=False stdio=stderr string_id=target-mssql

extractors:
`- name: tap-csv
variant: meltanolabs
pip_url: git+https://github.com/MeltanoLabs/tap-csv.git
settings:
- name: files
kind: array
value:
- entity: customer
path: customer.csv
keys:
- C_CUSTKEY
loaders:

name: target-mssql
variant: storebrand
pip_url: git+https://github.com/storebrand/target-mssql.git
config:
user: abc
password: abc
host: abc.database.windows.net
database: abc_dem0
port: 1433`

Conforming Names issue

I have a key property with two fields of of Unique Record ID , and _smart_source_bucket in my source data. The initial table get's created with the "correct" name of unique_record_id but the merge statement gets messed up and looks like this

MERGE INTO WIDA.wida AS target
            USING WIDA.#wida AS temp
            ON temp.unique _record id = target.unique _record id and temp._smart_source_bucket = target._smart_source_bucket
            WHEN MATCHED THEN

Notice temp.unique _record id isn't correct.

I honestly would much rather not have the names be conformed at all other than swapping any -'s for _'s , because I can imagine we're going to hit N number of these kind of issues I'd rather not deal with!

Merge statement, dealing with NULLs

The current merge statement condition https://github.com/storebrand/target-mssql/blob/main/target_mssql/sinks.py#L216 doesn't deal with NULLs.

There is a way to do this with just boolean math, but I think the easiest way is to use except, I've been down this rabbit hole a few times if whoever is doing this has more questions!

Support 'table_prefix'

It is possible to support something like table_prefix?

I would like to collect multiple tap streams under a single database schema, prefixing each table with ${MELTANO_EXTRACTOR_NAMESPACE} to give a bit more order / ease of filtering.

https://hub.meltano.com/loaders/target-bigquery--adswerve/#table_prefix-setting

Consider increasing Numeric spec to from 22,16 to 28,16

Using https://github.com/voxmedia/tap-facebook-pages, one schema declares the value field as number, it can contain reasonable sized decimals and some large integers (i.e. total video view time in seconds across 28 days).

I think the current numeric spec is NUMERIC(22, 16)) leaving 8 digits to the left size of the point.

target-mssql/target_mssql/connector.py

Line 361 in f043a2a

return cast(sqlalchemy.types.TypeEngine, sqlalchemy.types.NUMERIC(22, 16))

Could this be increased to NUMERIC(28, 16))?

From looking at the spec, it would seem to use up the same amount of memory as 22,16, so no impact there.

https://learn.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql?view=sql-server-ver16#arguments

I will do some testing in a fork to see if it resolves the issue.

This is a test

some description of the issue