I'm experimenting with extracting data from gitlab to my local postgres server. And I came across this problem: • Official target-postgres have some limitation on JSON schema. Specifically, it does not support anyOf, allOf. • While tap-gitlab, from meltano repo, does have a lot of field that specified data type as anyOf, allOf, for example, group_labels table I tried to remove fields that causing problem, loading to postgres and managed to run it without error, but of course there are missing data. I also tried, use meltano's target-postgres, but for some reason it failed to work as well. The problem is, with the missing data. I cannot run dbt-gitlab correctly without changing its models. My question is that, what is the proper way to make all 3 components (tap, target, dbt) work well together? Changing json schema from tap to make it work, config target, etc? Another question: have meltano's target-postgres fixed mentioned limitation with json schema?

Thank you for the great questions @tuan_cao! Let me start with how Meltano Transforms work: Our pre-bandled transformations (i.e. the dbt packages we automatically use in Meltano for specific extractors) are built to work with our own Taps and Targets. That means they work with our own version of

tap-gitlab

that has a lot of updates on top of the official

tap-gitlab

(a lot more entities extracted) and our own version of

target-postgres

that also has updates to fix bugs and make it work with additional option JSON schema provides (like the anyOf and allOf you describe). Those

tap-gitlab

and

target-postgre

are the ones added by Meltano when you:

meltano add extractor tap-gitlab
meltano add loader target-postgres

The reason for that is that dbt transformations, like any transformation, depend on a specific schema to be there. If you use a different extractor (tap) that defines the schema differently for the same API then a lot of attributes or tables that the Transformations expect will not be there. The same holds in case you use a different target that follows different attribute naming convention rules. For example, both us and Stitch use `_`s to convert arbitrary attribute names to proper SQL identifiers and un-nest nested structures using a

__

convention (e.g.

attibute_name__sub_attribute_name_

-->

author__id

). This is not true for all Loaders (targets) out there, which means that switching the loader will require a different Transformation to be built on top of it. That means that if you want to use Meltano out of the box you have to use our supported Extractors and Loaders either through Meltano UI or by adding them in the cli:

meltano add extractor {extractor}
meltano add loader {loader}
meltano add transformer dbt
meltano add transform {extractor}

Notes: (1) We want to support the official

target-postgres

. Our own version is based on the old

target-postgres

and we even consider switching to it, but until we officially do that and add transformations for it, we can only support our own fork. (2) We also want to contribute our updates to various Taps and Targets back to the official Singer.io Taps and Targets. Especially in the case of

tap-gitlab

, our own version is far more feature complete compared to the Singer.io one. This would be a great point that someone could help us with.

that's terrific, thanks so much @yannis_roussos

Hey Tuan! Do you set the correct port and address? Can you connect to it using

psql

from the cli?

create .env file with following variable

The

PG_PORT

and

PG_ADDRESS

seem weird, but if that's your setup and it works, then it should be OK. But please test that it works

message has been deleted

"host.docker.internal" to address: Name or service not known

This is not discoverable. You should find a way to make it work

right, to double check

Even before that, it can not resolve the name

right

I am really sorry @tuan_cao I am not a docker guy. Maybe someone else can help with this one. (@micael_bergeron)

no problem at all, me too 😞

I can only propose to check with a local Postgres and a regular venv to get a feeling of how things work until someone can help with the docker setup.

hmm, I can't seem to find target-postgres on meltano group repo anymore 😅

Yes, https://github.com/meltano/target-postgres

@yannis_roussos I managed to use Meltano on Docker, with Data Source = Gitlab and Target = SQLite, however since we are using Jira for tracking our issues, we don't use Gitlab Issues. And this cause some error when trying to render graph in Analyze step. It seem like data model make some assumption on dev team using both gitlab issues and repo for modeling. My question is: if that's the case, what the philosophy behind this? If not is that a known issues, how should I deal with it?

That's a great comment @tuan_cao and thank you for reporting it! Yes, unfortunately we have built all the Transforms and Models for Gitlab assuming that issues will be there. Our pre-bundled Transforms and Models should not be very useful if your team does not use Gitlab in that way. But you should not get errors if something is missing, just empty reports. The reason that you get errors is that you used SQLite: We do not support Transforms for SQLite, as dbt does not work over SQLite. We only support Postgres for the steps after the Extract and Load ones. That means that you may load the data to an SQLite, but Transforms will never run and there will be no data in the

analytics

for Meltano Analyze to find something. That's the reason you get the errors. Meltano Analyze tries to use the Connection for Postgres and access the

analytics

schema but there is nothing there. We know that we did not properly communicate the aforementioned limitations in previous versions of Meltano, that's why in the latest release we have remove SQLite from the available options in Meltano UI. Like the loader for Snowflake, it is still available for advanced users that just want to extract and load data, but we have only left the Postgres option for the regular use of Meltano. I would propose that you setup and use Postgres if you want to make use of the full features offered by Meltano. Back to our Gitlab Transforms and Models: I would advise that you check what we have there after we start using Postgres and see if what we have covers your use case. If you want different Transforms or Models, you can selectively build and add ones yourself in your project. If our pre-bundled dbt transforms and tables in the

analytics

schemas cover your needs, you can only add a different Model in there (for example, I am pretty sure that even though you bring in branches and commits, I have not added Models to expose that info in Meltano Analyze). If you need some other grouping of the data, you can also add your own dbt transforms that can mix and match data on top of both the RAW tables brought in by the Extractor and our own transformed tables. I can help you go through that process if you get stuck! And if your transforms and Models prove to be useful, you could even contribute them back to Meltano and we can add them to the pre-bundled ones we offer for all users.

ah, I see!. That makes sense. Thank so much for your very detailed and informative answers! I'll try out your suggestion, using a Postgres and play around with pre-bundled dbt models.