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 EGI-InSPIRE RI-261323
Outline 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 2 4/12/11 www.egi.eu EGI-InSPIRE RI-261323
LHC challenge 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
Persistency framework 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. POOL is a generic hybrid store COOL provides specific software to handle the for C++ objects, metadata time variation and versioning of conditions data. catalogues and collections, using s t re a m i n g a n d re l a t i o n a l CORAL is a generic abstraction layer with an technologies. SQL-free API for accessing relational databases. 4 4/12/11 www.egi.eu EGI-InSPIRE RI-261323
CORAL: introduction Common Relational Abstraction Layer (CORAL) is a C++ framework to access data in relational databases. What’s CORAL ? 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
Why CORAL? In the distributed data analysis model, there is an in- Why homogeneity in the data storage (database) infrastructure and security policies across the different CORAL ? institutes. 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 or CoralServer/CoralServerProxy server/cache system. 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. 6 4/12/11 www.egi.eu EGI-InSPIRE RI-261323
CORAL Backends 7 4/12/11 www.egi.eu EGI-InSPIRE RI-261323
CORAL Functionalities – Database indirection – Connection pooling / sharing – Reconnection – Data Definition Language (DDL) operations • Creating, altering tables, views • Multicolumn keys, indices and constraints • BLOB, fixed or variable-size string variables – Data Manipulation Languages (DML) operations • “Bulk” operations • Insert-select statements – Queries 8 4/12/11 www.egi.eu EGI-InSPIRE RI-261323
DB Connection Too many styles!!!....and it is necessary to specify username and password ???!!! � 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'; � 9 4/12/11 www.egi.eu EGI-InSPIRE RI-261323
DB connection in CORAL Have no fear daddy! …. � Here the way to deal with any database, without entering neither username or password!!!! � 1. dbname = ”sqlite_file://coralData.db” 2. dbname = “mysql://myhost/mydb” 3. dbname = ”oracle://oradb_name/schema_name” a. session = svc.connect(dbName, accessMode = coral.access_Update) b. session = svc.connect(’/my/database/schema’, accessMode = coral.access_ReadOnly) 10 4/12/11 www.egi.eu EGI-InSPIRE RI-261323
DB replica lookup and authentication <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> <connectionlist> </servicelist> <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> 11 4/12/11 www.egi.eu EGI-InSPIRE RI-261323
Behind the scenes • ConnectionService internal operations: – 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 • In case of failure the Connection Service tries the next replica • The user has a valid handle for performing DDL, DML, queries. 12 4/12/11 www.egi.eu EGI-InSPIRE RI-261323
SQL-free C++ API coral::ISchema& schema = session.nominalSchema(); � coral::TableDescription tableDescription; � tableDescription.setName( “T_t” ); � CORAL CORAL tableDescription.insertColumn( “I”, “int” ); � tableDescription.insertColumn( “X”, “double” ); � schema.createTable( tableDescription); � Schema.dropIfTableExists(“T_t”) � CREATE TABLE “T_t” (I CREATE TABLE T_t(I INT, � INTEGER,X BINARY_DOUBLE) � � � X DOUBLE � � � � PRECISION) � MySQL Oracle IF EXISTS(SELECT TABLE_NAME FROM SCHEMA_NAME.TABLES WHERE DROP TABLE [IF EXISTS] T_t � TABLE_NAME = “T_t”) DROP TABLE “T_t” � 13 4/12/11 www.egi.eu EGI-InSPIRE RI-261323
How to fill a table rowBuffer = coral.AttributeList() � CORAL CORAL table.dataEditor().rowBuffer( rowBuffer ) � rowBuffer["I"].setData(10) � rowBuffer["X"].setData(10000000) � MySQL Orcale INSERT INTO “T_t” (I, X) INSERT INTO T_t (I,X) VALUES (10,10000000) � VALUES(10,10000000) � 14 4/12/11 www.egi.eu EGI-InSPIRE RI-261323
Query and loop CORAL Oracle/MySQL query = schema.tableHandle SELECT * FROM T_t � (tableName).newQuery() � cursor = query.execute() � With CORAL it is easy to handle the loop on the data retrieved by the query. while ( cursor.next() ): � currentRow = cursor.currentRow() � i = currentRow["I"].data() � x = currentRow["X"].data() � 15 4/12/11 www.egi.eu EGI-InSPIRE RI-261323
Bulk operations 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 ) � BLOB (Binary Large Object) is a large bulkInserter = table.dataEditor collection of binary data stored in a database ().bulkInsert( rowBuffer, 3 ) table. rowBuffer["photo"].setData(blob) � The Oracle database enables use of bulk operation in order to reduce time needed for bulkInserter.flush() commands execution over a large amount of del bulkInserter data. 16 4/12/11 www.egi.eu EGI-InSPIRE RI-261323
Bind variables 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 of sending exactly the same SQL to Oracle every time the query is executed. Reducing the latch activity in the SGA BindVariableList = coral.AttributeList() � BindVariableList.extend("idvalue", typeid(int) ) � BindVariableList["idvalue"].data<int>()=1 � query.setCondition( "ID > :idvalue", BindVariableList ) � 17 4/12/11 www.egi.eu EGI-InSPIRE RI-261323
Oracle OCI Access 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++. CORAL implements OCI internally and allows the user to avoid a complex OCI implementations. 18 4/12/11 www.egi.eu EGI-InSPIRE RI-261323
Recommend
More recommend
