loadTableBySQL
Syntax
loadTableBySQL(sql)
Arguments
sql is a metacode object representing a SQL query. It can use
where
clause to filter partitions or rows and use
SELECT
statement to select columns including calculated
columns. However, it cannot use TOP
clause, GROUP
BY
clause, ORDER BY
clause, CONTEXT
BY
, and LIMIT
.
Details
Load only the rows of a partitioned table that satisfy the filtering conditions in a SQL query to memory. The result is a partitioned in-memory table with the same partitioning scheme as the database on disk.
Note: In scenarios involving large-scale data queries, this
function may result in high memory usage. In such cases, it is recommended to use
SQL statements directly for querying instead.
Examples
n=1000000
t=table(rand('A'..'Z',n) as sym, 2000.01.01+rand(365,n) as date, 10.0+rand(2.0,n) as price1, 100.0+rand(20.0,n) as price2, rand(10,n) as qty1, rand(100,n) as qty2)
db = database("dfs://tradeDB", VALUE, 'A'..'Z')
trades=db.createPartitionedTable(t,`trades,`sym).append!(t)
sample=select * from loadTableBySQL(<select * from trades where date between 2000.03.01 : 2000.05.01>)
sample=select * from loadTableBySQL(<select sym, date, price1, qty1 from trades where date between 2000.03.01 : 2000.05.01>)
dates = 2000.01.16 2000.02.14 2000.08.01
st = sql(<select sym, date, price1, qty1>, trades, expr(<date>, in, dates))
sample = select * from loadTableBySQL(st)
colNames =`sym`date`qty2`price2
st= sql(sqlCol(colNames), trades)
sample = select * from loadTableBySQL(st)