Skip to end of metadata
Go to start of metadata

The Community Edition is a one-node EXASOL installation that runs in a virtual environment. It is intended for commercial, private or academic use. And it's free! 

How it works is simple: you run the EXASOL system – which includes the Operating System and storage functionality – on a virtual machine. This system provides a default database instance (called EXAone). You can connect to this database instance using EXAplus, EXASOL's SQL client (or if you prefer, you can use other supported clients). You can also manage the system using a browser on your local machine to connect to EXAoperation, EXASOL's operations interface.

Step 1: Prepare

To get to the point where you can play around, you need to set some things up. Do the following in no particular order:

Download and install your virtualization software

If you do not already use a virtual environment (such as Virtualbox, VMWare, VMWare Player, KVM, etc.), you will need to set one up. If you plan to load data from your local machine, you do not have to change the standard network settings. In other cases, you might want to set it to either NAT or Bridge. Refer to the documentation of your chosen virtualization software for information on how to do this.

Download EXASOL Community Edition

It goes without saying that if you are going to use EXASOL Community Edition, you will first need to download it! It is available for download here.

You have a choice between the virtual machine image (.ova) or a disk image (.iso). In the case of the virtual machine image, the license is already installed. If you opt for the ISO file, you will receive a license file separately and will have to install it yourself (don't worry, it's really easy to do!). 

Tip

EXASOL Community Edition comes with Community Support. You can also purchase additional support options - for more information, refer to: http://www.exasol.com/en/services/#panel2174-3100

Download and install EXAplus

EXAplus is EXASOL's SQL client, and can be downloaded in the downloads section of the User Portal. 

Note that there are also other options available to you for connecting to the database. For the purpose of getting going fast, this guide refers only to EXAplus. 


Step 2: Import the Virtual Machine image

Import the Community Edition image using your virtualization software. The virtual machine has already been configured with default values and is ready to run. However, there are some parameters that can be changed.

Must check

Check the boot order: the virtual machine must be configured to boot from hard drive.

Consider changing

To improve the performance of your EXASOL installation, you can consider changing the following default values in the virtual machine's properties:

  • VM RAM: The default RAM for the Community Edition virtual machine is 4GB. You can increase this to improve performance, but always ensure that there is enough RAM remaining for the host OS.
  • Number of virtual cores: Depending on the capacity of your host system, you can increase the number of virtual cores for the virtual machine.

Do not change any other settings of the imported virtual machine.

Step 3: Start it up!

Start the virtual machine to boot up the EXASOL system.

If everything ran smoothly, you will see a screen that looks similar to this one:

On this screen you have two important bits of information:

  • The DB connection string and user details for connecting to the database instance
  • The EXAoperation URL and user details for connecting to EXAoperation

You're now ready to connect!

Important

If you opted for the .ISO file when downloading the Community Edition, and you received a license from EXASOL, you need to connect to EXAoperation next and install the license.

Connect to the database

On your local machine, open EXAplus and connect to the database using the DB string and the default user name/password combination (sys/exasol).

Connect to EXAoperation

EXAoperation lets you update the EXASOL license and drivers, and provides an overview of the system (such as database instances, available space, etc.).

To access EXAoperation, open a browser on your local machine and enter the EXAoperation URL from Step 2. A login page will open, and you can login using the default user name/password combination (admin/admin).


Add data to the database

You can import data using either an ETL tool of your choice, or our internal bulk loader. This is a very simply but powerful SQL command, and is fully integrated with EXASOL's transaction concept. 

If you would like some sample data to play around with, we provide some basic data, as well as the scripts to import it into your EXASOL instance, at https://github.com/EXASOL/opendata-examples/tree/master/starter_database. Refer to the description in the GitHub repository for more information.

Directly importing data from databases

To make it as easy as possible, we've prepared scripts for you which can automate the task of creating the schema structure and generating appropriate IMPORT statements. You only need the connection information and a user with appropriated rights for the database you want to load data from. Please find the appropriate scripts for various data sources on our open source GitHub repository: https://github.com/EXASOL/database-migration

Loading data from files

With the IMPORT command you can load CSV / FBV files directly from your local file system or from via ftp(s), sftp and http(s) servers or from S3 or Hadoop.


 Load data from CSV Files

First you should create a table with the according column types and column names inside EXASOL. 

Load data from the local file system:

  IMPORT into <targettable> from local CSV file '/path/to/filename.csv' <optional options>;

Load data from a server or webservice:

  IMPORT into <targettable> from CSV at 'ftp://<your_server>/path_to_file/' user 'username' identified by 'somepass' file 'filename.csv' <optional options>;
 

There are several options available to specify the format of the file:

Skip a header:

If your files contain a header: SKIP=1

Set the right linebreak:

  • Windows: ROW SEPARATOR='CRLF'
  • Linux(default): ROW SEPARATOR='LF'
  • Mac: ROW SEPARATOR='CR'

Set the separator between the fields (usually comma):

COLUMN SEPARATOR='<your_character>'

Set the delimiter of the fields (common: double quotes):

COLUMN DELIMITER='<your character>'


If your data (e.g. dates / timestamps) doesn't match the default format, you can either change the default format:

    alter session set NLS_DATE_FORMAT='DD.MM.YYYY'; --or your format
    alter session set NLS_TIMESTAMP_FORMAT='DD.MM.YYYY-HH24:MI.SS.FF3'; --or your format

or you can specify after the file the list of columns with the according format. To e.g. specify the format for column 6:

    ... file 'myfile.csv' (1..5,6 format='DD.MM.YYYY',7..12) ...

If you want to monitor the progress of the import: please open a new EXAplus, connect and select the information from the according system table. The amount of rows that have been read till now are displayed in the column ACTIVITY:

    select * from EXA_DBA_SESSIONS;


See also this comprehensive example with commonly used options:

IMPORT INTO DWH.STATE_DIM
	FROM LOCAL CSV FILE 'C:\Work\SampleData\STATE_DIM.csv' (1..3,4 FORMAT = 'DD/MM/YYYY HH24:MI:SS.FF6',5 FORMAT = 'DD/MM/YYYY HH24:MI:SS.FF6')
	ENCODING = 'UTF-8' 
	ROW SEPARATOR = 'CRLF' 
	COLUMN SEPARATOR = ',' 
	COLUMN DELIMITER = '"' 
	SKIP = 1 
	REJECT LIMIT 0;

Another way to import files is the Import Wizard in EXAplus (GUI). You find it in the context menu of a table (right click) in the EXAplus database browser.

 Load data from Amazon S3

You can write data from an EXASOL database to AWS S3 storage, or alternatively read data from AWS S3 storage.

To do either of these, you must first connect to your S3 bucket.

Connect to S3 Bucket
CREATE CONNECTION AWS_BUCKET
    TO 'https://<bucket_name>.s3.amazonaws.com'
    USER '<AWS_ACCESS_KEY>'
    IDENTIFIED BY '<AWS_KEY_SECRET>';

To write to AWS S3 storage, you export data to an AWS CSV bucket file, and import this file into your S3 storage.

Export to S3 Bucket File
EXPORT <tablename> 
	INTO CSV AT 'https://<bucketname>.s3.amazonaws.com'
	USER '<key-ID>' IDENTIFIED BY '<secret-key>' -- user/password
	FILE 'test/test.csv';
Import from S3 Bucket File
IMPORT INTO <tablename> 
	FROM CSV AT 'https://<bucketname>.s3.amazonaws.com'
	USER '<key-ID>' IDENTIFIED BY '<secret-key>' -- user/password
	FILE 'test/test.csv';
 Load data from HIVE

 The following solution describes the process of connecting and loading data from HIVE: HIVE JDBC Connection




  • No labels