Export Command

The export command exports the results of an SQL query to a CSV (comma separated value) file.

Syntax

EXPORT ( exportPath,
         compression,
         replicationCount,
         fileEncoding,
         fieldSeparator,
         quoteCharacter )  <SQL_QUERY>;

exportPath

The directory in which you want the export file(s) written.

compress

Whether or not to compress the exported files. You can specify one of the following values:

Value Description
true The exported files are compressed using deflate/gzip.
false Exported files are not compressed.

replicationCount

The file system block replication count to use for the exported CSV files.

You can specify any positive integer value. The default value is 1.

fileEncoding

The character set encoding to use for the exported CSV files.

You can specify any character set encoding that is supported by the Java Virtual Machine (JVM). The default encoding is UTF-8.

fieldSeparator

The character to use for separating fields in the exported CSV files.

The default separator character is the comma (,).

quoteCharacter

The character to use for quoting output in the exported CSV files.

The default quote character is the double quotation mark (").

Usage

The EXPORT command generates one or more CSV files and stores them in the directory that you specified in the exportPath parameter. More than one output file is generated to enhance the parallelism and performance of this operation.

If compression=true, then each of the generated files is named with this format:

export_<N>.csv.gz

If compression=false, then each of the generated files is named with this format:

export_<N>.csv

The value of <N> is a random integer value.

Merging the Exported Files

You can copy all of the exported files into a single file on your local file system using the Hadoop FS command getmerge. The syntax for getmerge is:

hadoop fs -getmerge sourceDir localPath

Use the exportPath directory as the value of sourceDir to copy all of the exported CSV files to your localPath.

For more information about the getmerge command, see http://hadoop.apache.org/docs/current/hadoop-project-dist/hadoop-common/FileSystemShell.html#getmerge.

Examples

         -- This example uses all default options:
 splice> EXPORT('/my/export/dir', false, null, null, null, null)
          SELECT a,b,sqrt(c) FROM t1 join t2 on t1.a=t2.a;

         -- This example explicitly specifies options:
splice> EXPORT('/my/export/dir', false, 3, 'utf-8', '|', ';')
          SELECT a,b,sqrt(c) FROM t1 join t2 on t1.a=t2.a;

See Also