Menu

Historical data import and export

The content described in this document belongs to the advanced usage of Sensory Analysis, involving many technical details. It is only applicable to the reference of customers with a total data volume of less than one billion for historical data export and import.

*Contact your Customer Success Manager or Project Manager to purchase data migration services if you need to migrate data to another new environment or if the total data volume exceeds one billion. If you have any questions about the document content, please consult Sensory's on-duty colleagues for assistance.

Overview

Sensory Analysis now provides users with self-service data import and export functions, which involve exporting historical data from the original Sensory Analysis environment through the API, and then importing the historical data into a newly created Sensory Analysis environment using the import tool.

Preparation for data import and export

Please check whether there are Sensory reserved fields in the event attributes and user attributes in your environment. If there are reserved fields, please contact Sensory's on-duty colleagues for processing. Otherwise, there will be a failure to import reserved fields into the database.

PS: To ensure that the attribute names do not conflict with system variable names when querying, set the following reserved fields, and avoid using them as event names and attribute names (key in properties):

date datetime distinct_id event events event_id first_id id original_id device_id properties second_id time user_id users user_group 开头 user_tag 开头

Data export

Use the query API to export data from the users table (including Track Signup data) and the events table by writing SQL. If you are using Sensory's item table feature and have a Sensory system version 1.15.1646 or later, you also need to export data from the items table. Set format=profile_json/event_json so that the exported data can be imported into another project using the import tool. In addition, please note that by default, the SQL will be forcibly killed by the system after exceeding 10 minutes during the execution. If you want to increase the timeout time, you can add the MAX_QUERY_EXECUTION_TIME parameter after the SQL statement to control it (the time unit is seconds). *The suggested maximum timeout duration should not exceed 1800 to avoid affecting normal system queries).

Export user data

An example of exporting the users table using curl is as follows:

curl 'https://saasdemo.cloud.sensorsdata.cn/api/sql/query?token=******&project=default' \ -X POST \ --data-urlencode "q=SELECT * FROM users /*MAX_QUERY_EXECUTION_TIME=1800*/" \ --data-urlencode "format=profile_json" \ >> profile.json

Sample of exported data:

{"type":"track_signup","original_id":"anonymousId1","distinct_id":"registerId1","event":"$SignUp","time":1626343612099,"properties":{}} {"type":"profile_set","distinct_id":"registerId1","properties":{"$update_time":1618000000000,"$is_deleted":false,"$is_login_id":true}} {"type":"track_signup","original_id":"anonymousId2","distinct_id":"registerId2","event":"$SignUp","time":1626343612099,"properties":{}} {"type":"profile_set","distinct_id":"registerId2","properties":{"$update_time":1599000000000,"$is_deleted":false,"$is_login_id":true}}

Export event data

When exporting data from the events table, it is suggested to export it by day using the date field. This can improve the query speed of SQL.

An example of exporting the events table using curl is as follows:

curl 'https://saasdemo.cloud.sensorsdata.cn/api/sql/query?token=******&project=default' \ -X POST \ --data-urlencode "q=SELECT * FROM events where date = '2017-01-01' /*MAX_QUERY_EXECUTION_TIME=1800*/" \ --data-urlencode "format=event_json" \ >> event.json

Sample of exported data:

{"type":"track","event":"$AppStart","time_free":true,"time":1590734123713,"distinct_id":"F8C867C1-5AA8-4F89-A8ED-240BD83A76A2","properties":{"$lib":"iOS","$app_version":"1.0"}} {"type":"track","event":"$AppClick","time_free":true,"time":1590733706742,"distinct_id":"F8C867C1-5AA8-4F89-A8ED-240BD83A76A2","properties":{"$lib":"iOS","$app_version":"1.0"}}

Data import

The exported data is in the format of Sensory data and can be directly imported using Sensory's import tool. In the process of importing data, follow the steps below to avoid confusion in the TrackSignup logic.

  • *First import data from the users table
  • Import data into the events table again

Import data into the users table

Please do not rearrange the file contents.

Import data into the events table

Use any import tool to import


Last modified: 2024-12-27