SQLFlow的restful接口
我们不光可以在SQLFlow图形化界面上使用，还可以通过SQLFlow提供的一系列restful接口进行使用，目前SQLFlow提供很多接口供第三方调用。
使用案例
通过SQLFlow的api实现从对指定SQL脚本文件进行分析并得到分析结果。
java实现版本
- sqlflow调用
import com.alibaba.fastjson.JSONObject;
import org.apache.http.HttpEntity;
import org.apache.http.NameValuePair;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.entity.ContentType;
import org.apache.http.entity.mime.MultipartEntityBuilder;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.message.BasicNameValuePair;
import org.apache.http.util.EntityUtils;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class SqlFlowUtil {
private static String token = "";
private SqlFlowUtil() {
}
public static String getToken(String url, String userId,
String secretKey, Integer flag) {
try {
System.out.println("start get token from sqlflow.");
Map<String, String> param = new HashMap<>();
param.put("secretKey", secretKey);
param.put("userId", userId);
if ("gudu|0123456789".equals(userId)) {
return "token";
}
String result = doPost(url, param);
JSONObject object = JSONObject.parseObject(result);
if ("200".equals(object.getString("code"))) {
token = object.getString("token");
System.out.println("get token from sqlflow successful.");
return token;
}
return "";
} catch (Exception e) {
if (flag == 0) {
if (url.startsWith("http:")) {
url = url.replace("http", "https");
}
return getToken(url, userId,
secretKey, 1);
}
if (flag == 1) {
System.err.println("get token from sqlflow failed.");
}
return token;
}
}
public static String submitJob(String filePath,
String url,
String dbVendor,
String userId,
String token,
String jobName) throws IOException {
System.out.println("start submit job to sqlflow.");
CloseableHttpClient httpClient = HttpClients.createDefault();
HttpPost uploadFile = new HttpPost(url);
MultipartEntityBuilder builder = MultipartEntityBuilder.create();
builder.addTextBody("dbvendor", dbVendor, ContentType.TEXT_PLAIN);
builder.addTextBody("jobName", jobName, ContentType.TEXT_PLAIN);
builder.addTextBody("token", token, ContentType.TEXT_PLAIN);
builder.addTextBody("userId", userId, ContentType.TEXT_PLAIN);
File f = new File(filePath);
builder.addBinaryBody("sqlfiles", new FileInputStream(f), ContentType.APPLICATION_OCTET_STREAM, f.getName());
HttpEntity multipart = builder.build();
uploadFile.setEntity(multipart);
CloseableHttpResponse response = httpClient.execute(uploadFile);
HttpEntity responseEntity = response.getEntity();
return EntityUtils.toString(responseEntity, "UTF-8");
}
public static String getStatus(String url,
String userId,
String token,
String jobId) throws IOException {
CloseableHttpClient httpClient = HttpClients.createDefault();
HttpPost uploadFile = new HttpPost(url);
MultipartEntityBuilder builder = MultipartEntityBuilder.create();
builder.addTextBody("jobId", jobId, ContentType.TEXT_PLAIN);
builder.addTextBody("token", token, ContentType.TEXT_PLAIN);
builder.addTextBody("userId", userId, ContentType.TEXT_PLAIN);
HttpEntity multipart = builder.build();
uploadFile.setEntity(multipart);
CloseableHttpResponse response = httpClient.execute(uploadFile);
HttpEntity responseEntity = response.getEntity();
return EntityUtils.toString(responseEntity, "UTF-8");
}
public static String exportLineage(ExportLineageReq req) throws IOException {
CloseableHttpClient httpClient = HttpClients.createDefault();
HttpPost uploadFile = new HttpPost(req.getUrl());
MultipartEntityBuilder builder = MultipartEntityBuilder.create();
builder.addTextBody("jobId", req.getJobId(), ContentType.TEXT_PLAIN);
builder.addTextBody("userId", req.getUserId(), ContentType.TEXT_PLAIN);
builder.addTextBody("token", req.getToken(), ContentType.TEXT_PLAIN);
builder.addTextBody("tableToTable", String.valueOf(req.getTableToTable()), ContentType.TEXT_PLAIN);
HttpEntity multipart = builder.build();
uploadFile.setEntity(multipart);
CloseableHttpResponse response = httpClient.execute(uploadFile);
HttpEntity responseEntity = response.getEntity();
InputStream in = responseEntity.getContent();
FileUtil.mkFile(req.getDownloadFilePath());
File file = new File(req.getDownloadFilePath());
FileOutputStream fout = new FileOutputStream(file);
int a;
byte[] tmp = new byte[1024];
while ((a = in.read(tmp)) != -1) {
fout.write(tmp, 0, a);
}
fout.flush();
fout.close();
in.close();
return "download success, path:" + req.getDownloadFilePath();
}
private static String doPost(String url, Map<String, String> param) {
CloseableHttpClient httpClient = HttpClients.createDefault();
CloseableHttpResponse response = null;
String resultString = "";
try {
HttpPost httpPost = new HttpPost(url);
if (param != null) {
List<NameValuePair> paramList = new ArrayList<>();
for (String key : param.keySet()) {
paramList.add(new BasicNameValuePair(key, param.get(key)));
}
UrlEncodedFormEntity entity = new UrlEncodedFormEntity(paramList, "utf-8");
httpPost.setEntity(entity);
}
response = httpClient.execute(httpPost);
resultString = EntityUtils.toString(response.getEntity(), "utf-8");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
response.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return resultString;
}
public static class ExportLineageReq {
private String jobId;
private String userId;
private String token;
private String url;
private String downloadFilePath;
private Boolean tableToTable = false;
public String getJobId() {
return jobId;
}
public void setJobId(String jobId) {
this.jobId = jobId;
}
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getToken() {
return token;
}
public void setToken(String token) {
this.token = token;
}
public Boolean getTableToTable() {
return tableToTable;
}
public void setTableToTable(Boolean tableToTable) {
this.tableToTable = tableToTable;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getDownloadFilePath() {
return downloadFilePath;
}
public void setDownloadFilePath(String downloadFilePath) {
this.downloadFilePath = downloadFilePath;
}
}
}
- 文件工具类
import java.io.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
public class FileUtil {
private static final int BUFFER_SIZE = 10 * 1024 * 1024;
private FileUtil() {
}
public static void mkFile(String filePath) throws IOException {
File testFile = new File(filePath);
File fileParent = testFile.getParentFile();
if (!fileParent.exists()) {
fileParent.mkdirs();
}
if (!testFile.exists()) {
testFile.createNewFile();
}
}
public static void toZip(String srcDir, OutputStream out, boolean KeepDirStructure)
throws RuntimeException {
ZipOutputStream zos = null;
try {
zos = new ZipOutputStream(out);
File sourceFile = new File(srcDir);
compress(sourceFile, zos, sourceFile.getName(), KeepDirStructure);
} catch (Exception e) {
throw new RuntimeException("zip error from ZipUtils", e);
} finally {
if (zos != null) {
try {
zos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
private static void compress(File sourceFile, ZipOutputStream zos, String name,
boolean KeepDirStructure) throws Exception {
byte[] buf = new byte[BUFFER_SIZE];
if (sourceFile.isFile()) {
zos.putNextEntry(new ZipEntry(name));
int len;
FileInputStream in = new FileInputStream(sourceFile);
while ((len = in.read(buf)) != -1) {
zos.write(buf, 0, len);
}
zos.closeEntry();
in.close();
} else {
File[] listFiles = sourceFile.listFiles();
if (listFiles == null || listFiles.length == 0) {
if (KeepDirStructure) {
zos.putNextEntry(new ZipEntry(name + "/"));
zos.closeEntry();
}
} else {
for (File file : listFiles) {
if (KeepDirStructure) {
compress(file, zos, name + "/" + file.getName(), KeepDirStructure);
} else {
compress(file, zos, file.getName(), KeepDirStructure);
}
}
}
}
}
public static OutputStream outStream(String path) throws IOException {
FileOutputStream fileOutputStream;
try {
fileOutputStream = new FileOutputStream(path);
} catch (Exception ex) {
mkFile(path);
fileOutputStream = new FileOutputStream(path);
}
return fileOutputStream;
}
}
- 时间工具类
import java.text.SimpleDateFormat;
import java.util.Date;
public class DateUtil {
public DateUtil() {
}
public static String format(Date date) {
return format(date, "yyyyMMdd");
}
public static String format(Date date, String pattern) {
if (date != null) {
SimpleDateFormat df = new SimpleDateFormat(pattern);
return df.format(date);
} else {
return null;
}
}
public static String timeStamp2Date(Long seconds) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
return sdf.format(new Date(seconds));
}
}
php版本
- 入口
<?php
class Grabit
{
function run($argv)
{
if (sizeof($argv) < 2) {
echo 'please enter the correct parameters.';
exit(1);
}
$userSecret = '';
$userId = '';
$dbvendor = '';
$sqlfiles = '';
$server = '';
$port = '';
$download = 1;
for ($i = 0; $i < sizeof($argv) - 1; $i++) {
if ($argv[$i] == '/s') {
$server = $argv[$i + 1];
}
if ($argv[$i] == '/p') {
$port = $argv[$i + 1];
}
if ($argv[$i] == '/f') {
$sqlfiles = $argv[$i + 1];
if (!file_exists($sqlfiles)) {
echo "The file is no exists";
exit(1);
}
}
if ($argv[$i] == '/u') {
$userId = $argv[$i + 1];
$userId = str_replace("'", '', $userId);
}
if ($argv[$i] == '/t') {
$dbvendor = 'dbv' . $argv[$i + 1];
if ($dbvendor == 'dbvsqlserver') {
$dbvendor = 'dbvmssql';
}
}
if ($argv[$i] == '/k') {
$userSecret = $argv[$i + 1];
}
if ($argv[$i] == '/r') {
$download = $argv[$i + 1];
}
}
if (!substr($server, 0, 4) === "http" && !substr($server, 0, 5) === "https") {
$server = "http://" . $server;
}
if (substr($server, -strlen(DIRECTORY_SEPARATOR)) === DIRECTORY_SEPARATOR) {
$server = substr($server, 0, strlen($server) - 1);
}
if ($port != '') {
$server = $server . ':' . $port;
}
echo '===================================== start =====================================';
echo PHP_EOL;
echo('start get token.');
echo PHP_EOL;
include('SqlFlowUtil.php');
$obj = new SqlFlowUtil();
$token = $obj->getToken($server, $userId, $userSecret);
echo 'get token successful.';
echo PHP_EOL;
if (is_dir($sqlfiles)) {
if (substr($sqlfiles, -strlen(DIRECTORY_SEPARATOR)) === DIRECTORY_SEPARATOR) {
$sqlfiles = rtrim($sqlfiles, DIRECTORY_SEPARATOR);
}
$zip = new \ZipArchive();
$sqlfileDir = $sqlfiles . '.zip';
if (file_exists($sqlfileDir)) {
if (PATH_SEPARATOR == ':') {
unlink($sqlfileDir);
} else {
$url = iconv('utf-8', 'gbk', $sqlfileDir);
unlink($url);
}
}
$open = $zip->open($sqlfileDir, \ZipArchive::CREATE);
if ($open === true) {
$this->toZip($sqlfiles, $zip);
$zip->close();
}
$sqlfiles = $sqlfileDir;
}
echo 'start submit job.';
echo PHP_EOL;
$result = $obj->submitJob($server, $userId, $token, $sqlfiles, time(), $dbvendor);
if ($result['code'] == 200) {
echo 'submit job successful.';
echo PHP_EOL;
$jobId = $result['data']['jobId'];
while (true) {
$result = $obj->getStatus($server, $userId, $token, $jobId);
if ($result['code'] == 200) {
$status = $result['data']['status'];
if ($status == 'partial_success' || $status == 'success') {
break;
}
if ($status == 'fail') {
echo 'job execution failed.';
exit(1);
}
}
}
echo $status;
echo 'start get result from sqlflow.';
echo PHP_EOL;
$filePath = $obj->getResult($server, $userId, $token, $jobId, $download);
echo 'get result from sqlflow successful. file path is : ' . $filePath;
} else {
echo 'submit job failed.';
}
echo PHP_EOL;
echo '===================================== end =====================================';
}
function toZip($path, $zip)
{
$handler = opendir($path);
while (($filename = readdir($handler)) !== false) {
if ($filename != "." && $filename != "..") {
if (is_dir($path . DIRECTORY_SEPARATOR . $filename)) {
$obj = new Grabit();
$obj->toZip($path . DIRECTORY_SEPARATOR . $filename, $zip);
} else {
$zip->addFile($path . DIRECTORY_SEPARATOR . $filename);
$zip->renameName($path . DIRECTORY_SEPARATOR . $filename, $filename);
}
}
}
@closedir($path);
}
}
$obj = new Grabit();
$obj->run($argv);
- sqlflow接口调用
<?php
include('HttpClient.php');
class SqlFlowUtil
{
function getToken($server, $userId, $userSecret)
{
if ($userId == 'gudu|0123456789') {
return 'token';
}
$httpVendor = new HttpClient();
$json['userId'] = $userId;
$json['secretKey'] = $userSecret;
$url = $server . '/gspLive_backend/user/generateToken';
$result = $httpVendor->postFrom($url, $json);
return $result['token'];
}
function submitJob($server, $userId, $token, $sqlfiles, $jobName, $dbvendor)
{
$httpVendor = new HttpClient();
$params = array(
'userId' => $userId,
'token' => $token,
'jobName' => $jobName,
'dbvendor' => $dbvendor,
'filename' => $jobName,
'sqlfiles' => file_get_contents($sqlfiles)
);
$url = $server . '/gspLive_backend/sqlflow/job/submitUserJob';
$result = $httpVendor->postFile($url, $params);
return $result;
}
function getStatus($server, $userId, $token, $jobId)
{
$httpVendor = new HttpClient();
$json['userId'] = $userId;
$json['token'] = $token;
$json['jobId'] = $jobId;
$url = $server . '/gspLive_backend/sqlflow/job/displayUserJobSummary';
$result = $httpVendor->postFrom($url, $json);
return $result;
}
function getResult($server, $userId, $token, $jobId, $download)
{
$dir = 'data' . DIRECTORY_SEPARATOR . 'result';
$str = $dir . DIRECTORY_SEPARATOR . date("Ymd") . '_' . $jobId;
$filePath = '';
$url = '';
if ($download == 1) {
$url = $server . '/gspLive_backend/sqlflow/job/exportLineageAsJson';
$filePath = $str . '_json.json';
} else if ($download == 2) {
$url = $server . '/gspLive_backend/sqlflow/job/exportLineageAsGraphml';
$filePath = $str . '_graphml.graphml';
} else if ($download == 3) {
$url = $server . '/gspLive_backend/sqlflow/job/exportLineageAsCsv';
$filePath = $str . '_csv.csv';
}
$httpVendor = new HttpClient();
$json['userId'] = $userId;
$json['token'] = $token;
$json['jobId'] = $jobId;
$httpVendor->mkdirs($dir);
$httpVendor->postJson($url, $json, $filePath);
return $filePath;
}
}
- http工具
<?php
class HttpClient
{
protected static $url;
protected static $delimiter;
function mkdirs($a1, $mode = 0777)
{
if (is_dir($a1) || @mkdir($a1, $mode)) return TRUE;
if (!static::mkdirs(dirname($a1), $mode)) return FALSE;
return @mkdir($a1, $mode);
}
public function __construct()
{
static::$delimiter = uniqid();
}
private static function buildData($param)
{
$data = '';
$eol = "\r\n";
$upload = $param['sqlfiles'];
unset($param['sqlfiles']);
foreach ($param as $name => $content) {
$data .= "--" . static::$delimiter . "\r\n"
. 'Content-Disposition: form-data; name="' . $name . "\"\r\n\r\n"
. $content . "\r\n";
}
$data .= "--" . static::$delimiter . $eol
. 'Content-Disposition: form-data; name="sqlfiles"; filename="' . $param['filename'] . '"' . "\r\n"
. 'Content-Type:application/octet-stream' . "\r\n\r\n";
$data .= $upload . "\r\n";
$data .= "--" . static::$delimiter . "--\r\n";
return $data;
}
function postFile($url, $param)
{
$post_data = static::buildData($param);
$curl = curl_init($url);
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
curl_setopt($curl, CURLOPT_POST, true);
curl_setopt($curl, CURLOPT_POSTFIELDS, $post_data);
curl_setopt($curl, CURLOPT_HTTPHEADER, [
"Content-Type: multipart/form-data; boundary=" . static::$delimiter,
"Content-Length: " . strlen($post_data)
]);
$response = curl_exec($curl);
curl_close($curl);
$info = json_decode($response, true);
return $info;
}
function postFrom($url, $data)
{
$headers = array('Content-Type: application/x-www-form-urlencoded');
$curl = curl_init();
curl_setopt($curl, CURLOPT_URL, $url);
curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, 0);
curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, 0);
curl_setopt($curl, CURLOPT_FOLLOWLOCATION, 1);
curl_setopt($curl, CURLOPT_AUTOREFERER, 1);
curl_setopt($curl, CURLOPT_POST, 1);
curl_setopt($curl, CURLOPT_POSTFIELDS, http_build_query($data));
curl_setopt($curl, CURLOPT_TIMEOUT, 30);
curl_setopt($curl, CURLOPT_HEADER, 0);
curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
$result = curl_exec($curl);
if (curl_errno($curl)) {
return 'Errno' . curl_error($curl);
}
curl_close($curl);
return json_decode($result, true);
}
function postJson($url, $data, $filePath)
{
$headers = array('Content-Type: application/x-www-form-urlencoded');
$curl = curl_init();
curl_setopt($curl, CURLOPT_URL, $url);
curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, 0);
curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, 0);
curl_setopt($curl, CURLOPT_FOLLOWLOCATION, 1);
curl_setopt($curl, CURLOPT_AUTOREFERER, 1);
curl_setopt($curl, CURLOPT_POST, 1);
curl_setopt($curl, CURLOPT_POSTFIELDS, http_build_query($data));
curl_setopt($curl, CURLOPT_TIMEOUT, 30);
curl_setopt($curl, CURLOPT_HEADER, 0);
curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
$result = curl_exec($curl);
if (curl_errno($curl)) {
return 'Errno' . curl_error($curl);
}
$fp = @fopen($filePath, "a");
fwrite($fp, $result);
fclose($fp);
}
}
python版本
总结
通过SQLFlow提供的一系列restful接口可以在我们的程序中操作获取到我们想要分析的SQL脚本的分析结果。
SQLFlow的地址：SQLFlow
460
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
