basic database table creation and load from csv using mysql and postgres

Basic database table creation with MySql and PostGreSQL.
The starting point to most data applications is getting the data feeds and populating the tables.
here’s an example of the process I’m loading a stock_history table from yahoo finance api source.

Make a table :

mysql:

create table stock_history
( id bigint not null primary key AUTO_INCREMENT,
symbol varchar(10) not null,
data_date date not null,
open_at decimal(14,6) null,
close_at decimal(14,6) null,
high decimal(14,6) null,
low decimal(14,6) null,
volume decimal(24,0) null
) ;
create index sidx on stock_history (symbol);

postgres:

create table stock_history
( id SERIAL primary key,
symbol varchar not null,
data_date date not null,
open_at money null,
close_at money null,
high money null,
low money null,
volume numeric null
) ;
create index sidx on stock_history (symbol);

 

Load a table from CSV source
mysql:

LOAD DATA INFILE “/tmp/data_source.csv”
INTO TABLE stock_tickers
COLUMNS TERMINATED BY ‘,’
OPTIONALLY ENCLOSED BY ‘”‘
ESCAPED BY ‘”‘
LINES TERMINATED BY ‘n’
IGNORE 1 LINES;


postgres:

copy stock_history (symbol, data_date, open_at, close_at, high, low, volume)
from '/tmp/data_source.csv' WITH HEADER DELIMITER ',' csv;

I’m using stock_quote gem to source my stock csv feed.

Advertisements
This entry was posted in data wrangling, relational databases and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s