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
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
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.
> -----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
> >>>
> >>>
> >
> >
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
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
Hi Martin,
Thank you for your quick response.
I was hoping to find a way to use 'data.frame' in MonetDB's SQL with embedded R functions.
Column oriented RDBMS with integrated/embedded R are serious contenders in the (Big) Data Science arena.
Kind regards,
Bart
