- users-list - monetdb.org

regular expression - substring
by Budulinku Dejmihrasku 27 Sep '13

27 Sep '13

Hi, i am looking for any idea/suggestion how to do something like Oracle has, i.e. REGEXP_SUBSTR(source, pattern[, position [, occurrence[, match_parameter]]]) which returns the desired substring. why i need this: i have vast amount of data to parse - i.e. i need to get all of the many possible [parameter=value] pairs from an url access log in order to feed the dimension tables w/parameter-value from the URL by parsing the string directly in MonetDB when i write a SQL function to do this (or any string parsing as a matter of fact), it is rather slow when parsing millions of records. i know that there is the PCRE.MAL and i already use with some success functions INDEX, MATCH or REPLACE but i was not able to find anything similar to the REGEXP_SUBSTR functionality to get the desired substring in one go would most likely help me to get better performance any suggestion? please? thanks milan

2 1

Stopping queries, deleting tuples and other general use questions
by Ruben Silva 27 Sep '13

27 Sep '13

Hi, after a while using MonetDB some general use questions pops up. I've collected some which answers I think will be of great help for any MonetDB user. * * *#1:* In the monetdb manpage we can read: *“The kill command immediately sends a SIGKILL and should only be used as last resort for a database that doesn't respond any more. Killing a database may result in (partial) data loss.”* * * 1.1 If kill is the last resort, what are the other options? * * *#2:* System monitor (Oct 2013)<http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/monitor> 2.1 Will this be available in October or is there a new estimate? 2.2 Does the current development version include this feature yet so we can test it? I'm interested mainly in the methods sys.pause(qtag), sys.resume(qtag) and sys.stop(qtag), *specially sys.stop(qtag)*. 2.4 Is there some alternative method already available in the Feb2013 release to achieve this action? *#3:* In the monetdb manpage we can read about the *nclients* property: *“Sets the maximum amount of clients that can connect to this database at the same time. Setting this to a high value is discouraged.” * 3.1 What do you consider as a high value? Fabian Groffen stated in a similar question *"Concurrency is always a problem for MonetDB, since in the ideal case it means the processes are fighting for resources with each other." * 3.2 Does this mean (theoretically) that if I can run Query A in 10s in a system with resources R (Ram, Disk, Processor), with Rx2 I can run Query A and B (equivalent in effort to A) in the same 10s? *#4:* www.monetdb.org/Documentation/Manuals/SQLreference/Transactions: *"WARNING. The tuples being deleted are only marked as such. They do not reduce the table size. It calls for a vacuum cleaning algorithm."* * * I've found the /usr/lib/monetdb5/createdb/20_vacuum.sql script and after some googling I found the following post* * http://mail.monetdb.org/pipermail/users-list/2011-August/005051.html without an answer. 4.1 Does the following sequence frees the space of the deleted rows? sql>delete from sys.transfer_ip ; 651950 affected rows (117.620ms) sql>call vacuum('sys','transfer_ip'); Best regards, Ruben Silva

2 1

Create Sorted View in monetdb
by Khaled Dehdouh 27 Sep '13

27 Sep '13

Please can you help me. I want to create a view sorted according to attributes like Oracle, my query is as follows: *Create view V1 as Select Name, First_Name, Age from **People O**redr by Name**;* but Monetdb shows me the following error "CREATE VIEW: ORDER BY not supported." How should I do to create a sorted view ?.

2 1

Announcement: New Feb2013-SP4 Bugfix release of MonetDB suite
by Sjoerd Mullender 27 Sep '13

27 Sep '13

The MonetDB team at CWI/MonetDB BV is pleased to announce the Feb2013-SP4 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Feb 2013-SP4 bugfix release Merovingian * monetdb now no longer compresses output when not connected to a terminal, bug #3292 Bug Fixes * 2781: SQL localtime() returns GMT time * 2962: client timezone information needs to be handled also for modifications to timestamp values * 3232: The function weekofyear returns results not according to the iso 8601 standard * 3255: M5 tests inlineFunction & ifthencst fail since changeset 207ed7d7e03a * 3292: monetdb status should print full line * 3307: distinct multicolumn performance and implementation suggestion for count distinct * 3310: Cast value to integer gives wrong result * 3311: strLength assertion on join and concat * 3313: Assertion in rel2bin_project * 3315: Incorrect behavior with subqueries in DELETE * 3317: correlated subselect does not work * 3318: UPDATE does not work with DECLEAREd TABLE in FUNCTION * 3319: UNIQUE column in DECLAREd TABLE in FUNCTION results in segfault * 3329: Function AVG fails during runtime in a procedure * 3330: Corrupt mitosis plan with group-by query * 3332: MEDIAN: no such operator 'median(decimal)' * 3336: DB files not removed if all rows are deleted, even after restart * 3337: Monet ODBC gives wrong schema information * 3339: Segvault in complex function call * 3340: crash on seeming incorrect SQL query * 3341: segfault caused by select rank() on a newly-loaded table * 3342: incorrect automatic cast from decimal to tinyint * 3345: copy binary into fails with the wrong error message * 3346: Deadlock in mal_dataflow's q_dequeue() (SciQL-2 branch) * 3348: SQL: LIKE incorrect with _ * 3349: When selecting everything from a joined result, column contents are incorrect in one of my use cases. * 3351: gzip'ed files cannot be bulkloaded into DB * 3352: Median function with decimal type doesn't work * 3353: Readonly tables call for UNSET * 3354: Introduce query time-out * 3358: Remote client connection stays in CLOSE_WAIT state * 3360: uninitialized memory causing bad output in query plans * 3362: ALTER TABLE operations silently remove previously set READ ONLY property * 3363: a join causes SIGSEGV after creation of Unique Key * 3367: Fully qualified order by column gives "relational query without result" * 3369: \0 Byte appears in MAPI

1 0

Re: Contents of users-list Digest, Vol 13, Issue 20
by Edgar Mejia 25 Sep '13

25 Sep '13

1. I have MonetDB Database Server Toolkit v1.1 (Feb2013-SP3) (i install so: sudo apt-get install monetdb5-sql monetdb-client) shell> monetdbd create /path/to/mydbfarm shell> monetdbd start /path/to/mydbfarm shell> monetdb create voc shell> monetdb release voc 2. Ubuntu 12.04.3 LTS 3. Schema and data: Schema: CREATE TABLE timedimension ( id integer NOT NULL, yeart int DEFAULT NULL, quarter varchar(2) DEFAULT NULL, weekyear int DEFAULT NULL, dayt int DEFAULT NULL, namemonth varchar(10) DEFAULT NULL, nameday varchar(20) DEFAULT NULL, montht int DEFAULT NULL, day_of_week varchar(15) DEFAULT NULL, date date DEFAULT NULL, month2 char(2) DEFAULT NULL ) CREATE TABLE bi_con_cdr_diario ( ano INT , facmesfac VARCHAR(6) , comcodigo INT , succodigo VARCHAR(3) , procodigo INT , pronombre VARCHAR(20) , prycodigo VARCHAR(20) , fecllamada timestamp , clicodigo INT , clinombre VARCHAR(80) , vencodigo INT , vennombre VARCHAR(50) , sercodigo VARCHAR(3) , pryservicio VARCHAR(4) , sertipo VARCHAR(3) , serclase VARCHAR(4) , trdtipotelefono CHAR(10) , cfacodigo INT , cfanombre VARCHAR(30) , trddurllamada INT , minutosreales DOUBLE , minutosenteros INT , minutosfacturados DOUBLE , costo DOUBLE , costohit DOUBLE , valorbruto DOUBLE , valorneto DOUBLE , trdvalorlocalneto DOUBLE , trdvalorlocalimpu DOUBLE , valorlocal DOUBLE , codpaisori INT , nompaisori VARCHAR(80) , codpaisdes INT , nompaisdes VARCHAR(80) , comnombre2 VARCHAR(15) , linprnombre VARCHAR(40) , trdcodciuori BIGINT , trdnomciuori VARCHAR(80) , sernombre VARCHAR(50) , idtimedimension integer , linprcodigo INT , trdcodciudes INT , trdnomciudes VARCHAR(80) ); Timedimension data:20080101,2008,"1",1,1,"Ene","",1,"Martes","2008-01-01","01" bi_con_cdr_diario data:2013,"092013",4,"CTA",4,"EMBRATEL","HITBRAEMB","2013-09-04 00:00:00",3608,"Air Liquide do Brasil Ltda - Canoas",420,"Flavio Eugenio/Caroline Carneiro","E-N","E-N","ddd","PSTN","Fijo",89,"Consumo",459,7.65,10,10,0.66924,0.66924,1.43199992,1.1456000328063967,3.437,0.471,3.9080000000000004,55,"Brasil",55,"Brazil","HitBrasil","HIT VOICE",0,"0","HIT CALLSERVICE EMB NAL",20130904,1,0,"0",\N But the error is when i do group by as (select sum(minutosfacturados),succodigo,montht,comnombre2 from bi_con_cdr_diario as cdr inner join timedimension as td on cdr.idtimedimension=td.id where comcodigo=4 group by succodigo,comnombre2,montht;) and have 10.000.000 of rows. I appreciate very much your help. I believe this is one great product. Best Regards, Edgar Mejia

1 0

Re: Contents of users-list Digest, Vol 13, Issue 20
by Edgar Mejia 25 Sep '13

25 Sep '13

Thank you Jennie Zhang. 1. I have MonetDB Database Server Toolkit v1.1 (Feb2013-SP3) (i install so: sudo apt-get install monetdb5-sql monetdb-client) shell> monetdbd create /path/to/mydbfarm shell> monetdbd start /path/to/mydbfarm shell> monetdb create voc shell> monetdb release voc 2. Ubuntu 12.04.3 LTS 3. Schema view schemadatamonetdb.sql file 4. Data: timedimension view datatimediM.csv bi_con_cdr_diario: cdr.csv But the error is when i do group by as (select sum(minutosfacturados),succodigo,montht,comnombre2 from bi_con_cdr_diario as cdr inner join timedimension as td on cdr.idtimedimension=td.id where comcodigo=4 group by succodigo,comnombre2,montht;) and have 10.000.000 of rows. I appreciate very much your help. I believe this is one great product. Best Regards, Edgar Mejia

1 0

Monetdb crashed as group by
by Edgar Mejia 24 Sep '13

24 Sep '13

select sum(minutosfacturados),succodigo,montht,comnombre2 from bi_con_cdr_diario as cdr inner join timedimension as td on cdr.idtimedimension=td.id where comcodigo=4 group by succodigo,comnombre2,montht; but for example as this query work fine: select sum(minutosfacturados),succodigo,montht from bi_con_cdr_diario as cdr inner join timedimension as td on cdr.idtimedimension=td.id where comcodigo=4 group by succodigo,montht; What i need to do for to fix this? Thanks Edgar M

3 2

Whats coming in upcoming build monetdb release
by Ashish Kumar Singh 21 Sep '13

21 Sep '13

Hi , During my periodic search for "Is a new monetdb build is going to be released soon" , I found one is placed under testing recently. Is there a place where it can be seen and reviewed what kind of features/fixes are expected in upcoming release? That will be a great help for some of the folks like me to plan ahead! Regards, Ashish

1 0

Re: users-list Digest, Vol 13, Issue 16
by Rose Beck 21 Sep '13

21 Sep '13

Thanks a lot for replying :) But its really unclear to me as to how can I load RDF data into monetdb. As I have a 1000 property values and I can not manually create table for each of the property values. TSQL allows something like SELECT * INTO someTable FROM sourceTable. RDF query engines like RDF3X, BitMat (Medha Atre, et.al.) have all employed monetdb. So, if there is some way out can you please help me with it. On Sat, Sep 21, 2013 at 12:00 PM, <users-list-request(a)monetdb.org> wrote: > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > http://mail.monetdb.org/mailman/listinfo/users-list > or, via email, send a message with subject or body 'help' to > users-list-request(a)monetdb.org > > You can reach the person managing the list at > users-list-owner(a)monetdb.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of users-list digest..." > > > Today's Topics: > > 1. Re: How to automate creation of property tables in monetdb? > (Rose Beck) > 2. Re: How to automate creation of property tables in monetdb? > (Martin Kersten) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Sat, 21 Sep 2013 11:49:10 +0530 > From: Rose Beck <rosebeck.82(a)gmail.com> > To: users-list(a)monetdb.org > Subject: Re: How to automate creation of property tables in monetdb? > Message-ID: > <CAPx9ze9DR0bmy9joSZewwrSL_UNQGP=HefZLHax= > WZAAfDK3Cw(a)mail.gmail.com> > Content-Type: text/plain; charset="iso-8859-1" > > A gentle reminder. Can someone please help me with this? > > > > > On Fri, Sep 20, 2013 at 7:58 PM, Rose Beck <rosebeck.82(a)gmail.com> wrote: > > > I am trying to load data (from myfile, the format of myfile is explained > > below) into monetdb using itsSQL interface. mytable format: > > > > 1 2 3 > > 5 7 8 > > 8 2 3..... > > > > The commands which I am using for performing the same are given below: > > > > sql> create table mytable(subject integer, property integer, object > integer); > > > > sql> copy 10000000 records into mytable from myfile delimiter ' ','\n'; > > > > For each property value (i.e. middle column) I create a separate table: > > > > create table prop1(subject integer, object integer); > > create table prop2(subject integer, object integer); > > create table prop3(subject integer, object integer); ... > > > > and then fill these tables using the following commands: > > > > INSERT into prop1 SELECT subject, object FROM triples_table where > property=prop1 ORDER BY subject, object; > > > > Is there some way to automate this process. i.e. right now I am manually > > creating a separate table for all the distinct properties (using create > > table prop1(subject integer, object integer); ) and then filling this > > table using insert command (INSERT into prop1 SELECT subject, object FROM > > triples_table where property=prop1 ORDER BY subject, object; ). Is there > > some way by which I may automate this procedure. > > > > One of the methods which I know of is to create a C++/python program and > > then call all functions like "create table" by using shell script. But is > > there a faster method to achieve the same. > > > > Also can you please guide as to how can I create and integrate dictionary > > (RDF: id to string mapping) to sql queries? > > <users-list(a)monetdb.org> > > >

2 2

How to automate creation of property tables in monetdb?
by Rose Beck 21 Sep '13

21 Sep '13

I am trying to load data (from myfile, the format of myfile is explained below) into monetdb using itsSQL interface. mytable format: 1 2 3 5 7 8 8 2 3..... The commands which I am using for performing the same are given below: sql> create table mytable(subject integer, property integer, object integer); sql> copy 10000000 records into mytable from myfile delimiter ' ','\n'; For each property value (i.e. middle column) I create a separate table: create table prop1(subject integer, object integer); create table prop2(subject integer, object integer); create table prop3(subject integer, object integer); ... and then fill these tables using the following commands: INSERT into prop1 SELECT subject, object FROM triples_table where property=prop1 ORDER BY subject, object; Is there some way to automate this process. i.e. right now I am manually creating a separate table for all the distinct properties (using create table prop1(subject integer, object integer); ) and then filling this table using insert command (INSERT into prop1 SELECT subject, object FROM triples_table where property=prop1 ORDER BY subject, object; ). Is there some way by which I may automate this procedure. One of the methods which I know of is to create a C++/python program and then call all functions like "create table" by using shell script. But is there a faster method to achieve the same. Also can you please guide as to how can I create and integrate dictionary (RDF: id to string mapping) to sql queries? <users-list(a)monetdb.org>

2 2