Handling Large Search Results

Let's take a look at how to Query, Filter, and Split large search results from GRAX Search Jobs.

The most robust way to export, search, query, and filter large data sets from GRAX is using Search Jobs. Simply
navigate to the Search tab and click on the button New Search Job and enter the criteria to find the data you are
looking for. The data will be prepared in a versatile CSV file that you can download or immediately restore back into

Search Jobs

Search Jobs provide a robust bulk search capabilities, allowing you to find large sets of records that have been
archived, deleted, or both.

Querying Large GRAX Search Results

The instructions below will give you a framework to do complex queries, joins, filters, and segmentation of large
Search Job results.
(In the below sample we are using a 564.8 MB file of Tasks containing 3,009,400 search results)

  1. Download the SQLite open source tool.

  2. Download the GRAX Search Job results via CSV save as graxsearchresults.csv to where you saved Step 1.

  3. Open a Terminal / Command Line and navigate to where SQLite and the graxsearchresults.csv are downloaded.

  4. Execute the below statements in the Terminal / Command Line to load the graxsearchresults.csv results into memory
    and temporary SQL table named tempsearchtable.

    • NOTE: Before proceeding, please check you have sufficient system resources to load the graxsearchresults.csv in
    .import ./graxsearchresults.csv tempsearchtable --csv
    .mode box
  5. Get count of tempsearchtable

    sqlite> select count(*) from tempsearchtable;
    │ count(*) │
    │ 3009400  │
  6. How to search by a Date field.

    sqlite> select count(*) from tempsearchtable where datetime(CreatedDate)>'2022-9-30' and datetime(CreatedDate)<'2022-10-05T21:00:00';
    │ count(*) │
    │ 1284400  │
  7. How to search by Boolean

    sqlite> select count(*) from tempsearchtable where boolean(IsClosed)=true;
    │ count(*) │
    │ 2623005  │
  8. How to export search results to CSV file

    sqlite> .mode csv
    sqlite> .header on
    sqlite> .once ./filteredresults.csv
    sqlite> select Id from tempsearchtable where datetime(CreatedDate)>'2022-9-30' and datetime(CreatedDate)<'2022-11-1';
    $ head -10 ./filteredresults.csv

SQLite Help

The steps above use open-source SQLite; you can find the SQLite Help Documentation Here