Ever wondered how to create tables in Manticore Search? Whether you’re just getting started or looking to optimize your setup, we’ve got you covered. In this guide, we’ll explore multiple approaches to table creation - from the simplest auto-schema method to more advanced configuration options.
The Magic of Auto-Schema: Zero Configuration Required
What if we told you that you don’t even need to create a table explicitly? Thanks to Manticore’s auto-schema feature, you can start inserting data right away. Let’s see this magic in action:
drop table if exists test;
Query OK, 0 rows affected (0.00 sec)
insert into test (name, in_stock, price, properties) values('nice bag', 117, 1.15, '{"color": "red"}');
Query OK, 1 row affected (0.01 sec)
That’s it! The table is automatically created. Let’s check its structure:
desc test;
+------------+--------+----------------+
| Field | Type | Properties |
+------------+--------+----------------+
| id | bigint | |
| name | text | indexed stored |
| in_stock | uint | |
| price | float | |
| properties | json | |
+------------+--------+----------------+
5 rows in set (0.01 sec)
And our data is already there:
select * from test;
+---------------------+----------+----------+----------+-----------------+
| id | name | in_stock | price | properties |
+---------------------+----------+----------+----------+-----------------+
| 1516257118578016257 | nice bag | 117 | 1.150000 | {"color":"red"} |
+---------------------+----------+----------+----------+-----------------+
1 row in set (0.00 sec)
--- 1 out of 1 results in 1ms ---
Pro Tip: While auto-schema is convenient, it has its trade-offs. The system detects field types based on your first document, which might not always match your intentions. For example, you might want a string attribute, but it could be created as a full-text field instead.
Creating Tables Manually: Taking Control
When you need precise control over your table structure, manual creation is the way to go. Here’s how to do it using SQL:
drop table if exists test;
Query OK, 0 rows affected (0.01 sec)
create table test(name text, in_stock int, price float, properties json);
Query OK, 0 rows affected (0.00 sec)
The HTTP Way: REST API for Table Creation
Prefer working with HTTP? Manticore’s got you covered! You can use the /cli endpoint to manage your tables:
curl "0:9308/cli" -d 'drop table if exists test'
Query OK, 0 rows affected (0.002 sec)
curl "0:9308/cli" -d 'create table test(name text, in_stock int, price float, properties json)'
Query OK, 0 rows affected (0.011 sec)
curl "0:9308/cli" -d 'desc test'
+------------+--------+----------------+
| Field | Type | Properties |
+------------+--------+----------------+
| id | bigint | |
| name | text | indexed stored |
| in_stock | uint | |
| price | float | |
| properties | json | |
+------------+--------+----------------+
5 rows in set (0.001 sec)
Cloning Tables: Work Smarter, Not Harder
Need a table with the same structure as an existing one? The CREATE TABLE LIKE statement is your friend:
drop table if exists test2;
Query OK, 0 rows affected (0.00 sec)
create table test2 like test;
Query OK, 0 rows affected (0.01 sec)
desc test2;
+------------+--------+----------------+
| Field | Type | Properties |
+------------+--------+----------------+
| id | bigint | |
| name | text | indexed stored |
| in_stock | uint | |
| price | float | |
| properties | json | |
+------------+--------+----------------+
5 rows in set (0.00 sec)
Advanced Configuration: Using Config Files
While real-time mode is the recommended default, sometimes you need more control or want to declare things beforehand. That’s where configuration files come in handy. Here’s an example configuration that demonstrates both real-time and plain tables:
searchd {
listen = 9315:mysql41
log = searchd.log
pid_file = searchd.pid
binlog_path =
}
source src {
type = csvpipe
csvpipe_command = echo "1,abc" && echo "3,def"
csvpipe_field = F
}
table plain {
type = plain
source = src
path = /tmp/plain
}
table goods {
type = rt
path = /tmp/rt
rt_field = title
rt_attr_uint = quantity
rt_attr_float = price
}
After starting Manticore with this configuration:
mysql -P9315 -h0 -e "show tables"
+-------+------+
| Table | Type |
+-------+------+
| goods | rt |
+-------+------+
To create and populate the plain table, we need to run the indexer:
indexer -c manticore_test.conf --all --rotate
After reloading the tables:
mysql -P9315 -h0 -e "show tables"
+-------+-------+
| Table | Type |
+-------+-------+
| goods | rt |
| plain | local |
+-------+-------+
mysql -P9315 -v -h0 -e "select * from goods; select * from plain"
--------------
select * from goods
--------------
--------------
select * from plain
--------------
+------+------+
| id | f |
+------+------+
| 1 | abc |
| 3 | def |
+------+------+
Wrapping Up
We’ve covered multiple ways to create tables in Manticore Search:
- Auto-schema for quick starts
- Manual creation for precise control
- HTTP API for REST-based management
- Table cloning for rapid duplication
- Configuration files for advanced scenarios
Each approach has its use cases, and choosing the right one depends on your specific needs. The real-time mode (default) offers simplicity and flexibility for most use cases, while the configuration file approach provides more control and easier deployment across servers.
Reader Challenge: Try creating a table using each method and see which one fits your workflow best!
Prefer watching over reading? Check out our detailed video tutorial on this topic: