# 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:

```sql
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](https://openmldb.ai/docs/zh/main/reference/client_config/client_spark_config.html) 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](https://openmldb.ai/docs/zh/main/openmldb_sql/ddl/SET_STATEMENT.html#id4).
- 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:

```sql
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:

```sql
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:

```sql
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:

```sql
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.
```