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@gmail.com
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@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@gmail.com
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
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:
Build MonetDB with FITS support
Create SQL functions for FIST data loading
Attach a FIST file for loading
Load the data from the file
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
- 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;
- 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;
- 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@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@gmail.com
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
On 23/02/15 12:00, Jiří Nádvorník 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?
Depends on how it is stored in Postgresql. If you use blobs it might be tricky, but you could try an COPY INTO/FROM pair.
I don't really need to work with the files as such - it is just means of holding the data for me.
Are you using the foreign file wrapper functionality of Postgresql? regards, Martin
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:
Build MonetDB with FITS support
Create SQL functions for FIST data loading
Attach a FIST file for loading
Load the data from the file
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
- 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;
- 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;
- 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@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@gmail.com
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Try this link http://pgsnaga.blogspot.ie/2013/07/monetdbfdw-postgresql-meets-column.html
for using monetdb fdw in postgresql , not sure about the performance yet . One advantage here would be that you can directly work on monetdb tables directly from Postgresql so your application/security stuff can point Postgresql and let Postgresql interact with monetdb using fdw in the backend
On Mon, Feb 23, 2015 at 12:22 PM, Martin Kersten martin@monetdb.org wrote:
On 23/02/15 12:00, Jiří Nádvorník 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?
Depends on how it is stored in Postgresql. If you use blobs it might be
tricky, but you could try an COPY INTO/FROM pair.
I don't really need to work with the files as such - it is just means of
holding the data for me.
Are you using the foreign file wrapper functionality of Postgresql? regards, Martin
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:
Build MonetDB with FITS support
Create SQL functions for FIST data loading
Attach a FIST file for loading
Load the data from the file
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
- 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;
- 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;
- 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@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@gmail.com
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hi Martin,
Are you using the foreign file wrapper functionality of Postgresql? regards, Martin
I am not, actually I am using the DaCHS server and database ingestion package from GAVO (http://soft.g-vo.org/dachs) which is built on top of PostgreSQL.
Kind regards,
Jiri N.
-----Original Message----- From: users-list [mailto:users-list- bounces+nadvornik.ji=gmail.com@monetdb.org] On Behalf Of Martin Kersten Sent: 23. února 2015 13:23 To: users-list@monetdb.org Subject: Re: MonetDB + FITS
On 23/02/15 12:00, Jiří Nádvorník 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?
Depends on how it is stored in Postgresql. If you use blobs it might be tricky, but you could try an COPY INTO/FROM pair.
I don't really need to work with the files as such - it is just means of holding
the data for me. Are you using the foreign file wrapper functionality of Postgresql? regards, Martin
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:
Build MonetDB with FITS support
Create SQL functions for FIST data loading
Attach a FIST file for loading
Load the data from the file
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
- 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;
- 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;
- 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@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@gmail.com
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (4)
-
Dimitar Nedev -
Jiří Nádvorník -
Martin Kersten -
shamsul hassan