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::=
dbms_dst:=
file_dst:=
connection_def:=
cloud_connection_def::=
csv_cols:=
fbv_cols:=
file_opts:=
error_clause:=
script_dst:=
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
-
The source data in Exasol can either be a table, using an identifier such as
MY_SCHEMA.MY_TABLE
, or a query such asSELECT * FROM DUAL
. For tables, you can also specify the columns to be used.
-
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 toSTATEMENT
), 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 withREPLACE
orCREATED 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.
-
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 theSECURE
keyword is used, the server uses the HTTPS protocol and the data is transferred encrypted. If theSECURE
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.
-
-
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
andIDENTIFIED 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
-
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
-
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: theCOLUMN SEPARATOR
, theROW SEPARATOR
, theCOLUMN 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')
-
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
orRIGHT
. The default value isLEFT
.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.
-
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 optionDELIMIT 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: theCOLUMN SEPARATOR
, theROW SEPARATOR
, theCOLUMN 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
.
-
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.
-
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 aSELECT
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 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:
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. |
|
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