@ianmilligan1
I wrote a script to do this. Do you have a small-ish dataset that has images like this that I can test with?

Great, thanks @JWZ2018 – just pinged you in Slack about access to a relatively small dataset that could be tested on (you could try on the sample data here, but I'm worried we need a large enough dataset to find these potential hits).

@ianmilligan1
I used this script:

import io.archivesunleashed._
import io.archivesunleashed.matchbox._
import io.archivesunleashed.df._
val data = RecordLoader.loadArchives("/mnt/vol1/data_sets/cpp/cpp_warcs_accession_01/partner.archive-it.org/cgi-bin/getarcs.pl/ARCHIVEIT-227-QUARTERLY-16606*",sc)
import spark.implicits._
val domains = data.extractImageLinksDF().select(df.ExtractDomain($"src").as("Domain"), $"image_url".as("ImageUrl"));
val images = data.extractImageDetailsDF().select($"url".as("ImageUrl"), $"md5".as("MD5"));

//domains and images in one table
val total = domains.join(images, "ImageUrl")
//group same images by MD5 and only keep the md5 with at least 2 distinct domains
val links = total.groupBy("MD5").count().where(countDistinct("Domain")>=2)
//rejoin with images to get the list of image urls
val result = total.join(links, "MD5").groupBy("Domain","MD5").agg(first("ImageUrl").as("ImageUrl")).orderBy(asc("MD5")).show()

Some results shared in the slack

This is awesome (and thanks for the results, looks great).

Given the results, I realize maybe we should isolate to just a single crawl.

If we want to do the above but slate it to just the crawl date in yyyymm format: 200912, where should we put that filter in above for optimal performance?

@ianmilligan1
We can try something like this:

import io.archivesunleashed._
import io.archivesunleashed.matchbox._
import io.archivesunleashed.df._
val data = RecordLoader.loadArchives("/mnt/vol1/data_sets/cpp/cpp_warcs_accession_01/partner.archive-it.org/cgi-bin/getarcs.pl/ARCHIVEIT-227-QUARTERLY-DNYDTY-20121103160515-00000-crawling202.us.archive.org-6683.warc.gz",sc).filter(r => r.getCrawlMonth == "201211")
val domains = data.extractImageLinksDF().select(df.ExtractDomain($"src").as("Domain"), $"image_url".as("ImageUrl"));
val images = data.extractImageDetailsDF().select($"url".as("ImageUrl"), $"md5".as("MD5"));

//domains and images in one table
val total = domains.join(images, "ImageUrl")
//group same images by MD5 and only keep the md5 with at least 2 distinct domains
val links = total.groupBy("MD5").count().where(countDistinct("Domain")>=2)
//rejoin with images to get the list of image urls
val result = total.join(links, "MD5").groupBy("Domain","MD5").agg(first("ImageUrl").as("ImageUrl")).orderBy(asc("MD5")).show()

This particular dataset didn't return any results for the given month but the script completed successfully.

@JWZ2018 in above, filter is being done on RDD... the plan is move everything over to DF, so we need a new set of UDFs... I'll create a new PR on this.

@ianmilligan1 are we good on this issue, or are we waiting for something from @lintool still?

Realistically we could probably just do this by filtering the resulting csv file, so I’m happy if we close this.

👎 on filtering CSVs - not scalable...

OK, thanks @lintool. Above you noted creating some new UDFs, is that still something you could do?

@SinghGursimran here's one for you.

import io.archivesunleashed.matchbox._
import io.archivesunleashed._

val imgDetails = udf((url: String, MimeTypeTika: String, content: String) => ExtractImageDetails(url,MimeTypeTika,content.getBytes()).md5Hash)
val imgLinks = udf((url: String, content: String) => ExtractImageLinks(url, content))
val domain = udf((url: String) => ExtractDomain(url))

val total = RecordLoader.loadArchives("./ARCHIVEIT-227-QUARTERLY-XUGECV-20091218231727-00039-crawling06.us.archive.org-8091.warc.gz", sc)
						.extractValidPagesDF()
						.select(
								$"crawl_date".as("crawl_date"),
								domain($"url").as("Domain"),
								explode_outer(imgLinks(($"url"), ($"content"))).as("ImageUrl"),
								imgDetails(($"url"), ($"mime_type_tika"), ($"content")).as("MD5")
							   )
					  	.filter($"crawl_date" rlike "200912[0-9]{2}")

val links = total.groupBy("MD5").count()
				 .where(countDistinct("Domain")>=2)

val result = total.join(links, "MD5")
				  .groupBy("Domain","MD5")
				  .agg(first("ImageUrl").as("ImageUrl"))
				  .orderBy(asc("MD5"))
				  .show(10,false)

The above script performs all operations on df. There are no potential hits for the given date in the dataset I used, though the script completed successfully.

Hrm... I think I should be getting matches here, but I'm not getting any:

Crawl dates that should match: 20091027

RecordLoader.loadArchives("/home/nruest/Projects/au/sample-data/geocites/1/", sc)
            .extractValidPagesDF()
            .show()

// Exiting paste mode, now interpreting.

+----------+--------------------+--------------------+--------------------+--------------------+
|crawl_date|                 url|mime_type_web_server|      mime_type_tika|             content|
+----------+--------------------+--------------------+--------------------+--------------------+
|  20091027|http://geocities....|           text/html|           text/html|HTTP/1.1 200 OK
...|
|  20091027|http://geocities....|           text/html|           text/html|HTTP/1.1 200 OK
...|
|  20091027|http://www.geocit...|           text/html|           text/html|HTTP/1.1 200 OK
...|
|  20091027|http://www.geocit...|           text/html|           text/html|HTTP/1.1 200 OK
...|
|  20091027|http://geocities....|           text/html|           text/html|HTTP/1.1 200 OK
...|
|  20091027|http://geocities....|           text/html|           text/html|HTTP/1.1 200 OK
...|
|  20091027|http://www.talent...|           text/html|application/xhtml...|HTTP/1.1 200 OK
...|
|  20091027|http://geocities....|           text/html|           text/html|HTTP/1.1 200 OK
...|
|  20091027|http://geocities....|           text/html|           text/html|HTTP/1.1 200 OK
...|
|  20091027|http://www.geocit...|           text/html|           text/html|HTTP/1.1 200 OK
...|
|  20091027|http://www.geocit...|           text/html|           text/html|HTTP/1.1 200 OK
...|
|  20091027|http://geocities....|           text/html|           text/html|HTTP/1.1 200 OK
...|
|  20091027|http://geocities....|           text/html|           text/html|HTTP/1.1 200 OK
...|
|  20091027|http://www.geocit...|           text/html|           text/html|HTTP/1.1 200 OK
...|
|  20091027|http://geocities....|           text/html|application/xhtml...|HTTP/1.1 200 OK
...|
|  20091027|http://geocities....|           text/html|           text/html|HTTP/1.1 200 OK
...|
|  20091027|http://geocities....|           text/html|           text/html|HTTP/1.1 200 OK
...|
|  20091027|http://geocities....|           text/html|           text/html|HTTP/1.1 200 OK
...|
|  20091027|http://www.infoca...|           text/html|           text/html|HTTP/1.1 200 OK
...|
|  20091027|http://geocities....|           text/html|           text/html|HTTP/1.1 200 OK
...|
+----------+--------------------+--------------------+--------------------+--------------------+
only showing top 20 rows

Filter for matching this pattern: 200910

scala> :paste
// Entering paste mode (ctrl-D to finish)

import io.archivesunleashed.matchbox._
import io.archivesunleashed._

val imgDetails = udf((url: String, MimeTypeTika: String, content: String) => ExtractImageDetails(url,MimeTypeTika,content.getBytes()).md5Hash)
val imgLinks = udf((url: String, content: String) => ExtractImageLinks(url, content))
val domain = udf((url: String) => ExtractDomain(url))

val total = RecordLoader
              .loadArchives("/home/nruest/Projects/au/sample-data/geocites/1/", sc)
              .extractValidPagesDF()
              .select(
                $"crawl_date".as("crawl_date"),
                domain($"url").as("Domain"),
                explode_outer(imgLinks(($"url"),
                ($"content"))).as("ImageUrl"),
                imgDetails(($"url"), 
                ($"mime_type_tika"), 
                ($"content")).as("MD5")
              )
              .filter($"crawl_date" rlike "200910[0-9]{2}")

val links = total
              .groupBy("MD5")
              .count()
              .where(countDistinct("Domain")>=2)

val result = total
               .join(links, "MD5")
               .groupBy("Domain","MD5")
               .agg(first("ImageUrl")
               .as("ImageUrl"))
               .orderBy(asc("MD5"))
               .show(10,false)

// Exiting paste mode, now interpreting.

+------+---+--------+                                                           
|Domain|MD5|ImageUrl|
+------+---+--------+
+------+---+--------+

import io.archivesunleashed.matchbox._
import io.archivesunleashed._
imgDetails: org.apache.spark.sql.expressions.UserDefinedFunction = UserDefinedFunction(<function3>,StringType,Some(List(StringType, StringType, StringType)))
imgLinks: org.apache.spark.sql.expressions.UserDefinedFunction = UserDefinedFunction(<function2>,ArrayType(StringType,true),Some(List(StringType, StringType)))
domain: org.apache.spark.sql.expressions.UserDefinedFunction = UserDefinedFunction(<function1>,StringType,Some(List(StringType)))
total: org.apache.spark.sql.Dataset[org.apache.spark.sql.Row] = [crawl_date: string, Domain: string ... 2 more fields]
links: org.apache.spark.sql.Dataset[org.apache.spark.sql.Row] = [MD5: string, count: bigint]
result: Unit = ()

I think I should be getting results there.

Are there 2 or more distinct domains with same md5 hash on the given date?

Oh, that's right. 🤦‍♂

Now we have to search for a datset that solves this. @ianmilligan1 I can run this on a larger portion of GeoCities on rho if you want, unless you have something better in mind.

Nope I think running on GeoCities on rho makes sense to me!

Ok, I'm running it on the entire 4T of GeoCities, and writing to csv. I'll report back in a few days when it finishes.

@ianmilligan1 @lintool if this is completes successfully, where do you two envision this landing in aut-docs-new?

Ok, I think we're good. This look right @ianmilligan1 @SinghGursimran?

@ianmilligan1 @lintool where do you two envision this landing in aut-docs-new, so we can fully resolve this issue?

As one of the questions under image analysis:
How do I find images shared between domains?

Ok, I think we're good. This look right @ianmilligan1 @SinghGursimran?

@ianmilligan1 @lintool where do you two envision this landing in aut-docs-new, so we can fully resolve this issue?

I guess result looks good. I will just check why Image Url is empty in few cases.

archivesunleashed/aut-docs#27