AutoFitTableUpsert

Syntax

AutoFitTableUpsert(String dbUrl, String tableName, DBConnection connection, boolean ignoreNull, String[] pkeyColNames, String[] psortColumns)

Parameters

• dbUrl: The DFS database path. An empty string indicates an in-memory table.
• tableName: The DFS table.
• connection: A DBConnection object.
• ignoreNull: Whether to update the target table's data when the new data contains NULL values.
• pkeyColNames: The key columns of the DFS table (target table).
• psortColumns: The sort columns by which the data within each partition should be sorted during the update.

Call the method upsert to update the target table with a BasicTable object. 0 is returned if data is successfully written.

int upsert(BasicTable table)

Example 1

Generate a DFS table and then use AutoFitTableUpsert to update the table, setting pkeyColNames to id as the key column. Retrieve the updated table and print it to the console.

@Test
public void testAutoFitTableUpsert() {
    DBConnection conn = new DBConnection(false, false, false);
    conn.connect("192.168.1.116", 18999, "admin", "123456");
    String dbName ="dfs://upsertTable";
    String tableName = "pt";
    String script = "dbName = \"dfs://upsertTable\"\n"+
            "if(exists(dbName)){\n"+
            "\tdropDatabase(dbName)\t\n"+
            "}\n"+
            "db  = database(dbName, RANGE,1 10000,,'TSDB')\n"+
            "t = table(1000:0, `id`value,[ INT, INT[]])\n"+
            "pt = db.createPartitionedTable(t,`pt,`id,,`id)";
    conn.run(script);

    BasicIntVector v1 = new BasicIntVector(3);
    v1.setInt(0, 1);
    v1.setInt(1, 100);
    v1.setInt(2, 9999);

    BasicArrayVector ba = new BasicArrayVector(Entity.DATA_TYPE.DT_INT_ARRAY, 1);
    ba.Append(v1);
    ba.Append(v1);
    ba.Append(v1);

    List<String> colNames = new ArrayList<>();
    colNames.add("id");
    colNames.add("value");
    List<Vector> cols = new ArrayList<>();
    cols.add(v1);
    cols.add(ba);
    BasicTable bt = new BasicTable(colNames, cols);
    String[] keyColName = new String[]{"id"};
    AutoFitTableUpsert aftu = new AutoFitTableUpsert(dbName, tableName, conn, false, keyColName, null);
    aftu.upsert(bt);
    BasicTable res = (BasicTable) conn.run("select * from pt;");
    System.out.println(res.getString());
}

Execution result:

id   value       
---- ------------
1    [1,100,9999]
100  [1,100,9999]
9999 [1,100,9999]

Example 2

Generate a DFS table and then use AutoFitTableUpsert to update the table, setting pkeyColNames to sym as the key column and psortColumns to date and val as the sorting columns. Retrieve the updated table and print it to the console.

@Test
public void test_tableUpsert_DP_sortColumns() throws Exception {
    DBConnection conn = new DBConnection();
    conn.connect("localhost", 8848, "admin", "123456");
    String script = "if(existsDatabase(\"dfs://upsert\")) {\n" +
            "dropDatabase(\"dfs://upsert\")\n" +
            "}\n" +
            "sym=`A`B`C`A`D`B`A\n" +
            "date=take(2021.12.10,3) join take(2021.12.09, 3) join 2021.12.10\n" +
            "price=8.3 7.2 3.7 4.5 6.3 8.4 7.6\n" +
            "val=10 19 13 9 19 16 10\n" +
            "t=table(sym, date, price, val)\n" +
            "db=database(\"dfs://upsert\", VALUE,  `A`B`C)\n" +
            "pt=db.createPartitionedTable(t, `pt, `sym)\n" +
            "pt.append!(t)";
    conn.run(script);
    BasicTable bt = (BasicTable) conn.run("t1=table(`A`B`E as sym, take(2021.12.11, 3) as date, 11.1 10.5 6.9 as price, 12 9 11 as val);t1;");
    String[] keyColName = new String[]{"sym"};
    String[] psortColumns = new String[]{"date","val"};
    AutoFitTableUpsert aftu = new AutoFitTableUpsert("dfs://upsert","pt",conn,false,keyColName,psortColumns);
    aftu.upsert(bt);
    BasicTable ua = (BasicTable) conn.run("select * from pt");
    System.out.println(ua.getString());
}

Execution result:

sym date       price val
--- ---------- ----- ---
A   2021.12.09 4.5   9  
A   2021.12.10 7.6   10 
A   2021.12.11 11.1  12 
B   2021.12.09 8.4   16 
B   2021.12.11 10.5  9  
C   2021.12.10 3.7   13 
D   2021.12.09 6.3   19 
E   2021.12.11 6.9   11