{"id":2821,"date":"2023-04-11T04:43:24","date_gmt":"2023-04-11T09:43:24","guid":{"rendered":"https:\/\/www.dpriver.com\/blog\/?p=2821"},"modified":"2023-04-11T04:52:45","modified_gmt":"2023-04-11T09:52:45","slug":"visualize-your-upstream-downstream-data-with-sqlflow-rest-api","status":"publish","type":"post","link":"https:\/\/www.dpriver.com\/blog\/2023\/04\/visualize-your-upstream-downstream-data-with-sqlflow-rest-api\/","title":{"rendered":"Visualize your upstream\/downstream 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\u2019t know much about Gudu SQLFlow.<\/p>\n\n\n\n

When dealing with complex SQL statements, we can check the data upstream or data downstream analysis by using the to upstream\/to downstream<\/strong> feature of SQLFlow.<\/p>\n\n\n\n

## To upstream\/downstream<\/h2>\n\n\n\n

SQLFlow UI provides the upstream\/downstream data analysis in regular SQLFlow Job<\/a><\/strong> by right clicking the data element.<\/p>\n\n\n\n

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

The upstream of the data gives the source lineage for the selected data. Given full data lineage `a -> b -> c<\/code>, if data element b is selected for its upstream, the upstream lineage would be `a -> b<\/code>.<\/p>\n\n\n\n``

On the contrary, the downstream of the data returns the affected lineage for the selected data. Given full data lineage `a -> b -> c<\/code>, if data element b is selected for its upstream, the upstream lineage would be `b -> c<\/code>.<\/p>\n\n\n\n``

Let’s take the following data lineage as an example:<\/p>\n\n\n\n

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

The upstream of the hr.dbo.countries would be: <\/p>\n\n\n\n

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

The downstream of the hr.dbo.countries is:<\/p>\n\n\n\n

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

## SQLFlow REST API<\/h2>\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

To visualize the data lineage upstream\/downstream with SQLFlow REST API, we need to invoke<\/p>\n\n\n\n

###

###

##

    \/sqlflow\/generation\/sqlflow\/upstreamGraph\/image<\/code><\/pre>\n\n\n\n\/sqlflow\/generation\/sqlflow\/downstreamGraph\/image<\/code><\/pre>\n\n\n\nThese two endpoints take sessionId<\/code> as input. There are two ways to get sessionId<\/code>:<\/p>\n\n\n\n1. Invoke data lineage API to get sessionId<\/h3>\n\n\n\nSample:<\/p>\n\n\n\ncurl --location 'https:\/\/<SQLFlow url>\/gspLive_backend\/sqlflow\/generation\/sqlflow\/graph' \\\n--header 'accept: application\/json;charset=utf-8' \\\n--form 'sqlfile=@\"\/home\/workspace\/test2.sql\"' \\\n--form 'dbvendor=\"dbvoracle\"' \\\n--form 'userId=\"gudu|0123456789\"'<\/code><\/pre>\n\n\n\n<\/figure>\n\n\n\n2. Create 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\nNow we have our Job created and we need to 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\nGet upstream\/downstream lineage <\/h2>\n\n\n\nNow we are good to invoke the upstream\/downstream SQLFlow Api with the sessionId<\/code> ready. You can use tableNamePattern<\/code> to map the desired table. By default the top SQLFlow Job is taken If sessionId is not given. Full parameter table:<\/p>\n\n\n\nField name<\/strong><\/td>Type<\/strong><\/td>Description<\/strong><\/td><\/tr>dbvendor<\/td>string<\/td>database vendor, support values: \u2013 dbvazuresql
    \u2013 dbvbigquery
    \u2013 dbvcouchbase
    \u2013 dbvdb2
    \u2013 dbvgreenplum
    \u2013 dbvhana
    \u2013 dbvhive
    \u2013 dbvimpala
    \u2013 dbvinformix
    \u2013 dbvmdx
    \u2013 dbvmysql
    \u2013 dbvnetezza
    \u2013 dbvopenedge
    \u2013 dbvoracle
    \u2013 dbvpostgresql
    \u2013 dbvredshift
    \u2013 dbvsnowflake
    \u2013 dbvmssql
    \u2013 dbvsparksql
    \u2013 dbvsybase
    \u2013 dbvteradata
    \u2013 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>token<\/td>string<\/td>The token is only used when connecting to the SQLFlow Cloud server,
    It\u2019s 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>isReturnModel<\/td>boolean<\/td>whether to return model<\/td><\/tr>sessionId<\/td>string<\/td>the graph session Id<\/td><\/tr>showConstantTable<\/td>boolean<\/td>whether show constant table<\/td><\/tr>showLinkOnly<\/td>boolean<\/td>whether show link only<\/td><\/tr>showRelationType<\/td>string<\/td>show relation type, required false, default value is \u2018fdd\u2019, multiple
    values seperated by comma like fdd,frd,fdr,join. Availables are \u2018fdd\u2019 value
    of target column from source column, \u2018frd\u2019 the recordset count of target
    column which is affected by value of source column, \u2018fdr\u2019 value of target
    column which is affected by the recordset count of source column, \u2018join\u2019
    combines rows from two or more tables, based on a related column between
    them<\/td><\/tr>tableColumn<\/td>string<\/td>target table column<\/td><\/tr>tableNamePattern<\/td>string<\/td>target table name, supports regex expression<\/td><\/tr>distance<\/td>integer<\/td>graph element distance<\/td><\/tr>stopat<\/td>string<\/td>stop at <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\nUpstream Sample:<\/strong><\/p>\n\n\n\ncurl --location 'https:\/\/<SQLFlow URL>\/gspLive_backend\/sqlflow\/generation\/sqlflow\/upstreamGraph\/image' \\\n--header 'accept: image\/*' \\\n--form 'userId=\"gudu|0123456789\"' \\\n--form 'sessionId=\"de3882d463c247ce82c2552d260f3661_1681194645814\"' \\\n--form 'dbvendor=\"dbvmssql\"' \\\n--form 'jobId=\"de3882d463c247ce82c2552d260f3661\"' \\\n--form 'tableNamePattern=\"hr.dbo.countries\"'<\/code><\/pre>\n\n\n\n<\/figure>\n\n\n\nDownstream Sample:<\/strong><\/p>\n\n\n\ncurl --location 'https:\/\/<SQLFlow URL>\/gspLive_backend\/sqlflow\/generation\/sqlflow\/downstreamGraph\/image' \\\n--header 'accept: image\/*' \\\n--form 'userId=\"gudu|0123456789\"' \\\n--form 'sessionId=\"de3882d463c247ce82c2552d260f3661_1681194645814\"' \\\n--form 'dbvendor=\"dbvmssql\"' \\\n--form 'jobId=\"de3882d463c247ce82c2552d260f3661\"' \\\n--form 'tableNamePattern=\"hr.dbo.countries\"'<\/code><\/pre>\n\n\n\n<\/figure>\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. When dealing with complex SQL statements, we can check the data upstream or data downstream analysis by using the to upstream\/to downstream feature of SQLFlow. To upstream\/downstream SQLFlow UI provides the upstream\/downstream data analysis in regular SQLFlow Job by right clicking the data element. The upstream of the data gives the source lineage for the selected\u2026<\/p>\n","protected":false},"author":6,"featured_media":2358,"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 upstream\/downstream 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