ContextQMD
Back to Manticoresearch

Listing tables

manual/english/Listing_tables.md

25.11.011.3 KB
Original Source

Listing tables

Manticore Search has a single level of hierarchy for tables.

Unlike other DBMS, there is no concept of grouping tables into databases in Manticore. However, for interoperability with SQL dialects, Manticore accepts SHOW DATABASES statements for interoperability with SQL dialect, statements, but the statement does not return any results.

<!-- example listing -->

SHOW TABLES

General syntax:

sql
SHOW TABLES [ LIKE pattern ]

The SHOW TABLESstatement lists all currently active tables along with their types. The existing table types are local, distributed, rt, percolate and template.

<!-- intro -->
SQL:
<!-- request SQL -->
sql
SHOW TABLES;
<!-- response SQL -->
sql
+----------+-------------+
| Index    | Type        |
+----------+-------------+
| dist     | distributed |
| plain    | local       |
| pq       | percolate   |
| rt       | rt          |
| template | template    |
+----------+-------------+
5 rows in set (0.00 sec)
<!-- request JSON -->
JSON
POST /sql?mode=raw -d "SHOW TABLES"
<!-- response JSON -->
JSON
[
  {
    "columns": [
      {
        "Table": {
          "type": "string"
        }
      },
      {
        "Type": {
          "type": "string"
        }
      }
    ],
    "data": [
      {
        "Table": "dist",
        "Type": "distributed"
      },
      {
        "Table": "plain",
        "Type": "local"
      },
      {
        "Table": "pq",
        "Type": "percolate"
      },{
        "Table": "rt",
        "Type": "rt"
      },{
        "Table": "template",
        "Type": "template"
      }
    ],
    "total": 5,
    "error": "",
    "warning": ""
  }
]
<!-- request PHP -->
php
$client->nodes()->table();
<!-- response PHP -->
php
Array
(
    [dist1] => distributed
    [rt] => rt
    [products] => rt
)
<!-- intro -->
Python:
<!-- request Python -->
python
utilsApi.sql('SHOW TABLES')
<!-- response Python -->
python
{u'columns': [{u'Index': {u'type': u'string'}},
              {u'Type': {u'type': u'string'}}],
 u'data': [{u'Index': u'dist1', u'Type': u'distributed'},
           {u'Index': u'rt', u'Type': u'rt'},
           {u'Index': u'products', u'Type': u'rt'}],
 u'error': u'',
 u'total': 0,
 u'warning': u''}
<!-- intro -->
Python-asyncio:
<!-- request Python-asyncio -->
python
await utilsApi.sql('SHOW TABLES')
<!-- response Python-asyncio -->
python
{u'columns': [{u'Index': {u'type': u'string'}},
              {u'Type': {u'type': u'string'}}],
 u'data': [{u'Index': u'dist1', u'Type': u'distributed'},
           {u'Index': u'rt', u'Type': u'rt'},
           {u'Index': u'products', u'Type': u'rt'}],
 u'error': u'',
 u'total': 0,
 u'warning': u''}
<!-- intro -->
Javascript:
<!-- request javascript -->
javascript
res = await utilsApi.sql('SHOW TABLES');
<!-- response javascript -->
javascript
{"columns":[{"Index":{"type":"string"}},{"Type":{"type":"string"}}],"data":[{"Index":"products","Type":"rt"}],"total":0,"error":"","warning":""}
<!-- intro -->
java:
<!-- request Java -->
java
utilsApi.sql("SHOW TABLES", true)
<!-- response Java -->
java
{columns=[{Index={type=string}}, {Type={type=string}}], data=[{Index=products, Type=rt}], total=0, error=, warning=}
<!-- intro -->
C#:
<!-- request C# -->
clike
utilsApi.Sql("SHOW TABLES", true)
<!-- response C# -->
C#
{columns=[{Index={type=string}}, {Type={type=string}}], data=[{Index=products, Type=rt}], total=0, error="", warning=""}
<!-- intro -->
Rust:
<!-- request Rust -->
rust
utils_api.sql("SHOW TABLES", Some(true)).await
<!-- response Rust -->
Rust
{columns=[{Index={type=string}}, {Type={type=string}}], data=[{Index=products, Type=rt}], total=0, error="", warning=""}
<!-- end --> <!-- data for the following examples: CREATE TABLE products type='distributed' local='products' agent='127.0.0.1:9312:products' --> <!-- example Example_2 -->

Optional LIKE clause is supported for filtering tables by name.

<!-- intro -->
SQL:
<!-- request SQL -->
sql
SHOW TABLES LIKE 'pro%';
<!-- response SQL -->
sql
+----------+-------------+
| Index    | Type        |
+----------+-------------+
| products | distributed |
+----------+-------------+
1 row in set (0.00 sec)
<!-- request JSON -->
sql
POST /sql?mode=raw -d "SHOW TABLES LIKE 'pro%';"
<!-- response JSON -->
JSON
[
  {
    "columns": [
      {
        "Table": {
          "type": "string"
        }
      },
      {
        "Type": {
          "type": "string"
        }
      }
    ],
    "data": [
      {
        "Table": "products",
        "Type": "distributed"
      }
    ],
    "total": 1,
    "error": "",
    "warning": ""
  }
]
<!-- request PHP -->
php
$client->nodes()->table(['body'=>['pattern'=>'pro%']]);
<!-- response PHP -->
php
Array
(
    [products] => distributed
)
<!-- intro -->
Python:
<!-- request Python -->
python
utilsApi.sql('SHOW TABLES LIKE \'pro%\'');
<!-- response Python -->
python
{u'columns': [{u'Index': {u'type': u'string'}},
              {u'Type': {u'type': u'string'}}],
 u'data': [{u'Index': u'products', u'Type': u'rt'}],
 u'error': u'',
 u'total': 0,
 u'warning': u''}
<!-- intro -->
Python-asyncio:
<!-- request Python-asyncio -->
python
await utilsApi.sql('SHOW TABLES LIKE \'pro%\'');
<!-- response Python-asyncio -->
python
{u'columns': [{u'Index': {u'type': u'string'}},
              {u'Type': {u'type': u'string'}}],
 u'data': [{u'Index': u'products', u'Type': u'rt'}],
 u'error': u'',
 u'total': 0,
 u'warning': u''}
<!-- intro -->
Javascript:
<!-- request javascript -->
javascript
utilsApi.sql('SHOW TABLES LIKE \'pro%\'')
<!-- response javascript -->
javascript
{"columns":[{"Index":{"type":"string"}},{"Type":{"type":"string"}}],"data":[{"Index":"products","Type":"rt"}],"total":0,"error":"","warning":""}
<!-- intro -->
java:
<!-- request Java -->
java
utilsApi.sql("SHOW TABLES LIKE 'pro%'", true)
<!-- response Java -->
java
{columns=[{Index={type=string}}, {Type={type=string}}], data=[{Index=products, Type=rt}], total=0, error=, warning=}
<!-- intro -->
C#:
<!-- request C# -->
clike
utilsApi.Sql("SHOW TABLES LIKE 'pro%'", true)
<!-- response C# -->
clike
{columns=[{Index={type=string}}, {Type={type=string}}], data=[{Index=products, Type=rt}], total=0, error="", warning=""}
<!-- intro -->
Rust:
<!-- request Rust -->
rust
utils_api.sql("SHOW TABLES LIKE 'pro%'", Some(true)).await
<!-- response Rust -->
rust
{columns=[{Index={type=string}}, {Type={type=string}}], data=[{Index=products, Type=rt}], total=0, error="", warning=""}
<!-- end -->

DESCRIBE

sql
{DESC | DESCRIBE} table_name [ LIKE pattern ]

The DESCRIBE statement lists the table columns and their associated types. The columns are document ID, full-text fields, and attributes. The order matches the order in which fields and attributes are expected by INSERT and REPLACE statements. Column types include field, integer, timestamp, ordinal, bool, float, bigint, string, and mva. ID column will be typed as bigint. Example:

sql
mysql> DESC rt;
+---------+---------+
| Field   | Type    |
+---------+---------+
| id      | bigint  |
| title   | field   |
| content | field   |
| gid     | integer |
+---------+---------+
4 rows in set (0.00 sec)

An optional LIKE clause is supported. Refer to SHOW META for its syntax details.

SELECT FROM name.@table

<!-- data for the following examples: DROP TABLE IF EXISTS tbl; CREATE TABLE tbl(title text indexed stored) charset_table='non_cont,cont' morphology='icu_chinese'; --> <!-- example name_table -->

You can also view the table schema by executing the query select * from <table_name>.@table. The benefit of this method is that you can use the WHERE clause for filtering:

<!-- request SQL -->
sql
select * from tbl.@table where type='text';
<!-- response SQL -->
sql
+------+-------+------+----------------+
| id   | field | type | properties     |
+------+-------+------+----------------+
|    2 | title | text | indexed stored |
+------+-------+------+----------------+
1 row in set (0.00 sec)
<!-- request JSON -->
sql
POST /sql?mode=raw -d "select * from tbl.@table where type='text';"
<!-- response JSON -->
JSON
[{
"columns":[{"id":{"type":"long long"}},{"field":{"type":"string"}},{"type":{"type":"string"}},{"properties":{"type":"string"}}],
"data":[
{"id":2,"field":"title","type":"text","properties":"indexed stored"}
],
"total":1,
"error":"",
"warning":""
}]
<!-- end --> <!-- example name_table2 -->

You can also perform many other actions on <your_table_name>.@table considering it as a regular Manticore table with columns consisting of integer and string attributes.

<!-- request SQL -->
sql
select field from tbl.@table;
select field, properties from tbl.@table where type in ('text', 'uint');
select * from tbl.@table where properties any ('stored');
<!-- end -->

SHOW CREATE TABLE

<!-- example show_create -->
sql
SHOW CREATE TABLE table_name [ OPTION output_words = 'list' | 'file' ]

Prints the CREATE TABLE statement used to create the specified table.

The output_words option allows you to control how external file settings (such as stopwords, exceptions, wordforms, hitless_words) are displayed:

  • 'list' (default): Displays the content of the files as inline lists using the *_list options (e.g. stopwords_list='word1; word2').
  • 'file': Displays the file paths using the original options (e.g. stopwords='/path/to/file').
<!-- intro -->
SQL:
<!-- request SQL -->
sql
SHOW CREATE TABLE tbl\G
<!-- response SQL -->
sql
       Table: tbl
Create Table: CREATE TABLE tbl (
f text indexed stored
) charset_table='non_cont,cont' morphology='icu_chinese'
1 row in set (0.00 sec)
<!-- intro -->
JSON:
<!-- request JSON -->
JSON
POST /sql?mode=raw -d "SHOW CREATE TABLE tbl"
<!-- response JSON -->
JSON
[{
"columns":[{"Table":{"type":"string"}},{"Create Table":{"type":"string"}}],
"data":[
{"Table":"tbl","Create Table":"CREATE TABLE tbl (\nf text)"}
],
"total":1,
"error":"",
"warning":""
}]
<!-- end -->

Percolate table schemas

If you use the DESC statement on a percolate table, it will display the outer table schema, which is the schema of stored queries. This schema is static and the same for all local percolate tables:

sql
mysql> DESC pq;
+---------+--------+
| Field   | Type   |
+---------+--------+
| id      | bigint |
| query   | string |
| tags    | string |
| filters | string |
+---------+--------+
4 rows in set (0.00 sec)

If you want to view the expected document schema, use the following command: DESC <pq table name> table:

sql
mysql> DESC pq TABLE;
+-------+--------+
| Field | Type   |
+-------+--------+
| id    | bigint |
| title | text   |
| gid   | uint   |
+-------+--------+
3 rows in set (0.00 sec)

Also desc pq table like ... is supported and works as follows:

sql
mysql> desc pq table like '%title%';
+-------+------+----------------+
| Field | Type | Properties     |
+-------+------+----------------+
| title | text | indexed stored |
+-------+------+----------------+
1 row in set (0.00 sec)