Custom table data import

Last updated 10/03/2026

1. Overview

In some cases, the data you need can't be represented as user or event data, such as mapping tables or external data. To use this data, import it into the AE system as custom data with the data_transfer command, and then use it together with the event table and user table.

The following two import data sources are currently supported:

• mysql: Remote mysql database
• txtfile: Local file

2. Instructions

2.1 Command

The data import command is as follows:

ta-tool data_transfer -conf <config files> [--date xxx]

2.2 Command parameters

2.2.1 -conf

The parameter passed in is the path of the configuration file of the table to import. Each table has its own configuration file. You can import multiple tables at the same time, and wildcards are supported, for example: /data/config/* or ./config/*.json

2.2.2 --date

Optional parameter --date: Optional. Specifies the data date, which time macros use as the base time for replacement. If omitted, the current date is used. The format is YYYY-MM-DD. For details on using time macros, see Using time macros

2.3 Configuration file

2.3.1 Sample configuration file for a single table:

{
  "parallel_num": 2,
  "source": {
    "type": "txtfile",
    "parameter": {
      "path": ["/data/home/ta/importer_test/data/*"],
      "encoding": "UTF-8",
      "column": ["*"],
      "fieldDelimiter": "\t"
    }
  },
  "target": {
    "appid": "test-appid",
    "table": "test_table",
    "table_desc": "Import test table",
    "partition_value": "@[{yyyyMMdd}-{1day}]",
    "column": [
      {
        "name": "col1",
        "type": "timestamp",
        "comment": "Timestamp"
      },
      {
        "name": "col2",
        "type": "varchar"
      }
    ]
  }
}

2.3.2 Top-level parameters

• parallel_num

  • Description: Number of concurrent import threads, which controls the import rate
  • Type: int
  • Required: Yes
  • Default: None

• source

  • Description: Parameter configuration of the import data source
  • Type: jsonObject
  • Required: Yes
  • Default: None

• target

  • Description: Parameter configuration of the target table for the import
  • Type: jsonObject
  • Required: Yes
  • Default: None

2.3.3 source parameters

• type

  • Description: Type of the import data source. The import tool currently supports three import data sources: txtfile, mysql, and ftp. More data sources will be supported in the future
  • Type: string
  • Required: Yes
  • Default: None

• parameter

  • Description: Configuration specific to each data source. See 3. Import data source configuration
  • Type: jsonObject
  • Required: Yes
  • Default: None

2.3.4 target parameters

• appid

  • Description: appid of the project that the imported table belongs to. You can find it in the AE console
  • Type: string
  • Required: Yes
  • Default: None

• table

  • Description: Name of the table imported into the AE system. Note: Table names must be globally unique. We recommend adding a distinguishing prefix or suffix for each project
  • Type: string
  • Required: Yes
  • Default: None

• table_desc

  • Description: Comment for the imported table. We recommend setting this parameter during import so the table's meaning is clear when you query it later
  • Type: string
  • Required: No
  • Default: Empty

• partition_value

  • Description: Partition value for the import. Custom tables imported into the AE system include the partition field $pt by default, so you must specify the partition value when importing. It's usually set to the data date of the import, for example: 20180701. Time macro replacement is also supported, for example: @[{yyyyMMdd}-{1day}]. Section 2.4 describes how to use it
  • Type: string
  • Required: Yes
  • Default: None

• column

  • Description: Defines the fields of the table imported into the AE system. Each field has 3 attributes, name, type, and comment, of which name and type are required. Example:

[
  {
    "name": "col1",
    "type": "timestamp",
    "comment": "Timestamp"
  },
  {
    "name": "col2",
    "type": "varchar"
  }
]

When the source is mysql and the whole table is imported (that is, the column field is ["*"]), you can omit the column parameter in target, and the import tool uses the table structure in mysql. In all other cases, this field is required

• Type: jsonArray
• Required: No
• Default: The table schema definition on the mysql source side

2.4 Using time macros

You can use time macros in configuration files to replace time parameters. The ta-tool tool uses the import start time 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 more

• 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

3. Import data source configuration

This section describes the parameter configuration for each data source. Three import data sources are currently supported: txtfile, mysql, and ftp. Adjust the source parameters according to the data source

3.1 mysql data source

This data source connects to a remote mysql database through a JDBC connector, generates a SELECT SQL query statement based on your configuration, sends it to the remote mysql database, and imports the results of the SQL into a table in the AE system

3.1.1 Configuration examples

• Configuration example for importing a whole mysql table into the AE system:

{
  "parallel_num": 2,
  "source": {
    "type": "mysql",
    "parameter": {
      "username": "test",
      "password": "test",
      "column": ["*"],
      "connection": [
        {
          "table": ["test_table"],
          "jdbcUrl": ["jdbc:mysql://mysql-ip:3306/testDb"]
        }
      ]
    }
  },
  "target": {
    "appid": "test-appid",
    "table": "test_table_abc",
    "table_desc": "mysql test table",
    "partition_value": "@[{yyyy-MM-dd}-{1day}]"
  }
}

• Configuration example for importing with custom SQL into the AE system:

{
  "parallel_num": 1,
  "source": {
    "type": "mysql",
    "parameter": {
      "username": "test",
      "password": "test",
      "connection": [
        {
          "querySql": [
            "select db_id,log_time from test_table where log_time>='@[{yyyy-MM-dd 00:00:00}-{1day}]' and log_time<'@[{yyyy-MM-dd 00:00:00}]'"
          ],
          "jdbcUrl": ["jdbc:mysql://mysql-ip:3306/testDb"]
        }
      ]
    }
  },
  "target": {
    "appid": "test-appid",
    "table": "test_table_abc",
    "table_desc": "mysql test table",
    "partition_value": "@[{yyyy-MM-dd}-{1day}]",
    "column": [
      {
        "name": "db_id",
        "type": "bigint",
        "comment": "db sequence number"
      },
      {
        "name": "log_time",
        "type": "timestamp",
        "comment": "Timestamp"
      }
    ]
  }
}

3.1.2 parameter parameters

• jdbcUrl

  • Description: JDBC connection information for the remote database, described as a JSON array. Note that jdbcUrl must be included in the connection configuration unit. In general, one JDBC connection in the JSON array is enough.
  • Type: jsonArray
  • Required: Yes
  • Default: None

• username

  • Description: Username for the data source
  • Type: string
  • Required: Yes
  • Default: None

• password

  • Description: Password of the specified username for the data source
  • Type: string
  • Required: Yes
  • Default: None

• table

  • Description: Tables to synchronize. They are described as a JSON array, so multiple tables can be extracted at the same time. When you configure multiple tables, you must make sure they share the same schema structure; MysqlReader doesn't check whether they are the same logical table. Note that table must be included in the connection configuration unit.
  • Type: jsonArray
  • Required: Yes
  • Default: None

• column

  • Description: Set of column names to synchronize in the configured tables, with the field information described as a JSON array. You can use * to use all columns by default, for example ["*"].
  • Type: jsonArray
  • Required: Yes
  • Default: None

• where

  • Description: Filter condition. SQL is assembled from the specified column, table, and where conditions, and data is extracted based on this SQL. In real business scenarios, data from the previous day is often synchronized, so you can set the where condition to log_time>='@[{yyyy-MM-dd 00:00:00}-{1day}]' and log_time<'@[{yyyy-MM-dd 00:00:00}]'. Note: You can't set the where condition to limit 10, because limit isn't a valid SQL where clause. The where condition makes incremental business synchronization efficient. If you don't fill in a where statement, the import tool synchronizes all data.
  • Type: string
  • Required: No
  • Default: None

• querySql

  • Description: In some business scenarios, the where option isn't enough to describe the filter conditions, so you can use this parameter to define the filter SQL yourself. When you configure this option, the import tool ignores parameters such as table and column and filters data directly with the content of this option. For example, to synchronize data after a multi-table join: select a,b from table_a join table_b on table_a.id = table_b.id. When you configure querySql, the import tool ignores the table, column, and where settings; querySql takes priority over the table, column, and where options.
  • Type: string
  • Required: No
  • Default: None

3.2 txtfile data source

The txtfile data source reads files on the local server and imports them into tables in the AE system. The current limits and features of txtfile are as follows:

1. Supports reading TXT files only, and the schema in the TXT file must be a two-dimensional table
2. Supports CSV-like files with custom delimiters
3. Supports reading multiple data types (represented as string), column pruning, and column constants
4. Supports recursive reading and file name filtering
5. Supports text compression; the available compression formats are zip, gzip, and bzip2

3.2.1 Configuration example

{
  "parallel_num": 5,
  "source": {
    "type": "txtfile",
    "parameter": {
      "path": ["/home/ftp/data/testData/*"],
      "column": [
        {
          "index": 0,
          "type": "long"
        },
        {
          "index": 1,
          "type": "string"
        }
      ],
      "encoding": "UTF-8",
      "fieldDelimiter": "\t"
    }
  },
  "target": {
    "appid": "test-appid",
    "table": "test_table_abc",
    "table_desc": "mysql test table",
    "partition_value": "@[{yyyy-MM-dd}-{1day}]",
    "column": [
      {
        "name": "db_id",
        "type": "bigint",
        "comment": "db sequence number"
      },
      {
        "name": "log_time",
        "type": "timestamp",
        "comment": "Timestamp"
      }
    ]
  }
}

3.2.2 parameter parameters

• path

  • Description: Path information in the local file system. Note that multiple paths are supported. When you specify a wildcard, the import tool tries to traverse multiple files. For example, specifying /data/* means reading all files in the /data directory. Currently only * is supported as a file wildcard. Note in particular that the import tool treats all Text Files synchronized in one job as the same data table. You must make sure all Files fit the same schema. Files to read must be in a CSV-like format.
  • Type: string
  • Required: Yes
  • Default: None

• column

  • Description: List of fields to read. type specifies the type of the source data, index specifies which column of the text the current column comes from (starting from 0), and value specifies that the current type is a constant: instead of reading data from the source file, the corresponding column is generated automatically from the value.

By default, you can read all data as the string type with the following configuration:

 "column": ["*"]

You can specify Column field information with the following configuration:

({
  "type": "long",
  "index": 0
},
{
  "type": "string",
  "value": "2018-07-01 00:00:00"
})

When you specify Column information, type is required, and you must choose one of index/value.

The value range of type is: long, double, string, boolean

• Type: jsonArray
• Required: Yes
• Default: None

• fieldDelimiter

  • Description: Field delimiter for reading
  • Type: string
  • Required: Yes
  • Default: ,

• compress

  • Description: Text compression type. Leaving it empty (the default) means no compression. Supported compression types are zip, gzip, and bzip2.
  • Type: string
  • Required: No
  • Default: No compression

• encoding

  • Description: Encoding of the files to read.
  • Type: string
  • Required: No
  • Default: utf-8

• skipHeader

  • Description: CSV-like files may have a header row of titles that needs to be skipped. Not skipped by default.
  • Type: boolean
  • Required: No
  • Default: false

• nullFormat

  • Description: Text files can't use a standard string to define null (null pointer), so ta-tool provides nullFormat to define which strings represent null. For example, if you configure nullFormat:"\N" and the source data is "\N", ta-tool treats it as a null field.
  • Type: string
  • Required: No
  • Default: \N