addColumn
Syntax
addColumn(table, colNames, colTypes)
Arguments
table can be a table of any type, including an in-memory table, a stream table, a DFS table, or a dimension table.
colNames is a STRING scalar/vector indicating the name(s) of the column(s) to be added.
colTypes is a scalar/vector indicating the data type(s) of the column(s) to be added.
Details
Add a column or columns to a table. It is the only way to add a column to a stream
table, a DFS table, or a dimension table. SQL update
statement is
not supported for these cases.
Note that the table should be updated by function loadTable
after
adding a new column into a DFS table or dimension table with
addColumn
.
Examples
Example 1. Add columns to a DFS table.
ID=1..6
x=1..6\5
t1=table(ID, x)
db=database("dfs://rangedb",RANGE, 1 4 7)
pt = db.createPartitionedTable(t1, `pt, `ID)
pt.append!(t1);
addColumn(pt,["price", "qty"],[DOUBLE, INT]);
After using addColumn
and before data with the new schema is
inserted, we can still insert data with the old schema.
t2=table(1 as ID, 1.2 as x)
pt.append!(t2)
select * from pt;
ID | x | price | qty |
---|---|---|---|
1 | 0.2 | ||
2 | 0.4 | ||
3 | 0.6 | ||
1 | 1.2 | ||
4 | 0.8 | ||
5 | 1 | ||
6 | 1.2 |
After data with the new schema is inserted, however, data with the old schema can no longer be inserted.
t3=table(1 as ID, 1.6 as x, 10.0 as price, 6 as qty)
pt.append!(t3)
select * from pt;
ID | x | price | qty |
---|---|---|---|
1 | 0.2 | ||
2 | 0.4 | ||
3 | 0.6 | ||
1 | 1.2 | ||
1 | 1.6 | 10 | 6 |
4 | 0.8 | ||
5 | 1 | ||
6 | 1.2 |
t4=table(2 as ID, 2.2 as x)
pt.append!(t4); // The data to append contains fewer columns than the schema.
Example 2. Add columns to a stream table.
n=10
ticker = rand(`MSFT`GOOG`FB`ORCL`IBM,n)
x=rand(1.0, n)
t=streamTable(ticker, x)
share t as st
addColumn(st,["price", "qty"],[DOUBLE, INT])
insert into st values("MSFT",12.0,25.46,256)
select * from st;
ticker | x | price | qty |
---|---|---|---|
MSFT | 0.743241031421349 | ||
FB | 0.254624255700037 | ||
FB | 0.947473830310628 | ||
FB | 0.904140035156161 | ||
MSFT | 0.193251194199547 | ||
MSFT | 0.416090324753895 | ||
MSFT | 0.479371337918565 | ||
ORCL | 0.69910929678008 | ||
GOOG | 0.131539688445628 | ||
MSFT | 0.472390263108537 | ||
MSFT | 12 | 25.46 | 256 |