Data Extract Tool

The data extract tool is a part of the JDBC driver to unload data. You can execute the tool directly from the JDBC CLI. The tool extracts a result set to delimited files in the target location. To invoke the JDBC CLI, see the JDBC Manual.

Supported Data Extract Formats

The data extract tool supports unloading result sets into files in specific formats. Supported extract formats are:

• CSV — Outputs result sets as text files with fields separated by a chosen delimiter.

General Command Structure

Here is the general structure of an extract command.

EXTRACT TO <location_type> [OPTIONS([param=value [,...]])] AS <query>

The command is case-insensitive. Each extract command must start with EXTRACT TO. The location type needs to follow and must be either LOCAL or Amazon® Web Services℠ (AWS℠) S3. You can enclose additional options within a pair of parentheses following the word OPTIONS. Note that location type is the only required option. Next, the query follows the word AS.

This example is a simple general command structure.

EXTRACT TO LOCAL OPTIONS(
  file_prefix="/home/user/out/data_",
  file_extension=".csv"
)
AS SELECT c1 FROM sys.dummy10;

For supported options, see Extract Options.

Specify Options, Quoting, and Escaping Quotes

Here is the general format of options.

key1 = value1, key2 = value2, ... , keyN = valueN

You need to follow certain guidelines when you specify options. Keys (option names) can only consist of alphanumeric characters and are unquoted. Values can be either quoted (with the reserved character ") or unquoted. If values are unquoted, they can only contain alphanumeric characters. If the value has a non-alphanumeric character, you must quote it with the reserved character ". Note that the single quote character does not work.

OPTIONS(file_prefix = "/path/to/dir/result", header_mode = none, file_extension = ".csv")

To use the reserved quote character " as an argument, you must escape it with the backslash character \. To use \ as an argument, you must escape it with another \. This code illustrates both of these scenarios.

OPTIONS(field_optionally_enclosed_by = "\"", escape = "\\")

File Naming Conventions

When you use the extract tool, the tool produces a number of files. If the tool extracts with a single thread, the tool names files in this convention: {file_prefix}_{file_number}{file_extension}{gzip_extension}

1. file_prefix: Option specified by the user.
2. file_number: This part of the convention is 0 if all results go into one file. However, if MAX_ROWS_PER_FILE is set, then the tool places rows into one file until that limit is reached. Then, the tool generates another file with an incremented file_number. File number starts from 0.
3. file_extension: Option specified by the user.
4. gzip_extension: If you specify gzip compression, then the tool adds the .gzip suffix.

If you use the multithreaded extract, then the naming convention is: {file_prefix}{thread_number}_{file_number}{file_extension}

file_prefix, file_extension, gzip_extension is still determined with the set options. thread_number is the number of the thread going from 0 to N - 1 where N is the number of threads specified. The file number is now calculated per thread with the number of rows given to that thread. Recall that rows are distributed to threads in a round-robin order, starting with thread 0. So, thread 0 receives the rows 0, N, 2N, 3N, etc. Thread 1 receives the rows 1, N + 1, 2N + 1, 3N + 1, etc.

Extract Options

Examples

OPTIONS(...) is only required when you specify additional options. This example unloads the results of SELECT c1 FROM sys.dummy10 to the local machine at the relative path result-0.csv.

EXTRACT TO LOCAL
AS SELECT c1 FROM sys.dummy10;

This example extracts the results of SELECT c1 FROM sys.dummy10 to the local machine at the absolute path /home/user/out/data_0.csv.

EXTRACT TO local OPTIONS(
  file_prefix="/home/user/out/data_",
  file_extension=".csv"
)
AS SELECT c1 FROM sys.dummy10;

The result set has 100 rows, and MAX_ROWS_PER_FILE is set to 10. Thus, the tool writes 10 files to S3 with the keys query_results/query_0/_results_{file_number}.csv.gzip, where file_number ranges from 0 to 9, inclusive.

Related Links

Connect Using JDBC

JDBC Manual