{"id":2805,"date":"2023-04-11T00:29:30","date_gmt":"2023-04-11T05:29:30","guid":{"rendered":"https:\/\/www.dpriver.com\/blog\/?p=2805"},"modified":"2023-04-11T00:47:18","modified_gmt":"2023-04-11T05:47:18","slug":"visualize-your-data-under-left-most-format-with-sqlflow-rest-api","status":"publish","type":"post","link":"https:\/\/www.dpriver.com\/blog\/2023\/04\/visualize-your-data-under-left-most-format-with-sqlflow-rest-api\/","title":{"rendered":"Visualize your data under Left-Most format 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

## 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

## The Left-Most format<\/h2>\n\n\n\n

A common usage scenario is that we would like to have a simple and direct data lineage when retrieving data from database or from multiple SQL statements. One of the features that SQLFlow provides is called the left-most<\/em><\/strong> result. The left-most format can simplify the data lineage result. <\/p>\n\n\n\n

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

It generates the selected db elements(such as database\/schema\/table\/column etc…) data lineage without the record set. Given data lineage `a->b->c<\/code>, the returned result under the left-most format is `a->c<\/code>.<\/p>\n\n\n\n``

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

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

###

###

##

    \/sqlflow\/generation\/sqlflow\/leftMostSourceTableGraph\/image<\/code><\/pre>\n\n\n\nThis endpoint takes 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\nRetrieve left-most data lineage<\/h2>\n\n\n\nNow we are good to invoke the left-most SQLFlow Api with the sessionId<\/code> ready. You can use tableNamePattern<\/code> to map the desired table. Full parameter table here: <\/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>columnLevel<\/td>string<\/td>whether to show table level or column level data, false or true<\/td><\/tr>isReturnModel<\/td>boolean<\/td>whether to return model<\/td><\/tr>sessionId<\/td>string<\/td>the graph session Id<\/td><\/tr>share<\/td>boolean<\/td>whether shareable <\/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 ‘fdd’, multiple
    values seperated by comma like fdd,frd,fdr,join. Availables are ‘fdd’ value
    of target column from source column, ‘frd’ the recordset count of target
    column which is affected by value of source column, ‘fdr’ value of target
    column which is affected by the recordset count of source column, ‘join’
    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><\/tbody><\/table><\/figure>\n\n\n\nSample:<\/p>\n\n\n\ncurl --location 'https:\/\/<SQLFLOW URL>\/gspLive_backend\/sqlflow\/generation\/sqlflow\/leftMostSourceTableGraph\/image' \\\n--header 'accept: image\/*' \\\n--form 'userId=\"gudu|0123456789\"' \\\n--form 'sessionId=\"85b5119f83fcfefa93136e79dc05f2f61efe3b75e6a2b733568e0879c24f0c08_1680615728918\"' \\\n--form 'dbvendor=\"dbvmssql\"' \\\n--form 'jobId=\"6def00e8796e4c20ba216826c0ab5b73\"' \\\n--form 'tableNamePattern=\"msdb.@event_data.nodes\"'<\/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. SQLFlow REST API 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\u2026<\/p>\n","protected":false},"author":6,"featured_media":2806,"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 under Left-Most format 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