AutoFitTableUpsert

The AutoFitTableUpsert (AFTU) is similar to AFTA but serves as the "upsert" version. When creating an AFTU object, you need to specify a column name. During data insertion, if the value in the specified column does not exist in the database, AFTU directly inserts the data. If the value in the specified column already exists, AFTU updates the existing data instead of inserting a new row. AFTU is better suited for scenarios involving writing duplicate data.

Constructing an AFTU

The constructor declaration is as follows:

AutoFitTableUpsert(string dbUrl, string tableName, DBConnection& conn,
                   bool ignoreNull=false,
                   vector<string> *pkeyColNames=nullptr,
                   vector<string> *psortColumns=nullptr);

Arguments

dbUrl: The path of a DFS database folder. Specify it as an empty string for an in-memory table.
tableName: The table name.
DBConnection: A connected DBConnection object.
ignoreNull: Whether to update existing values in the target table with NULL values from the new data being inserted or updated. The default value is false.
pkeyColNames: The name of the column that serves as the key column.
psortColumns: The updated partitions will be sorted on psortColumns (only within each partition, not across partitions).

For detailed usage of arguments, refer to upsert!.

`upsert`

You can use the upsert function to insert a table to the AFTU object. It will not return until the AFTU has finished writing all the data.

The function declaration is as follows:

int upsert(TableSP table);

Arguments

table: The table to be inserted.

Return Values

The number of rows that have been inserted.

Examples

(1) Create a DFS table with the following script:

dbPath = "dfs://SAMPLE_TRDDB";
tableName = `demoTable
if(existsDatabase(dbPath)){
	dropDatabase(dbPath)
}
db = database(dbPath, VALUE, 2010.01.01..2010.01.30)
pt=db.createPartitionedTable(table(1000000:0, `name`date`price, [STRING,DATE,DOUBLE]), tableName, `date)

(2) Use AFTU to insert a table to the DFS table "demoTable".

TableSP createDemoTable(){
    vector<string> colNames = {"name", "date","price"};
    vector<DATA_TYPE> colTypes = {DT_STRING, DT_DATE, DT_DOUBLE};
    int colNum = 3, rowNum = 10, indexCapacity = 10;
    ConstantSP table = Util::createTable(colNames, colTypes, rowNum, indexCapacity);
    vector<VectorSP> columnVecs;
    for(int i = 0; i < colNum; ++i)
        columnVecs.push_back(table->getColumn(i));

    for(int i = 0; i < rowNum; ++i){
        columnVecs[0]->set(i, Util::createString("name_"+std::to_string(i)));
        columnVecs[1]->set(i, Util::createDate(2010, 1, i+1));
        columnVecs[2]->set(i, Util::createDouble((rand()%100)/3.0));
    }
    return table;
}

int main(int argc, const char **argv)
{
    DBConnection conn;
    conn.connect("127.0.0.1", 8848, "admin", "123456");
    std::vector<std::string> keyNames{"name"};
    AutoFitTableUpsert upserter("dfs://SAMPLE_TRDDB", "demoTable", conn, false, &keyNames);
    TableSP table = createDemoTable();
    upserter.upsert(table);
    ConstantSP result = conn.run("select * from loadTable('dfs://SAMPLE_TRDDB', `demoTable)");
    std::cout <<  result->getString() << std::cout;
}