The official Metadata Access Interface for EGEE

AMGA to access existing
DataBases
Antonio Calanducci
INFN Catania
EGEE-II NA3 and NA4 Activities
I Corso di formazione INFN su aspetti pratici
dell'integrazione di applicazioni in GRID
Martina Franca (TA), 12-24th Nov 2007
www.ccr.infn.it
I Corso integrazione applicazioni Grid Martina Franca, 16 Nov 2007
http://grid.infn.it/
Contents
• AMGA Architecture refresh
• Grid DB Access with AMGA
• Example
www.ccr.infn.it
I Corso integrazione applicazioni Grid Martina Franca, 16 Nov 2007
http://grid.infn.it/
AMGA Implementation
• C++ multiprocess server
– Backends
Oracle, MySQL 4/5,
PostgreSQL, SQLite
– Front Ends
TCP text streaming
• High performance
• Client API for C++, Java,
Python, Perl, PHP
SOAP (web services)
• Interoperability
• Scalability
• AMGA server runs on SLC3/4,
Fedora Core, Gentoo, Debian
• Standalone Python
Library implementation
– Data stored on file system
www.ccr.infn.it
I Corso integrazione applicazioni Grid Martina Franca, 16 Nov 2007
http://grid.infn.it/
Existing DB access with AMGA
• Since AMGA 1.2.10, a new import feature allow to
access existing DB table
• Once imported into AMGA the tables from or more
DBs you want to access through AMGA, you can
exploit many of the features brought to you by
AMGA for your existing tables
• Advantages:
– your db tables can be accessed by grid users/applications,
using grid authentication (VOMS proxies)/authorization with
ACLs
– exploiting AMGA federation features you can access several
databases together from the Grid
www.ccr.infn.it
I Corso integrazione applicazioni Grid Martina Franca, 16 Nov 2007
http://grid.infn.it/
Set up AMGA to access your
tables
• To remember: AMGA stores its own tables in its
DB backend
• To access and existing DB you have 2 options:
 import the tables of the DB you want to access to into
AMGA DB backend
 viceversa, add AMGA DB backed tables to the DB you want
to access to
• Use the import command by root to “mount” you
table into the AMGA collection hierarchy
Query> whoami
>> root
Query> createdir /world
Query> cd /world/
Query> import world.City /world/City
Query> import world.Country /world/Country
Query> import world.CountryLanguage /world/CountryLanguage
www.ccr.infn.it
I Corso integrazione applicazioni Grid Martina Franca, 16 Nov 2007
http://grid.infn.it/
Option #2: Add AMGA tables to
the importing DB
• Create a DB schema to contain AMGA tables and grant
permissions to an amga (arda) user from the AMGA
server:
– [root@school18] mysqladmin -p create amga
– mysql> GRANT ALL ON amga.* TO 'arda'@'amga.gs.ba.infn.it';
• Import AMGA internal tables into the amga DB schema:
– [root@amga] mysql -u arda -h school18.gs.ba.infn.it amga < /opt/
glite/share/doc/glite-amga-server-1.3.0/createInitialMySQL5.sql
• Edit /etc/odbc.ini on the AMGA server to configure the
backend to use for AMGA:
•
– [MySchool18]
– Driver=MySQL
– User= arda
– Server= school18.gs.ba.infn.it
– Database = amga
– Port=3306
Edit /opt/glite/etc/amgad.config and configure DataSource to the previously definied DSN
– DataSource=MySchool18
www.ccr.infn.it
I Corso integrazione applicazioni Grid Martina Franca, 16 Nov 2007
http://grid.infn.it/
Set up AMGA to access your
tables
• Log in to AMGA with a client as root
• Use the import command by root to “mount” you table
into the AMGA collection hierarchy
–
–
–
–
–
–
–
–
–
–
–
–
–
–
–
Query> createdir /imdb
Query> cd /imdb
Query> import imdb.aka_name aka_name
Query> import imdb.aka_tile aka_title
Query> import imdb.cast_info cast_info
Query> import imdb.movie_info movie_info
Query> dir
>> /imdb/aka_name
>> collection
>> /imdb/aka_title
>> collection
>> /imdb/cast_info
>> collection
>> /imdb/movie_info
>> collection
www.ccr.infn.it
I Corso integrazione applicazioni Grid Martina Franca, 16 Nov 2007
http://grid.infn.it/
Set up permissions
• Properly set up authorization on the imported tables:
Query> acl_show /imdb
>> tcaland rwx
>> system:anyuser rx
Query> acl_remove /imdb system:anyuser
Query> acl_add /imdb gilda:users rx
Query> acl_show /imdb
>> tcaland rwx
>> gilda:users rx
• Let’s try some queries: DB example: The Internet Movie
Database (www.imdb.com)
– Query> selectattr title:title title:production_year
title:id 'like(title:title, "Pretty Woman")'
– Query> selectattr movie_info:info info_type:info
'movie_info:movie_id=362824 and info_type:info="plot"
and movie_info:info_type_id=info_type:id'
– Query> selectattr movie_info:info
'movie_info:movie_id=362824 and
info_type:info="soundtrack" and
movie_info:info_type_id=info_type:id'
www.ccr.infn.it
I Corso integrazione applicazioni Grid Martina Franca, 16 Nov 2007
http://grid.infn.it/
IMDB Database example
• Other values of info_type:info (you can try them)
– languages
– keywords
– goofs
– rating
– quotes
• Query> selectattr cast_info:person_role
name:name 'cast_info:movie_id=362824 and
name:id=cast_info:person_id order
cast_info:nr_order'
• Query> selectattr cast_info:person_role
name:name role_type:role
'cast_info:movie_id=362824 and
name:id=cast_info:person_id and
cast_info:role_id=role_type:id order
cast_info:nr_order'
www.ccr.infn.it
I Corso integrazione applicazioni Grid Martina Franca, 16 Nov 2007
http://grid.infn.it/
AMGA Replication and
Federation
Full replication
Federation
www.ccr.infn.it
Partial replication
Proxy
I Corso integrazione applicazioni Grid Martina Franca, 16 Nov 2007
http://grid.infn.it/
DB Access and Replication
AMGA slave
/
/movie
/storage
/movie/title
/movie/info
/actors
/storage/LFN
/movie/aka_title
/actors/name
/storage/SEs
/comments
/comments/users
/actors/info
/comments/info
AMGA
master
AMGA
master
AMGA
master
AMGA
master
MySQL DB
Movie Metadata
PostgreSQL DB
Storage
Oracle DB
Actors
PostgreSQL DB
User Comments
www.ccr.infn.it
I Corso integrazione applicazioni Grid Martina Franca, 16 Nov 2007
http://grid.infn.it/
References
• AMGA Web Site
http://cern.ch/amga
• AMGA Manual
http://amga.web.cern.ch/amga/downloads/amgamanual_1_3_0.pdf
• More information on existing DB access @:
http://amga.web.cern.ch/amga/importing.html
https://grid.ct.infn.it/twiki/bin/view/GILDA/AMGADBaccess
www.ccr.infn.it
I Corso integrazione applicazioni Grid Martina Franca, 16 Nov 2007
http://grid.infn.it/
Questions…
www.ccr.infn.it
I Corso integrazione applicazioni Grid Martina Franca, 16 Nov 2007
http://grid.infn.it/