Usages

1. Visualize sqltext

Visualize the data lineage after analyzing the input SQL query.

  • input: SQL text

  • output: data lineage diagram

Find the example code under the directory:

└── 1\
$(async () => {
    // get a instance of SQLFlow
    const sqlflow = await SQLFlow.init({
        container: document.getElementById('sqlflow'),
        width: 1000,
        height: 315,
        apiPrefix: 'http://xxx.com/api',
        token: '', // input your token
    });

    // set dbvendor property
    sqlflow.vendor.set('oracle');

    // set sql text property
    sqlflow.sqltext.set(`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 
    ;`);

    sqlflow.visualize();
});

2. Visualize job

Visualize the data lineage in a SQLFlow Job. The SQLFlow job must be already created.

  • input: a SQLFlow job id, or leave it empty to view the latest job

  • output: data lineage diagram

Find the example code under the directory:

└── 2\
$(async () => {
    const sqlflow = await SQLFlow.init({
        container: document.getElementById('demo-2'),
        width: 1000,
        height: 800,
        apiPrefix: 'http://xxx.com/api',
    });

    // view job detail by job id, or leave it empty to view the latest job
    await sqlflow.job.lineage.viewDetailById('b38273ec356d457bb98c6b3159c53be3');
});

3. Visualize job with hard coded parameters

Visualize the data lineage of a specified table or column in a SQLFlow job.

  • input: a SQLFlow job id, or leave it empty to view the latest job

  • input: database, schema, table, column.

    • If the column is omitted, the data lineage for the specified table will be returned.

    • if the table and column are ommited, the data lineage for the specified schema will be returned.

    • if the schema, table and column are ommited, the data lineage for the specified database will be returned.

  • output: data lineage diagram

Find the example code under the directory:

└── 3\
$(async () => {
    const sqlflow = await SQLFlow.init({
        container: document.getElementById('sqlflow'),
        width: 1000,
        height: 700,
        apiPrefix: 'http://xxx.com/api',
    });

    // view job detail by job id, or leave it empty to view the latest job
    await sqlflow.job.lineage.viewDetailById('b38273ec356d457bb98c6b3159c53be3');

    sqlflow.job.lineage.selectGraph({
        database: 'DWDB', //
        schema: 'DBO',
        table: '#TMP',
        column: 'NUMBER_OFOBJECTS',
    });
});

4. Visualize job with parameters

Visualize the data lineage of a specified table or column in a SQLFlow job.

  • input: a SQLFlow job id, or leave it empty to view the latest job

  • input: database, schema, table, column.

    • If the column is omitted, the data lineage for the specified table will be returned.

    • if the table and column are ommited, the data lineage for the specified schema will be returned.

    • if the schema, table and column are ommited, the data lineage for the specified database will be returned.

  • output: data lineage diagram

Find the example code under the directory:

└── 4\
$(async () => {
    const $jobid = $('#jobid');
    const $database = $('#database');
    const $schema = $('#schema');
    const $table = $('#table');
    const $column = $('#column');
    const $recordset = $('#recordset');
    const $function = $('#function');
    const $visualize = $('#visualize');

    const sqlflow = await SQLFlow.init({
        container: document.getElementById('sqlflow'),
        width: 1200,
        height: 800,
        apiPrefix: 'http://xxx.com/api',
        token: '', // input your token
    });

    const visualize = async () => {
        // reset default option
        $recordset.prop('checked', false);
        $function.prop('checked', false);

        // view job detail by job id, or leave it empty to view the latest job
        await sqlflow.job.lineage.viewDetailById($jobid.val());

        sqlflow.job.lineage.selectGraph({
            database: $database.val(),
            schema: $schema.val(),
            table: $table.val(),
            column: $column.val(),
        });
    };

    visualize();

    $visualize.click(visualize);

    $recordset.change(() => {
        const checked = $recordset.prop('checked');
        sqlflow.setting.showIntermediateRecordset.set(checked);
    });

    $function.change(() => {
        const checked = $function.prop('checked');
        sqlflow.setting.showFunction.set(checked);
    });
});

5. Set data lineage options of SQL query

Generate the data lineage of a SQL query with different input parameters.

Find the example code under the directory:

└── 5\
$(async () => {
    const $sqltext = $('#sqltext');
    const $dataflow = $('#dataflow');
    const $impact = $('#impact');
    const $args = $('#args');
    const $recordset = $('#recordset');
    const $function = $('#function');
    const $constant = $('#constant');
    const $transform = $('#transform');
    const $visualize = $('#visualize');
    const $visualizeTableLevel = $('#visualizeTableLevel');

    // get a instance of SQLFlow
    const sqlflow = await SQLFlow.init({
        container: document.getElementById('sqlflow'),
        width: 1000,
        height: 800,
        apiPrefix: 'http://xxx.com/api',
        token: '', // input your token
    });

    // set dbvendor property
    sqlflow.vendor.set('oracle');

    const visualize = async () => {
        // set sql text property
        sqlflow.sqltext.set($sqltext.val());

        // set default options
        $recordset.prop('checked', true);
        $dataflow.prop('checked', true);
        $args.prop('checked', true);
        $impact.prop('checked', false);
        $function.prop('checked', false);
        $constant.prop('checked', true);
        $transform.prop('checked', false);

        sqlflow.visualize();
    };

    visualize();

    $visualize.click(visualize);

    const visualizeTableLevel = async () => {
        // set sql text property
        sqlflow.sqltext.set($sqltext.val());
        sqlflow.visualizeTableLevel();
    };

    $visualizeTableLevel.click(visualizeTableLevel);

    $dataflow.change(() => {
        const checked = $dataflow.prop('checked');
        sqlflow.setting.dataflow.set(checked);
    });

    $impact.change(() => {
        const checked = $impact.prop('checked');
        sqlflow.setting.impact.set(checked);
    });

    $args.change(() => {
        const checked = $args.prop('checked');
        sqlflow.setting.dataflowOfAggregateFunction.set(checked);
    });

    $recordset.change(() => {
        const checked = $recordset.prop('checked');
        sqlflow.setting.showIntermediateRecordset.set(checked);
    });

    $function.change(() => {
        const checked = $function.prop('checked');
        sqlflow.setting.showFunction.set(checked);
    });

    $constant.change(() => {
        const checked = $constant.prop('checked');
        sqlflow.setting.showConstant.set(checked);
    });
    $transform.change(() => {
        const checked = $transform.prop('checked');
        sqlflow.setting.showTransform.set(checked);
    });
});

6. Visualize an embedded json object in html page

SQLFlow will visualize the json object which contains the data lineage information and will show the actionable diagram.

Since all layout data is included in the json file, the SQLFlow widget will draw the diagram and needn't to connect to the SQLFlow backend.

So this SQLFlow widget can work without the installation of the Gudu SQLFlow.

Find the example code under the directory:

└── 6\

The format of the Json is described in https://docs.gudusoft.com/7.-reference/lineage-model/json-format-lineage-model#id-4.-dbobjs-payload

The payload is the sqlflow part of the response in the graph interface:

{
  "dbobjs": {
    "createdBy": "sqlflow v1.0.0",
    "servers": [
      {
        "name": "DEFAULT_SERVER",
        "dbVendor": "dbvoracle",
        "supportsCatalogs": true,
        "supportsSchemas": true,
        "databases": [
          {
            "name": "DEFAULT",
            "schemas": [
              {
                "name": "DEFAULT",
                "tables": [
                  {
                    "id": "95",
                    "name": "customers",
                    "displayName": "customers",
                    "type": "table",
                    "columns": [
                      {
                        "id": "96",
                        "name": "credit_limit",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 21
                          },
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 35
                          }
                        ]
                      },
                      {
                        "id": "97",
                        "name": "cust_email",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 40
                          },
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 52
                          }
                        ]
                      },
                      {
                        "id": "98",
                        "name": "customer_id",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 33,
                            "y": 23
                          },
                          {
                            "hashCode": "0",
                            "x": 33,
                            "y": 36
                          }
                        ]
                      }
                    ],
                    "coordinates": [
                      {
                        "hashCode": "0",
                        "x": 32,
                        "y": 16
                      },
                      {
                        "hashCode": "0",
                        "x": 32,
                        "y": 27
                      }
                    ]
                  },
                  {
                    "id": "75",
                    "name": "large_orders",
                    "displayName": "large_orders",
                    "type": "table",
                    "columns": [
                      {
                        "id": "74",
                        "name": "RelationRows",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 26,
                            "y": 8
                          },
                          {
                            "hashCode": "0",
                            "x": 26,
                            "y": 20
                          }
                        ],
                        "source": "system",
                        "uiVisible": false
                      },
                      {
                        "id": "124",
                        "name": "cem",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 53
                          },
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 56
                          }
                        ]
                      },
                      {
                        "id": "138",
                        "name": "cid",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 27,
                            "y": 27
                          },
                          {
                            "hashCode": "0",
                            "x": 27,
                            "y": 30
                          }
                        ]
                      },
                      {
                        "id": "123",
                        "name": "cl",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 36
                          },
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 38
                          }
                        ]
                      },
                      {
                        "id": "135",
                        "name": "oid",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 27,
                            "y": 11
                          },
                          {
                            "hashCode": "0",
                            "x": 27,
                            "y": 14
                          }
                        ]
                      },
                      {
                        "id": "136",
                        "name": "ottl",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 27,
                            "y": 16
                          },
                          {
                            "hashCode": "0",
                            "x": 27,
                            "y": 20
                          }
                        ]
                      },
                      {
                        "id": "137",
                        "name": "sid",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 27,
                            "y": 22
                          },
                          {
                            "hashCode": "0",
                            "x": 27,
                            "y": 25
                          }
                        ]
                      }
                    ],
                    "coordinates": [
                      {
                        "hashCode": "0",
                        "x": 26,
                        "y": 8
                      },
                      {
                        "hashCode": "0",
                        "x": 26,
                        "y": 20
                      }
                    ]
                  },
                  {
                    "id": "67",
                    "name": "medium_orders",
                    "displayName": "medium_orders",
                    "type": "table",
                    "columns": [
                      {
                        "id": "66",
                        "name": "RelationRows",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 23,
                            "y": 8
                          },
                          {
                            "hashCode": "0",
                            "x": 23,
                            "y": 21
                          }
                        ],
                        "source": "system",
                        "uiVisible": false
                      },
                      {
                        "id": "118",
                        "name": "cem",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 53
                          },
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 56
                          }
                        ]
                      },
                      {
                        "id": "142",
                        "name": "cid",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 24,
                            "y": 27
                          },
                          {
                            "hashCode": "0",
                            "x": 24,
                            "y": 30
                          }
                        ]
                      },
                      {
                        "id": "117",
                        "name": "cl",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 36
                          },
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 38
                          }
                        ]
                      },
                      {
                        "id": "139",
                        "name": "oid",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 24,
                            "y": 11
                          },
                          {
                            "hashCode": "0",
                            "x": 24,
                            "y": 14
                          }
                        ]
                      },
                      {
                        "id": "140",
                        "name": "ottl",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 24,
                            "y": 16
                          },
                          {
                            "hashCode": "0",
                            "x": 24,
                            "y": 20
                          }
                        ]
                      },
                      {
                        "id": "141",
                        "name": "sid",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 24,
                            "y": 22
                          },
                          {
                            "hashCode": "0",
                            "x": 24,
                            "y": 25
                          }
                        ]
                      }
                    ],
                    "coordinates": [
                      {
                        "hashCode": "0",
                        "x": 23,
                        "y": 8
                      },
                      {
                        "hashCode": "0",
                        "x": 23,
                        "y": 21
                      }
                    ]
                  },
                  {
                    "id": "87",
                    "name": "orders",
                    "displayName": "orders",
                    "type": "table",
                    "columns": [
                      {
                        "id": "89",
                        "name": "customer_id",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 30,
                            "y": 24
                          },
                          {
                            "hashCode": "0",
                            "x": 30,
                            "y": 37
                          }
                        ]
                      },
                      {
                        "id": "88",
                        "name": "order_id",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 30,
                            "y": 8
                          },
                          {
                            "hashCode": "0",
                            "x": 30,
                            "y": 18
                          }
                        ]
                      },
                      {
                        "id": "90",
                        "name": "order_total",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 30,
                            "y": 43
                          },
                          {
                            "hashCode": "0",
                            "x": 30,
                            "y": 56
                          }
                        ]
                      },
                      {
                        "id": "91",
                        "name": "sales_rep_id",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 1
                          },
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 15
                          }
                        ]
                      }
                    ],
                    "coordinates": [
                      {
                        "hashCode": "0",
                        "x": 32,
                        "y": 6
                      },
                      {
                        "hashCode": "0",
                        "x": 32,
                        "y": 14
                      }
                    ]
                  },
                  {
                    "id": "58",
                    "name": "small_orders",
                    "displayName": "small_orders",
                    "type": "table",
                    "columns": [
                      {
                        "id": "57",
                        "name": "RelationRows",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 20,
                            "y": 8
                          },
                          {
                            "hashCode": "0",
                            "x": 20,
                            "y": 20
                          }
                        ],
                        "source": "system",
                        "uiVisible": false
                      },
                      {
                        "id": "112",
                        "name": "cem",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 53
                          },
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 56
                          }
                        ]
                      },
                      {
                        "id": "134",
                        "name": "cid",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 21,
                            "y": 27
                          },
                          {
                            "hashCode": "0",
                            "x": 21,
                            "y": 30
                          }
                        ]
                      },
                      {
                        "id": "111",
                        "name": "cl",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 36
                          },
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 38
                          }
                        ]
                      },
                      {
                        "id": "131",
                        "name": "oid",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 21,
                            "y": 11
                          },
                          {
                            "hashCode": "0",
                            "x": 21,
                            "y": 14
                          }
                        ]
                      },
                      {
                        "id": "132",
                        "name": "ottl",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 21,
                            "y": 16
                          },
                          {
                            "hashCode": "0",
                            "x": 21,
                            "y": 20
                          }
                        ]
                      },
                      {
                        "id": "133",
                        "name": "sid",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 21,
                            "y": 22
                          },
                          {
                            "hashCode": "0",
                            "x": 21,
                            "y": 25
                          }
                        ]
                      }
                    ],
                    "coordinates": [
                      {
                        "hashCode": "0",
                        "x": 20,
                        "y": 8
                      },
                      {
                        "hashCode": "0",
                        "x": 20,
                        "y": 20
                      }
                    ]
                  },
                  {
                    "id": "83",
                    "name": "special_orders",
                    "displayName": "special_orders",
                    "type": "table",
                    "columns": [
                      {
                        "id": "82",
                        "name": "RelationRows",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 29,
                            "y": 8
                          },
                          {
                            "hashCode": "0",
                            "x": 29,
                            "y": 22
                          }
                        ],
                        "source": "system",
                        "uiVisible": false
                      },
                      {
                        "id": "130",
                        "name": "cem",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 53
                          },
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 56
                          }
                        ]
                      },
                      {
                        "id": "126",
                        "name": "cid",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 30,
                            "y": 38
                          },
                          {
                            "hashCode": "0",
                            "x": 30,
                            "y": 41
                          }
                        ]
                      },
                      {
                        "id": "129",
                        "name": "cl",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 36
                          },
                          {
                            "hashCode": "0",
                            "x": 31,
                            "y": 38
                          }
                        ]
                      },
                      {
                        "id": "125",
                        "name": "oid",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 30,
                            "y": 19
                          },
                          {
                            "hashCode": "0",
                            "x": 30,
                            "y": 22
                          }
                        ]
                      },
                      {
                        "id": "127",
                        "name": "ottl",
                        "coordinates": [
                          {
                            "hashCode": "0",
                            "x": 30,
                            "y": 57
                          },
                          {
                            "hashCode": "0",
                            "x": 30,
                            "y": 61
                          }
                        ]
                      },
                      {
                        "id": "128",
                        "name": "sid",
                        "coordinates": [
                          {