- Instructions on how to install SQLFlow on your own server.
SQLFow was comprised of two parts: frontend and backend. The frontend and backend can be installed on the same server, or they can be installed on two different servers seperately.
- SQLFlow on-premise version
- A linux server with at least 8GB memory (ubuntu 20.04 is recommended).
- Java 8
- Nginx web server.
- Port needs to be opened. (80, 8761,8081,8083. Only 80 port need to be opened if you setup the nginx reverse proxy as mentioned in the below)
sudo apt-get update
sudo apt-get install nginx -y
sudo apt-get install default-jre -y
CentOS
Mac
Windows
create a directory :
# it must be created start with root path
sudo mkdir -p /wings/sqlflowupload your zip file including backend and frontend file to sqlflow folder, and unzip like this :
unzip sqlflow.zipYou 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
│ │ ├── monitor.bat
│ │ ├── monitor.sh
│ │ ├── sqlservice.bat
│ │ ├── sqlservice.sh
│ │ ├── sqlservice.vbs
│ │ ├── stop.bat
│ │ ├── stop.sh
│ ├── lib
│ │ ├── eureka.jar
│ │ ├── gspLive.jar
│ │ ├── sqlservice.jar
│ ├── conf
│ │ ├── gudu_sqlflow_license.txt
│ │ ├── gudu_sqlflow.conf
│ ├── data
│ │ ├── job
│ │ │ ├── task
│ │ │ ├── {userid}
│ │ ├── schema
│ │ ├── session
│ │ ├── version
│ ├── log
│ ├── tmp
│ │ └── cache
└── frontend
├── config.public.json
├── images
│ ├── check.svg
│ ├── Join.svg
│ ├── pic_Not logged in.png
│ └── visualize.svg
├── index.********************.css
├── index.********************.css
├── index.********************.css
├── index.********************.css
└── index.html
└── lang
├── page.*********************.js
├── page.*********************.js
├── page.*********************.js
├── page.*********************.js
├── public.*********************.js
├── widget
│ ├── index.js
│ ├── sqlflow-library.version.css
│ └── sqlflow-library.version.js
set folder permissions :
sudo chmod -R 755 /wings/sqlflow1. Config Nginx
open your nginx configuration file ( at /etc/nginx/sites-available/default under ubuntu ), add a server :
server {
listen 80 default_server;
listen [::]:80 default_server;
root /wings/sqlflow/frontend/;
index index.html;
location ~* ^/index.html {
add_header X-Frame-Options deny; # remove this line if you want embed sqlflow in iframe
add_header Cache-Control no-store;
}
location / {
try_files $uri $uri/ =404;
}
location /api/ {
proxy_pass http://127.0.0.1:8081/;
proxy_connect_timeout 600s ;
proxy_read_timeout 600s;
proxy_send_timeout 600s;
proxy_set_header Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header User-Agent $http_user_agent;
}
}note that 8081 in proxy_pass http://127.0.0.1:8081/ should be the same as gspLive.jar's port.
/api is mapped to http://127.0.0.1:8081 in the above configration. This is useful if you
company doesn't allow access 8081 port from the external.
2. modify frontend configuration file config.private.json
- Open the configration file "/wings/sqlflow/frontend/config.private.json"
- Modify the ApiPrefix attribute
"ApiPrefix": "/api"
If you don't want to change the default service port, just ignore this section, otherwise, please act as the following instructions.
- Web port is
80 - SQLFlow backend service port:
| File | Port |
|---|---|
| eureka.jar | 8761 |
| gspLive.jar | 8081 |
| sqlservice.jar | 8083 |
Change the default web port from 80 to 9000 (or any port you like).
Change the default gspLive port from 8081 to 9001(or any port you like).
-
Change the port in nginx config file
-
Change the port in gspLive.sh(gspLive.bat)
start service in background:
sudo /wings/sqlflow/backend/bin/backend.shplease 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
start your nginx :
sudo service nginx startor reload :
sudo nginx -s reloadopen http://yourdomain.com/ to see the SQLFlow.
open http://yourdomain.com/api/gspLive_backend/doc.html?lang=en to see the Restful API documention. OR
open http://yourdomain.com:8081/gspLive_backend/doc.html?lang=en to see the Restful API documention.
If this is the first time you setup the Gudu SQLFlow on a new machine,
then, you will see this license UI:
- You send us the Gudu SQLFlow Id (6 characters in red).
- We will generate a license file for you based on this id.
- You upload the license file by click the "upload license file" link.
sqlflow provides several optioins to control the service analysis logic. Open the sqlservice configuration file(conf/gudu_sqlflow.conf)
-
relation_limit: default value is 2000. 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 return error message "Sorry, the service is busy. Please try again later."
- Get userId from gudu_sqlflow.conf
- Open the configration file "/wings/sqlflow/backend/conf/gudu_sqlflow.conf"
- The value of anonymous_user_id field is webapi userId
anonymous_user_id=xxx- Note: on-promise mode, webapi call doesn't need the token parameter
-
Test webapi by curl
- test sql:
select name from user
- curl command:
curl -X POST "http://yourdomain.com/api/gspLive_backend/sqlflow/generation/sqlflow" -H "accept:application/json;charset=utf-8" -F "userId=YOUR USER ID HERE" -F "dbvendor=dbvoracle" -F "sqltext=select name from user"
- 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.
If you see this error, just wait another 3-5 minutes to wait the backend service startup successfully and refresh the web page.
Or, this issue may caused by the browser cache, just use Incognito mode to access the Sqlflow page and
clear the cache.
a) Type: vim /etc/nginx/nginx.conf and change the server section of the conf file with below configurations
server {
listen 80 default_server;
listen [::]:80 default_server;
server_name _;
#root /usr/share/nginx/html;
root /wings/sqlflow/frontend/;
index index.html
# Load configuration files for the default server block.
include /etc/nginx/default.d/*.conf;
location / {
try_files $uri $uri/ =404;
}
error_page 404 /404.html;
location = /40x.html {
}
error_page 500 502 503 504 /50x.html;
location = /50x.html {
}
location ~* ^/index.html {
add_header X-Frame-Options deny; # remove this line if you want to embed SQLFlow in iframe
add_header Cache-Control no-store;
}
location /api/ {
proxy_pass http://127.0.0.1:8081/;
proxy_connect_timeout 600s ;
proxy_read_timeout 600s;
proxy_send_timeout 600s;
proxy_set_header Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header User-Agent $http_user_agent;
}
}
b) Configure selinux to permission by going to: vi /etc/selinux/configure --> SELinux status to = permissive
If you find this error, this is because the port that is needed by the SQLFlow is already used by another application, please configure the SQLFlow to use another port.
Or, the Gudu SQLFlow backend service is not started. Please check how to start the backend and verify the status.