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

Load WISE catalog data in PDAC

    XMLWordPrintable

    Details

    • Type: Story
    • Status: Done
    • Resolution: Done
    • Fix Version/s: None
    • Component/s: None
    • Labels:
    • Story Points:
      100
    • Sprint:
      DB_S17_2, DB_S17_4, DB_S17_5
    • Team:
      Data Access and Database

      Description

      Goals

      Download from IPAC, pre-reprocess (if needed), partition and load into PDAC object and forced source catalogs of various WISE missions.

      References

      Properties of the dataset are documented at:

      A list of WISE catalogs to be loaded:

      Tools and scripts developed for this efforts are found at the GitHub package:

        Attachments

          Issue Links

            Activity

            Hide
            gapon Igor Gaponenko added a comment - - edited

            Testing

            Issues

            In a course of the initial testing of the dataset blocking 3 issues with Qserv were discovered. The issues were triggered by a scale or table schemas of the dataset.

            • DM-9672 - The cluster hangs with very large number of chunks due to a deadlock in an implementation of the worker component
            • DM-9706 - The WISE object identifiers are strings and this is not supported by the Qserv implementation
            • DM-9736 - Double quotes around column names in SQL statements cause a parser error. This prevents users from quoting SQL reserved words like dec (for declination) when making queries against the Object or ForcedSource tables.

            After discovering these problems a work began to improve Qserv.

            Performance

            When the first two previously mentioned problems were resolved then a few simple queries were successfully launched against the new dataset in order to test the performance of the Qserv service:

            SELECT COUNT(*) FROM wise_00.Object;
            +----------------+
            | SUM(QS1_COUNT) |
            +----------------+
            |      747634026 |
            +----------------+
            1 row in set (6 min 6.76 sec)
            

            SELECT COUNT(*) FROM wise_00.ForcedSource;
            +----------------+
            | SUM(QS1_COUNT) |
            +----------------+
            |    41997706856 |
            +----------------+
            1 row in set (6 min 30.62 sec)
            

            The secondary index optimization

            The next set of queries tested the secondary index:

            SELECT source_id FROM wise_00.Object WHERE source_id='0075m016_ac51-028478';
            +----------------------+
            | source_id            |
            +----------------------+
            | 0075m016_ac51-028478 |
            +----------------------+
            1 row in set (0.40 sec)
            

            SELECT source_id FROM wise_00.Object WHERE source_id IN ('0075m016_ac51-028478');
            +----------------------+
            | source_id            |
            +----------------------+
            | 0075m016_ac51-028478 |
            +----------------------+
            1 row in set (0.34 sec)
            

            SELECT source_id FROM wise_00.Object WHERE source_id IN ('0075m016_ac51-028478','0000m016_ac51-000001');
            +----------------------+
            | source_id            |
            +----------------------+
            | 0000m016_ac51-000001 |
            | 0075m016_ac51-028478 |
            +----------------------+
            2 rows in set (0.35 sec)
            

            These can be compared with the poor performance of the full-table scan query yielding the same result:

            SELECT source_id FROM wise_00.Object WHERE source_id LIKE '0075m016_ac51-028478';
            +----------------------+
            | source_id            |
            +----------------------+
            | 0075m016_ac51-028478 |
            +----------------------+
            1 row in set (6 min 17.70 sec)
            

            Show
            gapon Igor Gaponenko added a comment - - edited Testing Issues In a course of the initial testing of the dataset blocking 3 issues with Qserv were discovered. The issues were triggered by a scale or table schemas of the dataset. DM-9672 - The cluster hangs with very large number of chunks due to a deadlock in an implementation of the worker component DM-9706 - The WISE object identifiers are strings and this is not supported by the Qserv implementation DM-9736 - Double quotes around column names in SQL statements cause a parser error. This prevents users from quoting SQL reserved words like dec (for declination ) when making queries against the Object or ForcedSource tables. After discovering these problems a work began to improve Qserv . Performance When the first two previously mentioned problems were resolved then a few simple queries were successfully launched against the new dataset in order to test the performance of the Qserv service: SELECT COUNT (*) FROM wise_00.Object; + ----------------+ | SUM (QS1_COUNT) | + ----------------+ | 747634026 | + ----------------+ 1 row in set (6 min 6.76 sec) SELECT COUNT (*) FROM wise_00.ForcedSource; + ----------------+ | SUM (QS1_COUNT) | + ----------------+ | 41997706856 | + ----------------+ 1 row in set (6 min 30.62 sec) The secondary index optimization The next set of queries tested the secondary index: SELECT source_id FROM wise_00.Object WHERE source_id= '0075m016_ac51-028478' ; + ----------------------+ | source_id | + ----------------------+ | 0075m016_ac51-028478 | + ----------------------+ 1 row in set (0.40 sec) SELECT source_id FROM wise_00.Object WHERE source_id IN ( '0075m016_ac51-028478' ); + ----------------------+ | source_id | + ----------------------+ | 0075m016_ac51-028478 | + ----------------------+ 1 row in set (0.34 sec) SELECT source_id FROM wise_00.Object WHERE source_id IN ( '0075m016_ac51-028478' , '0000m016_ac51-000001' ); + ----------------------+ | source_id | + ----------------------+ | 0000m016_ac51-000001 | | 0075m016_ac51-028478 | + ----------------------+ 2 rows in set (0.35 sec) These can be compared with the poor performance of the full-table scan query yielding the same result: SELECT source_id FROM wise_00.Object WHERE source_id LIKE '0075m016_ac51-028478' ; + ----------------------+ | source_id | + ----------------------+ | 0075m016_ac51-028478 | + ----------------------+ 1 row in set (6 min 17.70 sec)
            Hide
            gapon Igor Gaponenko added a comment - - edited

            Schema fixup

            As a workaround to a problem explained in DM-9736 the table schema of all instances (and their chunk-specific derivatives) of tables Object and ForcedSource had been modified by replacing dec with decl as column names. This was done in 4 steps explaining below.

            Fixing up table schema

            scripts/run_fix_schema.bash [-n|--dry-run]
            

            Updating CSS

            Deleting a database entry for CSS entry:

            % source /datasets/gapon/stack/loadLSST.bash
            % setup -t qserv-dev qserv_distrib
            % setup -j -r /datasets/gapon/development/qserv
            % qserv-admin.py "SHOW DATABASES;"
            ...
            wise_00
            % qserv-admin.py "DROP DATABASE wise_00;"
            

            Then the schema and configuration files in the GitHub package were modified accordingly (to rename dec into decl as needed).
            And finally the new configuration was loaded into CSS with:

            scripts/setup_css.bash
            

            Testing

            SELECT source_id,ra,decl FROM wise_00.Object WHERE qserv_areaspec_box(0,0,3,10) LIMIT 10;
            +----------------------+-----------+-----------+
            | source_id            | ra        | decl      |
            +----------------------+-----------+-----------+
            | 0000p015_ac51-041241 | 0.3591511 | 1.8563953 |
            | 0000p015_ac51-050569 | 0.0401312 | 2.0203582 |
            | 0000p015_ac51-049853 | 0.1542783 | 2.1076074 |
            | 0000p015_ac51-038660 | 0.1149200 | 1.8393346 |
            | 0000p015_ac51-051652 | 0.0792609 | 2.0639651 |
            | 0000p015_ac51-052063 | 0.1071766 | 2.0572472 |
            | 0000p015_ac51-026097 | 0.3453069 | 1.6647640 |
            | 0000p015_ac51-048466 | 0.2439256 | 2.1099024 |
            | 0000p015_ac51-044164 | 0.4954270 | 1.9122088 |
            | 0000p015_ac51-038908 | 0.0102313 | 1.8636530 |
            +----------------------+-----------+-----------+
            10 rows in set (0.94 sec)
            

            SELECT source_id_mf,ra,decl FROM wise_00.ForcedSource WHERE qserv_areaspec_box(0,0,3,10) LIMIT 10;
            +----------------------+-----------+-----------+
            | source_id_mf         | ra        | decl      |
            +----------------------+-----------+-----------+
            | 0015p000_ac51-058064 | 1.3726421 | 0.5165190 |
            | 0015p000_ac51-057359 | 1.5482223 | 0.5079978 |
            | 0015p000_ac51-057594 | 1.3780350 | 0.4722597 |
            | 0015p000_ac51-058064 | 1.3726421 | 0.5165190 |
            | 0015p000_ac51-058064 | 1.3726421 | 0.5165190 |
            | 0015p000_ac51-057093 | 1.3613006 | 0.5196325 |
            | 0015p000_ac51-058064 | 1.3726421 | 0.5165190 |
            | 0015p000_ac51-058064 | 1.3726421 | 0.5165190 |
            | 0015p000_ac51-058064 | 1.3726421 | 0.5165190 |
            | 0015p000_ac51-057096 | 1.5480032 | 0.5225815 |
            +----------------------+-----------+-----------+
            10 rows in set (4.79 sec)
            

            Show
            gapon Igor Gaponenko added a comment - - edited Schema fixup As a workaround to a problem explained in DM-9736 the table schema of all instances (and their chunk-specific derivatives) of tables Object and ForcedSource had been modified by replacing dec with decl as column names. This was done in 4 steps explaining below. Fixing up table schema scripts /run_fix_schema . bash [-n|--dry-run] Updating CSS Deleting a database entry for CSS entry: % source /datasets/gapon/stack/loadLSST . bash % setup -t qserv-dev qserv_distrib % setup -j -r /datasets/gapon/development/qserv % qserv-admin.py "SHOW DATABASES;" ... wise_00 % qserv-admin.py "DROP DATABASE wise_00;" Then the schema and configuration files in the GitHub package were modified accordingly (to rename dec into decl as needed). And finally the new configuration was loaded into CSS with: scripts /setup_css . bash Testing SELECT source_id,ra,decl FROM wise_00.Object WHERE qserv_areaspec_box(0,0,3,10) LIMIT 10; + ----------------------+-----------+-----------+ | source_id | ra | decl | + ----------------------+-----------+-----------+ | 0000p015_ac51-041241 | 0.3591511 | 1.8563953 | | 0000p015_ac51-050569 | 0.0401312 | 2.0203582 | | 0000p015_ac51-049853 | 0.1542783 | 2.1076074 | | 0000p015_ac51-038660 | 0.1149200 | 1.8393346 | | 0000p015_ac51-051652 | 0.0792609 | 2.0639651 | | 0000p015_ac51-052063 | 0.1071766 | 2.0572472 | | 0000p015_ac51-026097 | 0.3453069 | 1.6647640 | | 0000p015_ac51-048466 | 0.2439256 | 2.1099024 | | 0000p015_ac51-044164 | 0.4954270 | 1.9122088 | | 0000p015_ac51-038908 | 0.0102313 | 1.8636530 | + ----------------------+-----------+-----------+ 10 rows in set (0.94 sec) SELECT source_id_mf,ra,decl FROM wise_00.ForcedSource WHERE qserv_areaspec_box(0,0,3,10) LIMIT 10; + ----------------------+-----------+-----------+ | source_id_mf | ra | decl | + ----------------------+-----------+-----------+ | 0015p000_ac51-058064 | 1.3726421 | 0.5165190 | | 0015p000_ac51-057359 | 1.5482223 | 0.5079978 | | 0015p000_ac51-057594 | 1.3780350 | 0.4722597 | | 0015p000_ac51-058064 | 1.3726421 | 0.5165190 | | 0015p000_ac51-058064 | 1.3726421 | 0.5165190 | | 0015p000_ac51-057093 | 1.3613006 | 0.5196325 | | 0015p000_ac51-058064 | 1.3726421 | 0.5165190 | | 0015p000_ac51-058064 | 1.3726421 | 0.5165190 | | 0015p000_ac51-058064 | 1.3726421 | 0.5165190 | | 0015p000_ac51-057096 | 1.5480032 | 0.5225815 | + ----------------------+-----------+-----------+ 10 rows in set (4.79 sec)
            Hide
            gapon Igor Gaponenko added a comment - - edited

            Renamed Object, Reject and ForcedSource tables back to the original names

            JUSTIFICATION: retaining the original (adopted by the WISE project) naming convention would significantly simplify a documentation and table metadata support efforts for these catalogs on behalf of the LSST/PDAC team. This will also avoid a possible (and unnecessary) confusion which might arise among PDAC users because of the present LSST-way of naming these tables.

            The procedure took the following steps

            Making proper modifications to the tools, scripts and input data sets

            The scripts, configurations and table schemas were updated and saved into GitHub (package):

            Renaming the data tables

            This was done by simply running on the PDAC master node the "fix-up" script added earlier into the toolset:

            % hostname
            lsst-qserv-master01
             
            % /bin/sudo -u qserv \
              /datasets/gapon/development/db_pdac_wise/scripts/run_rename_tables.bash --verbose
            

            A similar operation was also performed for the Reject table of Qserv database wise_ext_00 which required a few extra steps of setting up a proper data set specification files (and cleaning up the configuration back to its default state):

            % cd /datasets/gapon/development/db_pdac_wise/scripts/
            % mv dataset.bash dataset.bash.wise_00
            % mv dataset.bash.wise_ext_00 dataset.bash
            % /bin/sudo -u qserv run_rename_tables.bash --verbose
            % mv dataset.bash dataset.bash.wise_ext_00
            % mv dataset.bash.wise_00 dataset.bash.wise
            

            The log files retain on the local file system of the master node has not revealed any problem. The log files were placed at:

            % ls -l /tmp/wise_00/log/
            -rw-r--r-- 1 qserv qserv 3312209 May  4 15:10 qserv-db01_rename_tables.log
            -rw-r--r-- 1 qserv qserv 3312225 May  4 15:10 qserv-db02_rename_tables.log
            ...
            -rw-r--r-- 1 qserv qserv 3311567 May  4 15:10 qserv-db29_rename_tables.log
            -rw-r--r-- 1 qserv qserv 3311567 May  4 15:10 qserv-db30_rename_tables.log
            -rw-r--r-- 1 qserv qserv     291 May  4 15:07 qserv-master01_rename_tables.log
            

            Renaming the secondary index tables

            The secondary index tables of both Object and Reject tables were manually renamed by running the following operations directly on the database server of the PDAC master:

            MariaDB [qservMeta]> SHOW TABLES;
            +---------------------------------------+
            | Tables_in_qservMeta                   |
            +---------------------------------------+
            | wise_00__Object                       |
            | wise_ext_00__Reject                   |
            +---------------------------------------+
             
            MariaDB [qservMeta]> RENAME TABLE qservMeta.wise_00__Object TO qservMeta.wise_00__allwise_cat;
            MariaDB [qservMeta]> RENAME TABLE qservMeta.wise_ext_00__Reject TO qservMeta.wise_ext_00__allwise_rej;
             
            MariaDB [qservMeta]> SHOW TABLES;
            +---------------------------------------+
            | Tables_in_qservMeta                   |
            +---------------------------------------+
            | wise_00__allwise_cat                  |
            | wise_ext_00__allwise_rej              |
            +---------------------------------------+
            

            Making changes to Qserv CSS

            The current CSS has:

            serv-admin.py "DUMP EVERYTHING" | grep wise_
            /DBS/wise_00	READY
            /DBS/wise_00/.packed.json	{"partitioningId":"0000000018","releaseStatus":"RELEASED","storageClass":"L2"} 
            /DBS/wise_00/TABLES	\N
            /DBS/wise_00/TABLES/allwise_p3as	READY
            /DBS/wise_00/TABLES/allwise_p3as/schema	(
            /DBS/wise_00/TABLES/ForcedSource	READY
            /DBS/wise_00/TABLES/ForcedSource/partitioning	\N
            /DBS/wise_00/TABLES/ForcedSource/partitioning/.packed.json	{"dirColName":"source_id_mf","dirDb":"wise_00","dirTable":"Object","latColName":"decl","lonColName":"ra","subChunks":"0"} 
            /DBS/wise_00/TABLES/ForcedSource/schema	(
            /DBS/wise_00/TABLES/ForcedSource/sharedScan	\N
            /DBS/wise_00/TABLES/ForcedSource/sharedScan/.packed.json	{"lockInMem":"1"} 
            /DBS/wise_00/TABLES/Object	READY
            /DBS/wise_00/TABLES/Object/partitioning	\N
            /DBS/wise_00/TABLES/Object/partitioning/.packed.json	{"dirColName":"source_id","dirDb":"wise_00","dirTable":"Object","latColName":"decl","lonColName":"ra","subChunks":"1"} 
            /DBS/wise_00/TABLES/Object/schema	(
            /DBS/wise_00/TABLES/Object/sharedScan	\N
            /DBS/wise_00/TABLES/Object/sharedScan/.packed.json	{"lockInMem":"1"} 
            /DBS/wise_ext_00	READY
            /DBS/wise_ext_00/.packed.json	{"partitioningId":"0000000020","releaseStatus":"RELEASED","storageClass":"L2"} 
            /DBS/wise_ext_00/TABLES	\N
            /DBS/wise_ext_00/TABLES/Reject	READY
            /DBS/wise_ext_00/TABLES/Reject/partitioning	\N
            /DBS/wise_ext_00/TABLES/Reject/partitioning/.packed.json	{"dirColName":"source_id","dirDb":"wise_ext_00","dirTable":"Reject","latColName":"decl","lonColName":"ra","subChunks":"1"} 
            /DBS/wise_ext_00/TABLES/Reject/schema	(
            /DBS/wise_ext_00/TABLES/Reject/sharedScan	\N
            /DBS/wise_ext_00/TABLES/Reject/sharedScan/.packed.json	{"lockInMem":"1"} 
            

            The configuration was cleared with:

            qserv-admin.py "DROP DATABASE wise_00";
            qserv-admin.py "DROP DATABASE wise_ext_00";
            

            And the new one reinstalled (for database wise_00 and wise_ext_00) with:

            % cd /datasets/gapon/development/db_pdac_wise/scripts/
             
            % /bin/sudo -u qserv setup_css.bash --verbose
            % /bin/sudo -u qserv setup_css_nonpart.bash --verbose
             
            % mv dataset.bash dataset.bash.wise_00
            % mv dataset.bash.wise_ext_00 dataset.bash
             
            % /bin/sudo -u qserv setup_css.bash --verbose
             
            % mv dataset.bash dataset.bash.wise_ext_00
            % mv dataset.bash.wise_00 dataset.bash.wise
            

            After that the new configuration has:

            % qserv-admin.py "DUMP EVERYTHING" | grep wise_
            /DBS/wise_00	READY
            /DBS/wise_00/.packed.json	{"partitioningId":"0000000022","releaseStatus":"RELEASED","storageClass":"L2"} 
            /DBS/wise_00/TABLES	\N
            /DBS/wise_00/TABLES/allwise_cat	READY
            /DBS/wise_00/TABLES/allwise_cat/partitioning	\N
            /DBS/wise_00/TABLES/allwise_cat/partitioning/.packed.json	{"dirColName":"source_id","dirDb":"wise_00","dirTable":"allwise_cat","latColName":"decl","lonColName":"ra","subChunks":"1"} 
            /DBS/wise_00/TABLES/allwise_cat/schema	(
            /DBS/wise_00/TABLES/allwise_cat/sharedScan	\N
            /DBS/wise_00/TABLES/allwise_cat/sharedScan/.packed.json	{"lockInMem":"1"} 
            /DBS/wise_00/TABLES/allwise_i3as_mep	READY
            /DBS/wise_00/TABLES/allwise_i3as_mep/partitioning	\N
            /DBS/wise_00/TABLES/allwise_i3as_mep/partitioning/.packed.json	{"dirColName":"source_id_mf","dirDb":"wise_00","dirTable":"allwise_cat","latColName":"decl","lonColName":"ra","subChunks":"0"} 
            /DBS/wise_00/TABLES/allwise_i3as_mep/schema	(
                `source_id_mf`  VARCHAR(28)         NOT NULL,  -- foreign key to the primary key of the allwise_cat table
            /DBS/wise_00/TABLES/allwise_i3as_mep/sharedScan	\N
            /DBS/wise_00/TABLES/allwise_i3as_mep/sharedScan/.packed.json	{"lockInMem":"1"} 
            /DBS/wise_00/TABLES/allwise_p3as	READY
            /DBS/wise_00/TABLES/allwise_p3as/schema	(
            /DBS/wise_ext_00	READY
            /DBS/wise_ext_00/.packed.json	{"partitioningId":"0000000021","releaseStatus":"RELEASED","storageClass":"L2"} 
            /DBS/wise_ext_00/TABLES	\N
            /DBS/wise_ext_00/TABLES/allwise_rej	READY
            /DBS/wise_ext_00/TABLES/allwise_rej/partitioning	\N
            /DBS/wise_ext_00/TABLES/allwise_rej/partitioning/.packed.json	{"dirColName":"source_id","dirDb":"wise_ext_00","dirTable":"allwise_rej","latColName":"decl","lonColName":"ra","subChunks":"1"} 
            /DBS/wise_ext_00/TABLES/allwise_rej/schema	(
            /DBS/wise_ext_00/TABLES/allwise_rej/sharedScan	\N
            /DBS/wise_ext_00/TABLES/allwise_rej/sharedScan/.packed.json	{"lockInMem":"1"} 
            

            Testing

            No service restart was needed to enact the changes. All catalogs are now seen with their new names, and simple test queries produce sensible results:

            USE wise_00;
            SHOW TABLES;
            +-------------------+
            | Tables_in_wise_00 |
            +-------------------+
            | allwise_cat       |
            | allwise_i3as_mep  |
            | allwise_p3as      |
            +-------------------+
             
            USE wise_ext_00;
            SHOW TABLES;
            +-----------------------+
            | Tables_in_wise_ext_00 |
            +-----------------------+
            | allwise_rej           |
            +-----------------------+
            

            Show
            gapon Igor Gaponenko added a comment - - edited Renamed Object , Reject and ForcedSource tables back to the original names JUSTIFICATION : retaining the original (adopted by the WISE project) naming convention would significantly simplify a documentation and table metadata support efforts for these catalogs on behalf of the LSST/PDAC team. This will also avoid a possible (and unnecessary) confusion which might arise among PDAC users because of the present LSST-way of naming these tables. The procedure took the following steps Making proper modifications to the tools, scripts and input data sets The scripts, configurations and table schemas were updated and saved into GitHub (package): https://github.com/lsst-dm/db_pdac_wise Renaming the data tables This was done by simply running on the PDAC master node the "fix-up" script added earlier into the toolset: % hostname lsst-qserv-master01   % /bin/sudo -u qserv \ /datasets/gapon/development/db_pdac_wise/scripts/run_rename_tables . bash --verbose A similar operation was also performed for the Reject table of Qserv database wise_ext_00 which required a few extra steps of setting up a proper data set specification files (and cleaning up the configuration back to its default state): % cd /datasets/gapon/development/db_pdac_wise/scripts/ % mv dataset. bash dataset. bash .wise_00 % mv dataset. bash .wise_ext_00 dataset. bash % /bin/sudo -u qserv run_rename_tables. bash --verbose % mv dataset. bash dataset. bash .wise_ext_00 % mv dataset. bash .wise_00 dataset. bash .wise The log files retain on the local file system of the master node has not revealed any problem. The log files were placed at: % ls -l /tmp/wise_00/log/ -rw-r--r-- 1 qserv qserv 3312209 May 4 15:10 qserv-db01_rename_tables.log -rw-r--r-- 1 qserv qserv 3312225 May 4 15:10 qserv-db02_rename_tables.log ... -rw-r--r-- 1 qserv qserv 3311567 May 4 15:10 qserv-db29_rename_tables.log -rw-r--r-- 1 qserv qserv 3311567 May 4 15:10 qserv-db30_rename_tables.log -rw-r--r-- 1 qserv qserv 291 May 4 15:07 qserv-master01_rename_tables.log Renaming the secondary index tables The secondary index tables of both Object and Reject tables were manually renamed by running the following operations directly on the database server of the PDAC master : MariaDB [qservMeta]> SHOW TABLES; + ---------------------------------------+ | Tables_in_qservMeta | + ---------------------------------------+ | wise_00__Object | | wise_ext_00__Reject | + ---------------------------------------+   MariaDB [qservMeta]> RENAME TABLE qservMeta.wise_00__Object TO qservMeta.wise_00__allwise_cat; MariaDB [qservMeta]> RENAME TABLE qservMeta.wise_ext_00__Reject TO qservMeta.wise_ext_00__allwise_rej;   MariaDB [qservMeta]> SHOW TABLES; + ---------------------------------------+ | Tables_in_qservMeta | + ---------------------------------------+ | wise_00__allwise_cat | | wise_ext_00__allwise_rej | + ---------------------------------------+ Making changes to Qserv CSS The current CSS has: serv-admin.py "DUMP EVERYTHING" | grep wise_ /DBS/wise_00 READY /DBS/wise_00/.packed.json {"partitioningId":"0000000018","releaseStatus":"RELEASED","storageClass":"L2"} /DBS/wise_00/TABLES \N /DBS/wise_00/TABLES/allwise_p3as READY /DBS/wise_00/TABLES/allwise_p3as/schema ( /DBS/wise_00/TABLES/ForcedSource READY /DBS/wise_00/TABLES/ForcedSource/partitioning \N /DBS/wise_00/TABLES/ForcedSource/partitioning/.packed.json {"dirColName":"source_id_mf","dirDb":"wise_00","dirTable":"Object","latColName":"decl","lonColName":"ra","subChunks":"0"} /DBS/wise_00/TABLES/ForcedSource/schema ( /DBS/wise_00/TABLES/ForcedSource/sharedScan \N /DBS/wise_00/TABLES/ForcedSource/sharedScan/.packed.json {"lockInMem":"1"} /DBS/wise_00/TABLES/Object READY /DBS/wise_00/TABLES/Object/partitioning \N /DBS/wise_00/TABLES/Object/partitioning/.packed.json {"dirColName":"source_id","dirDb":"wise_00","dirTable":"Object","latColName":"decl","lonColName":"ra","subChunks":"1"} /DBS/wise_00/TABLES/Object/schema ( /DBS/wise_00/TABLES/Object/sharedScan \N /DBS/wise_00/TABLES/Object/sharedScan/.packed.json {"lockInMem":"1"} /DBS/wise_ext_00 READY /DBS/wise_ext_00/.packed.json {"partitioningId":"0000000020","releaseStatus":"RELEASED","storageClass":"L2"} /DBS/wise_ext_00/TABLES \N /DBS/wise_ext_00/TABLES/Reject READY /DBS/wise_ext_00/TABLES/Reject/partitioning \N /DBS/wise_ext_00/TABLES/Reject/partitioning/.packed.json {"dirColName":"source_id","dirDb":"wise_ext_00","dirTable":"Reject","latColName":"decl","lonColName":"ra","subChunks":"1"} /DBS/wise_ext_00/TABLES/Reject/schema ( /DBS/wise_ext_00/TABLES/Reject/sharedScan \N /DBS/wise_ext_00/TABLES/Reject/sharedScan/.packed.json {"lockInMem":"1"} The configuration was cleared with: qserv-admin.py "DROP DATABASE wise_00" ; qserv-admin.py "DROP DATABASE wise_ext_00" ; And the new one reinstalled (for database wise_00 and wise_ext_00 ) with: % cd /datasets/gapon/development/db_pdac_wise/scripts/   % /bin/sudo -u qserv setup_css. bash --verbose % /bin/sudo -u qserv setup_css_nonpart. bash --verbose   % mv dataset. bash dataset. bash .wise_00 % mv dataset. bash .wise_ext_00 dataset. bash   % /bin/sudo -u qserv setup_css. bash --verbose   % mv dataset. bash dataset. bash .wise_ext_00 % mv dataset. bash .wise_00 dataset. bash .wise After that the new configuration has: % qserv-admin.py "DUMP EVERYTHING" | grep wise_ /DBS/wise_00 READY /DBS/wise_00/.packed.json {"partitioningId":"0000000022","releaseStatus":"RELEASED","storageClass":"L2"} /DBS/wise_00/TABLES \N /DBS/wise_00/TABLES/allwise_cat READY /DBS/wise_00/TABLES/allwise_cat/partitioning \N /DBS/wise_00/TABLES/allwise_cat/partitioning/.packed.json {"dirColName":"source_id","dirDb":"wise_00","dirTable":"allwise_cat","latColName":"decl","lonColName":"ra","subChunks":"1"} /DBS/wise_00/TABLES/allwise_cat/schema ( /DBS/wise_00/TABLES/allwise_cat/sharedScan \N /DBS/wise_00/TABLES/allwise_cat/sharedScan/.packed.json {"lockInMem":"1"} /DBS/wise_00/TABLES/allwise_i3as_mep READY /DBS/wise_00/TABLES/allwise_i3as_mep/partitioning \N /DBS/wise_00/TABLES/allwise_i3as_mep/partitioning/.packed.json {"dirColName":"source_id_mf","dirDb":"wise_00","dirTable":"allwise_cat","latColName":"decl","lonColName":"ra","subChunks":"0"} /DBS/wise_00/TABLES/allwise_i3as_mep/schema ( `source_id_mf` VARCHAR(28) NOT NULL, -- foreign key to the primary key of the allwise_cat table /DBS/wise_00/TABLES/allwise_i3as_mep/sharedScan \N /DBS/wise_00/TABLES/allwise_i3as_mep/sharedScan/.packed.json {"lockInMem":"1"} /DBS/wise_00/TABLES/allwise_p3as READY /DBS/wise_00/TABLES/allwise_p3as/schema ( /DBS/wise_ext_00 READY /DBS/wise_ext_00/.packed.json {"partitioningId":"0000000021","releaseStatus":"RELEASED","storageClass":"L2"} /DBS/wise_ext_00/TABLES \N /DBS/wise_ext_00/TABLES/allwise_rej READY /DBS/wise_ext_00/TABLES/allwise_rej/partitioning \N /DBS/wise_ext_00/TABLES/allwise_rej/partitioning/.packed.json {"dirColName":"source_id","dirDb":"wise_ext_00","dirTable":"allwise_rej","latColName":"decl","lonColName":"ra","subChunks":"1"} /DBS/wise_ext_00/TABLES/allwise_rej/schema ( /DBS/wise_ext_00/TABLES/allwise_rej/sharedScan \N /DBS/wise_ext_00/TABLES/allwise_rej/sharedScan/.packed.json {"lockInMem":"1"} Testing No service restart was needed to enact the changes. All catalogs are now seen with their new names, and simple test queries produce sensible results: USE wise_00; SHOW TABLES; + -------------------+ | Tables_in_wise_00 | + -------------------+ | allwise_cat | | allwise_i3as_mep | | allwise_p3as | + -------------------+   USE wise_ext_00; SHOW TABLES; + -----------------------+ | Tables_in_wise_ext_00 | + -----------------------+ | allwise_rej | + -----------------------+
            Hide
            gapon Igor Gaponenko added a comment -

            Minor fix-up in the table names

            Renamed:

            database old name new name
            wise_00 allwise_cat allwise_p3as_psd
            wise_00 allwise_p3as allwise_p3as_cdd
            wise_00 allwise_i3as_mep allwise_p3as_mep
            wise_ext_00 allwise_rej allwise_p3as_psr

            Tested results by running queries against these tables.

            Show
            gapon Igor Gaponenko added a comment - Minor fix-up in the table names Renamed: database old name new name wise_00 allwise_cat allwise_p3as_psd wise_00 allwise_p3as allwise_p3as_cdd wise_00 allwise_i3as_mep allwise_p3as_mep wise_ext_00 allwise_rej allwise_p3as_psr Tested results by running queries against these tables.
            Hide
            gapon Igor Gaponenko added a comment -

            All catalogs requested in a scope of the current ticket has been loaded and tested. Closing this ticket.

            Note that there are 3 more relevant catalogs which will be loaded as a separate effort documented at DM-10740

            Show
            gapon Igor Gaponenko added a comment - All catalogs requested in a scope of the current ticket has been loaded and tested. Closing this ticket. Note that there are 3 more relevant catalogs which will be loaded as a separate effort documented at DM-10740

              People

              Assignee:
              gapon Igor Gaponenko
              Reporter:
              fritzm Fritz Mueller
              Watchers:
              Fritz Mueller, Gregory Dubois-Felsmann, Igor Gaponenko
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: