Exporting Amplitude events to SQLite

Amplitude offers an "Export Data" button in the project settings page. This can export up to 365 days of events (up to 4GB per export), where the export is a zip file containing *.json.gz gzipped newline-delimited JSON.

You can export multiple times, so if you have more than a year of events you can export them by specifying different date ranges. It's OK to overlap these ranges as each event has a uniue uuid that can be used to de-duplicate them.

Here's how to import that into a SQLite database using sqlite-utils:

unzip export # The exported file does not have a .zip extension for some reason
cd DIRECTORY_CREATED_FROM_EXPORT
gzcat *.json.gz | sqlite-utils insert amplitude.db events --nl --alter --pk uuid --ignore -

Since we are using --pk uuid and --ignore it's safe to run this against as many exported *.json.gz files as you like, including exports that overlap each other.

Then run datasette amplitude.db to browse the results.

Related

• googlecloud Analyzing Google Cloud spend with Datasette - 2022-08-16
• sqlite Importing CSV data into SQLite with .import - 2021-07-13
• mastodon Export a Mastodon timeline to SQLite - 2022-11-04
• sqlite One-liner for running queries against CSV files with SQLite - 2022-06-20
• cloudrun Tailing Google Cloud Run request logs and importing them into SQLite - 2021-08-09
• sqlite SQLite aggregate filter clauses - 2021-08-04
• sqlite Geopoly in SQLite - 2023-01-04
• twitter Loading Twitter Birdwatch into SQLite for analysis with Datasette - 2022-09-03
• duckdb Use DuckDB to convert parquet to JSON and then open it in Datasette Lite - 2023-03-21
• sqlite Saving an in-memory SQLite database to a file in Python - 2023-04-08

Created 2021-06-06T13:56:09-07:00 · Edit