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
J S said:
> 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 siz
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
> 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
J S said:
>
>
>> > 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
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
Fax to:
06/09/2004 05:00 Subject: Re: load data into 2 tables
and set id
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,
c
ROTECTED]To: [EMAIL PROTECTED]
com> cc: [EMAIL PROTECTED]
Fax to:
06/13/200
Shawn,
Thanks for helping on this. I really appreciate it.
No problem!!
Please post the structures of your "big_table" and your "url_table"
(whatever you called them) and I will help you to rewrite step 4 to count
how many times a URL appears in the "big_table".
mysql> desc internet_usage;
+--
Fax to:
06/18/2004 09:40 Subject: Re: load data into 2 tables
and
com> cc:
[EMAIL PROTECTED]
Fax to:
06/18/2004 09:40 Subject: Re: load data
into 2 tables and set id
AM
Shawn,
Thanks for helping on this. I really appreciate it.
&
welcome to a basic overview of bulk importing and normalizing as
you go
[ author's note: if you are seeing this thread for the first time and
certain items seem to be
introduced out of context, please review all previous posts in this thread.
There has been
a lot of informat
Shawn,
Thanks for your reply below. I found it extremely useful. I have followed
your instructions and got good results up to the url_visits table.
I have a perl script to parse the values out of the log. The log has
3,770,246 lines and is gzipped. I then applied your SQL statements with the
fo
Did you mean there to be duplicates in the url_visits? Do I need to use
IGNORE in the following SQL?
INSERT url_visit (url_server_ID, url_path_ID, querystring,
category)
SELECT us.ID, up.ID, if(bt.path_split >0, SUBSTRING(bt.url,path),
NULL),
bt.category
FROM bulk_table bt
INNER JOIN url_servers
I think I fixed it!
INSERT IGNORE url_visit (url_server_ID, url_path_ID, querystring,
category)
SELECT DISTINCT us.ID, up.ID, if(bt.path_split >0, SUBSTRING(bt.url,path),
NULL),
bt.category
FROM bulk_table bt
INNER JOIN url_servers us
ON us.server = bt.server
INNER JOIN url_paths up
on
Fax to:
06/22/2004 07:55 Subject: Re: load data into 2 tables
and set id
cc:
[EMAIL PROTECTED]
Fax to:
06/22/2004 07:55 Subject: Re: load data
into 2 tables and set id
AM
I think I fixed it!
INSERT IGNORE url_visit (url_server_ID, url_path_ID, querystring,
category)
SELECT DISTINCT us.ID,
:55 Subject: Re: load data
into 2 tables and set id
AM
I think I fixed it!
INSERT IGNORE url_visit (url_server_ID, url_path_ID, querystring,
category)
SELECT DISTINCT us.ID, up.ID, if(bt.path_split >0, SUBSTRING(bt.url,path),
NULL),
bt.category
FROM bulk_table b
Fax to:
06/23/2004 04:13 Subject: Re: load data into 2 tables
and set id
cc:
[EMAIL PROTECTED]
Fax to:
06/23/2004 04:13 Subject: Re: load data
into 2 tables and set id
AM
Shawn,
I uncovered a problem this morning. I wonder if you (or anyone else) can
help me out again?
mysql> select * fr
cc:
[EMAIL PROTECTED]
Fax to:
06/23/2004 04:13 Subject: Re: load data
into 2 tables and set id
AM
Shawn,
I uncovered a problem this morning. I wonder if you (or anyone else) can
help me out again?
mysql> select *
cc: [EMAIL PROTECTED]
Fax to:
06/23/2004 09:57 Subject: Re: load data into
Fax to:
06/23/2004 09:57 Subject: Re: load data
into 2 tables and set id
AM
Hi Shawn,
Here's the url_Schemes table (it's the same as the url_paths and
url_servers). This means url_sche
"J S"
> <[EMAIL PROTECTED]To:
[EMAIL PROTECTED]
> com> cc:
>[EMAIL PROTECTED]
> Fax to:
> 06/23/2004 09:57 Subject: Re: load data
&
Hi Shawn,
I wondered if you might be able to help me with an SQL query.
I want to list all the internet sites I've surfed in my database.
Here's a query that matches the url with a urlid:
SELECT concat(usc.scheme,"://",us.server,up.path)
FROM url_visit uv
INNER JOIN url_servers us
ON us.id=u
Figured it out! Took a gamble and run the below command!
SELECT iu.time, INET_NTOA(iu.ip), concat(usc.scheme,"://",us.server,up.path)
FROM url_visit uv
INNER JOIN internet_usage iu
ON iu.urlid=uv.urlid
INNER JOIN url_servers us
ON us.id=uv.url_server_ID
INNER JOIN url_paths up
27 matches
Mail list logo
