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
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
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 bt
INNER JOIN url_servers us
to:
06/23/2004 04:13 Subject: Re: load data into 2 tables
and set id
AM
]
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 * from url_visit where url_scheme_ID=3 limit 10
]
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 * from url_visit where url_scheme_ID=3 limit 10
PROTECTED]
Fax to:
06/23/2004 09:57 Subject: Re: load data into 2 tables
and set id
: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_scheme_ID is part of a unique constraint/key ?
mysql desc url_schemes
PROTECTED]
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
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
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
/2004 07:55 Subject: Re: load data into 2 tables
and set id
AM
]
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, up.ID, if(bt.path_split 0
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,
cc: [EMAIL PROTECTED]
Fax to:
06/13/2004 12:29 Subject: Re: load data into 2 tables
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;
to:
06/18/2004 09:40 Subject: Re: load data into 2 tables
and set id
AM
]
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.
No problem!!
Please post the structures of your big_table and your url_table
(whatever
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
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
to:
06/09/2004 05:00 Subject: Re: load data into 2 tables
and set id
PM
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 size
and
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_id date_time url_id
27 matches
Mail list logo
