Loading data from Yandex Metrica to a ClickHouse® data mart

Data Transfer enables you to transfer metrics from Yandex Metrica to a ClickHouse® cluster. This approach guarantees the completeness of the data transferred from Yandex Metrica and allows:

• Processing data with ClickHouse® tools.
• Streaming data from ClickHouse® to other locations.
• Visualizing data with Yandex DataLens or other services.

To transfer data:

1. Set up and activate the transfer.
2. Test your transfer.

If you no longer need the resources you created, delete them.

Required paid resourcesRequired paid resources

• Managed Service for ClickHouse® cluster: Use of computing resources allocated to hosts, storage and backup size (see Managed Service for ClickHouse® pricing).
• Public IP addresses if public access is enabled for cluster hosts (see Virtual Private Cloud pricing).
• Metrica Pro.

Getting startedGetting started

Set up the infrastructure:

1. Select an existing metric in Yandex Metrica or create and install a new one.

2. Create a target Managed Service for ClickHouse® cluster with your preferred configuration.

Set up and activate the transferSet up and activate the transfer

1. Create an endpoint for the Metrica source.

   Hits and sessions are transferred as separate tables.

2. Create a target endpoint:

   • Database type: ClickHouse

   • Endpoint parameters → Connection type: Managed cluster

     Select your target cluster from the list and specify its connection settings.

3. Create a Replication-type transfer configured to use the new endpoints.

4. Activate the transfer.

Transfers process only the latest data, leaving historical data untouched. If you deactivate and then reactivate the transfer:

1. Data collected by the Yandex Metrica tracking tag while the transfer was deactivated will not be migrated.

2. Depending on the cleanup policy selected in the target endpoint, existing data tables will be:

   • Drop: Deleted along with all data and recreated with the same names.
   • Truncate: Truncated while preserving their schemas.
   • Don't cleanup: Left intact for future data writing.

Test your transferTest your transfer

1. Wait for the transfer status to change to Replicating.

2. Make sure the metric data from Yandex Metrica has been transferred to the Managed Service for ClickHouse® database:

   1. Connect to the cluster via clickhouse-client.

   2. Verify that the hit and session tables have appeared in the database:

      SELECT table FROM system.tables
      WHERE database = '<ClickHouse®_database_name>'
      

      Result:

      ┌─table───────────────────────┐
      │ hits_dt...                  │
      │ visits_dt...                │
      └─────────────────────────────┘
      

   3. Check whether the hit and session tables contain the relevant metric data:

      SELECT * FROM <name_of_hit_or_visit_table>
      

Delete the resources you createdDelete the resources you created

To reduce the consumption of resources you do not need, delete them:

1. Delete the transfer.
2. Delete the source and target endpoints.
3. Delete the Managed Service for ClickHouse® cluster.
4. Delete the Yandex Metrica tracking counter from your Yandex Metrica Pro account.

ClickHouse® is a registered trademark of ClickHouse, Inc.