EXPORT

Purpose

Use the EXPORT command to transfer data from Exasol into external files or database systems.

Prerequisites

  • In Exasol, you must have the system privilege EXPORT and the corresponding privileges to read the table contents.
  • In the target system, you must have the privileges to insert rows or writing files. You must also have the rights to replace or truncate the target.
  • When using a connection you must have the system privilege USE ANY CONNECTION, or the connection must be granted either to the user or to one of the user roles by the GRANT statement. For more information, see CREATE CONNECTION.

Syntax

export::=

Export

Export

dbms_dst:=

dbms dst

dbms dst

file_dst:=

File Destination

File Destination

connection_def:=

Connection Definition

cloud_connection_def::=

Cloud connection

csv_cols:=

CSV Export

fbv_cols:=

FBV Export

file_opts:=

File Export Options

error_clause:=

Error Clause

script_dst:=

Script Destination

Usage notes

  • Only statements or views with an ORDER BY clause on the top level are exported in sorted order (only in case of files).

  • If no other option is specified, the data is appended to the target.

  • You can view the progress of the data transfer in the column ACTIVITY of the system table EXA_USER_SESSIONS on a second connection screen.

  • For more information about ETL processes, see ETL in Exasol.

Explanation of elements and parameters in EXPORT

data_src

The source data in Exasol can either be a table, using an identifier such as MY_SCHEMA.MY_TABLE, or a query such as SELECT * FROM DUAL. For tables, you can also specify the columns to be used.

dbms_dst

Defines the database destination whose connection data is specified in connection_def. You can choose among an Exasol connection (EXA), a native connection to an Oracle database (ORA), or a JDBC connection to any database (JDBC).

Some JDBC drivers are included by default (visible in EXAoperation) and can be used within the connection string. You can additionally configure JDBC drivers in EXAoperation and choose them using the DRIVER option if the prefix is ambiguous.

Only the pre-installed JDBC drivers (marked gray in EXAoperation) are tested and officially supported. If you need help with other drivers, contact support.

For the target, you can define either a table or a prepared statement (for example, an INSERT statement or a procedure call). In the latter case, the data is passed as input data to the prepared statement. Please note that you have to use schema-qualified table names. Within the statement, use question marks as placeholders for the parameter values, for example: INSERT INTO t VALUES (?, ?, ?).

Example:
EXPORT (SELECT a,b,c FROM myschema.t) INTO JDBC AT postgres_conn
    STATEMENT 'insert into pgschema.t(a,b,c) values (?,?,?)';

When using the TABLE syntax (as opposed to STATEMENT), the table name identifier is treated similarly to Exasol tables. If the remote system expects case-sensitive syntax, you must use quote marks to delimit the table names.

Parallel EXA to EXA connections

Additional options for the target

Option Description
REPLACE Drops the target table before the export is started.
TRUNCATE

Deletes all rows of the target table before the export is started.

TRUNCATE cannot be combined with REPLACE or CREATED BY.

CREATED BY

Defines a creation string which is used to create the table on the target system before the export is started.

The CREATED BY string must contain a single SQL statement; multiple statements are not supported.

file_dst

Specifies the data file target.

  • The target file can be either a CSV or FBV file, and will then comply to the respective format specifications described in CSV Data Format and Fixblock Data format (FBV).

  • File names may only consist of ASCII characters. Using a BOM (byte order mark) is not supported.

  • Compressed files are recognized by their file extension. Supported extensions are .zip, .gz (gzip) and .bz2 (bzip2).

  • When specifying multiple files, the actual data distribution depends on several factors. It is also possible that some files are completely empty. When the files are local, they are written into one file.

Remote files

FTP, FTPS, SFTP, HTTP, and HTTPS servers are supported if connection data is defined using connection_def. The following are some of the considerations when using remote data file target:

  • Certificates are not verified for encrypted connections.
  • In case of URLs starting with “ftps://”, the implicit encryption is used.
  • In case of URLs starting with “ftp://”, Exasol encrypts the user name and password (explicit encryption), if the server supports this. If the server requires encryption, the whole data transfer is done encrypted.
  • For FTP and FTPS servers, only passive mode is supported.
  • For HTTP and HTTPS servers, only basic authentication is supported.
  • For HTTP and HTTPS connections, HTTP query parameters can be specified by appending them to the file name. For example:

    FILE 'file.csv?op=OPEN&user.name=user'

Local files

You can export to local files on your client system. When exporting local files, the JDBC driver opens an internal connection to the cluster and provides an HTTP or HTTPS server, depending on if the SECURE option is specified. If the SECURE keyword is used, the server uses the HTTPS protocol and the data is transferred encrypted. If the SECURE keyword is omitted, the server uses HTTP and the connection will be unencrypted.

The default option for the internal connection is determined by the main connection:

  • If the main connection is encrypted, the local export operation is also encrypted by default.

  • If the main connection is not encrypted, the local export operation is also not encrypted by default. In this case, you can override the driver behavior by using the SECURE keyword to enforce a secure connection.

When using the SECURE option, data transfer performance will be reduced.

This functionality is only supported when using EXAplus and the JDBC driver. It cannot be used in prepared statements or within database scripts. If you want to process a local file by an explicit program, you can use the tool EXAjload, which is included in the JDBC driver package. For more information about how to use EXAjload, execute the program without parameters.

Cloud storage

You can export data to files on cloud storage services such as Amazon S3, Azure Blob Storage, and Google Cloud Storage. The connection to the cloud storage service is then defined in cloud_connection_def. For more information, see also Load Data from CSV/FBV Files.

connection_def

Defines the connection to an external database or file server. The connection can be specified in a connection string, which may include authentication information.

For regular ETL jobs you can also make use of connections, where connection data such as username and password can easily be encapsulated. For more information, see CREATE CONNECTION.

The declaration of USER  and IDENTIFIED BY within the command are optional. If they are omitted, the authentication information in the connection string or the connection object are used.

For JDBC connections, it is possible to use Kerberos authentication by specifying specific data in the IDENTIFIED BY field. This data consists of a key which indicates that Kerberos authentication should be used (ExaAuthType=Kerberos), a base64 encoded configuration file and a base64 encoded keytab file containing the credentials for the principal.

Example
EXPORT table1 INTO JDBC AT '<JDBC_URL>' 
    USER '<kerberos_principal>' 
    IDENTIFIED BY 'ExaAuthType=Kerberos;<base64_krb_conf>;<base64_keytab>' 
    TABLE table2; 

cloud_connection_def

Defines a connection to a cloud storage service. The connection can be specified in a connection string, which may include authentication information. The format of the connection string can be different depending on the cloud service.

For more information, see see also Load Data from CSV/FBV Files.

Example
CLOUD AZURE BLOBSTORAGE 'DefaultEndpointsProtocol=https;EndpointSuffix=core.windows.net' 
    USER '<AccountName>' IDENTIFIED BY '<AccountKey>';

csv_cols

Defines how the column data is written to a CSV file. For more information, see CSV DATA Format.

Option Description

col_nr

Defines the column number. The first column is 1.

You can also define a column range, for example, 5..8 for columns 5, 6, 7, 8. Column numbers must be in ascending order.

FORMAT

Optional format definition for numbers or datetime values. The default is the session format.

For more information, see Numeric format models and Date/time format models.

DELIMIT

AUTO (default) = Field delimiters are written only if special characters occur within the data: the COLUMN SEPARATOR, the ROW SEPARATOR, the COLUMN DELIMITER, or a whitespace character.

ALWAYS= Column separators are always written.

NEVER = Column separators are never written.

This local column option overwrites the global option. For more details, see file_opts.

Using DELIMIT=NEVER may have the result that the exported data cannot be imported again into Exasol.

Example

In this example, four columns are exported into the CSV file, with the fourth column using a specified date format.

(1..3,4 FORMAT='DD-MM-YYYY')

fbv_cols

Defines how the column data is written to an FBV file. For more information, see Fixblock Data Format.

The following elements can be specified in an FBV file:

Element Description
SIZE

Defines the number of bytes of the column. The default value is calculated by the source data type.

Source data type Default number of bytes
BOOL 1
DECIMAL(p) p + 1 (sign)
DECIMAL(p,s) p + 2 (sign+point)
DECIMAL(p,p) p + 3 (sign+point+leading zero)
DOUBLE 21
DECIMAL/DOUBLE with format Dependent on format string
DATE/TIMESTAMP Dependent on format string
VARCHAR(n)/CHAR(n) n x 4 in case of UTF-8 columns, n in case of ASCII columns.
FORMAT

Optional format definition for numbers or date time values. Default: session format.

For more information, see Numeric format models and Date/time format models.

ALIGN

Alignment of the column.

Allowed values are LEFT or RIGHT. The default value is LEFT.

PADDING

Padding characters for columns.

By default, a space character is used. You can also specify an ASCII character, either in plain text ('+'), as a hexadecimal value ('0x09'), or as an abbreviation ('NUL', 'TAB', 'LF', 'CR', 'ESC').

Example

In this example, four columns are exported into the FBV file. The first column is aligned to the right and filled with 8 bytes with + characters, the fourth column has the specified data format.

(SIZE=8 PADDING='+' ALIGN=RIGHT,
 ,
 ,
 FORMAT='DD-MM-YYYY')

file_opts

Option Description
BOOLEAN

Representation of boolean values.

The following value pairs can be defined in a string:

'1/0', 'TRUE/FALSE', 'true/false', 'True/False', 'T/F', 't/f', 'y/n', 'Y/N', 'yes/no', 'Yes/No', 'YES/NO'

The boolean value pairs are automatically accepted when inserting strings into a boolean column.

COLUMN DELIMITER

Defines the field delimiter for CSV files.

By default, the double quote character " is used. You can specify any string as a delimiter, either as plain text ('"'), as a hexadecimal value ('0x09'), or as an ASCII abbreviation ('NUL', 'TAB', 'LF', 'CR', 'ESC').

A plain text value is limited to 10 characters, which are automatically converted to the encoding set for the file with the ENCODING option.

A hexadecimal value is limited to 10 bytes (not characters) and will not be converted.

If you do not want to use any field delimiter, define an empty string (''), or use the option DELIMIT NEVER.

COLUMN SEPARATOR

Defines the field separator for CSV files.

By default, comma is used. You can specify any string as a separator, either as plain text (','), as a hexadecimal value ('0x09'), or as an ASCII abbreviation ('NUL', 'TAB', 'LF', 'CR', 'ESC').

A plain text value is limited to 10 characters, which are automatically converted to the encoding set for the file with the ENCODING option.

A hexadecimal value is limited to 10 bytes (not characters) and will not be converted.

DELIMIT

AUTO (default) = Field delimiters are written only if special characters occur within the data: the COLUMN SEPARATOR, the ROW SEPARATOR, the COLUMN DELIMITER, or a whitespace character.

ALWAYS= Column separators are always written.

NEVER = Column separators are never written.

This global option can be overwritten within the single column definitions. For more information, see csv_cols.

ENCODING

Encoding of the CSV or FBV files. Default is UTF-8.

For information about supported encodings, see Supported Encodings for ETL Processes.

NULL Representation of NULL values. If you do not specify this option, NULL values are represented by an empty string.
REPLACE

Replaces the target if it already exists.

In case of HTTP[S] servers, the target file will always be newly created due to protocol limitations.

ROW SEPARATOR

Defines the line break character. 

'LF' (default) corresponds to the ASCII character 0x0a (Unix/Linux).

'CR' corresponds to the ASCII character 0x0d (macOS).

'CRLF' corresponds to the ASCII characters 0x0d and 0x0a (Windows).

'NONE' means no line break. This is only allowed in FBV files.

TRUNCATE Deletes the data of the target before loading data.
WITH COLUMN NAMES

This option is only possible for CSV files. If this option is set, an additional row is written at the beginning of the file which contains the column names of the exported table. In case of a subselect, this can also be expressions. The other options, such as the column separator, are also applied for that row.

If you want to import the same file back into Exasol, use the IMPORT statement with option SKIP = 1.

error_clause

Defines how many invalid rows of the source are allowed.

For example, in the case of REJECT LIMIT 5, the statement will work fine if there are less than or equal to five invalid rows and would throw an exception after the sixth row.

REJECT LIMIT 0 has the same behavior as when you have omitted the error clause completely.

script_dst

Specifies the UDF script to be used for a user-defined export. Optionally, you can define a connection or properties that will be forwarded to the script. The specified script will generate a SELECT statement internally that will be executed to do the actual export. The script has to implement a special callback function that receives the export specification (for example, parameters and connection information) and returns a SELECT statement. For more information, see User-defined IMPORT/EXPORT Using UDFs.

connection_def

Optional connection definition for being able to encapsulate connection information such as password. For more information, see connection_def.

WITH parameter=value...

Optional parameters to be passed to the script. Each script can define the mandatory and optional parameters it supports. Parameters are simple key-value pairs, with the value being a string.

Example
... WITH PARAM_1='val1' PARAM_2 = 'val2';

Examples

EXPORT tab1 INTO CSV
    AT 'ftp://192.168.1.1/' 
    USER 'my_user' IDENTIFIED BY 'my_secret'
    FILE 'tab1.csv'
    COLUMN SEPARATOR = ';'
    ENCODING = 'Latin1'
    WITH COLUMN NAMES;
CREATE CONNECTION my_connection
    TO 'ftp://192.168.1.1/' 
    USER 'my_user' IDENTIFIED BY 'my_secret';

EXPORT (SELECT * FROM T WHERE id=3295) INTO FBV
    AT my_connection
    FILE 't1.fbv' FILE 't2.fbv'
    REPLACE;
EXPORT (SELECT * FROM my_view) INTO EXA
    AT '192.168.6.11..14:8563'
    USER 'my_user' IDENTIFIED BY 'my_secret'
    TABLE my_schema.my_table
    CREATED BY 'CREATE TABLE my_table(order_id INT, price DEC(18,2))';
EXPORT tab1 INTO JDBC DRIVER='MSSQL'
    AT 'jdbc:sqlserver://dbserver;databaseName=testdb'
    USER 'my_user' IDENTIFIED BY 'my_secret'
    TABLE my_schema.tab1;
EXPORT tab1 INTO CSV
    AT 'http://HadoopNode:50070/webhdfs/v1/tmp'
    FILE 'file.csv?op=CREATE&user.name=user';
EXPORT tab1 INTO SCRIPT etl.export_hcat_table
WITH HCAT_DB = 'default'
     HCAT_TABLE = 'my_hcat_table'
     HCAT_ADDRESS = 'hcatalog-server:50111'
     HDFS_USER = 'hdfs';
EXPORT tab1 INTO LOCAL CSV 
    FILE '/tmp/my_table.csv'
    COLUMN SEPARATOR = ';';

Export data to an Amazon S3 bucket:

EXPORT tab1 INTO CSV
    AT 'https://testbucket.s3-<region>.amazonaws.com'
    USER '<AccessKeyID>' IDENTIFIED BY '<SecretAccessKey>'
    FILE 'file.csv';

Export data to Azure Blob Storage:

EXPORT table1 INTO CSV 
    AT CLOUD AZURE BLOBSTORAGE 'DefaultEndpointsProtocol=https;EndpointSuffix=core.windows.net' 
    USER '<AccountName>' 
    IDENTIFIED BY '<AccountKey>' 
    FILE '<container>/<blob>';

Export data to Google Cloud Storage

EXPORT table1 INTO CSV
    AT 'https://testbucket.storage.googleapis.com' 
    USER '<Access ID>' IDENTIFIED BY '<secret>'
    FILE 'my_path/file.csv';

Encryption when exporting files to AWS

The EXPORT statement supports server-side encryption with Amazon S3 managed keys (SSE-S3) and Customer Managed Keys (CMK) stored in AWS Key Management Service (SSE-KMS). For more information about the server side encryption options available with AWS, see Protecting Data Using Server-Side Encryption.

Exasol does not support server-side encryption with customer provided keys (SSE-C).

To enable the encryption, add the following parameters in the IDENTIFIED BY field of your statement:

Parameter Required Description
secret-key

The parameter is not required for temporary EC2 credentials.

It is required for password authentication.

Secret access key
sse_type

It is required only for SSE-S3.

aws:kms for SSE-KMS

AES256 for SSE-S3

kms_key_id It is required only for SSE-KMS. KMS Key ID (ARN) to be used for encryption
enc_context The parameter is not required. JSON-formatted string key-value pairs
Example 1

SSE-S3 encryption, user/password authentication

EXPORT test_table INTO CSV 
AT 'https://testbucket.s3.amazonaws.com'
    USER '<key-ID>' IDENTIFIED BY '<secret-key>;sse_type=AES256'
    FILE 'testpath/test.csv';
Example 2

SSE-KMS encryption with encryption context, temporary credentials for EC2 role authentication

EXPORT test_table INTO CSV 
    AT 'https://testbucket.s3.amazonaws.com'
    USER '' IDENTIFIED BY ';sse_type=aws:kms;kms_key_id=arn:aws:kms:eu-west-1:<XXXXX>:key/<YYYYY>;enc_context={"foo":"bar", "baz":"qux"}'
    FILE 'testpath/test.csv';