If you're looking for information on adding documents to a plain table, please refer to the section on adding data from external storages.
Adding documents in real-time is supported only for Real-Time and percolate tables. The corresponding SQL command, HTTP endpoint, or client functions insert new rows (documents) into a table with the provided field values. It's not necessary for a table to exist before adding documents to it. If the table doesn't exist, Manticore will attempt to create it automatically. For more information, see Auto schema.
You can insert a single or multiple documents with values for all fields of the table or just a portion of them. In this case, the other fields will be filled with their default values (0 for scalar types, an empty string for text types).
Expressions are not currently supported in INSERT, so values must be explicitly specified.
The ID field/value can be omitted, as RT and PQ tables support auto-id functionality. For numeric-ID tables, you can also use 0 as the id value to force automatic ID generation. Rows with duplicate IDs will not be overwritten by INSERT. Instead, you can use REPLACE for that purpose.
For tables created with id uuid, pass an explicit UUID as a quoted string or omit id to generate one automatically. Explicit values must match xxxxxxxx-xxxx-Vxxx-Wxxx-xxxxxxxxxxxx, where each x is a hexadecimal digit, V is the version (1 through 8), and W is the variant (8, 9, a, or b). Uppercase hexadecimal letters are accepted and normalized to lowercase. Unlike numeric IDs, 0 does not request automatic UUID generation.
When using the HTTP JSON protocol, you have two different request formats to choose from: a common Manticore format and an Elasticsearch-like format. Both formats are demonstrated in the examples below.
Additionally, when using the Manticore JSON request format, keep in mind that the doc node is required, and all the values should be provided within it.
- SQL
- JSON
- Elasticsearch
- PHP
- Python
- Python-asyncio
- Javascript
- Java
- C#
General syntax:
INSERT INTO <table name> [(column, ...)]
VALUES (value, ...)
[, (...)]INSERT INTO products(title,price) VALUES ('Crossbody Bag with Tassel', 19.85);
INSERT INTO products(title) VALUES ('Crossbody Bag with Tassel');
INSERT INTO products VALUES (0,'Yellow bag', 4.95);POST /insert
{
"table":"products_uuid",
"id":"550e8400-e29b-41d4-a716-446655440000",
"doc":
{
"title":"Crossbody Bag with Tassel",
"price":19.85
}
}
POST /insert
{
"table":"products_uuid",
"doc":
{
"title":"Generated UUID Bag",
"price":29
}
}NOTE:
_createrequires Manticore Buddy. If it doesn't work, make sure Buddy is installed.
POST /products/_create/3
{
"title": "Yellow Bag with Tassel",
"price": 19.85
}
POST /products/_create/
{
"title": "Red Bag with Tassel",
"price": 19.85
}$index->addDocuments([
['id' => 1, 'title' => 'Crossbody Bag with Tassel', 'price' => 19.85],
['id' => 2, 'title' => 'microfiber sheet set', 'price' => 19.99],
['id' => 3, 'title' => 'Pet Hair Remover Glove', 'price' => 7.99]
]);For replicated tables, set the cluster name before adding documents:
// Set the cluster name
$index->setName('weekly_table')->setCluster('posts');
// Then add documents in bulk
$index->addDocuments([
['id' => 1, 'title' => 'Crossbody Bag with Tassel', 'price' => 19.85],
['id' => 2, 'title' => 'microfiber sheet set', 'price' => 19.99],
['id' => 3, 'title' => 'Pet Hair Remover Glove', 'price' => 7.99]
]);docs = [ \
{"insert": {"table" : "products", "id" : 1, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}}}, \
{"insert": {"table" : "products", "id" : 2, "doc" : {"title" : "microfiber sheet set", "price" : 19.99}}}, \
{"insert": {"table" : "products", "id" : 3, "doc" : {"title" : "Pet Hair Remover Glove", "price" : 7.99}}}
]
res = indexApi.bulk('\n'.join(map(json.dumps,docs)))For replicated tables, include the cluster property in each document:
docs = [ \
{"insert": {"cluster" : "posts", "table" : "weekly_table", "id" : 1, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}}}, \
{"insert": {"cluster" : "posts", "table" : "weekly_table", "id" : 2, "doc" : {"title" : "microfiber sheet set", "price" : 19.99}}}, \
{"insert": {"cluster" : "posts", "table" : "weekly_table", "id" : 3, "doc" : {"title" : "Pet Hair Remover Glove", "price" : 7.99}}}
]
res = indexApi.bulk('\n'.join(map(json.dumps,docs)))docs = [ \
{"insert": {"table" : "products", "id" : 1, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}}}, \
{"insert": {"table" : "products", "id" : 2, "doc" : {"title" : "microfiber sheet set", "price" : 19.99}}}, \
{"insert": {"table" : "products", "id" : 3, "doc" : {"title" : "Pet Hair Remover Glove", "price" : 7.99}}}
]
res = await indexApi.bulk('\n'.join(map(json.dumps,docs)))For replicated tables, include the cluster property in each document:
docs = [ \
{"insert": {"cluster" : "posts", "table" : "weekly_table", "id" : 1, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}}}, \
{"insert": {"cluster" : "posts", "table" : "weekly_table", "id" : 2, "doc" : {"title" : "microfiber sheet set", "price" : 19.99}}}, \
{"insert": {"cluster" : "posts", "table" : "weekly_table", "id" : 3, "doc" : {"title" : "Pet Hair Remover Glove", "price" : 7.99}}}
]
res = await indexApi.bulk('\n'.join(map(json.dumps,docs)))let docs = [
{"insert": {"table" : "products", "id" : 3, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}}},
{"insert": {"table" : "products", "id" : 4, "doc" : {"title" : "microfiber sheet set", "price" : 19.99}}},
{"insert": {"table" : "products", "id" : 5, "doc" : {"title" : "Pet Hair Remover Glove", "price" : 7.99}}}
];
res = await indexApi.bulk(docs.map(e=>JSON.stringify(e)).join('\n'));For replicated tables, include the cluster property in each document:
let docs = [
{"insert": {"cluster" : "posts", "table" : "weekly_table", "id" : 3, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}}},
{"insert": {"cluster" : "posts", "table" : "weekly_table", "id" : 4, "doc" : {"title" : "microfiber sheet set", "price" : 19.99}}},
{"insert": {"cluster" : "posts", "table" : "weekly_table", "id" : 5, "doc" : {"title" : "Pet Hair Remover Glove", "price" : 7.99}}}
];
res = await indexApi.bulk(docs.map(e=>JSON.stringify(e)).join('\n'));String body = "{\"insert\": {\"index\" : \"products\", \"id\" : 1, \"doc\" : {\"title\" : \"Crossbody Bag with Tassel\", \"price\" : 19.85}}}"+"\n"+
"{\"insert\": {\"index\" : \"products\", \"id\" : 4, \"doc\" : {\"title\" : \"microfiber sheet set\", \"price\" : 19.99}}}"+"\n"+
"{\"insert\": {\"index\" : \"products\", \"id\" : 5, \"doc\" : {\"title\" : \"Pet Hair Remover Glove\", \"price\" : 7.99}}}"+"\n";
BulkResponse bulkresult = indexApi.bulk(body);For replicated tables, include the cluster property in each document:
String body = "{\"insert\": {\"cluster\" : \"posts\", \"table\" : \"weekly_table\", \"id\" : 1, \"doc\" : {\"title\" : \"Crossbody Bag with Tassel\", \"price\" : 19.85}}}"+"\n"+
"{\"insert\": {\"cluster\" : \"posts\", \"table\" : \"weekly_table\", \"id\" : 4, \"doc\" : {\"title\" : \"microfiber sheet set\", \"price\" : 19.99}}}"+"\n"+
"{\"insert\": {\"cluster\" : \"posts\", \"table\" : \"weekly_table\", \"id\" : 5, \"doc\" : {\"title\" : \"Pet Hair Remover Glove\", \"price\" : 7.99}}}"+"\n";
BulkResponse bulkresult = indexApi.bulk(body);string body = "{\"insert\": {\"index\" : \"products\", \"id\" : 1, \"doc\" : {\"title\" : \"Crossbody Bag with Tassel\", \"price\" : 19.85}}}"+"\n"+
"{\"insert\": {\"index\" : \"products\", \"id\" : 4, \"doc\" : {\"title\" : \"microfiber sheet set\", \"price\" : 19.99}}}"+"\n"+
"{\"insert\": {\"index\" : \"products\", \"id\" : 5, \"doc\" : {\"title\" : \"Pet Hair Remover Glove\", \"price\" : 7.99}}}"+"\n";
BulkResponse bulkresult = indexApi.Bulk(string.Join("\n", docs));For replicated tables, include the cluster property in each document:
string body = "{\"insert\": {\"cluster\" : \"posts\", \"table\" : \"weekly_table\", \"id\" : 1, \"doc\" : {\"title\" : \"Crossbody Bag with Tassel\", \"price\" : 19.85}}}"+"\n"+
"{\"insert\": {\"cluster\" : \"posts\", \"table\" : \"weekly_table\", \"id\" : 4, \"doc\" : {\"title\" : \"microfiber sheet set\", \"price\" : 19.99}}}"+"\n"+
"{\"insert\": {\"cluster\" : \"posts\", \"table\" : \"weekly_table\", \"id\" : 5, \"doc\" : {\"title\" : \"Pet Hair Remover Glove\", \"price\" : 7.99}}}"+"\n";
BulkResponse bulkresult = indexApi.Bulk(string.Join("\n", docs));Query OK, 1 rows affected (0.00 sec)
Query OK, 1 rows affected (0.00 sec)
Query OK, 1 rows affected (0.00 sec){
"table": "products_uuid",
"id": "550e8400-e29b-41d4-a716-446655440000",
"created": true,
"result": "created",
"status": 201
}
{
"table": "products_uuid",
"id": "<generated UUID>",
"created": true,
"result": "created",
"status": 201
}{
"_id":3,
"table":"products",
"_primary_term":1,
"_seq_no":0,
"_shards":{
"failed":0,
"successful":1,
"total":1
},
"_type":"_doc",
"_version":1,
"result":"updated"
}
{
"_id":2235747273424240642,
"table":"products",
"_primary_term":1,
"_seq_no":0,
"_shards":{
"failed":0,
"successful":1,
"total":1
},
"_type":"_doc",
"_version":1,
"result":"updated"
}When working with replicated tables, you must use a special syntax to ensure that write operations are properly propagated to all nodes in the cluster.
For all write operations (INSERT, REPLACE, DELETE, TRUNCATE, UPDATE) on replicated tables, you must:
- In SQL: Use the
cluster_name:table_nameformat instead of just the table name - In JSON: Include the
clusterproperty along with thetableproperty
If you don't use the correct syntax, the operation will fail with an error.
- SQL
- JSON
- PHP
- Python
- Javascript
- Java
- C#
- Rust
INSERT INTO posts:weekly_table(title,price) VALUES ('Crossbody Bag with Tassel', 19.85);
INSERT INTO posts:weekly_table VALUES (0,'Yellow bag', 4.95);POST /insert
{
"cluster":"posts",
"table":"weekly_table",
"id":1,
"doc":
{
"title" : "Crossbody Bag with Tassel",
"price" : 19.85
}
}
POST /insert
{
"cluster":"posts",
"table":"weekly_table",
"id":0,
"doc":
{
"title" : "Yellow bag",
"price" : 4.95
}
}// Set the cluster name
$index->setName('weekly_table')->setCluster('posts');
// Then add documents
$index->addDocuments([
['id' => 1, 'title' => 'Crossbody Bag with Tassel', 'price' => 19.85],
['id' => 2, 'title' => 'microfiber sheet set', 'price' => 19.99],
['id' => 3, 'title' => 'Pet Hair Remover Glove', 'price' => 7.99]
]);indexApi.insert({"cluster":"posts", "table":"weekly_table", "id":1, "doc":{"title":"Crossbody Bag with Tassel", "price":19.85}})
indexApi.insert({"cluster":"posts", "table":"weekly_table", "id":0, "doc":{"title":"Yellow bag", "price":4.95}})res = await indexApi.insert({"cluster":"posts", "table":"weekly_table", "id":1, "doc":{"title":"Crossbody Bag with Tassel", "price":19.85}});
res = await indexApi.insert({"cluster":"posts", "table":"weekly_table", "id":0, "doc":{"title":"Yellow bag", "price":4.95}});InsertDocumentRequest newdoc = new InsertDocumentRequest();
HashMap<String,Object> doc = new HashMap<String,Object>(){{
put("title","Crossbody Bag with Tassel");
put("price",19.85);
}};
newdoc.table("weekly_table").cluster("posts").id(1L).setDoc(doc);
sqlresult = indexApi.insert(newdoc);
newdoc = new InsertDocumentRequest();
HashMap<String,Object> doc2 = new HashMap<String,Object>(){{
put("title","Yellow bag");
put("price",4.95);
}};
newdoc.table("weekly_table").cluster("posts").id(0L).setDoc(doc2);
sqlresult = indexApi.insert(newdoc);Dictionary<string, Object> doc = new Dictionary<string, Object>();
doc.Add("title", "Crossbody Bag with Tassel");
doc.Add("price", 19.85);
InsertDocumentRequest newdoc = new InsertDocumentRequest(table: "weekly_table", cluster: "posts", id: 1, doc: doc);
var sqlresult = indexApi.Insert(newdoc);
doc = new Dictionary<string, Object>();
doc.Add("title", "Yellow bag");
doc.Add("price", 4.95);
newdoc = new InsertDocumentRequest(table: "weekly_table", cluster: "posts", id: 0, doc: doc);
sqlresult = indexApi.Insert(newdoc);let mut doc = HashMap::new();
doc.insert("title".to_string(), serde_json::json!("Crossbody Bag with Tassel"));
doc.insert("price".to_string(), serde_json::json!(19.85));
let mut insert_req = InsertDocumentRequest {
table: serde_json::json!("weekly_table"),
doc: serde_json::json!(doc),
cluster: serde_json::json!("posts"),
id: serde_json::json!(1),
};
let mut insert_res = index_api.insert(insert_req).await;
doc = HashMap::new();
doc.insert("title".to_string(), serde_json::json!("Yellow bag"));
doc.insert("price".to_string(), serde_json::json!(4.95));
insert_req = InsertDocumentRequest {
table: serde_json::json!("weekly_table"),
doc: serde_json::json!(doc),
cluster: serde_json::json!("posts"),
id: serde_json::json!(0),
};
insert_res = index_api.insert(insert_req).await;Query OK, 1 rows affected (0.00 sec)
Query OK, 1 rows affected (0.00 sec){
"table": "weekly_table",
"id": 1,
"created": true,
"result": "created",
"status": 201
}
{
"table": "weekly_table",
"id": 1657860156022587406,
"created": true,
"result": "created",
"status": 201
}NOTE: Auto schema requires Manticore Buddy. If it doesn't work, make sure Buddy is installed.
Manticore features an automatic table creation mechanism, which activates when a specified table in the insert or replace query doesn't yet exist. This mechanism is enabled by default. To disable it, set auto_schema = 0 in the Searchd section of your Manticore config file.
By default, all text values in the VALUES clause are considered to be of the text type, except for values representing valid email addresses, which are treated as the string type.
If you attempt to INSERT/REPLACE multiple rows with different, incompatible value types for the same field, auto table creation will be canceled, and an error message will be returned. However, if the different value types are compatible, the resulting field type will be the one that accommodates all the values. Some automatic data type conversions that may occur include:
- mva -> mva64
- uint -> bigint -> float (this may cause some precision loss)
- string -> text
The auto schema mechanism does not support creating tables with vector fields (fields of type float_vector) used for KNN (K-Nearest Neighbors) similarity search. To use vector fields in your table, you must explicitly create the table with a schema that defines these fields. If you need to store vector data in a regular table without KNN search capability, you can store it as a JSON array using the standard JSON syntax, for example: INSERT INTO table_name (vector_field) VALUES ('[1.0, 2.0, 3.0]').
Also, the following formats of dates will be recognized and converted to timestamps while all other date formats will be treated as strings:
%Y-%m-%dT%H:%M:%E*S%Z%Y-%m-%d'T'%H:%M:%S%Z%Y-%m-%dT%H:%M:%E*S%Y-%m-%dT%H:%M:%s%Y-%m-%dT%H:%M%Y-%m-%dT%H
Keep in mind that the /bulk HTTP endpoint does not support automatic table creation (auto schema). Only the /_bulk (Elasticsearch-like) endpoint and the SQL interface support this feature.
- SQL
- JSON
MySQL [(none)]> drop table if exists t; insert into t(i,f,t,s,j,b,m,mb) values(123,1.2,'text here','test@mail.com','{"a": 123}',1099511627776,(1,2),(1099511627776,1099511627777)); desc t; select * from t;POST /insert -d
{
"table":"t",
"id": 2,
"doc":
{
"i" : 123,
"f" : 1.23,
"t": "text here",
"s": "test@mail.com",
"j": {"a": 123},
"b": 1099511627776,
"m": [1,2],
"mb": [1099511627776,1099511627777]
}
}--------------
drop table if exists t
--------------
Query OK, 0 rows affected (0.42 sec)
--------------
insert into t(i,f,t,j,b,m,mb) values(123,1.2,'text here','{"a": 123}',1099511627776,(1,2),(1099511627776,1099511627777))
--------------
Query OK, 1 row affected (0.00 sec)
--------------
desc t
--------------
+-------+--------+----------------+
| Field | Type | Properties |
+-------+--------+----------------+
| id | bigint | |
| t | text | indexed stored |
| s | string | |
| j | json | |
| i | uint | |
| b | bigint | |
| f | float | |
| m | mva | |
| mb | mva64 | |
+-------+--------+----------------+
8 rows in set (0.00 sec)
--------------
select * from t
--------------
+---------------------+------+---------------+----------+------+-----------------------------+-----------+---------------+------------+
| id | i | b | f | m | mb | t | s | j |
+---------------------+------+---------------+----------+------+-----------------------------+-----------+---------------+------------+
| 5045949922868723723 | 123 | 1099511627776 | 1.200000 | 1,2 | 1099511627776,1099511627777 | text here | test@mail.com | {"a": 123} |
+---------------------+------+---------------+----------+------+-----------------------------+-----------+---------------+------------+
1 row in set (0.00 sec){"table":"t","id":2,"created":true,"result":"created","status":201}Manticore provides automatic ID generation for documents inserted or replaced into a real-time or Percolate table. The generator produces a unique numeric value with the guarantees below, but it should not be considered an auto-incrementing sequence.
The generated ID value is guaranteed to be unique under the following conditions:
- The server_id value of the current server is in the range of 0 to 127 and is unique among nodes in the cluster, or it uses the default value generated from the MAC address as a seed
- The system time does not change for the Manticore node between server restarts
- The average auto-ID generation rate between two server starts stays below about 16 million IDs per second
The auto ID generator creates a 64-bit integer with the following layout:
- Bits 0 to 23 are a counter that is incremented on every call to the auto ID generator
- Bits 24 to 55 store the server start time in seconds, encoded as
(unix_timestamp_at_start - 2019-05-01 00:00:00 UTC) - Bits 56 to 62 store the
server_id(the value is masked to the range 0..127)
This layout ensures that generated IDs are unique among cluster nodes and that data inserted into different cluster nodes does not create collisions. This is particularly important when working with replicated tables, as it guarantees that auto-generated IDs are unique across all nodes in the replication cluster.
Important: the 24-bit counter is not a hard limit on the total number of documents you can insert during one server run. You can insert more than 16,777,216 documents after startup; the IDs will still keep increasing and remain unique for that running process. The ~16 million IDs per second rule matters for uniqueness across restarts: after a restart, the time-based part must advance far enough so that newly generated IDs do not overlap with IDs created before the restart.
As a result, the first ID from the generator used for auto ID is NOT 1 but a larger number. Additionally, the document stream inserted into a table might have non-sequential ID values if inserts into other tables occur between calls, as the ID generator is singular in the server and shared between all its tables.
For numeric-ID tables, this integer is the public document ID. For UUID-ID tables, Manticore encodes it as a canonical UUIDv8 string; clients see only the UUID.
- SQL
- JSON
- PHP
- Python
- Python-asyncio
- Javascript
- Java
- C#
- Rust
INSERT INTO products(title,price) VALUES ('Crossbody Bag with Tassel', 19.85);
INSERT INTO products VALUES (0,'Yello bag', 4.95);
select * from products;POST /insert
{
"table":"products",
"id":0,
"doc":
{
"title" : "Yellow bag"
}
}
GET /search
{
"table":"products",
"query":{
"query_string":""
}
}$index->addDocuments([
['id' => 0, 'title' => 'Yellow bag']
]);indexApi.insert({"table" : "products", "id" : 0, "doc" : {"title" : "Yellow bag"}})await indexApi.insert({"table" : "products", "id" : 0, "doc" : {"title" : "Yellow bag"}})res = await indexApi.insert({"table" : "products", "id" : 0, "doc" : {"title" : "Yellow bag"}});newdoc = new InsertDocumentRequest();
HashMap<String,Object> doc = new HashMap<String,Object>(){{
put("title","Yellow bag");
}};
newdoc.index("products").id(0L).setDoc(doc);
sqlresult = indexApi.insert(newdoc);Dictionary<string, Object> doc = new Dictionary<string, Object>();
doc.Add("title", "Yellow bag");
InsertDocumentRequest newdoc = new InsertDocumentRequest(index: "products", id: 0, doc: doc);
var sqlresult = indexApi.Insert(newdoc);let doc = HashMap::new();
doc.insert("title".to_string(), serde_json::json!("Yellow bag"));
let insert_req = InsertDocumentRequest {
table: serde_json::json!("products"),
doc: serde_json::json!(doc),
id: serde_json::json!(0),
..Default::default(),
};
let insert_res = index_api.insert(insert_req).await;+---------------------+-----------+---------------------------+
| id | price | title |
+---------------------+-----------+---------------------------+
| 1657860156022587404 | 19.850000 | Crossbody Bag with Tassel |
| 1657860156022587405 | 4.950000 | Yello bag |
+---------------------+-----------+---------------------------+{
"took": 0,
"timed_out": false,
"hits": {
"total": 1,
"hits": [
{
"_id": 1657860156022587406,
"_score": 1,
"_source": {
"price": 0,
"title": "Yellow bag"
}
}
]
}
}CALL UUID_SHORT(N)
The CALL UUID_SHORT(N) statement allows for generating N unique 64-bit IDs in a single call without inserting any documents. It is particularly useful when you need to pre-generate IDs in Manticore for use in other systems or storage solutions. For example, you can generate auto-IDs in Manticore and then use them in another database, application, or workflow, ensuring consistent and unique identifiers across different environments.
- Example
CALL UUID_SHORT(3)+---------------------+
| uuid_short() |
+---------------------+
| 1227930988733973183 |
| 1227930988733973184 |
| 1227930988733973185 |
+---------------------+You can insert not just a single document into a real-time table, but as many as you'd like. It's perfectly fine to insert batches of tens of thousands of documents into a real-time table. However, it's important to keep the following points in mind:
- The larger the batch, the higher the latency of each insert operation
- The larger the batch, the higher the indexation speed you can expect
- You might want to increase the max_packet_size value to allow for larger batches
- Normally, each batch insert operation is considered a single transaction with atomicity guarantee, so you will either have all the new documents in the table at once or, in case of failure, none of them will be added. See more details about an empty line or switching to another table in the "JSON" example.
Note that the /bulk HTTP endpoint does not support automatic creation of tables (auto schema). Only the /_bulk (Elasticsearch-like) endpoint and the SQL interface support this feature. The /_bulk (Elasticsearch-like) HTTP endpoint allows the table name to include the cluster name in the format cluster_name:table_name.
/_bulk endpoint accepts document IDs in the same format as Elasticsearch, and you can also include the id within the document itself:
{ "index": { "table": "products", "_id": "1" } }
{ "title": "Crossbody Bag with Tassel", "price": 19.85 }
or
{ "index": { "table": "products" } }
{ "title": "Crossbody Bag with Tassel", "price": 19.85, "id": "1" }
For an RT table declared with id uuid, /bulk reads UUIDs from id. /_bulk reads them from metadata _id or the document id. Both endpoints can omit the ID to generate a UUID automatically.
The /bulk (Manticore mode) endpoint supports Chunked transfer encoding. You can use it to transmit large batches. It:
- reduces peak RAM usage, lowering the risk of OOM
- decreases response time
- allows you to bypass max_packet_size and transfer batches much larger than the maximum allowed value of
max_packet_size(128MB), for example, 1GB at a time.
For faster large batch loads into a local real-time table, Manticore Search can write INSERT rows directly to a disk chunk and publish that chunk when the transaction commits. This avoids building the batch in a RAM chunk first. Rows remain invisible until the chunk is published, and a failed operation or ROLLBACK leaves the table unchanged.
It supports both row-wise and columnar tables, including full-text fields, numeric attributes, strings, JSON, MVA/MVA64, and float vectors with KNN indexes.
Table names are case-insensitive here. For example, SET bulk_import=Products and bulk_import=products both select the table stored as products; tables whose names differ only by letter case cannot be distinguished.
Enable the mode for the current SQL session, run one or more INSERT statements against the bound table, and commit:
SET bulk_import=products;
INSERT INTO products(id,title,price) VALUES
(101,'Crossbody Bag with Tassel',19.85),
(102,'Microfiber Sheet Set',19.99);
INSERT INTO products(id,title,price) VALUES
(103,'Pet Hair Remover Glove',7.99);
COMMIT;
SET bulk_import=0;
Run SET bulk_import=<table> before BEGIN and before any uncommitted write on that connection. It reserves the selected table for that session: searches remain available, while another bulk import or an ordinary write to the same table is rejected until the reservation is released. BEGIN and START TRANSACTION are allowed but not required and have no effect in this mode.
COMMIT publishes the rows collected since the previous COMMIT or ROLLBACK as one disk chunk. ROLLBACK discards those rows. In both cases, bulk import remains enabled so you can start another batch. Run SET bulk_import=0 to discard any uncommitted rows, disable bulk import, and release this connection's reservation. Writes to the table resume after the reservation is released. Closing the connection does the same. Autocommit cannot be changed while bulk import is enabled.
For a sharded table, SQL works differently: see Shared bulk import into a sharded table.
Add bulk_import=<table> to a Manticore /bulk request. The request body remains standard newline-delimited JSON (NDJSON), and each operation must be insert or create for the bound table:
printf '%s\n' \
'{"insert":{"table":"products","id":101,"doc":{"title":"Crossbody Bag with Tassel","price":19.85}}}' \
'{"insert":{"table":"products","id":102,"doc":{"title":"Microfiber Sheet Set","price":19.99}}}' |
curl -sS -X POST \
-H 'Content-Type: application/x-ndjson' \
--data-binary @- \
'http://localhost:9308/bulk?bulk_import=products'
A line can also be a bare document: the table comes from bulk_import=<table>, and id is one of the fields:
printf '%s\n' \
'{"id":101,"title":"Crossbody Bag with Tassel","price":19.85}' \
'{"id":102,"title":"Microfiber Sheet Set","price":19.99}' |
curl -sS -X POST \
-H 'Content-Type: application/x-ndjson' \
--data-binary @- \
'http://localhost:9308/bulk?bulk_import=products'
Every field of a bare document is a column, including a field named doc; a document without id gets an auto-generated ID. A line is an operation when its only field is named as a /bulk operation (insert, create, index, replace, update or delete) and holds an object with table or index; any other line is a bare document. Both forms can be mixed in one request. A /bulk request without bulk_import still requires the operation form.
In a request body, an empty line ends and publishes the current batch; the end of the request publishes the final batch. Manticore sends the HTTP response after processing all batches in that request. If a later batch fails, batches published earlier in the same request remain searchable. To publish the entire request atomically as one disk chunk, do not include empty lines. The response contains one aggregate bulk result for each published batch rather than one result per document.
For a typical import, send the complete NDJSON body in one request as shown above. Close the HTTP connection when the import is complete to release the table for other writes.
The endpoint supports chunked transfer encoding, so it can process bodies larger than max_packet_size without buffering the whole request.
For a sharded table, /bulk works differently: see Shared bulk import into a sharded table.
Add shared next to bulk_import=<table> when many concurrent requests, for example a log shipper that sends every flush over a new connection, load one table. All such requests feed one table-wide pool of indexers. The pool does not belong to a connection: it lives across requests, connections and reconnects until it is committed, cancelled with PURGE, the table is dropped, or the daemon stops.
curl -sS -X POST -H 'Content-Type: application/x-ndjson' --data-binary @part1.ndjson \
'http://localhost:9308/bulk?bulk_import=products&shared=4'
Each line must be an insert or create for the target table, or a bare document as described above. The first document creates the pool and starts its indexers. A successful data response ("shared_state":"staged") means the documents are staged, not searchable: neither the end of a request, nor a closed connection, nor HTTP chunk boundaries publish anything.
From the first document until the pool is committed, fails or is cancelled, the pool reserves the table, as a private bulk import does: searches keep working, while ordinary writes (INSERT, REPLACE, UPDATE, DELETE) and another bulk import of the same table fail with an error saying that the table is locked. Closing connections does not release the table. SHOW LOCKS lists a reserved table with the lock type write_allow_readers. The first document of a new pool is rejected with shared_precondition and the same error while another bulk import holds the table, while the table is locked with LOCK TABLES … READ, or while an ordinary write to it is running.
A request without documents that contains an empty line commits the pool:
printf '\n' | curl -sS -X POST -H 'Content-Type: application/x-ndjson' --data-binary @- \
'http://localhost:9308/bulk?bulk_import=products&shared'
The commit closes the pool to new data, stops the indexers that received no data, waits until every other indexer has built its part, attaches one disk chunk per such indexer, removes the pool and answers "shared_state":"published". If other data requests are still running, the commit answers HTTP 409 with shared_busy and the pool stays open; repeat the commit after the writers finish. A commit without a pool answers 409 with shared_no_pool, because a commit never creates a pool; a pool that received no data publishes no chunk. Empty lines in a request with documents, including lines of only spaces, are ignored, so a data request never publishes. LF and CRLF both end a line.
The number of indexers, and so of new disk chunks, is set by shared=N in the request that creates the pool. With a bare shared, the daemon uses the INDEXER_RT_BULK_SHARED_POOL_SIZE environment variable of searchd, otherwise one indexer. N is limited to the range from 1 to the number of worker threads minus 2 (at least 1): a smaller value, or one that is not a number, gives one indexer, and a larger value gives the maximum. Only the request that creates the pool reads N; requests that join the open pool, and the commit, ignore it. Data is spread over the indexers in batches of about 32 KiB, in turn. More indexers shorten the build after the commit but use more CPU and memory while loading. A good starting point is about two thirds of the worker threads, for example 8 indexers with 12 threads; with more, the indexers compete with the incoming requests for CPU and disk, and the load gets slower. Each indexer needs roughly 200 MB of RAM while loading with the default mem_limit.
Every response is one JSON object with errors, error, error_code and shared_state:
shared_state |
HTTP | error_code |
Meaning |
|---|---|---|---|
staged |
200 | data is in the pool | |
published |
200 | the pool was committed | |
busy |
409 | shared_busy |
commit while other data requests are active |
closing |
409 | shared_closing |
the pool is being published |
no_pool |
409 | shared_no_pool |
commit, or a data request with shared_join, without a pool |
rejected |
400, or 403 without write permission | shared_precondition |
invalid request, e.g. no write permission or a table that is locked when the pool is created |
failed |
400, 409 or 500 | shared_failed |
the load failed, see below |
current_line counts the documents of the request; a commit reports 1. On an error skipped_lines equals current_line, although the batches of the request already handed to the indexers stay in the pool.
If a connection breaks or times out in the middle of a request body, or the request is killed with KILL, only that request fails: its last unwritten part (less than 32 KiB) is dropped, data already handed to the indexers stays, and the pool remains open, so the client can resend the request. An invalid document (malformed JSON, unknown column, another table, an operation other than insert/create) or a failed write to an indexer fails the whole load: its indexers are stopped and the table is released at once, that request gets 400 for an invalid document or 500 for a failed write, every later request gets 409 with the first error, and the next commit returns this error with 500 and removes the failed pool and its staging files; PURGE … OPTION clear_sessions=1 removes it as well. Only then can a new load start.
A failed request and a failed load get the same answer, failed with shared_failed (400, or 500 for a killed request), so the response alone does not tell them apart. The next request does: staged means the pool is still open, while 409 with shared_failed means the load failed.
A pool lives only in the memory of searchd. If the daemon stops or crashes during a load, or the pool is cancelled, its staged documents are gone, and the next data request would start a new pool that holds only the later documents. To notice this, add shared_join to every data request after the first staged reply: such a request only joins the open pool and never creates one, so without a pool it answers 409 with shared_no_pool, and the load can be repeated from the start.
The pool does not end when its clients do. If they are gone before the commit, for example because a log shipper was stopped or crashed, the pool stays open and keeps the table reserved, so ordinary writes keep failing. Release the table in one of two ways: commit the pool as shown above, which publishes the documents staged so far, or cancel it, which discards them:
PURGE BULK_IMPORT FROM TABLE products OPTION clear_sessions=1;
The cancel stops the indexers, releases the table and removes the staging files; chunks published earlier stay in the table. A commit answers 409 with shared_busy while a data request of a vanished client still counts as running, until that request times out; the cancel does not wait for it. Once the table is released, SHOW LOCKS no longer lists it, and a new Shared load can start. See PURGE BULK_IMPORT for details.
A sharded table also takes a Shared HTTP bulk import, and SQL INSERT statements can feed the same load. Name the sharded table in the request:
curl -sS -X POST -H 'Content-Type: application/x-ndjson' --data-binary @part1.ndjson \
'http://localhost:9308/bulk?bulk_import=sharded_products'
Send all requests of a load and all ordinary writes to the sharded table through one node, the node that coordinates the load. With replication factor 1, a load started on another node is refused at its first document. With a higher replication factor, it can pick other replicas and is not refused.
The coordinating node routes every document to the shard that a regular INSERT into the sharded table would choose, after the same ID conversion and auto-ID assignment. A document with a cluster prefix is checked as in a regular INSERT into the sharded table. A bare document takes the cluster of the sharded table.
The first document of the load starts a Shared pool with one indexer on every shard; for the memory an indexer needs, see Shared HTTP bulk import. A pool runs on the node of its shard, so every node with shards of the table needs:
indexernext tosearchd;- a version of Manticore Search that supports this load. An older node fails the load at its first document.
Every shard that received documents gets one new disk chunk. A request needs no shared; shared=N and INDEXER_RT_BULK_SHARED_POOL_SIZE are ignored. The responses, shared_join and the empty-line commit work as described above.
With a replication factor above 1, every shard is a table in a replication cluster with a replica on several nodes. When the load starts, the coordinating node picks one replica of every shard, as a regular write does, and the pool of the shard runs on that replica. At the commit, that replica publishes its chunk as a bulk import into a replicated table does. The load does not move to another replica: if the picked replica fails, the load fails as with one replica.
SQL is one more client of the same load. On a connection to the coordinating node, run SET bulk_import=<sharded table>. Then every INSERT works as a data request and COMMIT as the commit request:
SET bulk_import=sharded_products;
INSERT INTO sharded_products(id,title,price) VALUES
(101,'Crossbody Bag with Tassel',19.85),
(102,'Microfiber Sheet Set',19.99);
COMMIT;
Unlike SQL bulk import into a real-time table, the load belongs to the sharded table, not to the connection:
SET bulk_import=<sharded table>reserves nothing; the shards are reserved from the first document.- Other SQL connections and HTTP requests can feed the same load.
SET bulk_import=0or a closed connection leaves the load open.- An invalid
INSERTfails the whole load, as an invalid document does. COMMITfails while another request is writing to the load, and when there is no load to commit.ROLLBACKis not supported. Cancel the load withPURGE BULK_IMPORT FROM TABLE <sharded table> OPTION clear_sessions=1.
The coordinating node refuses an ordinary INSERT, REPLACE, DELETE or UPDATE of the sharded table with table '<sharded table>' is locked until the commit ends, with any replication factor. Nothing is applied to any shard. In a transaction, INSERT, REPLACE and DELETE are accepted, and COMMIT is refused. An UPDATE of a sharded table is not part of a transaction and is refused at once.
A write that bypasses the coordinating node, through another node or straight into a shard:
- with one replica, fails with
table is lockedon every shard that runs a pool of the load; - with a replication factor above
1, can reach a replica without the pool and is applied there. A write whose commit had passed the check of the coordinating node before the load started can also finish this way. A published row replaces a row with the same ID in the order the cluster applies them.
A direct Shared request into a shard of the load is refused. SHOW LOCKS and @files show the shards by their own names.
Until the commit, any of these fails the whole load and cancels its pools on all shards:
- an invalid document;
- a shard that cannot start a pool: it is locked by another bulk import or by a running ordinary write, it is a local shard changed after the sharded table was loaded, or its node has no
indexer; - a failed write to a shard;
- any failed call to a remote shard, including a lost connection or a timeout.
The next commit returns the error and removes the load, or PURGE cancels it. Then repeat the whole load. A client that breaks its own request, or a KILL of a data request, fails only that request.
The cancel is best effort. After a load has failed, the coordinating node no longer refuses writes, but a shard whose node the cancel did not reach keeps its pool. Such a shard refuses ordinary writes with table is locked, so an ordinary commit that spans several shards can apply its changes to some of them before it stops there. The next load is refused on that shard too. Remove such pools as described below.
The commit publishes all the shards at once. Every indexer builds its chunk at the same time, so the commit takes about as long as the largest shard. A shard that fails does not stop the others, and the shards that succeeded keep their chunks. The commit then answers 500 with shared_failed and an error that names the first failed shard and its error, followed by published <N> of <M> tables and the published shards.
A failed shard can still have published its rows:
- the reply of a remote shard to the commit was lost. The error says that its commit is not confirmed.
- a replica of a replicated shard failed to attach its chunk while the other nodes of its cluster attached it. The rows reach every node, and that replica gets them when it rejoins the cluster.
After a failed commit, repeating the load with explicit IDs is safe. A KILL of the commit request fails the local shards that are still building. Remote shards have already received the commit and finish it.
A shard pool stays on its node until the load is committed or cancelled, also when the coordinating node stops or crashes. Remove leftover pools with PURGE BULK_IMPORT FROM TABLE <sharded table> OPTION clear_sessions=1 on any node that has the sharded table, or by the shard name on the node of the shard. PURGE BULK_IMPORT describes its errors. Run it:
- after the coordinating node stopped or crashed. A daemon shutdown cancels the load it coordinates and the pools of its own shards, but not the pools on other nodes.
- with a replication factor above
1, after the coordinating node restarts, before you write to the sharded table or start a new load. Until then, a write or a new load that picks the replica with the old pool is refused, and one that picks another replica is not. - when a node that the cancel did not reach is back.
DROP TABLE of the sharded table on the coordinating node cancels the load and the pools of its shards with the same best effort. A commit that is already running finishes. A pool left on an unreachable node can then be removed only by the shard name on that node.
Duplicate document IDs follow the rules below for each shard: a document ID always goes to the same shard, so the first row with an ID in the load stays, and a published row replaces a row with the same ID.
A real-time table in a replication cluster takes SQL, Manticore /bulk and Shared bulk import on any node of the cluster. As in any write to a cluster table, every row must name the cluster: INSERT INTO <cluster>:<table> in SQL and "cluster":"<cluster>" in JSON. A row without it is rejected with the same error as a regular INSERT. So is a row with a cluster for a table that is not in a cluster. SET bulk_import=<table> and the bulk_import=<table> parameter take the table name without the cluster. A bare document belongs to the table of the request and goes to its cluster without a cluster name.
SET bulk_import=products;
INSERT INTO posts:products(id,title,price) VALUES
(101,'Crossbody Bag with Tassel',19.85),
(102,'Microfiber Sheet Set',19.99);
COMMIT;
SET bulk_import=0;
printf '%s\n' \
'{"insert":{"cluster":"posts","table":"products","id":101,"doc":{"title":"Crossbody Bag with Tassel","price":19.85}}}' |
curl -sS -X POST -H 'Content-Type: application/x-ndjson' --data-binary @- \
'http://localhost:9308/bulk?bulk_import=products'
The node that runs the load builds the disk chunk as described above. At commit it:
- Waits until no node of the cluster is a donor or is joining. It checks up to twice replication_retry_count times, twice replication_retry_delay apart, and then fails the commit.
- Sends the chunk files to every other node of the cluster. Each node checks the chunk when it has received it.
- Attaches the chunk on all nodes, its own included, with one replicated command.
The commit answers once the node that runs the load has attached the chunk. The other nodes attach it as they apply the command, as with any replicated write. A Shared commit does this for every chunk it publishes, one after another.
Sending the chunk uses the same file transfer as adding a table to a cluster, to all nodes at once. So a commit takes longer than the same commit into a local table, by about the time that ALTER CLUSTER ... ADD of a table of the chunk's size would take. The command itself only attaches the files that the nodes already have. If the table has rows in RAM that are not saved to disk yet, for example from ordinary writes through other nodes, the command first saves them to disk on every node, and replication of the cluster waits until it finishes.
Each node keeps a received chunk in <table-filebase>.indexer-rt-bulk/repl-<id>.<ext>, with a .new suffix while it is being received, and keeps the prepared chunk open in memory until the attach command takes it or cleanup discards it.
While a load runs, only the node that runs it reserves the table: on that node, ordinary writes to the table fail with table is locked, as described above. Writes through other nodes keep working and are replicated as usual. Another node can run its own bulk import into the same table at the same time; every commit attaches its own chunk. A published row replaces a row with the same ID on every node, also a row written through another node while the load ran.
- A node of the cluster cannot receive the chunk, for example it does not answer or cannot create the staging directory of the table. The commit fails with an error that names the node, every node that answers removes the chunk, and no node changes the table. A Shared commit publishes its chunks one by one, so the chunks attached before the failed one stay on all nodes, as described in Duplicate document IDs.
- A node is not in the cluster at the moment of the commit, for example because it stopped or lost its network. It does not get the chunk. When it comes back, it rejoins the cluster with a full state transfer (SST) and gets the table with the rows.
- A node has left the cluster but is still in its node list and accepts connections without answering, for example a hung process. It delays the commit: the check for donor and joining nodes waits for its answer, which can take minutes. Run
ALTER CLUSTER <cluster> UPDATE nodesto drop such a node from the list. A node that does not accept connections delays the commit by seconds only. - A node cannot attach a chunk that the other nodes attach, for example because its copy was removed. It leaves the cluster: the replication provider stops the daemon, and after a restart (at once, if the watchdog is enabled) the node rejoins with a full state transfer.
- The node that runs the load cannot attach the chunk that the other nodes attach. The client gets an error saying that the other nodes attached the chunk, and the node rejoins the cluster with a full state transfer without a restart. If this rejoin fails twice, the cluster stays
closedon the node, with the reason incluster_<name>_last_error(see Replication cluster status). Restart the daemon, and the node rejoins with a full state transfer.
Until a node that did not attach a chunk has left the cluster, it does not send its tables to a joining node. A JOIN CLUSTER that it serves fails with the donor did not attach a bulk_import chunk and leaves the cluster, join again; repeat the join.
Between the delivery of a chunk and its attach command, PURGE BULK_IMPORT of the table is refused on every node that keeps the chunk. A node can also keep a delivered chunk after the load has ended, when:
- the node that runs the load stopped before the command;
- the node itself was out of the cluster at the moment of the command;
- the node did not answer while the delivery was cancelled.
The chunk stays in the staging root of the table, which @files shows. PURGE BULK_IMPORT describes when to wait for the commit and when to remove the chunk.
- A table with external files, such as stopwords, wordforms or exceptions, is not supported: the chunk is built on one node and attached on all nodes as it is. This is not checked, so such a load is not refused.
- Every node of the cluster needs a version of Manticore Search that supports it. When upgrading from an earlier version, stop all nodes of the cluster and start it again, see Upgrading a cluster.
The Elasticsearch-compatible /_bulk endpoint does not support direct-to-disk bulk_import; use SQL or Manticore /bulk.
Within one direct-to-disk batch, the first row for a numeric document ID remains visible and later rows with the same ID are logically removed. For SQL, a batch consists of the rows staged before COMMIT or ROLLBACK. For Manticore /bulk, each group published at an empty line, table change, or request EOF is a separate batch. In a Shared load, the data of each indexer is a separate batch, and its chunks are attached one after another when the pool is committed. If attaching one of them fails, the chunks attached before it stay in the table and the commit answers 500; loading the same documents again is safe for explicit IDs, because a published row replaces any row with the same ID, while documents with auto-generated IDs are added again.
When Manticore publishes a disk chunk to the target table, a row in that chunk replaces any existing row with the same ID. This also applies to IDs published by an earlier HTTP batch. As a result, retrying a previously published batch replaces its rows, while duplicates within the retried batch still keep the first row. In a Manticore /bulk request, the create operation, for example:
{"create":{"table":"products",...}}
behaves like insert; it does not fail merely because the ID already exists.
A direct-to-disk load does not collect its documents in the memory of searchd. Each INSERT statement, or each part of a /bulk request body (about 32 KiB when the request has the shared parameter), is converted to CSV and passed to an indexer process over a pipe right away. indexer builds the chunk the same way as a plain table: it collects data in memory up to its mem_limit, flushes it to temporary files and, after the last row, merges them into the disk chunk. So the memory a load needs does not grow with its size: each indexer process takes its mem_limit plus its write buffers and the process itself, roughly 200 MB in total with the default 128 MB mem_limit. A SET bulk_import session or a /bulk request runs one indexer, a load with shared=N runs N; add the statement or request part being processed.
The indexer processes take these options of the searchd configuration file: mem_limit, write_buffer, max_iops, max_iosize and lemmatizer_cache of the indexer section, and lemmatizer_base, plugin_dir, on_json_attr_error, json_autoconv_numbers and json_autoconv_keynames of the common section. Other options of these sections are not used by bulk import. searchd reads them when it starts, as it reads the common section; a relative path is resolved as searchd resolves it. Without mem_limit and write_buffer a load uses 128 MB and 1 MB. JSON values are parsed with the options of the common section, as for INSERT; the deprecated copies of these options in the indexer section are not used.
mem_limit applies to every indexer process, and many can run at the same time: one for every SQL session with uncommitted bulk import rows and for every running /bulk request, N for every Shared load, and, on every node, one for every shard of a sharded table that a load writes. A large mem_limit set for plain tables multiplies by that number, and it does not make a load faster: in our tests a chunk was built fastest with the default 128 MB.
As with a plain table (see inplace_enable), the intermediate data in the temporary files is comparable in size to the finished chunk. Both are written to the table's staging directory, next to the table files, so at the peak the disk that holds the table needs free space for about twice the size of the new chunk. When the chunk is published, its files are renamed into the table without copying. See Staging files and cleanup.
Manticore removes the current staging directory after a normal COMMIT, ROLLBACK, mode disable, or session close. A Shared pool keeps its staging directories until it is committed or cancelled; closing connections does not remove them. A daemon or host crash can leave an abandoned staging directory behind. A later direct-to-disk load creates a new uniquely named directory and does not reuse or attach files left by the crashed load.
Use the following query to inspect all direct-to-disk staging entries for a table:
SELECT file, normalized, size
FROM products.@files
OPTION format='bulk_import';
The result recursively lists files and directories below the table's direct-to-disk staging root. Directories and other non-regular entries have a reported size of 0.
After confirming that no direct-to-disk load is active for the table, remove its entire staging root with PURGE BULK_IMPORT:
PURGE BULK_IMPORT FROM TABLE products;
While the table has an open Shared pool, PURGE is refused; PURGE BULK_IMPORT FROM TABLE products OPTION clear_sessions=1 cancels the pool first, stops its indexers, releases the table and then removes the staging root. While the pool is attaching its chunks, PURGE is refused; repeat it after the commit finishes. On Windows, PURGE … OPTION clear_sessions=1 or DROP TABLE while a data request is still writing to the pool can answer failed to remove bulk staging root: the pool is already cancelled, but its stopped indexers keep their files open for a moment; repeat the statement.
PURGE requires an existing local real-time table, or a sharded or distributed table, see PURGE BULK_IMPORT. For a table in a replication cluster, it purges only the node where it runs, see Bulk import into a replicated table. It removes only direct-to-disk staging state and does not change the table schema or indexed rows. It succeeds as a no-op when the staging root is absent. In configless mode, DROP TABLE also removes the table's direct-to-disk staging root and cancels its Shared pool; a daemon shutdown cancels every Shared pool. Chunks published earlier stay in the table.
- The target is one existing, unfrozen local real-time table, also one in a replication cluster, see Bulk import into a replicated table.
- For Manticore
/bulkand SQL, the target can also be a sharded table, see Shared bulk import into a sharded table. - Distributed, percolate, and plain tables are not supported.
- SQL supports only
INSERT. Manticore/bulkacceptsinsert,createand bare documents;index,replace,update, anddeleteare rejected. - A document ID is converted as in a regular
INSERT, including attr_autoconv_strict. A row without an ID, or with an ID that converts to 0, gets an auto-generated ID; the response does not return generated IDs, and a repeated load adds such rows again. UUID document IDs are not supported. - Static builds are not supported.
- The platform-specific executable (
indexeron Linux,indexer.exeon Windows) must be in the same directory as the runningsearchdexecutable. Manticore Search resolves only that sibling path and does not searchPATH. Use the executable from the same installation assearchdto ensure compatibility. If it is absent, unreadable, or cannot be started, only bulk import fails; normal startup and regular insertion remain available.
The assisted loader uses the csvpipe source. Its command defaults to /bin/cat on Linux and - on Windows, where - reads from the indexer's standard input. Set INDEXER_RT_BULK_CSV_PIPE_COMMAND in the searchd environment to override only this command. The override does not select another source or feed; the command must work with the platform's input feed and pass CSV rows to the indexer. An unsupported command causes the assisted load to fail.
- SQL
- JSON
- Elasticsearch
- PHP
- Python
- Python-asyncio
- Javascript
- Java
- C#
- Rust
For bulk insert, simply provide more documents in brackets after VALUES(). The syntax is:
INSERT INTO <table name>[(column1, column2, ...)] VALUES(value1[, value2 , ...]), (...)The optional column name list allows you to explicitly specify values for some of the columns present in the table. All other columns will be filled with their default values (0 for scalar types, empty string for string types).
For example:
INSERT INTO products(title,price) VALUES ('Crossbody Bag with Tassel', 19.85), ('microfiber sheet set', 19.99), ('Pet Hair Remover Glove', 7.99);The syntax is generally the same as for inserting a single document. Just provide more lines, one for each document, and use the /bulk endpoint instead of /insert. Enclose each document in the "insert" node. Note that it also requires:
Content-Type: application/x-ndjson- The data should be formatted as newline-delimited JSON (NDJSON). Essentially, this means that each line should contain exactly one JSON statement and end with a newline
\nand possibly\r.
The /bulk endpoint supports 'insert', 'replace', 'delete', and 'update' queries. Keep in mind that you can direct operations to multiple tables, but transactions are only possible for a single table. If you specify more, Manticore will gather operations directed to one table into a single transaction. When the table changes, it will commit the collected operations and initiate a new transaction. An empty line separating batches also leads to committing the previous batch and starting a new transaction. A request containing no operations, whether its body is empty or contains only empty or whitespace-only lines, is a successful no-op and returns an empty items array.
In the response for a /bulk request, you can find the following fields:
- "errors": shows whether any errors occurred (true/false)
- "error": describes the error that took place
- "current_line": the line number where execution stopped (or failed); empty lines, including the first empty line, are also counted
- "skipped_lines": the count of non-committed lines, beginning from the
current_lineand moving backward
POST /bulk
-H "Content-Type: application/x-ndjson" -d '
{"insert": {"table":"products", "id":1, "doc": {"title":"Crossbody Bag with Tassel","price" : 19.85}}}
{"insert":{"table":"products", "id":2, "doc": {"title":"microfiber sheet set","price" : 19.99}}}
'
POST /bulk
-H "Content-Type: application/x-ndjson" -d '
{"insert":{"table":"test1","id":21,"doc":{"int_col":1,"price":1.1,"title":"bulk doc one"}}}
{"insert":{"table":"test1","id":22,"doc":{"int_col":2,"price":2.2,"title":"bulk doc two"}}}
{"insert":{"table":"test1","id":23,"doc":{"int_col":3,"price":3.3,"title":"bulk doc three"}}}
{"insert":{"table":"test2","id":24,"doc":{"int_col":4,"price":4.4,"title":"bulk doc four"}}}
{"insert":{"table":"test2","id":25,"doc":{"int_col":5,"price":5.5,"title":"bulk doc five"}}}
'For replicated tables, include the cluster property in each operation:
POST /bulk
-H "Content-Type: application/x-ndjson" -d '
{"insert": {"cluster":"posts", "table":"weekly_table", "id":1, "doc": {"title":"Crossbody Bag with Tassel","price":19.85}}}
{"insert": {"cluster":"posts", "table":"weekly_table", "id":2, "doc": {"title":"microfiber sheet set","price":19.99}}}
{"insert": {"cluster":"posts", "table":"weekly_table", "id":3, "doc": {"title":"Pet Hair Remover Glove","price":7.99}}}
'NOTE:
_bulkrequires Manticore Buddy if the table doesn't exist yet. If it doesn't work, make sure Buddy is installed.
POST /_bulk
-H "Content-Type: application/x-ndjson" -d '
{ "index" : { "table" : "products" } }
{ "title" : "Yellow Bag", "price": 12 }
{ "create" : { "table" : "products" } }
{ "title" : "Red Bag", "price": 12.5, "id": 3 }
'Use method addDocuments():
$index->addDocuments([
['id' => 1, 'title' => 'Crossbody Bag with Tassel', 'price' => 19.85],
['id' => 2, 'title' => 'microfiber sheet set', 'price' => 19.99],
['id' => 3, 'title' => 'Pet Hair Remover Glove', 'price' => 7.99]
]);For replicated tables, set the cluster name before adding documents:
// Set the cluster name
$index->setName('weekly_table')->setCluster('posts');
// Then add documents in bulk
$index->addDocuments([
['id' => 1, 'title' => 'Crossbody Bag with Tassel', 'price' => 19.85],
['id' => 2, 'title' => 'microfiber sheet set', 'price' => 19.99],
['id' => 3, 'title' => 'Pet Hair Remover Glove', 'price' => 7.99]
]);docs = [ \
{"insert": {"table" : "products", "id" : 1, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}}}, \
{"insert": {"table" : "products", "id" : 2, "doc" : {"title" : "microfiber sheet set", "price" : 19.99}}}, \
{"insert": {"table" : "products", "id" : 3, "doc" : {"title" : "Pet Hair Remover Glove", "price" : 7.99}}}
]
res = indexApi.bulk('\n'.join(map(json.dumps,docs)))For replicated tables, include the cluster property in each document:
docs = [ \
{"insert": {"cluster" : "posts", "table" : "weekly_table", "id" : 1, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}}}, \
{"insert": {"cluster" : "posts", "table" : "weekly_table", "id" : 2, "doc" : {"title" : "microfiber sheet set", "price" : 19.99}}}, \
{"insert": {"cluster" : "posts", "table" : "weekly_table", "id" : 3, "doc" : {"title" : "Pet Hair Remover Glove", "price" : 7.99}}}
]
res = indexApi.bulk('\n'.join(map(json.dumps,docs)))docs = [ \
{"insert": {"table" : "products", "id" : 1, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}}}, \
{"insert": {"table" : "products", "id" : 2, "doc" : {"title" : "microfiber sheet set", "price" : 19.99}}}, \
{"insert": {"table" : "products", "id" : 3, "doc" : {"title" : "Pet Hair Remover Glove", "price" : 7.99}}}
]
res = await indexApi.bulk('\n'.join(map(json.dumps,docs)))For replicated tables, include the cluster property in each document:
docs = [ \
{"insert": {"cluster" : "posts", "table" : "weekly_table", "id" : 1, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}}}, \
{"insert": {"cluster" : "posts", "table" : "weekly_table", "id" : 2, "doc" : {"title" : "microfiber sheet set", "price" : 19.99}}}, \
{"insert": {"cluster" : "posts", "table" : "weekly_table", "id" : 3, "doc" : {"title" : "Pet Hair Remover Glove", "price" : 7.99}}}
]
res = await indexApi.bulk('\n'.join(map(json.dumps,docs)))let docs = [
{"insert": {"table" : "products", "id" : 3, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}}},
{"insert": {"table" : "products", "id" : 4, "doc" : {"title" : "microfiber sheet set", "price" : 19.99}}},
{"insert": {"table" : "products", "id" : 5, "doc" : {"title" : "Pet Hair Remover Glove", "price" : 7.99}}}
];
res = await indexApi.bulk(docs.map(e=>JSON.stringify(e)).join('\n'));For replicated tables, include the cluster property in each document:
let docs = [
{"insert": {"cluster" : "posts", "table" : "weekly_table", "id" : 3, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}}},
{"insert": {"cluster" : "posts", "table" : "weekly_table", "id" : 4, "doc" : {"title" : "microfiber sheet set", "price" : 19.99}}},
{"insert": {"cluster" : "posts", "table" : "weekly_table", "id" : 5, "doc" : {"title" : "Pet Hair Remover Glove", "price" : 7.99}}}
];
res = await indexApi.bulk(docs.map(e=>JSON.stringify(e)).join('\n'));String body = "{\"insert\": {\"index\" : \"products\", \"id\" : 1, \"doc\" : {\"title\" : \"Crossbody Bag with Tassel\", \"price\" : 19.85}}}"+"\n"+
"{\"insert\": {\"index\" : \"products\", \"id\" : 4, \"doc\" : {\"title\" : \"microfiber sheet set\", \"price\" : 19.99}}}"+"\n"+
"{\"insert\": {\"index\" : \"products\", \"id\" : 5, \"doc\" : {\"title\" : \"Pet Hair Remover Glove\", \"price\" : 7.99}}}"+"\n";
BulkResponse bulkresult = indexApi.bulk(body);For replicated tables, include the cluster property in each document:
String body = "{\"insert\": {\"cluster\" : \"posts\", \"table\" : \"weekly_table\", \"id\" : 1, \"doc\" : {\"title\" : \"Crossbody Bag with Tassel\", \"price\" : 19.85}}}"+"\n"+
"{\"insert\": {\"cluster\" : \"posts\", \"table\" : \"weekly_table\", \"id\" : 4, \"doc\" : {\"title\" : \"microfiber sheet set\", \"price\" : 19.99}}}"+"\n"+
"{\"insert\": {\"cluster\" : \"posts\", \"table\" : \"weekly_table\", \"id\" : 5, \"doc\" : {\"title\" : \"Pet Hair Remover Glove\", \"price\" : 7.99}}}"+"\n";
BulkResponse bulkresult = indexApi.bulk(body);string body = "{\"insert\": {\"index\" : \"products\", \"id\" : 1, \"doc\" : {\"title\" : \"Crossbody Bag with Tassel\", \"price\" : 19.85}}}"+"\n"+
"{\"insert\": {\"index\" : \"products\", \"id\" : 4, \"doc\" : {\"title\" : \"microfiber sheet set\", \"price\" : 19.99}}}"+"\n"+
"{\"insert\": {\"index\" : \"products\", \"id\" : 5, \"doc\" : {\"title\" : \"Pet Hair Remover Glove\", \"price\" : 7.99}}}"+"\n";
BulkResponse bulkresult = indexApi.Bulk(string.Join("\n", docs));For replicated tables, include the cluster property in each document:
string body = "{\"insert\": {\"cluster\" : \"posts\", \"table\" : \"weekly_table\", \"id\" : 1, \"doc\" : {\"title\" : \"Crossbody Bag with Tassel\", \"price\" : 19.85}}}"+"\n"+
"{\"insert\": {\"cluster\" : \"posts\", \"table\" : \"weekly_table\", \"id\" : 4, \"doc\" : {\"title\" : \"microfiber sheet set\", \"price\" : 19.99}}}"+"\n"+
"{\"insert\": {\"cluster\" : \"posts\", \"table\" : \"weekly_table\", \"id\" : 5, \"doc\" : {\"title\" : \"Pet Hair Remover Glove\", \"price\" : 7.99}}}"+"\n";
BulkResponse bulkresult = indexApi.Bulk(string.Join("\n", docs));let bulk_body = r#"{"insert": "index" : "products", "id" : 1, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}}}
{"insert": {"index" : "products", "id" : 4, "doc" : {"title" : "microfiber sheet set", "price" : 19.99}}}
{"insert": {"index" : "products", "id" : 5, "doc" : {"title" : "CPet Hair Remover Glove", "price" : 7.99}}}
"#;
index_api.bulk(bulk_body).await;Query OK, 3 rows affected (0.01 sec)Expressions are currently not supported in INSERT, and values should be explicitly specified.
{
"items": [
{
"table": {
"table": "products",
"_type": "doc",
"_id": 1657860156022587406,
"_version": 1,
"result": "created",
"_shards": {
"total": 1,
"successful": 1,
"failed": 0
},
"_seq_no": 0,
"_primary_term": 1,
"status": 201
}
},
{
"create": {
"table": "products",
"_type": "doc",
"_id": 3,
"_version": 1,
"result": "created",
"_shards": {
"total": 1,
"successful": 1,
"failed": 0
},
"_seq_no": 0,
"_primary_term": 1,
"status": 201
}
}
],
"errors": false,
"took": 1
}For replicated tables, include the cluster name in the table name using the format cluster_name:table_name:
POST /_bulk
-H "Content-Type: application/x-ndjson" -d '
{ "index" : { "table" : "posts:weekly_table" } }
{ "title" : "Yellow Bag", "price": 12 }
{ "create" : { "table" : "posts:weekly_table" } }
{ "title" : "Red Bag", "price": 12.5, "id": 3 }
'- SQL
- JSON
- Elasticsearch
- PHP
- Python
- Python-asyncio
- Javascript
- Java
- C#
- Rust
INSERT INTO products(title, sizes) VALUES('shoes', (40,41,42,43));POST /insert
{
"table":"products",
"id":1,
"doc":
{
"title" : "shoes",
"sizes" : [40, 41, 42, 43]
}
}POST /products/_create/1
{
"title": "shoes",
"sizes" : [40, 41, 42, 43]
}Or, alternatively
POST /products/_doc/
{
"title": "shoes",
"sizes" : [40, 41, 42, 43]
}$index->addDocument(
['title' => 'shoes', 'sizes' => [40,41,42,43]],
1
);indexApi.insert({"table" : "products", "id" : 0, "doc" : {"title" : "Yellow bag","sizes":[40,41,42,43]}})await indexApi.insert({"table" : "products", "id" : 0, "doc" : {"title" : "Yellow bag","sizes":[40,41,42,43]}})res = await indexApi.insert({"table" : "products", "id" : 0, "doc" : {"title" : "Yellow bag","sizes":[40,41,42,43]}});newdoc = new InsertDocumentRequest();
HashMap<String,Object> doc = new HashMap<String,Object>(){{
put("title","Yellow bag");
put("sizes",new int[]{40,41,42,43});
}};
newdoc.index("products").id(0L).setDoc(doc);
sqlresult = indexApi.insert(newdoc);Dictionary<string, Object> doc = new Dictionary<string, Object>();
doc.Add("title", "Yellow bag");
doc.Add("sizes", new List<Object> {40,41,42,43});
InsertDocumentRequest newdoc = new InsertDocumentRequest(index: "products", id: 0, doc: doc);
var sqlresult = indexApi.Insert(newdoc);let mut doc = HashMap::new();
doc.insert("title".to_string(), serde_json::json!("Yellow bag"));
doc.insert("sizes".to_string(), serde_json::json!([40,41,42,43]));
let insert_req = InsertDocumentRequest::new("products".to_string(), serde_json::json!(doc));
let insert_res = index_api.insert(insert_req).await;JSON value can be inserted as an escaped string (via SQL or JSON) or as a JSON object (via the JSON interface).
- SQL
- JSON
- Elasticsearch
- PHP
- Python
- Python-asyncio
- Javascript
- Java
- C#
- Rust
INSERT INTO products VALUES (1, 'shoes', '{"size": 41, "color": "red"}');JSON value can be inserted as a JSON object
POST /insert
{
"table":"products",
"id":1,
"doc":
{
"title" : "shoes",
"meta" : {
"size": 41,
"color": "red"
}
}
}JSON value can be also inserted as a string containing escaped JSON:
POST /insert
{
"table":"products",
"id":1,
"doc":
{
"title" : "shoes",
"meta" : "{\"size\": 41, \"color\": \"red\"}"
}
}POST /products/_create/1
{
"title": "shoes",
"meta" : {
"size": 41,
"color": "red"
}
}Or, alternatively
POST /products/_doc/
{
"title": "shoes",
"meta" : {
"size": 41,
"color": "red"
}
}$index->addDocument(
['title' => 'shoes', 'meta' => '{"size": 41, "color": "red"}'],
1
);indexApi = api = manticoresearch.IndexApi(client)
indexApi.insert({"table" : "products", "id" : 0, "doc" : {"title" : "Yellow bag","meta":'{"size": 41, "color": "red"}'}})indexApi = api = manticoresearch.IndexApi(client)
await indexApi.insert({"table" : "products", "id" : 0, "doc" : {"title" : "Yellow bag","meta":'{"size": 41, "color": "red"}'}})res = await indexApi.insert({"table" : "products", "id" : 0, "doc" : {"title" : "Yellow bag","meta":'{"size": 41, "color": "red"}'}});newdoc = new InsertDocumentRequest();
HashMap<String,Object> doc = new HashMap<String,Object>(){{
put("title","Yellow bag");
put("meta",
new HashMap<String,Object>(){{
put("size",41);
put("color","red");
}});
}};
newdoc.index("products").id(0L).setDoc(doc);
sqlresult = indexApi.insert(newdoc);Dictionary<string, Object> meta = new Dictionary<string, Object>();
meta.Add("size", 41);
meta.Add("color", "red");
Dictionary<string, Object> doc = new Dictionary<string, Object>();
doc.Add("title", "Yellow bag");
doc.Add("meta", meta);
InsertDocumentRequest newdoc = new InsertDocumentRequest(index: "products", id: 0, doc: doc);
var sqlresult = indexApi.Insert(newdoc);let mut meta = HashMap::new();
metadoc.insert("size".to_string(), serde_json::json!(41));
meta.insert("color".to_string(), serde_json::json!("red"));
let mut doc = HashMap::new();
doc.insert("title".to_string(), serde_json::json!("Yellow bag"));
doc.insert("meta".to_string(), serde_json::json!(meta));
let insert_req = InsertDocumentRequest::new("products".to_string(), serde_json::json!(doc));
let insert_res = index_api.insert(insert_req).await;In a percolate table documents that are percolate query rules are stored and must follow the exact schema of four fields:
| field | type | description |
|---|---|---|
| id | bigint | PQ rule identifier (if omitted, it will be assigned automatically) |
| query | string | Full-text query (can be empty) compatible with the percolate table |
| filters | string | Additional filters by non-full-text fields (can be empty) compatible with the percolate table |
| tags | string | A string with one or many comma-separated tags, which may be used to selectively show/delete saved queries |
Any other field names are not supported and will trigger an error.
Warning: Inserting/replacing JSON-formatted PQ rules via SQL will not work. In other words, the JSON-specific operators (match etc) will be considered just parts of the rule's text that should match with documents. If you prefer JSON syntax, use the HTTP endpoint instead of INSERT/REPLACE.
- SQL
- JSON
- PHP
- Python
- Python-asyncio
- Javascript
- Java
- C#
- Rust
INSERT INTO pq(id, query, filters) VALUES (1, '@title shoes', 'price > 5');
INSERT INTO pq(id, query, tags) VALUES (2, '@title bag', 'Louis Vuitton');
SELECT * FROM pq;There are two ways you can add a percolate query into a percolate table:
-
Query in JSON /search compatible format, described at json/search
PUT /pq/pq_table/doc/1 { "query": { "match": { "title": "shoes" }, "range": { "price": { "gt": 5 } } }, "tags": ["Loius Vuitton"] } -
Query in SQL format, described at search query syntax
PUT /pq/pq_table/doc/2 { "query": { "ql": "@title shoes" }, "filters": "price > 5", "tags": ["Loius Vuitton"] }
$newstoredquery = [
'table' => 'test_pq',
'body' => [
'query' => [
'match' => [
'title' => 'shoes'
]
],
'range' => [
'price' => [
'gt' => 5
]
]
],
'tags' => ['Loius Vuitton']
];
$client->pq()->doc($newstoredquery);newstoredquery ={"table" : "test_pq", "id" : 2, "doc" : {"query": {"ql": "@title shoes"},"filters": "price > 5","tags": ["Loius Vuitton"]}}
indexApi.insert(newstoredquery)newstoredquery ={"table" : "test_pq", "id" : 2, "doc" : {"query": {"ql": "@title shoes"},"filters": "price > 5","tags": ["Loius Vuitton"]}}
await indexApi.insert(newstoredquery)newstoredquery ={"table" : "test_pq", "id" : 2, "doc" : {"query": {"ql": "@title shoes"},"filters": "price > 5","tags": ["Loius Vuitton"]}};
indexApi.insert(newstoredquery);newstoredquery = new HashMap<String,Object>(){{
put("query",new HashMap<String,Object >(){{
put("q1","@title shoes");
put("filters","price>5");
put("tags",new String[] {"Loius Vuitton"});
}});
}};
newdoc.index("test_pq").id(2L).setDoc(doc);
indexApi.insert(newdoc);Dictionary<string, Object> query = new Dictionary<string, Object>();
query.Add("q1", "@title shoes");
query.Add("filters", "price>5");
query.Add("tags", new List<string> {"Loius Vuitton"});
Dictionary<string, Object> newstoredquery = new Dictionary<string, Object>();
newstoredquery.Add("query", query);
InsertDocumentRequest newdoc = new InsertDocumentRequest(index: "test_pq", id: 2, doc: doc);
indexApi.Insert(newdoc);let mut pq_doc = HashMap::new();
pq_doc.insert("q1".to_string(), serde_json::json!("@title shoes"));
pq_doc.insert("filters".to_string(), serde_json::json!("price>5"));
pq_doc.insert("tags".to_string(), serde_json::json!(["Louis Vitton"]));
let mut doc = HashMap::new();
pq_doc.insert("query".to_string(), serde_json::json!(pq_doc));
let insert_req = InsertDocumentRequest::new("test_pq".to_string(), serde_json::json!(doc));
let insert_res = index_api.insert(insert_req).await;+------+--------------+---------------+---------+
| id | query | tags | filters |
+------+--------------+---------------+---------+
| 1 | @title shoes | | price>5 |
| 2 | @title bag | Louis Vuitton | |
+------+--------------+---------------+---------+If you don't specify an ID, it will be assigned automatically. You can read more about auto-ID here.
- SQL
- JSON
- PHP
- Python
- Python-asyncio
- Javascript
- Java
- C#
- Rust
INSERT INTO pq(query, filters) VALUES ('wristband', 'price > 5');
SELECT * FROM pq;PUT /pq/pq_table/doc
{
"query": {
"match": {
"title": "shoes"
},
"range": {
"price": {
"gt": 5
}
}
},
"tags": ["Loius Vuitton"]
}
PUT /pq/pq_table/doc
{
"query": {
"ql": "@title shoes"
},
"filters": "price > 5",
"tags": ["Loius Vuitton"]
}$newstoredquery = [
'table' => 'pq_table',
'body' => [
'query' => [
'match' => [
'title' => 'shoes'
]
],
'range' => [
'price' => [
'gt' => 5
]
]
],
'tags' => ['Loius Vuitton']
];
$client->pq()->doc($newstoredquery);indexApi = api = manticoresearch.IndexApi(client)
newstoredquery ={"table" : "test_pq", "doc" : {"query": {"ql": "@title shoes"},"filters": "price > 5","tags": ["Loius Vuitton"]}}
indexApi.insert(store_query)indexApi = api = manticoresearch.IndexApi(client)
newstoredquery ={"table" : "test_pq", "doc" : {"query": {"ql": "@title shoes"},"filters": "price > 5","tags": ["Loius Vuitton"]}}
await indexApi.insert(store_query)newstoredquery ={"table" : "test_pq", "doc" : {"query": {"ql": "@title shoes"},"filters": "price > 5","tags": ["Loius Vuitton"]}};
res = await indexApi.insert(store_query);newstoredquery = new HashMap<String,Object>(){{
put("query",new HashMap<String,Object >(){{
put("q1","@title shoes");
put("filters","price>5");
put("tags",new String[] {"Loius Vuitton"});
}});
}};
newdoc.index("test_pq").setDoc(doc);
indexApi.insert(newdoc);Dictionary<string, Object> query = new Dictionary<string, Object>();
query.Add("q1", "@title shoes");
query.Add("filters", "price>5");
query.Add("tags", new List<string> {"Loius Vuitton"});
Dictionary<string, Object> newstoredquery = new Dictionary<string, Object>();
newstoredquery.Add("query", query);
InsertDocumentRequest newdoc = new InsertDocumentRequest(index: "test_pq", doc: doc);
indexApi.Insert(newdoc);let mut pq_doc = HashMap::new();
pq_doc.insert("q1".to_string(), serde_json::json!("@title shoes"));
pq_doc.insert("filters".to_string(), serde_json::json!("price>5"));
pq_doc.insert("tags".to_string(), serde_json::json!(["Louis Vitton"]));
let mut doc = HashMap::new();
pq_doc.insert("query".to_string(), serde_json::json!(pq_doc));
let insert_req = InsertDocumentRequest::new("test_pq".to_string(), serde_json::json!(doc));
let insert_res = index_api.insert(insert_req).await;+---------------------+-----------+------+---------+
| id | query | tags | filters |
+---------------------+-----------+------+---------+
| 1657843905795719192 | wristband | | price>5 |
+---------------------+-----------+------+---------+{
"table": "pq_table",
"type": "doc",
"_id": 1657843905795719196,
"result": "created"
}
{
"table": "pq_table",
"type": "doc",
"_id": 1657843905795719198,
"result": "created"
}Array(
[index] => pq_table
[type] => doc
[_id] => 1657843905795719198
[result] => created
){'created': True,
'found': None,
'id': 1657843905795719198,
'table': 'test_pq',
'result': 'created'}{'created': True,
'found': None,
'id': 1657843905795719198,
'table': 'test_pq',
'result': 'created'}{"table":"test_pq","_id":1657843905795719198,"created":true,"result":"created"}In case of omitted schema in SQL INSERT command, the following parameters are expected:
- ID. You can use
0as the ID to trigger auto-ID generation. - Query - Full-text query.
- Tags - PQ rule tags string.
- Filters - Additional filters by attributes.
- SQL
- JSON
INSERT INTO pq VALUES (0, '@title shoes', '', '');
INSERT INTO pq VALUES (0, '@title shoes', 'Louis Vuitton', '');
SELECT * FROM pq;POST /sql?mode=raw -d "INSERT INTO pq VALUES (0, '@title shoes', '', '')"
POST /sql?mode=raw -d "INSERT INTO pq VALUES (0, '@title shoes', 'Louis Vuitton', '')"
POST /sql -d "SELECT * FROM pq"+---------------------+--------------+---------------+---------+
| id | query | tags | filters |
+---------------------+--------------+---------------+---------+
| 2810855531667783688 | @title shoes | | |
| 2810855531667783689 | @title shoes | Louis Vuitton | |
+---------------------+--------------+---------------+---------+[
{
"total": 1,
"error": "",
"warning": ""
}
]
[
{
"total": 1,
"error": "",
"warning": ""
}
]
{
"took": 0,
"timed_out": false,
"hits": {
"total": 2,
"total_relation": "eq",
"hits": [
{
"id": 724024784404348900,
"_score": 1,
"_source": {
"query": "@title shoes",
"tags": "Louis Vuitton",
"filters": ""
}
},
{
"id": 724024784404348900,
"_score": 1,
"_source": {
"query": "@title shoes",
"tags": "",
"filters": ""
}
}
]
}
}To replace an existing PQ rule with a new one in SQL, just use a regular REPLACE command. There's a special syntax ?refresh=1 to replace a PQ rule defined in JSON mode via the HTTP JSON interface.
- SQL
- JSON
mysql> select * from pq;
+---------------------+--------------+------+---------+
| id | query | tags | filters |
+---------------------+--------------+------+---------+
| 2810823411335430148 | @title shoes | | |
+---------------------+--------------+------+---------+
1 row in set (0.00 sec)
mysql> replace into pq(id,query) values(2810823411335430148,'@title boots');
Query OK, 1 row affected (0.00 sec)
mysql> select * from pq;
+---------------------+--------------+------+---------+
| id | query | tags | filters |
+---------------------+--------------+------+---------+
| 2810823411335430148 | @title boots | | |
+---------------------+--------------+------+---------+
1 row in set (0.00 sec)GET /pq/pq/doc/2810823411335430149
{
"took": 0,
"timed_out": false,
"hits": {
"total": 1,
"hits": [
{
"_id": 2810823411335430149,
"_score": 1,
"_source": {
"query": {
"match": {
"title": "shoes"
}
},
"tags": "",
"filters": ""
}
}
]
}
}
PUT /pq/pq/doc/2810823411335430149?refresh=1 -d '{
"query": {
"match": {
"title": "boots"
}
}
}'
GET /pq/pq/doc/2810823411335430149
{
"took": 0,
"timed_out": false,
"hits": {
"total": 1,
"hits": [
{
"_id": 2810823411335430149,
"_score": 1,
"_source": {
"query": {
"match": {
"title": "boots"
}
},
"tags": "",
"filters": ""
}
}
]
}
}You can ingest data into Manticore from external storages using various methods:
- Indexer tool to fetch data from various databases into plain tables.
- Logstash, Filebeat, and Vector.dev integrations to put data to Manticore real-time tables from these tools.
- Kafka integration to synchronize data from Kafka topics into a real-time table.
Plain tables are tables that are created one-time by fetching data at creation from one or several sources. A plain table is immutable as documents cannot be added or deleted during its lifespan. It is only possible to update values of numeric attributes (including MVA). Refreshing the data is only possible by recreating the whole table.
Plain tables are available only in the Plain mode and their definition is made up of a table declaration and one or several source declarations. The data gathering and table creation are not made by the searchd server but by the auxiliary tool indexer.
Indexer is a command-line tool that can be called directly from the command line or from shell scripts.
It can accept a number of arguments when called, but there are also several settings of its own in the Manticore configuration file.
In the typical scenario, indexer does the following:
- Fetches the data from the source
- Builds the plain table
- Writes the table files
- (Optional) Informs the search server about the new table which triggers table rotation
The indexer tool is used to create plain tables in Manticore Search. It has a general syntax of:
indexer [OPTIONS] [table_name1 [table_name2 [...]]]
When creating tables with indexer, the generated table files must be made with permissions that allow searchd to read, write, and delete them. In case of the official Linux packages, searchd runs under the manticore user. Therefore, indexer must also run under the manticore user:
sudo -u manticore indexer ...
If you are running searchd differently, you might need to omit sudo -u manticore. Just make sure that the user under which your searchd instance is running has read/write permissions to the tables generated using indexer.
To create a plain table, you need to list the table(s) you want to process. For example, if your manticore.conf file contains details on two tables, mybigindex and mysmallindex, you could run:
sudo -u manticore indexer mysmallindex mybigindex
You can also use wildcard tokens to match table names:
?matches any single character*matches any count of any characters%matches none or any single character
sudo -u manticore indexer indexpart*main --rotate
The exit codes for indexer are as follows:
- 0: everything went OK
- 1: there was a problem while indexing (and if
--rotatewas specified, it was skipped) or an operation emitted a warning - 2: indexing went OK, but the
--rotateattempt failed
You can also start indexer using the following systemctl unit file:
systemctl start --no-block manticore-indexer
Or, in case you want to build a specific table:
systemctl start --no-block manticore-indexer@specific-table-name
Use the systemctl set-environment INDEXER_CONFIG command to run the Indexer with a custom configuration, which replaces the default settings.
The systemctl set-environment INDEXER_ARGS command lets you add custom startup options for the Indexer. For a complete list of command-line options, see here.
For instance, to start the Indexer in quiet mode, run:
systemctl set-environment INDEXER_ARGS='--quiet'
systemctl restart manticore-indexer
To revert the changes, run:
systemctl set-environment INDEXER_ARGS=''
systemctl restart manticore-indexer
-
--config <file>(-c <file>for short) tellsindexerto use the given file as its configuration. Normally, it will look formanticore.confin the installation directory (e.g./etc/manticoresearch/manticore.conf), followed by the current directory you are in when callingindexerfrom the shell. This is most useful in shared environments where the binary files are installed in a global folder, e.g./usr/bin/, but you want to provide users with the ability to make their own custom Manticore set-ups, or if you want to run multiple instances on a single server. In cases like those you could allow them to create their ownmanticore.conffiles and pass them toindexerwith this option. For example:sudo -u manticore indexer --config /home/myuser/manticore.conf mytable -
--alltellsindexerto update every table listed inmanticore.confinstead of listing individual tables. This would be useful in small configurations or cron-kind or maintenance jobs where the entire table set will get rebuilt each day or week or whatever period is best. Please note that since--alltries to update all found tables in the configuration, it will issue a warning if it encounters RealTime tables and the exit code of the command will be1not0even if the plain tables finished without issue. Example usage:sudo -u manticore indexer --config /home/myuser/manticore.conf --all -
--rotateis used for rotating tables. Unless you have the situation where you can take the search function offline without troubling users you will almost certainly need to keep search running whilst indexing new documents.--rotatecreates a second table, parallel to the first (in the same place, simply including.newin the filenames). Once complete,indexernotifiessearchdvia sending theSIGHUPsignal, and thesearchdwill attempt to rename the tables (renaming the existing ones to include.oldand renaming the.newto replace them), and then will start serving from the newer files. Depending on the setting of seamless_rotate there may be a slight delay in being able to search the newer tables. In case multiple tables are rotated at once which are chained by killlist_target relations rotation will start with the tables that are not targets and finish with the ones at the end of target chain. Example usage:sudo -u manticore indexer --rotate --all -
--quiettellsindexerot to output anything, unless there is an error. This is mostly used for cron-type or other scripted jobs where the output is irrelevant or unnecessary, except in the event of some kind of error. Example usage:sudo -u manticore indexer --rotate --all --quiet -
--noprogressdoes not display progress details as they occur. Instead, the final status details (such as documents indexed, speed of indexing and so on are only reported at completion of indexing. In instances where the script is not being run on a console (or 'tty'), this will be on by default. Example usage:sudo -u manticore indexer --rotate --all --noprogress -
--remove_dupslogically removes repeated numeric document IDs while building a plain table. The first row indexed for each ID remains visible; later rows with the same ID are marked as dead. Without this option,indexerkeeps duplicate IDs. Example usage:sudo -u manticore indexer --config /home/myuser/manticore.conf --remove_dups mytable -
--buildstops <outputfile.text> <N>reviews the table source, as if it were indexing the data, and produces a list of the terms that are being indexed. In other words, it produces a list of all the searchable terms that are becoming part of the table. Note, it does not update the table in question, it simply processes the data as if it were indexing, including running queries defined with sql_query_pre or sql_query_post.outputfile.txtwill contain the list of words, one per line, sorted by frequency with most frequent first, andNspecifies the maximum number of words that will be listed. If it's sufficiently large to encompass every word in the table, only that many words will be returned. Such a dictionary list could be used for client application features around "Did you mean…" functionality, usually in conjunction with--buildfreqs, below. Example:sudo -u manticore indexer mytable --buildstops word_freq.txt 1000This would produce a document in the current directory,
word_freq.txt, with the 1,000 most common words in 'mytable', ordered by most common first. Note that the file will pertain to the last table indexed when specified with multiple tables or--all(i.e. the last one listed in the configuration file) -
--buildfreqsworks with--buildstops(and is ignored if--buildstopsis not specified). As--buildstopsprovides the list of words used within the table,--buildfreqsadds the quantity present in the table, which would be useful in establishing whether certain words should be considered stopwords if they are too prevalent. It will also help with developing "Did you mean…" features where you need to know how much more common a given word compared to another, similar one. For example:sudo -u manticore indexer mytable --buildstops word_freq.txt 1000 --buildfreqsThis would produce the
word_freq.txtas above, however after each word would be the number of times it occurred in the table in question. -
--merge <dst-table> <src-table>is used for physically merging tables together, for example if you have a main+delta scheme, where the main table rarely changes, but the delta table is rebuilt frequently, and--mergewould be used to combine the two. The operation moves from right to left - the contents ofsrc-tableget examined and physically combined with the contents ofdst-tableand the result is left indst-table. In pseudo-code, it might be expressed as:dst-table += src-tableAn example:sudo -u manticore indexer --merge main delta --rotateIn the above example, where the main is the master, rarely modified table, and the delta is more frequently modified one, you might use the above to call
indexerto combine the contents of the delta into the main table and rotate the tables. -
--merge-dst-range <attr> <min> <max>runs the filter range given upon merging. Specifically, as the merge is applied to the destination table (as part of--merge, and is ignored if--mergeis not specified),indexerwill also filter the documents ending up in the destination table, and only documents will pass through the filter given will end up in the final table. This could be used for example, in a table where there is a 'deleted' attribute, where 0 means 'not deleted'. Such a table could be merged with:sudo -u manticore indexer --merge main delta --merge-dst-range deleted 0 0Any documents marked as deleted (value 1) will be removed from the newly-merged destination table. It can be added several times to the command line, to add successive filters to the merge, all of which must be met in order for a document to become part of the final table.
-
--
merge-killlists(and its shorter alias--merge-klists) changes the way kill lists are processed when merging tables. By default, both kill lists get discarded after a merge. That supports the most typical main+delta merge scenario. With this option enabled, however, kill lists from both tables get concatenated and stored into the destination table. Note that a source (delta) table kill list will be used to suppress rows from a destination (main) table at all times. -
--keep-attrsallows to reuse existing attributes on reindexing. Whenever the table is rebuilt, each new document id is checked for presence in the "old" table, and if it already exists, its attributes are transferred to the "new" table; if not found, attributes from the new table are used. If the user has updated attributes in the table, but not in the actual source used for the table, all updates will be lost when reindexing; using--keep-attrsenables saving the updated attribute values from the previous table. It is possible to specify a path for table files to be used instead of the reference path from the config:sudo -u manticore indexer mytable --keep-attrs=/path/to/index/files -
--keep-attrs-names=<attributes list>allows you to specify attributes to reuse from an existing table on reindexing. By default, all attributes from the existing table are reused in the new table:sudo -u manticore indexer mytable --keep-attrs=/path/to/table/files --keep-attrs-names=update,state -
--dump-rows <FILE>dumps rows fetched by SQL source(s) into the specified file, in a MySQL compatible syntax. The resulting dumps are the exact representation of data as received byindexerand can help repeat indexing-time issues. The command performs fetching from the source and creates both table files and the dump file. -
--print-rt <rt_index> <table>outputs fetched data from the source as INSERTs for a real-time table. The first lines of the dump will contain the real-time fields and attributes (as a reflection of the plain table fields and attributes). The command performs fetching from the source and creates both table files and the dump output. The command can be used assudo -u manticore indexer -c manticore.conf --print-rt indexrt indexplain > dump.sql. Only SQL-based sources are supported. MVAs are not supported. -
--sighup-eachis useful when you are rebuilding many big tables and want each one rotated intosearchdas soon as possible. With--sighup-each,indexerwill send the SIGHUP signal to searchd after successfully completing work on each table. (The default behavior is to send a single SIGHUP after all the tables are built). -
--nohupis useful when you want to check your table with indextool before actually rotating it. indexer won't send the SIGHUP if this option is on. Table files are renamed to .tmp. Use indextool to rename table files to .new and rotate it. Example usage:sudo -u manticore indexer --rotate --nohup mytable sudo -u manticore indextool --rotate --check mytable -
--print-queriesprints out SQL queries thatindexersends to the database, along with SQL connection and disconnection events. That is useful to diagnose and fix problems with SQL sources. -
--help(-hfor short) lists all the parameters that can be called inindexer. -
-vshowsindexerversion.
You can also configure indexer behavior in the Manticore configuration file in the indexer section. searchd passes mem_limit, write_buffer, max_iops, max_iosize and lemmatizer_cache of this section to the indexer processes of a bulk import as well:
indexer {
...
}
lemmatizer_cache = 256M
Lemmatizer cache size. Optional, default is 256K.
Our lemmatizer implementation uses a compressed dictionary format that enables a space/speed tradeoff. It can either perform lemmatization off the compressed data, using more CPU but less RAM, or it can decompress and precache the dictionary either partially or fully, thus using less CPU but more RAM. The lemmatizer_cache directive lets you control how much RAM exactly can be spent for that uncompressed dictionary cache.
Currently, the only available dictionaries are ru.pak, en.pak, and de.pak. These are the Russian, English, and German dictionaries. The compressed dictionary is approximately 2 to 10 MB in size. Note that the dictionary stays in memory at all times too. The default cache size is 256 KB. The accepted cache sizes are 0 to 2047 MB. It's safe to raise the cache size too high; the lemmatizer will only use the needed memory. For example, the entire Russian dictionary decompresses to approximately 110 MB; thus settinglemmatizer_cache higher than that will not affect the memory use. Even when 1024 MB is allowed for the cache, if only 110 MB is needed, it will only use those 110 MB.
max_file_field_buffer = 128M
Maximum file field adaptive buffer size in bytes. Optional, default is 8MB, minimum is 1MB.
The file field buffer is used to load files referred to from sql_file_field columns. This buffer is adaptive, starting at 1 MB at first allocation, and growing in 2x steps until either the file contents can be loaded or the maximum buffer size, specified by the max_file_field_buffer directive, is reached.
Thus, if no file fields are specified, no buffer is allocated at all. If all files loaded during indexing are under (for example) 2 MB in size, but the max_file_field_buffer value is 128 MB, the peak buffer usage would still be only 2 MB. However, files over 128 MB would be entirely skipped.
max_iops = 40
Maximum I/O operations per second, for I/O throttling. Optional, default is 0 (unlimited).
I/O throttling related option. It limits the maximum count of I/O operations (reads or writes) per any given second. A value of 0 means that no limit is imposed.
indexer can cause bursts of intensive disk I/O during building a table, and it might be desirable to limit its disk activity (and reserve something for other programs running on the same machine, such as searchd). I/O throttling helps to do that. It works by enforcing a minimum guaranteed delay between subsequent disk I/O operations performed by indexer. Throttling I/O can help reduce search performance degradation caused by building. This setting is not effective for other kinds of data ingestion, e.g. inserting data into a real-time table, except a bulk import, which builds its chunks with indexer.
max_iosize = 1048576
Maximum allowed I/O operation size, in bytes, for I/O throttling. Optional, default is 0 (unlimited).
I/O throttling related option. It limits the maximum file I/O operation (read or write) size for all operations performed by indexer. A value of 0 means that no limit is imposed. Reads or writes that are bigger than the limit will be split into several smaller operations, and counted as several operations by the max_iops setting. At the time of this writing, all I/O calls should be under 256 KB (default internal buffer size) anyway, so max_iosize values higher than 256 KB should not have any effect.
max_xmlpipe2_field = 8M
Maximum allowed field size for XMLpipe2 source type, in bytes. Optional, default is 2 MB.
mem_limit = 256M
# mem_limit = 262144K # same, but in KB
# mem_limit = 268435456 # same, but in bytes
Plain table building RAM usage limit. Optional, default is 128 MB. Enforced memory usage limit that the indexer will not go above. Can be specified in bytes, or kilobytes (using K postfix), or megabytes (using M postfix); see the example. This limit will be automatically raised if set to an extremely low value causing I/O buffers to be less than 8 KB; the exact lower bound for that depends on the built data size. If the buffers are less than 256 KB, a warning will be produced.
The maximum possible limit is 2047M. Too low values can hurt plain table building speed, but 256M to 1024M should be enough for most, if not all datasets. Setting this value too high can cause SQL server timeouts. During the document collection phase, there will be periods when the memory buffer is partially sorted and no communication with the database is performed; and the database server can timeout. You can resolve that either by raising timeouts on the SQL server side or by lowering mem_limit.
on_file_field_error = skip_document
How to handle IO errors in file fields. Optional, default is ignore_field.
When there is a problem indexing a file referenced by a file field (sql_file_field), indexer can either process the document, assuming empty content in this particular field, or skip the document, or fail indexing entirely. on_file_field_error directive controls that behavior. The values it takes are:
ignore_field, process the current document without field;skip_document, skip the current document but continue indexing;fail_index, fail indexing with an error message.
The problems that can arise are: open error, size error (file too big), and data read error. Warning messages on any problem will be given at all times, regardless of the phase and the on_file_field_error setting.
Note that with on_file_field_error = skip_document documents will only be ignored if problems are detected during an early check phase, and not during the actual file parsing phase. indexer will open every referenced file and check its size before doing any work, and then open it again when doing actual parsing work. So in case a file goes away between these two open attempts, the document will still be indexed.
write_buffer = 4M
Write buffer size, bytes. Optional, default is 1MB. Write buffers are used to write both temporary and final table files when indexing. Larger buffers reduce the number of required disk writes. Memory for the buffers is allocated in addition to mem_limit. Note that several (currently up to 4) buffers for different files will be allocated, proportionally increasing the RAM usage.
ignore_non_plain = 1
ignore_non_plain allows you to completely ignore warnings about skipping non-plain tables. The default is 0 (not ignoring).
There are two approaches to scheduling indexer runs. The first way is the classical method of using crontab. The second way is using a systemd timer with a user-defined schedule. To create the timer unit files, you should place them in the appropriate directory where systemd looks for such unit files. On most Linux distributions, this directory is typically /etc/systemd/system. Here's how to do it:
-
Create a timer unit file for your custom schedule:
cat << EOF > /etc/systemd/system/manticore-indexer@.timer [Unit] Description=Run Manticore Search's indexer on schedule [Timer] OnCalendar=minutely RandomizedDelaySec=5m Unit=manticore-indexer@%i.service [Install] WantedBy=timers.target EOFMore on the
OnCalendarsyntax and examples can be found here. -
Edit the timer unit for your specific needs.
-
Enable the timer:
systemctl enable manticore-indexer@idx1.timer -
Start the timer:
systemctl start manticore-indexer@idx1.timer -
Repeat steps 2-4 for any additional timers.