EXPORT

Syntax

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). For more information about adding drivers, see Driver Management.

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

For parallelization in IMPORT/EXPORT operations using an Exasol connection (EXA), you can define multiple hosts in the connection string. Parallel connections are then established using the existing database port. No additional ports need to be opened by the database.

The connection string supports OpenID authentication and the following ODBC parameters: SSLCertificate, AuthMethod, HostTimeOut, LoginTimeout, Encryption, LogMode, EXALogFile, and Fingerprint. Other parameters are not supported.

You must include all hosts in the connection string.

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

You can export data to files that are stored on remote services.

FTP, FTPS, SFTP, HTTP, and HTTPS servers are supported if connection data is defined using connection_def.

• 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'

• In case of URLs starting with ftps://, Exasol uses the implicit encryption.

• 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 encrypted connections, TLS certificate verification is disabled by default. You can enable or disable certificate verification in the following ways:

  • Set the database parameter -etlCheckCertsDefault=1

  • Enable or disable certificate verification directly in the SQL statement:

    VERIFY CERTIFICATE: Enables certificate verification.

    IGNORE CERTIFICATE: Disables certificate verification.

    PUBLIC KEY 'sha256//*******': Specifies the public key for certificate verification.

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.

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'
    VERIFY CERTIFICATE PUBLIC KEY 'my_public_key'
    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,192.168.6.12:8563;HostTimeOut=1000;Encryption=Y'
    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 table1 INTO CSV 
    AT CLOUD AZURE BLOBSTORAGE 'DefaultEndpointsProtocol=https;EndpointSuffix=core.windows.net' 
    USER '<AccountName>' 
    IDENTIFIED BY '<AccountKey>' 
    FILE '<container>/<blob>';

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:

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';