Quick start with Exasol Personal

Learn how to quickly spin up an Exasol database and run your first query.

Exasol Personal is a completely free, single-user edition of Exasol running in your own cloud environment, allowing you to quickly get started with Exasol. This tutorial takes you through the steps to install Exasol Personal with a default configuration in an Amazon Web Services (AWS) account and get started using your database.

The following procedure assumes that you have a basic understanding of how AWS works and how to set up access using AWS Identity and Access Management (IAM). For more information, refer to the official AWS documentation.

Prerequisites

  • An AWS account that can provision large type EC2 instances. The default instance type when you deploy Exasol Personal is r6i.xlarge. You can select different instance types during installation.

  • An AWS user with enough permissions to create resources such as EC2 instances. For more details, see AWS setup for Exasol Personal.

Step 1: Install Exasol Personal

  1. Download Exasol Launcher for your platform using one of these methods (depending on your platform):

    • On Linux and macOS, run the following command:

      Copy 
      curl https://downloads.exasol.com/exasol-personal/installer.sh | sh

    • On all platforms including Windows: download Exasol Launcher from the Exasol Download Portal.

  2. Copy the path to the exasol binary into your PATH.

  3. Create a new directory deployment and change into the directory:

    Copy 
    mkdir deployment
    cd deployment

  4. Configure the AWS_PROFILE environment variable with the AWS instance profile that you want to use with Exasol Personal. See also AWS setup for Exasol Personal.

    Examples:

    If the AWS instance profile name is exasol:

    Copy 
    # Linux / macOS (Bash)
    export AWS_PROFILE=exasol
    Copy 
    # Windows (PowerShell)
    $env:AWS_PROFILE = "exasol"
    Copy 
    # Windows (cmd)
    set AWS_PROFILE=exasol

  5. To install Exasol Personal in the AWS account, run the following command:

    Copy 
    exasol install aws

    The exasol install command does the following:

    • Generates Terraform files in the deployment directory

    • Provisions the necessary AWS infrastructure with Terraform

    • Starts up the AWS infrastructure

    • Downloads and installs Exasol Personal on that infrastructure

    The installation process normally takes about 10 to 20 minutes to complete.

    Customize cluster size and instance type

    Exasol Launcher will by default generate Terraform files to install one Exasol node on one instance of the type r6i.xlarge. To deploy with more nodes and/or a different instance type, use the --cluster-size and --instance-type options:

    Copy 
    exasol install aws --cluster-size <number> --instance-type <string>

When the deployment process has finished, the terminal output will show the connection details for the new database. You can also find this information at any time by using exasol info in the terminal.

If the deployment process is interrupted, EC2 instances that were created by the process will not be terminated and may therefore continue to accrue cost. In case of an aborted deployment you must log in to the AWS console and manually terminate those instances.

Step 2: Connect to Exasol

Once your Exasol cluster is up and running, you can connect to it using a database client and start running queries. We recommend that you use DbVisualizer Pro, which is the SQL client used in our tutorials and trainings. However, you can use any SQL client or other compatible tool to connect to Exasol.

To connect from a client, you need to provide a connection string and credentials. To find the connection details for your new Exasol Personal database, use exasol info. The credentials for connecting to the database are stored in the file secrets-exasol-<play_id>.json in the deployment directory.

To learn more about how to connect to Exasol with various tools, see Connect to Exasol.

You can also use the built-in SQL client in Exasol Launcher to connect directly to the database from the command line:

Copy 
exasol connect

Step 3: Load sample data

Exasol provides two generated sample Parquet files on S3 for direct import using the IMPORT command:

To quickly load sample data from these datasets into Exasol Personal, just run the following DDL and IMPORT commands:

Copy 
CREATE OR REPLACE TABLE PRODUCTS (
    PRODUCT_ID        DECIMAL(18,0),
    PRODUCT_CATEGORY  VARCHAR(100),
    PRODUCT_NAME      VARCHAR(2000000),
    PRICE_USD         DOUBLE,
    INVENTORY_COUNT   DECIMAL(10,0),
    MARGIN            DOUBLE,
    DISTRIBUTE BY PRODUCT_ID
);

IMPORT INTO PRODUCTS
  FROM PARQUET AT 'https://exasol-easy-data-access.s3.eu-central-1.amazonaws.com/sample-data/'
  FILE 'online_products.parquet';
Copy 
CREATE OR REPLACE TABLE PRODUCT_REVIEWS (
    REVIEW_ID          DECIMAL(18,0),
    PRODUCT_ID         DECIMAL(18,0),
    PRODUCT_NAME       VARCHAR(2000000),
    PRODUCT_CATEGORY   VARCHAR(100),
    RATING             DECIMAL(2,0),
    REVIEW_TEXT        VARCHAR(100000),
    REVIEWER_NAME      VARCHAR(200),
    REVIEWER_PERSONA   VARCHAR(100),
    REVIEWER_AGE       DECIMAL(3,0),
    REVIEWER_LOCATION  VARCHAR(200),
    REVIEW_DATE       VARCHAR(200),
    DISTRIBUTE BY PRODUCT_ID
);

IMPORT INTO PRODUCT_REVIEWS
  FROM PARQUET AT 'https://exasol-easy-data-access.s3.eu-central-1.amazonaws.com/sample-data/'
  FILE 'product_reviews.parquet';

Step 4: Run your first queries

After loading the sample data, you can run the following sample queries in your client:

Copy 
-- number of items and average margin per product category
SELECT product_category, COUNT(*) num_items, AVG(MARGIN) avg_margin FROM products 
    GROUP BY product_category;

-- average customer rating per product category
SELECT p.product_category, AVG(rating) avg_rating FROM products p 
    JOIN product_reviews r ON p.product_id=r.product_id 
    GROUP BY p.product_category;

Next steps

Manage your new Exasol database

To learn more about how to start, stop, and uninstall Exasol Personal, see Exasol Personal administration. For more information about all administration tasks in Exasol, see Administration (on-prem).

Exasol Admin

Exasol Admin enables you to carry out common database administration tasks such as starting and stopping the database, creating backup schedules, monitoring health, generating logs, and updating Exasol from a web browser.

To find the URL to Exasol Admin in your Exasol Personal deployment, use exasol info. The credentials for connecting to Exasol Admin are stored in the file secrets-exasol-<play_id>.json in the deployment directory.

Your browser may show a security warning when connecting to Exasol Admin because of the self-signed certificate. Accept this warning and continue.

Connect using SSH

To connect with SSH to the EC2 instance that your Exasol database is running on, use exasol diag shell.

Explore the documentation

To get the most out of your Exasol database we suggest that you check out the following sections:

Load data

Learn how to use ETL and ELT processes efficiently with Exasol and how to load data into your database from different sources and file types.

Advanced analytics

Learn how to do advanced analytics and work with data science and machine learning in Exasol, and learn about Exasol AI Lab, a scalable platform that seamlessly integrates AI models into your data workflow.

SQL reference

Learn about all the SQL statements, data types, functions, and other SQL language elements that are supported in Exasol.

Additional learning resources

Developer documentation

Documentation and resources for data scientists and programmatic users who want to perform analytics with Exasol and build applications on top.

Exasol Knowledge Base

A large and constantly growing collection of articles about specific use cases and issues.

Exasol Training

Explore our large catalog of free online training and instructor-led courses.