Cache service CLI#

The Starburst Enterprise platform (SEP) cache service command line interface (CLI) provides a terminal-based interface for listing and configuring redirections managed by the cache service.

Requirements#

The cache service CLI requires a Java virtual machine available on the path. It can be used with Java version 8 and higher. It is a self-executing JAR file, which means it acts like a normal UNIX executable.

Installation#

  • To download the cache service CLI file, contact Starburst Support

  • Support provides access to a JAR file named starburst-cache-cli-nnn.jar where nnn is the version number.

  • Rename this file to cache-cli

  • Make it executable with chmod +x cache-cli

  • Place it in a directory on the PATH, such as ~/bin, or /usr/local/bin

cache-cli current_redirection --server localhost:8180 --source=mysql.test.orders

Run the CLI with the --help option to see the available options.

Authentication#

You can override your username with the --user option. It defaults to your operating system username. If your cache service requires password authentication, use the --password option to have the CLI prompt for a password. You can set the CACHE_SERVICE_PASSWORD environment variable with the password value to avoid the prompt.

HTTP client properties, such as keystore and truststore, can be specified through a configuration file that can be passed to the CLI with the --properties option.

For example, when the cache service is running in HTTPS mode, the CLI can be configured to connect to it by adding the below properties to a file and providing it’s path in the --properties option to the CLI.

http-client.trust-store-path=etc/localhost.truststore
http-client.trust-store-password=changeit
http-client.https.hostname-verification=false
cache-cli current_redirection --server https://localhost:8543 --source=mysql.test.orders --properties=etc/http-client-config.properties --user test --password

Output formats#

The cache service CLI provides the option --output-format to control how the output is displayed when running in non-interactive mode. The available options shown in the following table must be entered in uppercase. The default value is ALIGNED.

Output format options#

Option

Description

JSON

Output rows emitted as JSON objects with name-value pairs.

ALIGNED

Output emitted as an ASCII character table with values.

Examples#

Consider the following command run as shown, or with --output-format ALIGNED:

cache-cli current_redirection --source=mysql.test.nation_tmp

The output is as follows:

 id | target_catalog | target_schema |                  target_table                   |       create_time        |  ttl  | grace_period | max_import_duration |    import_start_time     |    import_finish_time    | columns | error_message
----+----------------+---------------+-------------------------------------------------+--------------------------+-------+--------------+---------------------+--------------------------+--------------------------+---------+---------------
 1  | hive           | cache         | nation_tmp_3d3554ed_6be6_4d01_b9f2_d1886c8a5e63 | 2021-01-04T18:47:19.245Z | 1.50h |  15.00m      | 30.00m              | 2021-01-04T18:47:19.331Z | 2021-01-04T18:47:25.100Z | *       |
(1 row)

The output with --output-format JSON is:

{"id":"1","target_catalog":"hive","target_schema":"cache","target_table":"nation_tmp_3d3554ed_6be6_4d01_b9f2_d1886c8a5e63","create_time":"2021-01-04T18:47:19.245Z","ttl":"1.50h","grace_period":"15.00m","max_import_duration":"30.00m","import_start_time":"2021-01-04T18:47:19.331Z","import_finish_time":"2021-01-04T18:47:25.100Z","columns":"*","error_message":""}

Commands#

The cache service CLI implements the following sub-commands for viewing and adding cached tables.

Current redirection#

The current_redirection command shows whether the specified source table has a valid redirection. The source table is provided through the --source option in the format <catalog>.<schema>.<table>:

cache-cli current_redirection --source=mysql.test.nation_tmp

The output either shows a valid redirection or 0 rows as follows:

 id | target_catalog | target_schema |                  target_table                   |       create_time        |  ttl  | grace_period | max_import_duration |    import_start_time     |    import_finish_time    | columns | error_message
----+----------------+---------------+-------------------------------------------------+--------------------------+-------+--------------+---------------------+--------------------------+--------------------------+---------+---------------
 1  | hive           | cache         | nation_tmp_3d3554ed_6be6_4d01_b9f2_d1886c8a5e63 | 2021-01-04T18:47:19.245Z | 1.50h |  15.00m      | 30.00m              | 2021-01-04T18:47:19.331Z | 2021-01-04T18:47:25.100Z | *       |
(1 row)

 id | target_catalog | target_schema | target_table | create_time | ttl | grace_period | max_import_duration | import_start_time | import_finish_time | columns | error_message
----+----------------+---------------+--------------+-------------+-----+--------------+---------------------+-------------------+--------------------+---------+---------------
(0 rows)

List redirections#

The list_redirections command shows the list of cached tables for the specified source table. The column error_message shows the reasons for any failure encountered while trying to create and populate the cached table:

cache-cli list_redirections --source=mysql.test.nation_tmp

The output is as follows:

 id | target_catalog | target_schema |                  target_table                   |       create_time        |  ttl  | grace_period | max_import_duration |    import_start_time     |    import_finish_time    |  columns |                                                                                                                                         error_message
----+----------------+---------------+-------------------------------------------------+--------------------------+-------+--------------+---------------------+--------------------------+--------------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 1  | hive           | cache         | nation_tmp_a2e89580_7179_4c96_be58_48d32bbbb11f | 2021-01-05T03:54:57.682Z | 2.42h | 15.00m       | 30.00m              | 2021-01-05T03:54:58.096Z | 2021-01-05T03:55:01.555Z |  *       |
 2  | hive           | cache         | nation_tmp_8dc0a64f_ed37_48ff_b241_7fd7e59c43d6 | 2021-01-05T04:54:57.682Z | 1.42h | 15.00m       | 30.02m              | 2021-01-05T04:54:58.656Z | 2021-01-05T04:55:01.602Z |  *       |
 3  | hive           | cache         | nation_tmp_5af2e36c_b303_425a_b735_cc9e925ca9d5 | 2021-01-05T05:54:57.682Z | 1.50h | 15.00m       | 30.03m              | 2021-01-05T05:54:59.480Z | 2021-01-05T05:55:01.556Z |  *       |
 4  | hive           | cache         | nation_tmp_9be03071_e197_4a8e_8e4e_46a44a32096b | 2021-01-05T06:27:05.463Z | 1.00h | 15.00m       | 30.00m              | 2021-01-05T06:27:05.561Z |                          | abc, xyz | java.sql.SQLException: Query failed (#20210105_021140_04846_58gyu): line 2:3: Column 'abc' cannot be resolved [statement:"CREATE TABLE hive.cache.nation_tmp_9be03071_e197_4a8e_8e4e_46a44a32096b AS SELECT abc,xyz FROM mysql.test.nation_tmp", arguments:{positional:{}, named:{}, finder:[]}]
(4 rows)

Add redirection#

The cache command adds caching of a given source table into the target catalog and schema. The cache service automatically generates a name for target table with a random suffix. The cached table is used to redirect table scans on source tables for a time interval specified by the --cache-ttl option:

cache-cli cache --source=mysql.test.nation_tmp --target-catalog=hive --target-schema=cache --cache-ttl=1h

The output shows the target table as follows:

 catalog_name | schema_name |                   table_name
--------------+-------------+-------------------------------------------------
 hive         | cache       | nation_tmp_9be03071_e197_4a8e_8e4e_46a44a32096b

The grace period is 15m by default. It can be extended using the --grace-period option based on the maximum duration that the queries are expected to run.

Optional cache command options#

Option name

Description

Default

allow-multiple-imports

Allow parallel imports for the same source table. By default another command does not start another import, if one is already running for the same source table. This is useful to force another import, for example when an existing import seems to be stalled.

false

grace-period

The service removes cached tables when they are no longer needed, such as when a newer redirection is present or ttl expired. The service waits for grace-period before removing the cached table. This allows any running queries which started just before the cached table is expired to finish gracefully.

15m

max-import-duration

Maximum allowed execution time for cached table import job

30m

columns

Comma separated list of columns to be cached from source table

All columns are cached by default

partition-columns

Comma separated list of columns for partitioning the data in the target table

use-preferred-write-partitioning

Use table partitioning to parallelize writes between worker nodes. This reduces import memory usage and improves cached table file sizes.

true

writer-count

Number of writers per task when writing table

4

scale-writers

Scale writers when writing table

false

use-server-import-config

Use caching server table import configuration. The cache server has different default import configuration for partitioned and non-partitioned tables.

true

Expire redirections#

The expire_redirection command expires the redirection corresponding to a given identifier. The redirection grace period is still honored. Therefore queries that use cached tables do not fail. The column id from the output of list_redirections or current_redirection commands can be used to identify a redirection for expiry.

cache-cli expire_redirection --id 1

The output is either Success or an error message containing the reason for failure.