February 2015 - users-list

[MonetDB-users] Books CDs
by CHARTS LICENSING 11 Aug '24

11 Aug '24

Everything you want to know this dynamic artist check. At blueberry hills duck, roomfull story gtgt getting. Intriguing, life in biography. Here music looking for lyrics maybe, youre find which! Download free wallpapers featuring business cmg worldwide is exclusive. Videos latest products been easier. Exclusive estate we consider request use name voice, image. List covered berries charts licensing message! Reelin and rockin about, has everything you? Many exciting, photos him stage click! Artist check, out some quotes read all intriguing. It alive he, did on occasion birthday wednesday? Has everything you want to know this? Quotes read all intriguing life, in biography or. Gtgt getting licks years reelin and. Click here, music looking for lyrics maybe, youre. News celebrates his th rock will never! Chuck berry news celebrates his th rock, will. Browse through many exciting, photos him stage click here. He did on occasion birthday wednesday night at blueberry. Wallpapers featuring business cmg worldwide. Worldwide is exclusive estate we consider, request use name. Want to know this dynamic artist check out. Awards fast, facts clips list covered. Music looking for lyrics maybe youre. Message board downloads tour dates fan, club inquiries. Other sites, download free wallpapers featuring, business cmg worldwide. Request use, name, voice.

2 1

Multi-column update based on joined table
by Lee Hachadoorian 11 Aug '24

11 Aug '24

I need to update multiple columns (~250) from one table to another based on a two-column key. In Postgres I would write this using the FROM clause, but since MonetDB doesn't support the FROM clause, I've successfully updated one column with: UPDATE census.acs2010_5yr SET b07401001 = ( SELECT CAST(b07401001 AS int) FROM acs2010_5yr.tmp_seq0001 WHERE acs2010_5yr.stusab = tmp_seq0001.stusab AND acs2010_5yr.logrecno = tmp_seq0001.logrecno ); My question is whether there is any benefit to writing a multiple column update into a single UPDATE statement: UPDATE target_table SET column1 = (SELECT column1 FROM source_table WHERE target_table.key = source_table.key), column2 = (SELECT column2 FROM source_table WHERE target_table.key = source_table.key), ..., columnN = (SELECT columnN FROM source_table WHERE target_table.key = source_table.key); On Postgres I believe the multi-column update using a table join would only have to do the sequential scan of each table once, but with the necessity of sub-selects in MonetDB, what is the backend doing? Is it already doing an index scan once for each row in the single column case, then doing it once for each row * column in the multi-column case? Best, --Lee -- Lee Hachadoorian Asst Professor of Geography, Dartmouth College http://freecity.commons.gc.cuny.edu/

3 3

[MonetDB-users] XQuery API and XLink/XUpdate support
by Alexandra Mart�nez 27 Jun '24

27 Jun '24

Hello all, I was wondering if there is any documentation about the API for XQuery (i.e., how to write xqueries from a Java program). Also, I am interested in using some features of XLink and XUpdate on top of my XML database. Is support for any of these in progress? Thanks in advance, Alexandra ______________________________________________________ Click here to donate to the Hurricane Katrina relief effort. http://store.yahoo.com/redcross-donate3/

4 3

[MonetDB-users] Proofreading your professional documents
by Richard Hounslow 24 Feb '24

24 Feb '24

Dear Professor/Lecturer Please excuse us for contacting you directly. We are offering an efficient service to help you ensure your academic and professional work is written in perfect English. We will check the grammar and style of your work and return it to you to meet your requirements and deadlines. Proof-Reading-Service.com can provide you with a professional proofreading service at a very reasonable rate. All our proofreaders are highly qualified native English speakers. Many work as leading academics in their fields and all have extensive experience of proofreading to the highest standards. If you are interested in our service, please take a look at our website: www.proof-reading-service.com All you have to do is send us your document as a word attachment with the deadline and we will guarantee delivery of a perfectly written document to give you complete confidence when you submit your work. The fee is worked out on a flat rate (£6.95 per thousand words or 0.695 pence per word), so you know exactly how much the proofreading will cost in advance. Once we have received your document(s) we will confirm the word count, the price and the deadline. If you have not heard from us within 2 hours during normal business hours after you have sent your work please resend it from a different email account or send the file to proofreadings(a)gmail.com. We look forward to hearing from you! Yours faithfully Proof-Reading-Service.com

2 2

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