I’m working on packing up our current meltano prototype to move to a more production ready state based on the docs here. We’ll be using the containerized version of things and I need to set up a more persistant metadata store than SQL lite. Based on that, I had a few questions: • Does the database need to be postgres? Could I use mysql instead? (initial experimentation seems to suggest I can, but not sure if there are pitfalls I haven’t discovered yet) • What’s the best way to initialize the new metadata store? I could create CREATE/INSERT statements to replicate the contents of the SQLite db in a new place, but I wondered if there might be a one-liner that could do that more efficiently?

I agree that this is far from straight-forward 😞

“production” rarely is 🙂

Does the database need to be postgres? Could I use mysql instead?

I have not using MySQL so I’m honestly not 100% sure. I think all operations are backed by SQLAlchemy, so in theory this could work. I just don’t know if there are any compatibility issues which would become an issue.

What’s the best way to initialize the new metadata store?

Is there a reason not to allow Meltano to auto-create the database on demand? We actually have some users who run meltano without a backend. After each run, they export state explicitly so the database can be spun down, and then they send in the state file again on the next invocation.

is it possible to use the 

--select

 flag for a scheduled job in the same way one can from the command line?

When scheduling with Airflow (the default scheduler), I believe this would just require modifying the respective jobs in the Airflow dag. Hope this helps, @nick_hamlin!

Thanks @aaronsteers! That was my guess on mysql as well, and I’ve figured out how to get meltano to connect via SQLalchemy just fine (Tip for future searchers: replace

postgres

with

mysql-pymysql

in the connection string - specifying a modern mysql driver is important, otherwise it’ll default to MySQLdb, which doesn’t play nice with python 3). My preference would also be to let Meltano auto-create the database, which is what I assumed it would do when I updated

$DATABASE_URI

to point to the new database. It didn’t seem to do anything though, which is why I wasn’t sure if I needed some kind of init command to get it to kick things off again. Couldn’t find anything in the docs, but I suspect this probably exists

My preference would also be to let Meltano auto-create the database, which is what I assumed it would do when I updated  

$DATABASE_URI

  to point to the new database. It didn’t seem to do anything though, which is why I wasn’t sure if I needed some kind of init command to get it to kick things off again. Couldn’t find anything in the docs, but I suspect this probably exists

@nick_hamlin - Totally random, but this might be related to what you are looking for: https://meltano.slack.com/archives/C01TCRBBJD7/p1617646393244900?thread_ts=1617639532.243200&cid=C01TCRBBJD7

huh, interesting! that one is new to me as well

If you find that the database or schema is not initializing in a way you expect, you could try this

meltano schema create <SCHEMA> <ROLES>

command as a start.

thanks, will do!