Database Maintenance - Revision history

Dgary: /* Keeping the MySQL database up to date */

2022-07-20T09:45:13Z

Keeping the MySQL database up to date

← Older revision		Revision as of 09:45, 20 July 2022
Line 181:		Line 181:
	s2m.sync2mysql()		s2m.sync2mysql()
	</pre>		</pre>
	This is the command that will be run daily to keep the databases in sync. It should take only a few minutes to run if done daily.		This is the command that will be run daily to keep the MS SQL and local MySQL databases in sync. It should take only a few minutes to run if done daily. It also dumps the updated data to a file /data1/YYYY-MM-DD.sql, which can be transferred to the cloud MySQL using the command:
			<pre>
			mysql -h eovsa-db0.cgb0fabhwkos.us-west-2.rds.amazonaws.com -u admin -p eOVSA < /data1/YYYY-MM-DD.sql
			</pre>

	==Maintaining the abin table==		==Maintaining the abin table==

Dgary: /* Keeping the MySQL database up to date */

2022-05-24T13:38:01Z

Keeping the MySQL database up to date

← Older revision		Revision as of 13:38, 24 May 2022
Line 179:		Line 179:
	<pre>		<pre>
	import eovsactl.sql2mysql as s2m		import eovsactl.sql2mysql as s2m
	s2m.~~sql2mysql~~()		s2m.sync2mysql()
	</pre>		</pre>
	This is the command that will be run daily to keep the databases in sync. It should take only a few minutes to run if done daily.		This is the command that will be run daily to keep the databases in sync. It should take only a few minutes to run if done daily.

Dgary: /* Maintaining the abin table */

2022-05-24T13:37:38Z

Maintaining the abin table

← Older revision		Revision as of 13:37, 24 May 2022
Line 189:		Line 189:

	A further complication is that the MS SQL is not the only source of new abin records. By having a copy of the MySQL database in the cloud, abin records can be written by other users based on their new analysis of calibration data. For security reasons these records will be written to a "staging" table and at some regular cadence (e.g. 30 minutes) those records will be vetted (by some as-yet undefined means) and then transferred to the actual abin table. Those vetted records could first be written to the MS SQL abin table to keep it up to date, but then latency is a worry. Probably they can be written simultaneously to MS SQL and MySQL.		A further complication is that the MS SQL is not the only source of new abin records. By having a copy of the MySQL database in the cloud, abin records can be written by other users based on their new analysis of calibration data. For security reasons these records will be written to a "staging" table and at some regular cadence (e.g. 30 minutes) those records will be vetted (by some as-yet undefined means) and then transferred to the actual abin table. Those vetted records could first be written to the MS SQL abin table to keep it up to date, but then latency is a worry. Probably they can be written simultaneously to MS SQL and MySQL.

			I have adapted the code in the eovsapy.cal_header module to automatically update all three databases if an abin record is written at OVRO. The routine that allows that to happen is eovsactl.sql2mysql.abin2all3().

Dgary: /* Keeping the MySQL database up to date */

2022-05-24T13:34:13Z

Keeping the MySQL database up to date

← Older revision		Revision as of 13:34, 24 May 2022
Line 175:		Line 175:
	</pre>		</pre>
	with no time specified. The latest Id from MySQL is read and all newer records are transferred from MS SQL up to the latest one.		with no time specified. The latest Id from MySQL is read and all newer records are transferred from MS SQL up to the latest one.

			I have written a 1-liner that will send all fV* and hV* new records in the MS SQL database to the MySQL databases on both localhost and the amazonaws cloud:
			<pre>
			import eovsactl.sql2mysql as s2m
			s2m.sql2mysql()
			</pre>
			This is the command that will be run daily to keep the databases in sync. It should take only a few minutes to run if done daily.

	==Maintaining the abin table==		==Maintaining the abin table==

Dgary: /* Maintaining the abin table */

2022-05-17T13:05:34Z

Maintaining the abin table

← Older revision		Revision as of 13:05, 17 May 2022
Line 179:		Line 179:
	Note that among the above tables the abin table is unique in that new records can be written at any time with a timestamp that refers to the past, and in fact new ones have to be written well after the date of the data as new calibrations are done. Sometimes existing abin entries have to be overwritten with new ones using the same timestamp. Thus, it is not enough to update the abin table simply by updating a date range based on timestamp. However, the abin table Id column auto-increments so it should be enough to take the last abin record in MySQL and find it in the MS SQL, then update MySQL with all Ids larger than that. In fact this MySQL query:		Note that among the above tables the abin table is unique in that new records can be written at any time with a timestamp that refers to the past, and in fact new ones have to be written well after the date of the data as new calibrations are done. Sometimes existing abin entries have to be overwritten with new ones using the same timestamp. Thus, it is not enough to update the abin table simply by updating a date range based on timestamp. However, the abin table Id column auto-increments so it should be enough to take the last abin record in MySQL and find it in the MS SQL, then update MySQL with all Ids larger than that. In fact this MySQL query:
	select Id,Timestamp from abin order by Id desc limit 1		select Id,Timestamp from abin order by Id desc limit 1
	returns the laset MySQL record, which I find has the same Id as the corresponding record in MS SQL so if we always transfer from MS SQL to MySQL the record numbers should remain in sync and we can just find newer (greater) Id numbers.		returns the laset MySQL record, which I find has the same Id as the corresponding record in MS SQL so if we always transfer from MS SQL to MySQL the record numbers should remain in sync and we can just find newer (greater) Id numbers. This is what the above <pre>sqltable2mysql('abin')</pre> call does.

	A further complication is that the MS SQL is not the only source of new abin records. By having a copy of the MySQL database in the cloud, abin records can be written by other users based on their new analysis of calibration data. For security reasons these records will be written to a "staging" table and at some regular cadence (e.g. 30 minutes) those records will be vetted (by some as-yet undefined means) and then transferred to the actual abin table. Those vetted records could first be written to the MS SQL abin table to keep it up to date, but then latency is a worry. Probably they can be written simultaneously to MS SQL and MySQL.		A further complication is that the MS SQL is not the only source of new abin records. By having a copy of the MySQL database in the cloud, abin records can be written by other users based on their new analysis of calibration data. For security reasons these records will be written to a "staging" table and at some regular cadence (e.g. 30 minutes) those records will be vetted (by some as-yet undefined means) and then transferred to the actual abin table. Those vetted records could first be written to the MS SQL abin table to keep it up to date, but then latency is a worry. Probably they can be written simultaneously to MS SQL and MySQL.

Dgary: /* Keeping the MySQL database up to date */

2022-05-17T13:02:13Z

Keeping the MySQL database up to date

← Older revision		Revision as of 13:02, 17 May 2022
Line 167:		Line 167:
	s2m.sqltable2mysql('fV66_vD1',Time(['2022-05-01','2022-05-31']))		s2m.sqltable2mysql('fV66_vD1',Time(['2022-05-01','2022-05-31']))
	</pre>		</pre>
	Note that when specifying a stateframe table (those starting with 'f', e.g. 'fV66_vD1'), only the columns noted above are transferred, and only those during times when EOVSA actually has data. This is accomplished by getting the scan times from FDB files, which must be on the system. For most tables, transferring a month of data from MS SQL to MySQL takes only a short time (minutes), but for the fV66_D15 table it can take several hours, so running that command ~~in a~~ '''screen''' is recommended.		Note that when specifying a stateframe table (those starting with 'f', e.g. 'fV66_vD1'), only the columns noted above are transferred, and only those during times when EOVSA actually has data. This is accomplished by getting the scan times from FDB files, which must be on the system. For most tables, transferring a month of data from MS SQL to MySQL takes only a short time (minutes), but for the fV66_D15 table it can take several hours, so running that command using '''screen''' is recommended.

	The abin table is handled differently since the records are not in time order. To transfer all records later than the most recent record in MySQL		The abin table is handled differently since the records are not in time order. To transfer all records later than the most recent record in MySQL

Dgary: /* Keeping the MySQL database up to date */

2022-05-17T13:01:56Z

Keeping the MySQL database up to date

← Older revision		Revision as of 13:01, 17 May 2022
Line 167:		Line 167:
	s2m.sqltable2mysql('fV66_vD1',Time(['2022-05-01','2022-05-31']))		s2m.sqltable2mysql('fV66_vD1',Time(['2022-05-01','2022-05-31']))
	</pre>		</pre>
	Note that when specifying a stateframe table (those starting with 'f', e.g. 'fV66_vD1'), only the columns noted above are transferred, and only those during times when EOVSA actually has data. This is accomplished by getting the scan times from FDB files, which must be on the system. For most tables, transferring a month of data from MS SQL to MySQL takes only a short time (minutes), but for the fV66_D15 table it can take ~~more than one hour~~.		Note that when specifying a stateframe table (those starting with 'f', e.g. 'fV66_vD1'), only the columns noted above are transferred, and only those during times when EOVSA actually has data. This is accomplished by getting the scan times from FDB files, which must be on the system. For most tables, transferring a month of data from MS SQL to MySQL takes only a short time (minutes), but for the fV66_D15 table it can take several hours, so running that command in a '''screen''' is recommended.

	The abin table is handled differently since the records are not in time order. To transfer all records later than the most recent record in MySQL		The abin table is handled differently since the records are not in time order. To transfer all records later than the most recent record in MySQL

Dgary: /* MySQL Tables */

2022-05-17T13:00:24Z

MySQL Tables

← Older revision		Revision as of 13:00, 17 May 2022
Line 88:		Line 88:
	For ease of portability, this new database is in MySQL format, which is accessed via Python using the mysql.connector routines. And to make it truly usable from anywhere, we have created an instance in the AWS RDB cloud service.		For ease of portability, this new database is in MySQL format, which is accessed via Python using the mysql.connector routines. And to make it truly usable from anywhere, we have created an instance in the AWS RDB cloud service.

	==MySQL Tables==		==Creating the Database==
			===MySQL Tables===
	The MySQL database name is eOVSA, and contains tables that are created with the following SQL statements (part of the mysql_tables.txt file in the SourceCat folder of the eovsapy software):		The MySQL database name is eOVSA, and contains tables that are created with the following SQL statements (part of the mysql_tables.txt file in the SourceCat folder of the eovsapy software):
	<pre>		<pre>
Line 154:		Line 155:
	);		);
	</pre>		</pre>

			===Backup and Restore===
			The primary MySQL database is on the Pipeline machine at OVRO. Records are periodically (daily) transferred to that database from MS SQL and the AWS Cloud database must then be updated to correspond. To create the initial Cloud databse, I used the command on Pipeline:
			mysqldump -u root -p eOVSA > /data1/dgary/eOVSA_20220517.sql
			which took about two hours to create the full backup (on 2022 May 17, when the database was about 130 GB).

	==Keeping the MySQL database up to date==		==Keeping the MySQL database up to date==

Dgary: /* Keeping the MySQL database up to date */

2022-05-16T20:25:27Z

Keeping the MySQL database up to date

← Older revision		Revision as of 20:25, 16 May 2022
Line 156:		Line 156:

	==Keeping the MySQL database up to date==		==Keeping the MySQL database up to date==
	Once these tables are created, they can be filled by querying the MS SQL database. The routines for doing this are in the Python3 eovsactl package, in the module sql2mysql. A single routine can handle the transfer to each table, one at a time. For example, the following lines will transfer all ~~abin~~ records taken during May 2022.		Once these tables are created, they can be filled by querying the MS SQL database. The routines for doing this are in the Python3 eovsactl package, in the module sql2mysql. A single routine can handle the transfer to each table, one at a time. For example, the following lines will transfer all records from table fV66_vD1 taken during May 2022.
	<pre>		<pre>
	import eovsactl.sql2mysql as s2m		import eovsactl.sql2mysql as s2m
	s2m.sqltable2mysql('~~abin~~',Time(['2022-05-01','2022-05-31']))		s2m.sqltable2mysql('fV66_vD1',Time(['2022-05-01','2022-05-31']))
	</pre>		</pre>
	Note that when specifying a stateframe table (those starting with 'f', e.g. 'fV66_vD1'), only the columns noted above are transferred, and only those during times when EOVSA actually has data. This is accomplished by getting the scan times from FDB files, which must be on the system. For most tables, transferring a month of data from MS SQL to MySQL takes only a short time (minutes), but for the fV66_D15 table it can take more than one hour.		Note that when specifying a stateframe table (those starting with 'f', e.g. 'fV66_vD1'), only the columns noted above are transferred, and only those during times when EOVSA actually has data. This is accomplished by getting the scan times from FDB files, which must be on the system. For most tables, transferring a month of data from MS SQL to MySQL takes only a short time (minutes), but for the fV66_D15 table it can take more than one hour.

			The abin table is handled differently since the records are not in time order. To transfer all records later than the most recent record in MySQL
			<pre>
			import eovsactl.sql2mysql as s2m
			s2m.sqltable2mysql('abin')
			</pre>
			with no time specified. The latest Id from MySQL is read and all newer records are transferred from MS SQL up to the latest one.

	==Maintaining the abin table==		==Maintaining the abin table==

Dgary: /* Maintaining the abin table */

2022-05-16T19:02:16Z

Maintaining the abin table

← Older revision		Revision as of 19:02, 16 May 2022
Line 168:		Line 168:
	returns the laset MySQL record, which I find has the same Id as the corresponding record in MS SQL so if we always transfer from MS SQL to MySQL the record numbers should remain in sync and we can just find newer (greater) Id numbers.		returns the laset MySQL record, which I find has the same Id as the corresponding record in MS SQL so if we always transfer from MS SQL to MySQL the record numbers should remain in sync and we can just find newer (greater) Id numbers.

	A further complication is that the MS SQL is not the only source of new abin records. By having a copy of the MySQL database in the cloud, abin records can be written by other users based on their new analysis of calibration data. For security reasons these records will be written to a "staging" table and at some regular cadence (e.g. 30 minutes) those records will be vetted (by some as-yet undefined means) and then transferred to the actual abin table. Those vetted records could ~~also~~ be written to the MS SQL abin table to keep it up to date.		A further complication is that the MS SQL is not the only source of new abin records. By having a copy of the MySQL database in the cloud, abin records can be written by other users based on their new analysis of calibration data. For security reasons these records will be written to a "staging" table and at some regular cadence (e.g. 30 minutes) those records will be vetted (by some as-yet undefined means) and then transferred to the actual abin table. Those vetted records could first be written to the MS SQL abin table to keep it up to date, but then latency is a worry. Probably they can be written simultaneously to MS SQL and MySQL.