Loading Data from Exasol

This section describes 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.

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)

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.

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';

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'' '
  );