alamb opened a new issue, #37720:
URL: https://github.com/apache/arrow/issues/37720

   ### Describe the enhancement requested
   
   # Usecase
   
   InfluxDB IOx / 3.0 would like to allow customers to create prepared SQL 
statements *with parameters*  so they can send parameterized queries and 
parameter values to the serve. Without this feature, they have to do the 
parameter substitution on the client side, which is both subject to possible 
SQL injection attacks, or (if they use a pre existing library) may not have the 
same parameter typing as our SQL implementation. 
   
   Given the JDBC driver doesn't yet support binding parameters to prepared 
statements (see #33961) I am not sure how widely used the parameter support is, 
but I think interest is growing -- for example 
https://github.com/apache/arrow-rs/pull/4797 adds client side support to the 
Rust implementation
   
   # Background: Stateless services
   
   A common design pattern in cloud services is that the request from a client 
can be handled by one of a number of identical backend servers as shown in the 
diagram below. 
   
   Subsequent requests may be processed by different backend servers. Any state 
needed to continue a session is sent to the client which  passes it back in 
subsequent requests.
   
   This design can used to support features such as zero downtime deployments 
and automatic workload based scaling. It also has the nice property that there 
is no server side state to clean up (via timeout or other mechanism).
   
   ```
                                                                               
┌────────────────────┐   
                                                                     ┌ ─ ─ ─ 
─▶│      Server 1      │   
                                                                               
└────────────────────┘   
                                                                     │          
                        
                                                                               
┌────────────────────┐   
      ┌────────────────────┐                                         │         
│      Server 2      │   
      │     FlightSQL      │                                                   
└────────────────────┘   
      │       Client       │─ ─ ─ ─ ─ ▶   ... Network ...    ─ ─ ─ ─ ┘          
                        
      │                    │                                                    
         ...            
      └────────────────────┘                                                    
                        
                                                                               
┌────────────────────┐   
                ActionCreatePreparedStatementRequest                           
│      Server N      │   
                         handled by Server 1                                   
└────────────────────┘   
                                                                                
                        
                                                                                
                        
                                                                                
                        
   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ 
─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─
                                                                                
                        
                                                                               
┌────────────────────┐   
                                                                               
│      Server 1      │   
                                                                               
└────────────────────┘   
                                                                                
                        
                                                                               
┌────────────────────┐   
      ┌────────────────────┐                                                   
│      Server 2      │   
      │     FlightSQL      │                                                   
└────────────────────┘   
      │       Client       │─ ─ ─ ─ ─ ▶   ... Network ...    ─ ─ ─ ─ ┐          
                        
      │                    │                                                    
         ...            
      └────────────────────┘                                         │          
                        
                                                                               
┌────────────────────┐   
                                                                     └ ─ ─ ─ 
─▶│      Server N      │   
                   ActionPreparedStatementExecute                              
└────────────────────┘   
                         handled by Server N                                    
                        
                                                                                
                        
   ```
   
   
   # Problem
   
   As currently specified, I don't think we can implement  
[FlightSQL](https://arrow.apache.org/docs/format/FlightSql.html) prepared 
statements with parameters with such a stateless design.
   
   In IOx, the `handle` returned from `ActionCreatePreparedStatementRequest` 
contains the original SQL query text among other things. Thus the subsequent 
call to `ActionPreparedStatementExecute` have access to the SQL query.
   
   However, the `CommandPrepareStatementQuery` message to bind parameters does 
not return anything to the client that is sent to calls to 
`ActionPreparedStatementExecute`. Thus there is no way for the server that 
processes `ActionPreparedStatementExecute` to know the values of the 
parameters. 
   
   
   # FlightSQL sequence Diagram 
   
   Here is the sequence diagram from 
https://arrow.apache.org/docs/format/FlightSql.html for reference
   
   ![CommandPreparedStatementQuery 
mmd](https://github.com/apache/arrow/assets/490673/198b120d-6054-40b7-baa2-ea7e4d3b2b62)
   
   # Strawman Proposal
   One way to support stateless implementation of prepared statements with bind 
parameters would be to extend the response returned from calling `DoPut` with 
`CommandPrepareStatementQuery` to include a  new 
`CommandPrepareStatementQueryResponse` , similar to 
https://github.com/apache/arrow/blob/15a8ac3ce4e3ac31f9f361770ad4a38c69102aa1/format/FlightSql.proto#L1782-L1792
   
   ```protobuf
   /**
   * Response returned when `DoPut` is called with 
`CommandPrepareStatementQuery`
   message DoPutStatementPrepareResult {
     option (experimental) = true;
   
     // (potentially updated) opaque handle for the prepared statement on the 
server.
     // All subsequent requests for his prepared statement must use this new 
handle, if specified
     bytes prepared_statement_handle = 1;
   }
   ```
   
   I think this would be a fairly low overhead and easy extension. Existing 
clients that support bind parameters would require an update, but existing 
servers would not. Given that bind parameters are just starting to be used more 
I think the overall ecosystem impact would be low
   
   # See also
   See this discussion for more context:  
https://github.com/apache/arrow-rs/pull/4797/files#r1319807938
   
   
   ### Component(s)
   
   FlightRPC


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]

Reply via email to