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:

• Linux
• macOS
• Windows

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

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

Editing

History

Control