Export danych z PostgreSQL-a

Pisałam już o sposobie export-u danych z MySQL (tego posta możecie znaleźć tutaj), dlatego przyszedł teraz czas na PostgreSQL'a.

Chce zwrócić waszą uwagę na dwa polecenia:

• psql - program klienta do połączenia z serwerem
• polecenie SQL: COPY - do kopiowania danych pomiędzy plikiem a tabelą

Program psql

Opcje programu psql przy exporcie danych:

• -c, --command=ZAPYTANIE - wykonuje zapytanie i kończy połączenie z serwerem
• -f, --file=ŚCIEŻKA_DO_PLIKU - możecie także wykonać zapytanie, które jest zapisane do pliku
• -o, --output=ŚCIEŻKA_DO_PLIKU - wynik zapisujemy w podanym pliku
• -H, --html - wynik zapisany jest w formie html-a
• -A, --no-align - dane nie są wyrównywane. Dane w kolumnach są oddzielane domyślnie znakiem pipe: "|". 
• -x, --expanded - dane są wyświetlane wertykalnie (pionowo)
• -t, --tuples-only - tylko dane są zwracane (bez informacji o nazwie kolumn)
• -F, --field-separator=ZNAK - dane w kolumnach są oddzielne nowo zdefiniowanym separatorem
• -R, --record-separator=ZNAK - rekord jest odseparowany przez znak ZNAK, domyślnie jest to znak nowej linii

Przykłady:

∴ /opt/PostgreSQL/9.1/bin/psql -h127.0.0.1 -p7432 -Upostgres --command="select * from test" test
Password for user postgres: 
 c1 |  c2   |             c3             
----+-------+----------------------------
  1 | test  | 2014-01-07 13:30:23.820911
  2 | test2 | 2014-01-07 13:30:29.892732
  3 | test3 | 2014-01-07 13:30:36.644758
  4 | test4 | 2014-01-07 13:30:43.048741
(4 rows)

∴ /opt/PostgreSQL/9.1/bin/psql -h127.0.0.1 -p7432 -Upostgres --command="select * from test" --no-align test
Password for user postgres: 
c1|c2|c3
1|test|2014-01-07 13:30:23.820911
2|test2|2014-01-07 13:30:29.892732
3|test3|2014-01-07 13:30:36.644758
4|test4|2014-01-07 13:30:43.048741
(4 rows)

∴ /opt/PostgreSQL/9.1/bin/psql -h127.0.0.1 -p7432 -Upostgres --command="select * from test" --expanded test
Password for user postgres: 
-[ RECORD 1 ]------------------
c1 | 1
c2 | test
c3 | 2014-01-07 13:30:23.820911
-[ RECORD 2 ]------------------
c1 | 2
c2 | test2
c3 | 2014-01-07 13:30:29.892732
-[ RECORD 3 ]------------------
c1 | 3
c2 | test3
c3 | 2014-01-07 13:30:36.644758
-[ RECORD 4 ]------------------
c1 | 4
c2 | test4
c3 | 2014-01-07 13:30:43.048741

∴ /opt/PostgreSQL/9.1/bin/psql -h127.0.0.1 -p7432 -Upostgres --command="select * from test" --no-align --expanded test
Password for user postgres: 
c1|1
c2|test
c3|2014-01-07 13:30:23.820911

c1|2
c2|test2
c3|2014-01-07 13:30:29.892732

c1|3
c2|test3
c3|2014-01-07 13:30:36.644758

c1|4
c2|test4
c3|2014-01-07 13:30:43.048741

∴ /opt/PostgreSQL/9.1/bin/psql -h127.0.0.1 -p7432 -Upostgres --command="select * from test" --no-align --field-separator=, --tuples-only test
Password for user postgres: 
1,test,2014-01-07 13:30:23.820911
2,test2,2014-01-07 13:30:29.892732
3,test3,2014-01-07 13:30:36.644758
4,test4,2014-01-07 13:30:43.048741

Polecenie COPY

To polecenie ma pewne ograniczenia - tylko superuser kopiować dane do lub z pliku. Jeśli jednak mamy takie prawa, plik który zdefiniujemy dla danych wyjściowych będzie zapisany na maszynie, gdzie działa serwer PostgreSQL.

Jako zwykły użytkownik, możemy tylko kopiować dane do standardowego wyjścia (stdout) lub z standardowego wejścia (stdin).

Opcje, o których warto wspomnieć:

• FORMAT - TEXT (domyślny), CSV, BINARY
• DELIMITER - w zależności od formatu, znak oddzielający dane w kolumnach są różne. Dla TEXT jest to znak tabulator-u, CSV - przecinek.
• NULL - co ma być zwracane gdy pojawi się NULL
• HEADER - czy zwracać nagłówek
• ESCAPE - czy i jak escapować dane

O innych szczegółach możecie przeczytać w dokumentacji tutaj.

Przykłady:

postgres@test(localhost) # COPY test (c1,c2,c3) TO '/tmp/test.csv';
COPY 4

14:23:44 ela@localhost:/tmp  ruby-1.9.3-p194 
∴ cat test.csv 
1 test 2014-01-07 13:30:23.820911
2 test2 2014-01-07 13:30:29.892732
3 test3 2014-01-07 13:30:36.644758
4 test4 2014-01-07 13:30:43.048741

Przekierowanie do standardowego wyjścia:

postgres@test(127.0.0.1) # COPY (select * from test) TO stdout WITH (FORMAT CSV, DELIMITER '|', HEADER ON, FORCE_QUOTE (c2));
c1|c2|c3
1|"test"|2014-01-07 13:30:23.820911
2|"test2"|2014-01-07 13:30:29.892732
3|"test3"|2014-01-07 13:30:36.644758
4|"test4"|2014-01-07 13:30:43.048741
postgres@test(127.0.0.1) # COPY (select * from test) TO stdout WITH (FORMAT CSV, DELIMITER '|', HEADER ON, FORCE_QUOTE (c2,c3));
c1|c2|c3
1|"test"|"2014-01-07 13:30:23.820911"
2|"test2"|"2014-01-07 13:30:29.892732"
3|"test3"|"2014-01-07 13:30:36.644758"
4|"test4"|"2014-01-07 13:30:43.048741"
postgres@test(127.0.0.1) # COPY (select * from test) TO stdout WITH (FORMAT CSV, DELIMITER '|', HEADER ON);
c1|c2|c3
1|test|2014-01-07 13:30:23.820911
2|test2|2014-01-07 13:30:29.892732
3|test3|2014-01-07 13:30:36.644758
4|test4|2014-01-07 13:30:43.048741
postgres@test(127.0.0.1) # COPY (select * from test) TO stdout WITH (FORMAT TEXT, DELIMITER '|');
1|test|2014-01-07 13:30:23.820911
2|test2|2014-01-07 13:30:29.892732
3|test3|2014-01-07 13:30:36.644758
4|test4|2014-01-07 13:30:43.048741
postgres@test(127.0.0.1) # COPY (select * from test) TO stdout WITH (FORMAT TEXT);
1 test 2014-01-07 13:30:23.820911
2 test2 2014-01-07 13:30:29.892732
3 test3 2014-01-07 13:30:36.644758
4 test4 2014-01-07 13:30:43.048741

Oczywiście najlepszym rozwiązaniem jest wykonać komendę COPY z polecenia psql i przekierowanie wyniku do pliku:

∴ /opt/PostgreSQL/9.1/bin/psql -h127.0.0.1 -p7432 -Upostgres -c "COPY (select * from test) TO stdout WITH (FORMAT CSV, DELIMITER '|', HEADER ON, FORCE_QUOTE (c2,c3))" test > /tmp/data.csv
Password for user postgres: 

∴ cat /tmp/data.csv
c1|c2|c3
1|"test"|"2014-01-07 13:30:23.820911"
2|"test2"|"2014-01-07 13:30:29.892732"
3|"test3"|"2014-01-07 13:30:36.644758"
4|"test4"|"2014-01-07 13:30:43.048741"

Export danych z MySQL-a

Pytano się mnie niedawno w jaki sposób zapisać wynik zapytania SQL do pliku w bazach danych MySQL. Przedstawię kilka możliwości.


Poniżej wyświetliłam dane z tabeli testowa, która pojawiała się już w moich wcześniejszych postach.

[11:58:33 root@goblin ~] mysql -h 127.0.0.1 -uroot -proot -P3309 -e "select * from test_myisam" ela_db 
+----+--------------+-------------------------------+---------------------+
| c1 | c2           | c3                            | c4                  |
+----+--------------+-------------------------------+---------------------+
|  3 | essss        | 2014-02-17 06:00:46           | 2014-02-17 07:14:29 |
|  1 | essss        | to jest cos innego            | 2014-02-17 06:03:22 |
|  4 | easdfsdfssss | bxcbvx to jest cos innego     | 2014-02-17 06:03:22 |
|  5 | easdfsdfssss | bxcbvx to jest cos innego     | 2014-02-17 07:14:49 |
|  7 | easdfsdfssss | bxcbvx to jest cos innego     | 2014-02-17 06:03:22 |
|  6 | easdfsdfssss | bxcbvx to jest cos innego     | 2014-02-17 07:15:03 |
|  2 | easdfsdfssss | bxcbvx to jest cos innffffego | 2014-02-17 06:03:22 |
|  8 | easdfsdfssss | bxcbvx to jest cos innffffego | 2014-02-17 08:20:08 |
+----+--------------+-------------------------------+---------------------+

Program klienta do połączenia z serwerem - mysql (w tym przypadku v5.5) może exportować dane:

• wertykalnie (pionowo)
• html-u
• xml-u
• tabeli (domyślnie)

Chciała bym przekierować wynik tego selektu do pliku tak aby móc później obrabiać te dane przy pomocy innych programów.
Jednym z najprostszych sposobów jest wywołanie komendy mysql z linii poleceń, przekierowanie wyniku do pliku i zakończenie połączenia z serwerem. Wykorzystamy do tego celu opcję: -e, --execute.
Problemem może jednak być to, że w wynik jest w formie tabeli, a dla nas ważne są tylko dane w niej zawarte. Skorzystamy w tym przypadku z opcji --skip-column-names i --silent. Pierwszą chyba nie muszę tłumaczyć ( :-) ). Ta ostatnia powoduje, że dane w kolumnach są oddzielone tabulatorami, a każdy wiersz jest pisany w nowej linii:

[12:11:28 root@goblin ~] /usr/local/mysql5.5/bin/mysql -h 127.0.0.1 -uroot -proot -P3309 --skip-column-names -e "select * from test_myisam" --silent ela_db 
3 essss 2014-02-17 06:00:46 2014-02-17 07:14:29
1 essss to jest cos innego 2014-02-17 06:03:22
4 easdfsdfssss bxcbvx to jest cos innego 2014-02-17 06:03:22
5 easdfsdfssss bxcbvx to jest cos innego 2014-02-17 07:14:49
7 easdfsdfssss bxcbvx to jest cos innego 2014-02-17 06:03:22
6 easdfsdfssss bxcbvx to jest cos innego 2014-02-17 07:15:03
2 easdfsdfssss bxcbvx to jest cos innffffego 2014-02-17 06:03:22
8 easdfsdfssss bxcbvx to jest cos innffffego 2014-02-17 08:20:08

Gdy już jednak jesteśmy zalogowani, także możemy przekierować wynik działania zapytania SELECT do pliku. Może nam pomóc w tym 'SELECT ... INTO OUTFILE'. Poważnym utrudnieniem jest jednak to, że utworzony plik został stworzony na maszynie, gdzie działa serwer MySQL.

mysql (root@ela_db)> select * INTO OUTFILE '/tmp/result.csv' from test_myisam;
Query OK, 8 rows affected (0.07 sec)

[12:18:30 root@goblin ~] cd /tmp/
[12:18:33 root@goblin tmp] ll result.csv
-rw-rw-rw- 1 mysql mysql 469 02-24 12:17 result.csv
[12:18:45 iloop@goblin tmp] cat result.csv 
3 essss 2014-02-17 06:00:46 2014-02-17 07:14:29
1 essss to jest cos innego 2014-02-17 06:03:22
4 easdfsdfssss bxcbvx to jest cos innego 2014-02-17 06:03:22
5 easdfsdfssss bxcbvx to jest cos innego 2014-02-17 07:14:49
7 easdfsdfssss bxcbvx to jest cos innego 2014-02-17 06:03:22
6 easdfsdfssss bxcbvx to jest cos innego 2014-02-17 07:15:03
2 easdfsdfssss bxcbvx to jest cos innffffego 2014-02-17 06:03:22
8 easdfsdfssss bxcbvx to jest cos innffffego 2014-02-17 08:20:08

Jest to o tyle elastyczna opcja, że możemy zdefiniować, że wynik ma być plikiem CSV. To my definiujmy jakim znakiem oddzielać dane w kolumnach i nowie wiersze:

mysql (root@ela_db)> select * FROM test_myisam INTO OUTFILE '/tmp/result.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Query OK, 8 rows affected (0.00 sec)

[12:34:07 iloop@goblin tmp] cat result.csv 
"3","essss","2014-02-17 06:00:46","2014-02-17 07:14:29"
"1","essss","to jest cos innego","2014-02-17 06:03:22"
"4","easdfsdfssss","bxcbvx to jest cos innego","2014-02-17 06:03:22"
"5","easdfsdfssss","bxcbvx to jest cos innego","2014-02-17 07:14:49"
"7","easdfsdfssss","bxcbvx to jest cos innego","2014-02-17 06:03:22"
"6","easdfsdfssss","bxcbvx to jest cos innego","2014-02-17 07:15:03"
"2","easdfsdfssss","bxcbvx to jest cos innffffego","2014-02-17 06:03:22"
"8","easdfsdfssss","bxcbvx to jest cos innffffego","2014-02-17 08:20:08"

To somo polecenie możemy wykonać także przy pomocy polecenia mysqldump:

[06:38:49 root@goblin ~] /usr/local/mysql5.5/bin/mysqldump -h 127.0.0.1 -uroot -proot -P3309 \
> --no-create-db --no-create-info --skip-opt --tab=/tmp \
> --fields-terminated-by=,  --fields-enclosed-by='"' ela_db test_myisam

Wynik:

[06:39:13 root@goblin tmp] cat test_myisam.*
"3","essss","2014-02-17 06:00:46","2014-02-17 13:14:29"
"1","essss","to jest cos innego","2014-02-17 12:03:22"
"4","easdfsdfssss","bxcbvx to jest cos innego","2014-02-17 12:03:22"
"5","easdfsdfssss","bxcbvx to jest cos innego","2014-02-17 13:14:49"
"7","easdfsdfssss","bxcbvx to jest cos innego","2014-02-17 12:03:22"
"6","easdfsdfssss","bxcbvx to jest cos innego","2014-02-17 13:15:03"
"2","easdfsdfssss","bxcbvx to jest cos innffffego","2014-02-17 12:03:22"
"8","easdfsdfssss","bxcbvx to jest cos innffffego","2014-02-17 14:20:08"

To mysqldump z tymi opcjami wykonuje SELECT z INTO OUTFILE dlatego plik wynikowy można znaleźć na podanej ścieżce na maszynie działającego serwera MySQL.