Sample data for import and xml export - mysql and postgres (part 3 of 3)

Following up on previous postings I begin by giving the full xml output which should be pastable to your computer and then imported to any database with an xml import facility.

amd_bang_per_watt__20090720.xml
amd_bang_per_watt__20090720.xml-mit-create

or just a couple of rows as appeared in the previous posting:
amd_bang_per_watt__20090720.xml-select

For two of the main open source databases I use, some graphical tools are listed here:

• mysql-admin for mysql with source available direct from mysql.com
• pgadmin3 for postgres with source availability mentioned in news.

mysql-admin documentation available in English, Greek, Italian, Portuguese, Russian, Spanish, Ukrainian.

If you like a screenshotted introduction to the gui tools then here is a quick idea of the look and feel:

The version 6 release of mysql (maybe 2010) will include
load xml infile

will be a step forward. Version 6 will follow version 5.4 of mysql which is in testing as I write.

Commercial releases of mysql might show slightly different versions but in keeping with the title of this blog I tend to focus on gpl or free and open source flavours of software.

If you have the data in a variety of formats (usually the case if it is your data) then csv format and the formats mysqlimport has supported for the last 10 years should do fine to get the job done.

All major databases will likely soon offer xml import (if not already) which should help data interchange between retailers and suppliers providing xml standards are strictly adhered to. Newer xml proposed standards have public pages for further research at w3.org

If you are itching for bringing in xml data directly to mysql today then you might want to browse the following:

• xml inside a mysql column and later retrieval
• xml importing using python (2003ish)
  
• xml importing using perl libraries (2003ish)
• xml importing using a stored procedure xmldump_load
  

And now back to the data which I give in mysql own .sql format to aid anyone wanting to just load it directly rather than getting hung up on xml.

• amd_power_dissipation__20090721.sql
• amd_power_dissipation__20090721.sql-compact
  

So at whatever point you have joined this posting thread you can quickly pick up from here if you are interested in what comes next which is all Postgres.

Before leaving mysql entirely we should export the data using the --compatible flag, so as the sample data and postgres postings can discuss what (if anything) must be done to the sql output from --compatible to make it totally postgres.

Here is what mysql produces from --compatible=postgresql:

• amd_bang_per_watt.mysqldump4postgresSansCreate
  
• [ data only (no create statement) ]
• amd_bang_per_watt.mysqldump4postgresSansOpts
  
• [ data+create (less compact form) ]
  
• amd_bang_per_watt.mysqldump4postgres
  
• [ data+create (compact form) ]

Mark Slade had this to say regarding what to expect in his article about converting databases.

Despite how it sounds, this won't generate PostgreSQL compatible code, but it will get you halfway there.

Halfway sounds pretty good if you do not want to go down the 'csv out csv in' route. I explore both ways in the postgres postings.

Sample data for import and xml export - mysql and postgres (part 1 of 3)

Export to xml is a feature of databases that I have never used until now.

I thought that a short guide might be useful to illustrate the results.

As a further task I will later look at --compatible option of mysqldump to see how postgres likes the results (a follow up post perhaps so see parts 2 and 3)

( If you came here just looking to getting data out of oocalc then into mysql, please shuffle along to paragraphs 11 to 13 where this is demonstrated )

There are projects out there to take mysql data into postgres, and vice versa and it will be a useful skill to have.

After reading up about freely available sample data for names and addresses and seeing if I might use any openoffice sample data, I decided on a slightly different tack.

It seems that most 'free' sample data has some sort of copyright restriction but there is plenty that is licensed under creative commons attribution or similar.

Reading up about AMD phenom processors earlier today I viewed the wikipedia table regarding socket am3 power usage and thought that this table of data when pasted into a spreadsheet is as good as any.

(For any reader who cannot get to spreadsheets.google.com or experiences an unexpected 'login to google' prompt, I have mirrored the spreadsheet in open document .ods format here)

It is right at this point to highlight again that any and all power usage data referenced in this blog posting directly/as xml/as a spreadsheet is not my data and I correctly credit Wikipedia explicitly as the source.

In the spreadsheet viewable at google docs I have added two comments to this effect.

Now to do something useful with it - firstly export it to .csv format by following instructions for openoffice calc csv export.

Now looking at the options for mysqlimport:
--fields-terminated-by Is how you will tell mysql that data is comma separated
--fields-optionally-enclosed-by Will be how I tell mysql that double quotes enclose filled fields.

Now openoffice calc (oocalc) export to/save as CSV does not wrap empty fields in double quotes, and so, a six field row with just first and last fields populated might just look like this "rhubarb",,,,,"custard"

Mysql is not going to like the series of commas without field delimiters and so in particular ,,,, will be a problem unless the word optionally in --fields-optionally-enclosed-by will come to my rescue.

This still leaves the question of escaping single quotes (I think mysql wants them all escaped) and the sed command might come to my aid there.

Part 2 will deal with getting the data into mysql using mysqlimport.
I will finish this posting now with a first attempt at a mysql table create that is suitable for the data being loaded.

DROP TABLE IF EXISTS amd_bang_per_watt;
SET character_set_client = utf8;
CREATE TABLE amd_bang_per_watt (model_family  VARCHAR(10) NOT NULL,
model   VARCHAR(30) NOT NULL default 'unknown',
clock_speed  VARCHAR(20) NOT NULL,
l2cache   VARCHAR(10),
l3cache   VARCHAR(10),
ht_bus_ghz  VARCHAR(10),
voltage   VARCHAR(10),
socket   VARCHAR(10),
tdp_watts  VARCHAR(20),
process_comments VARCHAR(20),
speed_power_ratio DECIMAL(5,2) COMMENT 'bang per watt'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 MIN_ROWS=50 CHECKSUM=1
COMMENT='Source: Wikipedia 200907http://en.wikipedia.org/wiki/List_of_CPU_power_dissipation#AMD_Phenom_.28Phenom_64.29';

So create a new mysql database and run that table create should enable you to verify the table using describe as follows:

root@d183z2g:~# mysql amd_power_dissipation -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 61
Server version: 5.0.75-0ubuntu10.2 (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show tables;
+---------------------------------+
| Tables_in_amd_power_dissipation |
+---------------------------------+
| amd_bang_per_watt               |
+---------------------------------+
1 row in set (0.00 sec)
mysql> describe amd_bang_per_watt;
+-------------------+--------------+------+-----+---------+-------+
| Field             | Type         | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| model_family      | varchar(10)  | NO   |     | NULL    |       |
| model             | varchar(30)  | NO   |     | unknown |       |
| clock_speed       | varchar(20)  | NO   |     | NULL    |       |
| l2cache           | varchar(10)  | YES  |     | NULL    |       |
| l3cache           | varchar(10)  | YES  |     | NULL    |       |
| ht_bus_ghz        | varchar(10)  | YES  |     | NULL    |       |
| voltage           | varchar(10)  | YES  |     | NULL    |       |
| socket            | varchar(10)  | YES  |     | NULL    |       |
| tdp_watts         | varchar(20)  | YES  |     | NULL    |       |
| process_comments  | varchar(20)  | YES  |     | NULL    |       |
| speed_power_ratio | decimal(5,2) | YES  |     | NULL    |       |
+-------------------+--------------+------+-----+---------+-------+
11 rows in set (0.00 sec)
mysql>

I have deliberately defined the final column as decimal(5,2) as I am thinking ahead to the import to postgres (much later) and want to see how postgres handles that column.

In part 2 of this posting I will attempt to bring in the power data from the .csv file and refine the mysql table create if necessary.