Join DFS Tables

Table joins on DFS tables are not supported outside SQL statements. The reason is that joining DFS tables without any filters such as select, where clauses usually results in unnecessarily large size tables.

Note:

  1. If both the left table and right table are partitioned tables:

    • Both tables must be in the same partitioned database.

    • All the partitioning columns must be included in the joining columns. The joining columns can include columns that are not partitioning columns.

    • Cross join is not supported.

  2. If only the right table is a partitioned table: only equi join is supported.

  3. If only the left table is a partitioned table: there are no restrictions regarding this case. When a partitioned table joins with an in-memory table or a dimension table (the right table thereafter), the system will copy the right table to all the nodes where the partitioned table is located for joining. If the right table is of significant size, it could be very time consuming to transfer the data. To improve performance, the system attempts to filter the right table as much as possible with the where conditions before data transfer. In this case if the right table is too large, the execution could be slow. It is recommended to use a small right table for this case.

  4. A partitioned table can be joined with a dimension table in any database.

Examples

Create a DFS database:

dates=2019.01.01..2019.01.31
syms="A"+string(1..30)
sym_range=cutPoints(syms,3)
db1=database("",VALUE,dates)
db2=database("",RANGE,sym_range)
db=database("dfs://stock",COMPO,[db1,db2])
n=10000
datetimes=2019.01.01T00:00:00..2019.01.31T23:59:59
t=table(take(datetimes,n) as trade_time,take(syms,n) as sym,rand(1000,n) as qty,rand(500.0,n) as price)
trades=db.createPartitionedTable(t,`trades,`trade_time`sym).append!(t)

n=200
t2=table(take(datetimes,n) as trade_time,take(syms,n) as sym,rand(500.0,n) as bid,rand(500.0,n) as offer)
quotes=db.createPartitionedTable(t2,`quotes,`trade_time`sym).append!(t2)

t3=table(syms as sym,take(0 1,30) as type)
infos=db.createDimensionTable(t3,`infos).append!(t3)

Example 1. Join 2 DFS table trades and quotes:

// output
select * from ej(trades,quotes,`trade_time`sym);
trade_time sym qty price bid offer
2019.01.01T00:00:00 A1 39 7.366735 37.933525 446.917644
2019.01.01T00:00:09 A10 15 461.381014 405.092702 26.659516
2019.01.01T00:00:10 A11 987 429.981704 404.289413 347.64917
2019.01.01T00:00:11 A12 266 60.466206 420.426175 83.538043
2019.01.01T00:00:12 A13 909 362.057769 324.886047 162.502655
2019.01.01T00:00:13 A14 264 113.964472 497.598722 103.114702
2019.01.01T00:00:14 A15 460 347.518325 24.584629 357.854207
2019.01.01T00:00:15 A16 196 258.889177 49.467399 13.974672
2019.01.01T00:00:16 A17 198 403.564922 428.539984 208.410852
2019.01.01T00:00:17 A18 30 288.469046 41.905556 378.080141
... ... ... ... ... ...

Example 2. Join a DFS table and a dimension table.

// output
select * from lj(trades,infos,`sym);
trade_time sym qty price type
2019.01.01T00:00:00 A1 856 359.809918 0
2019.01.01T00:00:09 A10 368 305.801702 1
2019.01.01T00:00:10 A11 549 447.406744 0
2019.01.01T00:00:11 A12 817 115.613373 1
2019.01.01T00:00:12 A13 321 298.317481 0
2019.01.01T00:00:13 A14 3 2.289171 1
2019.01.01T00:00:14 A15 586 91.841629 0
2019.01.01T00:00:15 A16 745 43.256142 1
2019.01.01T00:00:16 A17 60 0.153205 0
... ... ... ... ...

Example 3. Join a DFS table and an in-memory table.

// output
tmp=table("A"+string(1..15) as sym,2019.01.11..2019.01.25 as date);
select * from ej(trades,tmp,`sym);
trade_time sym qty price date
2019.01.01T00:00:00 A1 856 359.809918 2019.01.11
2019.01.01T00:00:09 A10 368 305.801702 2019.01.20
2019.01.01T00:00:10 A11 549 447.406744 2019.01.21
2019.01.01T00:00:11 A12 817 115.613373 2019.01.22
2019.01.01T00:00:12 A13 321 298.317481 2019.01.23
2019.01.01T00:00:13 A14 3 2.289171 2019.01.24
2019.01.01T00:00:14 A15 586 91.841629 2019.01.25
2019.01.01T00:00:30 A1 390 325.407485 2019.01.11
... ... ... ... ...

Please refer to TableJoiners for more information.