Linux

Please check the following page for the old SQLFlow installation:

For older version SQLFlow under Linux

If you have SQLFlow 5.x installed on your server and would like to upgrade to SQLFlow 6.x, please refer to this page:

Upgrade

Starting from SQLFlow 6.0, we have integrated web packages into the backend service, no Frontend installation and nginx configuration is required!

Prerequisites

• SQLFlow on-premise version

• A linux server with at least 8GB memory (ubuntu 20.04 is recommended).

• Java 8

• Port needs to be opened. (The default port is 8165 but you can customized this port)

Setup Environment (Ubuntu for example)

sudo apt-get update
sudo apt-get install default-jre -y	

CentOS

• How To Install Java on CentOS

Upload Files

create a directory :

# it must be created start with root path
sudo mkdir -p /wings/sqlflow

upload your zip file including backend and frontend file to sqlflow folder, and unzip like this :

unzip sqlflow.zip

You should get files organized like this:

/wings/
└── sqlflow
    ├── backend
    │   ├── bin
    │   │   ├── backend.bat
    │   │   ├── backend.sh
    │   │   ├── eureka.bat
    │   │   ├── eureka.sh
    │   │   ├── eureka.vbs
    │   │   ├── gspLive.bat
    │   │   ├── gspLive.sh
    │   │   ├── gspLive.vbs
    │   │   ├── init_regular.sh
    │   │   ├── monitor.bat
    │   │   ├── monitor.sh
    │   │   ├── sqlservice.bat
    │   │   ├── sqlservice.sh
    │   │   ├── sqlservice.vbs
    │   │   ├── stop.bat
    │   │   ├── stop.sh
    │   │   ├── taskscheduler.bat
    │   │   ├── taskscheduler.sh
    │   │   └── taskscheduler.vbs
    │   ├── conf
    │   │   └── gudu_sqlflow.conf
    │   └── lib
    │       ├── eureka.jar
    │       ├── gspLive.jar
    │       ├── sqlservice.jar
    │       └── taskscheduler.jar

set folder permissions :

sudo chmod -R 755 /wings/sqlflow

Customize the port

If you don't want to change the default service port you can just ignore this section. Otherwise this section will show you how to customize the port.

1. Default port

1. Web port is 8165

2. SQLFlow backend service port:

File	Port
eureka.jar	8761
gspLive.jar	8165
sqlservice.jar	8083

2. Change the default port in gspLive.sh(gspLive.bat)

You can change the web or backend api port from 8165 to any available port.

Add the following section in gspLive.sh(or gspLive.bat in Windows):

--server.port=<customized_port>

Start Backend Services

start service in background:

sudo /wings/sqlflow/backend/bin/backend.sh

You can assign the RAM to SQLFlow by specifying the boot parameter when starting the service.

 backend.sh /m <RAM_VALUE>

The RAM_VALUE could be: 4g 8g 16g 32g or 64g.

SQLFlow will automatically allocate the memory based on the status of the installed server if this parameter is not given. SQLFlow would allocate less than 31 GB memory if that is the case. However, if your total memory is less than 32 GB, SQLFlow would allocate all the remaining memory on your server.

please allow 3-5 minutes to start the service.

use ps -ef|grep java to check those 3 processing are running.

ubuntu   11047     1  0 Nov02 ?        00:04:44 java -server -jar eureka.jar
ubuntu   11076     1  0 Nov02 ?        00:04:11 java -server -Xmn512m -Xms2g -Xmx2g -Djavax.accessibility.assistive_technologies=  -jar sqlservice.jar
ubuntu   11114     1  0 Nov02 ?        00:05:17 java -server -jar gspLive.jar

Open SQLFlow

open http://yourdomain.com/ to see the SQLFlow.

open http://yourdomain.com:8165/doc.html?lang=en or http://localhost:8165/api/gspLive_backend/doc.html?lang=en to see the Restful API document.

To login, please check the following default user credentials:

Cloud and On-Premise version

Gudu SQLFlow License file

If this is the first time you setup the Gudu SQLFlow on a new machine, then, you will see this license UI:

1. You send us the Gudu SQLFlow Id (6 characters in red).

2. We will generate a license file for you based on this id.

3. You upload the license file by click the "upload license file" link.

Backend Services Configuration

sqlflow provides several options to control the service analysis logic. Open the sqlservice configuration file(conf/gudu_sqlflow.conf)

• user_token_expire_hours: default value is 24. User can customize the TTL of the token by setting this field. In case user_token_expire_hours less than or equals to 0, the user token would never expire.

• ignore_user_token: default value is false, token auth is skipped in case the value is set to true.

• relation_limit: default value is 1000. When the count of selected object relations is greater than relation_limit, sqlflow will fallback to the simple mode, ignore all the record sets. If the relations of simple mode are still greater than relation_limit, sqlflow will only show the summary information.

• big_sql_size: default value is 4096. If the sql length is greater than big_sql_size, sqlflow submit the sql in the work queue and execute it. If the work queue is full, sqlflow throws an exception and returns error message "Sorry, the service is busy. Please try again later."

SQLFlow client api call

See sqlflow client api call

• Get userId from the account profile page and generate the secrete key

• Generate token by invoking the token generate API

generate token

generate user token for restful api

POSThttps://api.gudusoft.com/api/gspLive_backend/user/generateToken

Query parameters

Response

200201401403404

OK

Body

application/json;charset=utf-8

string

Request

JavaScriptCurlPython

const response = await fetch('https://api.gudusoft.com/api/gspLive_backend/user/generateToken?userId=text&secretKey=text', {
    method: 'POST',
    headers: {},
});
const data = await response.json();

Response

200

text

curl --location --request POST 'http://localhost:8165/api/gspLive_backend/user/generateToken?userId=%3CUSER_ID%3E&secretKey=%3CSECRET_KEY%3E' \
--header 'Accept: application/json;charset=utf-8'

• Test webapi by curl

  • test sql:

    select name from user

  • curl command:

  curl --location 'http://localhost:8165/api/gspLive_backend/sqlflow/generation/sqlflow' \
  --header 'accept: application/json;charset=utf-8' \
  --form 'userId="<USER ID>"' \
  --form 'dbvendor="dbvoracle"' \
  --form 'sqltext="select name from user"' \
  --form 'token="<TOKEN>"'

  • response:

    {
      "code": 200,
      "data": {
        "dbvendor": "dbvoracle",
        "dbobjs": [
          ...
        ],
        "relations": [
          ...
        ]
      },
      "sessionId": ...
    }

  • If the code returns 401, please check the userId is set or the userId is valid.

Enable Regular Job

If you need to enable regular job feature on your sqlflow on-premiser, you will also need to install Clickhouse on your server. Check here for Clickhouse installation:

Clickhouse Installation

Enable HTTPS

To use HTTPS with your domain name, you need a SSL or TLS certificate installed. You can directly buy a certificate from CA. If you don't need a recognized certificate, you can generate one by yourselves.

To generate the certificate files, you can use the following commands:

openssl req -newkey rsa:2048 -nodes -keyout sqlflow.key -out sqlflow.csr -subj "/CN=127.0.0.1"
openssl x509 -req -days 365 -in sqlflow.csr -signkey sqlflow.key -out sqlflow.crt
openssl pkcs8 -topk8 -inform PEM -outform PEM -in sqlflow.key -out sqlflow-pkcs8.key -nocrypt
openssl pkcs12 -export -in sqlflow.crt -inkey sqlflow-pkcs8.key -out sqlflow_keystore.p12 -name sqlflow

You will have sqlflow_keystore.p12 and sqlflow-pkcs8.key generated with the above commands.

Copy sqlflow_keystore.p12 and sqlflow-pkcs8.key to /wings/sqlflow/backend/conf and add following configs in /wings/sqlflow/backend/bin/gspLive.sh:

--server.ssl.enabled=true --server.ssl.key-store-password=<your password> --server.ssl.key-store-type=PKCS12 --server.ssl.key-alias=sqlflow --server.ssl.key-store=file:../conf/sqlflow_keystore.p12

Restart the SQLFlow and https is now enabled.