Load data from Exasol

This section explains how to connect another Exasol database using the native EXA or JDBC interface.

This article uses the following terminology:

  • Target database: The database which you are loading data into. New data will be loaded into this database.
  • Source database: The database which contains the data you want to load. The data from the source database is loaded into the target database.

You can load data using a "pull" method or a "push" method. The "pull" method uses the IMPORT FROM EXA or IMPORT FROM JDBC syntax to pull data from the source database into the target database. These commands are executed on the target database. Refer to the IMPORT statement for more details.

The "push" method, on the other hand, uses the EXPORT INTO EXA or EXPORT INTO JDBC syntax to push data from the source database into the target database. These commands are executed on the source database. Refer to the EXPORT statement for more details.

Using the native EXA interface is faster than the JDBC interface.

Restrictions

The following restrictions currently apply

  • Loading data between two SaaS databases is not yet supported.
  • Loading data using the IMPORT FROM JDBC syntax on the SaaS database (target database) is not yet supported.
  • To load data into a SaaS database, you must run an IMPORT FROM EXA command on your target database. Running an EXPORT INTO EXA command from the source database connecting to a SaaS database is not yet supported.
  • To load data from a SaaS database (source database) into another Exasol database (target database), you must run an EXPORT INTO EXA command from the source database. Running an IMPORT FROM EXA command from the target database connecting to a SaaS database is not yet supported.

The following table explains which methods are supported, assuming all other prerequisites are met:

Target Database Source Database Available Commands
SaaS Database SaaS Database Not supported
SaaS Database Non-SaaS Database Target Database: IMPORT FROM EXA

Source Database: EXPORT INTO JDBC

Non-SaaS Database

SaaS Database

Target Database: IMPORT FROM JDBC

Source Database: EXPORT INTO EXA

Non-SaaS Database Non-SaaS Database Target Database: IMPORT FROM EXA or IMPORT FROM JDBC

Source Database: EXPORT INTO EXA or EXPORT INTO JDBC

EXA Interface

Prerequisites

  • The port range from 20000 to 21000 must be opened in the database defined in the IMPORT or EXPORT statement (or in the CONNECTION used in these statements).
  • The internal and external IP addresses of the database defined in the IMPORT or EXPORT statement (or in the CONNECTION used in these statements) must have a uniform distance between all active nodes. For example, a database with the IP Address 192.168.56.101, 192.168.56.102, 192.168.56.103 fulfills this requirement because the distance between nodes is 1. A database with an IP Address 192.168.56.101, 192.168.56.105, 192.168.56.106 does not fulfill this requirement because the distance between nodes is not uniform (in this case, it is 4 and 1, respectively)
  • If the database defined in the IMPORT or EXPORT statement (or in the CONNECTION used in these statements) is a SaaS database, all IP Addresses of the database nodes where the statement is running must be added to the allow list.

Load Data using EXA Interface

Run the following statement on your target database to create a connection to a different Exasol database. Replace each field with the values matching the database you want to connect.

CREATE OR REPLACE CONNECTION EXASOL_CONNECTION
TO '192.168.0.72:8563'
USER 'SYS'
IDENTIFIED BY 'exasol';

Run the following statement on your target database to test the connection.

 SELECT * FROM 
 (IMPORT FROM EXA AT EXASOL_CONNECTION
   STATEMENT 'select ''Connection works'' '
  );

 

To view the Exasol to Exasol migration script, refer to our GitHub repository.

JDBC Interface

Prerequisites

  • The two databases are able to communicate with each other.
  • If the database defined in the IMPORT or EXPORT statement (or in the CONNECTION used in these statements) is a SaaS database, all IP Addresses of the database nodes where the statement is running must be added to the allow list.

Load Data using JDBC Interface

Run the following statement on your target database to create a connection to a different Exasol database. Replace each field with the values matching the database you want to connect.

CREATE OR REPLACE CONNECTION EXASOL_CONNECTION_JDBC
TO 'jdbc:exa:192.168.0.72:8563'
USER 'SYS'
IDENTIFIED BY 'exasol';

A connection to a SaaS database would look like the following:

CREATE OR REPLACE CONNECTION EXASOL_SAAS_CONNECTION_JDBC
TO 'jdbc:exa:my_database_id.clusters.exasol.com:8563'
USER 'my_user_name'
IDENTIFIED BY 'my_personal_access_token';

Run the following statement on your target database to test the connection.

 SELECT * FROM 
 (IMPORT FROM JDBC AT EXASOL_CONNECTION_JDBC
   STATEMENT 'select ''Connection works'' '
  );