- users-list - monetdb.org

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 % of the observations) comparable to their distances. Overlaps are, of course, happening too. So I decided to use a more robust algorithm and compare the results. Due to the size of our dataset (cca 4e8 observations, cca 8e6 real objects), I am also counting on massive parallelization of the algorithm, focusing on saving real time. I have one simple question for you. What algorithm would you use for the clustering? I would like to use K-means or EM Gaussian mixtures, because the data is naturally Gaussian distributed. But then I would have to foretell the number of clusters - what algorithm would you use there? Or should I use some more complicated Bayesian algorithm which does not need the number of clusters? Thank you very much for your answers. Jiri Nadvornik Astronomical Institute AV CR Stellar department Czech Republic <mailto:nadvornik.ji@gmail.com> nadvornik.ji(a)gmail.com

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 millisec the thing does not change. I know i am doing something wrong but i cannot get it. Could you please advise me? :) thx! Davide

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 --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

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 DD file and data file are attached. Thank you very much for answers, Jiri Nadvornik

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 (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 > >>> > >>> > > > >

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

Fixed size table performance
by Gustavo Brian 23 Feb '15

23 Feb '15

Hi, I want to implement a calendar based big table with prices for hotel accommodation with MonetDB. Once I have all the data in the table the performance is amazing but now I'm worried about insert/delete/update performance. I've asking at stackoverflow: http://stackoverflow.com/questions/28122178/monetdb-refresh-data-in-backgro… and had a nice approach but now I wonder about having a fixed size table because I can approximate the needed records for having a year of data and update them each time I have to: enable/disable, change free units, update prices, ... Some of the columns will be used for search: "destination", "date", "category", ... others for refinement: "units", "blocked", ... and others as values for the aggregations: "price", "breakfast", ... Is a fixed size table a good approach ? Updating the columns used for refinement or calculation will affect performance ? Thanks, Gustavo Brian Developer

2 1

Table as function parameter
by Bart Lenoir 20 Feb '15

20 Feb '15

To be more precise: SQL table data 'converted' to R data.frame data inside MonetDB (functions).

1 0