CLI#

CLI refers to the shell client of OpenMLDB, which is started using the binary program openmldb from the release package bin. The startup command is usually bin/openmldb --zk_cluster=127.0.0.1:2181 --zk_root_path=/openmldb --role=sql_client.

Note

If the cluster is deployed using sbin, you can start the CLI using the openmldb-cli.sh script in sbin, which will automatically fill in the parameters. For example, in the demo cluster, the command to start the CLI is OPENMLDB_MODE=cluster /work/openmldb/sbin/openmldb-cli.sh.

This article will introduce some commonly used CLI configuration options and convenient non-interactive usage methods. The complete list of configuration options can be found in bin/openmldb --help.

Basic usage#

For each command executed in the CLI, it will print “SUCCEED”/“Error” to indicate whether the command was successful.

Configuration options#

In addition to the required parameters --zk_cluster, --zk_root_path, and --role, the CLI can also be supplemented with some configurable options. The format for configuring options is --<option_name>=<option_value>. For example, if we want to add the spark_conf configuration option:

bin/openmldb --zk_cluster=127.0.0.1:2181 --zk_root_path=/openmldb --role=sql_client --spark_conf=/work/openmldb/bin/spark.conf

Individual configuration option details can be obtained using the help command. For example, to find out the meaning and default value of the spark_conf option, we can use bin/openmldb --help | grep spark_conf.

Below we will describe some commonly used configuration options.

  • spark_conf: If the default configuration of offline tasks is not sufficient, such as the driver/executor memory of Spark being too small, this option can be used to change the Spark parameters of the offline tasks. The input is the path of the configuration file, and the format details can be found in the Client Spark Configuration documentation. It only affects the currently started CLI and does not affect other CLI or SDK clients.

  • glog_level & glog_dir: By default, CLI only prints logs of WARNING level and above. If you need to change the log level, you can use glog_level to adjust it. INFO, WARNING, ERROR, and FATAL logs correspond to 0, 1, 2, and 3 respectively. glog_dir is empty by default, output to stdout. It can also be specified as a path and saved as a log file.

  • sync_job_timeout: The default synchronization waiting time for CLI to execute offline synchronization tasks is 0.5h. If the offline synchronization task needs to wait for a longer time, you can change this configuration. However, it should be noted that the configuration of TaskManager in the cluster also needs to be changed. Details can be found in Offline Command Configuration.

  • zk_log_level & zk_log_file: The logs generated by CLI when connecting to ZooKeeper are not printed by default. If you want to display the logs, you can adjust zk_log_level. The printed logs are printed to stderr by default, and since the ZooKeeper connection is a background thread, ZooKeeper-related logs may suddenly appear in the CLI interactive interface, which does not affect the use of CLI but affects the display of the interface. You can use zk_log_file to output ZooKeeper-related logs to a file.

  • zk_session_timeout: The expected ZooKeeper session timeout is not necessarily the actual session timeout. If the value is set too large, ZooKeeper Server’s tickTime or maxSessionTimeout also needs to be adjusted.

  • user: Specify the username for login. If not specified, it defaults to ‘root’.

  • password: Specify the password for login. If not specified, you’ll be prompted to enter the password in interactive mode.

Non-Interactive Usage#

The interface that appears after starting the CLI is called an interactive interface. You need to enter SQL statements and press Enter to execute operations. Here are some non-interactive usage methods for batch processing or debugging.

Non-interactive Deletion#

When performing deletion operations, such as drop table or drop deployment, the CLI requires confirmation to prevent accidental deletion by default. You can use --interactive=false to disable interactive deletion confirmation.

Single SQL#

In some scenarios, we usually only use the CLI to execute a single SQL statement, such as show components; to check whether the cluster components are working properly. We can use non-interactive mode to avoid the cumbersome steps of the interactive CLI. The non-interactive mode can be started using --cmd to pass SQL, for example:

bin/openmldb --zk_cluster=127.0.0.1:2181 --zk_root_path=/openmldb --role=sql_client --cmd='show components;'

If the cmd needs to be executed under a specific database, you can use the --database option (only valid when cmd is not empty, otherwise the option is ignored) to specify the database name. For example:

bin/openmldb --zk_cluster=127.0.0.1:2181 --zk_root_path=/openmldb --role=sql_client --database=demo_db --cmd='desc demo_table1'

Note

Using –cmd will automatically set –interactive=false, so you don’t need to manually set it. Therefore, you can directly execute delete operations with –cmd.

SQL script#

If you want CLI to execute an SQL script, you can use redirection. For example, suppose you have an SQL script that creates a database and tables:

CREATE DATABASE demo_db;
USE demo_db;
CREATE TABLE demo_table1(c1 string, c2 int, c3 bigint, c4 float, c5 double, c6 timestamp, c7 date);

We save it as a file named create.sql and then execute the script through redirection:

bin/openmldb --zk_cluster=127.0.0.1:2181 --zk_root_path=/openmldb --role=sql_client < create.sql

Note

Please note that when using redirection, we cannot specify the database through CLI startup options, so we need to include use demo_db; in the SQL script.

If the SQL script includes a delete operation, --interactive=false is required to skip the deletion confirmation.