View source for SQL Notes

[[Date::2019]]

= How to pull and load data from SELECT query =
05.06.2019

Login to mariadb; Save the result of a SELECT query to a file

Links:
* https://mariadb.com/kb/en/library/load-data-infile/
* https://mariadb.com/kb/en/library/select-into-outfile/

<source lang="sql">
 use wiki2016;
 SELECT * INTO OUTFILE 'pages.dat' FROM wiki2016.page WHERE page_id < 10 AND page_namespace = 0;
</source>


This is possible in the '''localhost''', however it not viable if you connecting to a '''remote myslq/maridb server'''. 
In which case mariadb/myql can be invoked with an expression and the output written to a local file.

 mariadb -h your.db.host -u dbuser -p -e 'SELECT * FROM wiki2016.page WHERE page_id < 10 AND page_namespace = 0;
' > pages.dat  


Pages pages.dat stored in /var/lib/mysql/wiki2016

dump only the db schema without data
 
$ mysqldump --user=andre --default-character-set=utf8 --no-data --skip-triggers "wiki2016" -p

create a new db

<source lang="sql">
 CREATE DATABASE wikitest;
</source>



And import the import schema 
 $ mysql --user=andre wikitest -p < wiki2016.sql 

Load the data file with default tab separator and one entry per line.  And test if the data was inserted to the DB
<source lang="sql">
 LOAD DATA INFILE '/var/lib/mysql/wiki2016/pages.dat' REPLACE INTO TABLE wikitest.page;
 use wikitest;
 SELECT * FROM page;

+---------+----------------+--------------------+-------------------+------------------+-------------+----------------+----------------+--------------------+-------------+----------+--------------------+-----------+
| page_id | page_namespace | page_title         | page_restrictions | page_is_redirect | page_is_new | page_random    | page_touched   | page_links_updated | page_latest | page_len | page_content_model | page_lang |
+---------+----------------+--------------------+-------------------+------------------+-------------+----------------+----------------+--------------------+-------------+----------+--------------------+-----------+
|       1 |              0 | Main_Page          |                   |                0 |           0 | 0.849653152175 | 20190115085635 | 20190519111542     |        2458 |     1776 | wikitext           | NULL      |
|       2 |              0 | Test               |                   |                0 |           1 | 0.473474610327 | 20150318120109 | 20140909225556     |           4 |       18 | wikitext           | NULL      |
|       6 |              0 | WDKA               |                   |                0 |           0 |  0.43307080073 | 20190305091605 | 20190305091605     |        2464 |    12863 | wikitext           | NULL      |
|       8 |              0 | PageName           |                   |                0 |           1 | 0.033588984951 | 20150318120053 | 20140910082629     |          18 |       30 | wikitext           | NULL      |
|       9 |              0 | Calendars:PageName |                   |                0 |           1 | 0.976488411003 | 20150318120218 | 20140910082630     |          19 |        0 | wikitext           | NULL      |
+---------+----------------+--------------------+-------------------+------------------+-------------+----------------+----------------+--------------------+-------------+----------+--------------------+-----------+
5 rows in set (0.00 sec)
</source>

[[Section::Code Notes| ]][[File:Example.jpg]]