Batch exports mega-issue

[hazzadous]

We're moving a lot of currently available apps to a new export system based on Temporal, which exports events in batches. It's proven to be a lot more reliable than our previous streaming system.

Done

• Best effort duplicate removal (refactor: Account for duplicates in CH for BatchExports #16354)
• Revamp exports UI
• S3 exports
  • S3 key templating (feat: Support basic templating in S3 key prefixes #16591)
  • Add support for file compression (S3/blob storage, feat: S3 BatchExports missing features #17233, feat: Frontend support for S3 compression and exclude events #17269)
• Snowflake exports
• Postgres exports (feat: Postgres destination for BatchExports #17045, feat: Add frontend support for Postgres Batch Export destination #17178)
• Reliability improvements.
  • Extend existing retries (fix: Do retry on CH connection errors #16574, fix: Bump max_execution_time in batch exports #16513, fix: Increase number of retries to bump timeout #16507)
  • Retry on JSONDecode errors (fix: Resume iterator on JSONDecodeError for BatchExports #16473)
  • Gracefully Handle worker shutdown (chore: add term signal handler to temporal worker #16496, feat: Use heartbeat API to track activity progress in case of resume #16494)
  • Automated retries for PostHog errors
• Make the new exports discoverable in the UI - Exports UI requirements #16924 (Rolling out slowly to users as we migrate them Rolled out to all users).
• Bigquery exports (feat: BigQuery destination for BatchExports #17170)
• Support for filtering events:
  • S3 (feat: S3 BatchExports missing features #17233, feat: Frontend support for S3 compression and exclude events #17269)
  • Snowflake
  • Postgres
  • BigQuery
• Documentation.
  • S3
  • Snowflake
  • Postgres
  • BigQuery
• Debugging options (logs, error details).
• Track bytes/records exported (feat: Track bytes and records exported by each in BatchExportRun  #16403)

In progress

• Performance improvements.

  • Read data as Arrow instead of JSON (feat: Stream BatchExport queries in optimized format #16583)
  • Parallelize S3 upload/Download.
  • Handle retries on BigQuery

• Redshift exports (feat(batch-exports): Add Redshift to BatchExport destinations #18059).

Roadmap

• Add support for more output formats (S3/blob storage).
• RBAC for AWS.
• Postgres SSL support.
• Static IP range for exports.
• Historical export metrics/UI.

related tickets:

Add support for more output formats (S3/blob storage) / Static IP range for exports: https://posthoghelp.zendesk.com/agent/tickets/6490

[elijahbenizzy]

Really excited about this! Possible I haven't read it, but is there a backfill capability in the works? I'd love to be able to export every event I have for ad-hoc analysis. Used to do this with the S3 app (which ended up getting kinda slow/painful).

[tomasfarias]

Hey @elijahbenizzy! Backfill is supported already, but we need to expose it in the UI (currently you can trigger a backfill by calling the /batch_exports/{export_id}/backfill endpoint).

[elijahbenizzy]

Woo! Great to hear. Any idea for an ETA for the UI? Happy to try the API.

[tomasfarias]

@elijahbenizzy We are focusing on ironing out the backend, so it's hard to say an exact ETA, but I imagine UI improvements will start shipping this week and continue through next week.

[Anakin100100]

@hazzadous I see that the Postgres app is no longer available because of the redevelopment of the export system. When will it be available?

[levintrix]

+1 for postgres support

[tomasfarias]

We will be adding support for Postgres exports in the next sprint.

[Mohanbalaji1998]

waiting for s3 to ingest my events into that. Kindly tell me when can I see that

[ClemensKoehlerTemedica]

Great to see the exports getting some love!🥳 Any idea on when the BigQuery export might be available again?

[i0exception]

^ Same. Any idea when BigQuery will be available?

[nstreicher]

+1 on BigQuery export

[tomasfarias]

Postgres and BigQuery exports are our next two priorities. Postgres will likely come first (this sprint), while you can expect BigQuery by end of the month.

[tomasfarias]

Work on Big Query exports has been scheduled for next sprint. Folks running a Big Query export can expect to be migrated over to the next system around end of month.

[Mohanbalaji1998]

Eagerly waiting !

…

On Wed, Aug 16, 2023, 8:45 PM Tomás Farías Santana ***@***.***> wrote: Work on Big Query exports has been scheduled for next sprint. Folks running a Big Query export can expect to be migrated over to the next system around end of month. — Reply to this email directly, view it on GitHub <#15997 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/A4W7PLAG4B4Z3S5G4JNXISTXVTP27ANCNFSM6AAAAAAZDI5LKU> . You are receiving this because you commented.Message ID: ***@***.***>

[jedwhite]

Any news or eta on Redshift please? It's challenging to take down the previous version before making the new version available when users already have it in production use.

[mrnadia]

Eagerly waiting for BQ. Any updates?

[mrnadia]

Come on guys, that's not like a minor feature. The whole set up for product analytics is stuck cause of that. I doubt there are too many companies who only use the built-in posthog analytics tools.

[timgl]

Hey @mrnadia, we're actively working on the Bigquery destination, and you can follow along here: #17170

@jedwhite We've got a Postgres destination live now that you should be able to use.

[tomasfarias]

BigQuery batch exports were released last week. Documentation for Postgres and BigQuery is missing and coming next.

[jedwhite]

Hey @mrnadia, we're actively working on the Bigquery destination, and you can follow along here: #17170

@jedwhite We've got a Postgres destination live now that you should be able to use.

Hey thanks @timgl - is there an additional step needed to enable the Postgres destination? I had a look under Apps and settings but can't see the option to enable the export. Is this hosted version or local only? Thanks heaps :)

[jedwhite]

Thanks @timgl - got it now. Had to refresh Apps page to get the new Batch Exports menu. Much appreciated for the work on Postgres export!

[jedwhite]

Quick follow up in the interim of docs @timgl - it looks like it fails as soon as it tries writing to the database. Is there a create statement needed first for the events table, and any details on the schema for it available please if so? Set it up with defaults for public and events on the posthog side, and the database itself is already created but empty. Thanks kindly!

[jedwhite]

Just FYI @timgl I tried pausing and archiving the failed run, and creating a new one, and then Create Historic Export, but the screen just remains displaying the Button to Create Historic Export. If I can provide any more information to help debug the postgres connection please let me know. The table has not been created, which appears to be the expected behaviour according to the Snowflake docs that are there for the batch exports connector for that. Thanks again

[jedwhite]

Was anyone able to get postgres working so far? It appears the table create isn't compatible with redshift so I set up a new postgres instance on RDS but without logging in the UI or instructions it's hard to see where it's failing.

[timgl]

@tomasfarias Could you have a look?

[jedwhite]

Thanks all. Just to let you know, I tried with Snowflake - set up an account and db, set up connection and it connected and started populating the historic export, but stopped after a few hours in and no activity on the backlog for the last 12 hours. The hourly current updates are running. Is there a way to re-kick off the failed backlog export? Or is the best bet to delete the table on snowflake and try again? If it's recreated without deleting the already exported content, will it de-duplicate? Sorry for the questions and curious what other folks have got working.

[jedwhite]

Just an update in case it's useful - after another 5-6 hours the ongoing hourly batch export also stops, in addition to the historic export. I'll wait overnight and see if it resumes.

[cstoneham]

Hey all, awesome feature. I'm having some issues getting Postgres to work, is there any way to inspect the error message so that I can debug?

edit: the error in the console was

latest_error: "OperationalError: invalid sslmode value: \"no-verify\"\n"`

I get that regardless of what's checked for the SSL mode.

[MarconLP]

+1 Static IP range for exports https://posthoghelp.zendesk.com/agent/tickets/7623

[MarconLP]

+1 Static IP range for exports https://posthoghelp.zendesk.com/agent/tickets/7879

[d3rp3tt3]

Adding ClickHouse as a request <3