# Importing Table Records Using the Data Ingestion API 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](https://us01.records.in.treasuredata.com) * EU01 Region: [https://eu01.records.in.treasuredata.com](https://eu01.records.in.treasuredata.com) * Tokyo Region: [https://ap01.records.in.treasuredata.com](https://ap01.records.in.treasuredata.com) * Korea Region: [https://ap02.records.in.treasuredata.com](https://ap02.records.in.treasuredata.com) * AP03 Region: [https://ap03.records.in.treasuredata.com](https://ap02.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. ```bash $ 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** ```json { "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: ![](/assets/recordsarray.7c22654e1f3c3774893f1ad64cbfa6aa9984ff7293518ac3cd41a9847e067a14.ad50cbca.png) 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. ```bash $ 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 . ``` ```json { "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: ![](/assets/tdconsoletableview6.6984060300e66dd22292996836f03a959d3a816b69c690680c0586533511ba24.ad50cbca.png) **Figure 2: File listing of multipleRecordIngest.json, the request object for ingesting multiple records using a single request**. ```json { "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 In some situations you will not be able to see newly ingested records using the TD Console or the *table/tail* endpoint. The most reliable way to view table records is by 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: ![](/assets/tdconsoletableview3.59e76ee07b3ff30b31a014e0d1cf09b7cb0b0228d992fc76e494567a1312517a.ad50cbca.png) It can take several minutes for ingested records to be visible in the TD Console. You may have to refresh the screen several times before the new records are displayed. If the newly ingested records are not displayed in a few minutes, you will need to issue a SQL query to see the records. ### 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: ```bash $ curl GET 'https://api.treasuredata.com/v3/table/tail/wt_db/simple_table' -H 'Authorization: TD1 1/123456789abcdef0123456789abcdef012345678' -H 'Content-Type: application/json' | jq . ``` ```json [ { "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 } ] ``` It can take several minutes for ingested records to be visible using the API. You may need to issue the call several times before the new records are displayed. If the newly ingested records are not displayed in a few minutes, you will need to issue a SQL query to see the records. ### 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. ```sql select * from ``` #### 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](https://api-docs.treasuredata.com/en/tools/cli/api/#td-query)) ```bash $ 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: ```bash $ curl -X POST 'https://api.treasuredata.com/v3/job/issue/presto/wt_db?query=select+*+from+simple_table' -H 'Authorization: TD1 1/123456789abcdef0123456789abcdef012345678' | jq . ``` ```json { "job": "1408208769", "job_id": "1408208769", "database": "wt_db" } ``` ```bash # 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.: ```bash $ 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: ```bash $ 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. ```bash $ curl GET 'https://api.treasuredata.com/v3/table/show/wt_db/simple_table' -H 'Authorization: TD1 1/123456789abcdef0123456789abcdef012345678' -H 'Content-Type: application/json' | jq . ``` ```json { "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 } ``` If you ingest a record that contains columns that are not in the original table, the table schema will be updated to include the new columns.