Link

Run SQLFlow REPL

In addition to building SQLFlow into a gRPC server, accessed via Jupyter Notebook, we could also build it into a command line program, whose main function reads SQL statements from the console, evaluates them by calling SQLFlow, and prints the results. This command-line program makes it easy to debug and profile locally without starting the SQLFlow server and the Jupyter server. We call this command-line program the SQLFlow REPL. SQLFlow REPL supports automatic code completion to ease applying the underlying powerful AI toolset of SQLFlow.

Quick Start

The SQLFlow Docker image contains the REPL command-line program. We can run an example session by typing the following command on MacOS. If you run Docker on Linux, please change host.docker.internal:3306 to localhost:3306.

docker run -it --rm --net=host sqlflow/sqlflow bash -c '/start.sh populate-example-dataset-mysql-local; \
repl --datasource="mysql://root:root@tcp(host.docker.internal:3306)/?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 Description
-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 \| REPL...
-model_dir <local-directory> Save model to a local directory. e.g. -model_dir "./models/"
-datasource <database-connection-url> Connect to the specified database. e.g. -datasource "mysql://root:root@tcp(host.docker.internal:3306)/"
-A No auto completion for sqlflow_models. This gives a quicker start

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)