-
Notifications
You must be signed in to change notification settings - Fork 23
write_mysql
Biopiece records from the stream can be written to a MySQL database using write_mysql.
Biopiece record keys are used as MySQL table fields and the Biopiece values as the data.
write_mysql automatically guesses the data types and generates the table create statement.
The type guessing is based on the intial 1000 records, but can be changed using the -g switch.
Alternatively, it is possible to disable the data type guessing, and specify a table create
statement using the -s switch.
It is possible to replace existing databases ad tables, also, tables can be appended.
write_mysql uses bulk loading and MySQL must be compiled with --enable-local-infile.
See how to setup default MySQL username and password here: http://code.google.com/p/biopieces/wiki/HowTo#Howto_setup_default_MySQL_username_and_password
... | write_mysql [options] <-d database> <-t table>
[-? | --help] # Print full usage description.
[-u <string> | --user=<string>] # MySQL user - Default=<from ~/.biopiecesrc>
[-p <string> | --password=<string>] # MySQL user password - Default=<from ~/.biopiecesrc>
[-d <string> | --database=<string>] # MySQL database.
[-t <string> | --table=<string>] # MySQL table to create.
[-i <list> | --index=<list>] # Comma seperated list of fields to index.
[-g <uint> | --guess_type=<uint>] # Number of records for guessing types - Default=1000
[-s <string> | --sql=<string>] # Use explicit table create statement instead of guess.
[-D | --database_replace] # Replace database if exists.
[-T | --table_replace] # Replace table if exists.
[-A | --table_append] # Append data to existing table.
[-x | --no_stream] # Do not emit records.
[-I <file!> | --stream_in=<file!>] # Read input from stream file - Default=STDIN
[-O <file> | --stream_out=<file>] # Write output to stream file - Default=STDOUT
[-v | --verbose] # Verbose output.
Consider the following table in the file test.tab:
1 fish
2 bird
3 ape
4 cat
5 cow
6 dog
We can read this table with read_tab and scoop it into MySQL like this:
read_tab -i test.tab -k count,animal | write_mysql -d animals_db -t animals
Now the database should appear when using list_mysql_databases:
list_mysql_databases | grab -p animals_db
DATABASE: animals_db
---
And we can list the tables using read_mysql:
read_mysql -d animals_db -q 'show tables'
Tables_in_animals_db: animals
---
Moreover, we can retrieve the content again using read_mysql:
read_mysql -d animals_db -q 'select * from animals'
count: 1
animal: fish
---
count: 2
animal: bird
---
count: 3
animal: ape
---
count: 4
animal: cat
---
count: 5
animal: cow
---
count: 6
animal: dog
---
To append to a existing table use the -A switch:
read_tab -i test.tab -k count,animal | write_mysql -d animals_db -t animals
And inspect like before:
read_mysql -d animals_db -q 'select * from animals'
count: 1
animal: fish
---
count: 2
animal: bird
---
count: 3
animal: ape
---
count: 4
animal: cat
---
count: 5
animal: cow
---
count: 6
animal: dog
---
count: 1
animal: fish
---
count: 2
animal: bird
---
count: 3
animal: ape
---
count: 4
animal: cat
---
count: 5
animal: cow
---
count: 6
animal: dog
---
To add MySQL indexes to selected columns, use the -i switch that takes a comma seperated list
of fields to index:
read_tab -i test.tab -k count,animal | write_mysql -d animals_db -t animals -T -i count,animal -x
Finally, it is possible to disable the type guessing and explicitly supply a SQL create statement for the creation of tables:
read_tab -i test.tab -k count,animal |
write_mysql -d animals_db -t animals -s "CREATE TABLE animals2 (animal VARCHAR(4), count TINYINT)"
Martin Asser Hansen - Copyright (C) - All rights reserved.
July 2009
GNU General Public License version 2
http://www.gnu.org/copyleft/gpl.html
write_mysql is part of the Biopieces framework.