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

IMPORT/EXPORT with Microsoft SQL Server

    Details

    • Type: How To
    • Status: Obsolete
    • Affects Version/s: EXASolution 5.0, EXASOL 6.0.0, Exasol 6.1.0, Exasol 6.2.x
    • Fix Version/s: None
    • Component/s: EXAloader
    • Solution:
      Hide

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

      https://community.exasol.com/t5/connect-with-exasol/best-practices-for-import-export-with-sql-server/ta-p/215

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

      Background

      Which JDBC driver for SQL Server shall I use?

      Exasol is shipped with the open source JDBC driver JTDS.

      Prerequisites

      Have a look at EXAoperation to see all predefined JDBC drivers:

       

      It's also possible to configure and upload the original Microsoft JDBC driver:

       

      Please use the following parameters:

      • Source: Path to the MSSQL driver (sqljdbc4.jar)
      • Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
      • Prefix: jdbc:sqlserver:
      • Name: MSSQL

      We recommend the JTDS driver for best performance, but:
      If you do single loads with more than 4,294,967,295 rows, you should use the MSSQL native JDBC driver due to a bug in JTDS (note that the number 4,294,967,295 is the maximum value for a 32-bit unsigned integer). If you used JTDS for such big loads, only 4,294,967,295 rows would be imported, and the rest would be ignored without seeing an error.
      See also SOL-108 if you want to use the MSSQL native JDBC driver.

      How to import with Microsoft SQL Server

      Step 1. CREATE CONNECTION

      In EXAplus execute the following query to create a connection object in EXASolution:

      CREATE CONNECTION sql_server_jdbc_con_jtds
      TO 'jdbc:jtds:sqlserver://10.78.0.178:1433/EXATEST'
      USER 'exatest'
      IDENTIFIED BY 'test';
      

      It is also possible to use Windows Authentication in conjunction with this driver. In order to enable this authentication, simply add the parameters "useNTLMv2=true" and "domain=[Domain name]", like so:

      CREATE CONNECTION sql_server_jdbc_con_jtds
      TO 'jdbc:jtds:sqlserver://10.78.0.178:1433;DatabaseName=EXATEST;domain=AD;useNTLMv2=true;'
      USER 'username' -- Windows Username
      IDENTIFIED BY 'AD password here' --Windows password;
      

      Once the AD user/password are defined in the database connection (USER '' IDENTIFIED BY ''), they can be re-used as often as needed (as long as the credentials are valid). Please note that the passwords are masked in all SQL texts and logs. With this method, you can grant the connection only to the required users on EXASOL side and it can be used to IMPORT data from SQL server. 

      Step 2. Write IMPORT/EXPORT Statement

      Test the connectivity by querying the SQL Server system catalog, e.g.:

      select * from 
      (
      import from jdbc at sql_server_jdbc_con_jtds
      statement 'select * from information_schema.tables'
      
      );
      

      If the query returns a resultset, you are ready to IMPORT some tables.
      Otherwise please check your network settings and/or the settings of your SQL Server.

      NOTE: As of Version 6.1, you will need the system privilege IMPORT to perform the statement.

      Step 3. Import data from SQL Server

      Now you can IMPORT/EXPORT data from the SQL Server, e.g.:

      import into MYTABLE  from jdbc at sql_server_jdbc_con_jtds
      statement 'select * from [dbo].[Table_1]';
      
      export MYTABLE into jdbc at sql_server_jdbc_con_jtds table [dbo].[Table_1];
      
      
      Show
      Note: This solution is no longer maintained. For the latest information, please visit our Knowledge Base: https://community.exasol.com/t5/connect-with-exasol/best-practices-for-import-export-with-sql-server/ta-p/215 ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Background Which JDBC driver for SQL Server shall I use? Exasol is shipped with the open source JDBC driver JTDS . Prerequisites Have a look at EXAoperation to see all predefined JDBC drivers:   It's also possible to configure and upload the original Microsoft JDBC driver:   Please use the following parameters: Source: Path to the MSSQL driver (sqljdbc4.jar) Class: com.microsoft.sqlserver.jdbc.SQLServerDriver Prefix: jdbc:sqlserver: Name: MSSQL We recommend the JTDS driver for best performance, but: If you do single loads with more than 4,294,967,295 rows, you should use the MSSQL native JDBC driver due to a bug in JTDS (note that the number 4,294,967,295 is the maximum value for a 32-bit unsigned integer). If you used JTDS for such big loads, only 4,294,967,295 rows would be imported, and the rest would be ignored without seeing an error. See also SOL-108 if you want to use the MSSQL native JDBC driver. How to import with Microsoft SQL Server Step 1. CREATE CONNECTION In EXAplus execute the following query to create a connection object in EXASolution: CREATE CONNECTION sql_server_jdbc_con_jtds TO 'jdbc:jtds:sqlserver://10.78.0.178:1433/EXATEST' USER 'exatest' IDENTIFIED BY 'test' ; It is also possible to use Windows Authentication in conjunction with this driver. In order to enable this authentication, simply add the parameters "useNTLMv2=true" and "domain= [Domain name] ", like so: CREATE CONNECTION sql_server_jdbc_con_jtds TO 'jdbc:jtds:sqlserver://10.78.0.178:1433;DatabaseName=EXATEST; domain =AD;useNTLMv2= true ;' USER 'username' -- Windows Username IDENTIFIED BY 'AD password here' --Windows password ; Once the AD user/password are defined in the database connection (USER '' IDENTIFIED BY ''), they can be re-used as often as needed (as long as the credentials are valid). Please note that the passwords are masked in all SQL texts and logs. With this method, you can grant the connection only to the required users on EXASOL side and it can be used to IMPORT data from SQL server.  Step 2. Write IMPORT/EXPORT Statement Test the connectivity by querying the SQL Server system catalog, e.g.: select * from ( import from jdbc at sql_server_jdbc_con_jtds statement ' select * from information_schema. tables ' ); If the query returns a resultset, you are ready to IMPORT some tables. Otherwise please check your network settings and/or the settings of your SQL Server. NOTE: As of Version 6.1, you will need the system privilege IMPORT to perform the statement. Step 3. Import data from SQL Server Now you can IMPORT/EXPORT data from the SQL Server, e.g.: import into MYTABLE from jdbc at sql_server_jdbc_con_jtds statement ' select * from [dbo].[Table_1]' ; export MYTABLE into jdbc at sql_server_jdbc_con_jtds table [dbo].[Table_1];
    • Category 1:
      ETL / Data Integration
    • Category 2:
      SQL

      Attachments

      1. EXAoperation_Add_JDBC_Driver.PNG
        45 kB
        Thomas Bestfleisch
      2. EXAoperation_Overview_JDBC_Drivers.PNG
        80 kB
        Thomas Bestfleisch

        Issue Links

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved: