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)