Importing Table Records Using the Data Ingestion API

Copy

• Copy for LLM

  Copy page as Markdown for LLMs
• View as Markdown

  Open this page as Markdown
• Open in ChatGPT

  Get insights from ChatGPT
• Open in Claude

  Get insights from Claude
• Connect to Cursor

  Install MCP server on Cursor
• Connect to VS Code

  Install MCP server on VS Code

Treasure Data provides an ingest API that allows you to programmatically import rows into existing tables in a TD database. The endpoints you can use for the data ingestion API are

• US Region: https://us01.records.in.treasuredata.com
• EU01 Region: https://eu01.records.in.treasuredata.com
• Tokyo Region: https://ap01.records.in.treasuredata.com
• Korea Region: https://ap02.records.in.treasuredata.com
• AP03 Region: https://ap03.records.in.treasuredata.com

Choose the endpoint that corresponds to your region.

To make the ingest API call, you need the following data:

• the name of the database that contains the table you want to ingest records into
• the name of the table
• a request object that contains the column names and values you want to import. The information in the request object is specified as key-value pairs in JSON, and each entry in the request object will be entered as a new row in the table. Figure 1 below shows the format for ingesting a single record, and Figure 2 shows the format for ingesting multiple records in a single request.

As you create request objects, be aware of the following API behaviors:

• The records ingested do not need to match the current table schema; new columns will be added to the table schema on ingest.
• If you specify a time column, the value must be formatted as Unix timestamp in UTC seconds (not milliseconds). If you do not specify a time column, the system will use the current time.
• The API assigns a UUID to each record which is used in the ingest pipeline. This UUID is included in the response object; however, it is not persisted in the table. Consequently, avoid specifying a column named "uuid." The API allows you to specify a value for a "uuid" column if the value is syntactically valid, and it will be returned in the response, but the value will not be stored in the table.

Limitations

The streaming ingest API batch endpoint path records.in has the following limits:

• 1-500 records
• 1MB max record size
• 5MB max batch size

If this limit is exceeded, the API will return a 422 or 413 error.

This limitation also applies to the latest JS SDK and mobile SDKs. However, the IOS and Android SDKs manage this limit for you, and you should not encounter this error when using those SDKs.

Ingesting a Single Record with the Ingestion API

To ingest a single record into a table, you will need to create a request object similar to that shown in Figure 1. Additionally, you will need to specify the following Content-Type header

application/vnd.treasuredata.v1.single+json

Here is an example of a call that ingests one record into a table named simple_table in the wt_db database.

$ curl -X POST 'https://us01.records.in.treasuredata.com/wt_db/simple_table' -H 'Authorization: TD1 1/123456789abcdef0123456789abcdef012345678' -H 'Content-Type: application/vnd.treasuredata.v1.single+json' -d '{
       "time":1552689469,
       "name":"oliver",
       "favorite_color":"orange"
    }'

{"time":1552689469,"uuid":"65509cab-8fc6-443e-a4a4-8d5b78d77a8b","favorite_color":"orange","name":"oliver"}

Figure 1: File listing of singleRecordIngest.json, an example of a request object for ingesting a single record

{
       "time":1552689469,
       "name":"oliver",
       "favorite_color":"orange"
}

Ingesting Multiple Records with the Ingestion API

If you want to ingest multiple records in a single API call, you will need to create a request object similar to that shown in Figure 2. Note that the individual records you want to add are assigned to a "records" variable in JSON. That is, the individual records are wrapped in a "records" array like this:

Additionally, you will need to specify the following Content-Type header:

application/vnd.treasuredata.v1+json

Here is an example call that ingests multiple records into a table named simple_table in the wt_db database. For the request object, the call uses the file multipleRecordIngest.json shown in Figure 2.

$ curl -X POST 'https://us01.records.in.treasuredata.com/wt_db/simple_table' -H 'Authorization: TD1 1/123456789abcdef0123456789abcdef012345678' -H 'Content-Type: application/vnd.treasuredata.v1+json' -d @multipleRecordIngest.json | jq .

{
  "failures": 0,
  "receipts": [
    {
      "uuid": "0139dc76-9a45-4aad-9df9-eaa06c98d632",
      "success": true
    },
    {
      "uuid": "9ee5cf77-c32e-401f-9a16-b122b9faa43f",
      "success": true
    },
    {
      "uuid": "08602567-ac62-49f4-b63a-8c3c1080e813",
      "success": true
    }
  ]
}

Here is an example of how sample_table looks in the TD Console after ingesting the multiple records:

Figure 2: File listing of multipleRecordIngest.json, the request object for ingesting multiple records using a single request.

{
   "records":[
      {
         "time":1552689544,
         "name":"jerry",
         "favorite_number":13
      },
      {
         "time":1552689644,
         "name":"jack",
         "favorite_color":"yellow"
      },
      {
         "time":1552689744,
         "name":"jill",
         "customer_number":"12345"
      }
   ]
}

Related Tasks

As you work with the Data Ingestion API, there are some related tasks that can be performed with APIs or other tools.

Viewing the Records in a Table

You can view table records in the following ways:

• Using TD Console
• Calling the table/tail endpoint of the TD API
• Issuing a SQL Query

Viewing Table Records in TD Console

To view the records in a table by using the TD Console:

1. Navigate to Data Workbench > Databases.
2. Locate the database name by scrolling through the list of databases, or searching for the database name, and select the database.
3. Locate the table name by scrolling through the list of tables, or searching for the table name, and select the table.

The table display looks something like this:

Calling the table/tail Endpoint to View Table Records

You can view the contents of the table by calling the table/tail endpoint. Here is an example of that call:

$ curl GET 'https://api.treasuredata.com/v3/table/tail/wt_db/simple_table' -H 'Authorization: TD1 1/123456789abcdef0123456789abcdef012345678' -H 'Content-Type: application/json' | jq .

[
  {
    "favorite_number": "256",
    "favorite_color": null,
    "name": "Alyssa",
    "time": 1639079413
  },
  {
    "favorite_number": "7",
    "favorite_color": "red",
    "name": "Ben",
    "time": 1639079413
  },
  {
    "favorite_number": null,
    "favorite_color": "blue",
    "name": "Charlie",
    "time": 1639079413
  }
]

Issuing a SQL Query to View Table Records

To view table records, you can issue the following SQL query. If the table contains a large number of records you may want to add a LIMIT clause.

select * from <table_name>

Issuing a SQL Query Using TD Toolbelt

Here is an example of using the TD command line interface to issue the query. (For additional details on the td query command see https://api-docs.treasuredata.com/en/tools/cli/api/#td-query)

$ td query -d wt_db -T presto -w 'select * from simple_table'

Issuing a SQL Query Using TD API

Using the TD API to issue a SQL query is a two-step process:

1. Call the job/issue endpoint to create the job that issues the query
2. Call the job/result endpoint to see the results of the job

Here is an example of issuing the query with the TD API:

$ curl -X POST 'https://api.treasuredata.com/v3/job/issue/presto/wt_db?query=select+*+from+simple_table' -H 'Authorization: TD1 1/123456789abcdef0123456789abcdef012345678' | jq .

{
  "job": "1408208769",
  "job_id": "1408208769",
  "database": "wt_db"
}

# Use job_id from previous call in the following call

$ curl GET 'https://api.treasuredata.com/v3/job/result/1408208769' -H 'Authorization: TD1 1/123456789abcdef0123456789abcdef012345678'

Alyssa,256,,,1639079413
Ben,7,red,,1639079413
Charlie,,blue,,1639079413

Getting the Database Name

You can determine the database name by searching or browsing the TD Console. You can also determine the database name by calling the database/list endpoint. Here is an example of using the TD API to get the names of all the databases in your TD account.:

$ curl GET 'https://api.treasuredata.com/v3/database/list/' -H 'Authorization: TD1 1/123456789abcdef0123456789abcdef012345678' -H 'Content-Type: application/json' | jq . | grep '"name"\:'

      "name": "01_ca_database",
      "name": "demo_db",
      .
      .
      .
      "name": "wt_db",
      "name": "wt_db_test",

Getting the Table Name

You can determine the table name by searching or browsing the TD Console. You can a also determine the table name by calling the table/list endpoint. Here is an example of using the TD API to list all the tables in a database:

$ curl GET 'https://api.treasuredata.com/v3/table/list/wt_db' -H 'Authorization: TD1 1/123456789abcdef0123456789abcdef012345678' -H 'Content-Type: application/json' | jq . | grep '\"name\"\:'

      "name": "simple_table",
      "name": "test_db",
      "name": "wt_table13",

Viewing the Table Schema

After you have the table name, you can get the table schema by calling the table/show endpoint. Here is an example of using the TD API to view the schema of a table.

$ curl GET 'https://api.treasuredata.com/v3/table/show/wt_db/simple_table' -H 'Authorization: TD1 1/123456789abcdef0123456789abcdef012345678' -H 'Content-Type: application/json' | jq .

{
  "id": 190562750,
  "name": "simple_table",
  "estimated_storage_size": 203,
  "counter_updated_at": "2022-03-24T03:55:00Z",
  "last_log_timestamp": "2021-12-09T19:50:13Z",
  "delete_protected": false,
  "created_at": "2021-12-09 19:50:10 UTC",
  "updated_at": "2022-05-27 14:57:50 UTC",
  "type": "log",
  "include_v": true,
  "count": 3,
  "schema": "[[\"name\",\"string\"],[\"favorite_number\",\"long\"],[\"favorite_color\",\"string\"]]",
  "expire_days": null
}