replaceColumn!

replaceColumn!(table, colName, newCol)

table is a non-shared in-memory table or a DFS table.

colName is a string indicating the name of the column to replace. When table is an in-memory table, colName can also be a vector of strings indicating multiple column names.

• When table is an in-memory table:
  • replaceColumn! replaces table column(s) with the specified vector(s). The data type of the new column is the same as the data type of the specified vector. It supports replacing multiple columns at once. Note that the multi-column replacement operation is not atomic, which means in cases of system errors, some specified column replacements may succeed while others fail partway through.
  • To update column's value without changing its data type, we can use both replaceColumn! and SQL statement update. Only replaceColumn!, however, can change a column's data type.
• When table is a DFS table in the OLAP engine, replaceColumn! only modifies the data type of the specified column(s). Note that:
  • The SYMBOL type cannot be converted to or from other types.
  • Except for SYMBOL, data types within the same category can be converted between each other.
  • INTEGRAL and FLOATING types can be converted between each other.
  • INTEGRAL, FLOATING, LITERAL, and TEMPORAL types can be converted to the STRING type.

sym = `C`MS`MS`MS`IBM`IBM`C`C`C
price= 49.6 29.46 29.52 30.02 174.97 175.23 50.76 50.32 51.29
qty = 2200 1900 2100 3200 6800 5400 1300 2500 8800
timestamp = [09:34:07,09:36:42,09:36:51,09:36:59,09:32:47,09:35:26,09:34:16,09:34:26,09:38:12]
t = table(timestamp, sym, qty, price)
schema(t).colDefs;

To change the data type of column "sym" to SYMBOL:

syms=symbol(exec sym from t)
replaceColumn!(t,`sym,syms);
schema(t).colDefs;

To replace columns "price" and "timestamp":

newPrice =round(t.qty)
newTimestamp = minute(t.timestamp)
replaceColumn!(t, `price`timestamp, (newPrice,newTimestamp))
schema(t).colDefs;

login("admin","123456")
if(existsDatabase("dfs://replaceColumn")){
  dropDatabase("dfs://replaceColumn")
}
n=10
month=take(2012.06.13..2012.06.13, n);
x=rand(1.0, n);
t=table(month, x);
db=database("dfs://replaceColumn", VALUE, 2012.06.13..2012.06.23)
pt = db.createPartitionedTable(t, `pt, `month);
pt.append!(t);
schema(pt).colDefs

newCols=int(exec x from loadTable("dfs://replaceColumn",`pt))
replaceColumn!(loadTable("dfs://replaceColumn",`pt), `x, newCols)
schema(pt).colDefs