Table creation

Connect first to Manticore via SQL:

mysql -P9306 -h0

A simple table creation statement looks like:

CREATE TABLE testrt1 (title TEXT,category INT);

This will create a table having the document id (which doesn't need to be declared), a text field and a integer attribute.

Indexes support a range of options that can be passed at the declaration:

For example

CREATE TABLE testrt2 (title TEXT, category INT) html_strip='1' docstore_compression_level = '12';

This table has infixing enabled, text will be stripped of HTML and the compression level for document storage is set at the highest level.

If the type is not specified, by default the table will be a Real-Time. If we want to create a distributed table, we need to pass the type:

CREATE TABLE testrtdist type='distributed' local='testrt1' local='testrt2';

If we are not sure if the table is already made or not, the 'IF NOT EXISTS' clause can be used to not end in error:

CREATE TABLE IF NOT EXISTS testrt1 (title TEXT,category INT);

It is also possible to create a table using the settings and schema of a table that already exists:

CREATE TABLE testrt3 LIKE testrt2;

More table operations

To delete a table simply run:

DROP TABLE testrt3;

To empty a table:

TRUNCATE TABLE testrt2;

To add or drop a column:

ALTER TABLE testrt2 ADD COLUMN tagId INT;

ALTER TABLE testrt2 DROP COLUMN tagId;

Please note that TEXT cannot be added or dropped currently. You will need to re-create the table if you need to modify the TEXT fields.

It is also possible to change table settings:

ALTER TABLE testrt2 ignore_chars='.';

Please note that tokenization settings will not apply to existing data in the full-text component, only new documents added after the ALTER will get affected. If you need to affect the entire collection, the table must be re-created with the desired settings.

Table information

There are several statements that provide information about a table.

To get the table schema:

DESCRIBE testrt2;

Information about number of documents, size and performance metrics can be seen with SHOW:

SHOW TABLE testrt2 STATUS;

SHOW can also provide the list of settings:

SHOW TABLE testrt2 SETTINGS;

If you need to export the table declaration, use SHOW CREATE:

SHOW CREATE TABLE testrt2;

Importing a table

RT tables can be imported using IMPORT TABLE statement. The table files will be copied in the data_dir.

Example:

IMPORT TABLE movies FROM '/index/movies_rt';

SELECT * FROM movies\G