Uploaded image for project: 'Data Management'
  1. Data Management
  2. DM-3480

Design SQL APIs for async queries

    XMLWordPrintable

    Details

    • Type: Story
    • Status: Done
    • Resolution: Done
    • Fix Version/s: None
    • Component/s: Qserv
    • Labels:
      None

      Description

      Need SQL API for:

      • submitting async query, note that we should be able to specify where the results are going / what is the format of the results
      • retrieving status of async query
      • retrieving results of async query
      • retrieving partial results of async query while it is running

        Attachments

          Issue Links

            Activity

            Hide
            salnikov Andy Salnikov added a comment -

            Brian could you check what I wrote yesterday and tell if it makes sense (especially from TAP/dbserv perspective)? No code in this ticket.

            Show
            salnikov Andy Salnikov added a comment - Brian could you check what I wrote yesterday and tell if it makes sense (especially from TAP/dbserv perspective)? No code in this ticket.
            Hide
            bvan Brian Van Klaveren added a comment -

            For a first pass, SUBMIT is probably okay since it is regex-able and easier to parse. I really think that we should stick to a more idiomatic (in SQL) CREATE statement, but I think SUBMIT can exist long-term as syntactic sugar (without the options).

            e.g:

            CREATE QUERY AS SELECT something FROM table
            With alternate:
            CREATE QUERY AS (SELECT something FROM table)

            A more complete description might look like:

            create_query_definition :=
                CREATE QUERY  [query_options] [AS] query_expression
             
            query_options :=
                CACHED OK
                |  keyword_argument
                |  kevalue_argument
             
            query_expression:
                SELECT ...   (Some valid select or union statement)
            
            

            Show
            bvan Brian Van Klaveren added a comment - For a first pass, SUBMIT is probably okay since it is regex-able and easier to parse. I really think that we should stick to a more idiomatic (in SQL) CREATE statement, but I think SUBMIT can exist long-term as syntactic sugar (without the options). e.g: CREATE QUERY AS SELECT something FROM table With alternate: CREATE QUERY AS (SELECT something FROM table) A more complete description might look like: create_query_definition := CREATE QUERY [query_options] [AS] query_expression   query_options := CACHED OK | keyword_argument | kevalue_argument   query_expression: SELECT ... (Some valid select or union statement)
            Hide
            salnikov Andy Salnikov added a comment -

            In terms of parsing "SUBMIT" and "CREATE QUERY" should not be too much different (if we talk about pre-parsing and not our actual ANTLR parser). CREATE in SQL is a DDL statement, there is of course "CREATE TABLE ... AS SELECT ..." statement which is a DDL but also runs a query so there is some overlap indeed between DDL and DML. I'll talk to the db-team on Wednesday to see what they think about it, maybe we can put it to vote (worst candidate wins).

            Show
            salnikov Andy Salnikov added a comment - In terms of parsing "SUBMIT" and "CREATE QUERY" should not be too much different (if we talk about pre-parsing and not our actual ANTLR parser). CREATE in SQL is a DDL statement, there is of course "CREATE TABLE ... AS SELECT ..." statement which is a DDL but also runs a query so there is some overlap indeed between DDL and DML. I'll talk to the db-team on Wednesday to see what they think about it, maybe we can put it to vote (worst candidate wins).
            Hide
            salnikov Andy Salnikov added a comment -

            Fritz Mueller, Brian Van Klaveren, I wanted to discuss this at our Wed meeting but very likely I'll have to skip it in favor of our SuperTask hackaton. We have two (or more) competing proposals for async query syntax and I'd really prefer not to have to implement them both but to choose a single "best" one. I tried to get input from other guys at our Wed meeting two weeks ago (both Fritz and Brian were away) and I think no one had strong preference for either one. Here is my quick summary of pros/cons for each option:

            SUBMIT [query_options] query_expression (query_expression is "SELECT something FROM table, ...")

            • non-standard syntax
            • SUBMIT strongly implies that this is async/background execution

            CREATE QUERY [query_options] [AS] query_expression

            • looks a little bit more standard
            • nothing implies this is background operation

            In a sense what we do can be described as a variant of mysql "CREATE TABLE ... AS SELECT ..." query so we can consider it as another option:
            CREATE TABLE qserv_result.result_table_name [query_options] [AS] query_expression

            • even more standard, but behavior is different from standard (standard does not return result from this query)
            • needs unique result table name
            • longer to type
            • if we send result to file then table name makes no sense (but we can probably abuse table name to specify destination file name)

            We could meet and talk tomorrow if you prefer online discussion.

            Show
            salnikov Andy Salnikov added a comment - Fritz Mueller , Brian Van Klaveren , I wanted to discuss this at our Wed meeting but very likely I'll have to skip it in favor of our SuperTask hackaton. We have two (or more) competing proposals for async query syntax and I'd really prefer not to have to implement them both but to choose a single "best" one. I tried to get input from other guys at our Wed meeting two weeks ago (both Fritz and Brian were away) and I think no one had strong preference for either one. Here is my quick summary of pros/cons for each option: SUBMIT [query_options] query_expression (query_expression is "SELECT something FROM table, ...") non-standard syntax SUBMIT strongly implies that this is async/background execution CREATE QUERY [query_options] [AS] query_expression looks a little bit more standard nothing implies this is background operation In a sense what we do can be described as a variant of mysql "CREATE TABLE ... AS SELECT ..." query so we can consider it as another option: CREATE TABLE qserv_result.result_table_name [query_options] [AS] query_expression even more standard, but behavior is different from standard (standard does not return result from this query) needs unique result table name longer to type if we send result to file then table name makes no sense (but we can probably abuse table name to specify destination file name) We could meet and talk tomorrow if you prefer online discussion.
            Hide
            salnikov Andy Salnikov added a comment -

            After long and fruitful discussion we have reached consensus:

            • use "SUBMIT" to submit
            • don't call query ID a query ID but instead call it "job ID"

            Closing now, will start implementing ~soon.

            Show
            salnikov Andy Salnikov added a comment - After long and fruitful discussion we have reached consensus: use "SUBMIT" to submit don't call query ID a query ID but instead call it "job ID" Closing now, will start implementing ~soon.

              People

              Assignee:
              salnikov Andy Salnikov
              Reporter:
              fritzm Fritz Mueller
              Reviewers:
              Brian Van Klaveren
              Watchers:
              Andy Salnikov, Brian Van Klaveren, Fritz Mueller, Gregory Dubois-Felsmann, Kian-Tat Lim, Tim Jenness
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  CI Builds

                  No builds found.