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

Port Pandas to_sql and read_sql, using SQLAlchemy, to afw.table

    Details

    • Type: Story
    • Status: Done
    • Resolution: Done
    • Fix Version/s: None
    • Component/s: dax, metaserv
    • Labels:
      None
    • Story Points:
      6
    • Sprint:
      DB_S17_5, DB_S17_6, DB_S17_7, DB_S17_8
    • Team:
      Data Access and Database

      Description

      Using a combination of daf_ingest and the pandas library, work on supporting table reading and writing in afw.table to arbitrary databases.

        Attachments

          Issue Links

            Activity

            Hide
            bvan Brian Van Klaveren added a comment -

            About 80% complete.

            TODOs in the code mark places where code may need to be modified and/or afw may need to be better researched.
            FIXMEs are where code is absolutely required to implement this ticket - mostly creating afw.tables or dumping the data from them.

            Still need for sure:

            1. Type converters for converting to Angle (to degrees) and Array types (packing arrays). This should be added with a TODO to support databases with native array types.
            2. Smart code that determines which Database Column type to use for Flags (inspecting db connection dialect) - use BIT(1) for MySQL otherwise BOOLEAN
            3. Potentially interfaces for writing and reading metadata and renormalizing afw.table based on that
            Show
            bvan Brian Van Klaveren added a comment - About 80% complete. TODOs in the code mark places where code may need to be modified and/or afw may need to be better researched. FIXMEs are where code is absolutely required to implement this ticket - mostly creating afw.tables or dumping the data from them. Still need for sure: Type converters for converting to Angle (to degrees) and Array types (packing arrays). This should be added with a TODO to support databases with native array types. Smart code that determines which Database Column type to use for Flags (inspecting db connection dialect) - use BIT(1) for MySQL otherwise BOOLEAN Potentially interfaces for writing and reading metadata and renormalizing afw.table based on that
            Hide
            bvan Brian Van Klaveren added a comment -

            Initial work is completed. We need to create a new ticket to work on refining this and potentially removing the pandas dependency, as well as some discussions around formatting.

            Show
            bvan Brian Van Klaveren added a comment - Initial work is completed. We need to create a new ticket to work on refining this and potentially removing the pandas dependency, as well as some discussions around formatting.
            Hide
            bvan Brian Van Klaveren added a comment -

            Work to do: unit tests, Angle/Flag handling, Proper column extraction from table (when writing), a few other things.

            Show
            bvan Brian Van Klaveren added a comment - Work to do: unit tests, Angle/Flag handling, Proper column extraction from table (when writing), a few other things.
            Hide
            cwalter Chris Walter added a comment -

            If this already exists internally in the API could we expose the ability to use this for reading afw_tables directly to pandas DataFrames? The goal here being not to use this as an intermediate step but as the output itself.

            Show
            cwalter Chris Walter added a comment - If this already exists internally in the API could we expose the ability to use this for reading afw_tables directly to pandas DataFrames? The goal here being not to use this as an intermediate step but as the output itself.
            Hide
            bvan Brian Van Klaveren added a comment -

            This works in a roundabout way by converting to an astropy table, then to a pandas table. I'm hoping I can talk to Jim Bosch and Nate Pease this coming woeek to discuss improving afw.table APIs to better support converting between these different table representations. The main issue is making sure the mapping of of Flag and Angle works properly, trying to come up with a way of propagating table/column metadata, and better support for the other way (astropy/pandas table to afw table).

            Show
            bvan Brian Van Klaveren added a comment - This works in a roundabout way by converting to an astropy table, then to a pandas table. I'm hoping I can talk to Jim Bosch and Nate Pease this coming woeek to discuss improving afw.table APIs to better support converting between these different table representations. The main issue is making sure the mapping of of Flag and Angle works properly, trying to come up with a way of propagating table/column metadata, and better support for the other way (astropy/pandas table to afw table).
            Hide
            bvan Brian Van Klaveren added a comment -

            Fixed according to review

            Show
            bvan Brian Van Klaveren added a comment - Fixed according to review
            Hide
            bvan Brian Van Klaveren added a comment -

            Note for watchers: More work needs to be performed before this is really ready for users (some of that work is butler related). We'll discuss it at the JCW.

            Show
            bvan Brian Van Klaveren added a comment - Note for watchers: More work needs to be performed before this is really ready for users (some of that work is butler related). We'll discuss it at the JCW.

              People

              • Assignee:
                bvan Brian Van Klaveren
                Reporter:
                bvan Brian Van Klaveren
                Reviewers:
                Nate Pease
                Watchers:
                Brian Van Klaveren, Chris Walter, Colin Slater, Nate Pease
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Summary Panel