Data import and export
In the CSC computing environment we recommend command mysqlimport for loading datasets into the database. This command reads in a delimited text file into a table already existing in the database. To load a large text file from a CSC computing server to a database in Kaivos, you can use the command syntax:
mysqlimport -h kaivos.csc.fi -u db_user_account --local --compress --password database_name input_file.table
mysqlimport strips any extension from the input file name and uses the result to determine the name of the table into which to import the file's contents. The user must make sure that the database contains a previously created table with data columns that match the data in the input data file. The user may also have to change the name of the input file if it is not compatible with a database table. The --local option defines that the data file locates in the machine where the client is used and not in the actual database server. Thus in the case of mysqlimport, this option is obligatory in the computing servers of CSC.
Downloading complete tables or databases from the database can be done with command mysqldump. This command was developed for making backup copies of MySQL databases. In the case of kaivos.csc.fi, backup copies are not needed as the database is automatically backup copied by CSC. Instead mysqldump offers an easy way to make a copy of your database so that you can move both the data content and structure of your database to another SQL server. You can make a copy of the whole database:
mysqldump -u db_user_account -h kaivos.csc.fi -p database > database_dump.txt
or just from one or more tables:
mysqldump -u db_user_account -h kaivos.csc.fi -p database table_name > table_dump.txt
When mysqldump is used with the default settings, the result files contain MySQL commands that are needed to create and populate the selected database tables. mysqldump locks the table in the beginning of the copying. Because of this only the dbname_admin user account of the database can launch the command by default. In the case of other user accounts (dbname_read or dbname_user) --skip-lock-tables option should be added to the mysqldump command.
You can import the database_dump.txt file as follows:
mysql -u database_user_account -h kaivos.csc.fi -p database < database_dump.txt
Example: Importing data with mysqlimport
In this example we import a data set into the result table created in the example in chapter 2. The data to be imported to the database locates in file: data_to_import.txt. This file contains data rows like:
1 -419.557 STRUCTURE1.PDB
2 -479.662 STRUCTURE2.PDB
3 -517.019 STRUCTURE3.PDB
4 -450.922 STRUCTURE4.PDB
5 -421.991 STRUCTURE5.PDB
6 -507.076 STRUCTURE6.PDB
7 -444.598 STRUCTURE7.PDB
8 -444.552 STRUCTURE8.PDB
9 -414.492 STRUCTURE9.PDB
10 -444.549 STRUCTURE10.PDB
11 -463.394 STRUCTURE11.PDB
12 -430.548 STRUCTURE12.PDB
etc...
To import the data to the results table, we must first copy the data to a file with a name that is compatible with the table name (results).
cp data_to_import.txt results.table
Then use mysqlimport to import the data
mysqlimport -h kaivos.csc.fi --local --compress -p -u DB_A_admin DB_A results.table