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.

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:

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

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