{"id":2757,"date":"2023-03-27T06:05:25","date_gmt":"2023-03-27T11:05:25","guid":{"rendered":"https:\/\/www.dpriver.com\/blog\/?p=2757"},"modified":"2023-03-27T06:06:18","modified_gmt":"2023-03-27T11:06:18","slug":"visualize-selected-data-with-sqlflow-rest-api-in-job-mode","status":"publish","type":"post","link":"https:\/\/www.dpriver.com\/blog\/2023\/03\/visualize-selected-data-with-sqlflow-rest-api-in-job-mode\/","title":{"rendered":"Visualize selected data with SQLFlow REST API in Job mode"},"content":{"rendered":"\n

Gudu SQLFlow<\/a><\/strong> is an analysis software for analyzing SQL statements and discovering data lineage<\/a><\/strong> relationships. It is often used with metadata management<\/strong><\/a> tools and is a basic tool for enterprise data governance<\/strong><\/a>. Check this introduction blog<\/a> if you don\u2019t know much about Gudu SQLFlow.<\/p>\n\n\n\n

Gudu SQLFlow can analyze your SQL data and generate report in different formats, including Json, XML, CSV and image(PNG\/JPEG)<\/strong>. Gudu SQLFlow provides you different choices to visualize your data, SQLFlow REST API<\/a> 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.<\/p>\n\n\n\n

In this article, we will show you how to create SQLFlow Job and visualize the selected data with SQLFlow REST API.<\/p>\n\n\n\n

Let’s say from database, we have got the following DDL:<\/p>\n\n\n\n

##

##

##

##

##

    CREATE VIEW vsal \nAS \n  SELECT a.deptno                  \"Department\", \n         a.num_emp \/ b.total_count \"Employees\", \n         a.sal_sum \/ b.total_sal   \"Salary\" \n  FROM   (SELECT deptno, \n                 Count()  num_emp, \n                 SUM(sal) sal_sum \n          FROM   scott.emp \n          WHERE  city = 'NYC' \n          GROUP  BY deptno) a, \n         (SELECT Count()  total_count, \n                 SUM(sal) total_sal \n          FROM   scott.emp \n          WHERE  city = 'NYC') b \n;\n\nINSERT ALL\n\tWHEN ottl < 100000 THEN\n\t\tINTO small_orders\n\t\t\tVALUES(oid, ottl, sid, cid)\n\tWHEN ottl > 100000 and ottl < 200000 THEN\n\t\tINTO medium_orders\n\t\t\tVALUES(oid, ottl, sid, cid)\n\tWHEN ottl > 200000 THEN\n\t\tinto large_orders\n\t\t\tVALUES(oid, ottl, sid, cid)\n\tWHEN ottl > 290000 THEN\n\t\tINTO special_orders\nSELECT o.order_id oid, o.customer_id cid, o.order_total ottl,\no.sales_rep_id sid, c.credit_limit cl, c.cust_email cem\nFROM orders o, customers c\nWHERE o.customer_id = c.customer_id;\n\ncreate table scott.dept(   \n  deptno     number(2,0),   \n  dname      varchar2(14),   \n  loc        varchar2(13),   \n  constraint pk_dept primary key (deptno)   \n);\n\ncreate table scott.emp(   \n  empno    number(4,0),   \n  ename    varchar2(10),   \n  job      varchar2(9),   \n  mgr      number(4,0),   \n  hiredate date,   \n  sal      number(7,2),   \n  comm     number(7,2),   \n  deptno   number(2,0),   \n  constraint pk_emp primary key (empno),\n  constraint fk_deptno foreign key (deptno) references dept (deptno) \n);<\/code><\/pre>\n\n\n\nThe API Endpoints<\/h2>\n\n\n\nThe related SQLFlow Api to visualize selected data is:<\/p>\n\n\n\n\/sqlflow\/generation\/sqlflow\/selectedgraph\/image<\/code><\/pre>\n\n\n\nThe selectedgraph\/image<\/code> 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<\/em> and get the session Id.<\/p>\n\n\n\nCreate SQLFlow Job<\/h2>\n\n\n\nThe following endpoint is to create a SQLFlow:<\/p>\n\n\n\n\/sqlflow\/job\/submitUserJob<\/code><\/pre>\n\n\n\n<\/figure>\n\n\n\nHint<\/em><\/strong>:\u00a0SQLFlow On-Premise<\/a>\u00a0is used in the above capture, hence token is not required and only userId is given in param. Please refer to\u00a0this document<\/a>\u00a0to check how to get the userid and the token.<\/p>\n\n\n\nWait few seconds and If we check our job list, we should have this demoJob2<\/em> in our job success list. You can verify that ethiter go to the SQLFlow interface or use another SQLFlow REST API \/sqlflow\/job\/displayUserJobsSummary<\/code>.<\/p>\n\n\n\n<\/figure>\n\n\n\nGreat! After checking the SQLFlow web, we have confirmed the SQLFlow Job is succeeded. <\/p>\n\n\n\nRetrieve Data Session<\/h2>\n\n\n\nNow we have our Job created and we need select our data now. To do that, invoke:<\/p>\n\n\n\n\/sqlflow\/job\/displayUserJobGraph<\/code><\/pre>\n\n\n\nGive the Job Id as the input and in the response of the above API, we will have the sessionId:<\/p>\n\n\n\n<\/figure>\n\n\n\nVisualize Selected Data<\/h2>\n\n\n\nWith the sessionId, now we can invoke \/sqlflow\/generation\/sqlflow\/selectedgraph\/image<\/code> to generate the selected data element.<\/p>\n\n\n\nLet’s say we want retrieve data lineage related to table<\/strong><\/em> orders<\/strong><\/em>:<\/p>\n\n\n\n<\/figure>\n\n\n\nSimple 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.<\/p>\n\n\n\nWe can also give other filters to select different data such as column, schema etc… <\/p>\n\n\n\nCheck this doc<\/a> to get all available parameters for the endpoint.<\/p>\n\n\n\nOnline Web Tool<\/h2>\n\n\n\nYou can get more information about SQLFlow<\/a> with the online demo<\/a>. 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!<\/p>\n","protected":false},"excerpt":{"rendered":"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\u2019t 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\u2026<\/p>\n","protected":false},"author":6,"featured_media":2761,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[66,93],"tags":[116],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nVisualize selected data with SQLFlow REST API in Job mode<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n\n\n\n\t\n\t\n\t\n