til: amplitude_export-events-to-datasette.md
This data as json
| path | topic | title | url | body | html | shot | created | created_utc | updated | updated_utc | shot_hash | slug |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| amplitude_export-events-to-datasette.md | amplitude | Exporting Amplitude events to SQLite | https://github.com/simonw/til/blob/main/amplitude/export-events-to-datasette.md | [Amplitude](https://amplitude.com/) 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. | <p><a href="https://amplitude.com/" rel="nofollow">Amplitude</a> 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 <code>*.json.gz</code> gzipped newline-delimited JSON.</p> <p>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 <code>uuid</code> that can be used to de-duplicate them.</p> <p>Here's how to import that into a SQLite database using <code>sqlite-utils</code>:</p> <pre><code>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 - </code></pre> <p>Since we are using <code>--pk uuid</code> and <code>--ignore</code> it's safe to run this against as many exported <code>*.json.gz</code> files as you like, including exports that overlap each other.</p> <p>Then run <code>datasette amplitude.db</code> to browse the results.</p> | <Binary: 82,338 bytes> | 2021-06-06T13:56:09-07:00 | 2021-06-06T20:56:09+00:00 | 2021-06-06T13:56:09-07:00 | 2021-06-06T20:56:09+00:00 | e12c89da0cb3c1fabcf189aaed72925a | export-events-to-datasette |