Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-114

How to use EXASolution as Linked Server in MS SQL Server

    Details

    • Type: How To
    • Status: Obsolete
    • Affects Version/s: EXASOL 6.0.0, Exasol 6.1.0, Exasol 6.2.x
    • Fix Version/s: None
    • Component/s: 3rd Party Tools, ODBC
    • Labels:
      None
    • Solution:
      Hide

      Note: This solution is no longer maintained. For the latest information, please refer to our Knowledge Base:

      https://community.exasol.com/t5/connect-with-exasol/how-to-use-exasol-as-a-linked-server-in-sql-server/ta-p/362

      -------------------------------------------------------------------------------------------------------------------------------------------------

      Background

       

      "Linked Servers" in Microsoft SQl Server are what other database systems call "Database Links", "Database Gateway", "External Tables" or similar: It is a way to access contents from other systems to use in local processing. While other systems typically use ODBC or JDBC connectivity for this, microsoft mainly supports its own OLEdb connectivity. Luckily, Microsoft also provides an "OLEdb to ODBC bridge"...

      Using this, MS SQL Server can be configured to access data in EXASolution database systems, both for reading and for writing.

      Prerequisites

       

      • The EXASolution ODBC driver for windows, in the architecure (x86 / amd64) fitting the Sql Server installation. We recommend version 5 or higher to avoid some minor issues.
      • A properly configured ODBC data source (DSN), again with the correct bitness.
        • Provide a default schema if schema name and user name are not equal. The following setup will be limited to accessing tables in that schema
        • on the advanced tab, check the "Show only current schema" option. This will limit all catalog queries to one schema, eliminating "more than one table" errors later.
      • As with many other systems, EXASolution's database/catalog/schema concept is not 100% compatible with Sql Server, which is why we have to 'tune down' the according data provider to lower its expectations:
        • In the Sql Server Management Console, open the tree view to access "Linked Servers -> Data Providers -> MSDASQL". Use context menu to open its properties
        • Check the 'use level 0 only' option. This will reduce cross-checks on table metadata, especially catalog/schema names.

      Creating a Linked Server

      Step 1

      • In the tree view, use the context menu of "Linked Servers" to add a new server.

      Step 2

      • From the drop-down list, select "Microsoft OleDB for ODBC".

      Step 3

      • Enter a Linked Server name on top and the preconfigured ODBC DSN name below. Other fields are not required.

      Step 4

      • Click OK.

      Using the Linked Server

       

      • In the tree view you can now 'drill down' to table and view level on the remote database. This is all you will get in this view, due to limitations of the MSDASQL provider as stated in http://support.microsoft.com/kb/971261
      • In queries/views you will be able to access the EXASolution database objects using two different methods:
      • To execute LUA scripts stored in EXASolution from the Sql Server do the following steps:
        • Edit the properties of the the linked server. Set the parameter "RPC out" to True in the section "Server Options". This enables the execution of remote procedure calls.
        • Use the command EXEC ( 'execute script my_schema.my_lua_script' ) AT <link-name> to trigger the execution of LUA Scripts

       

      Additional Notes 

      Limitations:

      • When using three-dots-notation, Sql Server will always perform a full table select without any column selection or filters. Obviously, this is a performance killer for larger tables. When using views to transparently embed EXASolution into an Sql Server environment, you might want to use OPENQUERY or Views on EXASolution side instead.
      • The MSDASQL provider is limited in terms of VARCHAR size. If any column definition on EXASolution side exceeds VARCHAR(8191), this will result in an "unexpected catastrophic failure" on Sql Server side. The contents of the column are not even considered.
      • When sending data towards EXASolution (for UPDATE, INSERT or DELETE), Sql Server will send VARCHAR data for all parameters. For that reason, be very careful about your combination of language settings for numeric values. After all, in most cases there is some difference between '50.001' and '50,001'
      • Sql Server sets a QUERY TIMEOUT of 30 seconds on INSERT statements. This breaks larger transfers to EXASolution, as our query timeout affects the whole prepared statement, not single execs as expected.

      Performance:

      The following measurements are done on a single machine, using Sql Server 2012 Express and a local VM running a single-node EXASolution 5.0.rc2. The benchmark consists of transferring the 1 million row SUPPLIER table of the TPC-H scenario between both systems, target table always truncated before the run.

        Sql Server EXASolution
      Sql -> EXA
      insert into LOCALVM...SUPPLIER
      select * from sr_test.dbo.SUPPLIER

      Duration: 600 seconds (estimated)

      import into tpc.supplier
      from JDBC at 'jdbc:jtds:sqlserver://169.254.104.1;databaseName=sr_test'
      user 'sa' identified by ***
      table SUPPLIER;
      

      Duration: 30 seconds

      Sql <- EXA
      insert into sr_test.dbo.SUPPLIER
      select * from LOCALVM...SUPPLIER;

      Duration: 87 seconds

      export tpc.supplier
      into JDBC at CONN_SQLEXPRESS -- same as above, but in a connection object
      table SUPPLIER;
      

      Duration: 40 seconds

      The Sql -> EXA export using a Linked Server could only be estimated because of the 30-second timeout.

      Stored Procedures (aka. EXECUTE SCRIPT)

      The above methods all rely on prepared statements, which is not compatible with EXASolution's Lua scripting. If you need to trigger script execution remotely from the SQL Server, you need to use the RPC functionality:

      1. In the properties of your linked server (your EXASolution), go to the page 'Server Options' and enable the 'RPC Out' feature
      2. You can now use SQL Server's EXECUTE command to run arbitrary statements on the linked server and receive the results for further local processing:
      EXECUTE ('execute script sr_test.LuaTest(1,2,3)') AT LOCALVM

       

       

      Show
      Note: This solution is no longer maintained. For the latest information, please refer to our Knowledge Base: https://community.exasol.com/t5/connect-with-exasol/how-to-use-exasol-as-a-linked-server-in-sql-server/ta-p/362 ------------------------------------------------------------------------------------------------------------------------------------------------- Background   "Linked Servers" in Microsoft SQl Server are what other database systems call "Database Links", "Database Gateway", "External Tables" or similar: It is a way to access contents from other systems to use in local processing. While other systems typically use ODBC or JDBC connectivity for this, microsoft mainly supports its own OLEdb connectivity. Luckily, Microsoft also provides an "OLEdb to ODBC bridge"... Using this, MS SQL Server can be configured to access data in EXASolution database systems, both for reading and for writing. Prerequisites   The EXASolution ODBC driver for windows, in the architecure (x86 / amd64) fitting the Sql Server installation. We recommend version 5 or higher to avoid some minor issues. A properly configured ODBC data source (DSN), again with the correct bitness. Provide a default schema if schema name and user name are not equal. The following setup will be limited to accessing tables in that schema on the advanced tab, check the "Show only current schema" option. This will limit all catalog queries to one schema, eliminating "more than one table" errors later. As with many other systems, EXASolution's database/catalog/schema concept is not 100% compatible with Sql Server, which is why we have to 'tune down' the according data provider to lower its expectations: In the Sql Server Management Console, open the tree view to access "Linked Servers -> Data Providers -> MSDASQL". Use context menu to open its properties Check the 'use level 0 only' option. This will reduce cross-checks on table metadata, especially catalog/schema names. Creating a Linked Server Step 1 In the tree view, use the context menu of "Linked Servers" to add a new server. Step 2 From the drop-down list, select "Microsoft OleDB for ODBC". Step 3 Enter a Linked Server name on top and the preconfigured ODBC DSN name below. Other fields are not required. Step 4 Click OK. Using the Linked Server   In the tree view you can now 'drill down' to table and view level on the remote database. This is all you will get in this view, due to limitations of the MSDASQL provider as stated in http://support.microsoft.com/kb/971261 In queries/views you will be able to access the EXASolution database objects using two different methods: Three-dots-notation <link-name>...<object_name> will allow you to use objects in native SQL, Sql Server will exchange data with EXASolution as necessary (see Limitations below). OPENQUERY (see http://msdn.microsoft.com/en-us/library/ms188427.aspx ). To execute LUA scripts stored in EXASolution from the Sql Server do the following steps: Edit the properties of the the linked server. Set the parameter "RPC out" to True in the section "Server Options". This enables the execution of remote procedure calls. Use the command EXEC ( 'execute script my_schema.my_lua_script' ) AT <link-name> to trigger the execution of LUA Scripts   Additional Notes  Limitations: When using three-dots-notation, Sql Server will always perform a full table select without any column selection or filters. Obviously, this is a performance killer for larger tables. When using views to transparently embed EXASolution into an Sql Server environment, you might want to use OPENQUERY or Views on EXASolution side instead. The MSDASQL provider is limited in terms of VARCHAR size. If any column definition on EXASolution side exceeds VARCHAR(8191), this will result in an "unexpected catastrophic failure" on Sql Server side. The contents of the column are not even considered. When sending data towards EXASolution (for UPDATE, INSERT or DELETE), Sql Server will send VARCHAR data for all parameters. For that reason, be very careful about your combination of language settings for numeric values. After all, in most cases there is some difference between '50.001' and '50,001' Sql Server sets a QUERY TIMEOUT of 30 seconds on INSERT statements. This breaks larger transfers to EXASolution, as our query timeout affects the whole prepared statement, not single execs as expected. Performance: The following measurements are done on a single machine, using Sql Server 2012 Express and a local VM running a single-node EXASolution 5.0.rc2. The benchmark consists of transferring the 1 million row SUPPLIER table of the TPC-H scenario between both systems, target table always truncated before the run.   Sql Server EXASolution Sql -> EXA insert into LOCALVM...SUPPLIER select * from sr_test.dbo.SUPPLIER Duration: 600 seconds (estimated) import into tpc.supplier from JDBC at 'jdbc:jtds:sqlserver://169.254.104.1;databaseName=sr_test' user 'sa' identified by *** table SUPPLIER; Duration: 30 seconds Sql <- EXA insert into sr_test.dbo.SUPPLIER select * from LOCALVM...SUPPLIER; Duration: 87 seconds export tpc.supplier into JDBC at CONN_SQLEXPRESS -- same as above, but in a connection object table SUPPLIER; Duration: 40 seconds The Sql -> EXA export using a Linked Server could only be estimated because of the 30-second timeout. Stored Procedures (aka. EXECUTE SCRIPT) The above methods all rely on prepared statements, which is not compatible with EXASolution's Lua scripting. If you need to trigger script execution remotely from the SQL Server, you need to use the RPC functionality: In the properties of your linked server (your EXASolution), go to the page 'Server Options' and enable the 'RPC Out' feature You can now use SQL Server's EXECUTE command to run arbitrary statements on the linked server and receive the results for further local processing: EXECUTE ( 'execute script sr_test.LuaTest(1,2,3)' ) AT LOCALVM    
    • Category 1:
      3rd Party Tools
    • Category 2:
      Clients, Interfaces & Drivers - ODBC

      Attachments

        Issue Links

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              1 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: