| title: “An analysis of the Danish broard harvests of 2013” |
| author: “Per Møldrup-Dalum” |
| output: rmarkdown::tufte_handout |
| date: “17. dec. 2014” |
An analysis of the Danish broard harvests of 2013
We were given the task of extracting information from the Danish net archive to be used as grounds for a scientifically based decision on the number of yearly broard havests of the Danish Internet. The work and results from that task is presented in these notes.
TODO: a lot of preamble and introduction
Every broard harvest performed in Netarkivet is described on the Tværsnithøstningsstatistik page. On that page the performed harvests are defined with an unique identification string, its name, and some metadata for each harvest regarding size etc.
In this analysis we are interested in the year 2013, more specifically the harvests catagorized as /step-2/ harvests in that year. A /step-2/ harvest is defined as harvests that use a global upper limit of 10GB. During 2013, four such harvest were completed with the unique names: 2013-1-10GB, 2013-2-10GB, 2013-3-10GB, and 2013-4-10GB.
Data about these harvest is persisted in the production job database, a database which we do not have access to. Therefore we rolled a dump of the production database into our development enviromnent.
In the database thereby created we can get the ids of all the jobs performed in those four harvests. This is most easily done using a few simple SQL expressions.
select harvest_id,name
from harvestdefinitions
where name like '%2013-%-10GB%'
order by harvest_id;"To execute a SQL expression on the job database, we can use the following expression
read.csv(text=system(paste("echo \"",sqlexp,"\" | ssh test@kb-test-adm-001.kb.dk psql -F , --no-align -U test -d prod_dump_20141111_harvestdb | sed '$d'"), intern=T),header = T)## harvest_id name
## 1 181 2013-1-10GB
## 2 186 2013-2-10GB
## 3 191 2013-3-10GB
## 4 199 2013-4-10GBThe sed '$d' hack is just to remove the last line of the psql output containing the " (4 rows) " message.
As we’re going to use the above comman a few times a small function can be written
execSQL <- function(sqlString) {
return(
read.csv(
text=system(
paste("echo \"",
sqlString,
"\"|ssh test@kb-test-adm-001.kb.dk psql -F , --no-align -U test -d prod_dump_20141111_harvestdb | sed '$d'"),
intern=T),
header=T))
}All this function requires is key based ssh access to the server running the job database. A R data frame can be pretty printed using knitr::kable() and we get commands like
h_df <- execSQL("select harvest_id,name from harvestdefinitions where name like '2013-%-10GB' order by harvest_id;")
knitr::kable(h_df)| harvest_id | name |
|---|---|
| 181 | 2013-1-10GB |
| 186 | 2013-2-10GB |
| 191 | 2013-3-10GB |
| 199 | 2013-4-10GB |
To get the complete list of job ids from these four harvests we need this SQL expression (that really limits the output to 10 rows)
execSQL("select job_id,harvest_id,status from jobs where harvest_id = 181 or harvest_id = 186 or harvest_id = 191 or harvest_id = 199 limit 10;")## job_id harvest_id status
## 1 174837 181 3
## 2 174891 181 3
## 3 174842 181 3
## 4 174829 181 3
## 5 174843 181 3
## 6 175180 181 3
## 7 174852 181 3
## 8 174851 181 3
## 9 174849 181 3
## 10 174848 181 3This looks fine so we can modify it to get the full set of rows
jobIds <- execSQL("select job_id,harvest_id,status
from jobs
where harvest_id = 181
or harvest_id = 186
or harvest_id = 191
or harvest_id = 199;")
knitr::kable(head(jobIds))| job_id | harvest_id | status |
|---|---|---|
| 174959 | 181 | 3 |
| 174958 | 181 | 3 |
| 175246 | 181 | 3 |
| 189597 | 191 | 3 |
| 189598 | 191 | 3 |
| 181414 | 186 | 3 |
nrow(jobIds)## [1] 2723The four broard harvests in 2013 consisted of 2723 jobs. The above SQL expression also returns the status of each job. This status enumeration has the following meaning
| number | meaning |
|---|---|
| 0 | NEW |
| 1 | SUBMITTED |
| 2 | STARTED |
| 3 | DONE |
| 4 | FAILED |
| 5 | RESUBMITTED |
How are these job status values distributed for the jobs in 2013? This can be explored using the following SQL
dist_status <- execSQL("select harvest_id,status,count(job_id)
from jobs
where harvest_id = 181
or harvest_id = 186
or harvest_id = 191
or harvest_id = 199
group by harvest_id, status
order by harvest_id,status;")
knitr::kable(dist_status)| harvest_id | status | count |
|---|---|---|
| 181 | 3 | 596 |
| 181 | 4 | 21 |
| 186 | 3 | 473 |
| 186 | 4 | 13 |
| 191 | 3 | 775 |
| 191 | 4 | 5 |
| 191 | 5 | 101 |
| 199 | 3 | 692 |
| 199 | 4 | 16 |
| 199 | 5 | 31 |
To plot this table we need to cast the harvest_id and status variables as strings. We also add a new column that contains string representation of the status values for better visuals
status.names <- c(
"new",
"submitted",
"started",
"done",
"failed",
"resubmitted")
dist_status$named.status <- status.names[dist_status$status+1]
dist_status$harvest_id <- as.character(dist_status$harvest_id)
dist_status$status <- as.character(dist_status$status)
knitr::kable(dist_status)| harvest_id | status | count | named.status |
|---|---|---|---|
| 181 | 3 | 596 | done |
| 181 | 4 | 21 | failed |
| 186 | 3 | 473 | done |
| 186 | 4 | 13 | failed |
| 191 | 3 | 775 | done |
| 191 | 4 | 5 | failed |
| 191 | 5 | 101 | resubmitted |
| 199 | 3 | 692 | done |
| 199 | 4 | 16 | failed |
| 199 | 5 | 31 | resubmitted |
The dist_status data can now be plottet as a stacked bar chart
ggplot(dist_status, aes(harvest_id, count, fill=named.status)) + geom_bar(stat="identity") + xlab("Harvest") + ylab("Number of jobs")
TODO: Need lots of explanations or conjectures on the data
Filtering of the jobs based on status
We could now filter the jobs and look deeper into the DONE jobs.
done.jobs <- execSQL("
select job_id,harvest_id
from jobs
where (
harvest_id = 181
or harvest_id = 186
or harvest_id = 191
or harvest_id = 199)
and status = 3;")
nrow(done.jobs)## [1] 2536but instead we’ll look at the 2013 broard harvest from another perspective.
Looking at 2013 from the domain side
First we’ll create a list of all domains that are recorded from succesfull jobs. This can be done by this SQL expression
select domains.name,
historyinfo.harvest_id,
historyinfo.OBJECTCOUNT,
historyinfo.BYTECOUNT,
historyinfo.stopreason
from historyinfo, configurations, domains
where
job_id IN (
select job_id
from jobs
where (harvest_id=181
or harvest_id=186
or harvest_id=191
or harvest_id=199)
and status=3
)
and historyinfo.config_id=configurations.config_id
and configurations.domain_id = domains.domain_id;As we know this data set will contain more than 500,000 entries, we’ll dump the data into a local file for easy access later. If the above SQL expression is stored in file domains.sql then the following Bash script will dump a file with the desired data
cat domains.sql | ssh test@kb-test-adm-001.kb.dk psql -F , --no-align -U test -d prod_dump_20141111_harvestdb | sed '$d'>domains.csv#wc -l domains.csvLet’s read this data into R
domains <- tbl_df(read.csv("domains.csv", header = T))What did we just read?
nrow(domains)## [1] 623568head(domains)## Source: local data frame [6 x 5]
##
## name harvest_id objectcount bytecount stopreason
## 1 kreativbyg.dk 181 840 240932590 0
## 2 lindegaardsskolen.dk 181 2775 111238617 0
## 3 kirkeskibet.dk 181 678 14242570 0
## 4 komarna.com 181 958 17661187 0
## 5 learnchinese.dk 181 47043 944998860 0
## 6 kyllings.dk 181 771 15614013 0What can now be inferred from this data? What values can be extracted and conjectures created? Let’s just say that that question in it self can occupy a fair amount of time.
First we’ll look at stopreason. stopreason is an enumeration with the following string representations
| number | meaning |
|---|---|
| 0 | DOWNLOAD_COMPLETE |
| 1 | OBJECT_LIMIT |
| 2 | SIZE_LIMIT |
| 3 | CONFIG_SIZE_LIMIT |
| 4 | DOWNLOAD_UNFINISHED |
| 5 | CONFIG_OBJECT_LIMIT |
| 6 | TIME_LIMIT |
What values of stopreason do we observe in our data set?
First create a nice string representation of the stopreason based on the above table
stopreason.names <- c("DONWLOAD_COMPLETE",
"OBJECT_LIMIT",
"SIZE_LIMIT",
"CONFIG_SIZE_LIMIT",
"DONWLOAD_UNFINISHED",
"CONFIG_OBJECT_LIMIT",
"TIME_LIMIT")
domains$named.stopreason <- stopreason.names[domains$stopreason+1]and then knead the data
domains.stopreasons <- domains %>%
group_by(named.stopreason) %>%
summarise(counts=n()) %>%
arrange(desc(counts))
knitr::kable(domains.stopreasons)| named.stopreason | counts |
|---|---|
| DONWLOAD_COMPLETE | 559481 |
| CONFIG_SIZE_LIMIT | 35026 |
| DONWLOAD_UNFINISHED | 29048 |
| SIZE_LIMIT | 13 |
How does the data look if we spread the numbers out over the four broard harvests?
domains.stopreasons.harvest <- domains %>%
group_by(harvest_id,named.stopreason) %>%
summarise(counts=n()) %>%
arrange(desc(counts))
knitr::kable(
dcast(domains.stopreasons.harvest, harvest_id ~ named.stopreason))## Using counts as value column: use value.var to override.| harvest_id | CONFIG_SIZE_LIMIT | DONWLOAD_COMPLETE | DONWLOAD_UNFINISHED | SIZE_LIMIT |
|---|---|---|---|---|
| 181 | 8480 | 143591 | 420 | 1 |
| 186 | 8556 | 142841 | 87 | 4 |
| 191 | 9956 | 142747 | 10721 | 3 |
| 199 | 8034 | 130302 | 17820 | 5 |
or in a more visual way
Next let’s look at how the individual domains survives over the year. In other words how many domains apprears in the second broard harvest and has disappeared before the last borard harvest? Unfortunately this question is not easily answered as there are many reasons as to why a domain might not exist in a subsequent harvest. Especially if we only look at DOWNLOAD_COMPLETED domains. Then it could have disappeared from the internet, but it could just as easily have grown to a size beyond the CONFIG_SIZE_LIMIT or SIZE_LIMIT ending with a stopreason other than DONWLOAD_COMPLETE.
First let’s look at the survival rate of domains as we move through the year.
h1 <- domains %>%
filter(stopreason == 0, harvest_id == 181) %>%
select(name)
h2 <- domains %>%
filter(stopreason == 0, harvest_id == 186) %>%
select(name)
h3 <- domains %>%
filter(stopreason == 0, harvest_id == 191) %>%
select(name)
h4 <- domains %>%
filter(stopreason == 0, harvest_id == 199) %>%
select(name)
names <- c("1-2", "1-2", "2-3", "2-3", "3-4", "3-4")
growth <- c("increase", "decrease","increase", "decrease","increase", "decrease")
diffs <- c(nrow(setdiff(h2,h1)),-nrow(setdiff(h1,h2)), nrow(setdiff(h3,h2)),-nrow(setdiff(h2,h3)), nrow(setdiff(h4,h3)), -nrow(setdiff(h3,h4)) )
survival.rate <- data.frame(names,growth,diffs)ggplot(data=survival.rate, aes(x=names, y=diffs, fill=growth)) + geom_bar(stat="identity", position=position_dodge()) + xlab("Intervals") + ylab("Gained or lost domains")
Next question could be to examine where the lost domains went. So, to do that, we check whether the lost but completed domains in interval 1-2 still exists in harvest 2 albeit with a different stopreason.
lost <- setdiff(h2,h1)
h2.notcompleted <- domains %>%
filter(stopreason != 0, harvest_id == 186) %>%
select(name)
nrow(setdiff(lost,h2.notcompleted)) - nrow(lost)## [1] 0If we take all domains completed in the first harvest but not completed in the second (that’s 20437 domains) and compares that set of domains with non-completed domains in the second harvest, we get a set of 0 domains. I.e. every domain missing from the second harvest is in reality gone. Or?
If we compare that to the distribution of stopreasons in the second harvest
| named.stopreason | counts |
|---|---|
| DONWLOAD_COMPLETE | 142841 |
| CONFIG_SIZE_LIMIT | 8556 |
| DONWLOAD_UNFINISHED | 87 |
| SIZE_LIMIT | 4 |
less than 9,000 domains reached a limit while more than 20,000 is missing.
Another way to look at this would be the following: How many domains are in number 2 but not in 1, 3, nor 4? This set of domains could be appearing and disapperaing between two yearly harvests and thereby being under our radar if we only harvested twice a year.
To make an estimate on that number we could do
only.two <- setdiff(setdiff(setdiff(h2,h1), h3), h4)So why do 3313 domains only appear in the second harvest of 2013? If we take number 10 in that list of missing domains and look for it in the complete list of harvested domains, we see this:
only.two[10,]## Source: local data frame [1 x 1]
##
## name
## 1 beerguide.dkfilter(domains,name=="beerguide.dk")## Source: local data frame [1 x 6]
##
## name harvest_id objectcount bytecount stopreason
## 1 beerguide.dk 186 1245 39228627 0
## Variables not shown: named.stopreason (chr)Today, 19 December, this site still exists. So, again, why did we only see it once in the middle of 2013?
Creation of a corpus
If we want to shift our focus from the domains to the URLs, we need a well defined set of domains. We’ll define this URL corpus as the URLs from every completely domain harvested in 2013.
every.harvest <- intersect(intersect(intersect(h1,h2),h3),h4)
every.harvest## Source: local data frame [79,072 x 1]
##
## name
## 1 fiskehuset.com
## 2 foleboks.dk
## 3 flsk.dk
## 4 gedved-if.dk
## 5 gojf.dk
## 6 fmauto.dk
## 7 forbina.dk
## 8 friis-gaardbutik.dk
## 9 galleri-nord.dk
## 10 fritzes-fotoalbum.dk
## .. ...Do we have duplicated domains in the 4 harvests?
uniq.p <- c(
nrow(unique(h1)) == nrow(h1),
nrow(unique(h2)) == nrow(h2),
nrow(unique(h3)) == nrow(h3),
nrow(unique(h4)) == nrow(h4))
uniq.p## [1] TRUE TRUE TRUE TRUEEvery domain only appears once in each broard harvest. Therefore another view would be to count the number of appearences of each domain in the large data set.
domains %>%
filter(stopreason == 0) %>%
group_by(name) %>%
summarise(objectcountSum = sum(objectcount),bytecountSum = sum(bytecount),cnt = n()) %>%
filter(cnt==4) %>%
arrange(desc(bytecountSum))## Source: local data frame [79,072 x 4]
##
## name objectcountSum bytecountSum cnt
## 1 gentofte.dk 56995 24274844270 4
## 2 baptistkirke.dk 5121 22237396816 4
## 3 tekno.dk 52118 21066645516 4
## 4 morsoe.dk 54919 19180330721 4
## 5 safts.dk 260 18674915984 4
## 6 koncern.dk 9760 17573199044 4
## 7 adl.dk 1513840 15359441176 4
## 8 renaessancesprog.dk 76657 14127425644 4
## 9 rapspot.dk 212190 13858152580 4
## 10 ddfr.dk 347268 13025693228 4
## .. ... ... ... ...A look at the bytecount and objectcount
How is the distribution of bytecount
domains.completed <- domains %>%
filter(stopreason == 0)
hist(domains.completed$bytecount, breaks=100)
If we focus on domains from which less than 1kB were havested
domains.completed <- domains %>%
filter(stopreason == 0, bytecount<1000)
hist(domains.completed$bytecount, breaks=100)
domains.completed <- domains %>%
filter(stopreason == 0, bytecount > 40 , bytecount<80)
hist(domains.completed$bytecount, breaks=100)
What if we just look at the tail?
domains.completed <- domains %>%
filter(stopreason == 0, bytecount > 200 , bytecount<10000)
hist(domains.completed$bytecount, breaks=100)
What if we split that data up into the 4 individual harvests?
## Loading required package: grid## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$inv: partial match of 'inv' to 'inverse'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$inv: partial match of 'inv' to 'inverse'## Warning in scale$trans$inv: partial match of 'inv' to 'inverse'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$inv: partial match of 'inv' to 'inverse'## Warning in plot$layer: partial match of 'layer' to 'layers'## Warning in plot$coord: partial match of 'coord' to 'coordinates'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$inv: partial match of 'inv' to 'inverse'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$inv: partial match of 'inv' to 'inverse'## Warning in scale$trans$inv: partial match of 'inv' to 'inverse'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$inv: partial match of 'inv' to 'inverse'## Warning in plot$layer: partial match of 'layer' to 'layers'## Warning in plot$coord: partial match of 'coord' to 'coordinates'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$inv: partial match of 'inv' to 'inverse'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$inv: partial match of 'inv' to 'inverse'## Warning in scale$trans$inv: partial match of 'inv' to 'inverse'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$inv: partial match of 'inv' to 'inverse'## Warning in plot$layer: partial match of 'layer' to 'layers'## Warning in plot$coord: partial match of 'coord' to 'coordinates'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$inv: partial match of 'inv' to 'inverse'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$inv: partial match of 'inv' to 'inverse'## Warning in scale$trans$inv: partial match of 'inv' to 'inverse'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$trans: partial match of 'trans' to 'transform'## Warning in scale$trans$inv: partial match of 'inv' to 'inverse'## Warning in plot$layer: partial match of 'layer' to 'layers'## Warning in plot$coord: partial match of 'coord' to 'coordinates'
Again we see some interesting artefacts that could be investigated further.
What’s next?
One of the next steps would be to look at the individul URLs comprising the 4 harvests but that will have to waint for another time.