Load data from Exasol

This article explains how to load data from another Exasol database.

You can load data from another Exasol database using the native EXA or JDBC interfaces. Using the native EXA interface is faster than the JDBC interface.

In this article we use the following terminology:

Target database: The database that you are loading data into.

Source database: The database that contains the data you want to load.

Data load methods

When you transfer data between two Exasol databases, you can choose to either pull or push the data. This essentially refers to from which database you are carrying out the operation.

Pull data

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

For more details about how to use the IMPORT command, see IMPORT.

Push data

This method 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.

For more details about how to use the EXPORT command, see EXPORT.

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 private and public 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 addresses 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 the IP addresses 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 the EXA interface

To create a connection to the source database, run the following statement on the target database. Replace the URL and credentials with the values matching the database that you want to connect to. 

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

To test the connection, run the following statement on the target database.

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

Exasol provides a migration script that will generate the CREATE SCHEMA, CREATE TABLE, and CREATE IMPORT statements to load all needed data from an Exasol database. The script can be downloaded from our GitHub repository: exasol_to_exasol.sql

JDBC interface

Prerequisites

  • The two databases are able to communicate with each other.

Load data using the JDBC interface

To create a connection to the source database, run the following statement on the target database. Replace the URL and credentials with the values matching the database that you want to connect to. 

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

A connection to an Exasol 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';

To test the connection, run the following statement on the target database.

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