upsert!
Syntax
upsert!(obj, newData, [ignoreNull=false], [keyColNames], [sortColumns])
Arguments
obj is a keyed table, indexed table, or a DFS table.
newData is an in-memory table.
ignoreNull is a Boolean value. If set to true, for the NULL values in newData, the correponding elements in obj are not updated. The default value is false.
keyColNames is a STRING scalar/vector. When obj is a DFS table, keyColNames are considered as the key columns.
sortColumns is a STRING scalar or vector. The updated partitions will be sorted on sortColumns (only within each partition, not across partitions).
Please note:
1. To specify sortColumns, obj must be a DFS table.
2. When obj is an empty table, setting sortColumns has no effect. That is, the system will not sort the inserted data.
Details
Insert rows into a keyed table or indexed table if the values of the primary key do not already exist, or update them if they do.
Note:
When using this function, please make sure the corresponding columns in table newData and obj are arranged in the same order, or the system may generate the wrong result or throw an error.
If obj is a DFS table with duplicated “keys” (as specified by keyColNames),
upsert!on rows with duplicated keys only updates the first row.
Examples
upsert! a keyed table:
$ sym=`A`B`C
$ date=take(2021.01.06, 3)
$ x=1 2 3
$ y=5 6 7
$ t=keyedTable(`sym`date, sym, date, x, y)
$ t;
sym |
date |
x |
y |
|---|---|---|---|
A |
2021.01.06 |
1 |
5 |
B |
2021.01.06 |
2 |
6 |
C |
2021.01.06 |
3 |
7 |
$ newData = table(`A`B`C`D as sym, take(2021.01.06, 4) as date, NULL NULL 300 400 as x, NULL 600 700 800 as y);
$ newData;
sym |
date |
x |
y |
|---|---|---|---|
A |
2021.01.06 |
||
B |
2021.01.06 |
600 |
|
C |
2021.01.06 |
300 |
700 |
D |
2021.01.06 |
400 |
800 |
$ upsert!(t, newData, ignoreNull=true)
$ t;
sym |
date |
x |
y |
|---|---|---|---|
A |
2021.01.06 |
1 |
5 |
B |
2021.01.06 |
2 |
600 |
C |
2021.01.06 |
300 |
700 |
D |
2021.01.06 |
400 |
800 |
The example above is for the case when ignoreNull = true. Compare it with the following example where ignoreNull = false (by default).
$ sym=`A`B`C
$ date=take(2021.01.06, 3)
$ x=1 2 3
$ y=5 6 7
$ t=keyedTable(`sym`date, sym, date, x, y)
$ upsert!(t, newData)
$ t;
sym |
date |
x |
y |
|---|---|---|---|
A |
2021.01.06 |
||
B |
2021.01.06 |
600 |
|
C |
2021.01.06 |
300 |
700 |
D |
2021.01.06 |
400 |
800 |
upsert! a DFS table:
$ ID=0 1 2 2
$ x=0.1*0..3
$ t=table(ID, x)
$ db=database("dfs://rangedb128", VALUE, 0..10)
$ pt=db.createPartitionedTable(t, `pt, `ID)
$ pt.append!(t)
$ select * from pt;
ID |
x |
|---|---|
0 |
0 |
1 |
0.1 |
2 |
0.2 |
2 |
0.3 |
$ t1=table(1 as ID, 111 as x)
$ upsert!(pt, t1, keyColNames=`ID)
$ select * from pt;
ID |
x |
|---|---|
0 |
0 |
1 |
111 |
2 |
0.2 |
2 |
0.3 |
$ t1=table(2 as ID, 222 as x)
$ upsert!(pt, t1, keyColNames=`ID)
$ select * from pt;
ID |
x |
|---|---|
0 |
0 |
1 |
111 |
2 |
222 |
2 |
0.3 |
Use upsert! to update a DFS table. If ignoreNull = true, the records in the target table corresponding to the NULL value of the new data will not be updated.
$ if(existsDatabase("dfs://valuedemo")) {
$ dropDatabase("dfs://valuedemo")
$ }
$ db = database("dfs://valuedemo", VALUE, 1..10)
$ t = table(take(1..10, 100) as id, 1..100 as id2, 100..1 as value)
$ pt = db.createPartitionedTable(t, "pt", `id).append!(t)
$ t2 = table( 1 2 as id, 1 2 as id2, 1 NULL as value)
$ upsert!(pt, t2, true, "id2")
$ if(existsDatabase("dfs://upsert")) {
$ dropDatabase("dfs://upsert")
$ }
$ sym=`A`B`C`A`D`B`A
$ date=take(2021.12.10,3) join take(2021.12.09, 3) join 2021.12.10
$ price=8.3 7.2 3.7 4.5 6.3 8.4 7.6
$ val=10 19 13 9 19 16 10
$ t=table(sym, date, price, val)
$ db=database("dfs://upsert", VALUE, `A`B`C)
$ pt=db.createPartitionedTable(t, `pt, `sym)
$ pt.append!(t)
$ t1=table(`A`B`E as sym, take(2021.12.09, 3) as date, 11.1 10.5 6.9 as price, 12 9 11 as val)
$ upsert!(pt, t1, keyColNames=`sym, sortColumns=`date`val)
$ select * from pt
sym |
date |
price |
val |
|---|---|---|---|
A |
2021.12.09 |
4.5 |
9 |
A |
2021.12.09 |
11.1 |
12 |
A |
2021.12.10 |
7.6 |
10 |
B |
2021.12.09 |
10.5 |
9 |
B |
2021.12.09 |
8.4 |
16 |
C |
2021.12.10 |
3.7 |
13 |
D |
2021.12.09 |
6.3 |
19 |
E |
2021.12.09 |
6.9 |
11 |