- users-list - monetdb.org

Count function with Over clause()
by Amey M 30 Mar '15

30 Mar '15

The Aggregate count function doesn't work with the over clause in MonetDB. I am a new user on MonetDB, and have trouble using queries running on other databases like Postgres on MonetDB. When using the Count function with Over clause I get the following error: sql>select count(id) over() from sample_table ;SELECT: function 'count' not found Regards, Ameya

1 0

MonetDB C functions
by Jiří Nádvorník 29 Mar '15

29 Mar '15

Hi all, I'm trying to understand the example of user defined functions in MonetDB (https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFunct ion). I understand the concept for just reversing a string. But what about working with tabular data? I'd like to pass to this C function a whole table (list of rows) and then return another table(s)/write result data to other tables. What is the correct way of doing this? Can I pass some kind of arrays to the C function? … [View More]

2 2

Better understand those MAL functions
by Leon-Constantin Fopa 29 Mar '15

29 Mar '15

hello dear all, I'm trying to understand an execution plan generated in MAL by monetDB. I would like to how exactlly the following functions works : - sql.mvc - algebra.subselect - sql.bind - sql.subdelta - algebra.leftfetchjoin Best regards,

1 0

Arrays in MonetDB
by Jiří Nádvorník 27 Mar '15

27 Mar '15

Hi, Do I understand correctly that MonetDB does not support array types? What's the best possibility to use instead of them? I have a table of pixels and table of points. For each pixel.ID I have two different arrays of points to store in the DB. Thank you for clarification. Best regards, Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic <mailto:nadvornik.ji@gmail.com> nadvornik.ji(a)gmail.com

1 0

MonetDB user defined function grouping
by Jiří Nádvorník 26 Mar '15

26 Mar '15

Hi all, I am trying to do a user defined function written in C++ which takes the whole table as a parameter, loads it into a vector structure, and then divides the data in some groups (duplicities will occur). How can I return the results of this grouping from the C function? Probably I'd like to store the group IDs in a separate table and create and then create an M:N table between the group IDs and the original records. Any help appreciated. Best regards, Jiri Nadvornik … [View More]

1 0

HEALPix with monetDB
by Jiří Nádvorník 25 Mar '15

25 Mar '15

Hi all, I'm planning to index my spherical data in MonetDB byHEALPix. I will need it for: 1. Data partitioning - distribute the data uniformly on the DB nodes, but keep them sorted (keep points close on the sphere on the same node). 2. Neighbor lookup - can I actually index my spherical coordinates stored in MonetDB by some kind of a BTree of HEALPix pixels? I understand how computing a hash for the coordinates and indexing on it helps in relational DB, but how do the indices function in an … [View More]

1 0

Case insensitive LIKE ??
by Vijay Krishna 23 Mar '15

23 Mar '15

Hi, By default the LIKE criteria of MonetDB returns results by matching the exact case of the search word (MonetDB Jan 2014 SP1 Mac). How to make the LIKE criteria to do a case insensitive search by default?? (Something like the BINARY keyword that does case sensitive search in MySQL??) I don't wish to add a workaround like "LOWER(column) like LOWER(searchword)" here.. Any help much appreciated. Thanks in advance. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

2 2

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 … [View More]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 [View Less]

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 … [View More]

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 … [View More]

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.… [View More]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 >>> >>> [View Less]

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.… [View More]

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(… [View More]

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 … [View More]

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-… [View More]

1 0

Clustering
by Jiří Nádvorník 03 Mar '15

03 Mar '15

Hi All, I'm trying to solve a clustering problem on my astronomical data. It is simply repeated observations of stars on images with random errors, effectively creating clusters with Gaussian distributions. These clusters have from 1 to cca 1000 points and are approximately of the same size (~1 arcsec). So much for the introduction. I already tried to solve this problem with simple methods but with poor results. Our regions are very dense and the size of the clusters is often (cca 20 % … [View More]

2 1

Converting Bigint field (containing unixtime in millisecs) to TIMESTAMP
by Davide Magatti 02 Mar '15

02 Mar '15

Hi all, Database: MonetDB v11.19.9 (Oct2014-SP2) I am trying to convert a big string fiel in a table containing unixtimes in millisecs to timestamps but i cannot find the right way to do it. So my table descriptor: TABLE "table" ( "user" VARCHAR(512), "time" BIGINT, [...] ); i tried to do > select CAST( CAST(time/1000 AS INTERVAL SECOND) AS TIMESTAMP) from table ; >>types sec_interval(13,0) and timestamp(7,0) are not equal And did not work, also using time in … [View More]

2 1

SQL scenario initialization error
by Doug Service 02 Mar '15

02 Mar '15

A monet database we have created will not start with the following error: sudo monetdb start dbprod starting database 'dbprod'... FAILED start: starting 'dbprod' failed: database 'dbprod' did not initialise the sql scenario Is it possible to recover from an error like this? Here is the relevant log file portion. 2015-03-01 16:11:36 MSG dbprod[5743]: arguments: /usr/bin/mserver5 --dbpath=/var/lib/monetdb/dbprod --set merovingian_uri=mapi:monetdb://qbdata:42000/dbprod --set mapi_open=false … [View More] --set mapi_port=0 --set mapi_usock=/var/lib/monetdb/dbprod/.mapi.sock --set monet_vault_key=/var/lib/monetdb/dbprod/.vaultkey --set gdk_nr_threads=2 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2015-03-01 16:11:36 ERR dbprod[5743]: !mvc_init: unable to create system tables 2015-03-01 16:11:36 MSG dbprod[5743]: # MonetDB 5 server v11.19.7 "Oct2014-SP1" 2015-03-01 16:11:36 MSG dbprod[5743]: # Serving database 'dbprod', using 2 threads 2015-03-01 16:11:36 MSG dbprod[5743]: # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs dynamically linked 2015-03-01 16:11:36 MSG dbprod[5743]: # Found 1.955 GiB available main-memory. 2015-03-01 16:11:36 MSG dbprod[5743]: # Copyright (c) 1993-July 2008 CWI. 2015-03-01 16:11:36 MSG dbprod[5743]: # Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved 2015-03-01 16:11:36 MSG dbprod[5743]: # Visit http://www.monetdb.org/ for further information 2015-03-01 16:11:36 MSG dbprod[5743]: # Listening for UNIX domain connection requests on mapi:monetdb:///var/lib/monetdb/dbprod/.mapi.sock 2015-03-01 16:11:36 MSG dbprod[5743]: !SQLException:SQLinit:Catalogue initialization failed 2015-03-01 16:11:36 MSG dbprod[5743]: !ERROR: Could not read the version number from the file 'sql_logs/sql//log'. 2015-03-01 16:11:36 MSG merovingian[1568]: database 'dbprod' (5743) has exited with exit status 0 2015-03-01 16:11:36 MSG merovingian[1568]: database 'dbprod' has shut down 2015-03-01 16:11:36 ERR control[1568]: (local): failed to fork mserver: database 'dbprod' did not initialise the sql scenario 2015-03-01 16:12:45 MSG control[1568]: (local): served status list 2015-03-01 16:12:45 MSG control[1568]: (local): locked database 'dbprod' 2015-03-01 16:13:09 MSG control[1568]: (local): served status list 2015-03-01 16:13:09 MSG control[1568]: (local): released database 'dbprod' 2015-03-01 16:13:19 MSG control[1568]: (local): served status list 2015-03-01 16:13:19 MSG merovingian[1568]: starting database 'dbprod', up min/avg/max: 0s/2d/1w, crash average: 0.00 0.50 0.17 (15-10=5) 2015-03-01 16:13:20 MSG merovingian[1568]: sending process 5769 (database 'dbprod') the TERM signal [View Less]

3 3

Import from PostgreSQL
by Jiří Nádvorník 27 Feb '15

27 Feb '15

Hi All, I'm trying to migrate my data from postgresql to monetDB. I only need one table without privileges and such, so I decided to export it to csv and create a table in monetdb. Then when I run the copy command, the following error occurs. $ mclient -u caucau -d demo -s " copy into bextract.observation from stdin" - < bextract_observation.csv value 'obsname_id' from line 1 field 1 not inserted, expecting type int failed to import table I am quite sure the number is an integer :). The … [View More]

2 2

MonetDB installation
by Jiří Nádvorník 27 Feb '15

27 Feb '15

Hi All, I have followed the instructions as simple as they are in the docs, but when I tried to start the monetdb create <path to my dbfarm folder>, it cries that it cannot find conrol port (and I have to suply it with -p along with -h and -P parameters). I am only trying to get things running on localhost - do I need to somehow setup the mserver5 daemon? No mention of that in the tutorial. Thank you very much for help. Best regards, Jiri Nadvornik

6 9

Migration from MySQL to MonetDB
by Vijay Krishna 24 Feb '15

24 Feb '15

Hi, I have been trying to migrate my databases as a whole from MySQL to MonetDB. I tried out dumping from MySQL and restoring to MonetDB. But I received a lot of syntax errors due to the mismatch between MonetDB dump file recognition and MySQL dump file. Is this migration as a whole possible? Any ideas on how to do this?? Has anyone tried this out?? If not possible, what is the easiest way to copy a database as a whole from MySQL to MonetDB? Any help much appreciated. Thanks in advance. With Regards, Vijay.

6 6

RE: MonetDB + FITS
by Jiří Nádvorník 23 Feb '15

23 Feb '15

> -----Original Message----- > From: Dimitar Nedev [mailto:dimitar.nedev@monetdbsolutions.com] > Sent: 23. února 2015 16:28 > To: Jiří Nádvorník > Subject: Re: MonetDB + FITS > > > > On 2015-Feb-23, at 13:28 , Jiří Nádvorník <nadvornik.ji(a)gmail.com> wrote: > > > > Hi, > > > > I will be using this on Debian. > > > > Ok, the ingestion seems quite straight-forward, so to the next point. The > coordinates will be ra, dec (… [View More]right ascension, declination). I have to decide > what to do with these "coordinate types". > > > > First option is to use some kind of indexing (if you support any) on these > coordinates. Q3C, PgSphere comes in mind for relational DBs. > MonetDB does indexing (very lightweight), but currently entirely managed > by the engine. In general you can just start querying and not worry about > these things, depending on your data size, or at least how it was designed. > The first time this data is accessed an index of some sort is created. Some of > this indices are persistent, so they will be preserved between server restarts. > This means that any following query over the same columns will most likely > be faster. If you really want you can also force an index creation, executing a > range query (e.g. "SELECT COUNT(*) FROM table WHERE column > value") > will create an index on that column. > MonetDB already has a module called GEOM, that supports OGS Simple > Features for GeoSpatial queries. I am not sure if it currently has the function > that need to query a spherical coordinates. The module is already > considerably extended and all the changes will be in the next major version > (expected in the next few month). This includes a lot more GIS functions and > will also include some optimizations for GeoSpatial queries. > > > > > Second option is to transform them into [x,y,z] coordinates on a cube and > use these numbers as coordinates for dimensions. > You can certainly do that, but if actual data transformation is required, it > might be very time consuming. Such queries can very likely be done without > data transformation, albeit with a more complex SQL query. Performance > might not actually suffer. > Now, MonetDB has a module for accessing array-like data called SciQL, but it > is still quite experimental, so I don't recommend using it yet. > > > > > What would you choose? The queries will be of two kinds. > > > > First one: Select me a chunk of the sky and pass it for further processing. > This will be directly connected with how I want to partition the data > (physically) on hard drives. > > > > Second one (not necessarily in the DB, C++ parallel application is maybe a > better option?) - Some more fancy stuff on the result of previous query > (chunk of the sky). > > It will be more or less some Bayesian euclidean clustering of the rows > (result will be clusters of points with coordinates close to each other). > Running this algorithm will contain searching for neighbors many and many > times over. > > That is the question whether I want to keep this in DB or should I load it to > the memory of a C++ application and search for the neighbors and clusters > there. > For both kind of queries I would use a database, not only because I work on > one, but because the work/research done on database management > systems in general have made then very efficient when it comes to accessing > data. > > For an advanced analysis you can still use a database, and if you stick with > MonetDB you can actually do the clustering right in the database. As of this > release, we have support for in-database processing using R [1]. You can use > an R function to perform Bayesian clustering directly on the data in the > system, without having to export it or pass it to another application. Aside > from performance benefits, due to reduced amounts of data shuffling, this > should also limit the amount of processes you'll need to implement yourself. > This approach has been used by researchers - going over large volume of > data, looking for events/records in the data that stood out. This is actually the biggest performance issue. I have read lots of papers where Bayesian approach was used for cca 500 000 objects. We have ATM 4e8 objects and expect to have 6e8 objects in the end. That leads me to the opinion that some kind of parallelization will be needed (the clustering algorithm will have to be running in parallel for chunks of the sky and then some algorithm above that will have to solve the overlapping regions). Can something like that be written in the R module? > > My suggestion is to extract a small dataset, prepare your data ingestion > procedures and try out the small queries (select me a chunk of the sky). > Since you would be working on small amount of data the actual data > processing should not take much time. This way you can verify and refine > your process. When it comes to the actual queries, I would recommend you > to try with simple queries and see if they can do the work, crafting more > complex ones if needed. > That must be my approach and I will have to write the whole thing before I can test the actual usefulness on big data, but that's the beauty of this job :) that everything we have done in the past months can be worthless, but surely not the things we learn from it. > I hope this answers some of your questions. > Yes it does, thank you very much for such outgoing communication. Kind regards, Jiri > Best regards, > Dimitar > > [1] https://www.monetdb.org/content/embedded-r-monetdb > > > > > Thank you for your suggestions. > > > > Jiri N. > > > >> -----Original Message----- > >> From: Dimitar Nedev [mailto:dimitar.nedev@monetdbsolutions.com] > >> Sent: 23. února 2015 12:52 > >> To: Jiří Nádvorník > >> Subject: Re: MonetDB + FITS > >> > >> Hi Jiří, > >> > >> Absolutely, if you have the data in anther database, exporting and > >> importing in MonetDB shouldn't be a problem. > >> > >> To dump PostgreSQL database to an .sql file you you can use the > "pg_dump" > >> utility. You can then load it in MonetDB from "mclient". Here's more > >> info how to load a database dump in MonetDB: > >> https://www.monetdb.org/Documentation/UserGuide/DumpRestore > >> These are of course some differences between the two databases' SQL > >> interfaces, which might require some changes to the .sql file, like > >> adapting data types, but PostgreSQL and MonetDB datatypes are similar. > >> > >> Dumping it to a CSV is also an option and it can eliminate potential > >> SQL incompatibility issues. Loading CSV files also happens through the > "mclient". > >> First you'll need to create the table for loading data into. Then > >> basically involves invoking the "COPY INTO" SQL command, specifying > >> the table to load the data into, the location of the file and the > >> delimiters. Here's more info on that: > >> > https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/Loading > >> BulkData > >> > >> The FIST support was developed in conjunction with astronomers > >> (mostly from ASTRON - the Dutch institute for radio astronomy), the > >> main idea being connivence of data ingestion from large repositories > >> of FITS files. As such, it might pay of using it if you need to load a large > volume of FITS files. > >> > >> By the way, what kind of operating system are you going to use > >> MonetDB on? We are interested what operating system our users have. > >> > >> Best regards, > >> Dimitar > >> > >>> On 2015-Feb-23, at 12:00 , Jiří Nádvorník <nadvornik.ji(a)gmail.com> > wrote: > >>> > >>> Hi Dimitar, > >>> > >>> This sounds a bit complicated. I have the data already ingested into > >>> a > >> postgresql database (in more or less 1 table). Wouldn't it be easier > >> to import it from there? Or from some other simple structure like csv? > >>> > >>> I don't really need to work with the files as such - it is just > >>> means of holding > >> the data for me. > >>> > >>> Kind regads, > >>> > >>> Jiri N. > >>> > >>>> -----Original Message----- > >>>> From: Dimitar Nedev [mailto:dimitar.nedev@monetdbsolutions.com] > >>>> Sent: 23. února 2015 11:09 > >>>> To: Jiří Nádvorník > >>>> Cc: MonetDB Users > >>>> Subject: Re: MonetDB + FITS > >>>> > >>>> Hi Jiří, > >>>> > >>>> This might be a bit long, but please bear with me, if you do it > >>>> once it's relatively simple. > >>>> To use FIST it currently takes 4 general steps: > >>>> 1) Build MonetDB with FITS support > >>>> 2) Create SQL functions for FIST data loading > >>>> 3) Attach a FIST file for loading > >>>> 4) Load the data from the file > >>>> > >>>> 1) For FITS to work, you must build MonetDB together with the 'cfitsio' > >>>> library. If you are using OS X, you can installMonetDB via > >>>> Homebrew, which you can also configure to build the binaries with FITS > support: > >>>> "brew install monetdb --with-cfitsio". If you are building MonetDB > >>>> on another UNIX/Linux systems, you need to have the cfitsio library > >>>> present on your path when configuring the build. If its presence is > >>>> detected, MonetDB will be built with FITS support. More on building > >> MonetDB you can find here: > >>>> https://www.monetdb.org/Developers/SourceCompile > >>>> > >>>> 2) In all releases, including the least (Oct2014-SP2) of MonetDB > >>>> the FITS SQL functions for data loading are not created > >>>> automatically, even if FIST support is enabled. This is, sort of, > >>>> only cosmetic, since the FITS support is there, just not > >>>> conveniently exposed at SQL. Still, if you are building MonetDB > >>>> from the latest stable sources, as Homebrew will by default, you > >>>> will must first define the > >> required SQL functions like this: > >>>> create procedure fitsattach(fname string) external name fits.attach; > >>>> create procedure fitsload(tname string) external name fits.load; > >>>> create procedure listdirpat(dirname string,pat string) external > >>>> name fits.listdirpattern; > >>>> > >>>> 3) Then you can run the 'attach' function to pre-process your file: > >>>> call fitsattach('/Users/dnedev/monetdb/r564532_cat.fits'); > >>>> > >>>> At this point you can already take a glimpse at your (meta-)data > >>>> Listing the attached files: > >>>> select * from fits_files; > >>>> Listing the catalogs in each file: > >>>> select * from fits_tables; > >>>> Listing the columns contained in the catalogs: > >>>> select * from fits_columns; > >>>> > >>>> 4) Finally load the data in the FITS, specify separately which > >>>> catalogs to be loaded, as they are listed in "fits_tables", e.g. : > >>>> call fitsload('r564532_cat_1'); > >>>> call fitsload('r564532_cat_2'); > >>>> call fitsload('r564532_cat_3'); > >>>> call fitsload('r564532_cat_4'); > >>>> > >>>> The data should name be loaded in a table with the same name as the > >>>> catalog , which you can access like this: > >>>> select * from "r564532_cat_1"; > >>>> > >>>> As of the next MonetDB release, the FITS functions will be created > >>>> automatically, eliminating the second step. This also means that if > >>>> you are building MonetDB from sources checked out from our > >>>> repository, and not using the stable release sources, the functions > >>>> will be > >> create for you. > >>>> We are also working on improving the documentation and usability of > >>>> these functions. > >>>> > >>>> I hope this helps. > >>>> > >>>> Best regards, > >>>> Dimitar > >>>> > >>>> > >>>>> On 2015-Feb-23, at 09:55 , Jiří Nádvorník <nadvornik.ji(a)gmail.com> > >> wrote: > >>>>> > >>>>> Hi All, > >>>>> > >>>>> I have a simple question – how to setup and ingest data into > >>>>> MonetDB > >>>> from FITS binary tables (FITS extensions)? Namely this will be > >>>> astronomical objects (1 row = coordinates, magnitude, …). > >>>>> > >>>>> Thank you very much for info, > >>>>> > >>>>> Jiri Nadvornik > >>>>> Astronomical Institute AV CR > >>>>> Stellar department > >>>>> Czech Republic > >>>>> nadvornik.ji(a)gmail.com > >>>>> > >>>>> > >>>>> > >>>>> _______________________________________________ > >>>>> users-list mailing list > >>>>> users-list(a)monetdb.org > >>>>> https://www.monetdb.org/mailman/listinfo/users-list > >>> > >>> > > > > [View Less]

1 0

MonetDB + FITS
by Jiří Nádvorník 23 Feb '15

23 Feb '15

Hi All, I have a simple question - how to setup and ingest data into MonetDB from FITS binary tables (FITS extensions)? Namely this will be astronomical objects (1 row = coordinates, magnitude, .). Thank you very much for info, Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic <mailto:nadvornik.ji@gmail.com> nadvornik.ji(a)gmail.com

4 5