Skip to main content

Data backfill

Last updated 10/03/2026

1. Overview​

Data backfill queries data in the AE database with SQL statements and then writes the returned results back into the AE database to generate new events or new user properties.

2. Instructions​

2.1 Command​

Log in to the server where the secondary development tools are deployed (by default, the first three servers in a distributed deployment), and run the su - ta command to switch to the ta user

Run ta-tool user_event_import -conf to read the configuration file. The command is as follows:

ta-tool user_event_import -conf <config file> [--date data_date]

2.2 Command parameters​

2.2.1 -conf​

Required. The path to the configuration file of the data backfill task. Wildcards are supported, for example, /data/config/* or ./config/*.json

2.2.2 --date​

Optional. The data date, which is used as the base time for replacing time macros. If you don't pass it, the current date is used by default. The format is YYYY-MM-DD. For details on how to use time macros, see Using time macros

2.2.3 Example​

ta-tool user_event_import -conf /data/home/ta/import_configs/*.json

The parameter is the full path of the configuration file. You can use wildcards to read multiple configuration files

2.3 Configuration file​

2.3.1 Sample configuration file​

The core of data backfill is the configuration file, which contains the query statement and configuration parameters. Each configuration file corresponds to one data backfill task. A configuration file for backfilling an event is as follows:

{
  "event_desc": {
    "ltv_event": "User Lifecycle"
  },
  "appid": "APPID",
  "type": "event",
  "property_desc": {
    "register_date": "Registration Date",
    "date_prop": "LTV Days"
  },
  "sql": "SELECT 'thinkinggame' \"#account_id\",'ltv_event' \"#event_name\",register_date \"#time\",register_date,ltv,date_prop FROM  (SELECT recharge_money ltv,register_date,CASE date_trunc('day', cast(register_date AS TIMESTAMP)) WHEN CURRENT_DATE - interval '1' DAY THEN 'Next day' WHEN CURRENT_DATE - interval '2' DAY THEN '3-day' WHEN CURRENT_DATE - interval '6' DAY THEN '7-day' WHEN CURRENT_DATE - interval '13' DAY THEN '14-day' WHEN CURRENT_DATE - interval '29' DAY THEN '30-day' ELSE NULL END date_prop FROM (SELECT sum(recharge_money) recharge_money ,register_date FROM (SELECT \"#user_id\" ,sum(recharge_value) recharge_money ,\"$part_date\" recharge_date FROM v_event_0 WHERE \"$part_event\" = 'recharge' AND \"$part_date\" > '2018-06-30' AND \"$part_date\" < '2018-07-30' GROUP BY \"#user_id\" , \"$part_date\") a LEFT JOIN (SELECT \"#user_id\" , \"$part_date\" register_date FROM v_event_0 WHERE \"$part_event\" = 'player_register' AND \"$part_date\" > '2018-06-30' AND \"$part_date\" < '2018-07-30') b ON a.\"#user_id\" = b.\"#user_id\" WHERE b.\"#user_id\" IS NOT NULL AND recharge_date >= register_date GROUP BY register_date) c) d WHERE date_prop IS NOT NULL"
}
Note

To backfill a List type, you need to process it as follows, because lists are stored at the underlying layer as strings separated by \t:

split("arrayColumn", chr(0009)) as arrayColumn

2.3.2 Configuration parameters​

Each configuration file is expressed in JSON. The meaning of each element is as follows:

  • event_desc：

    • Description: Optional. A JSON object used to set the display names of new events
    • key: event name
    • value: display name

  • appid：

    • Description: Required. The APPID of the target project that the query results are written to

  • type：

    • Description: Required. Whether to write to the event table or the user table of the target project
    • Valid values: event, user

  • property_desc：

    • Description: Optional. A JSON object used to set the display names of properties
    • key: property name
    • value: display name

  • sql：

    • Description: Required. A string containing the query statement. Note that the column names of the returned results determine what the data in each column means. The following columns are required:

      • Required column 1: #account_id or #distinct_id. At least one of them is required, corresponding to the account ID and distinct ID of the user who triggered the event. If the generated event is not triggered by an individual, such as LTV in the example above, we recommend using a fixed value outside the ID rules, such as "system" or "admin"
      • Required column 2: #event_name, the event name. We recommend setting a fixed value
      • Required column 3: #time, the time when the event occurred. The format must be yyyy-MM-dd HH:mm:ss or yyyy-MM-dd HH:mm:ss.SSS

Apart from the columns above, the data in the other columns is used as properties of the event, and the column names are used as the property names

In addition to backfilling events, you can also use query results to generate new user properties or overwrite existing user properties. The configuration file is as follows:

{
  "appid": "8d1820678a064397bbfcc9732f352e75",
  "type": "user",
  "property_desc": {
    "user_level": "User Level",
    "coin_num": "Coin Balance"
  },
  "sql": "select \"#account_id\",localtimestamp \"#time\",user_level,coin_num from v_user_0"
}

As with backfilling events, this configuration file is also expressed in JSON. It differs from the configuration file for backfilling events as follows:

  • event_desc is not needed

  • The value of type is "user"

  • The required column names in sql don't include #event_name. Only the following two are required:

    • Required column 1: #account_id or #distinct_id. At least one of them is required
    • Required column 2: #time, the time

2.4 Using time macros​

You can use time macros to replace time parameters in the configuration file of a data backfill task. When you run the data backfill command, the ta-tool tool uses --date as the base, calculates time offsets based on the time macro parameters, and replaces the time macros in the configuration file. Supported time macro formats include @[{yyyyMMdd}], @[{yyyyMMdd}-{nday}], @[{yyyyMMdd}+{nday}], and so on

  • yyyyMMdd can be replaced with any date format that Java dateFormat can parse, for example: yyyy-MM-dd HH:mm:ss.SSS, yyyyMMddHH000000

  • n can be any integer and represents the time offset

  • day is the unit of the time offset and can be one of the following: day, hour, minute, week, month

  • Example: Assume the current time is 2018-07-01 15:13:23.234

    • @[{yyyyMMdd}] is replaced with 20180701
    • @[{yyyy-MM-dd}-{1day}] is replaced with 2018-06-30
    • @[{yyyyMMddHH}+{2hour}] is replaced with 2018070117
    • @[{yyyyMMddHHmm00}-{10minute}] is replaced with 20180701150300
Was this page helpful?
On this page