Common Crawl Index Table

Build and process the Common Crawl index table – an index to WARC files in a columnar data format (Apache Parquet).

The index table is built from the Common Crawl URL index files by Apache Spark. It can be queried by SparkSQL, Amazon Athena (built on Presto or Trino), Apache Hive and many other big data frameworks and applications.

This projects provides a comprehensive set of example queries (SQL) and also Java code to fetch and process the WARC records matched by a SQL query.

Build Java tools

Java 11 or upwards are required.

mvn package

Javadocs

The Javadocs are created by mvn javadoc:javadoc. Then open the file target/reports/apidocs/index.html in a browser.

Source Code Formatting

Run mvn spotless:check and mvn spotless:apply, see the Spotless Maven guide. Java formatting rules are defined in eclipse-formatter.xml.

Spark installation

Spark needs to be installed in order to build the table and also (alternatively) for processing. Please refer to the Spark documentation how to install Spark and set up a Spark cluster.

Building and running using Docker

A Dockerfile is provided to compile the project and run the Spark job in a Docker container.

1. build the Docker image: sh docker build . -t cc-index-table
2. run the table converter tool, here showing the command-line help (--help): sh docker run --rm -ti cc-index-table --help More details to run the converter are given below.

Note that the Dockerfile defines the conversion tool as entry point. Overriding the entrypoint would allow to inspect the container using an interactive shell:

$> docker run --rm --entrypoint=/bin/bash -ti cc-index-table

spark@9eb71e5f09a6:/app$ java -version
openjdk version "17.0.15" 2025-04-15
OpenJDK Runtime Environment Temurin-17.0.15+6 (build 17.0.15+6)
OpenJDK 64-Bit Server VM Temurin-17.0.15+6 (build 17.0.15+6, mixed mode, sharing)

Or you could directly call the command spark-submit:

docker run --rm --entrypoint=/opt/spark/bin/spark-submit cc-index-table

Python, PySpark, Jupyter Notebooks

Not part of this project. Please have a look at cc-pyspark for examples how to query and process the tabular URL index with Python and PySpark. The project cc-notebooks includes some examples how to gain insights into the Common Crawl data sets using the columnar index.

Conversion of the URL index

A Spark job converts the Common Crawl URL index files (a sharded gzipped index in CDXJ format) into a table in Parquet or ORC format.

> APPJAR=target/cc-index-table-0.3-SNAPSHOT-jar-with-dependencies.jar
> $SPARK_HOME/bin/spark-submit --class org.commoncrawl.spark.CCIndex2Table $APPJAR

CCIndex2Table [options] <inputPathSpec> <outputPath>

Arguments:
  <inputPaths>
        pattern describing paths of input CDX files, e.g.
        s3a://commoncrawl/cc-index/collections/CC-MAIN-2017-43/indexes/cdx-*.gz
  <outputPath>
        output directory

Options:
  -h,--help                     Show this message
     --outputCompression <arg>  data output compression codec: gzip/zlib
                                (default), snappy, lzo, none
     --outputFormat <arg>       data output format: parquet (default), orc
     --partitionBy <arg>        partition data by columns (comma-separated,
                                default: crawl,subset)
     --useNestedSchema          use the schema with nested columns (default:
                                false, use flat schema)

The script convert_url_index.sh runs CCIndex2Table using Spark on Yarn.

Columns are defined and described in the table schema (flat or nested).

Runing the converter in a Docker container

The converter can be run from the Docker container, built from the Dockerfile, see the instructions above.

The steps given below are just an example – the way data is passed in and out from the container may vary.

# create a test folder
mkdir -p /tmp/data/in

# copy CDX files into /tmp/data/in/
cp .../*.cdx.gz /tmp/data/in/

tree /tmp/data/
# outputs:
# /tmp/data/
# └── in
#     └── CC-MAIN-20241208172518-20241208202518-00000.cdx.gz

# ensure that also the user "spark" in the container has write permissions
chmod a+w /tmp/data

# note: the output will be written to /tmp/data/out/, but Spark
#       will complain if the output folder already exists

# launch the Docker container, running the Spark job
docker run --mount=type=bind,source=/tmp/data,destination=/data --rm cc-index-table /data/in /data/out

tree /tmp/data/
# /tmp/data/
# ├── in
# │   └── CC-MAIN-20241208172518-20241208202518-00000.cdx.gz
# └── out
#     ├── crawl=CC-MAIN-2024-51
#     │   └── subset=warc
#     │       └── part-00000-4b2c091d-24db-4248-8c3c-817fd04b7a85.c000.gz.parquet
#     └── _SUCCESS

Query the table in Amazon Athena

First, the table needs to be imported into Amazon Athena. In the Athena Query Editor:

1. create a database ccindex: CREATE DATABASE ccindex and make sure that it's selected as "DATABASE"
2. edit the "create table" statement (flat or nested) and add the correct table name and path to the Parquet/ORC data on s3://. Execute the "create table" query.
3. make Athena recognize the data partitions on s3://: MSCK REPAIR TABLE ccindex (do not forget to adapt the table name). This step needs to be repeated every time new data partitions have been added.

A couple of sample queries are also provided (for the flat schema): - count captures over partitions (crawls and subsets), get a quick overview how many pages are contained in the monthly crawl archives (and are also indexed in the table): count-by-partition.sql - page/host/domain counts per top-level domain: count-by-tld-page-host-domain.sql - "word" count of - host name elements (split host name at . into words): count-hostname-elements.sql - URL path elements (separated by /): count-url-path-elements.sql - count - HTTP status codes: count-fetch-status.sql - the domains of a specific top-level domain: count-domains-of-tld.sql - page captures of Internationalized Domain Names (IDNA): count-idna.sql - URL paths pointing to robots.txt files count-robotstxt-url-paths.sql (note: /robots.txt may be a redirect) - pages of the Alexa top 1 million sites by joining two tables (ccindex and a CSV file): count-domains-alexa-top-1m.sql - compare document MIME types (Content-Type in HTTP response header vs. MIME type detected by Tika: compare-mime-type-http-vs-detected.sql - distribution/histogram of host name lengths: host-length-distrib.sql - export WARC record specs (file, offset, length) for - a single domain: get-records-of-domain.sql - a specific MIME type: get-records-of-mime-type.sql - a specific language (e.g., Icelandic): get-records-for-language.sql - home pages of a given list of domains: get-records-home-pages.sql - find homepages for low-resource languages: get-home-pages-languages.sql - obtain a random sample of URLs: random-sample-urls.sql - find similar domain names by Levenshtein distance (few characters changed): similar-domains.sql - average length, occupied storage and payload truncation of WARC records by MIME type: average-warc-record-length-by-mime-type.sql - count pairs of top-level domain and content language: count-language-tld.sql - find correlations between TLD and content language using the log-likelihood ratio: loglikelihood-language-tld.sql - ... and similar for correlations between content language and character encoding: correlation-language-charset.sql - site discovery by content language: - specific language(s): site-discovery-by-language.sql - non-English sites: discovery-of-non-english-sites - Hungarian sites: site-discovery-hungarian.sql - find multi-lingual domains by analyzing URL paths: get-language-translations-url-path.sql - extract robots.txt records for a list of sites: get-records-robotstxt.sql

Athena creates results in CSV format. E.g., for the last example, the mining of multi-lingual domains we get:

Process the Table with Spark

Export Views

As a first use case, let's export parts of the table and save it in one of the formats supported by Spark. The tool CCIndexExport runs a Spark job to extract parts of the index table and save it as a table in Parquet, ORC, JSON or CSV. It may even transform the data into an entirely different table. Please refert to the Spark SQL programming guide and the overview of built-in SQL functions for more information.

The tool requires as arguments input and output path, but you also want to pass a useful SQL query instead of the default SELECT * FROM ccindex LIMIT 10. All available command-line options are show when called with --help:

```

$SPARK_HOME/bin/spark-submit --class org.commoncrawl.spark.examples.CCIndexExport $APPJAR --help

CCIndexExport [options]

Arguments: path to cc-index table s3://commoncrawl/cc-index/table/cc-main/warc/ output directory

Options: -h,--help Show this message -q,--query