EGI- FORUM Vilnius 2001 CORAL A Relational Abstraction Layer for - - PowerPoint PPT Presentation
EGI-InSPIRE EGI- FORUM Vilnius 2001 CORAL A Relational Abstraction Layer for C++ and Python applications Raffaello Trentadue On behalf of the IT-ES Persistency Framework group 1 4/12/11 www.egi.eu www.egi.eu EGI-InSPIRE RI-261323
www.egi.eu EGI-InSPIRE RI-261323
www.egi.eu EGI-InSPIRE RI-261323
4/12/11 1
Raffaello Trentadue On behalf of the IT-ES Persistency Framework group
www.egi.eu EGI-InSPIRE RI-261323
4/12/11 2
The context where CORAL was designed and implemented
CORAL INTRODUCTION What’s CORAL Why CORAL? CORAL in practice DB connection Table Creation/Filling/Deletion Query CORAL STRUCTURE CORAL back-ends Why should a user use CORAL? CONCLUSIONS
www.egi.eu EGI-InSPIRE RI-261323
Enormous amount of data to be
processed and analyzed (hundred petabytes over the whole lifetime). Impossible to implement a unique CERN analysis facility Boost of the development of the grid computing infrastructure and technology. A distributed analysis model implies an in-homogeneity in the data storage infrastructure across the different institutes and across the long LHC lifetime. The computing infrastructure has to be easily maintainable and adaptable.
3 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
In three of the experiments (ATLAS, CMS and LHCb), some types of data are stored and accessed using the software developed by the Persistency Framework (PF) within the Application Area (AA) of the LHC Computing Grid (LCG) to find common solutions for the LHC experiments.
COOL provides specific software to handle the
time variation and versioning of conditions data.
POOL is a generic hybrid store
for C++ objects, metadata catalogues and collections, using s t re a m i n g a n d re l a t i o n a l technologies. CORAL is a generic abstraction layer with an SQL-free API for accessing relational databases.
4 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
Common Relational Abstraction Layer (CORAL) is a C++ framework to access data in relational databases. The C++ API of CORAL consists of a set of SQL-free abstract interfaces that isolate the user code from the database implementation technology.
Python bindings of the API are also available.
5 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
In the distributed data analysis model, there is an in- homogeneity in the data storage (database) infrastructure and security policies across the different institutes. Users write the same code for all back-ends A detailed knowledge of the many SQL flavors is not required The SQL commands specific to each backend are executed by the relevant CORAL libraries, which are loaded at run-time by a special plugin infrastructure. CORAL provides a set of C++ libraries for several database back-ends: local access to SQLite files; direct client access to Oracle and MySQL servers; read-only access to Oracle through the FroNTier/Squid
server/cache system.
6 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
7 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
– Database indirection – Connection pooling / sharing – Reconnection – Data Definition Language (DDL) operations
– Data Manipulation Languages (DML) operations
– Queries
8 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
Using cx_Oracle python package:
conn = cx_Oracle.Connection("user/ password@server")
Using SQLPlus:
CONNECT <user_name>/<password>@<service_name>
Using MySQL:
mysql –u USERNAME -h DBSERVER -p DBNAME EXEC SQL CONNECT TO 'db' AS ’dbname' USER 'ident' USING 'pswd';
Too many styles!!!....and it is necessary to specify username and password ???!!!
9 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
coral.access_Update)
coral.access_ReadOnly) Have no fear daddy! …. Here the way to deal with any database, without entering neither username
10 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
<connectionlist> <connection name=”oracle://oradb_name/schema_name”> <parameter name=”user” value=”schema_reader” /> <parameter name=”password” value=”a_passwd” /> </connection> <connection name=”mysql://myhost/mydb”> <parameter name=”user” value=”db_user” /> <parameter name=”password” value=”my_passwd” /> </connection> </connectionlist> <servicelist> <logicalservice name=”/my/service”> <service name=”mysql://mydb/db” accessMode=”Update” authentication=”password”/> <service name=”oracle://db1/sch” accessMode=”Read” authentication=”certificate”/> <service name=”sqlite_file:data.db” accessMode=”Read” authentication=”password”/> </logicalservice> </servicelist>
11 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
– What are the available replicas for “/my/conditions/data” – The Lookup Service will reply “oracle://host/schema”, ... – The Relational Service will load the oracle plug-in” – The Authentication Service will return user name and password corresponding to “oracle://host/schema”. – The oracle plug-in will connect and authenticate
12 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
coral::ISchema& schema = session.nominalSchema(); coral::TableDescription tableDescription; tableDescription.setName( “T_t” ); tableDescription.insertColumn( “I”, “int” ); tableDescription.insertColumn( “X”, “double” ); schema.createTable( tableDescription); Schema.dropIfTableExists(“T_t”) CREATE TABLE “T_t” (I INTEGER,X BINARY_DOUBLE) IF EXISTS(SELECT TABLE_NAME FROM SCHEMA_NAME.TABLES WHERE TABLE_NAME = “T_t”) DROP TABLE “T_t” CREATE TABLE T_t(I INT,
DROP TABLE [IF EXISTS] T_t
Oracle CORAL MySQL CORAL
13 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
rowBuffer = coral.AttributeList() table.dataEditor().rowBuffer( rowBuffer ) rowBuffer["I"].setData(10) rowBuffer["X"].setData(10000000) INSERT INTO “T_t” (I, X) VALUES (10,10000000) INSERT INTO T_t (I,X) VALUES(10,10000000)
MySQL Orcale CORAL CORAL
14 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
while ( cursor.next() ): currentRow = cursor.currentRow() i = currentRow["I"].data() x = currentRow["X"].data() query = schema.tableHandle (tableName).newQuery() cursor = query.execute() SELECT * FROM T_t
Oracle/MySQL CORAL With CORAL it is easy to handle the loop on the data retrieved by the query.
15 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
BLOB (Binary Large Object) is a large collection of binary data stored in a database table. The Oracle database enables use of bulk
commands execution over a large amount of data.
data = open("res/london.jpg", "rb").read() blob = coral.Blob() cPickle.dump(data,blob,1) description.insertColumn( "photo", "blob") table = schema.createTable ( description ) rowBuffer = coral.AttributeList() table.dataEditor().rowBuffer ( rowBuffer ) bulkInserter = table.dataEditor ().bulkInsert( rowBuffer, 3 ) rowBuffer["photo"].setData(blob) bulkInserter.flush() del bulkInserter
4/12/11 16
www.egi.eu EGI-InSPIRE RI-261323
BindVariableList = coral.AttributeList() BindVariableList.extend("idvalue", typeid(int) ) BindVariableList["idvalue"].data<int>()=1 query.setCondition( "ID > :idvalue", BindVariableList )
Oracle first checks in the shared pool to see whether the query statement has been submied before. Bind variables are «substitution» variables that are used in place of literals and that have the effect
time the query is executed. Reducing the latch activity in the SGA
4/12/11 17
www.egi.eu EGI-InSPIRE RI-261323
Te Oracle Call Interface (OCI) is an application programming interface (API) which allows developers to build applications using low-level C and C++ function calls to access an Oracle database server. Similarly, not only does OCI allow users to control all the aspects of SQL statement execution, but it also fully supports the data-types, calling conventions, syntax, and semantics of C and C++.
18
CORAL implements OCI internally and allows the user to avoid a complex OCI implementations.
4/12/11
www.egi.eu EGI-InSPIRE RI-261323
A middle-tier server introduced b e t w e e n C O R A L c l i e n t applications and the Oracle database servers.
Client applications connect to the “CORAL server” through a new dedicated CoralAccess plugin, while the CORAL server, itself a CORAL application, would connect to the Oracle database server inside the site firewall.
19 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
The FronNTier client converts the SQL into an HTTP GET and sends it over the network to the ForNTier server. The FroNTier server unpacks the SQL request, sends it to the DB server, and retrieves the needed data. The data is optionally compressed, then packed into an HTTP formaed stream and sent back to the client. Squid proxy/caching servers between the FroNTier server and the client caches requested objects, significantly improving performance and greatly reducing the load on the central database.
20 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
authenticated and authorized using their proxy certificates. Access from several concurrent clients to the same Oracle schemas could be multiplexed through a single connection from the server to the database, reducing the load on the laer.
tier between the clients and the server, to provide data caching and further multiplexing for read-only use cases.
21 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
The CORAL framework is at the moment crucial for reading the condition data of almost all the LHC experiments. The reason of its success is in:
relational database;
database indirection to replicas support for Frontier/Coral server Furthermore, the user does not need to be an expert in all the possible database techniques, as they are already implemented in the backend accessing the plugins. These features allow CORAL to address the needs of any environment outside of High Energy Physics where safe access to a relational database is required, especially if in the context of a distributed analysis.
22 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
The software is by now mature in its development cycle, but a large development and support effort is still required for user support, service operation and maintenance tasks. Regular production releases are prepared whenever one of the LHC experiments demands it, leading to one release per month on average. The CORAL software is flexible and could be deployed in any environment where the access to relational databases is necessary, in particular in a distributed analysis. The user does not need almost any knowledge about SQL.
23 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
24 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
25 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
CoralBase Relational Access XML Authentication Service XML Lookup Service LFC Replica Service Connection Service Oracle Access MySQL Access SQLite Access Coral Access Frontier Access PyCoral Tests API are implemented as abstract classes Access point to CORAL CORAL backends CoralKernel CoralCommon
26 4/12/11
www.egi.eu EGI-InSPIRE RI-261323
The main interface which is used for all the data exchange between a CORAL-based software component and a relational database is the AributeList class. It is a container of Aributes, which are named variables of simple types. The AributeList can therefore be thought of as a buffer holding the data of a table
4/12/11 27
www.egi.eu EGI-InSPIRE RI-261323
SQL is the interface that enables access to data within a database. PL/SQL is a procedural expansion of SQL programming language, created by Oracle. It enables cyclic passing through records, processing exceptions, working with variables, parameters, collections, records, sequences, cursors etc. SQL sends inquiries to SQL engine which executes the inquiry and in some cases returns data to PL/SQL engine. This process is called context switching. Every switch of context between PL/SQL engine and SQL engine reduces performances. The best solution is use of bulk operations.
4/12/11 28