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, or order by
clause.
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.
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)