Cassandra User Defined Functions using the Python Driver

cluster = Cluster(protocol_version=4)

session = cluster.connect()

session.execute("CREATE KEYSPACE simplex WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'}")

session.execute("USE simplex")

enable_user_defined_functions: true

CREATE function_name(arg type)

    RETURNS NULL ON NULL INPUT

    RETURNS type

    LANGUAGE java

    AS '

    return arg';

session.execute("""

    CREATE TYPE Address (

        street text,

        city text,

        state text,

        zip int)

""")

session.execute("""

    CREATE TABLE inventory (

        item_id uuid PRIMARY KEY,

        name text,

        dimensions Tuple<double,double,double,text>,

        available_states Set<text>,

        item_location frozen<Address>)

""")

from collections import namedtuple

from uuid import uuid4

insert = session.prepare("""INSERT INTO inventory

    (item_id, name, dimensions, available_states, item_location)

    VALUES

    (?, ?, ?, ?, ?)

""")

Address = namedtuple('address', ('street', 'city', 'state', 'zip'))

session.execute(insert, [uuid4(), 'camping_tent', (8.5, 5.0, 9.5, 'ft'), set(['WA', 'OR', 'CA']), Address('487 Franciscan Ct', 'Santa Clara', 'CA', 95050)])

session.execute(insert, [uuid4(), 'cooking_pot', (8.4, 4.7, 8.4, 'in'), set(['WA', 'OR', 'CA', 'AZ', 'NV', 'UT', 'TX']), Address('623 Shaver St', 'Portland', 'OR', 97212)])

session.execute(insert, [uuid4(), 'curved_knife', (2.96, 0.450, 0.100, 'in'), set(['WA', 'OR', 'CA', 'AZ', 'NV', 'UT', 'TX', 'MN', 'KY', 'NY', 'NJ', 'FL', 'GA']), Address('728 Denny Way', 'Seattle', 'WA', 98122)])

item_id                              | available_states                                                               | dimensions              | item_location                                                               | name

--------------------------------------+--------------------------------------------------------------------------------+-------------------------+-----------------------------------------------------------------------------+--------------

 3c0958bb-a9ff-49b9-b076-d9ee878bb0a8 |                                                             {'CA', 'OR', 'WA'} |     (8.5, 5, 9.5, 'ft') | {street: '487 Franciscan Ct', city: 'Santa Clara', state: 'CA', zip: 95050} | camping_tent

 fed6171e-329a-4b23-a53a-cbed87d841c1 |                                     {'AZ', 'CA', 'NV', 'OR', 'TX', 'UT', 'WA'} |   (8.4, 4.7, 8.4, 'in') |        {street: '623 Shaver St', city: 'Portland', state: 'OR', zip: 97212} |  cooking_pot

 196d5b4b-7e7a-45c5-b993-5f397a9ebe8b | {'AZ', 'CA', 'FL', 'GA', 'KY', 'MN', 'NJ', 'NV', 'NY', 'OR', 'TX', 'UT', 'WA'} | (2.96, 0.45, 0.1, 'in') |         {street: '728 Denny Way', city: 'Seattle', state: 'WA', zip: 98122} | curved_knife

(3 rows)

results = session.execute("SELECT item_id, available_states FROM inventory")

sale_items = []

for item in results:

    if len(item.available_states) > 10:

        sale_items.append(item.item_id)

print sale_items

# OUTPUT:

[UUID('615db411-ab52-48df-a696-ac00c5ca9c79')]

session.execute("""

    CREATE FUNCTION len_states(states Set<text>)

        RETURNS NULL ON NULL INPUT

        RETURNS int

        LANGUAGE java

        AS '

        return states.size();'

""")

results = session.execute("SELECT item_id, len_states(available_states) FROM inventory")

sale_items = []

for item in results:

    if item.simplex_len_states_available_states > 10:

        sale_items.append(item.item_id)

print sale_items

# OUTPUT:

[UUID('615db411-ab52-48df-a696-ac00c5ca9c79')]

results = session.execute("SELECT item_id, item_location FROM inventory")

sale_items = []

for item in results:

    if item.item_location.zip < 97000:

        sale_items.append(item.item_id)

print sale_items

# OUTPUT:

[UUID('4c9616dc-f649-42c3-ace3-ecdbcda1802c')]

session.execute("""

    CREATE FUNCTION extract_zip(address Address)

        RETURNS NULL ON NULL INPUT

        RETURNS int

        LANGUAGE java

        AS '

        return address.getInt("zip");'

""")

results = session.execute("SELECT item_id, extract_zip(item_location) FROM inventory")

sale_items = []

for item in results:

    if item.simplex_extract_zip_item_location < 97000:

        sale_items.append(item.item_id)

print sale_items

# OUTPUT:

[UUID('4c9616dc-f649-42c3-ace3-ecdbcda1802c')]

results = session.execute("SELECT item_id, dimensions FROM inventory")

volumes = []

for item in results:

    volume = str(item.dimensions[0] * item.dimensions[1] * item.dimensions[2]) + " " + item.dimensions[3] + "^3"

    volumes.append((item.item_id, volume))

print volumes

# OUTPUT:

[(UUID('6d76a559-ecd8-4a76-ad96-a8a6aa3a6f83'), u'331.632 in^3'), (UUID('8b485426-f054-4406-9e20-2463a42b0d65'), u'0.1332 in^3'), (UUID('27d74750-1b0e-45b9-9628-e6df9ce342ef'), u'403.75 ft^3')]

session.execute("""

    CREATE FUNCTION volume(dimensions tuple<double, double, double, text>)

        RETURNS NULL ON NULL INPUT

        RETURNS text

        LANGUAGE java

        AS '

        return String.valueOf(dimensions.getDouble(0) * dimensions.getDouble(1) * dimensions.getDouble(2)) + " " + dimensions.getString(3) + "^3";'

""")

results = session.execute("SELECT item_id, volume(dimensions) FROM inventory")

volumes = []

for item in results:

    volumes.append((item.item_id, item.simplex_volume_dimensions))

print volumes

# OUTPUT:

[(UUID('6d76a559-ecd8-4a76-ad96-a8a6aa3a6f83'), u'331.632 in^3'), (UUID('8b485426-f054-4406-9e20-2463a42b0d65'), u'0.1332 in^3'), (UUID('27d74750-1b0e-45b9-9628-e6df9ce342ef'), u'403.75 ft^3')]