elasticsearch/x-pack/plugin/sql/qa/server/src/main/resources/multi-cluster-with-security/multi-cluster-command.csv-spec

// To mute tests follow example in file: example.csv-spec

//
// Commands
//

// Note: remote index discovery (for CCS) won't list aliases.

showCatalogs
// tag::showCatalogs
SHOW CATALOGS;

     name         |     type
------------------+---------------
javaRestTest         |local
my_remote_cluster |remote

// end::showCatalogs
;

showTables
SHOW TABLES CATALOG 'my_remote_cluster';

     catalog     |     name          |     type      |     kind
-----------------+-------------------+---------------+---------------
my_remote_cluster|empty_mapping      |TABLE          |INDEX
my_remote_cluster|logs               |TABLE          |INDEX
my_remote_cluster|logs_nanos         |TABLE          |INDEX
my_remote_cluster|logs_unsigned_long |TABLE          |INDEX
my_remote_cluster|test_emp           |TABLE          |INDEX
my_remote_cluster|test_emp_copy      |TABLE          |INDEX
;

showTablesWithLike
SHOW TABLES CATALOG LIKE 'my_remote_%';

     catalog     |     name          |     type      |     kind
-----------------+-------------------+---------------+---------------
my_remote_cluster|empty_mapping      |TABLE          |INDEX
my_remote_cluster|logs               |TABLE          |INDEX
my_remote_cluster|logs_nanos         |TABLE          |INDEX
my_remote_cluster|logs_unsigned_long |TABLE          |INDEX
my_remote_cluster|test_emp           |TABLE          |INDEX
my_remote_cluster|test_emp_copy      |TABLE          |INDEX
;

showTablesWithFrozen
SHOW TABLES CATALOG 'my_remote_cluster' INCLUDE FROZEN;

     catalog     |     name          |     type      |     kind
-----------------+-------------------+---------------+---------------
my_remote_cluster|empty_mapping      |TABLE          |INDEX
my_remote_cluster|frozen_emp         |TABLE          |INDEX
my_remote_cluster|logs               |TABLE          |INDEX
my_remote_cluster|logs_nanos         |TABLE          |INDEX
my_remote_cluster|logs_unsigned_long |TABLE          |INDEX
my_remote_cluster|test_emp           |TABLE          |INDEX
my_remote_cluster|test_emp_copy      |TABLE          |INDEX
;

showTablesSimpleLike
SHOW TABLES CATALOG 'my_remote_cluster' LIKE 'test_emp';

     catalog     |     name      |     type      |     kind
-----------------+---------------+---------------+---------------
my_remote_cluster|test_emp       |TABLE          |INDEX
;

showTablesCatalogPatternMultiLike
// tag::showTablesCatalogPatternMultiLike
SHOW TABLES CATALOG 'my_*' LIKE 'test_emp%';

     catalog     |     name      |     type      |     kind
-----------------+---------------+---------------+---------------
my_remote_cluster|test_emp       |TABLE          |INDEX
my_remote_cluster|test_emp_copy  |TABLE          |INDEX

// end::showTablesCatalogPatternMultiLike
;

showTablesList
SHOW TABLES CATALOG 'my_*' "test_emp,test_emp_copy";

     catalog     |     name      |     type      |     kind
-----------------+---------------+---------------+---------------
my_remote_cluster|test_emp       |TABLE          |INDEX
my_remote_cluster|test_emp_copy  |TABLE          |INDEX

;

showTablesIdentifier
SHOW TABLES CATALOG 'my_remote_cluster' "test_emp";

     catalog     |     name      |     type      |     kind
-----------------+---------------+---------------+---------------
my_remote_cluster|test_emp       |TABLE          |INDEX
;

showTablesIdentifierPattern
SHOW TABLES CATALOG 'my*' "test_e*,-test_emp";

     catalog     |     name      |     type      |     kind
-----------------+---------------+---------------+---------------
my_remote_cluster|test_emp_copy  |TABLE          |INDEX

;

// Aliases not supported with remote clusters
showTablesIdentifierPatternOnAliases-Ignore
SHOW TABLES CATALOG 'my*' "test*,-test_emp*";

 name:s        | type:s | kind:s
test_alias     |VIEW    |ALIAS
test_alias_emp |VIEW    |ALIAS
;

showTablesAllCatalogs
SHOW TABLES CATALOG '*';

     catalog     |     name          |     type      |     kind
-----------------+-------------------+---------------+---------------
my_remote_cluster|empty_mapping      |TABLE          |INDEX
my_remote_cluster|logs               |TABLE          |INDEX
my_remote_cluster|logs_nanos         |TABLE          |INDEX
my_remote_cluster|logs_unsigned_long |TABLE          |INDEX
my_remote_cluster|test_emp           |TABLE          |INDEX
my_remote_cluster|test_emp_copy      |TABLE          |INDEX
;

// DESCRIBE

describeSimpleLike
DESCRIBE CATALOG 'my_*' LIKE 'test_emp';

       column       |     type      |    mapping
--------------------+---------------+---------------
birth_date          |TIMESTAMP      |datetime
dep                 |STRUCT         |nested
dep.dep_id          |VARCHAR        |keyword
dep.dep_name        |VARCHAR        |text
dep.dep_name.keyword|VARCHAR        |keyword
dep.from_date       |TIMESTAMP      |datetime
dep.to_date         |TIMESTAMP      |datetime
emp_no              |INTEGER        |integer
extra               |STRUCT         |object
extra.info          |STRUCT         |object
extra.info.gender   |VARCHAR        |keyword
extra_gender        |VARCHAR        |keyword
extra_no            |INTEGER        |integer
first_name          |VARCHAR        |text
first_name.keyword  |VARCHAR        |keyword
gender              |VARCHAR        |keyword
hire_date           |TIMESTAMP      |datetime
languages           |TINYINT        |byte
last_name           |VARCHAR        |text
last_name.keyword   |VARCHAR        |keyword
name                |VARCHAR        |keyword
null_constant       |VARCHAR        |keyword
salary              |INTEGER        |integer
salary_ul           |NUMERIC        |unsigned_long
wildcard_name       |VARCHAR        |keyword
;

describeMultiLike
DESCRIBE CATALOG 'my_remote_cluster' LIKE 'test_emp%';

       column       |     type      |    mapping
--------------------+---------------+---------------
birth_date          |TIMESTAMP      |datetime
dep                 |STRUCT         |nested
dep.dep_id          |VARCHAR        |keyword
dep.dep_name        |VARCHAR        |text
dep.dep_name.keyword|VARCHAR        |keyword
dep.from_date       |TIMESTAMP      |datetime
dep.to_date         |TIMESTAMP      |datetime
emp_no              |INTEGER        |integer
extra               |STRUCT         |object
extra.info          |STRUCT         |object
extra.info.gender   |VARCHAR        |keyword
extra_gender        |VARCHAR        |keyword
extra_no            |INTEGER        |integer
first_name          |VARCHAR        |text
first_name.keyword  |VARCHAR        |keyword
gender              |VARCHAR        |keyword
hire_date           |TIMESTAMP      |datetime
languages           |TINYINT        |byte
last_name           |VARCHAR        |text
last_name.keyword   |VARCHAR        |keyword
name                |VARCHAR        |keyword
null_constant       |VARCHAR        |keyword
salary              |INTEGER        |integer
salary_ul           |NUMERIC        |unsigned_long
wildcard_name       |VARCHAR        |keyword
;

describeSimpleIdentifier
DESCRIBE CATALOG 'my_remote_cluster' "test_emp";

        column        |     type      |    mapping
----------------------+---------------+---------------
birth_date            |TIMESTAMP      |datetime
dep                   |STRUCT         |nested
dep.dep_id            |VARCHAR        |keyword
dep.dep_name          |VARCHAR        |text
dep.dep_name.keyword  |VARCHAR        |keyword
dep.from_date         |TIMESTAMP      |datetime
dep.to_date           |TIMESTAMP      |datetime
emp_no                |INTEGER        |integer
first_name            |VARCHAR        |text
first_name.keyword    |VARCHAR        |keyword
gender                |VARCHAR        |keyword
hire_date             |TIMESTAMP      |datetime
languages             |TINYINT        |byte
last_name             |VARCHAR        |text
last_name.keyword     |VARCHAR        |keyword
name                  |VARCHAR        |keyword
salary                |INTEGER        |integer
;

// NB: need to pursue how the resolution is done
// should aliases be included or excluded?
describeIncludeExcludeIdentifier-Ignore
DESCRIBE CATALOG 'my_*' "test_*,-test_alias*";

       column       |     type      |    mapping
--------------------+---------------+---------------
birth_date          |TIMESTAMP      |datetime
dep                 |STRUCT         |nested
dep.dep_id          |VARCHAR        |keyword
dep.dep_name        |VARCHAR        |text
dep.dep_name.keyword|VARCHAR        |keyword
dep.from_date       |TIMESTAMP      |datetime
dep.to_date         |TIMESTAMP      |datetime
emp_no              |INTEGER        |integer
first_name          |VARCHAR        |text
first_name.keyword  |VARCHAR        |keyword
gender              |VARCHAR        |keyword
hire_date           |TIMESTAMP      |datetime
languages           |TINYINT        |byte
last_name           |VARCHAR        |text
last_name.keyword   |VARCHAR        |keyword
salary              |INTEGER        |integer
;

describeNoCols
DESCRIBE CATALOG 'my_remote_cluster' "empty_mapping";

        column:s      |     type:s    |    mapping:s
----------------------+---------------+---------------
;


showColumnsInNoCols
SHOW COLUMNS CATALOG 'my_remote_cluster' IN "empty_mapping";

        column:s      |     type:s    |    mapping:s
----------------------+---------------+---------------
;

showColumnsSimpleIdentifier
SHOW COLUMNS CATALOG 'my_remote_cluster' IN "test_emp";

       column       |     type      |    mapping
--------------------+---------------+---------------
birth_date          |TIMESTAMP      |datetime
dep                 |STRUCT         |nested
dep.dep_id          |VARCHAR        |keyword
dep.dep_name        |VARCHAR        |text
dep.dep_name.keyword|VARCHAR        |keyword
dep.from_date       |TIMESTAMP      |datetime
dep.to_date         |TIMESTAMP      |datetime
emp_no              |INTEGER        |integer
first_name          |VARCHAR        |text
first_name.keyword  |VARCHAR        |keyword
gender              |VARCHAR        |keyword
hire_date           |TIMESTAMP      |datetime
languages           |TINYINT        |byte
last_name           |VARCHAR        |text
last_name.keyword   |VARCHAR        |keyword
name                |VARCHAR        |keyword
salary              |INTEGER        |integer
;

showColumnsIndexPattern
SHOW COLUMNS CATALOG 'my_remote_cluster' IN "test_*";

       column       |     type      |    mapping
--------------------+---------------+---------------
birth_date          |TIMESTAMP      |datetime
dep                 |STRUCT         |nested
dep.dep_id          |VARCHAR        |keyword
dep.dep_name        |VARCHAR        |text
dep.dep_name.keyword|VARCHAR        |keyword
dep.from_date       |TIMESTAMP      |datetime
dep.to_date         |TIMESTAMP      |datetime
emp_no              |INTEGER        |integer
extra               |STRUCT         |object
extra.info          |STRUCT         |object
extra.info.gender   |VARCHAR        |keyword
extra_gender        |VARCHAR        |keyword
extra_no            |INTEGER        |integer
first_name          |VARCHAR        |text
first_name.keyword  |VARCHAR        |keyword
gender              |VARCHAR        |keyword
hire_date           |TIMESTAMP      |datetime
languages           |TINYINT        |byte
last_name           |VARCHAR        |text
last_name.keyword   |VARCHAR        |keyword
name                |VARCHAR        |keyword
null_constant       |VARCHAR        |keyword
salary              |INTEGER        |integer
salary_ul           |NUMERIC        |unsigned_long
wildcard_name       |VARCHAR        |keyword
;

showColumnsCatalogAndIndexPattern
SHOW COLUMNS CATALOG 'my_*' IN "test_*";

       column       |     type      |    mapping
--------------------+---------------+---------------
birth_date          |TIMESTAMP      |datetime
dep                 |STRUCT         |nested
dep.dep_id          |VARCHAR        |keyword
dep.dep_name        |VARCHAR        |text
dep.dep_name.keyword|VARCHAR        |keyword
dep.from_date       |TIMESTAMP      |datetime
dep.to_date         |TIMESTAMP      |datetime
emp_no              |INTEGER        |integer
extra               |STRUCT         |object
extra.info          |STRUCT         |object
extra.info.gender   |VARCHAR        |keyword
extra_gender        |VARCHAR        |keyword
extra_no            |INTEGER        |integer
first_name          |VARCHAR        |text
first_name.keyword  |VARCHAR        |keyword
gender              |VARCHAR        |keyword
hire_date           |TIMESTAMP      |datetime
languages           |TINYINT        |byte
last_name           |VARCHAR        |text
last_name.keyword   |VARCHAR        |keyword
name                |VARCHAR        |keyword
null_constant       |VARCHAR        |keyword
salary              |INTEGER        |integer
salary_ul           |NUMERIC        |unsigned_long
wildcard_name       |VARCHAR        |keyword
;