Exporting Data

EXPORT...INTO

Kinetica can export data from one or more tables, using an EXPORT...INTO statement. The export makes use of a data sink configured to allow transmitting data as one or more INSERT statements issued to a remote database via JDBC (or CData JDBC).

EXPORT...INTO Syntax
1
2
3
4
5
6
7
8
EXPORT
<
      TABLE [<schema name>.]<table name>
      |
      QUERY (<source data query>)
> INTO
REMOTE QUERY '<target data insert>'
[WITH OPTIONS (<export option name> = '<export option value>'[,...])]

Note

No automatic data type transformations are done between the local data and remote column types. If any transformations are necessary, the QUERY clause should be used and the transformation done within it.

Parameters Description

TABLE

[<schema name>.]<table name>

Source data specification clause, where [<schema name>.]<table name> is the name of the table (and, optionally, its schema) to export into the remote database.

Note

This clause is mutually exclusive with the QUERY clause.

QUERY

'<source data query>'

Source data specification clause, where <source data query> is a SQL SELECT statement to run locally to generate a result set to export into the remote database.

Note

This clause is mutually exclusive with the TABLE clause.

REMOTE QUERY

'<target data insert>'

Target data specification clause, where <target data insert> is a SQL insert statement defining the way in which data will be loaded into the remote database.

Note

The target table must already exist in the remote database.

WITH OPTIONS

Optional indicator that a comma-delimited list of export option/value assignments will follow.

Option Description
BATCH SIZE

Use an ingest batch size of the given number of records.

The default batch size is 20,000.

DATASINK_NAME Export data through the given data sink. Data sink connect privilege is required when exporting through a data sink.

Examples

Export Table via Data Source

To export a table of employees through the JDBC data sink example.jdbc_dsink, into a remote database table named example.remote_employee:

EXPORT...INTO (Data Sink from Table) Example
1
2
3
EXPORT TABLE example.employee INTO
REMOTE QUERY 'INSERT INTO example.remote_employee VALUES (?, ?, ?, ?, ?, ?, ?)'
WITH OPTIONS (DATASINK_NAME = 'example.jdbc_dsink')

Export Query via Data Source

To export a query of employees in department 2 through the JDBC data sink example.jdbc_dsink, into a remote database table named example.remote_employee_dept2:

EXPORT...INTO (Data Sink from Query) Example
1
2
3
4
5
6
7
8
EXPORT QUERY
(
    SELECT id, manager_id, first_name, last_name, salary, hire_date
    FROM example.employee
    WHERE dept_id = 2
) INTO
REMOTE QUERY 'INSERT INTO example.remote_employee_dept2 VALUES (?, ?, ?, ?, ?, ?)'
WITH OPTIONS (DATASINK_NAME = 'example.jdbc_dsink')