Skip to content

Latest commit

 

History

History
157 lines (122 loc) · 8.15 KB

cli.md

File metadata and controls

157 lines (122 loc) · 8.15 KB
Raw

Run SQLFlow Command-line Tool

In addition to the Jupyter Notebook magic command, we provide a command-line client sqlflow to connect and operate the SQLFlow gRPC server. Similar to other command-line client tools like mysql, sqflow implements several interaction modes.

  1. sqlflow -e "SELECT ... TO TRAIN ..." runs the SQLFlow program provided in the command line.
  2. sqlflow -f a.sql runs the SQLFlow program in a file.
  3. sqflow starts the REPL mode for user interaction.

In the REPL mode, sqlflow supports automatic code completion and other features.

Installation

You can click below links to download the sqlflow binary according to your system type:

For Linux and macOS, after downloading the sqlflow binary, you need to change the file's mode to allow execution. e.g.

wget http://cdn.sqlflow.tech/latest/linux/sqlflow
chmod +x sqlflow

Quick Start

We can run an example session by typing the following command on macOS. We assume you have installed our playground on your computer.

  • follow this guide to start your playground
  • connect to SQLFlow server according to the prompt message output by the playground, just do copy & paste, like:
./sqlflow --data-source="mysql://root:root@tcp(172.17.0.8)/?maxAllowedPacket=0"

You should be able to see the following:

Welcome to SQLFlow.  Commands end with ;

sqlflow>

Suppose that we are going to train a DNNClassifier model.

Let's go over some training data from the Iris database:

sqlflow> SELECT * from iris.train limit 2;
+--------------+-------------+--------------+-------------+-------+
| SEPAL LENGTH | SEPAL WIDTH | PETAL LENGTH | PETAL WIDTH | CLASS |
+--------------+-------------+--------------+-------------+-------+
|          6.4 |         2.8 |          5.6 |         2.2 |     2 |
|            5 |         2.3 |          3.3 |           1 |     1 |
+--------------+-------------+--------------+-------------+-------+

Then we can train a TensorFlow DNNClassifier model using the following statement.

SELECT * FROM iris.train
TO TRAIN DNNClassifier
WITH model.n_classes=3, model.hidden_units=[128,64],
    validation.select="SELECT * FROM iris.test"
LABEL class
INTO sqlflow_models.my_dnn_model;

To predict using the trained model, we can type the following statement.

sqlflow> SELECT *
FROM iris.test
TO PREDICT iris.predict.class
USING sqlflow_models.my_dnn_model;

...
Done predicting. Predict table : iris.predict

We can then check the prediction result.

sqlflow> SELECT * from iris.predict limit 3;
...
+--------------+-------------+--------------+-------------+-------+
| SEPAL LENGTH | SEPAL WIDTH | PETAL LENGTH | PETAL WIDTH | CLASS |
+--------------+-------------+--------------+-------------+-------+
|          6.3 |         2.7 |          4.9 |         1.8 |     2 |
|          5.7 |         2.8 |          4.1 |         1.3 |     1 |
|            5 |           3 |          1.6 |         0.2 |     0 |
+--------------+-------------+--------------+-------------+-------+

Congratulations! Now you have successfully completed a session using SQLFlow syntax to train model using DNNClassifier and make a quick prediction.

Command-line Options

Option Environment Variable Description
-sqlflow_server <quoted-query-string> SQLFLOW_SERVER Specify sqlflow server address, in host:port form, e.g. -sqlflow_server "localhost:50051"
-e <quoted-query-string> Execute from command line without entering interactive mode. e.g.
-e "SELECT * FROM iris.train TRAIN DNNClassifier..."
does the same thing as the training example above.
-f <filename> Execute from file without entering interactive mode. e.g.
-f ./my_sqlflow.sql
does the same thing as
< ./my_sqlflow.sql and cat ./my_sqlflow.sql | sqlflow. The special file - means read from standard input.
-datasource <database-connection-url> SQLFLOW_DATASOURCE Connect to the specified database. e.g. -datasource "mysql://root:root@tcp(localhost:3306)/"
-A No auto completion for sqlflow_models. This gives a quicker start.

Environment Variable Config File Setup

You can specify some of the options in a config file named .sqlflow_env under your home directory. This process is optional but can be convenient if you use the same config intensively. The content are exported as environment variables at run time. Be aware the config file is just a default setting, you can overwrite them via corresponding command-line options. Currently supported variables are listed in Environment Variable column in above table. You can setup the file using the following bash code.

cat <<EOF >~/.sqlflow_env
SQLFLOW_SERVER=localhost:50051
SQLFLOW_DATASOURCE=mysql://root:root@tcp(localhost:3306)/?maxAllowedPacket=0
EOF

Keyboard Shortcuts

Moving the cursor

Keyboard Shortcut Action
Ctrl + a Go to the beginning of the line (Home)
Ctrl + e Go to the end of the line (End)
Meta + b Go back one word
Meta + f Go forward one word
Ctrl + b Go back one character (Left arrow)
Ctrl + f Go forward one character (Right arrow)

Editing

Keyboard Shortcut Action
Ctrl + l Clear the screen
Meta + Del Cut the word before the cursor to the clipboard
Meta + d Cut the word after the cursor to the clipboard
Ctrl + w Cut the word before the cursor to the clipboard
Ctrl + d Delete the character under the cursor
Ctrl + h Delete the character before the cursor (Backspace)
Ctrl + k Cut the line after the cursor to the clipboard
Ctrl + u Cut the line before the cursor to the clipboard
Ctrl + y Paste the last thing to be cut (yank)
TAB Auto completion for model/attributes names, navigate the pop-up menu

History

Keyboard Shortcut Action
Ctrl + r Pop a menu of history commands including the specified character(s), the menu updates as you type
Ctrl/Meta + p Show the previous statement in history, or navigate the pop-up history menu (Up arrow)
Ctrl/Meta + n Show the Next statement in history, or navigate the pop-up history menu (Down arrow)
Meta + W Similar to Ctrl + r but use wildcard to search history commands

Control

Keyboard Shortcut Action
Ctrl + L Clear the screen
Ctrl + D Exit (when no inputs)