{"id":2789,"date":"2023-04-10T05:20:07","date_gmt":"2023-04-10T10:20:07","guid":{"rendered":"https:\/\/www.dpriver.com\/blog\/?p=2789"},"modified":"2023-04-10T05:22:07","modified_gmt":"2023-04-10T10:22:07","slug":"export-e-r-diagram-with-sqlflow-rest-api","status":"publish","type":"post","link":"https:\/\/www.dpriver.com\/blog\/2023\/04\/export-e-r-diagram-with-sqlflow-rest-api\/","title":{"rendered":"Export E-R Diagram with SQLFlow REST API"},"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

<\/figure>\n\n\n\n

SQLFlow<\/a> is capable to convert SQL to Entity-Relation(ER) Diagram<\/strong> as well as to visualize the relations between tables and fields so that you can quickly understand the design model of the database and conduct efficient team communication. You can either go to https:\/\/sqlflow.gudusoft.com<\/a> to visualize your database with E-R Diagram or you can use SQLFlow REST API to generate and to export the ER Diagram.<\/p>\n\n\n\n

In this blog, we will show you how to generate the E-R diagram with SQLFlow REST API.<\/p>\n\n\n\n

## The API Endpoint<\/h2>\n\n\n\n

The related SQLFlow Api to generate ER diagram image is:<\/p>\n\n\n\n

##

#####

#####

##

###

###

###

    \/sqlflow\/generation\/sqlflow\/erdiagramSelectGraph\/image<\/code><\/pre>\n\n\n\nThis API can directly take SQL statement text as input or it can read session id and generate the ER diagram based on the given session Id. Check the following parameter table:<\/p>\n\n\n\nParameter Name<\/strong><\/td>Type<\/strong><\/td>Description<\/strong><\/td><\/tr>userId<\/td>string<\/td>the user id of sqlflow client<\/td><\/tr>token<\/td>string<\/td>The token is only used when connecting to the SQLFlow Cloud server, it is not used when connect to the SQLFlow on-premise version.<\/td><\/tr>sessionId<\/td>string<\/td>graph session Id<\/td><\/tr>sqltext<\/td>string<\/td>SQL statement text <\/td><\/tr>jobId<\/td>string<\/td>give the job Id if need to use the job settings<\/td><\/tr>database<\/td>string<\/td>database<\/td><\/tr>dbvendor<\/td>string<\/td>database vendor<\/td><\/tr>defaultDatabase<\/td>string<\/td>default databse when there’s no metadata<\/td><\/tr>defaultSchema<\/td>string<\/td>default schema<\/td><\/tr>defaultServer<\/td>string<\/td>default server<\/td><\/tr>normalizeIdentifier<\/td>boolean<\/td>whether normalize identifier<\/td><\/tr>server<\/td>string<\/td>server to filter <\/td><\/tr>table<\/td>string<\/td>table to filter<\/td><\/tr>schema<\/td>string<\/td>schema to filter<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\nLet\u2019s say from database, we have got the following DDL:<\/p>\n\n\n\nCREATE 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\nGenerate ER diagram with SQL text<\/h2>\n\n\n\nTo generate ER diagram from SQL statement text, simply put the text in sqltext<\/code> parameter.<\/p>\n\n\n\nSample:<\/h5>\n\n\n\ncurl --location 'https:\/\/<sqlflow url>\/gspLive_backend\/sqlflow\/generation\/sqlflow\/erdiagramSelectGraph\/image' \\\n--header 'accept: image\/*' \\\n--form 'userId=\"gudu|0123456789\"' \\\n--form 'dbvendor=\"dbvoracle\"' \\\n--form 'sqltext=\"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\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\nResult:<\/h5>\n\n\n\n<\/figure>\n\n\n\nGenerate ER diagram using session Id<\/h2>\n\n\n\nCreate SQLFlow Job<\/h3>\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\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<\/h3>\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\nGenerate ER diagram<\/h3>\n\n\n\nWith the sessionId, now we can invoke \/sqlflow\/generation\/sqlflow\/erdiagramSelectGraph\/image<\/code> to generate the selected data element.<\/p>\n\n\n\ncurl --location 'https:\/\/<sqlflow url>\/gspLive_backend\/sqlflow\/generation\/sqlflow\/erdiagramSelectGraph\/image' \\\n--header 'accept: image\/*' \\\n--form 'userId=\"gudu|0123456789\"' \\\n--form 'dbvendor=\"dbvoracle\"' \\\n--form 'sessionId=\"10fde6b3e234dbd3db95f27d8af255f64c863a096131ef416d998f0969534057_1681121651772\"'<\/code><\/pre>\n\n\n\n<\/figure>\n\n\n\n<\/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. SQLFlow is capable to convert SQL to Entity-Relation(ER) Diagram as well as to visualize the relations between tables and fields so that you can quickly understand the design model of the database and conduct efficient team communication. You can either go to https:\/\/sqlflow.gudusoft.com to visualize your database with E-R Diagram or you can use SQLFlow REST\u2026<\/p>\n","protected":false},"author":6,"featured_media":2673,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[66,93],"tags":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nExport E-R Diagram with SQLFlow REST API<\/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