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 docid apnndn tjqmjdd5oqdvd to use the data extract tool, you must have jdbc version 2 63 or higher 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 {{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 docid 1 ls6prwuefq it66b4ee 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} file prefix option specified by the user 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 file extension option specified by the user 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 158,94,270,135 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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; sys dummy creates a virtual table with the specified number for rows for details, see docid\ etf0ovy63hqzxjjt yvop 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 docid 1 p8y vgpzkd8k 0hxqd7 docid apnndn tjqmjdd5oqdvd