Download notebook · View source · Open in Colab

Indexing for Faster Queries

Indexes help ApertureDB locate records without scanning every object or connection. This example creates and removes an ordered index on an ingredient name.

Creating an index before loading data avoids building it over existing records. An index can also be added after ingestion, when ApertureDB must index the records already present.

Each run uses a unique ingredient class, so index changes and cleanup leave existing classes untouched. Keep the setup variables available until cleanup completes.

Connect to the database

The examples require a running database and configured client credentials. The quick start guide describes the setup.

# Install the required client packages if needed
%pip install --quiet "aperturedb==0.5.1"

Note: you may need to restart the kernel to use updated packages.

from aperturedb.CommonLibrary import create_connector

# Create the connector for ApertureDB
client = create_connector()

# A unique class keeps index changes and cleanup within this run.
import uuid
index_class = "DocumentationIngredient_" + uuid.uuid4().hex[:12]

Create an index on Ingredient's name

An ordered index on name supports property comparisons against ingredient names. The query creates that index and adds an ingredient in this run's class.

query = [{
    "CreateIndex": {
        "class": index_class,
        "target": "entity",
        "kind": "ordered",
        "property": "name"
    }
},{
    "AddEntity": {                        # Add a record for the example class
        "class": index_class, 
        "properties": {        
            "name": "butter",  
            "macronutrient": "fat",
            "subgroup": "dairy",
            "category": "vegetarian"
        },
        "if_not_found": ['$name', '==', 'butter']
    }
}]

response, blobs = client.query(query)

assert isinstance(response, list) and all(next(iter(r.values()))["status"] == 0 for r in response), response
client.print_last_response()

[
    {
        "CreateIndex": {
            "status": 0
        }
    },
    {
        "AddEntity": {
            "status": 0
        }
    }
]

Verify index using GetIndexes

GetIndexes returns the index definition for this run's class, name property, and ordered kind.

query = [{"GetIndexes": {
    "target": "entity", "class": index_class, "property": "name", "kind": "ordered"
}}]
response, blobs = client.query(query)
assert response[0]["GetIndexes"]["status"] == 0, response
assert response[0]["GetIndexes"]["returned"] == 1, response
client.print_last_response()

[
    {
        "GetIndexes": {
            "indexes": {
                "entity": {
                    "DocumentationIngredient_49a07e9ab257": {
                        "name": [
                            {
                                "kind": "ordered",
                                "params": {
                                    "text": "case_sensitive"
                                }
                            }
                        ]
                    }
                }
            },
            "returned": 1,
            "status": 0
        }
    }
]

Remove the index

RemoveIndex removes the selected index. The ingredient records remain in the database.

query = [{
    "RemoveIndex": {
        "class": index_class,
        "target": "entity",
        "kind": "ordered",
        "property": "name"
    }
}]

response, blobs = client.query(query)

assert isinstance(response, list) and all(next(iter(r.values()))["status"] == 0 for r in response), response
client.print_last_response()

[
    {
        "RemoveIndex": {
            "status": 0
        }
    }
]

Verify index using GetIndexes

GetIndexes returns no matching definition after the ordered index on name is removed.

query = [{"GetIndexes": {
    "target": "entity", "class": index_class, "property": "name", "kind": "ordered"
}}]
response, blobs = client.query(query)
assert response[0]["GetIndexes"]["status"] == 0, response
assert response[0]["GetIndexes"]["returned"] == 0, response
client.print_last_response()

[
    {
        "GetIndexes": {
            "indexes": {},
            "returned": 0,
            "status": 0
        }
    }
]

Using Python SDK Utils for Indexing

The SDK Utils helpers provide Python methods for creating and removing indexes. These helpers select the ordered kind by default. Their property_key argument specifies the indexed property; the JSON command calls this field property. The JSON command supports choosing another index kind.

from aperturedb.Utils import Utils

utils = Utils(client)
assert utils.create_entity_index(class_name=index_class, property_key="name")

GetIndexes returns the index created by the SDK helper.

query = [{"GetIndexes": {
    "target": "entity", "class": index_class, "property": "name", "kind": "ordered"
}}]
response, blobs = client.query(query)
assert response[0]["GetIndexes"]["status"] == 0, response
assert response[0]["GetIndexes"]["returned"] == 1, response
client.print_last_response()

[
    {
        "GetIndexes": {
            "indexes": {
                "entity": {
                    "DocumentationIngredient_49a07e9ab257": {
                        "name": [
                            {
                                "kind": "ordered",
                                "params": {
                                    "text": "case_sensitive"
                                }
                            }
                        ]
                    }
                }
            },
            "returned": 1,
            "status": 0
        }
    }
]

The corresponding Utils method removes the ordered index.

assert utils.remove_entity_index(class_name=index_class, property_key="name")

GetIndexes returns no matching definition after the helper removes the index.

query = [{"GetIndexes": {
    "target": "entity", "class": index_class, "property": "name", "kind": "ordered"
}}]
response, blobs = client.query(query)
assert response[0]["GetIndexes"]["status"] == 0, response
assert response[0]["GetIndexes"]["returned"] == 0, response
client.print_last_response()

[
    {
        "GetIndexes": {
            "indexes": {},
            "returned": 0,
            "status": 0
        }
    }
]

Cleanup

The cleanup query removes the ingredient created in this run's class.

query = [{
    "DeleteEntity": {
        "with_class": index_class,
        "constraints": ['$name', '==', 'butter']
    }
}]

response, blobs = client.query(query)

assert isinstance(response, list) and all(next(iter(r.values()))["status"] == 0 for r in response), response
client.print_last_response()
response, _ = client.query([{"FindEntity": {"with_class": index_class, "results": {"count": True}}},
                            {"GetIndexes": {"target": "entity", "class": index_class}}])
assert response[0]["FindEntity"]["status"] == response[1]["GetIndexes"]["status"] == 0, response
assert response[0]["FindEntity"]["count"] == response[1]["GetIndexes"]["returned"] == 0, response
client.close()
print("Verified cleanup: no fixture entities or indexes remain.")

[
    {
        "DeleteEntity": {
            "count": 1,
            "status": 0
        }
    }
]
Verified cleanup: no fixture entities or indexes remain.

What's next?

• Bulk loading of data
• Database administration