{"id":45,"date":"2018-05-07T14:51:51","date_gmt":"2018-05-07T14:51:51","guid":{"rendered":"http:\/\/ross2.oas.inaf.it\/wp\/imprs18\/?page_id=45"},"modified":"2021-12-10T11:51:57","modified_gmt":"2021-12-10T09:51:57","slug":"step-by-step-commands","status":"publish","type":"page","link":"https:\/\/ross2.oas.inaf.it\/wp\/imprs18\/the-course\/step-by-step-commands\/","title":{"rendered":"Step by step commands"},"content":{"rendered":"

You must have SID<\/a> or DIF<\/a> installed to run the SQL commands that use spherical coordinates functions \/ procedures.<\/p><\/blockquote>\n

Open two terminals<\/strong> and in one start the MySQL client application as root<\/tt>, preferably from the Data<\/tt> working directory (and with the files used in the examples present in it), and give the (MySQL) root password:
\nshell> mysql --local-infile=1 -u root -p<\/code>
\nBelow the MySQL client default prompt mysql><\/tt> (or MariaDB [...]><\/tt>) is omitted for an easier cut-and-paste.<\/p>\n

Create the mpe2018db<\/tt> database:<\/p>\n

\r\ncreate database mpe2018db;\r\n<\/pre>\n
Create a course reference user mpeusr<\/tt>:<\/p>\n
\r\ncreate user mpeusr@localhost identified by \"mpe2018pass\";\r\n<\/pre>\n
Grant all privileges (select, insert, delete, update, ...<\/tt>) to user mpeusr<\/tt> on the database mpe2018db<\/tt>:<\/p>\n
\r\ngrant all privileges on mpe2018db.* to mpeusr@localhost;\r\nflush privileges;\r\nexit;\r\n<\/pre>\n
Because you can perform SQL queries directly from the shell command line using the option -e<\/tt> for the mysql<\/tt> command, lets give some simple usage examples:<\/p>\n
\r\nshell> mysql -u mpeusr -pmpe2018pass mpe2018db -e \"show databases\"\r\nshell> mysql -u mpeusr -pmpe2018pass mpe2018db -e \"show tables from information_schema\"\r\nshell> mysql -u mpeusr -pmpe2018pass -e \"describe information_schema.tables\"\r\nshell> mysql -u mpeusr -pmpe2018pass -e \"describe columns\" information_schema\r\nshell> mysql -u mpeusr -p -e \"select version(), database(), user(), connection_id()\"\r\nEnter password: mpe2018pass\r\nshell> mysql -u mpeusr -p -e \"select curdate(), curtime(), now()\" mpe2018db\r\nEnter password: mpe2018pass\r\n<\/pre>\n
In these cases the database name mpe2018db<\/tt> could be omitted because we are not using any table in it. It will be mandatory for other queries we will use in the course.
\nNow let’s login as mpeusr<\/tt> and move to the mpe2018db<\/tt> DB:<\/p>\n
\r\nshell> mysql -u mpeusr -p\r\nEnter password: mpe2018pass\r\n<\/pre>\n
If you want to avoid to give user and password at each mysql<\/tt> invocation, you can set the default values in the special file .my.cnf<\/tt> placed in your home directory ($HOME<\/tt>):<\/p>\n
\r\n[client]\r\nuser = mpeusr\r\npassword = mpe2018pass\r\n<\/pre>\n
Of course, if you prefer to work always with the root privileges, can set user = root<\/code> and set the corresponding password. See also below.<\/p>\n
\r\nuse mpe2018db;\r\n-- see who you are and in which database\r\nselect user(), current_user(), database();\r\n+------------------+------------------+------------+\r\n| user()           | current_user()   | database() |\r\n+------------------+------------------+------------+\r\n| mpeusr@localhost | mpeusr@localhost | mpe2018db  |\r\n+------------------+------------------+------------+\r\n<\/pre>\n
In MySQL commands \/ functions \/ procedures \/ column names \/ etc. are NOT<\/strong> case sensitive. However, to be compliant with any operating system, you must assume that anything connected to your machine, like the database<\/strong>, table<\/strong> and user<\/strong> names ARE<\/strong> case sensitive!<\/p><\/blockquote>\n
First demo tables<\/h3>\n
Note:<\/strong> We assume that you have created a working directory, e.g. mpe2018<\/tt>, in your $HOME<\/tt><\/p>\n
\r\nshell> cd\r\nshell> mkdir mpe2018\r\nshell> cd mpe2018\r\nshell> mkdir Data\r\nshell> ...\r\n<\/pre>\n
and downloaded there (or will download when requested) the files with a directory structure like that reported in the course summary<\/a>.<\/p>\n
An optical catalogue: the UCAC-2<\/strong>
\nFrom the second terminal just copy the UCAC2 table files (MyISAM format) from the Data<\/tt> directory into the mpe2018db<\/tt> database directory. Can check its location in several ways, depending on installation and OS. These methods are generic:
\nshell> mysqladmin -u root -p variables | grep datadir<\/code>
\nor
\nshell> mysql -u root -p -e \"show variables like 'datadir'\"<\/code><\/p>\n
or from the MySQL client terminal:<\/p>\n
\r\nSHOW VARIABLES LIKE 'datadir'; \r\n+---------------+-----------------------+\r\n| Variable_name | Value                 |\r\n+---------------+-----------------------+\r\n| datadir       | \/usr\/local\/mysql\/var\/ |\r\n+---------------+-----------------------+\r\n<\/pre>\n
Assuming it is in \/var\/lib\/mysql\/<\/tt>:
\nshell> sudo chown mysql:mysql ~\/mpe2018\/Data\/ucac2_initial.*<\/code>
\nshell> sudo cp -a ~\/mpe2018\/Data\/ucac2_initial.* \/var\/lib\/mysql\/mpe2018db\/<\/code><\/p>\n
To avoid duplicated file, instead of cp -a<\/tt> you could have used mv<\/tt> !
\nLet’s check the catalogue:<\/p>\n
\r\n-- be sure the table is visible\r\nflush tables;\r\n-- show its structure\r\ndescribe ucac2_initial;\r\n-- show the number of rows it has\r\nselect count(*) from ucac2_initial;\r\n<\/pre>\n
We’ll use this catalogue later (see below).<\/p>\n
The system information_schema<\/code> table:<\/p>\n
\r\nshow tables from information_schema;\r\ndescribe information_schema.tables;\r\ndescribe information_schema.columns;\r\nselect * from information_schema.tables where table_name='ucac2_initial';\r\nselect * from information_schema.columns where table_name='ucac2_initial';\r\nSELECT table_name FROM INFORMATION_SCHEMA.TABLES where table_schema='mpe2018db' AND table_type != 'View';\r\nSELECT table_name FROM INFORMATION_SCHEMA.TABLES where table_schema='mpe2018db' AND table_type = 'View';\r\nSELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_name like 'sky%';\r\n<\/pre>\n
An X-ray catalogue from the Chandra archive<\/strong> (see https:\/\/cxc.cfa.harvard.edu\/csc\/cli\/<\/a>).
\nCreate the Chandra sources demo table using the SQL dump file (see file):<\/p>\n
\r\nsource ~\/mpe2018\/Soft\/SQL\/crea_cx_tab.sql\r\nshow create table cx_src1;\r\n\r\n-- show the number of rows\r\nselect count(*) from cx_src1;\r\n<\/pre>\n
Import the Chandra sources demo table created with the command (in the Data<\/tt> dir., where csc_query.sql<\/tt> is located):
\nshell> curl -o csc_out.csv --form coordFormat=decimal --form outputFormat=csv --form nullAppearance=NULL --form query=@csc_query.sql \"https:\/\/cda.cfa.harvard.edu\/csccli\/getProperties\"<\/code>
\nHave a look to the output file csc_query.csv<\/tt>.<\/p>\n
\r\nload data local infile \"~\/mpe2018\/Data\/csc_out.csv\" into table cx_src1 IGNORE 11 LINES;\r\n<\/pre>\n
or, to make explicit the defaults parameters:<\/p>\n
\r\nload data local infile \"~\/mpe2018\/Data\/csc_out.csv\" into table cx_src1 FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n' IGNORE 11 LINES;\r\n<\/pre>\n
If LOCAL<\/code> capability is disabled, on either the server or client side, a client that attempts to issue a LOAD DATA LOCAL<\/tt> statement receives the following error message:<\/p>\n
\r\nERROR 1148: The used command is not allowed with this MySQL version<\/tt><\/code>\r\n<\/pre>\n
See the MySQL reference page<\/a>.<\/p>\n
Will note Warnings: 35862<\/tt>. That is because of logical fields marked TRUE\/FALSE and empty fields that are not managed correctly. In fact:<\/p>\n
\r\nshow warnings;\r\nselect * from cx_src1 limit 10;\r\n\r\nselect count(*) from cx_src1 where extent_flag=1 or conf_flag=1;\r\n-- will return 0\r\n<\/pre>\n
We should have used BIT datatype, but it is not recommended. Then we use the correct import transforming them into 1\/0 and also consider empty flux columns:<\/p>\n
\r\nload data infile \"~\/mpe2018\/Data\/csc_out.csv\" into table cx_src1 IGNORE 11 LINES (name,ra,de,@c4,err_ellipse_r0,err_ellipse_r1,@c7,@c8,@c9,significance) set extent_flag=(@c4 like \"%TRUE\"), conf_flag=(@c7 like \"%TRUE\"), flux_aper_w = nullif(@c8,\"\"), flux_aper_b = nullif(@c9,\"\");\r\n<\/pre>\n
Note the use of local variables (starting with @<\/tt>) and the %<\/tt> in the like<\/tt> comparison. Note also that nullif(exp1,expx2)<\/code> returns NULL<\/tt> if expr1=expr2<\/tt>:<\/p>\n
\r\nselect * from cx_src1 limit 10;\r\nselect count(*) from cx_src1 where extent_flag=1 or conf_flag=1;\r\n-- will now return 3282\r\n<\/pre>\n
\r\nselect count(*) from cx_src1 where isnull(flux_aper_b);\r\n<\/pre>\n
or<\/p>\n
\r\nselect count(*) from cx_src1 where flux_aper_b is null;\r\n-- will return 11193\r\n<\/pre>\n
See also the function ifnull()<\/code>.<\/p>\n
Some SQL commands you can try:<\/p>\n
\r\nhelp\r\nhelp contents\r\nstatus\r\nprompt MySQL>\r\nprompt \\u@\\h [\\d]>\r\nprompt  -- back to default prompt\r\nshow tables;\r\nshow table status like \"cx%\" ;\r\ndelimiter \/\/  -- change the end command delimiter\r\nshow collation \/\/\r\nshow engines\\G\r\ndelimiter ;  -- back to default delimiter\r\nselect version(), database(), user(), connection_id();\r\nselect curdate(), curtime(), now();\r\n\r\nselect @sql_mode;\r\nselect @@sql_mode;\r\n<\/pre>\n
Let’s perform a query on the INFORMATION_SCHEMA<\/tt> to see which tables I have access to, excluding those in INFORMATION_SCHEMA<\/tt> itself:<\/p>\n
\r\n-- see who you are and in which database\r\nselect current_user(), database();\r\n+------------------+------------+\r\n| current_user()   | database() |\r\n+------------------+------------+\r\n| mpeusr@localhost | mpe2018db  |\r\n+------------------+------------+\r\n\r\nuse mpe2018db;\r\nSELECT table_schema, table_name from INFORMATION_SCHEMA.TABLES WHERE table_schema != 'INFORMATION_SCHEMA';\r\n<\/pre>\n
You can customize the MySQL client editing the user specific configuration file $HOME\/.my.cnf<\/tt> (or the system level my.cnf<\/tt>) in the section [client]<\/tt> or using environment variables. An example file content is (comment rows start with #<\/tt>):<\/p>\n
\r\n[client]\r\nuser     = root\r\npassword = _root_password_\r\nhost     = localhost\r\nsocket   = \/tmp\/mysql.sock\r\n#port     = 3306\r\n<\/pre>\n
This would allow the user to enter the MySQL client without the need to enter user and password, but simply typing mysql<\/tt>. For example to list the table in the mpe2018db<\/tt> database:<\/p>\n
\r\nshell> mysql -e \"show tables from mpe2018db\"\r\n<\/pre>\n
Some environment variable also affect the MySQL behavior. From the terminal you can, for example, change the prompt like this:
\n(bash) export MYSQL_PS1=\"\\u@\\h [\\d]> \"<\/code>
\n(tcsh) setenv MYSQL_PS1 \"\\u@\\h [\\d]> \"<\/code>
\nwhich will produce something like: mpeusr@localhost [mpe2018db]><\/tt><\/p>\n
Let’s practice with SQL performing SELECT queries, creating new tables and changing columns format.<\/strong> You do not need to follow the examples in this order. Just make sure you have SID (or DIF) installed.<\/p>\n
Practicing with the Messier Catalogue<\/strong><\/h3>\n
TSV files used: messier.tsv<\/a> and messier_legend.tsv<\/a>
\nDownload the files into your working data directory Data<\/tt> and go to the MySQL client terminal.
\nStep by step exercises using SID here<\/a>.
\nStep by step exercises using DIF here<\/a>.<\/p>\n
Practicing with the Bright Stars Catalogue<\/strong><\/h3>\n
Catalogue reference: https:\/\/cdsarc.u-strasbg.fr\/viz-bin\/Cat?V\/50<\/a>
\nSQL dump files used: bsc.sql<\/a> and bsc.sql<\/a>simpleBSC.sql<\/a> (compact version).
\nDownload the files into your working data directory Data<\/tt> and go to the MySQL client terminal.
\nStep by step exercises here<\/a>.<\/p>\n
Practicing with the ASCC2.5<\/strong><\/h3>\n
Catalogue reference: https:\/\/cdsarc.u-strasbg.fr\/viz-bin\/Cat?I\/280A<\/a>
\nSQL dump file used: ascc25_initial.sql.gz<\/a>
\nCopy and decompress it into the data directory Data<\/tt> and go to the MySQL client terminal.
\nStep by step exercises here<\/a>.<\/p>\n
Practicing with a sample of SDSS<\/strong> galaxies<\/h3>\n
Catalogue reference: SDSS SkyServer<\/a>
\nCSV file used: abell2065_2deg_radius.csv<\/a>
\nCopy it into the directory Data<\/tt> and go to the MySQL client terminal.
\nStep by step exercises here<\/a>.<\/p>\n
Exporting data to CSV file<\/h3>\n
First check which is the MySQL predefined dump directory you can write into:<\/p>\n
\r\nshell> mysqladmin -u root -p  variables | grep secure_file_priv\r\n<\/pre>\n
Returning for example \/var\/lib\/mysql-files\/<\/tt>.
\nThe default value is platform specific and can be changed using the --secure-file-priv=dir<\/tt> option given to the MySQL server deamon mysqld<\/tt>. See the manual page for more details.<\/p>\n
Then use this directory in the query:<\/p>\n
\r\nSELECT ramas\/3.6e6 as radeg, decmas\/3.6e6 as decdeg, Vmm\/1000 as Vmag, (Bmm-Vmm)\/1000 as color\r\nFROM ascc25\r\nWHERE Bmm > -9999 AND Vmm > -9999\r\nLIMIT 1000 -- remove to dump the whole catalogue\r\nINTO OUTFILE '\/var\/lib\/mysql-files\/ascc25_out.csv'\r\nFIELDS TERMINATED BY ',' \r\nOPTIONALLY ENCLOSED BY '\"'\r\nESCAPED BY '\\\\'\r\nLINES TERMINATED BY '\\n';\r\n<\/pre>\n
Check the output file, e.g. sudo vi \/var\/lib\/mysql-files\/ascc25_out.csv<\/code>.<\/p>\n
So let’s explain the various pieces.
\nThe first part before the INTO<\/tt> line is your query. This can be any SELECT<\/tt> query, including conditions and joins, to retrieve the data-set you wish.
\nAfter the SELECT<\/tt> query, the CSV output data definitions start:<\/p>\n
INTO OUTFILE<\/tt>: here you state the path where you want MySQL to store the CSV file. Keep in mind that the path needs to be writable for the MySQL user.
\nFIELD TERMINATED BY ','<\/tt>: this is the field delimiter character. You could have used a semi-colon or a tab (\\t<\/tt>) instead.
\nOPTIONALLY ENCLOSED BY<\/tt>: the character you use to enclose the fields in.
\nESCAPED BY<\/tt>: the escape character used in case of special characters in your output
\nLINES TERMINATED BY<\/tt>: here you define the line-end.<\/p>\n
The LIMIT 1000<\/tt> is just to reduce the output file size.
\nIn the output CSV you might see values as \\N<\/tt> when NULL<\/tt> values are present in the table. This is a required if you want to re-import that CSV file into a DB table.<\/p>\n
User defined SQL functions<\/h3>\n
Let’s add a function to display in scientific notation real\/double values. First exit the MySQL client session and enter as user root:<\/p>\n
\r\nmysql> exit;\r\nshell> mysql -u root -p mpe2018db;\r\n<\/pre>\n
Then let’s create the function (we keep 6 fractional digits):<\/p>\n
\r\ndrop function if exists sci;\r\nDELIMITER $$\r\ncreate function sci (val DOUBLE) returns text\r\ndeterministic language sql\r\nbegin\r\n  set @exp = IF(val=0, 0, FLOOR(LOG10(ABS(val))));\r\n  return CONCAT(FORMAT(val*POWER(10,-@exp), 6), 'e', @exp);\r\nend$$\r\nDELIMITER ;\r\n\r\nselect sci(min(Vmm\/1000)), sci(max(Vmm\/1000)), sci(avg(Vmm\/1000)) from ascc25 where Vmm > -9999;\r\n\r\n+--------------------+--------------------+--------------------+\r\n| sci(min(Vmm\/1000)) | sci(max(Vmm\/1000)) | sci(avg(Vmm\/1000)) |\r\n+--------------------+--------------------+--------------------+\r\n| -1.110000e0        | 1.675000e1         | 1.107064e1         |\r\n+--------------------+--------------------+--------------------+\r\n<\/pre>\n
Assuming the color<\/tt> field is present (if not, create it as the difference between Bmm\/1000<\/tt> and Vmm\/1000<\/tt>):<\/p>\n
\r\nselect sci(min(color)), sci(max(color)), sci(avg(color)) from ascc25 where color != -900;\r\n<\/pre>\n
And now let’s extend the privileges for the user mpeusr<\/tt>:<\/p>\n
\r\nshow grants for mpeusr@localhost;\r\n\r\ngrant execute on function mpe2018db.sci to mpeusr@localhost;\r\n<\/pre>\n
Note how in the examples above we mixed the use of lower \/ upper case for command and function names, as well as for column names. Again, this is because MySQL is not case sensitive!<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"
You must have SID or DIF installed to run the SQL commands that use spherical coordinates functions \/ procedures. Open two terminals […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":16,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-45","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/ross2.oas.inaf.it\/wp\/imprs18\/wp-json\/wp\/v2\/pages\/45"}],"collection":[{"href":"https:\/\/ross2.oas.inaf.it\/wp\/imprs18\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/ross2.oas.inaf.it\/wp\/imprs18\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/ross2.oas.inaf.it\/wp\/imprs18\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ross2.oas.inaf.it\/wp\/imprs18\/wp-json\/wp\/v2\/comments?post=45"}],"version-history":[{"count":2,"href":"https:\/\/ross2.oas.inaf.it\/wp\/imprs18\/wp-json\/wp\/v2\/pages\/45\/revisions"}],"predecessor-version":[{"id":779,"href":"https:\/\/ross2.oas.inaf.it\/wp\/imprs18\/wp-json\/wp\/v2\/pages\/45\/revisions\/779"}],"up":[{"embeddable":true,"href":"https:\/\/ross2.oas.inaf.it\/wp\/imprs18\/wp-json\/wp\/v2\/pages\/16"}],"wp:attachment":[{"href":"https:\/\/ross2.oas.inaf.it\/wp\/imprs18\/wp-json\/wp\/v2\/media?parent=45"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}