https://linen.dev logo
Join Slack
Powered by
Hi all! I'm new using Meltano UI and Meltano extra...
# announcements
a
Hi all! I'm new using Meltano UI and Meltano extractors. However, I've used singer before. So, now that I'm testing Meltano UI and its extractors, I have a question. Can I add singer-taps into Meltano UI? for example, the mysql tap is not supported by Meltano UI but the tap it's available at Singer...So, that's what I want to accomplish. Is there any form to do this? I appreciate your help 🙂
y
Hey @antony_henao, Yes you can! Please check our advanced tutorial on how to add a Custom Extractor to a Meltano Project: https://www.meltano.com/docs/tutorial.html#advanced-create-a-custom-extractor We call them custom, but they can be any Singer.io Tap/Target that are not officially supported in Meltano or a custom one of course. The same advice for adding a custom extractor (Tap) holds when adding a custom loader (Target). You can also check a more practical example on how to add 
tap-postgres
to a Meltano postgres: https://www.meltano.com/docs/tutorial.html#advanced-using-tap-postgres-with-meltano
After a Tap/Target is properly added, it should show up without issues in Meltano UI, together with its custom configuration options. (Or @derek_knox should be notified about a possible bug in our UI)
Also, if you check that a Singer.io Tap/Target works properly with Meltano, can you please inform us and provide the configuration required so that we can officially support it? (you'll know what I mean by configuration required after you check the tutorial above)
a
Thanks @yannis_roussos! I'll take a look on the info. Once I have something I'll let you know 😄
Hi @yannis_roussos, can you help with a question I have related to .env file?
y
please tell me
a
I'm checking the tutorial above and I'm not really sure what should I add in the .env
Copy code
export TAP_PG_DATABASE=my_source_db
export TAP_PG_ADDRESS=localhost
export TAP_PG_PORT=5432
export TAP_PG_USERNAME=source_username
export TAP_PG_PASSWORD=source_password
Such code is from the tutorial. What variables should I add to my project? I mean, what is my_source_db, localhost, etc...
Should I hard-code the connection in the .env?
I put my variables in this way in the .env
Copy code
TAP_MS_DATABASE=""
TAP_MS_ADDRESS=""
TAP_MS_PASSWORD=""
TAP_MS_PORT=33306
TAP_MS_USERNAME=""
but...I'm trying to test the tap in the Meltano UI and I'm getting next error
Copy code
ERROR Giving up connect_with_backoff(...) after 5 tries (pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 111] Connection refused)"))
so...I don't know if there is any problem with .env variables
y
So, the tutorial above is for using 
tap-postgres
Those variables are the ones required by 
tap-postgres
to work
and in the tutorial I added them to the custom tap I created
you have to do the same for the tap you are using for accessing MySQL
a
I think that is the point I'm missing. What should I add in those variables? so far, this what a I have on my .env
Copy code
FLASK_ENV=development
SQLITE_DATABASE=meltano
PG_PASSWORD=warehouse
PG_USERNAME=warehouse
PG_ADDRESS=localhost
PG_SCHEMA=meltano
PG_PORT=5502
PG_DATABASE=warehouse

TAP_MS_DATABASE=""
TAP_MS_ADDRESS=""
TAP_MS_PASSWORD=""
TAP_MS_PORT=33306
TAP_MS_USERNAME=""
y
OK, which tap are you trying to use with which target?
the above indicate to me that you want to export from MySQL and Import the data to a Postgres DB
If so, you have to add whatever variables are required by the tap, similar to what I did for using 
tap-postgres
1. Check the tap and the variables required to make it work 2. Follow the steps in the tutorial to add the variables in meltano.yml 3. Add the variables also in your 
.env
is the above clear?
a
Kind of...So, the configuration for the TAP is not independent of the TARGET?
I mean...I'm configuring the TAP but I want to able to change the target to CSV or Postgress or any target
so, I don't know why should I know now what is the target. For now I just want to be able to configure the target, so I can finish the pipeline via Meltano UI
y
of course they are, I am just trying to understand your setup 🙂
a
ok ok
forgive me...Let me try something...
I keep getting this error
Copy code
ERROR Giving up connect_with_backoff(...) after 5 tries (pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 111] Connection refused)"))
y
are you sure that your connection settings are correct?
that seems like wrong credentials for MySQL, so the tap can not connect to MySQL
a
Ups...I think I it's fine. I just try them using DBeaver and they work
y
can you make the tap that you are using work by itself? without meltano
a
Let me try
y
Sorry but my hands are tied cause this is a custom tap
a
No problem, Yannis. You have help me a lot 😄
yep...The tap works by itself
so, I think I'm missing something in the Meltano config
y
ok, that’s good 🙂 Can you give me the 
config.yml
you are using to make it work? (replace user and password with *)
a
do you mean the config.json from the tap?
or do you mean the meltano.yml?
y
Yes 🙂 I’ll use that info to guide you through the steps you need to go through to add it to Meltano
a
ok ok...I think I see the problem now
this is my meltano.yml
Copy code
extractors:
  - config:
      default_replication_method: FULL_TABLE
      include_schemas_in_destination_stream_name: true
    executable: tap-mysql
    name: tap-mysql
    namespace: tap-mysql
    pip_url: tap-mysql
    settings:
    - env: TAP_MS_DATABASE
      name: dbname
    - env: TAP_MS_ADDRESS
      name: host
    - env: TAP_MS_PASSWORD
      name: password
    - env: TAP_MS_PORT
      name: port
    - env: TAP_MS_USERNAME
      name: user
or at least my tap configuration
I didn't hard-code the DB configuration in the .yml
y
And your tap's config.yml when you made it work?
a
Copy code
{
  "host": "***",
  "port": "33306",
  "user": "***",
  "password": "***"
}
the config.yml does have tha hard-code DB configuration...
y
great, so all the steps to make it work are as follows:
Step 1:
Copy code
meltano add --custom extractor tap-mysql

  (namespace): tap-mysql
  (pip_url): tap-mysql
  (executable) [tap-postgres]: tap-mysql
STEP 2: update 
meltano.yml
Copy code
...
  extractors:
  - executable: tap-mysql
    name: tap-mysql
    namespace: tap-mysql
    pip_url: tap-mysql
    settings:
      - name: host
        env: TAP_MS_HOST
      - name: password
        env: TAP_MS_PASSWORD
      - name: port
        env: TAP_MS_PORT
      - name: user
        env: TAP_MS_USERNAME
....
You do not need the rest I have in the tutorial for Postgres, only what is required by 
tap-mysql
to work STEP 3: update your 
.env
Copy code
...
export TAP_MS_HOST=*****
export TAP_MS_PORT=33306
export TAP_MS_USERNAME=*****
export TAP_MS_PASSWORD=*****
...
STEP 4: load the .env and test
Copy code
source .env
meltano config tap-mysql 
meltano invoke tap-mysql
The last step will just dump everything to stdout to make sure that the tap is properly setup in Meltano
you can quit the process if everything is ok or continue debuging
when you are set and it works, you can run: 
meltano elt tap-mysql target-postgres
You should be able to filter tables and run only for selected ones, as I can see from their documentation that they have pretty unique `tap_stream_id`s
a
@yannis_roussos, thank you sooooo much. I apprecite the help you've give so far
It works. I have a problem with the replication method
Copy code
Exception: only INCREMENTAL, LOG_BASED, and FULL TABLE replication methods are supported
y
is this a 
tap-mysql
error?
a
I just add the replication to the config
Copy code
extractors:
  - executable: tap-mysql
    name: tap-mysql
    namespace: tap-mysql
    pip_url: tap-mysql
    settings:
      - name: host
        env: TAP_MS_HOST
      - name: password
        env: TAP_MS_PASSWORD
      - name: port
        env: TAP_MS_PORT
      - name: user
        env: TAP_MS_USERNAME
    config:
      default_replication_method: FULL_TABLE
      include_schemas_in_destination_stream_name: true
I'm not really sure...Let me see
y
There is not such option for 
tap-mysql
and from what I can see from their docs, it should work by default
Yep I can see the error, that's on 
tap-mysql
end
a
oh, ups...
😅
y
They need info on the replication method when a catalog is provided
and they do not generate one by default (I assume) 😞
Let's find out
Can you please run the following?
Copy code
meltano invoke tap-mysql --discover > .meltano/run/tap-mysql/tap.properties.json
and then check the .json generated
Search for 
"replication-method"
do they generate those entries?
a
yep...Give me a moment please
ups...It failed
Copy code
Traceback (most recent call last):
  File "/home/aj/Servex/meltano/meltano-dev/.meltano/extractors/tap-mysql/venv/bin/tap-mysql", line 10, in <module>
    sys.exit(main())
  File "/home/aj/Servex/meltano/meltano-dev/.meltano/extractors/tap-mysql/venv/lib/python3.7/site-packages/singer/utils.py", line 225, in wrapped
    return fnc(*args, **kwargs)
  File "/home/aj/Servex/meltano/meltano-dev/.meltano/extractors/tap-mysql/venv/lib/python3.7/site-packages/tap_mysql/__init__.py", line 713, in main
    args = utils.parse_args(REQUIRED_CONFIG_KEYS)
  File "/home/aj/Servex/meltano/meltano-dev/.meltano/extractors/tap-mysql/venv/lib/python3.7/site-packages/singer/utils.py", line 176, in parse_args
    args.catalog = Catalog.load(args.catalog)
  File "/home/aj/Servex/meltano/meltano-dev/.meltano/extractors/tap-mysql/venv/lib/python3.7/site-packages/singer/catalog.py", line 82, in load
    return Catalog.from_dict(json.load(fp))
  File "/home/aj/anaconda3/lib/python3.7/json/__init__.py", line 296, in load
    parse_constant=parse_constant, object_pairs_hook=object_pairs_hook, **kw)
  File "/home/aj/anaconda3/lib/python3.7/json/__init__.py", line 348, in loads
    return _default_decoder.decode(s)
  File "/home/aj/anaconda3/lib/python3.7/json/decoder.py", line 337, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/home/aj/anaconda3/lib/python3.7/json/decoder.py", line 355, in raw_decode
    raise JSONDecodeError("Expecting value", s, err.value) from None
json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)
y
That's a very strange error to get when generating a catalog
did you run 
meltano invoke tap-mysql --discover
?
a
yep
y
Can you do me one more favor and run 
tap-mysql --discover
wherever you were running the tap by itself?
a
It works. I did it before 😅
y
ok can you check the json generated there?
I only want to confirm that this tap is not designed to work with an automated process like Meltano
a
It seems the file doesn't have nothing related to the replication_method
y
OK, so that's what happening here:
Unfortunately, the creators of 
tap-mysql
do not generate those entries by default (let's say with a default value of FULL_TABLE)
But at the same time, if you provide a catalog without those entries, they do not pick a default value: https://github.com/singer-io/tap-mysql/blob/master/tap_mysql/__init__.py#L657
That means that they expect a user of that tap that has a decent DB with 100s of tables, to go manually through thousands of lines and add this entry manually for each table to be extracted (i.e. 100s of times)
This is more or less what happens also with selecting streams/attributes, but we have managed to automate that in meltano through 
meltano select
Unfortunately, we do not have support yet for those custom attributes that relate to DBs (
replication-method
and 
replication-key
)
a
ohhh ups...
y
So, I am really sorry, but there is no way to use that tap at the moment with Meltano.
This shows me a clear problem that we have to solve, like we did with automating the selection of streams and attributes. As it is clearly crazy to require that someone will manually add those entries.
And that's a problem in general with taps that delegate the configuration to editing the catalog.json. This is something we have to check for sure and help the community with using Taps and Targets more efficiently.
a
😅
@yannis_roussos, I understand. I really appreciate all your help! you are amazing!
I think you guys are doing a good work!!!! thanks for that 🙂
y
Thank you 🙂 I am really sorry that you have spent so much time trying to make this work. You can always use the tap directly and edit the generated catalog. And maybe use Meltano for the Transformation and Analysis parts
a
For sure I'll keep my eyes on Meltano!! I believe this an amazing project. I'll let you know I can make work on my daily basis 😉
again, thanks for everything
y
Please do and send any critique you may have our way!
13 Views
Open in Slack
PreviousNext
Powered by