Projections

A projection is a concept unique to Kinetica. A projection is created through the /create/projection endpoint and represents a set of columns from and/or column expressions applied to a source data set (table or view). Projections are memory-only tables by default, but can be persisted (like a table) using the persist option. While projections can simply represent a whole-cloth cross-section of an existing table, filtering clauses can be applied before creating the column to create an entirely new data set. Expressions that can be used to define columns or filters can be found here.

A projection name must adhere to the standard naming criteria. Each projection exists within a schema and follows the standard name resolution rules for tables.

A projection has several advantages:

• Because a projection is a copy of your data, querying the projection will be faster than querying a join. You could create a projection of a join to increase the speed of queries against the data set. This also means derived columns and join lookups are precalculated.
• A /create/projection call can calculate derived columns natively
• A projection can be sharded on entirely different columns than its source table or view; a projection can also be sharded even if its source table or view is randomly sharded or replicated
• A projection can also be replicated even if its source table is sharded

Projections can be created from any table or view and will create a new type schema using the columns and expressions specified in the /create/projection column list from the source schema. Note that if the source data set is replicated the projection will also be replicated, by default. If the source data set is sharded, the projection will also be sharded by default, but only if the entire shard key is included in the /create/projection call and is specified in the same order as it appears in the source data set. The sharded columns do not have to be listed continguously.

For example, a source table employees has four columns (in order), employee_name, salary, employee_id, and has_benefits, and two of those columns, salary and employee_id, comprise the shard key. To create a sharded projection using the employees table, the shard key will need to be listed in the same order as the source table, e.g., column_names = ["salary", "employee_name", "employee_id"].

A projection of an existing table or view (sharded, replicated, or otherwise) can be sharded on entirely different columns using the shard_key option on the /create/projection endpoint, regardless of the column(s) the table/view was previously sharded on (if any). See Shard Keys for more information on shard keys and sharding restrictions.

A projection is also a means to perform window functions. For examples of using window functions, see Examples.

Several limitations are discussed in further detail in the Limitations and Cautions section.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

h_db.create_projection(
    table_name = "demo.nyctaxi",
    projection_name = "example.nyctaxi_single_pickups",
    column_names = [
        "vendor_id",
        "pickup_latitude",
        "pickup_longitude",
        "pickup_datetime",
        "trip_distance",
        "total_amount"
    ],
    options = {
        "expression": "passenger_count = 1 AND total_amount > 0"
    }
)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

h_db.create_projection(
    table_name = "example.nyctaxi_single_pickups",
    projection_name = "example.nyctaxi_single_pickups_local_avg_dist",
    column_names = [
        "vendor_id",
        "pickup_datetime",
        "trip_distance",
        "AVG(trip_distance) " \
        "OVER (" \
        "   PARTITION BY vendor_id " \
        "   ORDER BY pickup_datetime " \
        "   ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING " \
        ") AS local_avg_dist"
    ],
    options = {
        "order_by": "vendor_id, pickup_datetime"
    }
)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

h_db.create_projection(
    table_name = "example.nyctaxi_single_pickups",
    projection_name = "example.nyctaxi_single_pickups_running_total_revenue",
    column_names = [
        "vendor_id",
        "pickup_datetime",
        "SUM(total_amount) " \
        "OVER (" \
        "   PARTITION BY vendor_id " \
        "   ORDER BY pickup_datetime " \
        ") AS running_total_revenue"
    ],
    options = {
        "order_by": "vendor_id, pickup_datetime"
    }
)

1
2
3
4
5

h_db.filter(
    table_name = "example.nyctaxi_single_pickups",
    view_name = "example.nyctaxi_single_pickups_over_10_miles",
    expression = "trip_distance > 10"
)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

h_db.aggregate_group_by(
    table_name = "example.nyctaxi_single_pickups",
    column_names = [
      "vendor_id",
      "MIN(total_amount) AS min_revenue",
      "AVG(total_amount) AS avg_revenue",
      "MAX(total_amount) AS max_revenue"
    ],
    offset = 0,
    limit = -9999,
    options = {"result_table": "example.nyctaxi_single_pickups_statistics"}
)