- users-list - monetdb.org

How to tell MonetDB that data are sorted in the table?
by Leon-Constantin Fopa 19 Mar '15

19 Mar '15

Hi Given a table `data` (as described below) and given the insurance that that values imported into this table are sorted. CREATE TABLE data (INT value) CREATE INDEX index_value ON data (value) Since MonetDB can neglect the `CREATE INDEX` statement (see details here : https://www.monetdb.org/Documentation/Manuals/SQLreference/Indices) How do I tell MonetDB that data are sorted in order to let him improves lookup operations ? Best regards, Fopa Léon Constantin

2 3

Datacell issue on Jan2014-SP3 build
by Tsai Li Ming 18 Mar '15

18 Mar '15

Hi, We are trying data cell on the Jan2014-SP3 build. There is a “Query datacell.pass failed:SQLException:mvc:SQL module not initialised” error. Please see below: Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014-SP3) Database: MonetDB v11.17.21 (Jan2014-SP3), 'mapi:monetdb://localhost.localdomain:50000/foo' Type \q to quit, \? for a list of available commands auto commit mode: on sql>set optimizer='datacell_pipe'; operation successful (1.465ms) sql> sql>create table datacell.bsktin( more> id integer, more> tag integer, more> payload integer more>); operation successful (325.852ms) sql>create table datacell.bsktout (like datacell.bsktin); operation successful (106.356ms) sql> sql>call datacell.receptor('datacell.bsktin','localhost',50500); sql> sql>call datacell.emitter('datacell.bsktout','localhost',50600); sql> sql>call datacell.query('datacell.pass', 'insert into datacell.bsktout select * from datacell.bsktin;'); sql> sql>select * from datacell.receptors(); select * from datacell.emitters(); select * from datacell.queries(); select * from datacell.baskets(); +-----------------+-----------+-------+----------+---------+--------+----------------------------+--------+----------+---------+ | nme | host | port | protocol | mode | status | lastseen | cycles | received | pending | +=================+===========+=======+==========+=========+========+============================+========+==========+=========+ | datacell.bsktin | localhost | 50500 | TCP | passive | paused | null | 0 | 0 | 0 | +-----------------+-----------+-------+----------+---------+--------+----------------------------+--------+----------+---------+ 1 tuple (10.546ms) +------------------+-----------+-------+----------+--------+--------+----------------------------+--------+------+---------+ | nme | host | port | protocol | mode | status | lastsent | cycles | sent | pending | +==================+===========+=======+==========+========+========+============================+========+======+=========+ | datacell.bsktout | localhost | 50600 | UDP | active | paused | null | 0 | 0 | 0 | +------------------+-----------+-------+----------+--------+--------+----------------------------+--------+------+---------+ 1 tuple (10.780ms) +---------------+--------+----------------------------+--------+--------+------+-------+-------------------------------------------------------------+ | nme | status | lastrun | cycles | events | time | error | def | +===============+========+============================+========+========+======+=======+=============================================================+ | datacell.pass | paused | null | 0 | 0 | 0 | | insert into datacell.bsktout select * from datacell.bsktin; | +---------------+--------+----------------------------+--------+--------+------+-------+-------------------------------------------------------------+ 1 tuple (15.348ms) +------------------+-----------+---------+-----------+-----------+------------+------+----------------------------+--------+ | nme | threshold | winsize | winstride | timeslice | timestride | beat | seen | events | +==================+===========+=========+===========+===========+============+======+============================+========+ | datacell.bsktin | 0 | 0 | 0 | 0 | 0 | 0 | null | 0 | | datacell.bsktout | 0 | 0 | 0 | 0 | 0 | 0 | null | 0 | +------------------+-----------+---------+-----------+-----------+------------+------+----------------------------+--------+ 2 tuples (15.704ms) sql> sql>call datacell.resume(); sql>call datacell.dump(); invalid/unknown response from server, ignoring output ? #baskets[ 1] datacell.bsktin columns 3 threshold 0 window=[0,0] time window=[0,0] beat 0 milliseconds events 0 ? #baskets[ 2] datacell.bsktout columns 3 threshold 0 window=[0,0] time window=[0,0] beat 0 milliseconds events 0 ? #receptor datacell.bsktin at localhost:50500 protocol=TCP mode=passive status=running delay=1000 ? #emitter datacell.bsktout at localhost:50600 protocol=UDP mode=active status=running delay=1000 ? #scheduler status running (remaining output omitted, use \fraw to examine in detail) We then ran nc and sensor program but get nothing on the nc output: sql>select * from datacell.receptors(); select * from datacell.emitters(); select * from datacell.queries(); select * from datacell.baskets(); +-----------------+-----------+-------+----------+---------+---------+----------------------------+--------+----------+---------+ | nme | host | port | protocol | mode | status | lastseen | cycles | received | pending | +=================+===========+=======+==========+=========+=========+============================+========+==========+=========+ | datacell.bsktin | localhost | 50500 | TCP | passive | running | 2015-03-12 09:58:36.000000 | 1 | 1000 | 1000 | +-----------------+-----------+-------+----------+---------+---------+----------------------------+--------+----------+---------+ 1 tuple (5.729ms) +------------------+-----------+-------+----------+--------+---------+----------------------------+--------+------+---------+ | nme | host | port | protocol | mode | status | lastsent | cycles | sent | pending | +==================+===========+=======+==========+========+=========+============================+========+======+=========+ | datacell.bsktout | localhost | 50600 | UDP | active | running | null | 0 | 0 | 0 | +------------------+-----------+-------+----------+--------+---------+----------------------------+--------+------+---------+ 1 tuple (6.183ms) +---------------+---------+----------------------------+--------+------+----------+------------------------------------------------+------------------------------------------------+ | nme | status | lastrun | cycles | even | time | error | def | : : : : : ts : : : : +===============+=========+============================+========+======+==========+================================================+================================================+ | datacell.pass | running | null | 105797 | 0 | 12205937 | Query datacell.pass failed:SQLException:mvc:SQ | insert into datacell.bsktout select * from dat | : : : : : : : L module not initialized : acell.bsktin; : +---------------+---------+----------------------------+--------+------+----------+------------------------------------------------+------------------------------------------------+ 1 tuple (6.299ms) +------------------+-----------+---------+-----------+-----------+------------+------+----------------------------+--------+ | nme | threshold | winsize | winstride | timeslice | timestride | beat | seen | events | +==================+===========+=========+===========+===========+============+======+============================+========+ | datacell.bsktin | 0 | 0 | 0 | 0 | 0 | 0 | 2015-03-12 10:04:22.000000 | 1000 | | datacell.bsktout | 0 | 0 | 0 | 0 | 0 | 0 | null | 0 | +------------------+-----------+---------+-----------+-----------+------------+------+----------------------------+--------+ 2 tuples (6.487ms) Can someone help? Our MonetDB build is faulty? Regards, Liming

4 5

Geometrical grid for spherical data
by Jiří Nádvorník 17 Mar '15

17 Mar '15

Hi All, I have spherical coordinates (right ascension, declination) and I'd like to iterate over areas in this 2-dimensional space. Easiest that comes to mind is just create a grid of squares (say 5 arcsecs wide) and iterate over them. Is it easier to do this in MonetDB with just spherical coordinates (some kind of spherical index), or do I have to convert them to x,y,z coordinates and create the grid in 3D? Thank you for your insight. Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic <mailto:nadvornik.ji@gmail.com> nadvornik.ji(a)gmail.com

3 4

Discussion on Concurrency Insert Issue "insert into A from B table and insert into C from D "
by Shivanandan Gupta 17 Mar '15

17 Mar '15

Hi All, I have written a java program when runs a two threads in parallel , copy command in parallel is working fine using batch files but the insert into using batch file is getting hanged , I have given the commands below which I have used in the batch file. Copy command Batch. mclient.bat demo -s "copy into A FROM 'D:\ETL_Source\abcd.csv' using delimiters ',';" Insert into Batch mclient.bat demo -s "insert into B select * from A;" One more problem I am facing is that while running concurrent insert into A select * from B and insert into C select * from D. I have read a lot about concurrent issue but still I am a bit confuse , my understanding is that for any insert into table monetdb do a db lock and rollback all other queries running in parallel , if this is true then in real time scenario how we can use this db for a DWH , as any time we will have multiple facts and dimensions and for facts we will be doing parallel loads, data volume will be very high. Please help me out if my understanding is wrong or if I can change some settings to accept parallel insert command. Please let me know if you need more information from my side for better understanding. -- Cheers, Shivanandan Gupta

1 0

Multi threaded (2 threads) Java program for copy command
by Shivanandan Gupta 13 Mar '15

13 Mar '15

Hi Jennie, I am getting below given exception while running in parallel. Exception in thread "main" java.sql.SQLException: COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2535) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2284) at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:508) at nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:349) at connect_monetdb.runETL2(connect_monetdb.java:79) at connect_monetdb.main(connect_monetdb.java:14) Thank you, Shivanandan Gupta. On Fri, Mar 13, 2015 at 8:09 PM, Shivanandan Gupta < shivnandangupta(a)gmail.com> wrote: > Hi Jennie, > > Thanks for your reply. please find my replies below: > > Jennie Commented: Concurrent insertion into two different tables should > work. Do you have anymore information about why the COMMIT has failed? > Maybe the problem lies somewhere else than concurrent insertions? > > Shiva's Comment: yes when I run two batch file by clicking it with > difference of second ( as it takes few seconds to click the second batch > file) and it runs perfectly, even I have tried running copy command > directly on the monetsql client and on two clients in parallel it works > perfectly. Is there some configuration change required on monetdb server? > > Jennie Commented: - Have you tried the two COPY INTO separately? To know > if the problem is indeed caused by concurrent insertions. > > Shiva's Comment: When I am trying with two separate java program running > concurrently its throwing same exception. > > > Jennie Comment: - Does the problem occur one-time, or every time you > repeatedly execute your code? > Shiva's Comment: Yes the same error message is coming every time I run > concurrent java programs with copy command in it ( here copying data to > different tables ). > > Any help will be really appreciated. > > Thank you. > > Shivanandan Gupta. > > > Date: Fri, 13 Mar 2015 15:36:23 +0800 > From: Ying Zhang <Y.Zhang(a)cwi.nl> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Cc: martin(a)monetdb.org > Subject: Re: Multi threaded (2 threads) Java program for copy command > Message-ID: <89A66109-B083-45A4-B59E-62B7AF0C9D86(a)cwi.nl> > Content-Type: text/plain; charset=us-ascii > > > > On Mar 12, 2015, at 19:27, Shivanandan Gupta <shivnandangupta(a)gmail.com> > wrote: > > > > Hi Martin, > > > > Thanks for your prompt reply , I am using two different tables here but > still I am getting "Exception in thread "main" java.sql.SQLException: > COMMIT: failed" exception. > > Hello Shivanandan, > > Concurrent insertion into two different tables should work. Do you have > anymore information about why the COMMIT has failed? Maybe the problem > lies somewhere else than concurrent insertions? > > - Have you tried the two COPY INTO separately? To know if the problem is > indeed caused by concurrent insertions. > - Does the problem occur one-time, or every time you repeatedly execute > your code? > > Regards, > > Jennie > > > > On Thu, Mar 12, 2015 at 4:57 PM, Shivanandan Gupta < > shivnandangupta(a)gmail.com> wrote: > >> Hi Martin, >>> >> >> Thanks for your prompt reply , I am using two different tables here but >> still I am getting "Exception in thread "main" java.sql.SQLException: >> COMMIT: failed" exception. >> >> >> >>> Message: 2 >>> Date: Thu, 12 Mar 2015 08:32:31 +0100 >>> From: Martin Kersten <martin(a)monetdb.org> >>> To: users-list(a)monetdb.org >>> Subject: Re: Multi threaded (2 threads) Java program for copy command >>> commit fails for one thread >>> Message-ID: <5501410F.1070508(a)monetdb.org> >>> Content-Type: text/plain; charset=windows-1252; format=flowed >>> >>> Hi, >>> >>> Yes. Insertion into the same table would be invalidated due to the >>> transaction semantics. >>> See >>> https://www.monetdb.org/Documentation/Manuals/SQLreference/Transactions >>> >>>

1 0

Re: Multi threaded (2 threads) Java program for copy command
by Shivanandan Gupta 13 Mar '15

13 Mar '15

> > Hi Martin, > Thanks for your prompt reply , I am using two different tables here but still I am getting "Exception in thread "main" java.sql.SQLException: COMMIT: failed" exception. > Message: 2 > Date: Thu, 12 Mar 2015 08:32:31 +0100 > From: Martin Kersten <martin(a)monetdb.org> > To: users-list(a)monetdb.org > Subject: Re: Multi threaded (2 threads) Java program for copy command > commit fails for one thread > Message-ID: <5501410F.1070508(a)monetdb.org> > Content-Type: text/plain; charset=windows-1252; format=flowed > > Hi, > > Yes. Insertion into the same table would be invalidated due to the > transaction semantics. > See > https://www.monetdb.org/Documentation/Manuals/SQLreference/Transactions > > On 12/03/15 08:18, Shivanandan Gupta wrote: > > Hi All, > > > > I am evaluating monetdb for our dwh and I am trying to load data from > two different csv file to 2 different tables using multi-threaded java > program in parallel, for one thread the load is successful and other one is > failing with exception given below. > > > > Exception in thread "main" java.sql.SQLException: COMMIT: failed > > at > nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2535) > > at > nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2284) > > at nl.cwi.monetdb.jdbc.MonetConnection.commit(MonetConnection.java:374) > > at connect_monetdb.runETL(connect_monetdb.java:78) > > at connect_monetdb.main(connect_monetdb.java:14) > > > > > > command used is : > > > > String csql = "copy into s_asset_"+filename+" from '"+filepath+""+ > filename +".csv' using delimiters',';"; > > st.execute(csql); > > con.commit(); > > > > > > I am using monetdb jdbc driver and monetdb server is also on the same > machine. > > > > Any help will be really great. > > > > Thank you. > > > > Cheers, > > Shivanandan Gupta > > > > > > > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org > > https://www.monetdb.org/mailman/listinfo/users-list > > > > > >

2 2

Multi threaded (2 threads) Java program for copy command commit fails for one thread
by Shivanandan Gupta 12 Mar '15

12 Mar '15

Hi All, I am evaluating monetdb for our dwh and I am trying to load data from two different csv file to 2 different tables using multi-threaded java program in parallel, for one thread the load is successful and other one is failing with exception given below. Exception in thread "main" java.sql.SQLException: COMMIT: failed at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2535) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2284) at nl.cwi.monetdb.jdbc.MonetConnection.commit(MonetConnection.java:374) at connect_monetdb.runETL(connect_monetdb.java:78) at connect_monetdb.main(connect_monetdb.java:14) command used is : String csql = "copy into s_asset_"+filename+" from '"+filepath+""+ filename +".csv' using delimiters',';"; st.execute(csql); con.commit(); I am using monetdb jdbc driver and monetdb server is also on the same machine. Any help will be really great. Thank you. Cheers, Shivanandan Gupta

2 1

Re: Problem with Datacell
by Julien Marie 10 Mar '15

10 Mar '15

I managed to make it work a bit more by installing Jan-2014 version of MonetDB. I can send data into datacell, I can send data out of datacell if I insert directly into the bsktout. But the queries are not working : +----------------+---------+---------------------------+--------+------+----------+----------------------------+----------------------------+ | nme | status | lastrun | cycles | even | time | error | def | : : : : : ts : : : : +================+=========+===========================+========+======+==========+============================+============================+ | datacell.passd | running | null | 980245 | 0 | 17780876 | Query datacell.passd faile | insert into datacell.bout | : : : : : : : d:SQLException:mvc:SQL mod : select * from datacell.bbi : : : : : : : : ule not initialized : n; : +----------------+---------+---------------------------+--------+------+----------+----------------------------+----------------------------+ Is there a version or release that was working ? Julien Marie > Message: 1 > Date: Mon, 9 Mar 2015 09:55:06 +0800 > From: Julien Marie <jm(a)producture.com> > To: users-list(a)monetdb.org > Subject: Problem with Datacell > Message-ID: > < > CABmP2VKm0csQTzcUcgwiusw9o52qxmbmrZ7PM+auxoj7skdrCw(a)mail.gmail.com> > Content-Type: text/plain; charset="utf-8" > > I know the feature is deemed experimental, but I have troubles just to > start the example and I keep on having transaction error messages : > > sql>call datacell.receptor('datacell.bsktin', 'localhost', 50500); > sql>call datacell.emitter('datacell.bsktout', 'localhost', 50600); > START TRANSACTION: cannot start a transaction within a transaction > > What is going on ? > > Thank you very much > > -- > *Julien Marie* > > jm(a)producture.com > skype : thewiseup > +63 917 511 8258 > > >

1 0

Horizontal partitioning
by Bart Lenoir 09 Mar '15

09 Mar '15

Hi All, Does MonetDB besides load balancing (round robin) in its clustering also support horizontal partitioning? If so, how? Thanks. Cheers, John

1 0

Problem with Datacell
by Julien Marie 09 Mar '15

09 Mar '15

I know the feature is deemed experimental, but I have troubles just to start the example and I keep on having transaction error messages : sql>call datacell.receptor('datacell.bsktin', 'localhost', 50500); sql>call datacell.emitter('datacell.bsktout', 'localhost', 50600); START TRANSACTION: cannot start a transaction within a transaction What is going on ? Thank you very much -- *Julien Marie* jm(a)producture.com skype : thewiseup +63 917 511 8258 -----BEGIN PGP PUBLIC KEY BLOCK----- xsBNBFNmiRIBCAC77MQjKyaDaRo9bQhfGNXIDK3SldtI+A56EAY2xh8/WCrJ HYwhdwW/2aConIa5eL92WLf0W6FSmjk0rqSp1bkkDt8RW7JFiftCACZkSwHF Rg3zpUeMgFG3BBRpwCiOxSygDza61AmMAQ+8LuLZOETEFddT/DQNCBouZLYN KE9uCs+lYkrpc3YbPfx+kGUDj5rT2SYnHwt4r352OjT1U8ABQdxBigymmzgT rZh/4oyVXjMtBCRwAqwmVtzwoNoarAcgsm6qQgNwTdy5NtmKlgs2hqQtIkQK 3uL+e3zyWb1B8e6gUhpn5cooeJG1hvlJ65XUTHgHOCCz7Ue4q1rl/5kZABEB AAHNIEp1bGllbiBNYXJpZSA8am1AcHJvZHVjdHVyZS5jb20+wsByBBABCAAm BQJTZokZBgsJCAcDAgkQ7nj49Sxgl2gEFQgCCgMWAgECGwMCHgEAANulB/4u c+UeSeo8Wn8COAKSoMyWh2m5zBEdRdTlELVNcQIH3ssqu1M3TEpfBixVofp2 NPYKNF7FrQPl7z9+w0azlVJVIDF6DlGWjVlKm1nDTlVr4gW8J6mgRYivV3Tp HlBHIFmelOIcBpJAhUfc73H5a9BAsJ/a2Iekpc0TERsnarVuA3sqeTqPkwBY komURJWx0yxiYj91Be1PDRRi+DyGBKa8AeapXiCmCtpGAxksB43iwiiFnLev iZCWp3bKeBCLZy5IkONNU6mNzTcljrgHzRjs9P8OSiEIJ9T8tubHUkkA0LsR 9l7vmBk3aESNF8w1LmRTRN+gBkXsMSjzNilGj7zBzsBNBFNmiRkBCACOsST4 AuZffy8Ny7w1Qhvo1Okt9bBoh5zIJKE0tEo1L4YmDYALKnagVHNUwiAM26Bv ynK+pAvRopt62Wu+s1hQgHNuwnTMuuL0OW4Y8daOIN9pUAqnT/etskSwH24W niP445IEa09RyA1b2Mwhy8hEN/eCFHmnY36U5+qiiUAjN6Khsflzkq4I2Qy9 wwMVhNC8/J1QtmODcUekMgnGG0XcXNm22N1EU3llr5yG/oYD0dAKnIIv4rf8 3s6LU7ND/SOjmaLNtzwLuGawcCBPGfaBpB77ozJz0jJ3RaEwuY/Kqt4Zfp5T 0xLFgr+fXxSjHku0qU6PGT3iH+eHkWZXGG2pABEBAAHCwF8EGAEIABMFAlNm iRwJEO54+PUsYJdoAhsMAADWtwf9HJiym4Oi2zwWiDksmLc7699iVY1pvuic RlqwMw2bmFd+PUUFupU6CR8jOTcYPJm3NzZ9gwPM3yzjd/BReWmd4mfZJfvf EIYHzJoDRmnT5t8b9Wp6jllWtVAHbN9FTUW3uJODDKhffhYNbmAhxpotao+a XQJcSrcWE1Gl030ZQryOfkz2l/eUfYYSK38gsR7ksz22Gb1CKS4gmG8jgxQs vmDTSRagUYEXOdTaX5HKMdHrMw1ll1Z8bMb/y0N0kgDmIF0pAX2i+bk5UpnU 6mbeLCwo0OoIPYpC4GvGPBglull+BRJlwvf9Kpzlip5KHYSTnb19yyjbs1qC rpsqrnUA6g== =o6AX -----END PGP PUBLIC KEY BLOCK-----

1 0