Mos forgot to populate the url_id column in your user table. I would use
his same process but re-arrange it like this:
1) create table BIG_TABLE....
2) load data infile....
3) create table URL_TABLE (
url_id bigint not null auto_increment,
url varchar(25) not null primary key,
category ....
)
4) INSERT IGNORE URL_TABLE (url, category)
SELECT url,category
FROM BIG_TABLE
4) create table USER_TABLE (
user_id varchar?(...) primary key,
date_time datetime,
url_id bigint,
size int
)
5) insert USER_TABLE
SELECT bt.userID, bt.datetime, u.url_id, bt.size
FROM BIG_TABLE bt
INNER JOIN URL_TABLE u
ON u.url = bt.url
doing it this way lets the SQL engine handle the conversion of URLs to
their IDs in the USER_TABLE...see? No scripting required at all!
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
mos
<mos99@stripped To: mysql@stripped
m> cc:
Fax to:
06/09/2004 05:00 Subject: Re: load data into 2 tables and
set id
PM
At 02:34 PM 6/9/2004, you wrote:
>Hi,
>
>I need some help please! I have 60GB of proxy logs to parse and load into
>a mysql database. I've written a parsing script but I'm stuck now on how
>to load the data in.
>
>I have a database called PROXY_LOG with 2 tables:
>
>USER_TABLE
>user_id date_time url_id size
>
>and
>
>URL_TABLE
>url_id url category
>
>
>The data values I have for each record are:
>
>user_id date_time size url category
>
>for example:
>
>u752359 2004-04-02 12:33:04 3403 http://www.mysql.com/index.html
>business
>
>The problem is I'm not sure how to load the data into the 2 tables and set
>the url_id. Could anyone point me in the right direction please?
>
>Many thanks,
>
>js.
js,
Is this a one time operation? If so, you could create a temporary
table with all the fields:
>BIG_TABLE
>user_id date_time url_id size url category
and use Load Data InFile to load the data into this one table.
Then create the two smaller tables using:
create table USER_TABLE select user_id, date_time, url_id, size from
BIG_TABLE;
create table URL_TABLE select url_id, url, category from BIG_TABLE;
drop table BIG_TABLE;
So there you have it in 3 lines of code. :-)
Mike
P.S. If you could break the text file into two files to begin with, then
two Load Data InFile statements would handle things nicely.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=1
