Thank you Simon. Your solution would work for the example i gave.
Nevertheless my problem is still more complex because i also use SQLite
as a generic streaming engine (yes i know, SQLite wasn't designed for
doing things like that).
Appart from input VTs (FILE) we also have output VTs like so [*]:
OUTPUT '10.0.0.1:8080' select upper(c1), c2, lower(c3) from
(
select * from file('http://www.foo.com/list1.tsv.gz')
union all
select * from file('http://www.foo.com/list2.tsv.gz')
)
where c2!=c4;
Internally this is implemented as a VT (OUTPUT) that takes as input a
query and sends its results to another SQLite. The beauty of the concept
is that a streaming query flow can go from machine to machine and still
the transactional properties hold. If anything happens anywhere in the
query's path, all of the machines will rollback due to the transactional
properties of SQLite.
In addition to above we are developing a parallel processing engine
(like Hadoop) where we use SQLite "chunks" for processing and data
storage. So tables are split (or collected) into multiple SQLite DBs and
transferred to multiple machines, where the queries will be executed in
parallel [**].
For above we heavilly use UNION ALLs over the attached DBs to scan over
the many chunks of a table.
A UNION ALL that unnecessarily buffers to the disk its inputs (for a
single scan over them), is very nasty, performance wise, to all of the
above machinations.
I can guess that changing UNION ALL processing for single scans, isn't
so easy, nevertheless it would be nice if the /var/tmp buffering
directory could be at least changed.
lefteris.
[*] In practise we pipe to named pipes that point to other machines.
[**] Other projects based on the same idea are:
http://hadoopdb.sourceforge.net/
and the company that sprang from above:
http://hadapt.com/
On 27/10/2012 3:05 πμ, Simon Slavin wrote:
Does this work instead ?
CREATE TABLE t ([[whatever columns you want]]);
INSERT INTO t SELECT upper(c1),c2,lower(c3) FROM
file('http://www.foo.com/list1.tsv.gz') WHERE c2!=c4;
INSERT INTO t SELECT upper(c1),c2,lower(c3) FROM
file('http://www.foo.com/list2.tsv.gz') WHERE c2!=c4;
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
