Visualize selected data with SQLFlow REST API in Job mode

Gudu SQLFlow is an analysis software for analyzing SQL statements and discovering data lineage relationships. It is often used with metadata management tools and is a basic tool for enterprise data governance. Check this introduction blog if you don’t know much about Gudu SQLFlow.

Gudu SQLFlow can analyze your SQL data and generate report in different formats, including Json, XML, CSV and image(PNG/JPEG). Gudu SQLFlow provides you different choices to visualize your data, SQLFlow REST API is one of the most commonly used features. Your data analysis result will be generated and returned from SQLFlow server by sending standard parameterized RESTFul Api request.

In this article, we will show you how to create SQLFlow Job and visualize the selected data with SQLFlow REST API.

Let’s say from database, we have got the following DDL:

CREATE VIEW vsal 
AS 
  SELECT a.deptno                  "Department", 
         a.num_emp / b.total_count "Employees", 
         a.sal_sum / b.total_sal   "Salary" 
  FROM   (SELECT deptno, 
                 Count()  num_emp, 
                 SUM(sal) sal_sum 
          FROM   scott.emp 
          WHERE  city = 'NYC' 
          GROUP  BY deptno) a, 
         (SELECT Count()  total_count, 
                 SUM(sal) total_sal 
          FROM   scott.emp 
          WHERE  city = 'NYC') b 
;

INSERT ALL
	WHEN ottl < 100000 THEN
		INTO small_orders
			VALUES(oid, ottl, sid, cid)
	WHEN ottl > 100000 and ottl < 200000 THEN
		INTO medium_orders
			VALUES(oid, ottl, sid, cid)
	WHEN ottl > 200000 THEN
		into large_orders
			VALUES(oid, ottl, sid, cid)
	WHEN ottl > 290000 THEN
		INTO special_orders
SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,
o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem
FROM orders o, customers c
WHERE o.customer_id = c.customer_id;

create table scott.dept(   
  deptno     number(2,0),   
  dname      varchar2(14),   
  loc        varchar2(13),   
  constraint pk_dept primary key (deptno)   
);

create table scott.emp(   
  empno    number(4,0),   
  ename    varchar2(10),   
  job      varchar2(9),   
  mgr      number(4,0),   
  hiredate date,   
  sal      number(7,2),   
  comm     number(7,2),   
  deptno   number(2,0),   
  constraint pk_emp primary key (empno),
  constraint fk_deptno foreign key (deptno) references dept (deptno) 
);

The API Endpoints

The related SQLFlow Api to visualize selected data is:

/sqlflow/generation/sqlflow/selectedgraph/image

The selectedgraph/image will take the session Id as the input and it generates the data lineage image based on the given sessionId. Therefore, before invoking the above api to get the selected data, we have to firstly call a SQLFlow endpoint to analyze the database/SQL file/ SQL text so that we can select data and get the session Id.

Create SQLFlow Job

The following endpoint is to create a SQLFlow:

/sqlflow/job/submitUserJob

Hint: SQLFlow On-Premise is used in the above capture, hence token is not required and only userId is given in param. Please refer to this document to check how to get the userid and the token.

Wait few seconds and If we check our job list, we should have this demoJob2 in our job success list. You can verify that ethiter go to the SQLFlow interface or use another SQLFlow REST API /sqlflow/job/displayUserJobsSummary.

Great! After checking the SQLFlow web, we have confirmed the SQLFlow Job is succeeded.

Retrieve Data Session

Now we have our Job created and we need select our data now. To do that, invoke:

/sqlflow/job/displayUserJobGraph

Give the Job Id as the input and in the response of the above API, we will have the sessionId:

Visualize Selected Data

With the sessionId, now we can invoke /sqlflow/generation/sqlflow/selectedgraph/image to generate the selected data element.

Let’s say we want retrieve data lineage related to table orders:

Simple give the table name that we want in the request and the data lineage image will be returned. In this example we are not giving the previous job id that we created, you can give that Id if you prefer to use the Job settings when you created the SQLFlow Job for the generated image.

We can also give other filters to select different data such as column, schema etc…

Check this doc to get all available parameters for the endpoint.

Online Web Tool

You can get more information about SQLFlow with the online demo. The online web tool uses SQLFlow API to generate the result. You can also build your own web tool with the help of SQLFlow REST API. Check this fantastic data visualization tool today!