https://linen.dev logo
Join Slack
Powered by
Over the weekend, I built an orchestrator for Melt...
# announcements
d
Over the weekend, I built an orchestrator for Meltano on top of GitLab CI, using (dynamically generated!) pipeline schedules: https://gitlab.com/DouweM/orchestrator-gitlab-test/pipeline_schedules https://gitlab.com/meltano/meltano/-/merge_requests/1620 https://gitlab.com/meltano/gitlab-ci-scheduler. Those repos prove the concept, and just a little more work is required to make deploying Meltano as easy as scheduling some EL(T) pipelines using the UI or 
meltano schedule
 , running 
meltano install orchestrator gitlab-ci-scheduler
 (which will add 
.gitlab-ci.yml
 and 4 files under 
orchestrate
), and pushing up to GitLab!
Pipeline state is stored using artifacts, as is file-based output when using the target-csv and target-jsonl loaders
t
This is semi related to this announcement but I'm just starting to use Meltano now and was already thinking that I might be able to get away with just using a Gitlab CI job on a schedule to run 
meltano etl ...
to be able to fetch the data I wanted without needing to run Meltano as a long lived thing or use Airflow as a scheduler. I did notice that the most recent state for a job seems to be stored in the database though and is only written out as a 
state.json
file when running the next 
meltano etl
command so I don't see how I could do this without extracting data since the job before the last one? Or is this only the case for the first run?
This is some terminal output where you can see the output from yesterday's run. I then ran 
meltano etl ...
in another terminal and noticed that it said it was using the state from yesterday:
Copy code
meltano      | Running extract & load...
meltano      | Found state from 2020-09-14 15:15:05.539318.
but there wasn't a 
state.json
file before as you can see from the file timestamp. Also it still seemed to do a complete extraction for most of the tables as you can see from the output I have now:
Copy code
tom@tom-cloudnc-laptop:~/git/meltano-projects/gitlab-metrics$ ll output -h
total 296M
drwxrwxr-x  2 tom tom 4.0K Sep 15 11:51 ./
drwxrwxr-x 12 tom tom 4.0K Sep 14 17:27 ../
-rw-rw-r--  1 tom tom 312K Sep 14 17:06 branches-20200914T161507.jsonl
-rw-rw-r--  1 tom tom 314K Sep 15 11:51 branches-20200915T112405.jsonl
-rw-rw-r--  1 tom tom 4.4M Sep 14 17:06 commits-20200914T161507.jsonl
-rw-rw-r--  1 tom tom 8.6K Sep 15 11:51 commits-20200915T112405.jsonl
-rw-rw-r--  1 tom tom  75K Sep 14 16:41 issues-20200914T161507.jsonl
-rw-rw-r--  1 tom tom 4.0K Sep 15 11:37 issues-20200915T112405.jsonl
-rw-rw-r--  1 tom tom 155M Sep 14 17:06 jobs-20200914T161507.jsonl
-rw-rw-r--  1 tom tom 118M Sep 15 11:51 jobs-20200915T112405.jsonl
-rw-rw-r--  1 tom tom 3.4M Sep 14 17:06 merge_requests-20200914T161507.jsonl
-rw-rw-r--  1 tom tom  33K Sep 15 11:51 merge_requests-20200915T112405.jsonl
-rw-rw-r--  1 tom tom 4.3M Sep 14 17:13 pipelines-20200914T161507.jsonl
-rw-rw-r--  1 tom tom  12K Sep 15 11:51 pipelines-20200915T112405.jsonl
-rw-rw-r--  1 tom tom 9.4M Sep 14 17:13 pipelines_extended-20200914T161507.jsonl
-rw-rw-r--  1 tom tom  27K Sep 15 11:51 pipelines_extended-20200915T112405.jsonl
-rw-rw-r--  1 tom tom 9.2K Sep 14 17:06 project_labels-20200914T161507.jsonl
-rw-rw-r--  1 tom tom 9.2K Sep 15 11:51 project_labels-20200915T112405.jsonl
-rw-rw-r--  1 tom tom  512 Sep 14 16:41 project_members-20200914T161507.jsonl
-rw-rw-r--  1 tom tom  512 Sep 15 11:37 project_members-20200915T112405.jsonl
-rw-rw-r--  1 tom tom 3.2K Sep 14 17:06 project_milestones-20200914T161507.jsonl
-rw-rw-r--  1 tom tom 3.2K Sep 15 11:51 project_milestones-20200915T112405.jsonl
-rw-rw-r--  1 tom tom 3.2K Sep 14 17:13 projects-20200914T161507.jsonl
-rw-rw-r--  1 tom tom 3.2K Sep 15 11:51 projects-20200915T112405.jsonl
-rw-rw-r--  1 tom tom 624K Sep 14 17:06 releases-20200914T161507.jsonl
-rw-rw-r--  1 tom tom 625K Sep 15 11:51 releases-20200915T112405.jsonl
-rw-rw-r--  1 tom tom 146K Sep 14 17:06 tags-20200914T161507.jsonl
-rw-rw-r--  1 tom tom 146K Sep 15 11:51 tags-20200915T112405.jsonl
-rw-rw-r--  1 tom tom  14K Sep 14 16:41 users-20200914T161507.jsonl
-rw-rw-r--  1 tom tom  14K Sep 15 11:37 users-20200915T112405.jsonl
Untitled
Am I misunderstanding how the state stuff is meant to work with Singer taps? My end goal is to push this data into S3 and search it with Athena so I was hoping to be have this just run regularly and only output updates since the last run.
j
Are you sharing the 
.meltano
directory between jobs?
t
this is currently just me running it locally. I haven't got as far as running it as a Gitlab CI job yet
is there something I can read about singer tap state handling? Because I'm confused by the inconsistent behaviour of the outputs for different endpoints in the Gitlab tap where some things seem to be incremental and others aren't at all and are full dumps. There's also some oddness happening with the 
jobs
endpoint in that rerunning it seems to track back a long way past the last run but not all the way
d
I did notice that the most recent state for a job seems to be stored in the database though and is only written out as a 
state.json
 file when running the next 
meltano etl
 command
Yep, that's right.
so I don't see how I could do this without extracting data since the job before the last one?
If you don't want to use the last run's state, you can use a different job ID, or pass the 
--full-refresh
flag: https://meltano.com/docs/command-line-interface.html#elt
Or is this only the case for the first run?
On every 
meltano elt
run, it'll look for state matching the Job ID
Am I misunderstanding how the state stuff is meant to work with Singer taps? My end goal is to push this data into S3 and search it with Athena so I was hoping to be have this just run regularly and only output updates since the last run.
That should work, as long as each 
meltano elt
run has access to the system database, which stores the state: https://meltano.com/docs/project.html#system-database That'd mean either persisting 
.meltano/meltano.db
between runs (using CI artifacts, as in my example repo), or using an external Postgres system DB
is there something I can read about singer tap state handling? Because I'm confused by the inconsistent behaviour of the outputs for different endpoints in the Gitlab tap where some things seem to be incremental and others aren't at all and are full dumps.
This may be because of a limitation in GitLab's API. Which specific entities/endpoints are you seeing always doing a full refresh?
There's also some oddness happening with the 
jobs
 endpoint in that rerunning it seems to track back a long way past the last run but not all the way
This is due to a limitation in the GitLab API: https://gitlab.com/meltano/tap-gitlab/-/blob/master/tap_gitlab/__init__.py#L327
t
ah okay I think I see that in my case it's reaching back to the first 
created
job which was months ago because it was blocked by a manual job (a deployment that then would have triggered some smoke tests automatically after the deployment completed) so that's why I see it fetching pretty much all of the jobs.
d
@tom_elliff Ah yeah. We may be able to make that behavior a little less naive in the tap, or plead with GitLab to improve their API
Or we could even fix GitLab's API ourselves 🙂
t
I'm slightly confused by how the incremental runs are meant to work though because I end up with lots of duplicated data in my output each time I run things
do different targets handle deduplicating things? I'm still playing with the jsonl target right now but I was actually hoping to use that (or the parquet target) beyond local usage as well as I intend to analyse this data with Athena
d
@tom_elliff tap-gitlab resyncs some tables in full, because we need all users, labels, milestones, etc, to make the relations between tables work, even if a user account was created ages ago and only recently added to the project, for example.
Yep, targets typically handle deduplication based on the record's 
key_properties
target-jsonl just doesn't because JSONL files are append-only
t
even if it doesn't resync things in full it still pulls the last record again right? So if you last pulled pipeline 42 at some specific time then the incremental one starts with pipeline 42 at that time
ah thanks for that info about 
key_properties
- I'll look into that some more and have a think about how my pipeline needs to work then
d
The tap should start after the ID/timestamp indicated by the replication key
It should not include duplicate records if it can help itself
You're planning to use tap-gitlab in production, or another data source?
t
the gitlab tap for now but then possibly the non meltano pipedrive tap as the next step. Potentially also Google Analytics at a later date
d
tap-gitlab is not the highest quality tap out there, it lacks support for entity selection as well: https://gitlab.com/meltano/tap-gitlab/-/issues/25 Nothing we can't fix, of course 🙂
t
our engineers want to be able to more easily understand pipeline data so they can track when things are ballooning as they tend to do
so this seems like a nice starting point for understanding singer taps/meltano
d
Makes sense! I think @julian_knight is using tap-gitlab for similar reasons 🙂
t
currently I have a Python script that grabs everything but it's hacked together and not really incremental and we don't surface the data in the same way we do for our existing data warehouse stuff (we dump everything in S3 and have a Glue crawler work out the schema and then query with Athena via Redash)
I also intend to surface the job section durations to the API (it's in the database but not exposed by the API) so we can track how long it took to schedule a CI job on our Kubernetes cluster (feature request is at https://gitlab.com/gitlab-org/gitlab/-/issues/224155). I intend to work on it once we've done this and then I guess that gives me a chance to look at extending the schema of the tap to support that data once the API exposes it
o
@umut_yalcinkaya
8 Views
Open in Slack
PreviousNext
Powered by