Import Local Data

The following mainly introduces how to import local data in client.

Now Doris support two way to load data from client local file:

  1. Stream Load
  2. MySql Load

Stream Load

Stream Load is used to import local files into Doris.

Unlike the submission methods of other commands, Stream Load communicates with Doris through the HTTP protocol.

The HOST:PORT involved in this method should be the HTTP protocol port.

  • BE’s HTTP protocol port, the default is 8040.
  • FE’s HTTP protocol port, the default is 8030. However, it must be ensured that the network of the machine where the client is located can connect to the machine where the BE is located.

In this document, we use the curl command as an example to demonstrate how to import data.

At the end of the document, we give a code example of importing data using Java

Import Data

The request body of Stream Load is as follows:

  1. PUT /api/{db}/{table}/_stream_load

  1. Create a table

    Use the CREATE TABLE command to create a table in the demo to store the data to be imported. For the specific import method, please refer to the CREATE TABLE command manual. An example is as follows:

    1. CREATE TABLE IF NOT EXISTS load_local_file_test
    2. (
    3.     id INT,
    4.     age TINYINT,
    5.     name VARCHAR(50)
    6. )
    7. unique key(id)
    8. DISTRIBUTED BY HASH(id) BUCKETS 3;

  2. Import data

    Execute the following curl command to import the local file:

    1.  curl -u user:passwd -H "label:load_local_file_test" -T /path/to/local/demo.txt http://host:port/api/demo/load_local_file_test/_stream_load
    • user:passwd is the user created in Doris. The initial user is admin/root, and the password is blank in the initial state.
    • host:port is the HTTP protocol port of BE, the default is 8040, which can be viewed on the Doris cluster WEB UI page.
    • label: Label can be specified in the Header to uniquely identify this import task.

    For more advanced operations of the Stream Load command, see Stream Load Command documentation.

  3. Wait for the import result

    The Stream Load command is a synchronous command, and a successful return indicates that the import is successful. If the imported data is large, a longer waiting time may be required. Examples are as follows:

    1. {
    2.     "TxnId": 1003,
    3.     "Label": "load_local_file_test",
    4.     "Status": "Success",
    5.     "Message": "OK",
    6.     "NumberTotalRows": 1000000,
    7.     "NumberLoadedRows": 1000000,
    8.     "NumberFilteredRows": 1,
    9.     "NumberUnselectedRows": 0,
    10.     "LoadBytes": 40888898,
    11.     "LoadTimeMs": 2144,
    12.     "BeginTxnTimeMs": 1,
    13.     "StreamLoadPutTimeMs": 2,
    14.     "ReadDataTimeMs": 325,
    15.     "WriteDataTimeMs": 1933,
    16.     "CommitAndPublishTimeMs": 106,
    17.     "ErrorURL": "http://192.168.1.1:8042/api/_load_error_log?file=__shard_0/error_log_insert_stmt_db18266d4d9b4ee5-abb00ddd64bdf005_db18266d4d9b4ee5_abb00ddd64bdf005"
    18. }
    • The status of the Status field is Success, which means the import is successful.
    • For details of other fields, please refer to the Stream Load command documentation.

Import Suggestion

  • Stream Load can only import local files.
  • It is recommended to limit the amount of data for an import request to 1 - 2 GB. If you have a large number of local files, you can submit them concurrently in batches.

Java Code Examples

Here is a simple JAVA example to execute Stream Load:

  1. package demo.doris;
  3. import org.apache.commons.codec.binary.Base64;
  4. import org.apache.http.HttpHeaders;
  5. import org.apache.http.client.methods.CloseableHttpResponse;
  6. import org.apache.http.client.methods.HttpPut;
  7. import org.apache.http.entity.FileEntity;
  8. import org.apache.http.impl.client.CloseableHttpClient;
  9. import org.apache.http.impl.client.DefaultRedirectStrategy;
  10. import org.apache.http.impl.client.HttpClientBuilder;
  11. import org.apache.http.impl.client.HttpClients;
  12. import org.apache.http.util.EntityUtils;
  14. import java.io.File;
  15. import java.io.IOException;
  16. import java.nio.charset.StandardCharsets;
  18. /*
  19. This is an example of Doris Stream Load, which requires dependencies
  20. <dependency>
  21.     <groupId>org.apache.httpcomponents</groupId>
  22.     <artifactId>httpclient</artifactId>
  23.     <version>4.5.13</version>
  24. </dependency>
  25.  */
  26. public class DorisStreamLoader {
  27.     //You can choose to fill in the FE address and the http_port of the FE, but the connectivity between the client and the BE node must be guaranteed.
  28.     private final static String HOST = "your_host";
  29.     private final static int PORT = 8040;
  30.     private final static String DATABASE = "db1"; // database to import
  31.     private final static String TABLE = "tbl1"; // table to import
  32.     private final static String USER = "root"; // Doris username
  33.     private final static String PASSWD = ""; // Doris password
  34.     private final static String LOAD_FILE_NAME = "/path/to/1.txt"; // local file path to import
  36.     private final static String loadUrl = String.format("http://%s:%s/api/%s/%s/_stream_load",
  37.             HOST, PORT, DATABASE, TABLE);
  39.     private final static HttpClientBuilder httpClientBuilder = HttpClients
  40.             .custom()
  41.             .setRedirectStrategy(new DefaultRedirectStrategy() {
  42.                 @Override
  43.                 protected boolean isRedirectable(String method) {
  44.                     // If the connection target is FE, you need to handle 307 redirect.
  45.                     return true;
  46.                 }
  47.             });
  49.     public void load(File file) throws Exception {
  50.         try (CloseableHttpClient client = httpClientBuilder.build()) {
  51.             HttpPut put = new HttpPut(loadUrl);
  52.             put.setHeader(HttpHeaders.EXPECT, "100-continue");
  53.             put.setHeader(HttpHeaders.AUTHORIZATION, basicAuthHeader(USER, PASSWD));
  55.             // You can set stream load related properties in Header, here we set label and column_separator.
  56.             put.setHeader("label","label1");
  57.             put.setHeader("column_separator",",");
  59.             // Set the import file.
  60.             // StringEntity can also be used here to transfer arbitrary data.
  61.             FileEntity entity = new FileEntity(file);
  62.             put.setEntity(entity);
  64.             try (CloseableHttpResponse response = client.execute(put)) {
  65.                 String loadResult = "";
  66.                 if (response.getEntity() != null) {
  67.                     loadResult = EntityUtils.toString(response.getEntity());
  68.                 }
  70.                 final int statusCode = response.getStatusLine().getStatusCode();
  71.                 if (statusCode != 200) {
  72.                     throw new IOException(
  73.                             String.format("Stream load failed. status: %s load result: %s", statusCode, loadResult));
  74.                 }
  76.                 System.out.println("Get load result: " + loadResult);
  77.             }
  78.         }
  79.     }
  81.     private String basicAuthHeader(String username, String password) {
  82.         final String tobeEncode = username + ":" + password;
  83.         byte[] encoded = Base64.encodeBase64(tobeEncode.getBytes(StandardCharsets.UTF_8));
  84.         return "Basic " + new String(encoded);
  85.     }
  87.     public static void main(String[] args) throws Exception{
  88.         DorisStreamLoader loader = new DorisStreamLoader();
  89.         File file = new File(LOAD_FILE_NAME);
  90.         loader.load(file);
  91.     }
  92. }

Note: The version of http client here is 4.5.13

  1. <dependency>
  2. <groupId>org.apache.httpcomponents</groupId>
  3. <artifactId>httpclient</artifactId>
  4. <version>4.5.13</version>
  5. </dependency>

MySql LOAD

SinceVersion devExample of mysql load

Import Data

  1. Create a table

    Use the CREATE TABLE command to create a table in the demo database to store the data to be imported.

    1. CREATE TABLE IF NOT EXISTS load_local_file_test
    2. (
    3. id INT,
    4. age TINYINT,
    5. name VARCHAR(50)
    6. )
    7. unique key(id)
    8. DISTRIBUTED BY HASH(id) BUCKETS 3;

  2. Import data Excute fellowing sql statmeent in the mysql client to load client local file:

    1. LOAD DATA
    2. LOCAL
    3. INFILE '/path/to/local/demo.txt'
    4. INTO TABLE demo.load_local_file_test

    For more advanced operations of the MySQL Load command, see MySQL Load Command documentation.

  3. Wait for the import result

    The MySql Load command is a synchronous command, and a successful return indicates that the import is successful. If the imported data is large, a longer waiting time may be required. Examples are as follows:

    1. Query OK, 1 row affected (0.17 sec)
    2. Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
    • Load success if the client show the return rows. Otherwise sql statement will throw an exception and show the error message in client.
    • For details of other fields, please refer to the MySQL Load command documentation.

Import Suggestion

  • MySql Load can only import local files(which can be client local file or fe local file) and only support csv format.
  • It is recommended to limit the amount of data for an import request to 1 - 2 GB. If you have a large number of local files, you can submit them concurrently in batches.