{"id":2726,"date":"2023-03-23T23:23:32","date_gmt":"2023-03-24T04:23:32","guid":{"rendered":"https:\/\/www.dpriver.com\/blog\/?p=2726"},"modified":"2023-03-27T04:21:18","modified_gmt":"2023-03-27T09:21:18","slug":"visualize-your-data-with-sqlflow-rest-api","status":"publish","type":"post","link":"https:\/\/www.dpriver.com\/blog\/2023\/03\/visualize-your-data-with-sqlflow-rest-api\/","title":{"rendered":"Visualize your data 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’t know much about Gudu SQLFlow.<\/p>\n\n\n\n

Gudu SQLFlow has various functionalities for data lineage generation and it 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. Apart from report under Json<\/em><\/strong>, XML<\/em><\/strong>, CSV <\/em><\/strong>format, SQLFlow REST API is also able to return result as image(PNG\/JPEG)<\/strong> from your SQL.<\/p>\n\n\n\n

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

The related SQLFlow Api to visualize your data lineage is: <\/p>\n\n\n\n

- ##

  ##

  ###

  - - - - ##

    \/sqlflow\/generation\/sqlflow\/graph\/image<\/code><\/pre>\n\n\n\nThe \/graph\/image<\/code> accepts either SQL statements text or it can reads from SQL file. Export your database metadata into SQL files if you would like to generate data lineage image for your database with \/graph\/image<\/code>. (tips: you can use SQLFlow Ingester<\/a> to export metadata files from your database)<\/p>\n\n\n\nLet\u2019s use the following SQL statements to see how to sort out the data dependencies between various tables\/views with a simple call to SQLFlow REST API:<\/p>\n\n\n\n<\/figure>\n\n\n\nBy analyzing the above INSERT SQL statement, we can know:<\/p>\n\n\n\nThe data of the deptsal table comes from the dept and emp tables. Further, the data dependency (data lineage) at the field level is:<\/p>\n\n\n\nThe data of the deptsal.dept_no field comes from dept.deptno\uff1b<\/li>The data of the deptsal.dept_name field comes from dept.name\uff1b<\/li>The data of the deptsal.salary field comes from emp.sal and emp.comm\uff1b<\/li><\/ul>\n\n\n\nSimply upload the above piece of code to SQLFlow API (or you can put the SQL statements in SQL file in case of the sql statement is complex):<\/p>\n\n\n\ncurl --location --request POST 'https:\/\/api.gudusoft.com\/gspLive_backend\/sqlflow\/generation\/sqlflow\/graph\/image' \\\n    --header 'accept: image\/*' \\\n    --form 'sqlfile=@\"\/home\/test.sql\"' \\\n    --form 'dbvendor=\"dbvoracle\"' \\\n    --form 'userId=\"<YOUR USER ID>\"' \\\n    --form 'token=\"<THE GENERATED TOKEN>\"'<\/code><\/pre>\n\n\n\nResult:<\/p>\n\n\n\n<\/figure>\n\n\n\nHint: SQLFlow On-Premise<\/a> is used in the above capture, hence token is not required and only userId is given in param. Please refer to this document<\/a> to check how to get the userid and the token.<\/p>\n\n\n\nRequest Parameters<\/h2>\n\n\n\nIn the above request, only sqlfile<\/code>, dbvendor<\/code> and userId<\/code> are given as the request parameters. The sqlfile is the file that you are going to upload. Use sqltext<\/code> If you prefer use SQL text. The dbvendor<\/code> tells SQLFlow to what database this SQL is based on. Other request parameters are not required and have default value.  However, you can apply all SQLFlow settings to customize the output image. Check the following table:<\/p>\n\n\n\nField name<\/td>Type<\/td>Description<\/td><\/tr>dbvendor<\/td>string<\/td>database vendor, support values: – dbvazuresql
    – dbvbigquery
    – dbvcouchbase
    – dbvdb2
    – dbvgreenplum
    – dbvhana
    – dbvhive
    – dbvimpala
    – dbvinformix
    – dbvmdx
    – dbvmysql
    – dbvnetezza
    – dbvopenedge
    – dbvoracle
    – dbvpostgresql
    – dbvredshift
    – dbvsnowflake
    – dbvmssql
    – dbvsparksql
    – dbvsybase
    – dbvteradata
    – dbvvertica<\/td><\/tr>hideColumn<\/td>boolean<\/td>whether hide column<\/td><\/tr>ignoreFunction<\/td>boolean<\/td>whether ignore function<\/td><\/tr>simpleOutput<\/td>boolean<\/td>simple output, ignore the intermediate results, defualt is false.<\/td><\/tr>ignoreRecordSet<\/td>boolean<\/td>same as simpleOutput, but will keep output of the top level select
    list, default is false.<\/td><\/tr>jobId<\/td>string<\/td>give the job Id if need to use the job settings and display the
    job graph<\/td><\/tr>normalizeIdentifier<\/td>boolean<\/td>whether normalize the Identifier, default is true<\/td><\/tr>showTransform<\/td>boolean<\/td>whether show transform<\/td><\/tr>sqltext<\/td>string<\/td>sql text from which to generate the sqlflow model<\/td><\/tr>token<\/td>string<\/td>The token is only used when connecting to the SQLFlow Cloud server,
    It’s not in use when connect to the SQLFlow on-premise version.<\/td><\/tr>treatArgumentsInCountFunctionAsDirectDataflow<\/td>boolean<\/td>Whether treat the arguments in COUNT function as direct Dataflow<\/td><\/tr>userId<\/td>string<\/td>the user id of sqlflow web or client<\/td><\/tr>columnLevel<\/td>string<\/td>whether to show table level or column level data, false or true<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\nImage Data Lineage Result<\/h2>\n\n\n\n<\/figure>\n\n\n\nLet’s use the following settings and make the output more easier to understand:<\/p>\n\n\n\nField name<\/td>Value<\/td><\/tr>showRelationType<\/td>fdd<\/td><\/tr>treatArgumentsInCountFunctionAsDirectDataflow<\/td>true<\/td><\/tr>ignoreRecordSet<\/td>false<\/td><\/tr>ignoreFunction<\/td>true<\/td><\/tr>showConstantTable<\/td>false<\/td><\/tr>showTransform<\/td>false<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<\/figure>\n\n\n\nAll database objects discovered during the data lineage analysis are stored in the $.dbobjs<\/code> object.<\/p>\n\n\n\nTable<\/h3>\n\n\n\nThere are four tables founded in the above result:<\/p>\n\n\n\nDEPTSAL: you can use $.dbobjs[1].name<\/code> to return the table name, and $.dbobjs[1].type<\/code> to return the type of this object which is table<\/code> in this case. you can also use expression like this to get this table:<\/li><\/ul>\n\n\n\n$.dbobjs[?(@.name=='deptsal')].name<\/code><\/pre>\n\n\n\nDEPT<\/li><\/ul>\n\n\n\n$.dbobjs[?(@.name=='dept')].name<\/code><\/pre>\n\n\n\nEMP<\/li><\/ul>\n\n\n\n$.dbobjs[?(@.name=='emp')].name<\/code><\/pre>\n\n\n\nSQL_CONSTANTS: This is not a real table, but a table generated by the Gudu SQLFlow to store the constant used in the SQL query.<\/li><\/ul>\n\n\n\n$.dbobjs[?(@.name=='SQL_CONSTANTS')].name<\/code><\/pre>\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’t know much about Gudu SQLFlow. Gudu SQLFlow has various functionalities for data lineage generation and it 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. Apart from report under Json, XML, CSV\u2026<\/p>\n","protected":false},"author":6,"featured_media":2729,"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":"\nVisualize your data 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