December 2011 - users-list

[MonetDB-users] Benefits of sorted columns
by Viktor Rosenfeld 19 Dec '11

19 Dec '11

Hi, I know that when a table is sorted on a column, value look-ups on that column will use a binary search instead of a linear search. Is there a similar benefit for joins? E.g. if I have 2 tables that contain a sorted column C and I do an equality join on this column (or a self-join using 2 aliases of the same table), I would expect a traditional DBMS to do a merge-join. Cheers, Viktor

2 2

[MonetDB-users] SQLException:SQLengine:Program contains errors
by Charalampos Nikolaou 17 Dec '11

17 Dec '11

Hi, I am getting the error "SQLException:SQLengine:Program contains errors" when I define a simple function that returns a table. The SQL reference on the site of MonetDB (http://www.monetdb.org/Documentation/SQLreference/Flowofcontrol) states that the accepted expression to a "TABLE" expression should be "select_no_parens_orderby" which is not defined in any place (or haven't found it) other than sql_parser.y. The function I define is the following and apart from the fact that is inserted successfully into the "functions" table, I checked that it can be produced by the grammar in sql_parser.y: CREATE FUNCTION refine () RETURNS TABLE (u varchar(32), v varchar(32), r varchar(5)) BEGIN RETURN TABLE ( ( SELECT R.u, R.v, R.r FROM R INTERSECT SELECT R.u, R.v, R.r FROM R ) ); END; Further, what is the proper way to deleting a function, in order to re-define it in the same session? I observed that is not sufficient to just delete it from sys.functions table. An entry #function_name is also present in sys._tables. Thanks, Babis

3 5

Re: [MonetDB-users] Information request
by Ying Zhang 16 Dec '11

16 Dec '11

On Fri, Dec 16, 2011 at 10:51:18PM +0800, dj zhang wrote: > Hello Jennie, > > Glad to hear from you! Thanks for your reply! I'm sorry that I didn't > realize that you guys are in a new year vacation. Hello Dongjie, I'd like to recommend you to subscribe to the MonetDB users mailling list: http://www.monetdb.org/Developers/Mailinglists , and sent your feature questions to the mailinglist. This way, your questions will reach the whole developer team at one, and you'll most probably get answers faster. I felt free to forward this e-mail to the mailing list. > Maybe I didn't express myself very clear. I've generated a table file in > which each line represents a record, and the fields of a record are > separated by "|", e.g. "abc|1.2.3.3|www.monetdb.org|3". I can bulk load > this file to MonetDB by SQL COPY INTO command. Unfortunately, you can't load this table using the binary bulk load feature of MonetDB, since this feature only handles numerical data, and doesn't not work with strings. How large (in GBs) is your total dataset? Maybe you don't have to use the binary bulk load feature... > Now I want to change the table file to column files, each file just > contains one column's values which also stored line by line. I want to know > if I can load this table by loading these column files. > > From your information, Can I say that I have to change these column files > to binary format that MonetDB recognise(BAT storage), then use binary bulk > load command to load the table to MonetDB. And if I change the files to > other binary format it will not work. Is that right? That's right. MonetDB just works with one binary format. Regards, Jennie > > Thank you so much! Once again happy new year! > > Best wishes, > > Dongjie > ------------------------------------- > > On Fri, Dec 16, 2011 at 8:55 PM, Ying Zhang <Y.Zhang(a)cwi.nl> wrote: > > > Hello Zhang Dongjie, > > > > First of all, thank you very much for your interest in MonetDB. > > > > > I have learn from the website document that I can load data use column > > > files when migrate between MonetDB instances, but the file has to be in > > the > > > format of binary version of the BAT storage if I didn't misunderstand. > > > > If each column of a table is already stored in binary format in a file, > > the table can be loaded using the "binary bulk load" feature of MonetDB, > > as described here: > > http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/BinaryBulkLoad > > > > If you are migrating your existing database to an exactly the same > > machine, you can just copy dbfarm sub-directory for your database. If > > you are migrating to a machine with different hardware, you need to dump > > the database and reload it on the destination machine, as described here: > > http://www.monetdb.org/Documentation/UserGuide/DumpRestore > > However, in this case, the dumped data can not be loaded using the > > binary bulk load feature, because the file containing dumpted data is an > > ASCII file. > > > > > So my question is, is there any way to load data to monetdb by column as > > > long as the record is store by column. Or do I have to first change the > > > column files to the binary version of BAT format then use the Binary Bulk > > > Load commands. > > > > You can only use the binary bulk load feature, iff the data of each > > column is stored in a separate file in binary format. Hence, yes, > > you'll have to generate those binary files, if you don't have them yet. > > However, if your data is already in, e.g., CSV format, you don't have to > > create binary files for the columns, since you can just load the CSV > > file. If your (CSV) data is not too large, the standard SQL COPY INTO > > feature should just work. > > > > > And if I have to change the format, is there any way to use google > > protobuf > > > to serialize the column file to the BAT format, or is there any solutions > > > like handlersocket for mysql? > > > > I'm unfortunately not familiar with protobuf and handlersocket. Maybe > > someone else in our group can answer these questions. > > > > Hope this helps a bit. > > > > > > With kind regads, > > > > Jennie Zhang > > > > > > > > > > By the way I think MonetDB is a great project and I wish it can find it's > > > way to commercialize very soon! > > > > > > Thank you very much, and Merry Christmas! > > > > > > Best regards, > > > > > > Dongjie > > > ------------------ > > > Dongjie Zhang > > > > > > Services Computing Technology and System Lab, > > > Cluster and Grid Computing Lab, > > > School of Computer Science and Technology, > > > Huazhong University of Science and Technology, > > > Wuhan, 430074, China > > > > > > 华中科技大学计算机科学与技术学院 > > > 服务计算技术与系统教育部重点实验室集群与网格计算湖北省重点实验室 > > > 湖北武汉 430074 > > > > > _______________________________________________ > > > Info mailing list > > > Info(a)monetdb.org > > > http://mail.monetdb.org/mailman/listinfo/info > > > > > > > -- > ------------------ > Dongjie Zhang > > Services Computing Technology and System Lab, > Cluster and Grid Computing Lab, > School of Computer Science and Technology, > Huazhong University of Science and Technology, > Wuhan, 430074, China > > 华中科技大学计算机科学与技术学院 > 服务计算技术与系统教育部重点实验室集群与网格计算湖北省重点实验室 > 湖北武汉 430074

1 0

[MonetDB-users] Invalid BAT if left join condition has only columns from right table
by George Garbis 15 Dec '11

15 Dec '11

Hello to all, a left join which have only right table columns in join condition seems to produce invalid bat. Assume we have the tables below: CREATE TABLE tableA(x integer, y integer); INSERT INTO tableA values(1,10); INSERT INTO tableA values(2,20); CREATE TABLE tableB(x integer, y integer); INSERT INTO tableB values(1,-10); INSERT INTO tableB values(2,-20); and pose the query: SELECT A.x, A.y, B.x, B.y FROM tableA A LEFT JOIN tableB B ON ( B.y < -10 ) ; the expected results are: +------+----- +------+------+ | x | y | x | y | +===+===+===+===+ | 1 | 10 | 2 | -20 | | 2 | 20 | 2 | -20 | +------+------+------+------+ but mclient prints this message: TypeException:user.s1_1[36]:'algebra.kdifference' undefined in: _55:any := algebra.kdifference(_54:bat[:oid,:int], _44:bat[:void,:oid]) TypeException:user.s1_1[37]:'algebra.markT' undefined in: _56:any := algebra.markT(_55:any, _21:oid) TypeException:user.s1_1[38]:'bat.reverse' undefined in: _57:any := bat.reverse(_56:any) TypeException:user.s1_1[39]:'algebra.join' undefined in: _58:any := algebra.join(_57:any, _20:bat[:oid,:int]) TypeException:user.s1_1[41]:'algebra.leftjoin' undefined in: _60:any := algebra.leftjoin(_59:bat[:oid,:void], _54:bat[:oid,:int]) TypeException:user.s1_1[43]:'bat.append' undefined in: _64:any := bat.append(_61:bat[:oid,:int], _60:any, _65:bit) TypeException:user.s1_1[44]:'bat.append' undefined in: _66:any := bat.append(_64:any, _58:any, _65:bit) TypeException:user.s1_1[55]:'algebra.join' undefined in: _80:any := algebra.join(_57:any, _79:bat[:oid,:int]) TypeException:user.s1_1[61]:'algebra.leftjoin' undefined in: _90:any := algebra.leftjoin(_59:bat[:oid,:void], _89:bat[:oid,:int]) TypeException:user.s1_1[63]:'bat.append' undefined in: _92:any := bat.append(_91:bat[:oid,:int], _90:any, _65:bit) TypeException:user.s1_1[64]:'bat.append' undefined in: _93:any := bat.append(_92:any, _80:any, _65:bit) TypeException:user.s1_1[70]:'algebra.project' undefined in: _102:any := algebra.project(_57:any, _100:int) TypeException:user.s1_1[83]:'bat.append' undefined in: _119:any := bat.append(_117:bat[:oid,:int], _102:any, _118:bit) TypeException:user.s1_1[89]:'algebra.project' undefined in: _126:any := algebra.project(_57:any, _125:int) TypeException:user.s1_1[96]:'bat.append' undefined in: _133:any := bat.append(_132:bat[:oid,:int], _126:any, _118:bit) SQLException:SQLengine:Program contains errors For ease I attach sql scripts for creating tables and posing the query Best regards, George Garbis

2 1

[MonetDB-users] ODBC Stops inserting
by Joseph Brower 13 Dec '11

13 Dec '11

Hello everyone! We're using the odbc driver to insert records every half second or so. These records are for analytics information about visitors to various websites that we host. (We found MonetDB to be very fast for fetching information like average request time, number of visitors, general analytics information like that.) We've run into a few issues though. The biggest concern is that we stop seeing new records entered after a while. The log shows 2011-12-13 17:00:00 MSG merovingian[86]: proxying client 10.60.5.13:37359 for database 'web_requests' to mapi:monetdb:///var/lib/monetdb/web_requests/web_requests/.mapi.sock?database=web_requests 2011-12-13 17:00:00 MSG merovingian[86]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying over and over for each machine that would be inserting these records. But no new records are ever added. Another concern is that in our installation, we don't see a MonetDB.conf file. I was hoping that we could specify how much RAM we'd like to allow MonetDB to consume. This is to ensure that our memory manager doesn't kill MonetDB, since that's the primary role of this server. Is this a file that we should create? I'm still fairly new to MonetDB and as a result, am still trying to find my way with it. The last concern I have is the log file size. Is there a way (once we've ironed out the before mentioned issues) to instruct MonetDB to only log things of a certain importance or higher? For example, to only log ERR rather than ERR and MSG messages? Our log files will become quite large and unmanageable if we don't disable or implement some sort of log rotation system. The version of MonetDB that we are running is MonetDB 5 server v11.5.7 "Aug2011-SP2". We are running under Ubuntu 10.04 64Bit. Thanks, Joseph Brower

2 3

[MonetDB-users] SpagoBI
by Jeremy OSullivan 09 Dec '11

09 Dec '11

Hello,We are trying to get SpagoBI to work with MonetDB. SpagoBI uses a JDBC interface. The problem is as follows: The import of the schema works fine. However, when you save the model in SpagoBI, the schema in MonetDB can no longer be viewed. We have tried this with MySQL and it works fine.Regards,Jeremy

1 0

[MonetDB-users] Can't create a csv vault?
by Mark Aufflick 08 Dec '11

08 Dec '11

Hi all, I'm very interested in the vault feature of monetdb, both for csv and for creating my own formats. Trouble is I can't make a simple example work. I've flattended the voc sample data into a csv file, and then try to load it so: create table test (type varchar(255), number integer, number_sup varchar(255), trip integer, trip_sup varchar(255), onboard_at_departure integer, death_at_cap integer, left_at_cape integer, onboard_at_cape integer, death_during_voyage integer, onboard_at_arrival integer) external csv '/tmp/all.csv'; But I get an error: syntax error, unexpected EXTERNAL, expecting SCOLON in: ... Do I need to enable vaults at configure time or create time or something? I didn't see an option for it. I built from the "Latest" tarball on OSX Lion. Cheers, Mark.

2 3

[MonetDB-users] stethoscope on OS X can't connect to database
by Viktor Rosenfeld 07 Dec '11

07 Dec '11

Hi, when I try to connect to a database with stethoscope I get an error: $ stethoscope -d annis_test -u annis_test -P ... +tis stethoscope: no databases found for 'annis_test' I'm using the Aug2011-SP3 release on OS X 10.6.8. The same command works under Linux. The instances are set up identically. Cheers, Viktor

2 2