Kinetica supports the SQL concept of joining data sets. It does so through the /create/jointable endpoint, which creates an intermediary join view, whose purpose is to connect the tables involved in the join before any query or aggregation operations are applied. In addition to any equi-join clauses specified in the join view creation expression, filtering clauses may also be used to further refine the data set.
Tables being joined together must either be replicated, or be sharded on the columns being used to join the tables, to avoid extensive interprocessor communication. While a join may include any number of replicated tables and sharded tables, all sharded tables must be joined on their respective shard key columns. Distributed joins, or joins that connect sharded tables on columns other than their shard keys are not supported at this time.
For instance, given a table A and a table B, both sharded on their
respective id columns, the only join possible is between A.id and
B.id. No other columns from either table can be used to connect the two
tables.
When a join view is created, it will contain the results of all join and filter clauses specified. Any replicated tables not joined in the specified expression will be merged into the join view as cross-products. Since this is a generally undesirable outcome, it is recommended that all tables specified in the join view creation be joined within the specified expression. Any tables not merged into the overall join by the expression can be subsequently merged by using the /filter endpoint to connect the unmerged tables.
Once the join view has been created, adding or deleting data from the joined view base tables does not change the data queried from the join view, though updates will be reflected. The join view may be used as a source table in most API calls.
Joins can be performed on any number and combination of tables and views, but not on collections.
These limitations, and others, are discussed in further detail in the Limitations & Cautions section.
To create a join view, the /create/jointable endpoint requires four parameters:
TableA as A, TableB as B, ..., TableZ)TableA.id, B.id as B_id, ..., TableZ.id as Z_id)TableA.b_id = B.id and ... A.z_id = TableZ.id)Given a table clause of TableA as A, the a_id column of that table can
be accessed via any of the following means:
a_id)TableA.a_id)A.a_id)NOTE: all sharded tables must be associated through the given expression.
In Python, given tables customer, order and lineitem, a
join view can be created via:
retobj = gpudb.create_join_table(
join_table_name = 'customer_order_item',
table_names = ['customer as c','order as o','lineitem as l'],
column_names = ['c.name','o.orderstatus','l.partkey','l.price','l.quantity'],
expressions = ['c.custkey = o.custkey','o.orderkey = l.orderkey']
)
The view produced is an equi-join of the member tables, containing the specified columns from all three tables, accessible by their simple column names, given no name collisions among the columns selected. The result would match what would be produced by the SQL:
SELECT
c.name,
o.orderstatus,
l.partkey,
l.price,
l.quantity
FROM
customer c,
order o,
lineitem l
WHERE
c.customerkey = o.customerkey AND
o.orderkey = l.orderkey
or:
SELECT
c.name,
o.orderstatus,
l.partkey,
l.price,
l.quantity
FROM
customer c
INNER JOIN
order o ON c.customerkey = o.customerkey
INNER JOIN
lineitem l ON o.orderkey = l.orderkey
The list of endpoints currently implemented to operate against join view is as follows:
A Python example filter on the join view created in the Creating a Join View section for customer orders containing multiples of the same product is:
retobj = gpudb.filter(
table_name = 'customer_order_item',
view_name = 'customer_order_item_multiple',
expression = 'quantity > 1'
)
When executed against a join view, the filter endpoint produces a view that is also a join view, having the same endpoint restrictions mentioned above. A chain of such filters can be used to create more and more restrictive views of the original data set. The filter endpoint can also be used to merge any tables not merged during the creation of the original join view or during subsequent filtering of the view.
To call the group-by endpoint, in Python:
retobj = gpudb.aggregate_group_by(
table_name = 'customer_order_item',
column_names = [
'name',
'sum(quantity) as total_items',
'sum(quantity * price) as total_spent'
],
offset = 0
)
To call the statistics endpoint, in Python:
retobj = gpudb.aggregate_statistics(
table_name = 'customer_order_item',
column_name = 'quantity * price',
stats = 'sum'
)
To call the endoint to retrieve data from specific join view columns, in Python:
retobj = gpudb.get_records_by_column(
table_name = 'customer_order_item',
column_names = ['name','orderstatus','partkey'],
offset = 0,
limit = 50
)
This example shows how the TPC-H benchmark's Query 5 SQL statement can be implemented in the Kinetica Python interface. The SQL for Query 5 is:
select
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
from
customer,
orders,
lineitem,
supplier,
nation,
region
where
c_custkey = o_custkey and
l_orderkey = o_orderkey and
l_suppkey = s_suppkey and
c_nationkey = s_nationkey and
s_nationkey = n_nationkey and
n_regionkey = r_regionkey and
r_name = 'ASIA' and
o_orderdate >= 757382400 and
o_orderdate < 788918400
group by
n_name
order by
revenue desc;
To implement the above statement, a join view needs to be created with all of
the tables listed in the FROM clause, and an expression, based on the
WHERE clause, which binds the tables together and applies the necessary
filters:
retobj = gpudb.create_join_table(
join_table_name = 'tpch_query_5_join_filter',
table_names = ['customer','orders','lineitem','supplier','nation','region'],
column_names = ['o_orderdate','l_extendedprice','l_discount','n_name','r_name'],
expressions = [
'c_custkey = o_custkey',
'l_orderkey = o_orderkey',
'l_suppkey = s_suppkey',
'c_nationkey = s_nationkey',
's_nationkey = n_nationkey',
'n_regionkey = r_regionkey',
'r_name = \'ASIA\'',
'o_orderdate >= 757382400',
'o_orderdate < 788918400'
]
)
Neither the table names nor the column names of the join view need to be aliased, as all column names are unique across all tables in this example.
This join view could also have been done in two steps, a join and a filter:
retobj = gpudb.create_join_table(
join_table_name = 'tpch_query_5_join',
table_names = ['customer','orders','lineitem','supplier','nation','region'],
column_names = ['o_orderdate','l_extendedprice','l_discount','n_name','r_name'],
expressions = [
'c_custkey = o_custkey',
'l_orderkey = o_orderkey',
'l_suppkey = s_suppkey',
'c_nationkey = s_nationkey',
's_nationkey = n_nationkey',
'n_regionkey = r_regionkey'
]
)
The join view can then have the WHERE clause filters applied in a
separate step:
retobj = gpudb.filter(
table_name = 'tpch_query_5_join',
view_name = 'tpch_query_5_join_filter',
expression = 'r_name = \'ASIA\' and ' \
'o_orderdate >= 757382400 and ' \
'o_orderdate < 788918400'
)
Regardless of whether the join/filter was done in one step or two, the results
need to be grouped and summed to match the TPC-H query. The following call is
given the directive to sort_by the value, which will sort on the
aggregation column:
retobj = gpudb.aggregate_group_by(
table_name = 'tpch_query_5_join_filter',
column_names = [
'n_name',
'sum(l_extendedprice * (1 - l_discount))'
],
offset = 0,
options = {'sort_by':'value', 'sort_order':'descending'}
)
The results can be displayed by calling:
gpudb.parse_dynamic_response(retobj, do_print=True)
In order to improve the performance of a join operation, the columns used to make the relation should be indexed.
When a join is performed along a foreign key relation, it is indexed automatically as a function of the foreign key itself--no additional setup is required.
When a join is performed along a relation where no foreign key exists, the indexing must be done as a separate configuration step. Both columns, on either side of the join relation, should have a column index applied. An error may result if a join is attempted across a relation where only one side of the relation is indexed.
When a composite equi-join is performed, only one index will be used. For
example: you have two tables, "a" and "b", that each have the same columns,
"x" and "y". You also have an attribute index on each column. If you use the
expression full_outer join a,b on a.x == b.x and a.y == b.y, only the first
expression, a.x == b.x, will use the index.
Joins must merge together simple data sets, and can therefore use both tables and views as sources, but not collections.
The amount of memory a join requires depends on the type of join and the source data sets being joined together.
Join views are affected by data modifications on underlying tables in different ways, based on the type of data modification.
Overall, data manipulation within the tables supporting a join view will not cause the number of records returned by the join view to change, though updates to records already part of the join view record set will be reflected in the data values retrieved from the join view.
Though the sharded tables in a join view must be joined in the join view creation expression, the replicated tables do not have to be. In this case, a Cartesian Product will result for every unmerged replicated table.
For example, given table A with integer columns x and y and three
records, and table B with a string column r and an integer column
s and two records, as follows:
Table A:
| x | y |
|---|---|
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
Table B:
| r | s |
|---|---|
| 'x' | 100 |
| 'y' | 200 |
Then the join view of A and B, prefixing the result columns with
a_ and b_, respectively, and with no expression given is a 4-column
view with 6 records, as follows:
| a_x | a_y | b_r | b_s |
|---|---|---|---|
| 1 | 10 | 'x' | 100 |
| 1 | 10 | 'y' | 200 |
| 2 | 20 | 'x' | 100 |
| 2 | 20 | 'y' | 200 |
| 3 | 30 | 'x' | 100 |
| 3 | 30 | 'y' | 200 |
It should be noted that while the view produced is the cross-product of the input tables, the actual cross-product is never realized in memory. However, when doing an aggregate operation (such as group-by or histogram) on the join view, every object in the cross-product will be visited. Filter operations that transform input join views into smaller result join views should be done prior to any aggregation queries, reducing the number of records that must be visited.
NOTE: In order for a join to be allowed to contain a Cartesian Product, both the server and the client call need to be configured properly. See the Configuration Reference section for server configuration details and /create/jointable endpoint documentation for API details. If not configured properly, a Cartesian Product may result in an error.