How are you guys managing tap-google-analytics tha...
# plugins-generald
daniel_pettersson
12/14/2020, 4:50 PMHow are you guys managing tap-google-analytics that doesn't support state for incremental data? Running it as-is will create duplicate rows since it runs full table replication.
d
douwe_maan
12/14/2020, 5:12 PM@daniel_pettersson What loader are you using?
As you point out, tap-google-analytics doesn't currently do incremental replication, but loaders are still usually capable of deduplicating records by upserting based on each stream's
key_propertiesga_datega:dated
daniel_pettersson
12/14/2020, 5:13 PMPostgres and bigquery
daniel_pettersson
12/14/2020, 5:13 PMMostly bigquery
d
douwe_maan
12/14/2020, 5:13 PMNote that incremental replication should not be too hard to add per https://gitlab.com/meltano/tap-google-analytics/-/issues/6, if you were interested in contributing it 🙂
douwe_maan
12/14/2020, 5:15 PMAh OK, then the issue would appear to be that BigQuery can't upsert, so then you're dependent on the incremental loading behavior of the tap
douwe_maan
12/14/2020, 5:15 PMNote that you would still see duplicate records at incremental replication boundaries even then: https://meltano.slack.com/archives/CFG3C3C66/p1602522150168400
douwe_maan
12/14/2020, 5:17 PM@daniel_parker Instead of manually loading from the GA API to BigQuery, have you considered https://support.google.com/analytics/answer/9358801?hl=en?
douwe_maan
12/14/2020, 5:21 PMTo get this to work with tap-google-analytics and target-bigquery, we'd need to add incremental replication to the tap, plus a setting to find new/changed records exclusively of the replication key date (like https://meltano.com/plugins/extractors/bigquery.html#start-always-inclusive does for tap-bigquery).
douwe_maan
12/14/2020, 5:24 PMIt's made a little trickier by the fact that a report for the current day will already show up in the morning, but will still go up during the day, so if you can't upsert in the destination, you'd probably want to exclude incomplete records entirely, getting only data up until yesterday
douwe_maan
12/14/2020, 5:24 PMWhich is behavior you can realize yourself by dynamically adjusting the start_date and end_date settings. You could make sure your pipeline runs once a day, and sets both to yesterday's YYYY-MM-DD timestamp
douwe_maan
12/14/2020, 5:25 PMThat is, assuming you're only interested in daily reports.
douwe_maan
12/14/2020, 5:28 PMWhen using the tap with a target that can do upserts, though, there's no reason to worry about the boundary and it may be useful to have incomplete records loaded during the day, so if we were to add incremental replication to the tap, we'd probably still want a setting to make it work properly with targets that can't upsert
d
daniel_pettersson
12/14/2020, 5:28 PMHmm, alright, sound like a bit of a hassle. Maybe connecting GA v4 with bigquery would be easier than this
d
douwe_maan
12/14/2020, 5:28 PMIt probably will be, looks like tap-google-analytics and target-bigquery are not a great match (right now)
d
daniel_pettersson
12/14/2020, 5:29 PMYeah, will take a look tomorrow, thanks for the pointers!
daniel_pettersson
12/15/2020, 5:46 PMHey again, how would I set dynamic dates in schedule or the config for the tap? I'm referring to yesterday's YYYY-MM-DD.
d
douwe_maan
12/15/2020, 5:47 PM@daniel_parker How much control do you have over the execution environment of
meltano eltTAP_GOOGLE_ANALYTICS_{START,END}_DATEdouwe_maan
12/15/2020, 5:48 PMWhatever you're using to execute
meltano eltd
daniel_pettersson
12/15/2020, 5:57 PMFull control, running it on GKE, airflow scheduler. Alright 👍
d
douwe_maan
12/15/2020, 5:58 PMPerfect, then you should be able to modify the DAG generator at
orchestrate/dags/meltano.pyx
xinge_li
03/10/2022, 8:30 PMHi @daniel_pettersson ! I know this post is a bit too old, but I am wondering if you have solved the incremental replication for
tap-google-analytics