SQLMED AND MORE Management of External Database Seminar

SQL/MED AND MORE Management of External Database Seminar HS 11/12

Overview Introduction SQL/MED Linking Postgre. SQL & MSSQL Further Information (about SQL/MED) Conclusion

Introduction (1/2) Different Database Managemenent Systems Each system has different benefits Possible scenarios �… �…

Introduction (2/2) SQL/MED gives new opportunities Use other systems as needed That’s possible? Really?

SQL/MED (1/3) SQL/MED defined in ISO/IEC 9075 -9: 2003 � Management of External Data Two concepts � Foreign Data Wrappers � Datalinks At least 10 years old � Not very widespread � Most “googled” information is Postgre. SQL related

SQL/MED (2/3) Foreign Data Wrappers Access external data FDW is a library � Programming language neutral � Compile for different OS’s Good idea – breakthrough? � API � Existing technologies Advance. Init. Request Alloc. Descriptor Alloc. Query. Context Alloc. Wrapper. Env Close Connect. Server Free. Descriptor Free. Execution. Handle Free. FSConnection Free. Query. Context Free. Reply. Handle Free. Wrapper. Env Get. Authorization. Id Get. Bool. VE Get. Descriptor Get. Diagnostics Get. Distinct Get. Next. Reply Get. Num. Bool. VE Get. Num. Children Get. Num. Order. By. Elems Get. Num. Reply. Bool. VE Get. Num. Reply. Order. By Get. Num. Reply. Select. Elems Get. Num. Reply. Table. Refs Get. Num. Rout. Map. Opts Get. Num. Select. Elems Get. Num. Server. Opts Get. Num. Table. Col. Opts Get. Num. Table. Ref. Elems Get. Num. User. Opts Get. Num. Wrapper. Opts Get. Order. By. Elem Get. Reply. Bool. VE Get. Reply. Cardinality Get. Reply. Distinct Get. Reply. Exec. Cost Get. Reply. First. Cost Get. Reply. Order. Elem Get. Reply. Re. Exec. Cost Get. Reply. Select. Elem Get. Reply. Table. Ref Get. Routine. Mapping Get. Rout. Map. Opt. Name Get. Select. Elem. Type Get. Server. Name Get. Server. Opt. By. Name Get. Server. Type Get. Server. Version Get. SPDHandle Get. SQLString Get. SRDHandle Get. Statistics Get. Table. Col. Opt. By. Nam e Get. Table. Opt. By. Name Get. Table. Ref. Elem. Type Get. Table. Ref. Table. Nam e Get. Table. Server. Name Get. TRDHandle Get. User. Opt. By. Name Get. Val. Expr. Col. Name Get. Value. Exp. Desc Get. Value. Exp. Kind Get. Value. Exp. Name Get. Value. Exp. Table Get. VEChild Get. WPDHandle Get. Wrapper. Library. Na me Get. Wrapper. Name Get. Wrapper. Opt. By. Nam e Get. WRDHandle Init. Request Iterate Open Re. Open Set. Descriptor Transmit. Request

SQL/MED (3/3) Data links Link files like cell values DBMS becomes “manager” � Only process allowed to change the file � Integrity mechanism Good idea – breakthrough? � Very OS heavy � Existing technologies

Linking Postgre. SQL & MSSQL (1/4) Microsoft Linked Servers SQL/MED: Foreign Data Wrappers

Linking Postgre. SQL & MSSQL (2/4) Microsoft Linked Servers OLE DB � Very similar to Foreign Data Wrappers � Connection to "wrappers" via interface Related to ODBC � � Not limited to SQL C++ instead of C Widespread � � Many OLE DB providers available Supports ODBC

Linking Postgre. SQL & MSSQL (3/4) Postgre. SQL Foreign Data Wrappers (1/2) Using the OBDC_FDW extension One time odbc_fdw. so CREATE FOREIGN DATA WRAPPER odbc_fdw LIBRARY 'odbc_fdw. so‘; CREATE EXTENSION odbc_fdw; Each time CREATE SERVER odbc_server FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn ‘…DSN…'); passed to FDW CREATE FOREIGN TABLE odbc_table ( db_id integer, SELECT db_name varchar(255) ) SERVER odbc_server OPTIONS (… sql_query 'select id, name from `dbo`. `table`' …); passed to FDW

Linking Postgre. SQL & MSSQL (4/4) Postgre. SQL Foreign Data Wrappers (2/2) Postgre. SQL proprietary API for FDWs ‘C’ Code � Method pointer in header odbc_fdw_handler(PG_FUNCTION_ARGS) { Fdw. Routine *fdwroutine = make. Node(Fdw. Routine); fdwroutine->Plan. Foreign. Scan = odbc. Plan. Foreign. Scan; fdwroutine->Explain. Foreign. Scan = odbc. Explain. Foreign. Scan; fdwroutine->Begin. Foreign. Scan = odbc. Begin. Foreign. Scan; fdwroutine->Iterate. Foreign. Scan = odbc. Iterate. Foreign. Scan; fdwroutine->Re. Scan. Foreign. Scan = odbc. Re. Scan. Foreign. Scan; fdwroutine->End. Foreign. Scan = odbc. End. Foreign. Scan; PG_RETURN_POINTER(fdwroutine); }

Further Information about SQL/MED (1/4) Query costs Interesting applications

Further Information about SQL/MED Query costs (1/2) Consider the following tables 200 500 Row count of a JOIN statement (all employees) � Best case 500 rows � Worst case 100’ 000 rows Best execution strategy � External system performs JOIN? � Perform JOIN locally?

Further Information about SQL/MED Query costs (2/2) SQL Server Costs � 1. 0 $ per transferred row � 0. 1 $ per local join operation Plan. Foreign. Sc an FDW External System Strategy #A � SELECT * FROM Employee JOIN City 100’ 000$ Strategy #B � SELECT * FROM Employee Worst Case 500$ � SELECT * FROM City Scenario 200$ � Clear win for #A � Important to implement Plan. Foreign. Scan Local JOIN 500 x 200 = 10’ 000$

Further Information about SQL/MED Interesting applications Extension www_fdw to query all Restful Webservices CREATE SERVER google_server FOREIGN DATA WRAPPER www_fdw OPTIONS (uri 'https: //ajax. googleapis. com/search/web? v=1. 0'); CREATE FOREIGN TABLE google_table ( title text, snippet text, link text, q text) SERVER google_server; Field legend • Response • Request select * from google_table where q =’cat dog’ limit 1; title | snippet | link ----------+--------------------------Cat. Dog – Wikipedia | Cat. Dog is an American. . . | http: //en. wikipedia. . .

Conclusion Great concepts FDW: Accessing external data via standard interfaces � Datalink: Create secure links from tuples to files � Drawbacks which prevent the breakthrough Far too complex API � Existing technologies (Microsoft, Oracle) � Documentation � Do we really need it? � Most environments are based on 1 server technology � Use built-in "MED" (Linked Servers, DBLink) Other ways to solve problems Many years to stable release

Outlook Relies on community � Stable wrappers needed � Other DBMS need to push it Uncertain future

The End Questions?