Skip to content
No results
We use cookies to ensure that we give you the best experience on our website.

# Export E-R Diagram with SQLFlow REST API

- leo gu
- April 10, 2023
- Data Governance, SQLFlow

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.

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 API to generate and to export the ER Diagram.

In this blog, we will show you how to generate the E-R diagram with SQLFlow REST API.

## The API Endpoint

The related SQLFlow Api to generate ER diagram image is:

    /sqlflow/generation/sqlflow/erdiagramSelectGraph/image

This 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:

Parameter Name | Type | Description
userId | string | the user id of sqlflow client
token | string | The token is only used when connecting to the SQLFlow Cloud server, it is not used when connect to the SQLFlow on-premise version.
sessionId | string | graph session Id
sqltext | string | SQL statement text
jobId | string | give the job Id if need to use the job settings
database | string | database
dbvendor | string | database vendor
defaultDatabase | string | default databse when there’s no metadata
defaultSchema | string | default schema
defaultServer | string | default server
normalizeIdentifier | boolean | whether normalize identifier
server | string | server to filter
table | string | table to filter
schema | string | schema to filter

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)
    );

## Generate ER diagram with SQL text

To generate ER diagram from SQL statement text, simply put the text in `sqltext` parameter.

##### Sample:

    curl --location 'https://<sqlflow url>/gspLive_backend/sqlflow/generation/sqlflow/erdiagramSelectGraph/image' \
    --header 'accept: image/*' \
    --form 'userId="gudu|0123456789"' \
    --form 'dbvendor="dbvoracle"' \
    --form 'sqltext="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)
    );"'

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.

##### Result:

## Generate ER diagram using session Id

### Create SQLFlow Job

The following endpoint is to create a SQLFlow:

    /sqlflow/job/submitUserJob

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:

### Generate ER diagram

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

    curl --location 'https://<sqlflow url>/gspLive_backend/sqlflow/generation/sqlflow/erdiagramSelectGraph/image' \
    --header 'accept: image/*' \
    --form 'userId="gudu|0123456789"' \
    --form 'dbvendor="dbvoracle"' \
    --form 'sessionId="10fde6b3e234dbd3db95f27d8af255f64c863a096131ef416d998f0969534057_1681121651772"'

### Newsletter Updates

Enter your email address below to subscribe to our newsletter

I accept the Privacy Policy

### Related Posts

#### Ingest MySQL metadata on Datahub

- July 11, 2023

#### Integrate SQLFlow into Datahub

- July 7, 2023

#### Visualize your upstream/downstream data with SQLFlow REST API

- April 11, 2023