https://linen.dev logo
Join Slack
Powered by
Hey I created a quick hacky target-mysql based on ...
# singer-targets
k
Hey I created a quick hacky target-mysql based on target-mssql if someone wants to test and help expand it https://github.com/siilats/siilats-target-mysql
d
Very cool @keith_siilats! cc @visch
What does it still need to be ready to be listed on hub.meltano.com/singer/targets/? (How about 
target-mssql
, @visch?)
k
I just tested it and added incremental update support, so it seems to be done. It needs someone to test it. I think it needs better docs on how to install ODBC driver on unix/mac for mariadb/mysql, would be good to have someone try installing it from my notes and see how far they get. I got it working with brew but the default docs on mariadb website don’t quite work. It would be nice to get the odbc “fast” option to work, I think it fails because of utf-8 vs utf-16 conversion, but the “slow” option is adequate for now. Finally if you look at the diff from target-mssql its just a few SQL syntax differences, we can make those differences into parameters and then have a generic target you can customize for different odbc drivers. Someone from your team is probably better at pulling the parameters out and making it look pretty python
v
Awesome just read through your code @keith_siilats To make this go live on the hub I think target-mssql should be migrated to use the singer_sdk target implementations. In production I've seen target-mssql get behind our tap (with fast_sync on) my gut is it's json serialization and poor optimization on my end. Singer_SDK is just going to be better than what I did, at a minimum the sdk will spin up multiple threads for each stream which will help significantly for my issue. Full table repliation isn't following the norms of singer at the moment. Currently we're creating a temp table, dropping the original table, then renaming the first table. Most of the singer stuff I've seen with DB's does upserts instead and will mark records as deleted instead of just deleting them. I'm not sure exactly where to go with this one but we should pick one (probably the current standard) and have that be the default. tldr; 1. Singer SDK migration 2. Full table replication should follow the standard "singer" way
3. Incremental replicaiton comes with #2 above
h
@keith_siilats So to use your MySQL target will I need to install an ODBC driver on my machine first?
k
yeah
I wrote some readme
on how to install it
h
Okay, I noticed you didn’t include the 
pyproject.toml
file so to install the Python module I had to recreate it and guess what the deps are. Do you think you can check that in? @keith_siilats
k
which repo did you use?
h
@keith_siilats Is this the wrong one? https://github.com/siilats/siilats-target-mysql
@keith_siilats I got it to run – hit a different error:
Copy code
Traceback (most recent call last):
  File "target-mysql/bin/target-mysql", line 8, in <module>
    sys.exit(cli())
  File "target-mysql/lib/python3.7/site-packages/click/core.py", line 1137, in __call__
    return self.main(*args, **kwargs)
  File "target-mysql/lib/python3.7/site-packages/click/core.py", line 1062, in main
    rv = self.invoke(ctx)
  File "target-mysql/lib/python3.7/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "target-mysql/lib/python3.7/site-packages/click/core.py", line 763, in invoke
    return __callback(*args, **kwargs)
  File "target-mysql/lib/python3.7/site-packages/target_mysql/singer_sdk/target.py", line 115, in cli
    target = cls(config=config)
  File "target-mysql/lib/python3.7/site-packages/target_mysql/target.py", line 34, in __init__
    database=self.config.get("database"),
pyodbc.Error: ('HY000', "[HY000] [ma-3.1.13]Unknown system variable 'session_track_schema' (1193) (SQLDriverConnect)")
k
Copy code
nknown system variable 'session_track_schema'
I basically made it enough so it worked on my one table
by the way you work on netsuite? have you figured out how to get system_notes
table
not just Notes, it only comes with ODBC through SuiteAnalytics
h
Yes I have worked on 
tap-netsuite
– not sure about system_notes
The error I’m seeing with 
target-mysql
is happening when pyodbc tries to connect to my DB though. Is it not compatible with MySQL 5.7+?
k
it works on my machine, I commited a pyproject toml
do you have the debugger setup in pycharm?
I think you need to match the ODBC and MySQL versions, I ended up using mariadb and it worked
people have ugly hacks about the system_notes with a cross join against an empty table
but I couldnt get it to work
stich doesnt release their tap-netsuite that has the suiteanalytics connector
h
got the target-mysql to run – had to downgrade my 
mariadb-connector-odbc
to 
3.1.12
got a new error 🙂
Copy code
pyodbc.ProgrammingError: ('42000', "[42000] [ma-3.1.12][5.5.62-0ubuntu0.14.04.1]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE `Contacts_temp`( `vid` VARCHAR(255), `profile-url` VARCHAR(255), `associat' at line 1 (1064) (SQLExecDirectW)")
ah I don’t think 
CREATE OR REPLACE TABLE
is valid MySQL syntax
Removed that and got the data to come through 🎉
v
happy dance
k
yeah i’m getting better at tap development I will pick the mysql thing up. if you don’t have create or replace you need drop if exists. Derek has that in his tap-mssql. We either need something that works on both or some config flags …
v
@keith_siilats me too 😄
2 Views
Open in Slack
PreviousNext
Powered by