[ https://issues.apache.org/jira/browse/LENS-742?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14745339#comment-14745339 ]
Hadoop QA commented on LENS-742: -------------------------------- Applied patch: [LENS-742-new.patch|https://issues.apache.org/jira/secure/attachment/12755903/LENS-742-new.patch] and ran command: mvn clean install. Result: Success. Build Job: https://builds.apache.org/job/PreCommit-Lens-Build/58/ > Saved query and parameterization > -------------------------------- > > Key: LENS-742 > URL: https://issues.apache.org/jira/browse/LENS-742 > Project: Apache Lens > Issue Type: New Feature > Reporter: Amruth S > Assignee: Amruth S > Fix For: 2.4 > > Attachments: LENS-742-REV-8.patch, LENS-742-new.patch > > > - User should be able to > * save a query parameterising parts of it. > * list all saved queries that are created by him and shared with him. > * share a query with other people with privileges. > * execute a saved query if his privilege allows (READ, EXECUTE). > * clone a saved query (READ). > - All of these operations should be supported from CLI as well as service > User flow (from UI) > Saving > -> User authors a query and clicks on SAVE. > -> Client calls the helper api /parameters to get info about the parameters > existing in the query. > -> For each parameter in the response, these details are obtained from the > user (from a pop up UI) > * DATA TYPE > * COLLECTION TYPE > (scroll down to the end to see why we would need these details) > -> User enters all the details and clicks on SAVE again. A final payload > containing the query and all the parameter details is sent. > { > "name": "query_name", > "description": "description", > "query": "select * from table where col1 = :param1 and col2 in :param2 and > col3 = :param3 and col4 in :param4 and col5 = :param5 and col6 in :param6", > "parameters": [ > { > "name": "param1", > "displayName": "Param1", > "defaultValue": "x", > "dataType": "STRING", > "collectionType": "SINGLE" > }, > { > "name": "param2", > "displayName": "Param2", > "defaultValue": "x", > "dataType": "STRING", > "collectionType": "MULTIPLE" > }, > { > "name": "param3", > "displayName": "Param3", > "defaultValue": "1.0", > "dataType": "NUMBER", > "collectionType": "SINGLE" > }, > { > "name": "param4", > "displayName": "Param4", > "defaultValue": "1.0", > "dataType": "NUMBER", > "collectionType": "MULTIPLE" > }, > { > "name": "param5", > "displayName": "Param5", > "defaultValue": "true", > "dataType": "BOOLEAN", > "collectionType": "SINGLE" > }, > { > "name": "param6", > "displayName": "Param6", > "defaultValue": "true", > "dataType": "BOOLEAN", > "collectionType": "MULTIPLE" > } > ] > } > Execution > - User selects a saved query from a list of saved queries and clicks on RUN > - A pop is shown asking for parameter values > - User enters all param values (client side validation happens on datatype > and collection type) and clicks RUN. A query handle is returned. > User flow - From CLI > Saving > - create savedquery 'path to the final json payload' > Executing > - savedquery execute <id> <param values query string> > Why do we need these DATATYPE and COLLECTION TYPE details in parameters?? > A parameterised query would look like this > select col1, col2 from table where col1 = :param1 and col in :param2 limit > :param3 > param1, param2, param3 are parameters here. > While resolving values for the parameters datatype would be required else SQL > injection could happen. > Eg. param1 value could be : 'val2 or param1 like '%''. We would not know if > we can single quote it unless its a String. > Data types could be inferred from the native tables but it is not applicable > to all the drivers. > Also depending on the operator the parameter is associated with, the > collection type could differ. Eg. IN versus =. Ideally this intelligence > should exist with the code. This would require a great deal of query parsing. > (The existing HQLParser - ASTNode is not able to parse an ANSI compatible > parameterised query) -- This message was sent by Atlassian JIRA (v6.3.4#6332)